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
157 lines
5.3 KiB
Plaintext
157 lines
5.3 KiB
Plaintext
set @@sql_mode=strict_trans_tables;
|
|
create table t1(a text not null) row_format=dynamic engine=innodb;
|
|
create index idx1 on t1(a(3073));
|
|
Warnings:
|
|
Note 1071 Specified key was too long; max key length is 3072 bytes
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` text NOT NULL,
|
|
KEY `idx1` (`a`(768))
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=DYNAMIC
|
|
drop table t1;
|
|
set @@sql_mode=default;
|
|
create table t1 (
|
|
id1 int(11) not null auto_increment,
|
|
id2 varchar(30) not null,
|
|
id3 datetime not null default current_timestamp,
|
|
primary key (id1),
|
|
unique key unique_id2 (id2)
|
|
) engine=innodb;
|
|
alter table t1 change column id2 id4 varchar(100) not null;
|
|
select * from t1 where id4 like 'a';
|
|
id1 id4 id3
|
|
drop table t1;
|
|
#
|
|
# MDEV-17725 Assertion `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' failed in Diagnostics_area::set_ok_status upon ALTER failing due to error from engine
|
|
#
|
|
SET sql_mode=STRICT_ALL_TABLES;
|
|
CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB;
|
|
ALTER TABLE t1 ORDER BY a;
|
|
Warnings:
|
|
Warning 1105 ORDER BY ignored as there is a user-defined clustered index in the table 't1'
|
|
DROP TABLE t1;
|
|
SET sql_mode='';
|
|
CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB;
|
|
ALTER TABLE t1 ORDER BY a;
|
|
Warnings:
|
|
Warning 1105 ORDER BY ignored as there is a user-defined clustered index in the table 't1'
|
|
DROP TABLE t1;
|
|
SET sql_mode=DEFAULT;
|
|
#
|
|
# MDEV-18775 Server crashes in dict_table_t::instant_column
|
|
# upon ADD COLUMN
|
|
#
|
|
CREATE TABLE tx (pk INT PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE TABLE t1 (pk INT, a INT, PRIMARY KEY (pk), KEY (a), FOREIGN KEY (a) REFERENCES tx (pk)) ENGINE=InnoDB;
|
|
SET FOREIGN_KEY_CHECKS=OFF;
|
|
ALTER TABLE t1 DROP a;
|
|
ERROR HY000: Cannot drop column 'a': needed in a foreign key constraint '1'
|
|
SET FOREIGN_KEY_CHECKS=ON;
|
|
ALTER TABLE t1 ADD b INT;
|
|
ALTER TABLE t1 DROP a;
|
|
ERROR HY000: Cannot drop index 'a': needed in a foreign key constraint
|
|
ALTER TABLE t1 ADD c INT;
|
|
DROP TABLE t1, tx;
|
|
#
|
|
# MDEV-14119 Assertion cmp_rec_rec() on ALTER TABLE
|
|
#
|
|
CREATE TABLE t1(a INT NOT NULL UNIQUE) ENGINE=InnoDB;
|
|
INSERT INTO t1 SELECT * FROM seq_1_to_128;
|
|
ALTER TABLE t1 ADD b TINYINT AUTO_INCREMENT PRIMARY KEY, DROP KEY a;
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-22939 Server crashes in row_make_new_pathname()
|
|
#
|
|
CREATE TABLE t (a INT) ENGINE=INNODB;
|
|
ALTER TABLE t DISCARD TABLESPACE;
|
|
ALTER TABLE t ENGINE INNODB;
|
|
ERROR HY000: Tablespace has been discarded for table `t`
|
|
ALTER TABLE t FORCE;
|
|
ERROR HY000: Tablespace has been discarded for table `t`
|
|
DROP TABLE t;
|
|
#
|
|
# MDEV-24763 ALTER TABLE fails to rename a column in SYS_FIELDS
|
|
#
|
|
CREATE TABLE t1 (a INT, b TEXT, c INT, PRIMARY KEY(b(9)), INDEX(c,a))
|
|
ENGINE=InnoDB;
|
|
ALTER TABLE t1 CHANGE COLUMN a u INT;
|
|
SELECT sf.* FROM information_schema.innodb_sys_fields sf
|
|
INNER JOIN information_schema.innodb_sys_indexes si ON sf.index_id=si.index_id
|
|
INNER JOIN information_schema.innodb_sys_tables st ON si.table_id=st.table_id
|
|
WHERE st.name='test/t1' ORDER BY sf.index_id,sf.pos;
|
|
INDEX_ID NAME POS
|
|
ID b 0
|
|
ID c 0
|
|
ID u 1
|
|
DROP TABLE t1;
|
|
#
|
|
# End of 10.2 tests
|
|
#
|
|
#
|
|
# Check that innodb supports transactional=1
|
|
#
|
|
create table t1 (a int) transactional=1 engine=aria;
|
|
create table t2 (a int) transactional=1 engine=innodb;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1 TRANSACTIONAL=1
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci TRANSACTIONAL=1
|
|
alter table t1 engine=innodb;
|
|
alter table t1 add column b int;
|
|
drop table t1,t2;
|
|
#
|
|
# MDEV-19272 Assertion unireg_check...Field::NEXT_NUMBER failed
|
|
#
|
|
CREATE TABLE t1 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB;
|
|
ALTER TABLE t1 MODIFY c INT NOT NULL, ALGORITHM=INPLACE;
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (c TIMESTAMP AUTO_INCREMENT UNIQUE) ENGINE=InnoDB;
|
|
ERROR 42000: Incorrect column specifier for column 'c'
|
|
CREATE TABLE t1 (c DATETIME AUTO_INCREMENT UNIQUE) ENGINE=InnoDB;
|
|
ERROR 42000: Incorrect column specifier for column 'c'
|
|
#
|
|
# MDEV-31000 Assertion failed on ALTER TABLE...page_compressed=1
|
|
#
|
|
SET @save_file_per_table=@@GLOBAL.innodb_file_per_table;
|
|
SET GLOBAL innodb_file_per_table=0;
|
|
Warnings:
|
|
Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
|
|
CREATE TABLE t (c INT PRIMARY KEY) ENGINE=INNODB;
|
|
SET GLOBAL innodb_file_per_table=1;
|
|
Warnings:
|
|
Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
|
|
ALTER TABLE t page_compressed=1;
|
|
SET GLOBAL innodb_file_per_table=@save_file_per_table;
|
|
Warnings:
|
|
Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
|
|
SELECT space>0 FROM information_schema.innodb_sys_tables WHERE name='test/t';
|
|
space>0
|
|
1
|
|
DROP TABLE t;
|
|
# End of 10.4 tests
|
|
#
|
|
# MDEV-21748 ASAN use-after-poison in PageBulk::insertPage()
|
|
#
|
|
CREATE TABLE t1 (pk TIMESTAMP PRIMARY KEY, a TIMESTAMP NULL UNIQUE)
|
|
ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES
|
|
('2020-03-10 10:21:00', NULL),
|
|
('0000-00-00 00:00:00', '0000-00-00 00:00:00');
|
|
ALTER TABLE t1 FORCE, ALGORITHM=INPLACE;
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-21850 ASAN use-after-poison in page_cur_insert_rec_low()
|
|
#
|
|
CREATE TABLE t1 (id INT PRIMARY KEY, a YEAR, INDEX(id,a)) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (1,NULL),(2,NULL);
|
|
UPDATE t1 SET a=0;
|
|
DROP TABLE t1;
|
|
# End of 10.5 tests
|