MySQL Bugs: #32633: Can not create any routine if SQL_MODE=no_engine

Bug #32633	Can not create any routine if SQL_MODE=no_engine_substitution
Submitted:	22 Nov 2007 19:43	Modified:	11 Feb 2008 20:32
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.x; 6.0.4	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	regression

Description:
One can not create a stored routine (function or procedure) if sql_mode contains some of these:

NO_ENGINE_SUBSTITUTION
PAD_CHAR_TO_FULL_LENGTH

The event is successfully created, but the sql mode is lost.

How to repeat:
mysql> set sql_mode=no_engine_substitution;
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p2() begin end;
ERROR 1607 (HY000): Cannot create stored routine `p2`. Check warnings
mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1265 | Data truncated for column 'sql_mode' at row 7     | 
| Error   | 1607 | Cannot create stored routine `p2`. Check warnings | 
+---------+------+---------------------------------------------------+
2 rows in set (0.00 sec)

mysql> create event e1 on schedule every 15 minute starts now() on completion not preserve do begin end;
Query OK, 0 rows affected (0.00 sec)

mysql> show create event e1;
+-------+----------+-----------+---------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Event | sql_mode | time_zone | Create Event                                                                                                              | character_set_client | collation_connection | Database Collation |
+-------+----------+-----------+---------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| e1    |          | SYSTEM    | CREATE EVENT `e1` ON SCHEDULE EVERY 15 MINUTE STARTS '2007-11-22 22:40:09' ON COMPLETION NOT PRESERVE ENABLE DO begin end | latin1               | latin1_swedish_ci    | latin1_swedish_ci  | 
+-------+----------+-----------+---------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> select sql_mode from mysql.event;
+----------+
| sql_mode |
+----------+
|          | 
+----------+

Suggested fix:
Store the sql mode as string, not as a set.
Parse it on loading.

Looks like this is a bug in versions 5.1.x. I can not repeat it (with procedure) on 5.0.44, for example.

It is easily repeatable on 5.1.21 and other 5.1.x versions, though.

Valeriy, the bug is repeatable in 5.0, but there the sql_mode value is silently lost, even though the routine is created.
You can verify it by selecting from mysql.proc or information_schema.
So on 5.0 it's a dump/restore problem.

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/41858

ChangeSet@1.2521, 2008-02-07 08:47:39-02:00, davi@mysql.com +13 -0
  Bug#32633 Can not create any routine if SQL_MODE=no_engine_substitution
  
  The problem is that one can not create a stored routine if sql_mode
  contains NO_ENGINE_SUBSTITUTION or PAD_CHAR_TO_FULL_LENGTH. Also when
  a event is created, the mode is silently lost if sql_mode contains one
  of the aforementioned.  This was happening because the table definitions
  which stored sql_mode values weren't being updated to accept new values
  of sql_mode.
  
  The solution is to update, in a backwards compatible manner, the various
  table definitions (columns) that store the sql_mode value to take into
  account the new possible values. One incompatible change is that if a event
  that is being created can't be stored to the mysql.event table, an error
  will be raised.
  
  The tests case also ensure that new SQL modes will be added to the mysql.proc
  and mysql.event tables, otherwise the tests will fail.

Pushed in 5.1-runtime

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Noted in 5.1.24, 6.0.5 changelogs.

Tables in the mysql database that stored the current sql_mode value
as part of stored program definitions were not updated with newer
mode values (NO_ENGINE_SUBSTITUTION, PAD_CHAR_TO_FULL_LENGTH). This
causes various problems defining stored programs if those modes were
included in the current sql_mode value.

Bug#34613 was marked as a duplicate of this one.

Bug#34794 was marked as duplicate of this one.

Bug #35064 was marked as a duplicate of this one.

Also documented for 5.1.23-ndb-6.2.14.

Also documented for 5.1.23-ndb-6.3.11.

I tried changing SQL_MODE but CREATE PROCEDURE was still throwing error 4\1607

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER'

Bug #35824 appears to be a result of that fix.

Bug#35857 has been marked as a duplicate of this bug.

when will 5.1.24-rc be release?

Thanks, just dowloaded 5.1.24 with the fix.