bug: Stored procedures cannot be created if the value of sql_mode includes variable mandatory_tianmu.

[haitaoguan]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

mysql> show variables like '%mode%';
+--------------------------+----------------------------------------------------------------+
| Variable_name            | Value                                                          |
+--------------------------+----------------------------------------------------------------+
| block_encryption_mode    | aes-128-ecb                                                    |
| gtid_mode                | OFF                                                            |
| innodb_autoinc_lock_mode | 1                                                              |
| innodb_strict_mode       | ON                                                             |
| offline_mode             | OFF                                                            |
| pseudo_slave_mode        | OFF                                                            |
| rbr_exec_mode            | STRICT                                                         |
| slave_exec_mode          | STRICT                                                         |
| sql_mode                 | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------+----------------------------------------------------------------+
9 rows in set (0.00 sec)

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,MANDATORY_TIANMU';
Query OK, 0 rows affected, 1 warning (0.00 sec)

msql> exit;

mysql>     create PROCEDURE add_user(in num INT)
    ->     BEGIN
    ->         DECLARE rowid INT DEFAULT 0;
    ->         DECLARE firstname CHAR(1);
    ->         DECLARE name1 CHAR(1);
    ->         DECLARE name2 CHAR(1);
    ->         DECLARE lastname VARCHAR(3) DEFAULT '';
    ->         DECLARE sex CHAR(1);
    ->         DECLARE score CHAR(2);
    ->         WHILE rowid < num DO
    ->         SET firstname = SUBSTRING('赵钱孙李周吴郑王林杨柳刘孙陈江阮侯邹高彭徐',FLOOR(1+21*RAND()),1);
    ->         SET name1 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1);
    ->         SET name2 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1);
    ->         SET sex=FLOOR(0 + (RAND() * 2));
    ->         SET score= FLOOR(40 + (RAND() *60));
    ->         SET rowid = rowid + 1;
    ->         IF ROUND(RAND())=0 THEN
    ->         SET lastname =name1;
    ->         END IF;
    ->         IF ROUND(RAND())=1 THEN
    ->         SET lastname = CONCAT(name1,name2);
    ->         END IF;
    ->         insert INTO t_test(first_name,last_name,sex,score,copy_id) VALUES (firstname,lastname,sex,score,rowid);
    ->         END WHILE;
    ->     END //
ERROR 1607 (HY000): Cannot create stored routine `add_user`. Check warnings
mysql> DELIMITER ;
mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1265 | Data truncated for column 'sql_mode' at row 1           |
| Error   | 1607 | Cannot create stored routine `add_user`. Check warnings |
+---------+------+---------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show variables like '%mode%';
+--------------------------+---------------------------------------------------------------------------------+
| Variable_name            | Value                                                                           |
+--------------------------+---------------------------------------------------------------------------------+
| block_encryption_mode    | aes-128-ecb                                                                     |
| gtid_mode                | OFF                                                                             |
| innodb_autoinc_lock_mode | 1                                                                               |
| innodb_strict_mode       | ON                                                                              |
| offline_mode             | OFF                                                                             |
| pseudo_slave_mode        | OFF                                                                             |
| rbr_exec_mode            | STRICT                                                                          |
| slave_exec_mode          | STRICT                                                                          |
| sql_mode                 | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,MANDATORY_TIANMU |
+--------------------------+---------------------------------------------------------------------------------+
9 rows in set (0.01 sec)

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER //
mysql>     create PROCEDURE add_user(in num INT)
    ->     BEGIN
    ->         DECLARE rowid INT DEFAULT 0;
    ->         DECLARE firstname CHAR(1);
    ->         DECLARE name1 CHAR(1);
    ->         DECLARE name2 CHAR(1);
    ->         DECLARE lastname VARCHAR(3) DEFAULT '';
    ->         DECLARE sex CHAR(1);
    ->         DECLARE score CHAR(2);
    ->         WHILE rowid < num DO
    ->         SET firstname = SUBSTRING('赵钱孙李周吴郑王林杨柳刘孙陈江阮侯邹高彭徐',FLOOR(1+21*RAND()),1);
    ->         SET name1 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1);
    ->         SET name2 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1);
    ->         SET sex=FLOOR(0 + (RAND() * 2));
    ->         SET score= FLOOR(40 + (RAND() *60));
    ->         SET rowid = rowid + 1;
    ->         IF ROUND(RAND())=0 THEN
    ->         SET lastname =name1;
    ->         END IF;
    ->         IF ROUND(RAND())=1 THEN
    ->         SET lastname = CONCAT(name1,name2);
    ->         END IF;
    ->         insert INTO t_test(first_name,last_name,sex,score,copy_id) VALUES (firstname,lastname,sex,score,rowid);
    ->         END WHILE;
    ->     END //
DELIMITER ;Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

Expected behavior

No response

How To Reproduce

No response

Environment

./mysqld Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
Repository address: https://github.com/stoneatom/stonedb.git:HEAD
Branch name: HEAD
Last commit ID: 99b5c35
Last commit time: Date: Wed Nov 30 16:52:53 2022 +0800
Build time: Date: Mon Dec 5 03:35:58 UTC 2022

Are you interested in submitting a PR to solve the problem?

• Yes, I will!

[konghaiya]

ACK