diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/innodb-system-table-view.result')
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb-system-table-view.result | 178 |
1 files changed, 178 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-system-table-view.result b/mysql-test/suite/innodb/r/innodb-system-table-view.result new file mode 100644 index 00000000000..46730ef3aac --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-system-table-view.result @@ -0,0 +1,178 @@ +SELECT table_id INTO @table_stats_id FROM information_schema.innodb_sys_tables +WHERE name = 'mysql/innodb_table_stats'; +SELECT table_id INTO @index_stats_id FROM information_schema.innodb_sys_tables +WHERE name = 'mysql/innodb_index_stats'; +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES +WHERE table_id NOT IN (@table_stats_id, @index_stats_id) ORDER BY table_id; +TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE +11 SYS_FOREIGN 0 7 0 Redundant 0 System +12 SYS_FOREIGN_COLS 0 7 0 Redundant 0 System +13 SYS_TABLESPACES 0 6 0 Redundant 0 System +14 SYS_DATAFILES 0 5 0 Redundant 0 System +15 SYS_VIRTUAL 0 6 0 Redundant 0 System +18 mysql/transaction_registry 33 8 3 Dynamic 0 Single +SELECT table_id,pos,mtype,prtype,len,name +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS +WHERE table_id NOT IN (@table_stats_id, @index_stats_id) +ORDER BY table_id, pos; +table_id pos mtype prtype len name +11 0 1 524292 0 ID +11 1 1 524292 0 FOR_NAME +11 2 1 524292 0 REF_NAME +11 3 6 0 4 N_COLS +12 0 1 524292 0 ID +12 1 6 0 4 POS +12 2 1 524292 0 FOR_COL_NAME +12 3 1 524292 0 REF_COL_NAME +13 0 6 0 4 SPACE +13 1 1 524292 0 NAME +13 2 6 0 4 FLAGS +14 0 6 0 4 SPACE +14 1 1 524292 0 PATH +15 0 6 0 8 TABLE_ID +15 1 6 0 4 POS +15 2 6 0 4 BASE_POS +18 0 6 1800 8 transaction_id +18 1 6 1800 8 commit_id +18 2 3 526087 7 begin_timestamp +18 3 3 526087 7 commit_timestamp +18 4 6 1022 1 isolation_level +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES +WHERE table_id NOT IN (@table_stats_id, @index_stats_id) ORDER BY index_id; +INDEX_ID NAME TABLE_ID TYPE N_FIELDS PAGE_NO SPACE MERGE_THRESHOLD +# ID_IND # 3 1 # # 50 +# FOR_IND # 0 1 # # 50 +# REF_IND # 0 1 # # 50 +# ID_IND # 3 2 # # 50 +# SYS_TABLESPACES_SPACE # 3 1 # # 50 +# SYS_DATAFILES_SPACE # 3 1 # # 50 +# BASE_IDX # 3 3 # # 50 +# PRIMARY # 3 1 # # 50 +# commit_id # 2 1 # # 50 +# begin_timestamp # 0 1 # # 50 +# commit_timestamp # 0 2 # # 50 +SELECT index_id,pos,name FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS +WHERE name NOT IN ('database_name', 'table_name', 'index_name', 'stat_name') +ORDER BY index_id, pos; +index_id pos name +11 0 ID +12 0 FOR_NAME +13 0 REF_NAME +14 0 ID +14 1 POS +15 0 SPACE +16 0 SPACE +17 0 TABLE_ID +17 1 POS +17 2 BASE_POS +20 0 transaction_id +21 0 commit_id +22 0 begin_timestamp +23 0 commit_timestamp +23 1 transaction_id +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; +ID FOR_NAME REF_NAME N_COLS TYPE +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; +ID FOR_COL_NAME REF_COL_NAME POS +CREATE TABLE t_redundant (a INT KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=innodb; +CREATE TABLE t_compact (a INT KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=innodb; +CREATE TABLE t_compressed (a INT KEY, b TEXT) ROW_FORMAT=COMPRESSED ENGINE=innodb KEY_BLOCK_SIZE=2; +CREATE TABLE t_dynamic (a INT KEY, b TEXT) ROW_FORMAT=DYNAMIC ENGINE=innodb; +=== information_schema.innodb_sys_tables and innodb_sys_tablespaces === +Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type +test/t_compact test/t_compact 1 5 Compact 0 Single +test/t_compressed test/t_compressed 37 5 Compressed 2048 Single +test/t_dynamic test/t_dynamic 33 5 Dynamic 0 Single +test/t_redundant test/t_redundant 0 5 Redundant 0 Single +=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === +Space_Name Space_Type Page_Size Zip_Size Path +test/t_redundant Single DEFAULT DEFAULT MYSQLD_DATADIR/test/t_redundant.ibd +test/t_compact Single DEFAULT DEFAULT MYSQLD_DATADIR/test/t_compact.ibd +test/t_compressed Single DEFAULT 2048 MYSQLD_DATADIR/test/t_compressed.ibd +test/t_dynamic Single DEFAULT DEFAULT MYSQLD_DATADIR/test/t_dynamic.ibd +DROP TABLE t_redundant, t_compact, t_compressed, t_dynamic; +SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS; +count(*) +8 +CREATE TABLE parent (id INT NOT NULL, +PRIMARY KEY (id)) ENGINE=INNODB; +CREATE TABLE child (id INT, parent_id INT, +INDEX par_ind (parent_id), +CONSTRAINT constraint_test +FOREIGN KEY (parent_id) REFERENCES parent(id) +ON DELETE CASCADE) ENGINE=INNODB; +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; +ID FOR_NAME REF_NAME N_COLS TYPE +test/constraint_test test/child test/parent 1 1 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; +ID FOR_COL_NAME REF_COL_NAME POS +test/constraint_test parent_id id 0 +INSERT INTO parent VALUES(1); +SELECT name, num_rows, ref_count +FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name LIKE "%parent"; +name num_rows ref_count +test/parent 1 1 +SELECT NAME, FLAG, N_COLS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES +WHERE name NOT LIKE 'sys/%'; +NAME FLAG N_COLS +SYS_DATAFILES 0 5 +SYS_FOREIGN 0 7 +SYS_FOREIGN_COLS 0 7 +SYS_TABLESPACES 0 6 +SYS_VIRTUAL 0 6 +mysql/innodb_index_stats 33 11 +mysql/innodb_table_stats 33 9 +mysql/transaction_registry 33 8 +test/child 33 5 +test/parent 33 4 +SELECT name, n_fields +from INFORMATION_SCHEMA.INNODB_SYS_INDEXES +WHERE table_id In (SELECT table_id from +INFORMATION_SCHEMA.INNODB_SYS_TABLES +WHERE name LIKE "%parent%"); +name n_fields +PRIMARY 1 +SELECT name, n_fields +from INFORMATION_SCHEMA.INNODB_SYS_INDEXES +WHERE table_id In (SELECT table_id from +INFORMATION_SCHEMA.INNODB_SYS_TABLES +WHERE name LIKE "%child%"); +name n_fields +GEN_CLUST_INDEX 0 +par_ind 1 +SELECT name, pos, mtype, len +from INFORMATION_SCHEMA.INNODB_SYS_COLUMNS +WHERE table_id In (SELECT table_id from +INFORMATION_SCHEMA.INNODB_SYS_TABLES +WHERE name LIKE "%child%"); +name pos mtype len +id 0 6 4 +parent_id 1 6 4 +DROP TABLE child; +DROP TABLE parent; +CREATE TABLE parent (id INT NOT NULL, newid INT NOT NULL, +PRIMARY KEY (id, newid)) ENGINE=INNODB; +CREATE TABLE child (id INT, parent_id INT, +INDEX par_ind (parent_id), +CONSTRAINT constraint_test +FOREIGN KEY (id, parent_id) REFERENCES parent(id, newid) +ON DELETE CASCADE) ENGINE=INNODB; +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; +ID FOR_NAME REF_NAME N_COLS TYPE +test/constraint_test test/child test/parent 2 1 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; +ID FOR_COL_NAME REF_COL_NAME POS +test/constraint_test id id 0 +test/constraint_test parent_id newid 1 +INSERT INTO parent VALUES(1, 9); +SELECT * FROM parent WHERE id IN (SELECT id FROM parent); +id newid +1 9 +SELECT name, num_rows, ref_count +FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name LIKE "%parent"; +name num_rows ref_count +test/parent 1 2 +DROP TABLE child; +DROP TABLE parent; |