MySQL Bugs: #36540: CREATE EVENT and ALTER EVENT statements fail with large server

Bug #36540	CREATE EVENT and ALTER EVENT statements fail with large server_id
Submitted:	6 May 2008 16:07	Modified:	18 Mar 2009 14:28
Reporter:	Gregory Haase	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.1.24, 5.1, 6.0 BK	OS:	Linux (x86_64)
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	Event, server_id

Description:
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, I was only able to store an event if the server_id was 1/2 that.

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 to the originator column on the event table. If you make that unsigned, you can set your server_id to inet_aton('255.255.255.255') and still store events successfully.

How to repeat:
Log onto mysql with the super privilege, change the server_id, and try to create a new event:

mysql> set global server_id = 2147483648;
mysql>  create event `test`.`test_1` on schedule every 1 day do set @a = 1;
ERROR 1538 (HY000): Failed to store event body. Error code 1 from storage engine

reduce the server_id by one and try again:

mysql> set global server_id = 2147483647;
mysql> create event `test`.`test_1` on schedule every 1 day do set @a = 1;
Query OK, 0 rows affected (0.00 sec)

change originator column to unsigned
mysql> alter table mysql.event modify originator int(10) unsigned NOT NULL;

Try the first test again (note change of event name to avoid duplicates)
mysql> set global server_id = 2147483648;
mysql> create event `test`.`test_2` on schedule every 1 day do set @a = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global server_id = inet_aton('255.255.255.255');
mysql> create event `test`.`test_3` on schedule every 1 day do set @a = 1;
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
change the originator column in event table to be unsigned

mysql.event has originator_id being INT(10) , which is signed

Thank you for the report.

Verified as described.

correct OS comment

The bug is due to the server_id field in the mysql.event table being
declared as signed INT instead of UNSIGNED INT.
According to our documentation, server ID can hold values from 0 to 232.
This means that the data type in the event table should be INT UNSIGNED.

In the specific case reported by the user, he is assigning as server ID the
value of inet_aton(IP_ADDRESS), as recommended by our instructors, when he
attended our training classes. So, this bug affects the usability of events
applied to server monitoring.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66551

2801 Davi Arnaut	2009-02-16
      Bug#36540: CREATE EVENT and ALTER EVENT statements fail with large server_id
      
      The problem is that creating a event could fail if the value of
      the variable server_id didn't fit in the originator column of
      the event system table. The cause is two-fold, it was possible
      to set server_id to a value outside the documented range (from
      1 to 2^32-1) and the originator column of the event table didn't
      have enough room for values in this range.
      
      The log tables (general_log and slow_log) also don't have a proper
      column type to store the server_id and having a large server_id
      value could prevent queries from being logged.
      
      The solution is to ensure that all system tables that store the
      server_id value have a proper column type (int unsigned) and that
      the variable can't be set to a value that is not within the range.
      modified:
        mysql-test/r/events_bugs.result
        mysql-test/r/log_tables.result
        mysql-test/r/system_mysql_db.result
        mysql-test/r/variables.result
        mysql-test/t/events_bugs.test
        mysql-test/t/log_tables.test
        mysql-test/t/variables.test
        scripts/mysql_system_tables.sql
        scripts/mysql_system_tables_fix.sql
        sql/mysqld.cc

Discussed with Davi some refinement of regression tests, and changes
in mtr due to one tries setting server_id to out-of-range zero.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67198

2809 Davi Arnaut	2009-02-23
      Bug#36540: CREATE EVENT and ALTER EVENT statements fail with large server_id
      
      The problem is that creating a event could fail if the value of
      the variable server_id didn't fit in the originator column of
      the event system table. The cause is two-fold: it was possible
      to set server_id to a value outside the documented range (from
      1 to 2^32-1) and the originator column of the event table didn't
      have enough room for values in this range.
      
      The log tables (general_log and slow_log) also don't have a proper
      column type to store the server_id and having a large server_id
      value could prevent queries from being logged.
      
      The solution is to ensure that all system tables that store the
      server_id value have a proper column type (int unsigned) and that
      the variable can't be set to a value that is not within the range.
      The default value of the server_id variable is now 1.
     @ mysql-test/r/events_bugs.result
        Add test case result for Bug#36540
     @ mysql-test/r/log_tables.result
        Update column type.
     @ mysql-test/r/system_mysql_db.result
        Update column type.
     @ mysql-test/r/variables.result
        Add test case result for server_id value range.
     @ mysql-test/suite/sys_vars/inc/server_id_basic.inc
        New defautl value for server_id.
     @ mysql-test/suite/sys_vars/r/server_id_basic_64.result
        Update test case results.
     @ mysql-test/t/events_bugs.test
        Add test case for Bug#36540
     @ mysql-test/t/log_tables.test
        Fix column type.
     @ mysql-test/t/variables.test
        Add test case for server_id value range.
     @ scripts/mysql_system_tables.sql
        Columns that store the server_id value must be of type INT UNSIGNED,
        fix event (originator), general_log and slow_log (server_id) tables
        in accordance.
     @ scripts/mysql_system_tables_fix.sql
        Columns that store the server_id value must be of type INT UNSIGNED,
        fix event (originator), general_log and slow_log (server_id) tables
        in accordance.
     @ sql/mysqld.cc
        Set default, min and max values for the server_id variable.
        Unfortunately we can't easily change server_id variable type
        from ulong to uint32 because of the sys_var classes.

