All posts tagged with 'MySQL'

SQL Snippets


Some useful SQL Snippets: Oracle Create a date without any formatting hassle (like to_date): SELECT DATE’2014-01-01′ FROM dual;select date’2014-01-01′ from dual; Extract values as numbers from a date object SELECT EXTRACT (YEAR FROM DATE’2013-05-06’) FROM dual; SELECT EXTRACT (MONTH FROM DATE’2013-05-06’) FROM dual; SELECT EXTRACT (DAY FROM DATE’2013-05-06’) FROM dual;select extract (year from date’2013-05-06′) from […]

Read the complete article »

mysqldump / mysql tips


Two tips to make your life with mysqldump easier and your backups better: The following command creates a full backup of your mysql server including all databases and structures (including views and(!) stored procedures) along with the data: mysqldump -uroot -proot –opt –routines –add-drop-database –default-character-set=utf8 –create-options –all-databases | \ bzip2 > backup.sql.bz2mysqldump -uroot -proot –opt […]

Read the complete article »

Fixing hibernate “Cannot release connection” exception using DBCP and MySQL.


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( at org.hibernate.exception.SQLStateConverter.convert( at org.hibernate.exception.JDBCExceptionHelper.convert( at org.hibernate.exception.JDBCExceptionHelper.convert( .. .. Caused by: java.sql.SQLException: Already closed. Not only that the messages polluted my inbox, the exception was visible to the enduser, […]

Read the complete article »

MySql compatible AES encryption / decryption in Java


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 […]

Read the complete article »