Debian Codegraph @

mysql-test/suite/ndb/r/ndb_autoinc.result

raw
1 CREATE TABLE t1 (
2 id INT AUTO_INCREMENT,
3 PRIMARY KEY(id)
4 ) ENGINE=NDBCLUSTER;
5 CREATE TABLE t2 (
6 id INT AUTO_INCREMENT,
7 KEY(id)
8 ) ENGINE=NDBCLUSTER;
9 ERROR HY000: Can't create table 't2' (use SHOW WARNINGS for more info).
10 SHOW WARNINGS;
11 Level Code Message
12 Warning 1296 Got error 4335 'Only one autoincrement column allowed per table. Having a table without primary key uses an autoincr' from NDB
13 Error 1005 Can't create table 't2' (use SHOW WARNINGS for more info).
14 SHOW TABLES;
15 Tables_in_test
16 t1
17 CREATE TABLE t3 (
18 id INT AUTO_INCREMENT,
19 KEY(id)
20 ) ENGINE=MYISAM;
21 ALTER TABLE t3
22 ENGINE NDBCLUSTER;
23 ERROR HY000: Can't create destination table for copying alter table (use SHOW WARNINGS for more info).
24 SHOW WARNINGS;
25 Level Code Message
26 Warning 1296 Got error 4335 'Only one autoincrement column allowed per table. Having a table without primary key uses an autoincr' from NDB
27 Error 1005 Can't create destination table for copying alter table (use SHOW WARNINGS for more info).
28 SHOW CREATE TABLE t3;
29 Table Create Table
30 t3 CREATE TABLE `t3` (
31 `id` int NOT NULL AUTO_INCREMENT,
32 KEY `id` (`id`)
33 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
34 ALTER TABLE t3
35 ADD PRIMARY KEY (id);
36 SHOW CREATE TABLE t3;
37 Table Create Table
38 t3 CREATE TABLE `t3` (
39 `id` int NOT NULL AUTO_INCREMENT,
40 PRIMARY KEY (`id`),
41 KEY `id` (`id`)
42 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
43 DROP TABLE t1, t3;
44 set @@auto_increment_increment=4;
45 set @@auto_increment_offset=1;
46 set @@ndb_autoincrement_prefetch_sz=2;
47 create table t1 (a int unsigned not null primary key auto_increment)
48 engine ndb;
49 insert into t1 values ();
50 insert into t1 values (2);
51 insert into t1 values ();
52 insert into t1 values ();
53 select * from t1 order by a;
54 a
55 1
56 2
57 5
58 9
59 drop table t1;
60 set @@auto_increment_increment=2;
61 set @@auto_increment_offset=1;
62 First look at default SE behaviour on manual insert unaligned with
63 offset + step
64 create table t1 (a int unsigned not null primary key auto_increment);
65 show variables like 'LAST_INSERT_ID';
66 Variable_name Value
67 last_insert_id 9
68 insert into t1 values();
69 show variables like 'LAST_INSERT_ID';
70 Variable_name Value
71 last_insert_id 1
72 insert into t1 values(18);
73 show variables like 'LAST_INSERT_ID';
74 Variable_name Value
75 last_insert_id 1
76 insert into t1 values();
77 show variables like 'LAST_INSERT_ID';
78 Variable_name Value
79 last_insert_id 19
80 insert into t1 values();
81 show variables like 'LAST_INSERT_ID';
82 Variable_name Value
83 last_insert_id 21
84 insert into t1 values();
85 show variables like 'LAST_INSERT_ID';
86 Variable_name Value
87 last_insert_id 23
88 insert into t1 values(22);
89 show variables like 'LAST_INSERT_ID';
90 Variable_name Value
91 last_insert_id 23
92 insert into t1 values();
93 show variables like 'LAST_INSERT_ID';
94 Variable_name Value
95 last_insert_id 25
96 insert into t1 values(28);
97 show variables like 'LAST_INSERT_ID';
98 Variable_name Value
99 last_insert_id 25
100 insert into t1 values();
101 show variables like 'LAST_INSERT_ID';
102 Variable_name Value
103 last_insert_id 29
104 insert into t1 values();
105 show variables like 'LAST_INSERT_ID';
106 Variable_name Value
107 last_insert_id 31
108 select * from t1 order by a;
109 a
110 1
111 18
112 19
113 21
114 22
115 23
116 25
117 28
118 29
119 31
120 drop table t1;