Bug #16407 Events: Changes in sql_mode won't be taken into account
Submitted: 12 Jan 2006 3:52 Modified: 23 Feb 2006 3:37
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1.6-alpha-debug OS:Linux (SUSE 10.0)
Assigned to: Andrey Hristov CPU Architecture:Any

[12 Jan 2006 3:52] Peter Gulutzan
Description:
If I have sql_mode=ansi, and put DROP TABLE "f" in the
event body, then I see DROP TABLE "f" in mysql.event.
That's no good because if I change sql_mode, the
expectation will be DROP TABLE `f`.

How to repeat:

mysql> set sql_mode=ansi;
Query OK, 0 rows affected (0.00 sec)

mysql> create event e_14 on schedule every 1 second do drop table "f";
Query OK, 1 row affected (0.00 sec)

mysql> select body from mysql.event where name = 'e_14';
+----------------+
| body           |
+----------------+
| drop table "f" |
+----------------+
1 row in set (0.00 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select body from mysql.event where name = 'e_14';
+----------------+
| body           |
+----------------+
| drop table "f" |
+----------------+
1 row in set (0.00 sec)

Stop the server, mysqld. Start mysqld again.
Say "set globa event_scheduler = 1" on the client.

Notice the mysqld message
"Error while compiling db1.e_14. Aborting load."
[12 Jan 2006 18:10] MySQL Verification Team
miguel@hegel:~/dbs/5.1> libexec/mysqld
030412 16:10:12  InnoDB: Started; log sequence number 0 43655
030412 16:10:12 [Note] libexec/mysqld: ready for connections.
Version: '5.1.6-alpha-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
030412 16:10:24 [ERROR] Error while compiling db1.e_14. Aborting load.
030412 16:10:24 [Note] Scheduler loaded 1 event
030412 16:10:24 [Note] Event scheduler stopping. Waiting for worker threads to finish.
030412 16:10:24 [Note] Event scheduler stopped.
[31 Jan 2006 15:03] 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:

  http://lists.mysql.com/commits/1947
[31 Jan 2006 16:30] Andrey Hristov
Only the column sql_mode was added to the table structure. Because 5.1.6 will be cloned off tommorow it's better to have the column there but because of time constraints the bug is still not fixed. This will ease an upgrade from 5.1.6 to the version where this bug will be fixed.
[13 Feb 2006 22:29] 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:

  http://lists.mysql.com/commits/2551
[13 Feb 2006 22:31] 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:

  http://lists.mysql.com/commits/2552
[13 Feb 2006 22:35] 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:

  http://lists.mysql.com/commits/2553
[20 Feb 2006 22:55] 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:

  http://lists.mysql.com/commits/2937
[21 Feb 2006 1:42] 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:

  http://lists.mysql.com/commits/2942
[21 Feb 2006 2:54] Andrey Hristov
Fixed in 5.1.7 . sql_mode of the client during event create/alter was disregarded.
The sql_mode is recorded during creat event and during alter event if in this case the body is changed. If there is no body change then sql_mode is preserved.
[23 Feb 2006 3:37] Paul DuBois
Noted in 5.1.7 changelog.

The SQL mode in effect at the time an event is created or
altered is recorded and used during event execution. (Bug
#16407)