Tuesday, March 24, 2009

Another usability bug bites the dust

In MySQL 5.1.33 there is a fix for an apparently innocuous bug.
Bug #36540 CREATE EVENT and ALTER EVENT statements fail with large server_id.
This is a usability bug, that makes the DBA life unnecessarily hard. The reason for having a large server_id is because a DBA might want to use the IP address as server ID, to make sure that there are unique IDs, and to have an easy way of identifying the server through the IP.
All is well until you mix the server_id assignment with event creation:

select version();
+-----------+
| version() |
+-----------+
| 5.1.32 |
+-----------+
1 row in set (0.00 sec)

set global server_id =inet_aton('192.168.2.55');
Query OK, 0 rows affected (0.00 sec)

select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 3232236087 |
+-------------+
1 row in set (0.00 sec)

create event e1 on schedule at now() + interval 60 second do set @a = 1;
ERROR 1538 (HY000): Failed to store event body. Error code 1 from storage engine.
Oops! the event table has a problem. The "originator" column is designed as a signed INT (2^31 -1), while the server_ID can take values up to 2^32 - 1.
In the latest version, soon to hit the market, the problem is solved.

select version();
+-----------+
| version() |
+-----------+
| 5.1.33 |
+-----------+
1 row in set (0.01 sec)

set global server_id =inet_aton('192.168.2.55');
Query OK, 0 rows affected (0.00 sec)

select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 3232236087 |
+-------------+
1 row in set (0.00 sec)

create event e1 on schedule at now() + interval 60 second do set @a = 1;
Query OK, 0 rows affected (0.00 sec)

No comments: