CentOS Linux release 7.5.1804 (Core)

Thanks for the report!
It looks like it is the same problem as https://bugs.mysql.com/bug.php?id=70260 (reproducible on MariaDB 5.5-10.3, Mysql 5.6, 5.7) In Mysql 8.0 error is returned on the attempt to drop index before dropping constraint. (it is described in the documentation here https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_foreign_key_checks vs https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_foreign_key_checks)

--source include/have_innodb.inc

set foreign_key_checks=on;

create table t1 (id int not null primary key, name varchar(100) not null)engine=innodb;

create table t2 (id int not null primary key, fid int not null, name varchar(100) not null,

 CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`))engine=innodb;

insert into t1 values(1, 'test 1'),(2, 'test 2'),(3,'test 3');

insert into t2 values(1, 1, 'test 1'),(2, 1, 'test 2'),(3, 2, 'test 3');

set foreign_key_checks=off;

alter table t2 drop index `fk_fid`;

set foreign_key_checks=on;

insert into t2 values(4, 99, 'test 1');

 show create table t1;

 show create table t2;

 select * from t1; 

 select * from t2;

--source include/restart_mysqld.inc

select * from t2;

mysqltest: At line 39: query 'select * from t2' failed: 1932: Table 'test.t2' doesn't exist in engine

It seems that the problem is in CONSTRAINT definition. After ALTER of other tables CHARSETs in definitions of fields in CONSTRAINT are no more the same. If the CONSTRAINT is dropped re-creation of the same CONSTRAINT fails with:

MariaDB [testFK]> ALTER TABLE transaction ADD CONSTRAINT transaction_ibfk_1 FOREIGN KEY (c_id) REFERENCES customer (id);

ERROR 1005 (HY000): Can't create table `testFK`.`#sql-1055_a` (errno: 150 "Foreign key constraint is incorrectly formed")

Table definitions after ALTER statements:

MariaDB [testFK]>  SHOW CREATE TABLE customer;

...

| customer | CREATE TABLE `customer` (

  `id` char(32) COLLATE utf8_slovenian_ci NOT NULL,

...

MariaDB [testFK]> SHOW CREATE TABLE transaction;

...

 `c_id` char(32) CHARACTER SET ascii NOT NULL DEFAULT '',

...

 CONSTRAINT `transaction_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `customer` (`id`),

...

For Alice's test case above, the error log after restart for 10.2+ says

2019-01-13 16:26:43 140606899308288 [Warning] InnoDB: Load table `test`.`t2` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.

For 10.0 and 10.1, it contains an extra line:

190113 16:25:21 [Warning] InnoDB: Load table 'test/t2' failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.

190113 16:25:21 [Warning] InnoDB: Cannot open table test/t2 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

No errors or warnings before restart.

I think that a sane fix of this would be to allow ha_innobase::open(), but refuse any modifications of the table data unless foreign_key_checks=0.

I tried an initial patch that removes DICT_ERR_IGNORE_FK_NOKEY. It turns out that we would have to implement an additional check in ha_innobase::create() and possibly ha_innobase::prepare_inplace_alter_table():

CURRENT_TEST: innodb.innodb

mysqltest: At line 1644: query 'create table t1(a char(10) primary key, b varchar(20)) engine = innodb' succeeded - should have failed with errno 1005...

…

CURRENT_TEST: innodb.innodb_bug12902967

mysqltest: At line 24: query 'alter table t1 add constraint c1 foreign key (f1) references t1(f1)' succeeded - should have failed with errno 1025...

…

CURRENT_TEST: innodb.innodb-fkcheck

mysqltest: At line 49: query 'CREATE TABLE b (

b bigint unsigned NOT NULL,

d1 date NOT NULL,

PRIMARY KEY (b,d1)

) ENGINE=InnoDB' succeeded - should have failed with errno 1005...

Also, if we remove the check from ha_innobase::open(), we must report errors on DML when foreign_key_checks are enabled and we find out that dict_foreign_t::referenced_index or dict_foreign_t::foreign_index is NULL.

It turns out that the MySQL 5.7.5 fix of removing a condition from ha_innobase::prepare_inplace_alter_table() was omitted when the InnoDB changes from MySQL 5.7.9 were applied to MariaDB 10.2.2.

I think that it is best to remove the check from ha_innobase::open() and adjust the other code accordingly. If we fail to do that, users who have already dropped the indexes that are needed for enforcing FOREIGN KEY constraints would seem to have no way to regain access to the table.

The observation in the Description is explained by the fact that the function ha_innobase::open() allows the table to be opened when foreign_key_checks=0:

	/* Check whether FOREIGN_KEY_CHECKS is set to 0. If so, the table

	can be opened even if some FK indexes are missing. If not, the table

	can't be opened in the same situation */

	if (thd_test_options(thd, OPTION_NO_FOREIGN_KEY_CHECKS)) {

		ignore_err = DICT_ERR_IGNORE_FK_NOKEY;

	}

I will try to remove this special case, and implement the check for missing indexes when DML operations would access the indexes for enforcing FOREIGN KEY constraints.

I am working on a fix that replaces some use of DICT_ERR_IGNORE_NONE with DICT_ERR_IGNORE_FK_NOKEY, so that most of the time, we would allow access to tables even when some underlying indexes for FOREIGN KEY are missing.

All existing tests pass with that, but I think that I must write an additional test where DML would encounter the missing index when trying to enforce a FOREIGN KEY constraint.

It turns out that the function row_ins_check_foreign_constraint(), which gets invoked when deleting from the parent table, was silently ignoring the constraint when the index is missing from the child table (the one with the CONSTRAINT…FOREIGN KEY…REFERENCES clause). Even after fixing that, I was still having the problem that an INSERT into the child table is being accepted.
The higher-level code seems to ignore any FOREIGN KEY constraints that are not backed by indexes in the referencing table are being ignored altogether.

I fixed it so that such DELETE and INSERT will be rejected with foreign_key_checks=1 and accepted with foreign_key_checks=0.

Please review the 3 commits at bb-10.2-marko, on top of 10.2.