• istruewing@chilla.local's avatar
    Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID · e9e58c66
    istruewing@chilla.local authored
    Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
    
    INSERT DELAYED ignored an explicitly set INSERT_ID and session
    specific auto_increment_* variables.
    
    The problem was that the inserts are done by a system thread,
    which does not have access to the session variables of the user
    thread.
    
    On a proposal of Guilhem I fixed it so that the variables are
    copied to the data structure for every delayed row. The system
    thread sets its session variables from these values.
    e9e58c66
delayed.result 5.83 KB
drop table if exists t1;
create table t1 (a char(10), tmsp timestamp);
insert into t1 set a = 1;
insert delayed into t1 set a = 2;
insert into t1 set a = 3, tmsp=NULL;
insert delayed into t1 set a = 4;
insert delayed into t1 set a = 5, tmsp = 19711006010203;
insert delayed into t1 (a, tmsp) values (6, 19711006010203);
insert delayed into t1 (a, tmsp) values (7, NULL);
FLUSH TABLE t1;
insert into t1 set a = 8,tmsp=19711006010203;
select * from t1 where tmsp=0;
a	tmsp
select * from t1 where tmsp=19711006010203;
a	tmsp
5	1971-10-06 01:02:03
6	1971-10-06 01:02:03
8	1971-10-06 01:02:03
drop table t1;
create table t1 (a int not null auto_increment primary key, b char(10));
insert delayed into t1 values (1,"b");
insert delayed into t1 values (null,"c");
insert delayed into t1 values (3,"d"),(null,"e");
insert delayed into t1 values (3,"this will give an","error");
ERROR 21S01: Column count doesn't match value count at row 1
FLUSH TABLE t1;
show status like 'not_flushed_delayed_rows';
Variable_name	Value
Not_flushed_delayed_rows	0
select * from t1;
a	b
1	b
2	c
3	d
4	e
drop table t1;
create table t1 (a int not null primary key);
insert into t1 values (1);
insert delayed into t1 values (1);
select * from t1;
a
1
drop table t1;
CREATE TABLE t1 ( a int(10) NOT NULL auto_increment, PRIMARY KEY (a));
insert delayed into t1 values(null);
insert into t1 values(null);
insert into t1 values(null);
insert delayed into t1 values(null);
insert delayed into t1 values(null);
insert delayed into t1 values(null);
insert into t1 values(null);
insert into t1 values(null);
insert into t1 values(null);
delete from t1 where a=6;
insert delayed into t1 values(null);
insert delayed into t1 values(null);
insert delayed into t1 values(null);
insert delayed into t1 values(null);
FLUSH TABLE t1;
select * from t1 order by a;
a
1
2
3
4
5
7
8
9
10
11
12
13
DROP TABLE t1;
SET @bug20627_old_auto_increment_offset=
@@auto_increment_offset= 2;
SET @bug20627_old_auto_increment_increment=
@@auto_increment_increment= 3;
SET @bug20627_old_session_auto_increment_offset=
@@session.auto_increment_offset= 4;
SET @bug20627_old_session_auto_increment_increment=
@@session.auto_increment_increment= 5;
SET @@auto_increment_offset= 2;
SET @@auto_increment_increment= 3;
SET @@session.auto_increment_offset= 4;
SET @@session.auto_increment_increment= 5;
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1)
);
INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
SELECT * FROM t1;
c1
4
9
14
DROP TABLE t1;
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1)
);
INSERT DELAYED INTO t1 VALUES (NULL),(NULL),(NULL);
FLUSH TABLE t1;
SELECT * FROM t1;
c1
4
9
14
DROP TABLE t1;
SET             @@auto_increment_offset=
@bug20627_old_auto_increment_offset;
SET             @@auto_increment_increment=
@bug20627_old_auto_increment_increment;
SET             @@session.auto_increment_offset=
@bug20627_old_session_auto_increment_offset;
SET             @@session.auto_increment_increment=
@bug20627_old_session_auto_increment_increment;
SET @bug20830_old_auto_increment_offset=
@@auto_increment_offset= 2;
SET @bug20830_old_auto_increment_increment=
@@auto_increment_increment= 3;
SET @bug20830_old_session_auto_increment_offset=
@@session.auto_increment_offset= 4;
SET @bug20830_old_session_auto_increment_increment=
@@session.auto_increment_increment= 5;
SET @@auto_increment_offset= 2;
SET @@auto_increment_increment= 3;
SET @@session.auto_increment_offset= 4;
SET @@session.auto_increment_increment= 5;
CREATE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 INT(11) DEFAULT NULL,
PRIMARY KEY (c1)
);
SET insert_id= 14;
INSERT INTO t1 VALUES(NULL, 11), (NULL, 12), (NULL, 13);
INSERT INTO t1 VALUES(NULL, 21), (NULL, 22), (NULL, 23);
INSERT INTO t1 VALUES(  69, 31), (NULL, 32), (NULL, 33);
INSERT INTO t1 VALUES(NULL, 41), (NULL, 42), (NULL, 43);
SET insert_id= 114;
INSERT INTO t1 VALUES(NULL, 51), (NULL, 52), (NULL, 53);
INSERT INTO t1 VALUES(NULL, 61), (NULL, 62), (NULL, 63);
INSERT INTO t1 VALUES(  49, 71), (NULL, 72), (NULL, 73);
INSERT INTO t1 VALUES(NULL, 81), (NULL, 82), (NULL, 83);
SET insert_id= 114;
INSERT INTO t1 VALUES(NULL, 91);
ERROR 23000: Duplicate entry '114' for key 1
INSERT INTO t1 VALUES           (NULL, 92), (NULL, 93);
SELECT * FROM t1;
c1	c2
14	11
19	12
24	13
29	21
34	22
39	23
69	31
74	32
79	33
84	41
89	42
94	43
114	51
119	52
124	53
129	61
134	62
139	63
49	71
144	72
149	73
154	81
159	82
164	83
169	92
174	93
SELECT COUNT(*) FROM t1;
COUNT(*)
26
SELECT SUM(c1) FROM t1;
SUM(c1)
2569
DROP TABLE t1;
CREATE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 INT(11) DEFAULT NULL,
PRIMARY KEY (c1)
);
SET insert_id= 14;
INSERT DELAYED INTO t1 VALUES(NULL, 11), (NULL, 12), (NULL, 13);
INSERT DELAYED INTO t1 VALUES(NULL, 21), (NULL, 22), (NULL, 23);
INSERT DELAYED INTO t1 VALUES(  69, 31), (NULL, 32), (NULL, 33);
INSERT DELAYED INTO t1 VALUES(NULL, 41), (NULL, 42), (NULL, 43);
SET insert_id= 114;
INSERT DELAYED INTO t1 VALUES(NULL, 51), (NULL, 52), (NULL, 53);
INSERT DELAYED INTO t1 VALUES(NULL, 61), (NULL, 62), (NULL, 63);
INSERT DELAYED INTO t1 VALUES(  49, 71), (NULL, 72), (NULL, 73);
INSERT DELAYED INTO t1 VALUES(NULL, 81), (NULL, 82), (NULL, 83);
SET insert_id= 114;
INSERT DELAYED INTO t1 VALUES(NULL, 91);
INSERT DELAYED INTO t1 VALUES           (NULL, 92), (NULL, 93);
FLUSH TABLE t1;
SELECT * FROM t1;
c1	c2
14	11
19	12
24	13
29	21
34	22
39	23
69	31
74	32
79	33
84	41
89	42
94	43
114	51
119	52
124	53
129	61
134	62
139	63
49	71
144	72
149	73
154	81
159	82
164	83
169	92
174	93
SELECT COUNT(*) FROM t1;
COUNT(*)
26
SELECT SUM(c1) FROM t1;
SUM(c1)
2569
DROP TABLE t1;
SET             @@auto_increment_offset=
@bug20830_old_auto_increment_offset;
SET             @@auto_increment_increment=
@bug20830_old_auto_increment_increment;
SET             @@session.auto_increment_offset=
@bug20830_old_session_auto_increment_offset;
SET             @@session.auto_increment_increment=
@bug20830_old_session_auto_increment_increment;