Bug #16407 Events: Changes in sql_mode won't be taken into account
Submitted: 12 Jan 2006 4:52 Modified: 23 Feb 2006 4:37
Reporter: Peter Gulutzan
Status: Closed
Category:Server: Events Severity:S3 (Non-critical)
Version:5.1.6-alpha-debug OS:Linux (SUSE 10.0)
Assigned to: Andrey Hristov Target Version:

[12 Jan 2006 4: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 19:10] Miguel Solorzano 
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 16: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 17: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 23: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 23: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 23: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 23: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 2: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 3: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 4: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)