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:
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
Triage: D2 (Serious)

[22 Nov 2007 19:43] Konstantin Osipov
One can not create a stored routine (function or procedure) if sql_mode contains some of these:


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.
[22 Nov 2007 19:48] Valeriy Kravchuk
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.
[22 Nov 2007 20:20] Konstantin Osipov
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.
[7 Feb 2008 10:47] Bugs System
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:


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
  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.
[7 Feb 2008 18:16] Davi Arnaut
Pushed in 5.1-runtime
[11 Feb 2008 16:24] Bugs System
Pushed into 5.1.24-rc
[11 Feb 2008 16:26] Bugs System
Pushed into 6.0.5-alpha
[11 Feb 2008 20:32] Paul Dubois
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
causes various problems defining stored programs if those modes were
included in the current sql_mode value.
[15 Feb 2008 23:31] Davi Arnaut
Bug#34613 was marked as a duplicate of this one.
[25 Feb 2008 10:05] Davi Arnaut
Bug#34794 was marked as duplicate of this one.
[5 Mar 2008 5:40] Valeriy Kravchuk
Bug #35064 was marked as a duplicate of this one.
[6 Mar 2008 13:42] Jon Stephens
Also documented for 5.1.23-ndb-6.2.14.
[31 Mar 2008 19:03] Jon Stephens
Also documented for 5.1.23-ndb-6.3.11.
[1 Apr 2008 23:46] Jared S
I tried changing SQL_MODE but CREATE PROCEDURE was still throwing error 4\1607

[4 Apr 2008 14:24] Philip Stoev
Bug #35824 appears to be a result of that fix.
[6 Apr 2008 18:22] Davi Arnaut
Bug#35857 has been marked as a duplicate of this bug.
[10 Apr 2008 2:04] Jared S
when will 5.1.24-rc be release?
[18 Apr 2008 20:32] Jared S
Thanks, just dowloaded 5.1.24 with the fix.