259 lines
11 KiB
Plaintext
259 lines
11 KiB
Plaintext
SELECT @@innodb_default_row_format;
|
|
@@innodb_default_row_format
|
|
dynamic
|
|
SELECT @@innodb_file_per_table;
|
|
@@innodb_file_per_table
|
|
1
|
|
#Create table with no tablespace
|
|
CREATE TABLE tab(c1 TEXT, c2 BLOB);
|
|
INSERT INTO tab VALUES('Check with no tablespace','Check with no tablespace');
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab test/tab 33 8 Dynamic 0 Single
|
|
ALTER TABLE tab ROW_FORMAT=COMPACT;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab test/tab 1 8 Compact 0 Single
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
ALTER TABLE tab ROW_FORMAT=DYNAMIC;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab test/tab 33 8 Dynamic 0 Single
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
ALTER TABLE tab ROW_FORMAT=REDUNDANT;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab test/tab 0 8 Redundant 0 Single
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
ALTER TABLE tab ROW_FORMAT=COMPRESSED;
|
|
SELECT NAME,ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES
|
|
WHERE NAME='test/tab';
|
|
NAME ROW_FORMAT
|
|
test/tab Compressed
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
ALTER TABLE tab ROW_FORMAT=Dynamic;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab test/tab 33 8 Dynamic 0 Single
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
DROP TABLE tab;
|
|
SET GLOBAL innodb_file_per_table=Default;
|
|
SELECT @@innodb_default_row_format;
|
|
@@innodb_default_row_format
|
|
dynamic
|
|
#Create table with tablespace
|
|
CREATE TABLESPACE $tablespace ADD DATAFILE '$tablespace.ibd'
|
|
ENGINE=InnoDB;
|
|
CREATE TABLE tab(c1 TEXT, c2 BLOB) TABLESPACE $tablespace;
|
|
CREATE TABLESPACE tblsp1 ADD DATAFILE 'tblsp1.ibd' FILE_BLOCK_SIZE=1k
|
|
ENGINE=InnoDB;
|
|
CREATE TABLE tab1(c1 TEXT, c2 BLOB) KEY_BLOCK_SIZE=1 TABLESPACE tblsp1;
|
|
INSERT INTO tab VALUES('Check with General tablespace',
|
|
'Check with General tablespace');
|
|
INSERT INTO tab1 VALUES('tablsp File Block size',
|
|
'tablsp File Block size');
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab $tablespace 161 8 Dynamic 0 General
|
|
test/tab1 tblsp1 163 8 Compressed 1024 General
|
|
ALTER TABLE tab ROW_FORMAT=COMPACT;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab $tablespace 129 8 Compact 0 General
|
|
test/tab1 tblsp1 163 8 Compressed 1024 General
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
ALTER TABLE tab ROW_FORMAT=DYNAMIC;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab $tablespace 161 8 Dynamic 0 General
|
|
test/tab1 tblsp1 163 8 Compressed 1024 General
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
ALTER TABLE tab ROW_FORMAT=REDUNDANT;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab $tablespace 128 8 Redundant 0 General
|
|
test/tab1 tblsp1 163 8 Compressed 1024 General
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
ALTER TABLE tab ROW_FORMAT=COMPRESSED;
|
|
ERROR HY000: InnoDB: Tablespace `$tablespace` cannot contain a COMPRESSED table
|
|
ALTER TABLE tab1 ROW_FORMAT=DYNAMIC;
|
|
ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'
|
|
SELECT NAME,ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES
|
|
WHERE NAME='test/tab';
|
|
NAME ROW_FORMAT
|
|
test/tab Redundant
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
CHECK TABLE tab1;
|
|
Table Op Msg_type Msg_text
|
|
test.tab1 check status OK
|
|
ALTER TABLE tab ROW_FORMAT=Dynamic;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab $tablespace 161 8 Dynamic 0 General
|
|
test/tab1 tblsp1 163 8 Compressed 1024 General
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
DROP TABLE tab;
|
|
DROP TABLE tab1;
|
|
DROP TABLESPACE $tablespace;
|
|
DROP TABLESPACE tblsp1;
|
|
SET GLOBAL innodb_file_per_table=Default;
|
|
SELECT @@innodb_default_row_format;
|
|
@@innodb_default_row_format
|
|
dynamic
|
|
#Create table with innodb system tablespace
|
|
CREATE TABLE tab(c1 TEXT, c2 BLOB) TABLESPACE innodb_system;
|
|
INSERT INTO tab VALUES('Check with InnoDB system tablespace',
|
|
'Check with InnoDB system tablespace');
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab innodb_system 161 8 Dynamic 0 System
|
|
ALTER TABLE tab ROW_FORMAT=COMPACT;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab innodb_system 129 8 Compact 0 System
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
ALTER TABLE tab ROW_FORMAT=DYNAMIC;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab innodb_system 161 8 Dynamic 0 System
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
ALTER TABLE tab ROW_FORMAT=REDUNDANT;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab innodb_system 128 8 Redundant 0 System
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
SELECT NAME,ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES
|
|
WHERE NAME='test/tab';
|
|
NAME ROW_FORMAT
|
|
test/tab Redundant
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
ALTER TABLE tab ROW_FORMAT=Dynamic;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab innodb_system 161 8 Dynamic 0 System
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
DROP TABLE tab;
|
|
SET GLOBAL innodb_file_per_table=Default;
|
|
SELECT @@innodb_default_row_format;
|
|
@@innodb_default_row_format
|
|
dynamic
|
|
SET GLOBAL innodb_file_per_table=0;
|
|
SELECT @@innodb_file_per_table;
|
|
@@innodb_file_per_table
|
|
0
|
|
#Create table with file_per_table=0
|
|
CREATE TABLE tab(c1 TEXT, c2 BLOB);
|
|
INSERT INTO tab VALUES('File per table off','File per table off');
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab innodb_system 33 8 Dynamic 0 System
|
|
ALTER TABLE tab ROW_FORMAT=COMPACT;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab innodb_system 129 8 Compact 0 System
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
ALTER TABLE tab ROW_FORMAT=DYNAMIC;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab innodb_system 161 8 Dynamic 0 System
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
ALTER TABLE tab ROW_FORMAT=REDUNDANT;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab innodb_system 128 8 Redundant 0 System
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
ALTER TABLE tab ROW_FORMAT=COMPRESSED;
|
|
ERROR HY000: InnoDB: Tablespace `innodb_system` cannot contain a COMPRESSED table
|
|
SELECT NAME,ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES
|
|
WHERE NAME='test/tab';
|
|
NAME ROW_FORMAT
|
|
test/tab Redundant
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
ALTER TABLE tab ROW_FORMAT=Dynamic;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/tab innodb_system 161 8 Dynamic 0 System
|
|
CHECK TABLE tab;
|
|
Table Op Msg_type Msg_text
|
|
test.tab check status OK
|
|
DROP TABLE tab;
|
|
SET GLOBAL innodb_file_per_table=Default;
|