Every 8 hours i got a Hibernate exception “Cannot release connection” within a Java application using Hibernate, Apache DBCP on Tomcat:
org.hibernate.exception.GenericJDBCException: Cannot release connection
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
..
..
Caused by: java.sql.SQLException: Already closed.
Not only that the messages polluted my inbox, the exception was visible to the enduser, resulting in a HTTP 500 error. An older blog post i found suggested dismissing DBCP and using c3p0, a solution that i’m not quite found of. At least, the post helped to reproduce the problem within my development setup. The underlying problem was indeed the MySQL wait_timeout.
There’s quite a long documentation on the Tomcat JDBC Connection Pool. Although the Tomcat team recommends their own solution since Tomcat 7, i still wanted to go with DBCP.
The relevant keywords are “testOnBorrow”, “testOnReturn”, “testWhileIdle”, “validationQuery” and “timeBetweenEvictionRunsMillis”. The first 3 are boolean values. If set to true, the query given as validationQuery is executed on borrowing a connection from the pool, on returning or when idling. The first option is not an option on production use as the query is executed before each call. Although “Select 1″ is probably very fast, i just don’t want to have. Also: The problem is an invalidated, idle connection so i set testWhileIdle to true. And what happened? Nothing! The problem stayed. So there is the last option timeBetweenEvictionRunsMillis which should, according to the docs, default to 5 seconds but it doesn’t. The documentation is wrong. It’s under zero, so the eviction thread that tests idle connections never run. I’ve tweeted the tomcat team, but there was no reaction.
So the correct configuration for a DBCP pool database source is:
<Resource
type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
maxActive="100"
maxIdle="30"
maxWait="10000"
testOnBorrow="false"
testOnReturn="false"
testWhileIdle="true"
validationQuery="Select 1"
timeBetweenEvictionRunsMillis="1800000"
/>
This way the eviction thread runs every 30 minutes, testing idle connections with the query “Select 1″ and removing them from the pool. The timeBetweenEvictionRunsMillis should not be to low. It should be adapted to the configured MySQL wait_timeout.
Share This
MySQL has an aes_encrypt/aes_decrypt pair of functions (Encryption and Compression Functions) that enable encryption and decryption of data using the official AES algorithm.
The functions are easy to use (select AES_ENCRYPT(‘text’,'password’)) and the result is easy to store (insert into secrets values HEX(AES_ENCRYPT(‘text’,'password’))) as hex values.
I used this technique for a while but i wanted to have a more database agnostic version of this encryption and tried to build the same methods with java.
Although it was relatively easy to find the exact cipher mode (which is AES/ECB/PKCS5Padding), i had a real hard time figuring out how the key is calculated from the given password (the key must be 16bytes long, per default MySql uses AES-128). It turns out that the MySQL algorithm just or’s the bytes of a given passphrase against the previous bytes if the password is longer than 16 chars and just leaves them 0 when the password is shorter than 16 chars. So you can generate a secret key spec in Java for an aes_encrypt/decrypt compatible cipher like so:
import java.io.UnsupportedEncodingException;
import javax.crypto.Cipher;
import javax.crypto.spec.SecretKeySpec;
import org.apache.commons.codec.binary.Hex;
public class Demo {
public static SecretKeySpec generateMySQLAESKey(final String key, final String encoding) {
try {
final byte[] finalKey = new byte[16];
int i = 0;
for(byte b : key.getBytes(encoding))
finalKey[i++%16] ^= b;
return new SecretKeySpec(finalKey, "AES");
} catch(UnsupportedEncodingException e) {
throw new RuntimeException(e);
}
}
public static void main(String... args) throws Exception {
// Decrypt
final Cipher decryptCipher = Cipher.getInstance("AES");
decryptCipher.init(Cipher.DECRYPT_MODE, generateMySQLAESKey("your super secret passphrase", "UTF-8"));
System.out.println(new String(decryptCipher.doFinal(Hex.decodeHex("56A34D7AB6225616799F6559AA388F07C2C9E53983111BDD5F49F36461AAD789".toCharArray()))));
// Encrypt
final Cipher encryptCipher = Cipher.getInstance("AES");
encryptCipher.init(Cipher.ENCRYPT_MODE, generateMySQLAESKey("your super secret passphrase", "UTF-8"));
System.out.println(String.format("Select aes_decrypt(unhex('%s'), 'your super secret passphrase');", new String(Hex.encodeHex(encryptCipher.doFinal("Hallo nach Aachen".getBytes("UTF-8"))))));
}
}You need Commons Codec to run these.
This isn’t probably the most secure solution from a cryptographic point of view but it just replicates the built-in MySql function for other databases or just for interoperability. I hope to save someone else time with this as i spent about days about those view lines.
Share This
In case you need a MySQL schema transferred from one host to another and the schema names differ, you can ran into problems with a standard MySQL dump.
Use the following statement to create a schema dump that contains all table and view definitions as well as all stored procedures without a reference to the original schema:
mysqldump -uroot -p name_of_the_original_schema --no-data --opt --routines | sed 's/`name_of_the_original_schema`.//g' > dump.sql
The dump will only contain the schema definition and no data. Calls to routines will not be prefixed with a schema name.
Share This
Again, the MySQL ruby gem totally annyoed me trying to install it on a fresh Mac OS X 10.5.5 install and MySQL 5.0.67.
This time the following command brought it to life:
sudo env ARCHFLAGS="-arch i386" gem install mysql -- \
--with-mysql-dir=/usr/local/mysql --with-mysql-lib=/usr/local/mysql/lib \
--with-mysql-include=/usr/local/mysql/include
Thanks to a bitter software engineer.
Share This
In den letzten Tagen habe ich einige ganz interessante, deutsche Projektdokumentationen gefunden.
Zum einen die zur Zeiterfassung Mite gehörende Diplomarbeit, die hier zum Download angeboten wird.
Zum anderen einen Aufsatz von Thomas Bachem, einem der Macher von sevenload.de
Ich finde beide Dokumentationen hochgradig interessant zu lesen, nichts desto habe ich einige Anmerkungen und Gedanken dazu:
Mite ist ein Projekt mit Ruby on Rails. Die Macher hatten am Anfang Lastprobleme, konnten das aber durch einen Umzug auf einen performanteren Server lösen.
Sevenload ist ein PHP Projekt. Leider nutze ich es nicht so häufig wie Youtube, daher kann ich keine definitive Aussage zur Geschwindigkeit treffen. Dennoch frage ich mich, ob es wirklich nötig ist, in einem Grundlagenartikel direkt mit kontrollierten Redundanzen für die einfachsten Sachen wie “durchschnittliche Bewertung eines Bildes” loszulegen? Ich meine, bin ich der einzige, der so etwas für Überflüssig hält? Letzten Endes ist es ein Einzeiler in SQL, der mit korrekter Indexerstellung kein DBMS in die Knie zwingen sollte:
SELECT avg(rating)/COUNT(*), rateable_id FROM ratings GROUP BY rateable_id ORDER BY 1 ASC;
Das dann noch mit einem inner join über die zu bewertenden Dinger verknüpft und gut.
Welcher Ansatz würde ich wählen? Ich selber würde jederzeit Standards vorziehen, im obigen Fall auf ein sauberes ER <->Objekt Mapping und auf Normalisierung in der DB (witzigerweise erwähnt Thomas Bachem das im nächsten Absatz bzgl. Tagging Schema) setzen. In anderen Worten: Lieber den Railsweg gehen und sauberes Design erhalten und dann im Zweifelsfall etwas mehr Hardware hinter her werfen.
Tatsächlich redundate Informationen zu speichern würde ich generell nicht ausschliessen, in diesem Fall allerdings schon. Ich denke, wenn man soweit unten bereits diesen Bedarf hat, wird es eng mit Optimierungen, wenn die Luft unter Last dünner wird.
Share This