[MDEV-9524] Cannot load from mysql.event when sql_mode is set to PAD_CHAR_TO_FULL_LENGTH Created: 2016-02-05  Updated: 2016-06-21  Resolved: 2016-06-21

Status: Closed
Project: MariaDB Server
Component/s: Events
Affects Version/s: 10.0.23, 10.1.11
Fix Version/s: 10.0.26, 10.1.15

Type: Bug Priority: Major
Reporter: Nirbhay Choubey (Inactive) Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: SUSE, upstream

Issue Links:
Relates
relates to MDEV-9830 Plugins unable to load with sql_mode ... Closed
Sprint: 10.0.26

 Description   

Cannot load from mysql.event when sql_mode is set to PAD_CHAR_TO_FULL_LENGTH

mysql Ver 15.1 Distrib 10.0.22-MariaDB, for Linux (x86_64) using readline 5.1

Steps to reproduce:

MariaDB [test]> create table test1 (col1 INT);
MariaDB [test]> insert into test1 values(100);
MariaDB [test]> select * from test1;
+------+
| col1 |
+------+
|  100 |
+------+
1 row in set (0.00 sec)
 
MariaDB [test]> create event event1 ON SCHEDULE every 5 SECOND do insert into test.test1 (col1) values(200);
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> show events;
ERROR 1728 (HY000): Cannot load from mysql.event. The table is probably corrupted
MariaDB [test]> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH
1 row in set (0.00 sec)
 
MariaDB [test]> show events;
ERROR 1728 (HY000): Cannot load from mysql.event. The table is probably corrupted
 
MariaDB [test]> set sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [test]> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: STRICT_TRANS_TABLES
1 row in set (0.00 sec)
 
MariaDB [test]> show events \G
*************************** 1. row ***************************
                  Db: test
                Name: event1
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 5
      Interval field: SECOND
              Starts: 2016-01-27 17:52:40
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

While searching I have found https://bugs.mysql.com/bug.php?id=74947
looks like the same problem.



 Comments   
Comment by Nirbhay Choubey (Inactive) [ 2016-05-09 ]

elenst Do you know who should be the right developer for this issue?

Comment by Elena Stepanova [ 2016-05-09 ]

bar, could you please look at it? Feel free to reassign if needed.

Comment by Alexander Barkov [ 2016-06-20 ]

A smaller test case demonstrating the same problem:

DROP TABLE IF EXISTS t1;
DROP EVENT IF EXISTS ev1;
CREATE TABLE t1 (a INT);
CREATE EVENT ev1 ON SCHEDULE EVERY 5 SECOND DO DELETE FROM t1;
SHOW EVENTS;
SET sql_mode=PAD_CHAR_TO_FULL_LENGTH;
SHOW EVENTS;

The first SHOW EVENTS works fine, the second one fails.

Comment by Alexander Barkov [ 2016-06-21 ]

The same problem is also repeatable with the "HELP" command.
HELP SELECT normally returns:

Name: 'SELECT'
Description:
Syntax:
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
...

If I set sql_mode to PAD_CHAR_TO_FULL_LENGTH, it stops working:

SET sql_mode=PAD_CHAR_TO_FULL_LENGTH;
HELP SELECT;

Nothing found
Please try to run 'help contents' for a list of all accessible topics

Generated at Thu Feb 08 07:35:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.