Discussed with Davi some refinement of regression tests, and changes
in mtr due to one tries setting server_id to out-of-range zero.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67291

2811 Davi Arnaut	2009-02-24
      Bug#36540: CREATE EVENT and ALTER EVENT statements fail with large server_id
      
      The problem is that creating a event could fail if the value of
      the variable server_id didn't fit in the originator column of
      the event system table. The cause is two-fold: it was possible
      to set server_id to a value outside the documented range (from
      1 to 2^32-1) and the originator column of the event table didn't
      have enough room for values in this range.
      
      The log tables (general_log and slow_log) also don't have a proper
      column type to store the server_id and having a large server_id
      value could prevent queries from being logged.
      
      The solution is to ensure that all system tables that store the
      server_id value have a proper column type (int unsigned) and that
      the variable can't be set to a value that is not within the range.
      The default value of the server_id variable is now 1.
     @ mysql-test/r/events_bugs.result
        Add test case result for Bug#36540
     @ mysql-test/r/log_tables.result
        Update column type.
     @ mysql-test/r/system_mysql_db.result
        Update column type.
     @ mysql-test/r/variables.result
        Add test case result for server_id value range.
     @ mysql-test/suite/sys_vars/inc/server_id_basic.inc
        New defautl value for server_id.
     @ mysql-test/suite/sys_vars/r/server_id_basic_32.result
        Update test case results.
     @ mysql-test/suite/sys_vars/r/server_id_basic_64.result
        Update test case results.
     @ mysql-test/t/events_bugs.test
        Add test case for Bug#36540
     @ mysql-test/t/log_tables.test
        Fix column type.
     @ mysql-test/t/variables.test
        Add test case for server_id value range.
     @ scripts/mysql_system_tables.sql
        Columns that store the server_id value must be of type INT UNSIGNED,
        fix event (originator), general_log and slow_log (server_id) tables
        in accordance.
     @ scripts/mysql_system_tables_fix.sql
        Columns that store the server_id value must be of type INT UNSIGNED,
        fix event (originator), general_log and slow_log (server_id) tables
        in accordance.
     @ sql/mysqld.cc
        Set default, min and max values for the server_id variable.
        Unfortunately we can't easily change server_id variable type
        from ulong to uint32 because of the sys_var classes.

Discussed with Davi some refinement of regression tests, and changes
in mtr due to one tries setting server_id to out-of-range zero.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67443

2818 Davi Arnaut	2009-02-25
      Bug#36540: CREATE EVENT and ALTER EVENT statements fail with large server_id
      
      The problem is that creating a event could fail if the value of
      the variable server_id didn't fit in the originator column of
      the event system table. The cause is two-fold: it was possible
      to set server_id to a value outside the documented range (from
      1 to 2^32-1) and the originator column of the event table didn't
      have enough room for values in this range.
      
      The log tables (general_log and slow_log) also don't have a proper
      column type to store the server_id and having a large server_id
      value could prevent queries from being logged.
      
      The solution is to ensure that all system tables that store the
      server_id value have a proper column type (int unsigned) and that
      the variable can't be set to a value that is not within the range.
     @ mysql-test/r/events_bugs.result
        Add test case result for Bug#36540
     @ mysql-test/r/log_tables.result
        Update column type.
     @ mysql-test/r/system_mysql_db.result
        Update column type.
     @ mysql-test/r/variables.result
        Add test case result for server_id value range.
     @ mysql-test/suite/sys_vars/r/server_id_basic_64.result
        Update test case results.
     @ mysql-test/t/events_bugs.test
        Add test case for Bug#36540
     @ mysql-test/t/log_tables.test
        Fix column type.
     @ mysql-test/t/variables.test
        Add test case for server_id value range.
     @ scripts/mysql_system_tables.sql
        Columns that store the server_id value must be of type INT UNSIGNED,
                fix event (originator), general_log and slow_log (server_id) tables
                in accordance.
     @ scripts/mysql_system_tables_fix.sql
        Columns that store the server_id value must be of type INT UNSIGNED,
        fix event (originator), general_log and slow_log (server_id) tables
        in accordance.
     @ sql/mysqld.cc
        Set min and max values for the server_id variable.
        Unfortunately we can't easily change server_id variable type
        from ulong to uint32 because of the sys_var classes.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67838

