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

[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> set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_KEY_ERROR';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------+
| Variable_name | Value                                                                       |
+---------------+-----------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_KEY_ERROR |
+---------------+-----------------------------------------------------------------------------+
1 row in set (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 //
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)

Expected behavior

No response

How To Reproduce

No response

Environment

./mysqld Ver 5.7.36-StoneDB-v1.0.3 for Linux on x86_64 (build-)
build information as follow:
Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
Branch name: stonedb-5.7-dev
Last commit ID: 5c7d21a
Last commit time: Date: Mon Mar 13 17:00:17 2023 +0800
Build time: Date: Tue Mar 14 08:58:54 CST 2023

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

• Yes, I will!

[konghaiya]

This question looks like:

• bug: Stored procedures cannot be created if the value of sql_mode includes variable mandatory_tianmu. #1065
  I will solve it.