mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
1154 lines
26 KiB
Text
1154 lines
26 KiB
Text
DROP TABLE IF EXISTS t1;
|
|
# test without partitioning for reference
|
|
CREATE TABLE t1 (
|
|
c1 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c1))
|
|
ENGINE='InnoDB';
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
|
|
AND TABLE_NAME='t1';
|
|
AUTO_INCREMENT
|
|
1
|
|
INSERT INTO t1 VALUES (2);
|
|
INSERT INTO t1 VALUES (4);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
|
|
AND TABLE_NAME='t1';
|
|
AUTO_INCREMENT
|
|
6
|
|
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
|
|
AND TABLE_NAME='t1';
|
|
AUTO_INCREMENT
|
|
6
|
|
INSERT INTO t1 VALUES (0);
|
|
INSERT INTO t1 VALUES (5), (16);
|
|
INSERT INTO t1 VALUES (17);
|
|
INSERT INTO t1 VALUES (19), (NULL);
|
|
INSERT INTO t1 VALUES (NULL), (10), (NULL);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SET INSERT_ID = 30;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SET INSERT_ID = 29;
|
|
INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
UPDATE t1 SET c1 = 50 WHERE c1 = 17;
|
|
UPDATE t1 SET c1 = 51 WHERE c1 = 19;
|
|
FLUSH TABLES;
|
|
UPDATE t1 SET c1 = 40 WHERE c1 = 50;
|
|
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
|
|
AND TABLE_NAME='t1';
|
|
AUTO_INCREMENT
|
|
52
|
|
UPDATE t1 SET c1 = NULL WHERE c1 = 4;
|
|
Warnings:
|
|
Warning 1048 Column 'c1' cannot be null
|
|
INSERT INTO t1 VALUES (NULL);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
0
|
|
2
|
|
5
|
|
6
|
|
10
|
|
20
|
|
22
|
|
23
|
|
25
|
|
30
|
|
31
|
|
40
|
|
51
|
|
52
|
|
53
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (
|
|
c1 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c1))
|
|
ENGINE='InnoDB';
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
FLUSH TABLE;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
INSERT INTO t1 VALUES (4);
|
|
FLUSH TABLE;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
INSERT INTO t1 VALUES (NULL);
|
|
FLUSH TABLE;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
DELETE FROM t1;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
6
|
|
TRUNCATE TABLE t1;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
1
|
|
INSERT INTO t1 VALUES (100);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
DELETE FROM t1 WHERE c1 >= 100;
|
|
OPTIMIZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
|
|
test.t1 optimize status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1
|
|
(a INT NULL AUTO_INCREMENT,
|
|
UNIQUE KEY (a))
|
|
ENGINE='InnoDB';
|
|
SET LAST_INSERT_ID = 999;
|
|
SET INSERT_ID = 0;
|
|
INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
999
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
999
|
|
SELECT * FROM t1;
|
|
a
|
|
0
|
|
UPDATE t1 SET a = 1 WHERE a IS NULL;
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
999
|
|
SELECT * FROM t1;
|
|
a
|
|
0
|
|
UPDATE t1 SET a = NULL WHERE a = 1;
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
999
|
|
SELECT * FROM t1;
|
|
a
|
|
0
|
|
DROP TABLE t1;
|
|
SET INSERT_ID = 1;
|
|
# Simple test with NULL
|
|
CREATE TABLE t1 (
|
|
c1 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c1))
|
|
ENGINE='InnoDB'
|
|
PARTITION BY HASH(c1)
|
|
PARTITIONS 2;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
SELECT * FROM t1;
|
|
c1
|
|
1
|
|
DROP TABLE t1;
|
|
# Test with sql_mode and first insert as 0
|
|
CREATE TABLE t1 (
|
|
c1 INT,
|
|
c2 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c2))
|
|
ENGINE='InnoDB'
|
|
PARTITION BY HASH(c2)
|
|
PARTITIONS 2;
|
|
INSERT INTO t1 VALUES (1, NULL);
|
|
INSERT INTO t1 VALUES (1, 1), (99, 99);
|
|
INSERT INTO t1 VALUES (1, NULL);
|
|
SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
|
|
INSERT INTO t1 VALUES (1, 0);
|
|
SELECT * FROM t1 ORDER BY c1, c2;
|
|
c1 c2
|
|
1 0
|
|
1 1
|
|
1 2
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (
|
|
c1 INT,
|
|
c2 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c2))
|
|
ENGINE='InnoDB'
|
|
PARTITION BY HASH(c2)
|
|
PARTITIONS 2;
|
|
INSERT INTO t1 VALUES (1, 0);
|
|
INSERT INTO t1 VALUES (1, 1), (1, NULL);
|
|
INSERT INTO t1 VALUES (2, NULL), (4, 7);
|
|
INSERT INTO t1 VALUES (1, NULL);
|
|
SELECT * FROM t1 ORDER BY c1, c2;
|
|
c1 c2
|
|
1 0
|
|
1 1
|
|
1 2
|
|
1 8
|
|
2 3
|
|
4 7
|
|
SET @@session.sql_mode = '';
|
|
DROP TABLE t1;
|
|
# Simple test with NULL, 0 and explicit values both incr. and desc.
|
|
CREATE TABLE t1 (
|
|
c1 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c1))
|
|
ENGINE='InnoDB'
|
|
PARTITION BY HASH(c1)
|
|
PARTITIONS 2;
|
|
INSERT INTO t1 VALUES (2), (4), (NULL);
|
|
INSERT INTO t1 VALUES (0);
|
|
INSERT INTO t1 VALUES (5), (16);
|
|
INSERT INTO t1 VALUES (17), (19), (NULL);
|
|
INSERT INTO t1 VALUES (NULL), (10), (NULL);
|
|
INSERT INTO t1 VALUES (NULL), (9);
|
|
INSERT INTO t1 VALUES (59), (55);
|
|
INSERT INTO t1 VALUES (NULL), (90);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
UPDATE t1 SET c1 = 150 WHERE c1 = 17;
|
|
UPDATE t1 SET c1 = 151 WHERE c1 = 19;
|
|
FLUSH TABLES;
|
|
UPDATE t1 SET c1 = 140 WHERE c1 = 150;
|
|
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
|
|
AND TABLE_NAME='t1';
|
|
AUTO_INCREMENT
|
|
152
|
|
UPDATE t1 SET c1 = NULL WHERE c1 = 4;
|
|
Warnings:
|
|
Warning 1048 Column 'c1' cannot be null
|
|
INSERT INTO t1 VALUES (NULL);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
0
|
|
2
|
|
5
|
|
6
|
|
9
|
|
10
|
|
20
|
|
21
|
|
22
|
|
23
|
|
55
|
|
59
|
|
60
|
|
90
|
|
91
|
|
140
|
|
151
|
|
152
|
|
153
|
|
DROP TABLE t1;
|
|
# Test with auto_increment_increment and auto_increment_offset.
|
|
CREATE TABLE t1 (
|
|
c1 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c1))
|
|
ENGINE='InnoDB'
|
|
PARTITION BY HASH(c1)
|
|
PARTITIONS 2;
|
|
SET @@session.auto_increment_increment = 10;
|
|
SET @@session.auto_increment_offset = 5;
|
|
INSERT INTO t1 VALUES (1);
|
|
INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
|
|
SET @@session.auto_increment_increment = 5;
|
|
SET @@session.auto_increment_offset = 3;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
INSERT INTO t1 VALUES (33 + 1);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
INSERT INTO t1 VALUES (38 + 2);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
INSERT INTO t1 VALUES (43 + 3);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
INSERT INTO t1 VALUES (48 + 4);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
INSERT INTO t1 VALUES (53 + 5);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
INSERT INTO t1 VALUES (63 + 6);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SET @@session.auto_increment_increment = 1;
|
|
SET @@session.auto_increment_offset = 1;
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
1
|
|
5
|
|
15
|
|
25
|
|
33
|
|
34
|
|
38
|
|
40
|
|
43
|
|
46
|
|
48
|
|
52
|
|
53
|
|
58
|
|
63
|
|
69
|
|
73
|
|
DROP TABLE t1;
|
|
# Test reported auto_increment value
|
|
CREATE TABLE t1 (
|
|
c1 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c1))
|
|
ENGINE='InnoDB'
|
|
PARTITION BY HASH (c1)
|
|
PARTITIONS 2;
|
|
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
|
|
AND TABLE_NAME='t1';
|
|
AUTO_INCREMENT
|
|
1
|
|
INSERT INTO t1 VALUES (2);
|
|
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
|
|
AND TABLE_NAME='t1';
|
|
AUTO_INCREMENT
|
|
3
|
|
INSERT INTO t1 VALUES (4);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
|
|
AND TABLE_NAME='t1';
|
|
AUTO_INCREMENT
|
|
6
|
|
INSERT INTO t1 VALUES (NULL);
|
|
INSERT INTO t1 VALUES (17);
|
|
INSERT INTO t1 VALUES (19);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
|
|
AND TABLE_NAME='t1';
|
|
AUTO_INCREMENT
|
|
22
|
|
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
|
|
AND TABLE_NAME='t1';
|
|
AUTO_INCREMENT
|
|
22
|
|
INSERT INTO t1 VALUES (10);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
6
|
|
10
|
|
17
|
|
19
|
|
20
|
|
21
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
|
|
AND TABLE_NAME='t1';
|
|
AUTO_INCREMENT
|
|
23
|
|
INSERT INTO t1 VALUES (NULL);
|
|
INSERT INTO t1 VALUES (15);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
6
|
|
10
|
|
15
|
|
17
|
|
19
|
|
20
|
|
21
|
|
22
|
|
23
|
|
24
|
|
INSERT INTO t1 VALUES (NULL);
|
|
DELETE FROM t1;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
26
|
|
TRUNCATE TABLE t1;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
1
|
|
INSERT INTO t1 VALUES (100);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
DELETE FROM t1 WHERE c1 >= 100;
|
|
OPTIMIZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
|
|
test.t1 optimize status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
DROP TABLE t1;
|
|
# Test with two threads
|
|
connection default;
|
|
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
|
|
ENGINE = 'InnoDB'
|
|
PARTITION BY HASH(c1)
|
|
PARTITIONS 2;
|
|
INSERT INTO t1 (c1) VALUES (2);
|
|
INSERT INTO t1 (c1) VALUES (4);
|
|
connect con1, localhost, root,,;
|
|
connection con1;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
INSERT INTO t1 (c1) VALUES (10);
|
|
connection default;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
INSERT INTO t1 (c1) VALUES (19);
|
|
INSERT INTO t1 (c1) VALUES (21);
|
|
connection con1;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
connection default;
|
|
INSERT INTO t1 (c1) VALUES (16);
|
|
connection con1;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
disconnect con1;
|
|
connection default;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
10
|
|
11
|
|
12
|
|
16
|
|
19
|
|
21
|
|
22
|
|
23
|
|
24
|
|
DROP TABLE t1;
|
|
# Test with two threads + start transaction NO PARTITIONING
|
|
connect con1, localhost, root,,;
|
|
connection default;
|
|
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
|
|
ENGINE = 'InnoDB';
|
|
INSERT INTO t1(c1) VALUES(100);
|
|
START TRANSACTION;
|
|
INSERT INTO t1 (c1) VALUES (2);
|
|
INSERT INTO t1 (c1) VALUES (4);
|
|
connection con1;
|
|
START TRANSACTION;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
INSERT INTO t1 (c1) VALUES (10);
|
|
connection default;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
INSERT INTO t1 (c1) VALUES (19);
|
|
INSERT INTO t1 (c1) VALUES (21);
|
|
connection con1;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
connection default;
|
|
INSERT INTO t1 (c1) VALUES (16);
|
|
connection con1;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
10
|
|
100
|
|
101
|
|
104
|
|
105
|
|
COMMIT;
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
10
|
|
100
|
|
101
|
|
104
|
|
105
|
|
disconnect con1;
|
|
connection default;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
10
|
|
16
|
|
19
|
|
21
|
|
100
|
|
101
|
|
102
|
|
103
|
|
104
|
|
105
|
|
106
|
|
COMMIT;
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
10
|
|
16
|
|
19
|
|
21
|
|
100
|
|
101
|
|
102
|
|
103
|
|
104
|
|
105
|
|
106
|
|
DROP TABLE t1;
|
|
# Test with two threads + start transaction
|
|
connect con1, localhost, root,,;
|
|
connection default;
|
|
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
|
|
ENGINE = 'InnoDB'
|
|
PARTITION BY HASH(c1)
|
|
PARTITIONS 2;
|
|
INSERT INTO t1 (c1) VALUES (100);
|
|
INSERT INTO t1 (c1) VALUES (101);
|
|
START TRANSACTION;
|
|
INSERT INTO t1 (c1) VALUES (2);
|
|
INSERT INTO t1 (c1) VALUES (4);
|
|
connection con1;
|
|
START TRANSACTION;
|
|
INSERT INTO t1 (c1) VALUES (NULL), (10);
|
|
connection default;
|
|
INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
|
|
INSERT INTO t1 (c1) VALUES (21);
|
|
connection con1;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
connection default;
|
|
INSERT INTO t1 (c1) VALUES (16);
|
|
connection con1;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
10
|
|
100
|
|
101
|
|
102
|
|
105
|
|
106
|
|
COMMIT;
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
10
|
|
100
|
|
101
|
|
102
|
|
105
|
|
106
|
|
disconnect con1;
|
|
connection default;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
10
|
|
16
|
|
19
|
|
21
|
|
100
|
|
101
|
|
102
|
|
103
|
|
104
|
|
105
|
|
106
|
|
107
|
|
COMMIT;
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
10
|
|
16
|
|
19
|
|
21
|
|
100
|
|
101
|
|
102
|
|
103
|
|
104
|
|
105
|
|
106
|
|
107
|
|
DROP TABLE t1;
|
|
# Test with another column after
|
|
CREATE TABLE t1 (
|
|
c1 INT NOT NULL AUTO_INCREMENT,
|
|
c2 INT,
|
|
PRIMARY KEY (c1,c2))
|
|
ENGINE = 'InnoDB'
|
|
PARTITION BY HASH(c2)
|
|
PARTITIONS 2;
|
|
INSERT INTO t1 VALUES (1, 0);
|
|
INSERT INTO t1 VALUES (1, 1);
|
|
INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
|
|
INSERT INTO t1 VALUES (NULL, 3);
|
|
INSERT INTO t1 VALUES (2, 0), (NULL, 2);
|
|
INSERT INTO t1 VALUES (2, 2);
|
|
INSERT INTO t1 VALUES (2, 22);
|
|
INSERT INTO t1 VALUES (NULL, 2);
|
|
SELECT * FROM t1 ORDER BY c1,c2;
|
|
c1 c2
|
|
1 0
|
|
1 1
|
|
2 0
|
|
2 1
|
|
2 2
|
|
2 22
|
|
3 2
|
|
4 3
|
|
5 3
|
|
6 2
|
|
7 2
|
|
DROP TABLE t1;
|
|
# Test with another column before
|
|
CREATE TABLE t1 (
|
|
c1 INT,
|
|
c2 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c2))
|
|
ENGINE = 'InnoDB'
|
|
PARTITION BY HASH(c2)
|
|
PARTITIONS 2;
|
|
INSERT INTO t1 VALUES (1, 0);
|
|
INSERT INTO t1 VALUES (1, 1);
|
|
INSERT INTO t1 VALUES (1, NULL);
|
|
INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
|
|
INSERT INTO t1 VALUES (2, NULL);
|
|
INSERT INTO t1 VALUES (2, 2);
|
|
INSERT INTO t1 VALUES (2, 22);
|
|
INSERT INTO t1 VALUES (2, NULL);
|
|
SELECT * FROM t1 ORDER BY c1,c2;
|
|
c1 c2
|
|
1 1
|
|
1 2
|
|
2 3
|
|
2 13
|
|
2 14
|
|
2 22
|
|
2 23
|
|
3 11
|
|
3 12
|
|
DROP TABLE t1;
|
|
# Test with auto_increment on secondary column in multi-column-index
|
|
CREATE TABLE t1 (
|
|
c1 INT,
|
|
c2 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c1,c2))
|
|
ENGINE = 'InnoDB'
|
|
PARTITION BY HASH(c2)
|
|
PARTITIONS 2;
|
|
ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
|
|
# Test AUTO_INCREMENT in CREATE
|
|
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
|
|
ENGINE = 'InnoDB'
|
|
AUTO_INCREMENT = 15
|
|
PARTITION BY HASH(c1)
|
|
PARTITIONS 2;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
INSERT INTO t1 (c1) VALUES (4);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
INSERT INTO t1 (c1) VALUES (0);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
4
|
|
15
|
|
16
|
|
# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
|
|
SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
|
|
INSERT INTO t1 (c1) VALUES (300);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
INSERT INTO t1 (c1) VALUES (0);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
0
|
|
4
|
|
15
|
|
16
|
|
300
|
|
301
|
|
SET @@session.sql_mode = '';
|
|
DROP TABLE t1;
|
|
# Test SET INSERT_ID
|
|
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
|
|
ENGINE = 'InnoDB'
|
|
PARTITION BY HASH(c1)
|
|
PARTITIONS 2;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
SELECT * FROM t1;
|
|
c1
|
|
1
|
|
SET INSERT_ID = 23;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
SET INSERT_ID = 22;
|
|
INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
1
|
|
23
|
|
24
|
|
DROP TABLE t1;
|
|
# Testing with FLUSH TABLE
|
|
CREATE TABLE t1 (
|
|
c1 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c1))
|
|
ENGINE='InnoDB'
|
|
PARTITION BY HASH(c1)
|
|
PARTITIONS 2;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
FLUSH TABLE;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
INSERT INTO t1 VALUES (4);
|
|
FLUSH TABLE;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
INSERT INTO t1 VALUES (NULL);
|
|
FLUSH TABLE;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
4
|
|
5
|
|
DROP TABLE t1;
|
|
#############################################################################
|
|
# Bug #45823 - Assertion failure in file row/row0mysql.c line 1386
|
|
# Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31
|
|
##############################################################################
|
|
# Inserting negative autoincrement values into a partition table (partitions >= 4)
|
|
CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
|
|
c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
|
|
INSERT INTO t(c2) VALUES (10);
|
|
INSERT INTO t(c2) VALUES (20);
|
|
INSERT INTO t VALUES (-1,-10);
|
|
INSERT INTO t(c2) VALUES (30);
|
|
INSERT INTO t(c2) VALUES (40);
|
|
SELECT * FROM t ORDER BY c1 ASC;
|
|
c1 c2
|
|
-1 -10
|
|
1 10
|
|
2 20
|
|
3 30
|
|
4 40
|
|
DROP TABLE t;
|
|
# Reading from a partition table (partitions >= 2 ) after inserting a negative
|
|
# value into the auto increment column
|
|
CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
|
|
c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 2;
|
|
INSERT INTO t VALUES (-2,-20);
|
|
INSERT INTO t(c2) VALUES (30);
|
|
SELECT * FROM t ORDER BY c1 ASC;
|
|
c1 c2
|
|
-2 -20
|
|
1 30
|
|
DROP TABLE t;
|
|
# Inserting negative auto increment value into a partition table (partitions >= 2)
|
|
# auto increment value > 2.
|
|
CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
|
|
c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 2;
|
|
INSERT INTO t VALUES (-4,-20);
|
|
INSERT INTO t(c2) VALUES (30);
|
|
INSERT INTO t(c2) VALUES (40);
|
|
SELECT * FROM t ORDER BY c1 ASC;
|
|
c1 c2
|
|
-4 -20
|
|
1 30
|
|
2 40
|
|
DROP TABLE t;
|
|
# Inserting -1 into autoincrement column of a partition table (partition >= 4)
|
|
CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
|
|
c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
|
|
INSERT INTO t(c2) VALUES (10);
|
|
INSERT INTO t(c2) VALUES (20);
|
|
INSERT INTO t VALUES (-1,-10);
|
|
SELECT * FROM t ORDER BY c1 ASC;
|
|
c1 c2
|
|
-1 -10
|
|
1 10
|
|
2 20
|
|
INSERT INTO t(c2) VALUES (30);
|
|
SELECT * FROM t ORDER BY c1 ASC;
|
|
c1 c2
|
|
-1 -10
|
|
1 10
|
|
2 20
|
|
3 30
|
|
DROP TABLE t;
|
|
# Deleting from an auto increment table after inserting negative values
|
|
CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
|
|
c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
|
|
INSERT INTO t(c2) VALUES (10);
|
|
INSERT INTO t(c2) VALUES (20);
|
|
INSERT INTO t VALUES (-1,-10);
|
|
INSERT INTO t(c2) VALUES (30);
|
|
INSERT INTO t VALUES (-3,-20);
|
|
INSERT INTO t(c2) VALUES (40);
|
|
SELECT * FROM t ORDER BY c1 ASC;
|
|
c1 c2
|
|
-3 -20
|
|
-1 -10
|
|
1 10
|
|
2 20
|
|
3 30
|
|
4 40
|
|
DELETE FROM t WHERE c1 > 1;
|
|
SELECT * FROM t ORDER BY c1 ASC;
|
|
c1 c2
|
|
-3 -20
|
|
-1 -10
|
|
1 10
|
|
DROP TABLE t;
|
|
# Inserting a positive value that exceeds maximum allowed value for an
|
|
# Auto Increment column (positive maximum)
|
|
CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
|
|
c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
|
|
INSERT INTO t(c2) VALUES (10);
|
|
INSERT INTO t(c2) VALUES (20);
|
|
INSERT INTO t VALUES (126,30);
|
|
INSERT INTO t VALUES (127,40);
|
|
INSERT INTO t VALUES (128,50);
|
|
ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
|
|
INSERT INTO t VALUES (129,60);
|
|
ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
|
|
SELECT * FROM t ORDER BY c1 ASC;
|
|
c1 c2
|
|
1 10
|
|
2 20
|
|
126 30
|
|
127 40
|
|
DROP TABLE t;
|
|
# Inserting a negative value that goes below minimum allowed value for an
|
|
# Auto Increment column (negative minimum)
|
|
CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
|
|
c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
|
|
INSERT INTO t(c2) VALUES (10);
|
|
INSERT INTO t(c2) VALUES (20);
|
|
INSERT INTO t VALUES (-127,30);
|
|
INSERT INTO t VALUES (-128,40);
|
|
INSERT INTO t VALUES (-129,50);
|
|
ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
|
|
INSERT INTO t VALUES (-130,60);
|
|
ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
|
|
SELECT * FROM t ORDER BY c1 ASC;
|
|
c1 c2
|
|
-128 40
|
|
-127 30
|
|
1 10
|
|
2 20
|
|
DROP TABLE t;
|
|
# Updating the partition table with a negative Auto Increment value
|
|
CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
|
|
c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
|
|
INSERT INTO t(c2) VALUES (10);
|
|
INSERT INTO t(c2) VALUES (20);
|
|
INSERT INTO t VALUES (-1,-10);
|
|
INSERT INTO t(c2) VALUES (30);
|
|
SELECT * FROM t ORDER BY c1 ASC;
|
|
c1 c2
|
|
-1 -10
|
|
1 10
|
|
2 20
|
|
3 30
|
|
UPDATE t SET c1 = -6 WHERE c1 = 2;
|
|
SELECT * FROM t ORDER BY c1 ASC;
|
|
c1 c2
|
|
-6 20
|
|
-1 -10
|
|
1 10
|
|
3 30
|
|
INSERT INTO t(c2) VALUES (40);
|
|
INSERT INTO t(c2) VALUES (50);
|
|
UPDATE t SET c1 = -6 WHERE c1 = 2;
|
|
SELECT * FROM t ORDER BY c1 ASC;
|
|
c1 c2
|
|
-6 20
|
|
-1 -10
|
|
1 10
|
|
3 30
|
|
4 40
|
|
5 50
|
|
DROP TABLE t;
|
|
# Updating the partition table with a value that crosses the upper limits
|
|
# on both the positive and the negative side.
|
|
CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
|
|
c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
|
|
INSERT INTO t(c2) VALUES (10);
|
|
INSERT INTO t(c2) VALUES (20);
|
|
INSERT INTO t VALUES (126,30);
|
|
INSERT INTO t VALUES (127,40);
|
|
SELECT * FROM t ORDER BY c1 ASC;
|
|
c1 c2
|
|
1 10
|
|
2 20
|
|
126 30
|
|
127 40
|
|
UPDATE t SET c1 = 130 where c1 = 127;
|
|
Warnings:
|
|
Warning 1264 Out of range value for column 'c1' at row 1
|
|
SELECT * FROM t ORDER BY c1 ASC;
|
|
c1 c2
|
|
1 10
|
|
2 20
|
|
126 30
|
|
127 40
|
|
UPDATE t SET c1 = -140 where c1 = 126;
|
|
Warnings:
|
|
Warning 1264 Out of range value for column 'c1' at row 1
|
|
SELECT * FROM t ORDER BY c1 ASC;
|
|
c1 c2
|
|
-128 30
|
|
1 10
|
|
2 20
|
|
127 40
|
|
DROP TABLE t;
|
|
CREATE TABLE t1
|
|
(a INT NULL AUTO_INCREMENT,
|
|
UNIQUE KEY (a))
|
|
ENGINE='InnoDB'
|
|
PARTITION BY KEY(a) PARTITIONS 2;
|
|
SET LAST_INSERT_ID = 999;
|
|
SET INSERT_ID = 0;
|
|
INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
999
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
999
|
|
SELECT * FROM t1;
|
|
a
|
|
0
|
|
UPDATE t1 SET a = 1 WHERE a IS NULL;
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
999
|
|
SELECT * FROM t1;
|
|
a
|
|
0
|
|
UPDATE t1 SET a = NULL WHERE a = 1;
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
999
|
|
SELECT * FROM t1;
|
|
a
|
|
0
|
|
DROP TABLE t1;
|
|
##############################################################################
|
|
#
|
|
# MDEV-19622 Assertion failures in
|
|
# ha_partition::set_auto_increment_if_higher upon UPDATE on Aria table
|
|
#
|
|
CREATE OR REPLACE TABLE t1 (pk INT AUTO_INCREMENT, a INT, KEY(pk)) ENGINE='InnoDB' PARTITION BY HASH(a);
|
|
INSERT INTO t1 VALUES (1,1),(2,2);
|
|
UPDATE t1 SET pk = 0;
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-29636 Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()'
|
|
# failed in ha_partition::set_auto_increment_if_higher upon REPLACE
|
|
# with partition pruning
|
|
#
|
|
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE='InnoDB'
|
|
PARTITION BY RANGE (a) (
|
|
PARTITION p0 VALUES LESS THAN (10),
|
|
PARTITION pn VALUES LESS THAN MAXVALUE
|
|
);
|
|
REPLACE INTO t1 PARTITION (p0) SELECT 1;
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-21027 Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()'
|
|
# ha_partition::set_auto_increment_if_higher
|
|
#
|
|
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE='InnoDB' PARTITION BY HASH (a) PARTITIONS 3;
|
|
REPLACE INTO t1 PARTITION (p0) VALUES (3);
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-21310 AUTO_INCREMENT column throws range error on INSERT in partitioned table |
|
|
# Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()' failed.
|
|
#
|
|
CREATE TABLE t1 (c INT AUTO_INCREMENT KEY) ENGINE='InnoDB' PARTITION BY LIST (c) (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2));
|
|
ALTER TABLE t1 TRUNCATE PARTITION p1;
|
|
INSERT INTO t1 PARTITION (p1) (c) SELECT 1;
|
|
DROP TABLE t1;
|