drop table if exists t1,t2,t3; create table t1 (id int auto_increment key) engine=pbxt auto_increment=200; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=PBXT AUTO_INCREMENT=200 DEFAULT CHARSET=latin1 alter table t1 add column text char(40); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `text` char(40) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=PBXT AUTO_INCREMENT=200 DEFAULT CHARSET=latin1 alter table t1 auto_increment = 100; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `text` char(40) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=PBXT AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 drop table if exists t1; create table t1 ( id int, c_char char(3), c_varchar varchar(3), c_char_bin char(3) binary, c_varchar_bin varchar(3) binary, c_char_uni char(3) unicode, c_varchar_uni varchar(3) unicode, c_dec decimal(3), index(c_char), index(c_varchar), index(c_char_bin), index(c_varchar_bin), index(c_char_uni), index(c_varchar_uni), index(c_dec) ) engine=pbxt; insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", 12); insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", 123); select c_char from t1 where c_char = "ab"; c_char ab ab select c_varchar from t1 where c_varchar = "ab"; c_varchar ab ab select c_char_bin from t1 where c_char_bin = "ab"; c_char_bin ab ab select c_varchar_bin from t1 where c_varchar_bin = "ab"; c_varchar_bin ab ab select c_char_uni from t1 where c_char_uni = "ab"; c_char_uni ab ab select c_varchar_uni from t1 where c_varchar_uni = "ab"; c_varchar_uni ab ab select c_dec from t1 where c_dec = 12; c_dec 12 select * from t1 where c_char = "ab "; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_varchar = "ab "; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_char_bin = "ab "; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_varchar_bin = "ab "; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_char_uni = "ab "; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_varchar_uni = "ab "; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_dec = 123; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 2 ab ab ab ab ab ab 123 drop table if exists t1; create table t1 ( id int, c_char char(100), c_varchar varchar(100), c_char_bin char(100) binary, c_varchar_bin varchar(100) binary, c_char_uni char(100) unicode, c_varchar_uni varchar(100) unicode, c_dec decimal(65), index(c_char), index(c_varchar), index(c_char_bin), index(c_varchar_bin), index(c_char_uni), index(c_varchar_uni), index(c_dec) ) engine=pbxt; insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", 12); insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", 123); select * from t1 where c_char = "ab"; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_varchar = "ab"; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_char_bin = "ab"; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_varchar_bin = "ab"; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_char_uni = "ab"; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_varchar_uni = "ab"; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_dec = 12; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 select c_char from t1 where c_char = "ab "; c_char ab ab select c_varchar from t1 where c_varchar = "ab "; c_varchar ab ab select c_char_bin from t1 where c_char_bin = "ab "; c_char_bin ab ab select c_varchar_bin from t1 where c_varchar_bin = "ab "; c_varchar_bin ab ab select c_char_uni from t1 where c_char_uni = "ab "; c_char_uni ab ab select c_varchar_uni from t1 where c_varchar_uni = "ab "; c_varchar_uni ab ab select c_dec from t1 where c_dec = 123; c_dec 123 drop table if exists t1; create table t1 ( id int, c_varchar varchar(300), c_varchar_bin varchar(300) binary, c_varchar_uni varchar(300) unicode, c_ttext TINYTEXT, c_text TEXT, c_mtext MEDIUMTEXT, c_ltext LONGTEXT, index(c_varchar), index(c_varchar_bin), index(c_varchar_uni), index(c_ttext(100)), index(c_text(100)), index(c_mtext(100)), index(c_ltext(100)) ) engine=pbxt; insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", "ab"); insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", "ab "); select c_varchar from t1 where c_varchar = "ab"; c_varchar ab ab select c_varchar_bin from t1 where c_varchar_bin = "ab"; c_varchar_bin ab ab select c_varchar_uni from t1 where c_varchar_uni = "ab"; c_varchar_uni ab ab select c_ttext from t1 where c_ttext = "ab"; c_ttext ab ab select c_text from t1 where c_text = "ab"; c_text ab ab select c_mtext from t1 where c_mtext = "ab"; c_mtext ab ab select c_ltext from t1 where c_ltext = "ab"; c_ltext ab ab select * from t1 where c_varchar = "ab "; id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext 1 ab ab ab ab ab ab ab 2 ab ab ab ab ab ab ab select * from t1 where c_varchar_bin = "ab "; id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext 1 ab ab ab ab ab ab ab 2 ab ab ab ab ab ab ab select * from t1 where c_varchar_uni = "ab "; id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext 1 ab ab ab ab ab ab ab 2 ab ab ab ab ab ab ab select * from t1 where c_ttext = "ab "; id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext 1 ab ab ab ab ab ab ab 2 ab ab ab ab ab ab ab select * from t1 where c_text = "ab "; id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext 1 ab ab ab ab ab ab ab 2 ab ab ab ab ab ab ab select * from t1 where c_mtext = "ab "; id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext 1 ab ab ab ab ab ab ab 2 ab ab ab ab ab ab ab select * from t1 where c_ltext = "ab "; id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext 1 ab ab ab ab ab ab ab 2 ab ab ab ab ab ab ab drop table if exists t1; create table t1 ( id int not null, c_char char(3) not null, c_varchar varchar(3) not null, c_char_bin char(3) binary not null, c_varchar_bin varchar(3) binary not null, c_char_uni char(3) unicode not null, c_varchar_uni varchar(3) unicode not null, c_dec decimal(3) not null, index(c_char), index(c_varchar), index(c_char_bin), index(c_varchar_bin), index(c_char_uni), index(c_varchar_uni), index(c_dec) ) engine=pbxt; insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", 12); insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", 123); select * from t1 where c_char = "ab"; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_varchar = "ab"; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_char_bin = "ab"; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_varchar_bin = "ab"; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_char_uni = "ab"; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_varchar_uni = "ab"; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_dec = 12; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 select c_char from t1 where c_char = "ab "; c_char ab ab select c_varchar from t1 where c_varchar = "ab "; c_varchar ab ab select c_char_bin from t1 where c_char_bin = "ab "; c_char_bin ab ab select c_varchar_bin from t1 where c_varchar_bin = "ab "; c_varchar_bin ab ab select c_char_uni from t1 where c_char_uni = "ab "; c_char_uni ab ab select c_varchar_uni from t1 where c_varchar_uni = "ab "; c_varchar_uni ab ab select c_dec from t1 where c_dec = 123; c_dec 123 drop table if exists t1; create table t1 ( id int not null, c_char char(100) not null, c_varchar varchar(100) not null, c_char_bin char(100) binary not null, c_varchar_bin varchar(100) binary not null, c_char_uni char(100) unicode not null, c_varchar_uni varchar(100) unicode not null, c_dec decimal(65), index(c_char), index(c_varchar), index(c_char_bin), index(c_varchar_bin), index(c_char_uni), index(c_varchar_uni), index(c_dec) ) engine=pbxt; insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", 12); insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", 123); select c_char from t1 where c_char = "ab"; c_char ab ab select c_varchar from t1 where c_varchar = "ab"; c_varchar ab ab select c_char_bin from t1 where c_char_bin = "ab"; c_char_bin ab ab select c_varchar_bin from t1 where c_varchar_bin = "ab"; c_varchar_bin ab ab select c_char_uni from t1 where c_char_uni = "ab"; c_char_uni ab ab select c_varchar_uni from t1 where c_varchar_uni = "ab"; c_varchar_uni ab ab select c_dec from t1 where c_dec = 12; c_dec 12 select * from t1 where c_char = "ab "; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_varchar = "ab "; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_char_bin = "ab "; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_varchar_bin = "ab "; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_char_uni = "ab "; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_varchar_uni = "ab "; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 1 ab ab ab ab ab ab 12 2 ab ab ab ab ab ab 123 select * from t1 where c_dec = 123; id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec 2 ab ab ab ab ab ab 123 drop table if exists t1; create table t1 ( id int, c_varchar varchar(300) not null, c_varchar_bin varchar(300) binary not null, c_varchar_uni varchar(300) unicode not null, c_ttext TINYTEXT not null, c_text TEXT not null, c_mtext MEDIUMTEXT not null, c_ltext LONGTEXT not null, index(c_varchar), index(c_varchar_bin), index(c_varchar_uni), index(c_ttext(100)), index(c_text(100)), index(c_mtext(100)), index(c_ltext(100)) ) engine=pbxt; insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", "ab"); insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", "ab "); select * from t1 where c_varchar = "ab"; id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext 1 ab ab ab ab ab ab ab 2 ab ab ab ab ab ab ab select * from t1 where c_varchar_bin = "ab"; id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext 1 ab ab ab ab ab ab ab 2 ab ab ab ab ab ab ab select * from t1 where c_varchar_uni = "ab"; id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext 1 ab ab ab ab ab ab ab 2 ab ab ab ab ab ab ab select * from t1 where c_ttext = "ab"; id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext 1 ab ab ab ab ab ab ab 2 ab ab ab ab ab ab ab select * from t1 where c_text = "ab"; id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext 1 ab ab ab ab ab ab ab 2 ab ab ab ab ab ab ab select * from t1 where c_mtext = "ab"; id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext 1 ab ab ab ab ab ab ab 2 ab ab ab ab ab ab ab select * from t1 where c_ltext = "ab"; id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext 1 ab ab ab ab ab ab ab 2 ab ab ab ab ab ab ab select c_varchar from t1 where c_varchar = "ab "; c_varchar ab ab select c_varchar_bin from t1 where c_varchar_bin = "ab "; c_varchar_bin ab ab select c_varchar_uni from t1 where c_varchar_uni = "ab "; c_varchar_uni ab ab select c_ttext from t1 where c_ttext = "ab "; c_ttext ab ab select c_text from t1 where c_text = "ab "; c_text ab ab select c_mtext from t1 where c_mtext = "ab "; c_mtext ab ab select c_ltext from t1 where c_ltext = "ab "; c_ltext ab ab drop table if exists t1; create table t1 ( id int, c_char_suni char(4) unicode, c_varchar_suni varchar(4) unicode, c_char_uni char(255) unicode, c_varchar_uni varchar(300) unicode, index(c_char_suni), index(c_varchar_suni), index(c_char_uni), index(c_varchar_uni) ) engine=pbxt; insert t1 values (1, "ab", "ab", "ab", "ab"); insert t1 values (1, "abcd", "abcd", "abcd", "abcd"); insert t1 values (1, "ab ", "ab ", "abcd123123123123123123123123123123123123", "abcd123123123123123123123123123123123123"); select * from t1 where c_char_suni = "ab"; id c_char_suni c_varchar_suni c_char_uni c_varchar_uni 1 ab ab ab ab 1 ab ab abcd123123123123123123123123123123123123 abcd123123123123123123123123123123123123 select * from t1 where c_varchar_suni = "ab"; id c_char_suni c_varchar_suni c_char_uni c_varchar_uni 1 ab ab ab ab 1 ab ab abcd123123123123123123123123123123123123 abcd123123123123123123123123123123123123 select * from t1 where c_char_uni = "ab"; id c_char_suni c_varchar_suni c_char_uni c_varchar_uni 1 ab ab ab ab select * from t1 where c_varchar_uni = "ab"; id c_char_suni c_varchar_suni c_char_uni c_varchar_uni 1 ab ab ab ab drop table if exists t1; create table t1 (id int, name char(10) key) engine=pbxt; insert t1 values (1, "ab"); select * from t1 where name = "ab"; id name 1 ab insert t1 values (2, "ab "); ERROR 23000: Duplicate entry 'ab' for key 'PRIMARY' drop table if exists t1; create table t1 (id int, name char(100) key) engine=pbxt; insert t1 values (1, "ab"); select * from t1 where name = "ab"; id name 1 ab insert t1 values (2, "ab "); ERROR 23000: Duplicate entry 'ab' for key 'PRIMARY' drop table if exists t1; create table t1 (id int, name varchar(10) key) engine=pbxt; insert t1 values (1, "ab"); select * from t1 where name = "ab"; id name 1 ab insert t1 values (2, "ab "); ERROR 23000: Duplicate entry 'ab ' for key 'PRIMARY' drop table if exists t1; create table t1 (id int, name varchar(100) key) engine=pbxt; insert t1 values (1, "ab"); select * from t1 where name = "ab"; id name 1 ab insert t1 values (2, "ab "); ERROR 23000: Duplicate entry 'ab ' for key 'PRIMARY' drop table if exists t1; create table t1 (id int, name varchar(400) key) engine=pbxt; insert t1 values (1, "ab"); select * from t1 where name = "ab"; id name 1 ab insert t1 values (2, "ab "); ERROR 23000: Duplicate entry 'ab ' for key 'PRIMARY' drop table if exists t1; create table t1 (id int, name char(10) key) engine=pbxt; insert t1 values (1, "ab"); select * from t1 where name = "ab"; id name 1 ab insert t1 values (2, "ab "); ERROR 23000: Duplicate entry 'ab' for key 'PRIMARY' drop table if exists t1; create table t1 (id int, name char(100) key) engine=pbxt; insert t1 values (1, "ab"); select * from t1 where name = "ab"; id name 1 ab insert t1 values (2, "ab "); ERROR 23000: Duplicate entry 'ab' for key 'PRIMARY' drop table if exists t1; create table t1 (id int, name varchar(10), index(name)) engine=pbxt; insert t1 values (1, "ab"); select * from t1 where name = "ab"; id name 1 ab insert t1 values (2, "ab "); insert t1 values (3, "ab "); select * from t1 where name = "ab"; id name 1 ab 2 ab 3 ab insert t1 values (4, "ab "); drop table if exists t1; create table t1 (id int, name varchar(100), index(name)) engine=pbxt; insert t1 values (1, "ab"); select * from t1 where name = "ab"; id name 1 ab insert t1 values (2, "ab "); insert t1 values (3, "ab "); select * from t1 where name = "ab"; id name 1 ab 2 ab 3 ab insert t1 values (4, "ab "); drop table if exists t1; create table t1 (id int, name varchar(400), index(name)) engine=pbxt; insert t1 values (1, "ab"); select * from t1 where name = "ab"; id name 1 ab insert t1 values (2, "ab "); insert t1 values (3, "ab "); select * from t1 where name = "ab"; id name 1 ab 2 ab 3 ab insert t1 values (4, "ab "); drop table if exists t1; create table t1 (id int, name char(10), index(name)) engine=pbxt; insert t1 values (1, "ab"); select * from t1 where name = "ab"; id name 1 ab insert t1 values (2, "ab "); insert t1 values (3, "ab "); select * from t1 where name = "ab"; id name 1 ab 2 ab 3 ab insert t1 values (4, "ab "); drop table if exists t1; create table t1 (id int, name char(100), index(name)) engine=pbxt; insert t1 values (1, "ab"); select * from t1 where name = "ab"; id name 1 ab insert t1 values (2, "ab "); insert t1 values (3, "ab "); select * from t1 where name = "ab"; id name 1 ab 2 ab 3 ab insert t1 values (4, "ab "); DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id int, name varchar(300)) engine=pbxt; begin; insert t1(id, name) values(1, "aaa"); update t1 set name=REPEAT('A', 300) where id = 1; commit; flush tables; select * from t1; id name 1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id int, name varchar(300)) engine=pbxt; begin; insert t1(id, name) values(1, REPEAT('A', 300)); update t1 set name="aaa" where id = 1; commit; flush tables; select * from t1; id name 1 aaa drop table if exists t1, t2; create table t1 (s1 int primary key) engine = pbxt; insert into t1 values (1); create table t2 (s1 int, foreign key (s1) references t1 (s1)) engine = pbxt; insert into t2 values (1); truncate table t1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `FOREIGN_1`) alter table t1 engine myisam; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `s1` int(11) NOT NULL, PRIMARY KEY (`s1`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table if exists t1, t2; create table t1 (s1 int primary key, s2 int unique not null) engine = pbxt; create table t2 (s1 int, foreign key (s1) references t1 (s1)) engine = pbxt; select * from information_schema.table_constraints where constraint_type = 'FOREIGN KEY' and table_name = 't2'; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE NULL test FOREIGN_1 test t2 FOREIGN KEY select * from information_schema.referential_constraints where table_name = 't2'; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME NULL test FOREIGN_1 NULL test PRIMARY NONE RESTRICT RESTRICT t2 t1 drop table t2, t1; create table t1 (s1 int, s2 int, unique key ix1 (s1, s2)) engine = pbxt; create table t2 (s1 int, s2 int, foreign key (s1, s2) references t1 (s1, s2)) engine = pbxt; select * from information_schema.table_constraints where constraint_type = 'FOREIGN KEY' and table_name = 't2'; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE NULL test FOREIGN_1 test t2 FOREIGN KEY select * from information_schema.referential_constraints where table_name = 't2'; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME NULL test FOREIGN_1 NULL test ix1 NONE RESTRICT RESTRICT t2 t1 drop table t2, t1; create table t1 (s1 int, s2 int, unique key ix1 (s1, s2), unique key ix2 (s1, s2)) engine = pbxt; create table t2 (s1 int, s2 int, foreign key (s1, s2) references t1 (s1, s2)) engine = pbxt; select * from information_schema.table_constraints where constraint_type = 'FOREIGN KEY' and table_name = 't2'; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE NULL test FOREIGN_1 test t2 FOREIGN KEY select * from information_schema.referential_constraints where table_name = 't2'; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME NULL test FOREIGN_1 NULL test ix1 NONE RESTRICT RESTRICT t2 t1 drop table t2, t1; create table t1 (s1 int, s2 int, unique key ix1 (s1, s2)) engine = pbxt; create table t2 (s1 int, s2 int, foreign key (s1, s2) references t1 (s1, s2) on delete cascade on update set null) engine = pbxt; select * from information_schema.table_constraints where constraint_type = 'FOREIGN KEY' and table_name = 't2'; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE NULL test FOREIGN_1 test t2 FOREIGN KEY select * from information_schema.referential_constraints where table_name = 't2'; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME NULL test FOREIGN_1 NULL test ix1 NONE SET NULL CASCADE t2 t1 drop table t2, t1; create table t2 (s1 int, s2 int, s3 int, s4 int, unique key ix34 (s3, s4), foreign key (s1, s2) references t2 (s3, s4)) engine = pbxt; select * from information_schema.table_constraints where constraint_type = 'FOREIGN KEY' and table_name = 't2'; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE NULL test FOREIGN_1 test t2 FOREIGN KEY select * from information_schema.referential_constraints where table_name = 't2'; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME NULL test FOREIGN_1 NULL test ix34 NONE RESTRICT RESTRICT t2 t2 drop table t2; create table t1 (s1 int, s2 int, unique key ix1 (s1, s2)) engine = pbxt; create table t2 (s1 int, s2 int, foreign key (s1, s2) references t1 (s1, s2)) engine = pbxt; alter table t1 add constraint s2 foreign key (s1, s2) references t2 (s1, s2); select * from information_schema.table_constraints where constraint_type = 'FOREIGN KEY' and (table_name = 't1' or table_name = 't2'); CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE NULL test s2 test t1 FOREIGN KEY NULL test FOREIGN_1 test t2 FOREIGN KEY select * from information_schema.referential_constraints where (table_name = 't1' or table_name = 't2'); CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME NULL test s2 NULL test s1 NONE RESTRICT RESTRICT t1 t2 NULL test FOREIGN_1 NULL test ix1 NONE RESTRICT RESTRICT t2 t1 set foreign_key_checks = 0; drop table t2, t1; set foreign_key_checks = 1; create table t1 (id int primary key, s1 int, foreign key (s1) references t1 (id)) engine = pbxt; select * from information_schema.table_constraints where constraint_type = 'FOREIGN KEY' and table_name = 't1'; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE NULL test FOREIGN_1 test t1 FOREIGN KEY select * from information_schema.referential_constraints where table_name = 't1'; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME NULL test FOREIGN_1 NULL test PRIMARY NONE RESTRICT RESTRICT t1 t1 drop table t1; drop table if exists t2,t1; create table t1 (s1 int primary key) engine = pbxt; create table t2 (s1 int primary key, foreign key (s1) references t1 (s1) on update cascade) engine = pbxt; set @@autocommit = 0; insert into t1 values (1); insert into t2 values (1); update t1 set s1 = 2; set @@autocommit = 1; select * from t1, t2; s1 s1 2 2 drop table t2, t1; create table t1 (s1 int primary key, s2 int, key (s1, s2)) engine = pbxt; create table t2 (s1 int primary key, s2 int, key (s1, s2), foreign key (s1) references t1 (s1) on update cascade) engine = pbxt; set @@autocommit = 0; insert into t1 values (1, 5); insert into t2 values (1, 6); update t1 set s1 = 2; set @@autocommit = 1; select * from t1, t2; s1 s2 s1 s2 2 5 2 6 drop table t2, t1; set @@autocommit = 1; SET foreign_key_checks = 0; DROP TABLE IF EXISTS t15,t14,t13,t12,t11,t10,t9,t8,t7,t6,t5,t4,t3,t2,t1; SET foreign_key_checks = 1; CREATE TABLE t1 (s1 INT PRIMARY KEY, s2 INT) engine = pbxt; CREATE TABLE t2 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t1 (s1) ON UPDATE CASCADE) engine = pbxt; CREATE TABLE t3 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t2 (s1) ON UPDATE CASCADE) engine = pbxt; CREATE TABLE t4 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t3 (s1) ON UPDATE CASCADE) engine = pbxt; CREATE TABLE t5 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t4 (s1) ON UPDATE CASCADE) engine = pbxt; CREATE TABLE t6 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t5 (s1) ON UPDATE CASCADE) engine = pbxt; CREATE TABLE t7 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t6 (s1) ON UPDATE CASCADE) engine = pbxt; CREATE TABLE t8 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t7 (s1) ON UPDATE CASCADE) engine = pbxt; CREATE TABLE t9 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t8 (s1) ON UPDATE CASCADE) engine = pbxt; CREATE TABLE t10(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t9 (s1) ON UPDATE CASCADE) engine = pbxt; CREATE TABLE t11(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t10(s1) ON UPDATE CASCADE) engine = pbxt; CREATE TABLE t12(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t11(s1) ON UPDATE CASCADE) engine = pbxt; CREATE TABLE t13(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t12(s1) ON UPDATE CASCADE) engine = pbxt; CREATE TABLE t14(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t13(s1) ON UPDATE CASCADE) engine = pbxt; CREATE TABLE t15(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t14(s1) ON UPDATE CASCADE) engine = pbxt; ALTER TABLE t1 ADD FOREIGN KEY (s2) REFERENCES t15(s1) ON UPDATE CASCADE; SET foreign_key_checks = 0; INSERT INTO t1 VALUES (1,NULL); INSERT INTO t2 VALUES (1); INSERT INTO t3 VALUES (1); INSERT INTO t4 VALUES (1); INSERT INTO t5 VALUES (1); INSERT INTO t6 VALUES (1); INSERT INTO t7 VALUES (1); INSERT INTO t8 VALUES (1); INSERT INTO t9 VALUES (1); INSERT INTO t10 VALUES (1); INSERT INTO t11 VALUES (1); INSERT INTO t12 VALUES (1); INSERT INTO t13 VALUES (1); INSERT INTO t14 VALUES (1); INSERT INTO t15 VALUES (1); SET foreign_key_checks = 1; UPDATE t1 SET s1 = 2; select * from t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15; s1 s2 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 2 NULL 2 2 2 2 2 2 2 2 2 2 2 2 2 2 UPDATE t1 SET s2 = 2; select * from t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15; s1 s2 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 UPDATE t1 SET s1 = 3; select * from t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15; s1 s2 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 SET foreign_key_checks = 0; DROP TABLE IF EXISTS t15,t14,t13,t12,t11,t10,t9,t8,t7,t6,t5,t4,t3,t2,t1; SET foreign_key_checks = 1; DROP TABLE IF EXISTS t2,t1; CREATE TABLE t1 (s1 ENUM('a','b') PRIMARY KEY) engine = pbxt; CREATE TABLE t2 (s1 ENUM('A','B'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt; DROP TABLE t2,t1; CREATE TABLE t1 (s1 ENUM('a','b') PRIMARY KEY) engine = pbxt; CREATE TABLE t2 (s1 ENUM('A','B', 'C'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt; ERROR HY000: Can't create table 'test.t2' (errno: 151) DROP TABLE t1; CREATE TABLE t1 (s1 ENUM('a','b','c') PRIMARY KEY) engine = pbxt; CREATE TABLE t2 (s1 ENUM('d','e','f'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt; DROP TABLE t2,t1; CREATE TABLE t1 (s1 SET('a','b') PRIMARY KEY) engine = pbxt; CREATE TABLE t2 (s1 SET('A','B'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt; DROP TABLE t2,t1; CREATE TABLE t1 (s1 SET('a','b') PRIMARY KEY) engine = pbxt; CREATE TABLE t2 (s1 SET('A','B', 'C'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt; ERROR HY000: Can't create table 'test.t2' (errno: 151) DROP TABLE t1; CREATE TABLE t1 (s1 SET('a','b','c') PRIMARY KEY) engine = pbxt; CREATE TABLE t2 (s1 SET('d','e','f'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt; DROP TABLE t2,t1; CREATE TABLE t1 (s1 SET('a','b') PRIMARY KEY) engine = pbxt; CREATE TABLE t2 (s1 ENUM('a','b'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt; ERROR HY000: Can't create table 'test.t2' (errno: 151) DROP TABLE t1; CREATE TABLE t1 (s1 ENUM('a','b') PRIMARY KEY) engine = pbxt; CREATE TABLE t2 (s1 SET('a','b'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt; ERROR HY000: Can't create table 'test.t2' (errno: 151) DROP TABLE t1; DROP TABLE IF EXISTS t2, t1; Warnings: Note 1051 Unknown table 't2' Note 1051 Unknown table 't1' CREATE TABLE t1 (s1 VARCHAR(50) PRIMARY KEY) engine = pbxt; CREATE TABLE t2 (s1 VARCHAR(50), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt; INSERT INTO t1 VALUES ('A'); INSERT INTO t2 VALUES ('A '); UPDATE t1 SET s1 = 'A '; DELETE FROM t2; DELETE FROM t1; INSERT INTO t1 VALUES ('A'); INSERT INTO t2 VALUES ('A'); UPDATE t1 SET s1 = 'A '; UPDATE t2 SET s1 = 'A '; UPDATE t1 SET s1 = 'a'; UPDATE t2 SET s1 = 'a'; DROP TABLE t2, t1; DROP TABLE IF EXISTS t2,t1; Warnings: Note 1051 Unknown table 't2' Note 1051 Unknown table 't1' CREATE TABLE t1 (s1 INT PRIMARY KEY); CREATE TABLE t2 (s1 INT DEFAULT 2, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT); INSERT INTO t1 VALUES (1),(2); INSERT INTO t2 VALUES (1); DELETE FROM t1 WHERE s1 = 1; SELECT * FROM t2; s1 2 DROP TABLE IF EXISTS t2,t1; CREATE TABLE t1 (s1 DATE NOT NULL UNIQUE); CREATE TABLE t2 (s1 DATE DEFAULT '2000-01-01', FOREIGN KEY (s1) REFERENCES t1 (s1) ON UPDATE SET DEFAULT); INSERT INTO t1 VALUES ('2001-01-01'); INSERT INTO t2 VALUES ('2001-01-01'); UPDATE t1 SET s1 = '2001-01-02'; SELECT * FROM t2; s1 2000-01-01 DROP TABLE IF EXISTS t2,t1; CREATE TABLE t1 (s1 INT PRIMARY KEY); CREATE TABLE t2 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT); INSERT INTO t1 VALUES (1),(2); INSERT INTO t2 VALUES (1); DELETE FROM t1 WHERE s1 = 1; SELECT * FROM t2; s1 NULL DROP TABLE IF EXISTS t2,t1; CREATE TABLE t1 (s1 VARCHAR(45) primary key); CREATE TABLE t2 (s1 VARCHAR(45) DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT); INSERT INTO t1 VALUES (1),(2); INSERT INTO t2 VALUES (1); DELETE FROM t1 WHERE s1 = 1; SELECT * FROM t2; s1 NULL DROP TABLE IF EXISTS t2,t1; CREATE TABLE t1 (s1 VARCHAR(45), INDEX (s1(10))); CREATE TABLE t2 (s1 VARCHAR(45) DEFAULT "12345678901", FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT); INSERT INTO t1 VALUES (1),(2); INSERT INTO t2 VALUES (1); DELETE FROM t1 WHERE s1 = 1; SELECT * FROM t2; s1 12345678901 DROP TABLE IF EXISTS t2,t1; CREATE TABLE t1 (s1 INT); CREATE TABLE t2 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT); INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (1); ERROR HY000: Got temporary error -1 'Matching index required for '`t1` (s1)'' from PBXT DROP TABLE IF EXISTS t3,t2,t1; CREATE TABLE t1 (s1 INT primary key); CREATE TABLE t2 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET NULL); CREATE TABLE t3 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t2 (s1) ON DELETE SET NULL); INSERT INTO t1 VALUES (1),(2); INSERT INTO t2 VALUES (1); INSERT INTO t3 VALUES (1); DELETE FROM t1 WHERE s1 = 1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `FOREIGN_1`) SELECT * FROM t1; s1 1 2 SELECT * FROM t2; s1 1 SELECT * FROM t3; s1 1 DROP TABLE IF EXISTS t3,t2,t1; CREATE TABLE t1 (s1 INT primary key); CREATE TABLE t2 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT); CREATE TABLE t3 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t2 (s1) ON DELETE SET DEFAULT); INSERT INTO t1 VALUES (1),(2); INSERT INTO t2 VALUES (1); INSERT INTO t3 VALUES (1); DELETE FROM t1 WHERE s1 = 1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `FOREIGN_1`) SELECT * FROM t1; s1 1 2 SELECT * FROM t2; s1 1 SELECT * FROM t3; s1 1 DROP TABLE IF EXISTS t3,t2,t1; DROP TABLE IF EXISTS t2,t1; CREATE TABLE t1 (s1 INT PRIMARY KEY, s2 char(1)); CREATE TABLE t2 (s1 INT, s2 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE CASCADE); INSERT INTO t1 VALUES (1,'a'); INSERT INTO t2 VALUES (1,1); REPLACE INTO t1 VALUES (1,'b'); SELECT COUNT(*) FROM t2; COUNT(*) 0 set foreign_key_checks = 0; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (s1 INT PRIMARY KEY, s2 char(1)); set foreign_key_checks = 1; INSERT INTO t1 VALUES (1,'a'); REPLACE INTO t1 VALUES (1,'b'); DROP TABLE IF EXISTS t3,t2,t1; CREATE TABLE t1 (s1 INT PRIMARY KEY, s2 INT); CREATE TABLE t2 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t1 (s1) ON UPDATE CASCADE); CREATE TABLE t3 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t2 (s1) ON UPDATE CASCADE); DROP TABLE IF EXISTS t3,t2,t1; CREATE TABLE t1 (s1 ENUM('a','b') PRIMARY KEY); CREATE TABLE t2 (s1 ENUM('A','B','C'), FOREIGN KEY (s1) REFERENCES t1 (s1)); ERROR HY000: Can't create table 'test.t2' (errno: 151) DROP TABLE IF EXISTS t2,t1; Warnings: Note 1051 Unknown table 't2' CREATE TABLE t1 (s1 INT PRIMARY KEY, s2 INT); CREATE TABLE t2 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t1 (s1) ON UPDATE CASCADE); DROP TABLE IF EXISTS t2,t1; create table t1 (s1 varchar(40) primary key); create table t2 (s1 VARCHAR(30), foreign key (s1) references t1 (s1)); insert into t1 values ("1"); insert into t2 values ("1"); DROP TABLE IF EXISTS t2,t1; DROP TABLE IF EXISTS t5; CREATE TABLE t5 ( c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, c2 BIGINT SIGNED NULL, c3 BIGINT SIGNED NOT NULL, c4 TINYINT, c5 SMALLINT, c6 MEDIUMINT, c7 INT, c8 INTEGER, PRIMARY KEY(c1,c2), UNIQUE INDEX(c3)); INSERT INTO t5 VALUES (0,-9223372036854775808,1,2,3,4,5,5), (255,-2147483648,6,7,8,9,10,10), (65535,-8388608,11,12,13,14,15,15), (16777215,-32768,16,17,18,19,20,20), (4294967295,-128,21,22,23,24,25,25), (18446744073709551615,9223372036854775807,26,27,28,29,30,30); INSERT INTO t5(c2,c3) VALUES(33,34) /* tries to increment out of range */; ERROR HY000: Failed to read auto-increment value from storage engine INSERT INTO t5(c2,c3) VALUES(33,34); ERROR HY000: Failed to read auto-increment value from storage engine SELECT * FROM t5; c1 c2 c3 c4 c5 c6 c7 c8 1 -9223372036854775808 1 2 3 4 5 5 255 -2147483648 6 7 8 9 10 10 65535 -8388608 11 12 13 14 15 15 16777215 -32768 16 17 18 19 20 20 4294967295 -128 21 22 23 24 25 25 18446744073709551615 9223372036854775807 26 27 28 29 30 30 DROP TABLE t5; /* same test as above with signed bigint */ CREATE TABLE t5 ( c1 BIGINT SIGNED NOT NULL AUTO_INCREMENT, c2 BIGINT SIGNED NULL, c3 BIGINT SIGNED NOT NULL, c4 TINYINT, c5 SMALLINT, c6 MEDIUMINT, c7 INT, c8 INTEGER, PRIMARY KEY(c1,c2), UNIQUE INDEX(c3)); INSERT INTO t5 VALUES (0,-9223372036854775808,1,2,3,4,5,5), (255,-2147483648,6,7,8,9,10,10), (65535,-8388608,11,12,13,14,15,15), (16777215,-32768,16,17,18,19,20,20), (4294967295,-128,21,22,23,24,25,25), (9223372036854775807,9223372036854775807,26,27,28,29,30,30); INSERT INTO t5(c2,c3) VALUES(33,34) /* tries to increment out of range */; ERROR HY000: Failed to read auto-increment value from storage engine INSERT INTO t5(c2,c3) VALUES(33,34); ERROR HY000: Failed to read auto-increment value from storage engine SELECT * FROM t5; c1 c2 c3 c4 c5 c6 c7 c8 1 -9223372036854775808 1 2 3 4 5 5 255 -2147483648 6 7 8 9 10 10 65535 -8388608 11 12 13 14 15 15 16777215 -32768 16 17 18 19 20 20 4294967295 -128 21 22 23 24 25 25 9223372036854775807 9223372036854775807 26 27 28 29 30 30 CREATE TABLE t2(c1 INT SIGNED NOT NULL, c2 INT UNSIGNED NULL, c3 INT, KEY(c1), KEY(c2)); INSERT INTO t2 VALUES(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(-7,7,7),(-8,8,8),(-9,9,9),(10,10,10),(-11,NULL,11),(-12,12,12); INSERT INTO t2 VALUES(-2147483648,0,13),(2147483647,4294967295,14),(0,2147483648,15),(2147483647,2147483647,16); SELECT * FROM t2; c1 c2 c3 -1 1 1 -11 NULL 11 -12 12 12 -2 2 2 -2147483648 0 13 -3 3 3 -4 4 4 -5 5 5 -6 6 6 -7 7 7 -8 8 8 -9 9 9 0 2147483648 15 10 10 10 2147483647 2147483647 16 2147483647 4294967295 14 EXPLAIN SELECT c1 FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL c1 4 NULL 16 Using index SELECT c1 FROM t2; c1 -1 -11 -12 -2 -2147483648 -3 -4 -5 -6 -7 -8 -9 0 10 2147483647 2147483647 UPDATE t2 SET c1=-2147483648 WHERE c2 <> 0 ORDER BY c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <> 0 ORDER BY c2; c1 c2 c3 -12 12 12 -2147483648 1 1 -2147483648 2 2 -3 3 3 -4 4 4 -5 5 5 -6 6 6 -7 7 7 -8 8 8 -9 9 9 0 2147483648 15 10 10 10 2147483647 2147483647 16 2147483647 4294967295 14 UPDATE t2 SET c1=-2147483648 WHERE c2 >= 0 ORDER BY c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= 0 ORDER BY c2 DESC; c1 c2 c3 -12 12 12 -2147483648 0 13 -2147483648 1 1 -2147483648 2 2 -2147483648 2147483648 15 -2147483648 4294967295 14 -3 3 3 -4 4 4 -5 5 5 -6 6 6 -7 7 7 -8 8 8 -9 9 9 10 10 10 2147483647 2147483647 16 UPDATE t2 SET c1=-2147483648 WHERE c2 <= 3 ORDER BY c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <= 3 ORDER BY c2; c1 c2 c3 -2147483648 0 13 -2147483648 1 1 -2147483648 2 2 -3 3 3 UPDATE t2 SET c1=-2147483648 WHERE c2 <=> 4 ORDER BY c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <=> 4 ORDER BY c2; c1 c2 c3 -2147483648 4 4 UPDATE t2 SET c1=-2147483648 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2 LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2; c1 c2 c3 -2147483648 4 4 -2147483648 5 5 -6 6 6 -7 7 7 UPDATE t2 SET c1=-2147483648 WHERE c2 IN(8,9) ORDER BY c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IN(8,9) ORDER BY c2 DESC; c1 c2 c3 -2147483648 8 8 -2147483648 9 9 UPDATE t2 SET c1=-2147483648 WHERE c2 IS NULL ORDER BY c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2; c1 c2 c3 -2147483648 NULL 11 UPDATE t2 SET c1=-2147483648 WHERE c2>= 6 AND c2 < 9 ORDER BY c2 LIMIT 2; SELECT * FROM t2 WHERE c2>= 6 AND c2 < 9 ORDER BY c2; c1 c2 c3 -2147483648 6 6 -2147483648 7 7 -2147483648 8 8 UPDATE t2 SET c1=-2147483648 WHERE c1=-12 OR c2=1; SELECT * FROM t2 WHERE c1=-2147483648; c1 c2 c3 -2147483648 0 13 -2147483648 1 1 -2147483648 12 12 -2147483648 2 2 -2147483648 2147483648 15 -2147483648 4 4 -2147483648 4294967295 14 -2147483648 5 5 -2147483648 6 6 -2147483648 7 7 -2147483648 8 8 -2147483648 9 9 -2147483648 NULL 11 SELECT * FROM t2; c1 c2 c3 -2147483648 0 13 -2147483648 1 1 -2147483648 12 12 -2147483648 2 2 -2147483648 2147483648 15 -2147483648 4 4 -2147483648 4294967295 14 -2147483648 5 5 -2147483648 6 6 -2147483648 7 7 -2147483648 8 8 -2147483648 9 9 -2147483648 NULL 11 -3 3 3 10 10 10 2147483647 2147483647 16 EXPLAIN SELECT c1 FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL c1 4 NULL 16 Using index SELECT c1 FROM t2; c1 -2147483648 -2147483648 -2147483648 -2147483648 -2147483648 -2147483648 -2147483648 -2147483648 -2147483648 -2147483648 -2147483648 -2147483648 -2147483648 -3 10 2147483647 DROP TABLE IF EXISTS t1; CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 VARCHAR(255)); LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/std_data/pbxt_load_unique_error1.inc' REPLACE INTO TABLE t1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (@c1,c2) SET c1 = @c1 % 2; SELECT * FROM t1 ORDER BY c1; c1 c2 0 opq 1 jkl DROP TABLE t1; create table parent (id int primary key); create table child (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES parent(id)); insert into parent values (2), (3), (4); insert into child values (3), (4); delete ignore from parent; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `FOREIGN_1`) select * from parent; id 2 3 4 drop table child, parent; create schema test378222; use test378222; create table t1 (id int primary key); create table t2 (id int primary key); alter table t1 add constraint foreign key (id) references t2 (id); alter table t2 add constraint foreign key (id) references t1 (id); drop schema test378222; create schema test378222a; create schema test378222b; create table test378222a.t1 (id int primary key); create table test378222b.t2 (id int primary key); alter table test378222a.t1 add constraint foreign key (id) references test378222b.t2 (id); alter table test378222b.t2 add constraint foreign key (id) references test378222a.t1 (id); set foreign_key_checks = 1; drop schema test378222a; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails drop schema test378222b; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails set foreign_key_checks = 0; drop schema test378222a; drop schema test378222b; set foreign_key_checks = 1; use test; CREATE TABLE t1(c1 TINYINT AUTO_INCREMENT NULL KEY ) AUTO_INCREMENT=10; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` tinyint(4) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=PBXT AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES(null); INSERT INTO t1 VALUES(null); INSERT INTO t1 VALUES(null); SELECT * FROM t1; c1 10 11 12 TRUNCATE TABLE t1; INSERT INTO t1 VALUES(null); SELECT * FROM t1; c1 1 DROP TABLE t1;