Mysql IF_IDLE patch

I isolated Google’s IF_IDLE feature from SqlChanges; part of the Google V2 patch.
I diffed this IF_IDLE patch for mysql-5.0.68-percona-highperf but it’ll patch mysql-5.0.67 as well.

This simple and brilliant change allows us to
attempt to kill idle mysql processes while avoiding race conditions.

KILL IF_IDLE <id>

mysql> SHOW PROCESSLIST;
+----+-------------+-----------+-----------+---------+------+----------------------------------+
| Id | User        | Host      | db        | Command | Time | State                            | Info
+----+-------------+-----------+-----------+---------+------+----------------------------------+
|  2 | system user |           | NULL      | Connect | 2192 | Waiting for master to send event | NULL
| 13 | root        | localhost | NULL      | Query   |    0 | NULL                             | show processlist
| 30 | root        | localhost | NULL      | Sleep   |    2 |                                  | NULL
+----+-------------+-----------+-----------+---------+------+----------------------------------+
4 rows in set (0.00 sec)
mysql> KILL IF_IDLE 30;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW PROCESSLIST;
+----+-------------+-----------+-----------+---------+------+----------------------------------+
| Id | User        | Host      | db        | Command | Time | State                            | Info
+----+-------------+-----------+-----------+---------+------+----------------------------------+
|  2 | system user |           | NULL      | Connect | 2357 | Waiting for master to send event | NULL
| 13 | root        | localhost | NULL      | Query   |    0 | NULL                             | show processlist
+----+-------------+-----------+-----------+---------+------+----------------------------------+
3 rows in set (0.00 sec)

Innodb Freeze patch

We needed a Mysql Innodb Freeze mechanism for Spinn3r’s database operations, so we decided to isolate Google’s Innodb Freeze feature from Google’s V2 patch. The Innodb Freeze can be toggled on and off like so :

set global innodb_disallow_writes=ON
set global innodb_disallow_writes=OFF

Isolating this patch was easy since its a very small code change.
I simply patched Mysql with the Google V2 patch.
Then I started up Cscope and searched for the string: “innodb_disallow_writes”
From there I was able to trace the various C/C++ symbols; finding all the code for this feature…
I then edited an unpatched Mysql; manually pasting in the Innodb Freeze code changes.
Finally I diffed this manually edited Mysql against the unpatched Mysql producing
this Innodb Freeze patch for Mysql 5.0.37; it’s 323 lines small and only modifies 7 files of the Mysql source.

I have re-targeted the patch for Mysql 5.0.67 which will also patch Percona’s High Performance Mysql 5.0.68: Innodb Freeze patch for Mysql 5.0.67

I’ve briefly tested the patch and verified via md5sum that after setting global innodb_disallow_writes to OFF; data on disk does not change and all writes via the Innodb storage engine are blocked. However if you have set innodb_flush_log_at_trx_commit=0 in the my.cnf then updating an existing row will succeed. However the change will only exist in memory and will not be written to disk until you unfreeze (set global innodb_disallow_writes=OFF).

This is a small step towards Spinn3r’s upgrade to Mysql 5.0.x. I’ll probably end up extracting another feature from the Google V2 patch; and then eventually re-target these for Percona’s High Performance patch Mysql 5.0.68.