All we need is an easy explanation of the problem, so here it is.
This is a minimal example that triggers the error
CREATE TABLE A ( id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY ); CREATE TABLE B ( what tinyint NOT NULL, a bigint NOT NULL, CONSTRAINT x FOREIGN KEY (a) REFERENCES A(id), CONSTRAINT y CHECK (what > 0) ); SELECT b.a FROM B b; ALTER TABLE B DROP CONSTRAINT x;
When i run this in a fresh MariaDB instance (docker mariadb:10.3.28) everything works fine.
When MariaDB 10.3.29 was released the query suddenly broke. So, when i run this in a fresh MariaDB instance (docker mariadb:10.3.29) i get the following error message for the last statement:
[42S22] (conn=8) Unknown column '`db`.`b`.`what`' in 'CHECK'
Some weird observations:
- if i delete the
ycheck and add it after dropping
- if i remove the
bfrom the error message changes with the table alias from the
- running the last statement again shows the same error
- the error message makes no sense at all
So my question: what’s wrong with my SQL?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
This is actually a bug in MariaDB. See the bugtracker.
They recommended to do
FLUSH TABLES; before the
I can confirm that this "hides the bug".
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