Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-33313

Incorrect error message for "ALTER TABLE ... DROP CONSTRAINT ..., DROP col, DROP col"

Details

    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.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          marko Marko Mäkelä added a comment -

          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).

          marko Marko Mäkelä added a comment - 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).
          serg Sergei Golubchik added a comment - - edited

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

          sql/sql_table.cc

          		 
          8755
          		 
               while ((drop=drop_it++)) {
          		 
          8756
          		 
                 switch (drop->type) {
          		 
          8757
          		 
                 case Alter_drop::KEY:
          		 
          8758
          		 
                 case Alter_drop::COLUMN:
          		 
          8759
          		 
                 case Alter_drop::CHECK_CONSTRAINT:
          		 
          8760
          		 
                 case Alter_drop::PERIOD:
          		 
          8761
          		 
                   my_error(ER_CANT_DROP_FIELD_OR_KEY, MYF(0), drop->type_name(),
          		 
          8762
          		 
                            alter_info->drop_list.head()->name);
          		 
          8763
          		 
                   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

          serg Sergei Golubchik added a comment - - edited marko , the bug was that it says "COLUMN" and used FK name. Because of sql/sql_table.cc 8755 while ((drop=drop_it++)) { 8756 switch (drop->type) { 8757 case Alter_drop::KEY: 8758 case Alter_drop::COLUMN: 8759 case Alter_drop::CHECK_CONSTRAINT: 8760 case Alter_drop::PERIOD: 8761 my_error(ER_CANT_DROP_FIELD_OR_KEY, MYF(0), drop->type_name(), 8762 alter_info->drop_list.head()->name); 8763 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
          marko Marko Mäkelä added a comment -

          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.

          marko Marko Mäkelä added a comment - 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.

          People

            serg Sergei Golubchik
            karll Karl Levik
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.