Batch Update with JPA

A customer of mine has a highly scalable system, with high database load. To improve performance, we’ve decided to use Batch update. Alas – the application uses JPA, so how do we do it?

SessionFactoryImplementor sfi = SessionFactoryImplementor)entityManager.unwrap(Session.class).getSessionFactory();
//Retrieve DB connection
connect = sfi.getConnectionProvider().getConnection();
PreparedStatement ps = connect.prepareStatement("INSERT INTO temp_table values(?,?)");
for (Data p : insertCommands) {
	ps.setInt(1, p.getId());
	if (p.isWorking() != null) {
		ps.setInt(2, p.isWorking() ? 1 : 0);
	} else {
		ps.setNull(2, Types.INTEGER);
	}
	ps.addBatch();
}
ps.executeBatch();


Some points to remember:

  1. Do not close the connection!!! When closing the connection, it returns to the connection pool, without commit or rollback. You’ll get some exceptions in your code, but mainly – you’re open to database locks and dead-locks, as the connection keeps all the locks that were done in the batch update. And since other threads will reuse the connection – it will never close.
  2. Do not use Statement.addBatch. Use PreparedStatement.addBatch. When I checked both solutions, the Statement one took anywhere between 30s-60s. The PreparedStatement version took <300ms...

Call to undefined function mysql_pconnect()

Wow. This took ages.
A customer of mine has developed a PHP application on his windows machine (yeah yeah I know). I tried installing it on my Linux laptop – and all I got was a white page.
Well, loving the challenge, I started putting in debu prints in all the PHP files. Turns out the customer uses CodeIgniter, so I started with core/CodeIgniter.php, moved to core/Loader.php, core/db/DB.php and ended up with the mysql_driver.php.
Turns out I was crashing on this line:

return @mysql_pconnect($this->hostname, $this->username, $this->password);

The error message (after removing the @ of course) was
Call to undefined function mysql_pconnect()
So lame. I forgot to run sudo apt-get install php5-mysql. 2 hours of my life wasted 🙁

MySQL replication on Windows

I feel like a real MySQL replication configuration wiz these days – I can probably even do it with my eyes closed.
But lately, I had the joy of configuring MySQL replication on Windows. I don’t know why, don’t know how – but all configurations in the my.ini file just didn’t work. I constantly got the server-id param not set error on the slave database.
Finally, I gave up. I opened MySQL Workbench, configured the remote administration capabilities, and walla – everything worked like a charm.