[#MDEV-33313] Incorrect error message for "ALTER TABLE ... DROP CONSTRAINT ..., DROP col, DROP col"

[MDEV-33313] Incorrect error message for "ALTER TABLE ... DROP CONSTRAINT ..., DROP col, DROP col" Created: 2024-01-25 Updated: 2024-01-26
Status:	In Testing
Project:	MariaDB Server
Component/s:	Data Definition - Alter Table
Affects Version/s:	10.8.3, 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3
Fix Version/s:	10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3

Type:

Bug

Priority:

Minor

Reporter:

Karl Levik

Assignee:

Sergei Golubchik

Resolution:

Unresolved

Votes:

Labels:

error

Description

The error message we get for statements of the type "ALTER TABLE ... DROP CONSTRAINT ..., DROP col, DROP col" does not make sense. Example below.

create table t2(id int primary key);

create table t1(id int primary key, t2_id int, constraint t1_fk_t2_id foreign key(t2_id) references t2(id));

Then try to drop the constraint and the t2_id column from table t1, but accidentally give two "drop t2_id" clauses.

alter table t1 drop constraint t1_fk_t2_id, drop t2_id, drop t2_id;

ERROR 1091 (42000): Can't DROP COLUMN `t1_fk_t2_id`; check that it exists

This not a sensible or correct error message. I would have expected the error message to refer to column t2_id rather than a non-existing column with an identical name to the constraint we're trying to drop.

I tested this on MariaDB Server 10.8.3 (with a newer mariadb client), but suspect it's present in newer versions as well.

Comments

Comment by Marko Mäkelä [ 2024-01-26 ]

It looks like this issue exists since the creation of MariaDB Server 10.0 when some code was imported from MySQL 5.6. Both in MySQL and MariaDB, InnoDB specific error messages were discouraged, so I had used the closest applicable one, ER_CANT_DROP_FIELD_OR_KEY, in WL#5545 Implement simple in-place ALTER TABLE operations: DROP_FOREIGN_KEY (MySQL 5.6.6).

Comment by Sergei Golubchik [ 2024-01-26 ]

marko, the bug was that it says "COLUMN" and used FK name. Because of


     while ((drop=drop_it++)) {
       switch (drop->type) {
       case Alter_drop::KEY:
       case Alter_drop::COLUMN:
       case Alter_drop::CHECK_CONSTRAINT:
       case Alter_drop::PERIOD:
         my_error(ER_CANT_DROP_FIELD_OR_KEY, MYF(0), drop->type_name(),
                  alter_info->drop_list.head()->name);
         goto err;

where, as you can see, the word "COLUMN" comes from some element in the middle of the list drop->type_name(), but the name comes from the first element in the drop list, thus the mismatch. The correct message should've been

ERROR 1091 (42000): Can't DROP COLUMN `t2_id`; check that it exists

Comment by Marko Mäkelä [ 2024-01-26 ]

Oh, I see that the error message had been refined to include the type of the object, in MariaDB Server 10.2. I can’t find in InnoDB the code snippet that you quoted in mariadb-10.8.3, or in any head of current version branch.

Generated at Thu Feb 08 10:37:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-33313] Incorrect error message for "ALTER TABLE ... DROP CONSTRAINT ..., DROP col, DROP col" Created: 2024-01-25 Updated: 2024-01-26