Bug #27358 INSERT DELAYED does not honour SQL_MODE of the client
Submitted: 21 Mar 2007 20:18 Modified: 23 Oct 2007 19:48
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0-bk OS:Linux (linux)
Assigned to: Kristofer Pettersson CPU Architecture:Any

[21 Mar 2007 20:18] Guilhem Bichot
Description:
If a client does
SET SESSION SQL_MODE=some_mode;
INSERT DELAYED something;
then the "something" is queued into the queue of the delayed system thread, without the "some_mode" information.
So if the interpretation of "something" depends on "some_mode", INSERT DELAYED will not insert what was intended.
Example:
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
CREATE TABLE `t1` (
  `id` int(11) PRIMARY KEY auto_increment,
  `f1` varchar(10) NOT NULL UNIQUE
);
insert delayed into t1 values(0,"test1");

How to repeat:
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
CREATE TABLE `t1` (
  `id` int(11) PRIMARY KEY auto_increment,
  `f1` varchar(10) NOT NULL UNIQUE
);
insert delayed into t1 values(0,"test1");
sleep 1;
SELECT * FROM t1;
In t1 I see:
1       test1
i.e. the sql_mode value was not honoured.
While if I replace INSERT DELAYED by a plain INSERT, in t1 I see
0       test1
as expected.
Can happen if you have a MyISAM table with an autoincrement column, containing (0,"test1") and you dump it with
mysqldump --delayed-insert
then the dump will look like my example above and so will insert (1,"test1") if executed.
Other example:
set sql_mode=pipes_as_concat;
insert delayed into t1 values(0, "a" || "b");

Suggested fix:
long fix: add "sql_mode" to the delayed_row class, save it when the row is queued, use it when the row is actually inserted later.
short fix: document the problem.
[23 Aug 2007 8:22] 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/32929

ChangeSet@1.2512, 2007-08-23 10:22:20+02:00, thek@adventure.(none) +3 -0
  Bug#27358 INSERT DELAYED does not honour SQL_MODE of the client
  
  SQL_MODE was ignored when a client issued INSERT DELAYED.
  
  Some system settings weren't copied as intended when a record was saved for
  a delayed insert.
[19 Oct 2007 18:53] Bugs System
Pushed into 5.1.23-beta
[23 Oct 2007 19:48] Paul Dubois
Noted in 5.1.23 changelog.