| Bug #74947 | Error while loading events from mysql.event. | ||
|---|---|---|---|
| Submitted: | 21 Nov 2014 4:51 | Modified: | 13 Oct 2016 16:30 |
| Reporter: | Yoshiaki Tajika (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 5.6.21, 5.6.23, 5.7.6 | OS: | Linux (CentOS 6.5) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Event, PAD_CHAR_TO_FULL_LENGTH, SQL_MODE | ||
[21 Nov 2014 4:51]
Yoshiaki Tajika
[21 Nov 2014 6:05]
MySQL Verification Team
Hello Tajika-San, Thank you for the report and test case. Thanks, Umesh
[21 Nov 2014 6:11]
MySQL Verification Team
// 5.6.23 scripts/mysql_install_db --basedir=/data/ushastry/server/mysql-advanced-5.6.23 --datadir=/tmp/bug --user=mysql bin/mysqld --basedir=/data/ushastry/server/mysql-advanced-5.6.23 --datadir=/tmp/bug --core --socket=/tmp/mysql.sock --port=3306 --log-error=/tmp/bug/log.err --user=mysql 2>&1 & [root@cluster-repo mysql-advanced-5.6.23]# bin/mysql -u root -p test Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.23-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. [test]> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.6.23 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.23-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) [test]> show variables like 'sql_mode'; +---------------+--------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +---------------+--------------------------------------------+ 1 row in set (0.00 sec) [test]> create table t1(c1 int primary key, c2 int); Query OK, 0 rows affected (0.02 sec) [test]> insert t1 values(1, 1); Query OK, 1 row affected (0.00 sec) [test]> create event e1 on schedule every 1 second do update t1 set c2=c2+1; Query OK, 0 rows affected (0.00 sec) [test]> show events; +------+------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +------+------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ | test | e1 | root@localhost | SYSTEM | RECURRING | NULL | 1 | SECOND | 2014-11-23 13:04:49 | NULL | ENABLED | 0 | utf8 | utf8_general_ci | latin1_swedish_ci | +------+------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec) [test]> set global sql_mode=PAD_CHAR_TO_FULL_LENGTH; Query OK, 0 rows affected (0.00 sec) [test]> \q Bye [root@cluster-repo mysql-advanced-5.6.23]# bin/mysql -u root -p test Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.23-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. [test]> show variables like 'sql_mode'; +---------------+-------------------------+ | Variable_name | Value | +---------------+-------------------------+ | sql_mode | PAD_CHAR_TO_FULL_LENGTH | +---------------+-------------------------+ 1 row in set (0.00 sec) [test]> show events; ERROR 1728 (HY000): Cannot load from mysql.event. The table is probably corrupted [test]> // Build [root@cluster-repo mysql-advanced-5.6.23]# more docs/INFO_SRC revision-id: michael.izioumtchenko@oracle.com-20141106152508-nntohvuco3v1rjjx date: 2014-11-06 16:25:08 +0100 build-date: 2014-11-06 18:00:43 +0100 revno: 6243 branch-nick: daily-5.6 MySQL source 5.6.23
[21 Nov 2014 8:32]
MySQL Verification Team
// 5.7.6 - affected [ushastry@cluster-repo mysql-advanced-5.7.6]$ bin/mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.7.6-m16-enterprise-commercial-advanced-log Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> set password=''; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.7.6 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.7.6-m16-enterprise-commercial-advanced-log | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | linux-glibc2.5 | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> show variables like 'sql_mode'; +---------------+--------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +---------------+--------------------------------------------+ 1 row in set (0.00 sec) mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> create table t1(c1 int primary key, c2 int); Query OK, 0 rows affected (0.30 sec) mysql> insert t1 values(1, 1); Query OK, 1 row affected (0.03 sec) mysql> create event e1 on schedule every 1 second do update t1 set c2=c2+1; Query OK, 0 rows affected (0.00 sec) mysql> show events; +------+------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +------+------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ | test | e1 | root@localhost | SYSTEM | RECURRING | NULL | 1 | SECOND | 2014-11-23 15:36:25 | NULL | ENABLED | 1 | utf8 | utf8_general_ci | latin1_swedish_ci | +------+------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec) mysql> set global sql_mode=PAD_CHAR_TO_FULL_LENGTH; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'sql_mode'; +---------------+--------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +---------------+--------------------------------------------+ 1 row in set (0.00 sec) mysql> \q Bye [ushastry@cluster-repo mysql-advanced-5.7.6]$ bin/mysql -u root -p Enter password: mysql> show variables like 'sql_mode'; +---------------+-------------------------+ | Variable_name | Value | +---------------+-------------------------+ | sql_mode | PAD_CHAR_TO_FULL_LENGTH | +---------------+-------------------------+ 1 row in set (0.00 sec) mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> show events; ERROR 1728 (HY000): Cannot load from mysql.event. The table is probably corrupted mysql>
[13 Oct 2016 16:30]
Paul DuBois
Posted by developer: Noted in 8.0.0 changelog. Event loading from the mysql.event system table could fail if the PAD_CHAR_TO_FULL_LENGTH SQL mode was enabled.
