mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
822 lines
17 KiB
Text
822 lines
17 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='Archive';
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=ARCHIVE 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);
|
|
# ERROR (only OK if Archive) mysql_errno: 1022
|
|
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);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
6
|
|
17
|
|
19
|
|
20
|
|
21
|
|
22
|
|
30
|
|
31
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (
|
|
c1 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c1))
|
|
ENGINE='Archive';
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=ARCHIVE 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=ARCHIVE 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=ARCHIVE 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=ARCHIVE AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
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=ARCHIVE AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
4
|
|
5
|
|
6
|
|
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=ARCHIVE AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
4
|
|
5
|
|
6
|
|
7
|
|
INSERT INTO t1 VALUES (100);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
OPTIMIZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
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=ARCHIVE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
DROP TABLE t1;
|
|
# Simple test with NULL
|
|
CREATE TABLE t1 (
|
|
c1 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c1))
|
|
ENGINE='Archive'
|
|
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=ARCHIVE 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='Archive'
|
|
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);
|
|
# ERROR (only OK if Archive) mysql_errno: 1022
|
|
SELECT * FROM t1 ORDER BY c1, c2;
|
|
c1 c2
|
|
1 1
|
|
1 2
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (
|
|
c1 INT,
|
|
c2 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c2))
|
|
ENGINE='Archive'
|
|
PARTITION BY HASH(c2)
|
|
PARTITIONS 2;
|
|
INSERT INTO t1 VALUES (1, 0);
|
|
# ERROR (only OK if Archive) mysql_errno: 1022
|
|
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 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='Archive'
|
|
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);
|
|
# ERROR (only OK if Archive) mysql_errno: 1022
|
|
INSERT INTO t1 VALUES (NULL), (9);
|
|
# ERROR (only OK if Archive) mysql_errno: 1022
|
|
INSERT INTO t1 VALUES (59), (55);
|
|
# ERROR (only OK if Archive) mysql_errno: 1022
|
|
INSERT INTO t1 VALUES (NULL), (90);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
6
|
|
17
|
|
19
|
|
20
|
|
21
|
|
22
|
|
59
|
|
60
|
|
90
|
|
91
|
|
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='Archive'
|
|
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='Archive'
|
|
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);
|
|
# ERROR (only OK if Archive) mysql_errno: 1022
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
6
|
|
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);
|
|
# ERROR (only OK if Archive) mysql_errno: 1022
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
6
|
|
17
|
|
19
|
|
20
|
|
21
|
|
22
|
|
23
|
|
24
|
|
INSERT INTO t1 VALUES (NULL);
|
|
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=ARCHIVE AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
6
|
|
17
|
|
19
|
|
20
|
|
21
|
|
22
|
|
23
|
|
24
|
|
25
|
|
26
|
|
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=ARCHIVE AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
6
|
|
17
|
|
19
|
|
20
|
|
21
|
|
22
|
|
23
|
|
24
|
|
25
|
|
26
|
|
27
|
|
INSERT INTO t1 VALUES (100);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
OPTIMIZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
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=ARCHIVE 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 = 'Archive'
|
|
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);
|
|
# ERROR (only OK if Archive) mysql_errno: 1022
|
|
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
|
|
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 = 'Archive';
|
|
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);
|
|
# ERROR (only OK if Archive) mysql_errno: 1022
|
|
connection con1;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
10
|
|
11
|
|
12
|
|
19
|
|
21
|
|
22
|
|
23
|
|
COMMIT;
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
10
|
|
11
|
|
12
|
|
19
|
|
21
|
|
22
|
|
23
|
|
disconnect con1;
|
|
connection default;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
10
|
|
11
|
|
12
|
|
19
|
|
21
|
|
22
|
|
23
|
|
24
|
|
COMMIT;
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
10
|
|
11
|
|
12
|
|
19
|
|
21
|
|
22
|
|
23
|
|
24
|
|
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 = 'Archive'
|
|
PARTITION BY HASH(c1)
|
|
PARTITIONS 2;
|
|
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);
|
|
# ERROR (only OK if Archive) mysql_errno: 1022
|
|
connection con1;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
10
|
|
11
|
|
12
|
|
19
|
|
21
|
|
22
|
|
23
|
|
COMMIT;
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
10
|
|
11
|
|
12
|
|
19
|
|
21
|
|
22
|
|
23
|
|
disconnect con1;
|
|
connection default;
|
|
INSERT INTO t1 (c1) VALUES (NULL);
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
10
|
|
11
|
|
12
|
|
19
|
|
21
|
|
22
|
|
23
|
|
24
|
|
COMMIT;
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
2
|
|
4
|
|
5
|
|
10
|
|
11
|
|
12
|
|
19
|
|
21
|
|
22
|
|
23
|
|
24
|
|
DROP TABLE t1;
|
|
# Test with another column before
|
|
CREATE TABLE t1 (
|
|
c1 INT,
|
|
c2 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c2))
|
|
ENGINE = 'Archive'
|
|
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 = 'Archive'
|
|
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 = 'Archive'
|
|
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=ARCHIVE AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
INSERT INTO t1 (c1) VALUES (4);
|
|
# ERROR (only OK if Archive) mysql_errno: 1022
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=ARCHIVE 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=ARCHIVE 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=ARCHIVE AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
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=ARCHIVE AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
INSERT INTO t1 (c1) VALUES (0);
|
|
# ERROR (only OK if Archive) mysql_errno: 1022
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=ARCHIVE 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=ARCHIVE AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`c1`)
|
|
PARTITIONS 2
|
|
SELECT * FROM t1 ORDER BY c1;
|
|
c1
|
|
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 = 'Archive'
|
|
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=ARCHIVE 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=ARCHIVE 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=ARCHIVE 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=ARCHIVE 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
|
|
22
|
|
23
|
|
24
|
|
DROP TABLE t1;
|
|
# Testing with FLUSH TABLE
|
|
CREATE TABLE t1 (
|
|
c1 INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (c1))
|
|
ENGINE='Archive'
|
|
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=ARCHIVE 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=ARCHIVE 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=ARCHIVE 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=ARCHIVE 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;
|