currently 10.2 is still failing, while 10.3-10.6 return "ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly for"
|
10.2 c22f7f2323d6f823c9340 |
#5 0x00007ff4dd399859 in __GI_abort () at abort.c:79
|
#6 0x00007ff4dd399729 in __assert_fail_base (fmt=0x7ff4dd52f588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x563067eb8b88 "mdl_ticket->m_type == MDL_SHARED_UPGRADABLE || mdl_ticket->m_type == MDL_SHARED_NO_WRITE || mdl_ticket->m_type == MDL_SHARED_NO_READ_WRITE || mdl_ticket->m_type == MDL_SHARED_READ || mdl_ticket->m_typ"..., file=0x563067eb8170 "/10.2/src/sql/mdl.cc", line=2324, function=<optimized out>) at assert.c:92
|
#7 0x00007ff4dd3aaf36 in __GI___assert_fail (assertion=0x563067eb8b88 "mdl_ticket->m_type == MDL_SHARED_UPGRADABLE || mdl_ticket->m_type == MDL_SHARED_NO_WRITE || mdl_ticket->m_type == MDL_SHARED_NO_READ_WRITE || mdl_ticket->m_type == MDL_SHARED_READ || mdl_ticket->m_typ"..., file=0x563067eb8170 "/10.2/src/sql/mdl.cc", line=2324, function=0x563067eb8b38 "bool MDL_context::upgrade_shared_lock(MDL_ticket*, enum_mdl_type, double)") at assert.c:101
|
#8 0x00005630675483ac in MDL_context::upgrade_shared_lock (this=0x7ff480000eb0, mdl_ticket=0x7ff48012e4c0, new_type=MDL_SHARED_NO_WRITE, lock_wait_timeout=86400) at /10.2/src/sql/mdl.cc:2324
|
#9 0x00005630674c1d8b in mysql_alter_table (thd=0x7ff480000d90, new_db=0x7ff480012e38 "test", new_name=0x0, create_info=0x7ff4d8076250, table_list=0x7ff480012818, alter_info=0x7ff4d80761a0, order_num=0, order=0x0, ignore=false) at /10.2/src/sql/sql_table.cc:9735
|
#10 0x000056306754060c in Sql_cmd_alter_table::execute (this=0x7ff480013038, thd=0x7ff480000d90) at /10.2/src/sql/sql_alter.cc:333
|
#11 0x00005630673edc30 in mysql_execute_command (thd=0x7ff480000d90) at /10.2/src/sql/sql_parse.cc:6017
|
#12 0x00005630673f2a96 in mysql_parse (thd=0x7ff480000d90, rawbuf=0x7ff480012708 "ALTER TABLE t1 ADD FOREIGN KEY (pk) REFERENCES t1 (f)", length=53, parser_state=0x7ff4d8077560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793
|
#13 0x00005630673e0cf1 in dispatch_command (command=COM_QUERY, thd=0x7ff480000d90, packet=0x7ff480008b61 "ALTER TABLE t1 ADD FOREIGN KEY (pk) REFERENCES t1 (f)", packet_length=53, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
|
#14 0x00005630673df7ec in do_command (thd=0x7ff480000d90) at /10.2/src/sql/sql_parse.cc:1381
|
#15 0x000056306753b001 in do_handle_one_connection (connect=0x56306ad4a8d0) at /10.2/src/sql/sql_connect.cc:1336
|
#16 0x000056306753ad66 in handle_one_connection (arg=0x56306ad4a8d0) at /10.2/src/sql/sql_connect.cc:1241
|
#17 0x0000563067d673ca in pfs_spawn_thread (arg=0x56306ad34e80) at /10.2/src/storage/perfschema/pfs.cc:1869
|
#18 0x00007ff4dd8bb609 in start_thread (arg=<optimized out>) at pthread_create.c:477
|
#19 0x00007ff4dd496293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
"Foreign key constraint is incorrectly formed" is a good error. See
With table created like this
CREATE TABLE t1 (pk INT, f INT, PRIMARY KEY(pk)) ENGINE=InnoDB;
|
statement
ALTER TABLE t1 ADD FOREIGN KEY (pk) REFERENCES t1 (f);
|
is invalid, as foreign-key-to-be does not reference an indexed column in the "foreign table", which innodb correctly rejects
Was perhaps
ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES t1 (pk);
|
intented instead?
If this was intented, in 10.3+, this test would succeed
--source include/have_innodb.inc
|
|
CREATE TABLE t1 (pk INT, f INT, PRIMARY KEY(pk)) ENGINE=InnoDB;
|
CREATE VIEW v1 AS SELECT * FROM t1;
|
LOCK TABLES t1 READ, v1 WRITE;
|
ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES t1 (pk);
|
# Cleanup
|
UNLOCK TABLES;
|
DROP VIEW v1;
|
DROP TABLE t1;
|
Would this be fine? Feel free to reassign if not.
wlad,
By "error I wouldn't expect" I meant that in the given circumstances (read lock on the table, write lock on the view) I expected a different error, ER_TABLE_NOT_LOCKED_FOR_WRITE, which apparently was happening before the commit mentioned in the description. But maybe it was done intentionally and I just misinterpreted the intention. If so, and 10.3+ behaves as expected, feel free to close since 10.2 is now EOL.
Without the locks, sure, it was and is ER_CANT_CREATE_TABLE / Foreign key constraint is incorrectly formed.
Ok, since neither I nor elenst knows for sure whether ER_TABLE_NOT_LOCKED_FOR_WRITE is expected or now, maybe holyfoot does 
It looks fine to me if there is no error, but maybe I miss some historical importance of the former error. Please free to close the bug, if the example in my previous entry works as expected.