Tom's blog

In this post, I'll setup an automatic backup script that create an sql compressed with bz2 file per database. sudo -s Autologin with MySQL : sudo -s vi /root/.my.cnf [client] user=root password=TheRootPassword protocol=tcp chmod 400 /root/.my.cnf Test : [root@dell1 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 870724 to server version: 4.1.22 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> quit Bye [root@dell1 ~]# Script vi /root/scripts/cron/backupDatabaseJob.sh #!/bin/bash TIME=`date` echo "Starting MySQL Backup at $TIME" BACKUPLOCATION=/home/backup/databases CURRENTDATE=`date +%Y%m%d` CURRENTLOCATION=$BACKUPLOCATION/MySQL_$CURRENTDATE if [ ! -d $BACKUPLOCATION ] then echo "create directory for database saves $BACKUPLOCATION" mkdir $BACKUPLOCATION fi echo "Databases saves at $CURRENTLOCATION"; mkdir -p $CURRENTLOCATION mysql --def...

I'm using Spring Framework JdbcTemplate on a project with MySQL server as database. And I've noticed that the query "SELECT last_insert_id()" returns 0 most of the time. Using this syntax improves a bit : SELECT last_insert_id() from ` last_inserted_table ` LIMIT 1 The reason is that for each jdbc access, Spring use a connection for it's connection pool. So it may use one connection from its pool, to make the insert, and another connection for the last_insert_id query. The problem is that the last_insert_id query is tied to the connection that actually made the insert. In order to force Spring JdbcTemplate to use the same connection all along a portion of code is to start a Transaction before the insert, and commit it after the last_insert_id query. See this post for simply managing transaction. You might say "I always use transaction"... well, in my case, transaction were not needed until this last_insert_id issue that returned 0.