Description:
This bug is related to #36540 (root cause and symptoms are identical)
The only documentation I could find on length of server_id was in the sample my.cnf files, where the limit status 2^32. 2^32 is 4294967296. However in testing, logging to a table only occurs if the server_id is half that. Otherwise your get the following logging to the error log, e.g.
080508 14:24:32 [ERROR] Failed to write to mysql.general_log:
080508 14:24:37 [ERROR] Failed to write to mysql.general_log:
080508 14:24:44 [ERROR] Failed to write to mysql.general_log:
This is a problem for people who want their server_id to be set as
inet_aton('<ip_address>') to ensure they are always unique in large data centers.
I narrowed the problem down to the server_id column on the general_log and slow_log tables. If you make those fields unsigned, you can set your server_id to inet_aton('255.255.255.255') and logging works correctly.
How to repeat:
mysql> truncate table general_log;
Query OK, 0 rows affected (0.00 sec)
mysql> set global server_id = 2147483648;
Query OK, 0 rows affected (0.00 sec)
mysql> use mysql;
Database changed
mysql> select * from general_log;
Empty set (0.00 sec)
mysql> set global server_id = 2147483647;
Query OK, 0 rows affected (0.00 sec)
mysql> use mysql;
Database changed
mysql> select * from general_log;
+---------------------+---------------------------+-----------+------------+--------------+---------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+---------------------+---------------------------+-----------+------------+--------------+---------------------------+
| 2008-05-08 14:24:50 | root[root] @ localhost [] | 82 | 2147483647 | Query | SELECT DATABASE() |
| 2008-05-08 14:24:50 | root[root] @ localhost [] | 82 | 2147483647 | Init DB | mysql |
| 2008-05-08 14:24:54 | root[root] @ localhost [] | 82 | 2147483647 | Query | select * from general_log |
+---------------------+---------------------------+-----------+------------+--------------+---------------------------+
3 rows in set (0.00 sec)
mysql> set global general_log = OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table mysql.general_log modify server_id int(11) unsigned not null;
Query OK, 27 rows affected (0.01 sec)
Records: 27 Duplicates: 0 Warnings: 0
mysql> set global server_id = inet_aton('255.255.255.255');
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table mysql.general_log;
Query OK, 0 rows affected (0.01 sec)
mysql> set global general_log = ON;
Query OK, 0 rows affected (0.00 sec)
mysql> use mysql;
Database changed
mysql> select * from mysql.general_log;
+---------------------+---------------------------+-----------+------------+--------------+---------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+---------------------+---------------------------+-----------+------------+--------------+---------------------------------+
| 2008-05-08 14:29:35 | root[root] @ localhost [] | 105 | 4294967295 | Query | SELECT DATABASE() |
| 2008-05-08 14:29:35 | root[root] @ localhost [] | 105 | 4294967295 | Init DB | mysql |
| 2008-05-08 14:29:41 | root[root] @ localhost [] | 105 | 4294967295 | Query | select * from mysql.general_log |
+---------------------+---------------------------+-----------+------------+--------------+---------------------------------+
3 rows in set (0.00 sec)
Suggested fix:
server_id column in both mysql.general_log and mysql.slow_log should be made INT(11) UNSIGNED
Description: This bug is related to #36540 (root cause and symptoms are identical) The only documentation I could find on length of server_id was in the sample my.cnf files, where the limit status 2^32. 2^32 is 4294967296. However in testing, logging to a table only occurs if the server_id is half that. Otherwise your get the following logging to the error log, e.g. 080508 14:24:32 [ERROR] Failed to write to mysql.general_log: 080508 14:24:37 [ERROR] Failed to write to mysql.general_log: 080508 14:24:44 [ERROR] Failed to write to mysql.general_log: This is a problem for people who want their server_id to be set as inet_aton('<ip_address>') to ensure they are always unique in large data centers. I narrowed the problem down to the server_id column on the general_log and slow_log tables. If you make those fields unsigned, you can set your server_id to inet_aton('255.255.255.255') and logging works correctly. How to repeat: mysql> truncate table general_log; Query OK, 0 rows affected (0.00 sec) mysql> set global server_id = 2147483648; Query OK, 0 rows affected (0.00 sec) mysql> use mysql; Database changed mysql> select * from general_log; Empty set (0.00 sec) mysql> set global server_id = 2147483647; Query OK, 0 rows affected (0.00 sec) mysql> use mysql; Database changed mysql> select * from general_log; +---------------------+---------------------------+-----------+------------+--------------+---------------------------+ | event_time | user_host | thread_id | server_id | command_type | argument | +---------------------+---------------------------+-----------+------------+--------------+---------------------------+ | 2008-05-08 14:24:50 | root[root] @ localhost [] | 82 | 2147483647 | Query | SELECT DATABASE() | | 2008-05-08 14:24:50 | root[root] @ localhost [] | 82 | 2147483647 | Init DB | mysql | | 2008-05-08 14:24:54 | root[root] @ localhost [] | 82 | 2147483647 | Query | select * from general_log | +---------------------+---------------------------+-----------+------------+--------------+---------------------------+ 3 rows in set (0.00 sec) mysql> set global general_log = OFF; Query OK, 0 rows affected (0.00 sec) mysql> alter table mysql.general_log modify server_id int(11) unsigned not null; Query OK, 27 rows affected (0.01 sec) Records: 27 Duplicates: 0 Warnings: 0 mysql> set global server_id = inet_aton('255.255.255.255'); Query OK, 0 rows affected (0.00 sec) mysql> truncate table mysql.general_log; Query OK, 0 rows affected (0.01 sec) mysql> set global general_log = ON; Query OK, 0 rows affected (0.00 sec) mysql> use mysql; Database changed mysql> select * from mysql.general_log; +---------------------+---------------------------+-----------+------------+--------------+---------------------------------+ | event_time | user_host | thread_id | server_id | command_type | argument | +---------------------+---------------------------+-----------+------------+--------------+---------------------------------+ | 2008-05-08 14:29:35 | root[root] @ localhost [] | 105 | 4294967295 | Query | SELECT DATABASE() | | 2008-05-08 14:29:35 | root[root] @ localhost [] | 105 | 4294967295 | Init DB | mysql | | 2008-05-08 14:29:41 | root[root] @ localhost [] | 105 | 4294967295 | Query | select * from mysql.general_log | +---------------------+---------------------------+-----------+------------+--------------+---------------------------------+ 3 rows in set (0.00 sec) Suggested fix: server_id column in both mysql.general_log and mysql.slow_log should be made INT(11) UNSIGNED