[Building Sakai] for people running mysql 5.1.33 - 48

[Hedrick Charles]

In the course of tracking down KNL-815, I found that there is a known bug in mysql that can cause transactions to deadlock. Most mysql experts see a certain level of random deadlocks as normal (hard as I find that to believe), but it can become an issue. Deadlocks cause things to pause for 50 sec. If you're lucky it's just a couple of users, but we saw situations where it seemed to affect all of Sakai.

You can check deadlocks by doing "show innodb status." Near the top is the last foreign key error and the last deadlock.

Here's the bug:

Normally you expect that a select operation won't get any locks. But it turns out that in the affected versions of mysql, if you have a select with a subselect, the tables used in the subselect are locked until the end of the transaction. But because of the way Sakai does transaction management, if you're not doing any updates, a transaction can continue pretty much forever, spanning the return of a connection to the dbcp pool and its reallocation. 

(Sakai runs with autocommit=off, which means that you're always in a transaction, and transactions only end with a commit or rollback. But Sakai often does commit or rollback only after an update operation. It may not do them after a sequence of selects, because selects aren't expected to do anything that needs to be committed. Incidentally, I recommend doing a commit or rollback when returning a connection to the pool, just in case.)

This problem can be fixed in several different ways:

1) Update to at least 5.1.48
2) change all select statements that use subselects to use joins instead. Historically, mysql has had problems with subselects. I never use them.
3) set binlog_format=ROW

Rutgers is changing binlog_format to ROW for the moment, since I don't like to change mysql during a semester.

There are issues with binlog_format='ROW'. Probably not in normal operation, but you may want to change it back to MIXED when running the database update scripts for version updates. The problem is that it causes the actual changed data to be sent rather than the statements. So if you add a new column and set the value for all rows, with MIXED you just get the SQL statement. With ROW you get a separate item for each row in the table setting the new value.

I'm not saying that this is going to be an issue for everyone, but if you're seeing unexplained hangs in Sakai, it's an issue to look at. (The other cause we've seen is long pauses for garbage collection. Also, Lesson Builder could trigger another type of mysql deadlock. I added a patch to Lesson Builder 1.3 and 1.4 recently. I don't think this is actually a Lesson Builder bug, but we can do things to make innodb deadlocks less likely to happen.)