mirror of
https://github.com/MariaDB/server.git
synced 2025-08-01 03:47:19 +03:00
cffbb17480
Before MySQL 4.0.18, user-specified constraint names were ignored. Starting with MySQL 4.0.18, the specified constraint name was prepended with the schema name and '/'. Now we are transforming into a format where the constraint name is prepended with the dict_table_t::name and the impossible UTF-8 sequence 0xff. Generated constraint names will be ASCII decimal numbers. On upgrade, old FOREIGN KEY constraint names will be displayed without any schema name prefix. They will be updated to the new format on DDL operations. dict_foreign_t::sql_id(): Return the SQL constraint name without any schemaname/tablename\377 or schemaname/ prefix. row_rename_table_for_mysql(), dict_table_rename_in_cache(): Simplify the logic: Just rename constraints to the new format. dict_table_get_foreign_id(): Replaces dict_table_get_highest_foreign_id(). innobase_get_foreign_key_info(): Let my_error() refer to erroneous anonymous constraints as "(null)". row_delete_constraint(): Try to drop all 3 constraint name variants. Reviewed by: Thirunarayanan Balathandayuthapani Tested by: Matthias Leich
182 lines
6.3 KiB
Plaintext
182 lines
6.3 KiB
Plaintext
SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
|
|
SET GLOBAL innodb_stats_persistent = 0;
|
|
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_VIRTUAL 0 6 0 Redundant 0 System
|
|
16 mysql/transaction_registry 33 8 6 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 8 TABLE_ID
|
|
13 1 6 0 4 POS
|
|
13 2 6 0 4 BASE_POS
|
|
16 0 6 1800 8 transaction_id
|
|
16 1 6 1800 8 commit_id
|
|
16 2 3 526087 7 begin_timestamp
|
|
16 3 3 526087 7 commit_timestamp
|
|
16 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
|
|
# 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 TABLE_ID
|
|
15 1 POS
|
|
15 2 BASE_POS
|
|
18 0 transaction_id
|
|
19 0 commit_id
|
|
20 0 begin_timestamp
|
|
21 0 commit_timestamp
|
|
21 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
|
|
test/t_compact test/t_compact 1 5 Compact 0
|
|
test/t_compressed test/t_compressed 37 5 Compressed 2048
|
|
test/t_dynamic test/t_dynamic 33 5 Dynamic 0
|
|
test/t_redundant test/t_redundant 0 5 Redundant 0
|
|
=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
|
|
Space_Name Page_Size Zip_Size Path
|
|
innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001
|
|
innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002
|
|
innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003
|
|
test/t_redundant DEFAULT DEFAULT MYSQLD_DATADIR/test/t_redundant.ibd
|
|
test/t_compact DEFAULT DEFAULT MYSQLD_DATADIR/test/t_compact.ibd
|
|
test/t_compressed DEFAULT 2048 MYSQLD_DATADIR/test/t_compressed.ibd
|
|
test/t_dynamic DEFAULT DEFAULT MYSQLD_DATADIR/test/t_dynamic.ibd
|
|
innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1
|
|
DROP TABLE t_redundant, t_compact, t_compressed, t_dynamic;
|
|
SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS;
|
|
count(*)
|
|
6
|
|
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
|
|
constraint_test test/child test/parent 1 1
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
constraint_test parent_id id 0
|
|
INSERT INTO parent VALUES(1);
|
|
InnoDB 0 transactions not purged
|
|
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_FOREIGN 0 7
|
|
SYS_FOREIGN_COLS 0 7
|
|
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
|
|
constraint_test test/child test/parent 2 1
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
|
|
ID FOR_COL_NAME REF_COL_NAME POS
|
|
constraint_test id id 0
|
|
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
|
|
InnoDB 0 transactions not purged
|
|
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;
|
|
#
|
|
# MDEV-29479 I_S.INNODB_SYS_TABLESPACES doesn't have
|
|
# temporary tablespace information
|
|
#
|
|
SELECT SPACE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE name like 'innodb_temporary';
|
|
SPACE
|
|
4294967294
|
|
SET GLOBAL innodb_stats_persistent = @save_stats_persistent;
|