2827 Davi Arnaut	2009-02-27
      Bug#36540: CREATE EVENT and ALTER EVENT statements fail with large server_id
      
      The problem is that creating a event could fail if the value of
      the variable server_id didn't fit in the originator column of
      the event system table. The cause is two-fold: it was possible
      to set server_id to a value outside the documented range (from
      0 to 2^32-1) and the originator column of the event table didn't
      have enough room for values in this range.
      
      The log tables (general_log and slow_log) also don't have a proper
      column type to store the server_id and having a large server_id
      value could prevent queries from being logged.
      
      The solution is to ensure that all system tables that store the
      server_id value have a proper column type (int unsigned) and that
      the variable can't be set to a value that is not within the range.
     @ mysql-test/r/events_bugs.result
        Add test case result for Bug#36540
     @ mysql-test/r/log_tables.result
        Update column type.
     @ mysql-test/r/system_mysql_db.result
        Update column type.
     @ mysql-test/r/variables.result
        Add test case result for server_id value range.
     @ mysql-test/suite/sys_vars/r/server_id_basic_64.result
        Update test case results.
     @ mysql-test/t/events_bugs.test
        Add test case for Bug#36540
     @ mysql-test/t/log_tables.test
        Fix column type.
     @ mysql-test/t/variables.test
        Add test case for server_id value range.
     @ scripts/mysql_system_tables.sql
        Columns that store the server_id value must be of type INT UNSIGNED,
        fix event (originator), general_log and slow_log (server_id) tables
        in accordance.
     @ scripts/mysql_system_tables_fix.sql
        Columns that store the server_id value must be of type INT UNSIGNED,
        fix event (originator), general_log and slow_log (server_id) tables
        in accordance.
     @ sql/mysqld.cc
        Set min and max values for the server_id variable.
        Unfortunately we can't easily change server_id variable type
        from ulong to uint32 because of the sys_var classes.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68951

2832 Davi Arnaut	2009-03-11
      Bug#36540: CREATE EVENT and ALTER EVENT statements fail with large server_id
      
      The problem is that creating a event could fail if the value of
      the variable server_id didn't fit in the originator column of
      the event system table. The cause is two-fold: it was possible
      to set server_id to a value outside the documented range (from
      0 to 2^32-1) and the originator column of the event table didn't
      have enough room for values in this range.
      
      The log tables (general_log and slow_log) also don't have a proper
      column type to store the server_id and having a large server_id
      value could prevent queries from being logged.
      
      The solution is to ensure that all system tables that store the
      server_id value have a proper column type (int unsigned) and that
      the variable can't be set to a value that is not within the range.
     @ mysql-test/r/events_bugs.result
        Add test case result for Bug#36540
     @ mysql-test/r/log_tables.result
        Update column type.
     @ mysql-test/r/system_mysql_db.result
        Update column type.
     @ mysql-test/r/variables.result
        Add test case result for server_id value range.
     @ mysql-test/suite/sys_vars/r/server_id_basic_64.result
        Update test case results.
     @ mysql-test/t/events_bugs.test
        Add test case for Bug#36540
     @ mysql-test/t/log_tables.test
        Fix column type.
     @ mysql-test/t/variables.test
        Add test case for server_id value range.
     @ scripts/mysql_system_tables.sql
        Columns that store the server_id value must be of type INT UNSIGNED,
        fix event (originator), general_log and slow_log (server_id) tables
        in accordance.
     @ scripts/mysql_system_tables_fix.sql
        Columns that store the server_id value must be of type INT UNSIGNED,
        fix event (originator), general_log and slow_log (server_id) tables
        in accordance.
     @ sql/mysqld.cc
        Set min and max values for the server_id variable.
        Unfortunately we can't easily change server_id variable type
        from ulong to uint32 because of the sys_var classes.

Queued to 5.1-bugteam

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/69108

2836 Georgi Kodinov	2009-03-13
      addendum to bug #36540 : fix the funcs_1 test suite.

Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:sergey.glukhov@sun.com-20090313093111-hnu8i5yzs6bc7h3g) (merge vers: 5.1.33) (pib:6)

Noted in 5.1.33 changelog.

The event, general_log, and slow_log tables in the mysql database
store server_id values, but did not use an UNSIGNED column and thus 
were not able to store the full range of ID values.

Setting report to NDI pending push into 6.0.x.

Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:sergey.glukhov@sun.com-20090313095240-79oxifkzaz6ee2xg) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 6.0.11 changelog.

Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)

Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)

Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)