Skip to content

Instantly share code, notes, and snippets.

@yoku0825

yoku0825/PoC_for_bug78347.test

Created September 30, 2015 06:48
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save yoku0825/57ba8a8788b792e0ca39 to your computer and use it in GitHub Desktop.
Save yoku0825/57ba8a8788b792e0ca39 to your computer and use it in GitHub Desktop.
Download ZIP
PoC for [MySQL Bugs: #78347: innodb_default_row_format: Undesireable new behaviour](https://bugs.mysql.com/bug.php?id=78347)
Raw
PoC_for_bug78347.test
--disable_warnings
DROP DATABASE test;
CREATE DATABASE test;
USE test;
--enable_warnings
SET GLOBAL innodb_default_row_format= 'Compact';
# with pkey, second-col is int
CREATE TABLE int_pkey_add_col (c1 int NOT NULL, c2 int, PRIMARY KEY(c1));
CREATE TABLE int_pkey_drop_col (c1 int NOT NULL, c2 int, PRIMARY KEY(c1));
CREATE TABLE int_pkey_add_idx (c1 int NOT NULL, c2 int, PRIMARY KEY(c1));
CREATE TABLE int_pkey_add_uidx (c1 int NOT NULL, c2 int, PRIMARY KEY(c1));
CREATE TABLE int_pkey_add_idx_copy (c1 int NOT NULL, c2 int, PRIMARY KEY(c1));
CREATE TABLE int_pkey_add_fidx (c1 int NOT NULL, c2 int, PRIMARY KEY(c1));
CREATE TABLE int_pkey_chg_col_to_int (c1 int NOT NULL, c2 int, PRIMARY KEY(c1));
CREATE TABLE int_pkey_chg_col_to_uint (c1 int NOT NULL, c2 int, PRIMARY KEY(c1));
CREATE TABLE int_pkey_chg_col_to_big (c1 int NOT NULL, c2 int, PRIMARY KEY(c1));
CREATE TABLE int_pkey_chg_col_to_vchar (c1 int NOT NULL, c2 int, PRIMARY KEY(c1));
CREATE TABLE int_pkey_optimize (c1 int NOT NULL, c2 int, PRIMARY KEY(c1));
CREATE TABLE int_pkey_drop_pkey (c1 int NOT NULL, c2 int, PRIMARY KEY(c1));
CREATE TABLE int_pkey_set_default (c1 int NOT NULL, c2 int, PRIMARY KEY(c1));
# with pkey, second-col is varchar
CREATE TABLE vchar32_pkey_add_col (c1 int NOT NULL, c2 varchar(32), PRIMARY KEY(c1));
CREATE TABLE vchar32_pkey_drop_col (c1 int NOT NULL, c2 varchar(32), PRIMARY KEY(c1));
CREATE TABLE vchar32_pkey_add_idx (c1 int NOT NULL, c2 varchar(32), PRIMARY KEY(c1));
CREATE TABLE vchar32_pkey_add_uidx (c1 int NOT NULL, c2 varchar(32), PRIMARY KEY(c1));
CREATE TABLE vchar32_pkey_add_idx_copy (c1 int NOT NULL, c2 varchar(32), PRIMARY KEY(c1));
CREATE TABLE vchar32_pkey_add_fidx (c1 int NOT NULL, c2 varchar(32), PRIMARY KEY(c1));
CREATE TABLE vchar32_pkey_add_ft (c1 int NOT NULL, c2 varchar(32), PRIMARY KEY(c1));
CREATE TABLE vchar32_pkey_chg_col_to_vchar32 (c1 int NOT NULL, c2 varchar(32), PRIMARY KEY(c1));
CREATE TABLE vchar32_pkey_chg_col_to_vchar64 (c1 int NOT NULL, c2 varchar(32), PRIMARY KEY(c1));
CREATE TABLE vchar32_pkey_chg_col_to_vchar256 (c1 int NOT NULL, c2 varchar(32), PRIMARY KEY(c1));
CREATE TABLE vchar32_pkey_chg_col_to_int (c1 int NOT NULL, c2 varchar(32), PRIMARY KEY(c1));
CREATE TABLE vchar32_pkey_optimize (c1 int NOT NULL, c2 varchar(32), PRIMARY KEY(c1));
CREATE TABLE vchar32_pkey_drop_pkey (c1 int NOT NULL, c2 varchar(32), PRIMARY KEY(c1));
CREATE TABLE vchar32_pkey_set_default (c1 int NOT NULL, c2 varchar(32), PRIMARY KEY(c1));
# without pkey, second-col is int
CREATE TABLE int_nopkey_add_col (c1 int NOT NULL, c2 int);
CREATE TABLE int_nopkey_drop_col (c1 int NOT NULL, c2 int);
CREATE TABLE int_nopkey_add_idx (c1 int NOT NULL, c2 int);
CREATE TABLE int_nopkey_add_uidx (c1 int NOT NULL, c2 int);
CREATE TABLE int_nopkey_add_idx_copy (c1 int NOT NULL, c2 int);
CREATE TABLE int_nopkey_add_fidx (c1 int NOT NULL, c2 int);
CREATE TABLE int_nopkey_chg_col_to_int (c1 int NOT NULL, c2 int);
CREATE TABLE int_nopkey_chg_col_to_uint (c1 int NOT NULL, c2 int);
CREATE TABLE int_nopkey_chg_col_to_big (c1 int NOT NULL, c2 int);
CREATE TABLE int_nopkey_chg_col_to_vchar (c1 int NOT NULL, c2 int);
CREATE TABLE int_nopkey_optimize (c1 int NOT NULL, c2 int);
CREATE TABLE int_nopkey_add_pkey (c1 int NOT NULL, c2 int);
CREATE TABLE int_nopkey_set_default (c1 int NOT NULL, c2 int);
# without pkey, second-col is varchar
CREATE TABLE vchar32_nopkey_add_col (c1 int NOT NULL, c2 varchar(32));
CREATE TABLE vchar32_nopkey_drop_col (c1 int NOT NULL, c2 varchar(32));
CREATE TABLE vchar32_nopkey_add_idx (c1 int NOT NULL, c2 varchar(32));
CREATE TABLE vchar32_nopkey_add_uidx (c1 int NOT NULL, c2 varchar(32));
CREATE TABLE vchar32_nopkey_add_idx_copy (c1 int NOT NULL, c2 varchar(32));
CREATE TABLE vchar32_nopkey_add_fidx (c1 int NOT NULL, c2 varchar(32));
CREATE TABLE vchar32_nopkey_add_ft (c1 int NOT NULL, c2 varchar(32));
CREATE TABLE vchar32_nopkey_chg_col_to_vchar32 (c1 int NOT NULL, c2 varchar(32));
CREATE TABLE vchar32_nopkey_chg_col_to_vchar64 (c1 int NOT NULL, c2 varchar(32));
CREATE TABLE vchar32_nopkey_chg_col_to_vchar256 (c1 int NOT NULL, c2 varchar(32));
CREATE TABLE vchar32_nopkey_chg_col_to_int (c1 int NOT NULL, c2 varchar(32));
CREATE TABLE vchar32_nopkey_optimize (c1 int NOT NULL, c2 varchar(32));
CREATE TABLE vchar32_nopkey_add_pkey (c1 int NOT NULL, c2 varchar(32));
CREATE TABLE vchar32_nopkey_set_default (c1 int NOT NULL, c2 varchar(32));
CREATE TABLE rename_table_pkey (c1 int NOT NULL, PRIMARY KEY(c1));
CREATE TABLE rename_table_nopkey (c1 int NOT NULL);
CREATE TABLE foreign_key_int (c1 int NOT NULL PRIMARY KEY);
CREATE TABLE foreign_key_vchar (c1 varchar(32) NOT NULL PRIMARY KEY);
SELECT table_id, name, row_format FROM information_schema.innodb_sys_tables WHERE name LIKE 'test/%' ORDER BY name;
SET GLOBAL innodb_default_row_format= 'Dynamic';
ALTER TABLE int_pkey_add_col ADD c3 int;
ALTER TABLE int_pkey_drop_col DROP c2;
ALTER TABLE int_pkey_add_idx ADD KEY(c2);
ALTER TABLE int_pkey_add_uidx ADD UNIQUE KEY(c2);
ALTER TABLE int_pkey_add_idx_copy ADD KEY(c2), ALGORITHM= COPY;
ALTER TABLE int_pkey_add_fidx ADD FOREIGN KEY(c2) REFERENCES foreign_key_int(c1);
ALTER TABLE int_pkey_chg_col_to_int CHANGE c2 c3 int;
ALTER TABLE int_pkey_chg_col_to_uint CHANGE c2 c3 int unsigned;
ALTER TABLE int_pkey_chg_col_to_big CHANGE c2 c3 bigint;
ALTER TABLE int_pkey_chg_col_to_vchar CHANGE c2 c3 varchar(32);
ALTER TABLE int_pkey_optimize Engine= InnoDB;
ALTER TABLE int_pkey_drop_pkey DROP PRIMARY KEY;
ALTER TABLE int_pkey_set_default ALTER c2 SET DEFAULT 0;
ALTER TABLE vchar32_pkey_add_col ADD c3 int;
ALTER TABLE vchar32_pkey_drop_col DROP c2;
ALTER TABLE vchar32_pkey_add_idx ADD KEY(c2);
ALTER TABLE vchar32_pkey_add_uidx ADD UNIQUE KEY(c2);
ALTER TABLE vchar32_pkey_add_idx_copy ADD KEY(c2), ALGORITHM= COPY;
ALTER TABLE vchar32_pkey_add_fidx ADD FOREIGN KEY(c2) REFERENCES foreign_key_vchar(c1);
ALTER TABLE vchar32_pkey_add_ft ADD FULLTEXT KEY(c2);
ALTER TABLE vchar32_pkey_chg_col_to_vchar32 CHANGE c2 c3 varchar(32);
ALTER TABLE vchar32_pkey_chg_col_to_vchar64 CHANGE c2 c3 varchar(32);
ALTER TABLE vchar32_pkey_chg_col_to_vchar256 CHANGE c2 c3 varchar(256);
ALTER TABLE vchar32_pkey_chg_col_to_int CHANGE c2 c3 int NOT NULL;
ALTER TABLE vchar32_pkey_optimize Engine= InnoDB;
ALTER TABLE vchar32_pkey_drop_pkey DROP PRIMARY KEY;
ALTER TABLE vchar32_pkey_set_default ALTER c2 SET DEFAULT '';
ALTER TABLE int_nopkey_add_col ADD c3 int;
ALTER TABLE int_nopkey_drop_col DROP c2;
ALTER TABLE int_nopkey_add_idx ADD KEY(c2);
ALTER TABLE int_nopkey_add_uidx ADD UNIQUE KEY(c2);
ALTER TABLE int_nopkey_add_idx_copy ADD KEY(c2), ALGORITHM= COPY;
ALTER TABLE int_nopkey_add_fidx ADD FOREIGN KEY(c2) REFERENCES foreign_key_int(c1);
ALTER TABLE int_nopkey_chg_col_to_int CHANGE c2 c3 int;
ALTER TABLE int_nopkey_chg_col_to_uint CHANGE c2 c3 int unsigned;
ALTER TABLE int_nopkey_chg_col_to_big CHANGE c2 c3 bigint;
ALTER TABLE int_nopkey_chg_col_to_vchar CHANGE c2 c3 varchar(32);
ALTER TABLE int_nopkey_optimize Engine= InnoDB;
ALTER TABLE int_nopkey_add_pkey ADD PRIMARY KEY(c1);
ALTER TABLE int_nopkey_set_default ALTER c2 SET DEFAULT 0;
ALTER TABLE vchar32_nopkey_add_col ADD c3 int;
ALTER TABLE vchar32_nopkey_drop_col DROP c2;
ALTER TABLE vchar32_nopkey_add_idx ADD KEY(c2);
ALTER TABLE vchar32_nopkey_add_uidx ADD UNIQUE KEY(c2);
ALTER TABLE vchar32_nopkey_add_idx_copy ADD KEY(c2), ALGORITHM= COPY;
ALTER TABLE vchar32_nopkey_add_fidx ADD FOREIGN KEY(c2) REFERENCES foreign_key_vchar(c1);
ALTER TABLE vchar32_nopkey_add_ft ADD FULLTEXT KEY(c2);
ALTER TABLE vchar32_nopkey_chg_col_to_vchar32 CHANGE c2 c3 varchar(32);
ALTER TABLE vchar32_nopkey_chg_col_to_vchar64 CHANGE c2 c3 varchar(32);
ALTER TABLE vchar32_nopkey_chg_col_to_vchar256 CHANGE c2 c3 varchar(256);
ALTER TABLE vchar32_nopkey_chg_col_to_int CHANGE c2 c3 int NOT NULL;
ALTER TABLE vchar32_nopkey_optimize Engine= InnoDB;
ALTER TABLE vchar32_nopkey_add_pkey ADD PRIMARY KEY(c1);
ALTER TABLE vchar32_nopkey_set_default ALTER c2 SET DEFAULT '';
ALTER TABLE rename_table_pkey RENAME TO renamed_table_pkey;
ALTER TABLE rename_table_nopkey RENAME TO renamed_table_nopkey;
SELECT table_id, name, row_format FROM information_schema.innodb_sys_tables WHERE name LIKE 'test/%' ORDER BY name;
SET GLOBAL innodb_default_row_format= default;
DROP DATABASE test;
CREATE DATABASE test;
USE test;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment