1# Tests for various combinations of ROW_FORMAT and KEY_BLOCK_SIZE 2# Related bugs; 3# Bug#54679: ALTER TABLE causes compressed row_format to revert to compact 4# Bug#56628: ALTER TABLE .. KEY_BLOCK_SIZE=0 produces untrue warning or unnecessary error 5# Bug#56632: ALTER TABLE implicitly changes ROW_FORMAT to COMPRESSED 6# Rules for interpreting CREATE_OPTIONS 7# 1) Create options on an ALTER are added to the options on the 8# previous CREATE or ALTER statements. 9# 2) KEY_BLOCK_SIZE=0 is considered a unspecified value. 10# If the current ROW_FORMAT has explicitly been set to COMPRESSED, 11# InnoDB will use a default value of 8. Otherwise KEY_BLOCK_SIZE 12# will not be used. 13# 3) ROW_FORMAT=DEFAULT allows InnoDB to choose its own default, COMPACT. 14# 4) ROW_FORMAT=DEFAULT and KEY_BLOCK_SIZE=0 can be used at any time to 15# unset or erase the values persisted in the MySQL dictionary and 16# by SHOW CTREATE TABLE. 17# 5) When incompatible values for ROW_FORMAT and KEY_BLOCK_SIZE are 18# both explicitly given, the ROW_FORMAT is always used in non-strict 19# mode. 20# 6) InnoDB will automatically convert a table to COMPRESSED only if a 21# valid non-zero KEY_BLOCK_SIZE has been given and ROW_FORMAT=DEFAULT 22# or has not been used on a previous CREATE TABLE or ALTER TABLE. 23# 7) InnoDB strict mode is designed to prevent incompatible create 24# options from being used together. 25# 8) The non-strict behavior is intended to permit you to import a 26# mysqldump file into a database that does not support compressed 27# tables, even if the source database contained compressed tables. 28# All invalid values and/or incompatible combinations of ROW_FORMAT 29# and KEY_BLOCK_SIZE are automatically corrected 30# 31# *** innodb_strict_mode=ON *** 32# 1) Valid ROW_FORMATs are COMPRESSED, COMPACT, DEFAULT, DYNAMIC 33# & REDUNDANT. All others are rejected. 34# 2) Valid KEY_BLOCK_SIZEs are 0,1,2,4,8,16. All others are rejected. 35# 3) KEY_BLOCK_SIZE=0 can be used to set it to 'unspecified'. 36# 4) KEY_BLOCK_SIZE=1,2,4,8 & 16 are incompatible with COMPACT, DYNAMIC & 37# REDUNDANT. 38# 5) KEY_BLOCK_SIZE=1,2,4,8 & 16 as well as ROW_FORMAT=COMPRESSED and 39# ROW_FORMAT=DYNAMIC are incompatible with innodb_file_format=Antelope 40# and innodb_file_per_table=OFF 41# 6) KEY_BLOCK_SIZE on an ALTER must occur with ROW_FORMAT=COMPRESSED 42# or ROW_FORMAT=DEFAULT if the ROW_FORMAT was previously specified 43# as COMPACT, DYNAMIC or REDUNDANT. 44# 7) KEY_BLOCK_SIZE on an ALTER can occur without a ROW_FORMAT if the 45# previous ROW_FORMAT was DEFAULT, COMPRESSED, or unspecified. 46# 47# *** innodb_strict_mode=OFF *** 48# 1. Ignore a bad KEY_BLOCK_SIZE, defaulting it to 8. 49# 2. Ignore a bad ROW_FORMAT, defaulting to COMPACT. 50# 3. Ignore a valid KEY_BLOCK_SIZE when an incompatible but valid 51# ROW_FORMAT is specified. 52# 4. If innodb_file_format=Antelope or innodb_file_per_table=OFF 53# it will ignore ROW_FORMAT=COMPRESSED or DYNAMIC and it will 54# ignore all non-zero KEY_BLOCK_SIZEs. 55# 56# See InnoDB documentation page "SQL Compression Syntax Warnings and Errors" 57# This test case does not try to create tables with KEY_BLOCK_SIZE > 4 58# since they are rejected for InnoDB page sizes of 8k and 16k. 59# See innodb_16k and innodb_8k for those tests. 60 61-- source include/have_innodb.inc 62SET default_storage_engine=InnoDB; 63 64--disable_query_log 65# These values can change during the test 66LET $innodb_file_format_orig=`select @@innodb_file_format`; 67LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`; 68LET $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`; 69--enable_query_log 70 71SET GLOBAL innodb_file_format=`Barracuda`; 72SET GLOBAL innodb_file_per_table=ON; 73 74# The first half of these tests are with strict mode ON. 75SET SESSION innodb_strict_mode = ON; 76 77--echo # Test 1) StrictMode=ON, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0 78--echo # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified' 79DROP TABLE IF EXISTS t1; 80--echo # 'FIXED' is sent to InnoDB since it is used by MyISAM. 81--echo # But it is an invalid mode in InnoDB 82--error ER_ILLEGAL_HA 83CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED; 84SHOW WARNINGS; 85CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0; 86SHOW WARNINGS; 87SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 88ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0; 89SHOW WARNINGS; 90SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 91ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; 92SHOW WARNINGS; 93SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 94ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0; 95SHOW WARNINGS; 96SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 97ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; 98SHOW WARNINGS; 99SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 100--error ER_ILLEGAL_HA_CREATE_OPTION 101ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; 102SHOW WARNINGS; 103SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 104 105 106 107--echo # Test 2) StrictMode=ON, CREATE with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE 108--echo # KEY_BLOCK_SIZE is incompatible with COMPACT, REDUNDANT, & DYNAMIC 109DROP TABLE IF EXISTS t1; 110--error ER_ILLEGAL_HA 111CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; 112SHOW WARNINGS; 113--error ER_ILLEGAL_HA 114CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; 115SHOW WARNINGS; 116--error ER_ILLEGAL_HA 117CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; 118SHOW WARNINGS; 119CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; 120SHOW WARNINGS; 121SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 122ALTER TABLE t1 ADD COLUMN f1 INT; 123SHOW WARNINGS; 124SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 125DROP TABLE IF EXISTS t1; 126CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1; 127SHOW WARNINGS; 128SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 129ALTER TABLE t1 ADD COLUMN f1 INT; 130SHOW WARNINGS; 131SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 132 133 134--echo # Test 3) StrictMode=ON, ALTER with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE 135DROP TABLE IF EXISTS t1; 136CREATE TABLE t1 ( i INT ); 137--error ER_ILLEGAL_HA_CREATE_OPTION 138ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; 139SHOW WARNINGS; 140--error ER_ILLEGAL_HA_CREATE_OPTION 141ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; 142SHOW WARNINGS; 143--error ER_ILLEGAL_HA_CREATE_OPTION 144ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; 145SHOW WARNINGS; 146--error ER_ILLEGAL_HA_CREATE_OPTION 147ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; 148SHOW WARNINGS; 149ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1; 150SHOW WARNINGS; 151SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 152ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; 153SHOW WARNINGS; 154SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 155 156 157--echo # Test 4) StrictMode=ON, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid non-zero KEY_BLOCK_SIZE 158DROP TABLE IF EXISTS t1; 159CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; 160SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 161--error ER_ILLEGAL_HA_CREATE_OPTION 162ALTER TABLE t1 KEY_BLOCK_SIZE=2; 163SHOW WARNINGS; 164ALTER TABLE t1 ROW_FORMAT=REDUNDANT; 165SHOW WARNINGS; 166SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 167--error ER_ILLEGAL_HA_CREATE_OPTION 168ALTER TABLE t1 KEY_BLOCK_SIZE=4; 169SHOW WARNINGS; 170ALTER TABLE t1 ROW_FORMAT=DYNAMIC; 171SHOW WARNINGS; 172SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 173--error ER_ILLEGAL_HA_CREATE_OPTION 174ALTER TABLE t1 KEY_BLOCK_SIZE=2; 175SHOW WARNINGS; 176ALTER TABLE t1 ROW_FORMAT=COMPRESSED; 177SHOW WARNINGS; 178SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 179ALTER TABLE t1 KEY_BLOCK_SIZE=1; 180SHOW WARNINGS; 181SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 182DROP TABLE IF EXISTS t1; 183CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; 184ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1; 185SHOW WARNINGS; 186SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 187 188--echo # Test 5) StrictMode=ON, CREATE with a valid KEY_BLOCK_SIZE 189--echo # ALTER with each ROW_FORMAT 190DROP TABLE IF EXISTS t1; 191CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=2; 192SHOW CREATE TABLE t1; 193ALTER TABLE t1 ADD COLUMN f1 INT; 194SHOW CREATE TABLE t1; 195--error ER_ILLEGAL_HA_CREATE_OPTION 196ALTER TABLE t1 ROW_FORMAT=COMPACT; 197SHOW WARNINGS; 198--error ER_ILLEGAL_HA_CREATE_OPTION 199ALTER TABLE t1 ROW_FORMAT=REDUNDANT; 200SHOW WARNINGS; 201--error ER_ILLEGAL_HA_CREATE_OPTION 202ALTER TABLE t1 ROW_FORMAT=DYNAMIC; 203SHOW WARNINGS; 204ALTER TABLE t1 ROW_FORMAT=COMPRESSED; 205SHOW WARNINGS; 206SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 207ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; 208SHOW WARNINGS; 209SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 210ALTER TABLE t1 ROW_FORMAT=COMPACT; 211SHOW WARNINGS; 212SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 213 214--echo # Test 6) StrictMode=ON, CREATE with an invalid KEY_BLOCK_SIZE. 215DROP TABLE IF EXISTS t1; 216--error ER_ILLEGAL_HA 217CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=9; 218SHOW WARNINGS; 219 220--echo # Test 7) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and 221--echo # and a valid non-zero KEY_BLOCK_SIZE are rejected with Antelope 222--echo # and that they can be set to default values during strict mode. 223SET GLOBAL innodb_file_format=Antelope; 224DROP TABLE IF EXISTS t1; 225--error ER_ILLEGAL_HA 226CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=4; 227SHOW WARNINGS; 228--error ER_ILLEGAL_HA 229CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; 230SHOW WARNINGS; 231--error ER_ILLEGAL_HA 232CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; 233SHOW WARNINGS; 234CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; 235SHOW WARNINGS; 236SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 237DROP TABLE IF EXISTS t1; 238CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; 239SHOW WARNINGS; 240SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 241DROP TABLE IF EXISTS t1; 242CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT; 243SHOW WARNINGS; 244--error ER_ILLEGAL_HA_CREATE_OPTION 245ALTER TABLE t1 KEY_BLOCK_SIZE=2; 246SHOW WARNINGS; 247--error ER_ILLEGAL_HA_CREATE_OPTION 248ALTER TABLE t1 ROW_FORMAT=COMPRESSED; 249SHOW WARNINGS; 250--error ER_ILLEGAL_HA_CREATE_OPTION 251ALTER TABLE t1 ROW_FORMAT=DYNAMIC; 252SHOW WARNINGS; 253SET GLOBAL innodb_file_format=Barracuda; 254DROP TABLE IF EXISTS t1; 255CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; 256SET GLOBAL innodb_file_format=Antelope; 257ALTER TABLE t1 ADD COLUMN f1 INT; 258SHOW CREATE TABLE t1; 259SHOW WARNINGS; 260ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; 261SHOW WARNINGS; 262ALTER TABLE t1 ADD COLUMN f2 INT; 263SHOW WARNINGS; 264SET GLOBAL innodb_file_format=Barracuda; 265 266--echo # Test 8) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and 267--echo # and a valid non-zero KEY_BLOCK_SIZE are rejected with 268--echo # innodb_file_per_table=OFF and that they can be set to default 269--echo # values during strict mode. 270SET GLOBAL innodb_file_per_table=OFF; 271DROP TABLE IF EXISTS t1; 272--error ER_ILLEGAL_HA 273CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=1; 274SHOW WARNINGS; 275--error ER_ILLEGAL_HA 276CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; 277SHOW WARNINGS; 278--error ER_ILLEGAL_HA 279CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; 280SHOW WARNINGS; 281CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; 282SHOW WARNINGS; 283SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 284DROP TABLE IF EXISTS t1; 285CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; 286SHOW WARNINGS; 287SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 288DROP TABLE IF EXISTS t1; 289CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT; 290SHOW WARNINGS; 291--error ER_ILLEGAL_HA_CREATE_OPTION 292ALTER TABLE t1 KEY_BLOCK_SIZE=1; 293SHOW WARNINGS; 294--error ER_ILLEGAL_HA_CREATE_OPTION 295ALTER TABLE t1 ROW_FORMAT=COMPRESSED; 296SHOW WARNINGS; 297--error ER_ILLEGAL_HA_CREATE_OPTION 298ALTER TABLE t1 ROW_FORMAT=DYNAMIC; 299SHOW WARNINGS; 300ALTER TABLE t1 ROW_FORMAT=COMPACT; 301SHOW WARNINGS; 302SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 303ALTER TABLE t1 ROW_FORMAT=REDUNDANT; 304SHOW WARNINGS; 305SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 306ALTER TABLE t1 ROW_FORMAT=DEFAULT; 307SHOW WARNINGS; 308SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 309SET GLOBAL innodb_file_per_table=ON; 310DROP TABLE IF EXISTS t1; 311CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; 312SET GLOBAL innodb_file_per_table=OFF; 313ALTER TABLE t1 ADD COLUMN f1 INT; 314SHOW WARNINGS; 315ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; 316SHOW WARNINGS; 317ALTER TABLE t1 ADD COLUMN f2 INT; 318SHOW WARNINGS; 319SET GLOBAL innodb_file_per_table=ON; 320 321--echo ################################################## 322SET SESSION innodb_strict_mode = OFF; 323 324--echo # Test 9) StrictMode=OFF, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0 325--echo # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified' 326--echo # 'FIXED' is sent to InnoDB since it is used by MyISAM. 327--echo # It is an invalid mode in InnoDB, use COMPACT 328DROP TABLE IF EXISTS t1; 329CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED; 330SHOW WARNINGS; 331SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 332DROP TABLE IF EXISTS t1; 333CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0; 334SHOW WARNINGS; 335SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 336ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=0; 337SHOW WARNINGS; 338SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 339ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; 340SHOW WARNINGS; 341SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 342ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=0; 343SHOW WARNINGS; 344SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 345ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; 346SHOW WARNINGS; 347SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 348ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=0; 349SHOW WARNINGS; 350SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 351 352--echo # Test 10) StrictMode=OFF, CREATE with each ROW_FORMAT & a valid KEY_BLOCK_SIZE 353--echo # KEY_BLOCK_SIZE is ignored with COMPACT, REDUNDANT, & DYNAMIC 354DROP TABLE IF EXISTS t1; 355CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; 356SHOW WARNINGS; 357SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 358DROP TABLE IF EXISTS t1; 359CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; 360SHOW WARNINGS; 361SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 362DROP TABLE IF EXISTS t1; 363CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; 364SHOW WARNINGS; 365SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 366DROP TABLE IF EXISTS t1; 367CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; 368SHOW WARNINGS; 369SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 370ALTER TABLE t1 ADD COLUMN f1 INT; 371SHOW WARNINGS; 372SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 373DROP TABLE IF EXISTS t1; 374CREATE TABLE t1 ( i INT ) ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1; 375SHOW WARNINGS; 376SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 377ALTER TABLE t1 ADD COLUMN f1 INT; 378SHOW WARNINGS; 379SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 380 381 382--echo # Test 11) StrictMode=OFF, ALTER with each ROW_FORMAT & a valid KEY_BLOCK_SIZE 383DROP TABLE IF EXISTS t1; 384CREATE TABLE t1 ( i INT ); 385ALTER TABLE t1 ROW_FORMAT=FIXED KEY_BLOCK_SIZE=1; 386SHOW WARNINGS; 387SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 388DROP TABLE IF EXISTS t1; 389CREATE TABLE t1 ( i INT ); 390ALTER TABLE t1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=2; 391SHOW WARNINGS; 392SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 393DROP TABLE IF EXISTS t1; 394CREATE TABLE t1 ( i INT ); 395ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; 396SHOW WARNINGS; 397SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 398DROP TABLE IF EXISTS t1; 399CREATE TABLE t1 ( i INT ); 400ALTER TABLE t1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; 401SHOW WARNINGS; 402SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 403DROP TABLE IF EXISTS t1; 404CREATE TABLE t1 ( i INT ); 405ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=1; 406SHOW WARNINGS; 407SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 408ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; 409SHOW WARNINGS; 410SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 411 412 413--echo # Test 12) StrictMode=OFF, CREATE with ROW_FORMAT=COMPACT, ALTER with a valid KEY_BLOCK_SIZE 414DROP TABLE IF EXISTS t1; 415CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; 416SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 417ALTER TABLE t1 KEY_BLOCK_SIZE=2; 418SHOW WARNINGS; 419SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 420ALTER TABLE t1 ROW_FORMAT=REDUNDANT; 421SHOW WARNINGS; 422SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 423ALTER TABLE t1 ROW_FORMAT=DYNAMIC; 424SHOW WARNINGS; 425SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 426ALTER TABLE t1 ROW_FORMAT=COMPRESSED; 427SHOW WARNINGS; 428SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 429ALTER TABLE t1 KEY_BLOCK_SIZE=4; 430SHOW WARNINGS; 431SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 432DROP TABLE IF EXISTS t1; 433CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT; 434ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=2; 435SHOW WARNINGS; 436SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 437 438--echo # Test 13) StrictMode=OFF, CREATE with a valid KEY_BLOCK_SIZE 439--echo # ALTER with each ROW_FORMAT 440DROP TABLE IF EXISTS t1; 441CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=1; 442SHOW WARNINGS; 443SHOW CREATE TABLE t1; 444ALTER TABLE t1 ADD COLUMN f1 INT; 445SHOW WARNINGS; 446SHOW CREATE TABLE t1; 447ALTER TABLE t1 ROW_FORMAT=COMPACT; 448SHOW WARNINGS; 449SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 450ALTER TABLE t1 ROW_FORMAT=REDUNDANT; 451SHOW WARNINGS; 452SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 453ALTER TABLE t1 ROW_FORMAT=DYNAMIC; 454SHOW WARNINGS; 455SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 456ALTER TABLE t1 ROW_FORMAT=COMPRESSED; 457SHOW WARNINGS; 458SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 459ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; 460SHOW WARNINGS; 461SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 462ALTER TABLE t1 ROW_FORMAT=COMPACT; 463SHOW WARNINGS; 464SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 465 466--echo # Test 14) StrictMode=OFF, CREATE with an invalid KEY_BLOCK_SIZE, 467--echo # it defaults to half of the page size. 468DROP TABLE IF EXISTS t1; 469CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=15; 470SHOW WARNINGS; 471SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 472 473--echo # Test 15) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a 474--echo valid KEY_BLOCK_SIZE are remembered but not used when ROW_FORMAT 475--echo is reverted to Antelope and then used again when ROW_FORMAT=Barracuda. 476DROP TABLE IF EXISTS t1; 477CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; 478SHOW WARNINGS; 479SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 480SET GLOBAL innodb_file_format=Antelope; 481ALTER TABLE t1 ADD COLUMN f1 INT; 482SHOW WARNINGS; 483SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 484SET GLOBAL innodb_file_format=Barracuda; 485ALTER TABLE t1 ADD COLUMN f2 INT; 486SHOW WARNINGS; 487SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 488DROP TABLE IF EXISTS t1; 489CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; 490SHOW WARNINGS; 491SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 492SET GLOBAL innodb_file_format=Antelope; 493ALTER TABLE t1 ADD COLUMN f1 INT; 494SHOW WARNINGS; 495SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 496SET GLOBAL innodb_file_format=Barracuda; 497ALTER TABLE t1 ADD COLUMN f2 INT; 498SHOW WARNINGS; 499SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 500 501--echo # Test 16) StrictMode=OFF, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and a 502--echo valid KEY_BLOCK_SIZE are remembered but not used when innodb_file_per_table=OFF 503--echo and then used again when innodb_file_per_table=ON. 504DROP TABLE IF EXISTS t1; 505CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; 506SHOW WARNINGS; 507SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 508SET GLOBAL innodb_file_per_table=OFF; 509ALTER TABLE t1 ADD COLUMN f1 INT; 510SHOW WARNINGS; 511SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 512SET GLOBAL innodb_file_per_table=ON; 513ALTER TABLE t1 ADD COLUMN f2 INT; 514SHOW WARNINGS; 515SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 516DROP TABLE IF EXISTS t1; 517CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; 518SHOW WARNINGS; 519SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 520SET GLOBAL innodb_file_per_table=OFF; 521ALTER TABLE t1 ADD COLUMN f1 INT; 522SHOW WARNINGS; 523SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 524SET GLOBAL innodb_file_per_table=ON; 525ALTER TABLE t1 ADD COLUMN f2 INT; 526SHOW WARNINGS; 527SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = 't1'; 528 529 530--echo # Cleanup 531DROP TABLE IF EXISTS t1; 532 533--disable_query_log 534EVAL SET GLOBAL innodb_file_format=$innodb_file_format_orig; 535EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; 536EVAL SET SESSION innodb_strict_mode=$innodb_strict_mode_orig; 537--enable_query_log 538 539