[MDEV-14738] Replication breaks when dropping a column of a combined primary key (with a confusing error message) Created: 2017-12-21  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.2.11
Fix Version/s: 10.4

Type: Bug Priority: Minor
Reporter: Patrick Kummutat Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-13613 Dropping column from table that is pa... Closed
duplicates MDEV-21097 Incorrect error message when trying t... Closed
Problem/Incident
is caused by MDEV-11114 Cannot drop column referenced by CHEC... Closed

 Description   

Hi,

replication breaks when I'm dropping a column which is part of a combined primary key.

Master: 10.1.29 Version
Slave: 10.2.11 Version

Howto reproduce:

MariaDB [test]> create table t1(i1 int not null,f1 varchar(10) not null, f2 varchar(20) not null, f3 varchar(30), primary key (i1,f1,f2));
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [test]> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE "t1" (
  "i1" int(11) NOT NULL,
  "f1" varchar(10) NOT NULL,
  "f2" varchar(20) NOT NULL,
  "f3" varchar(30) DEFAULT NULL,
  PRIMARY KEY ("i1","f1","f2")
) |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
MariaDB [test]> alter table t1 drop column f1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
MariaDB [test]> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.1.29-MariaDB |
+-----------------+
1 row in set (0.00 sec)

On Slave:

MariaDB [test]> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE "t1" (
  "i1" int(11) NOT NULL,
  "f1" varchar(10) NOT NULL,
  "f2" varchar(20) NOT NULL,
  "f3" varchar(30) DEFAULT NULL,
  PRIMARY KEY ("i1","f1","f2")
) |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
MariaDB [test]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 17541
               Relay_Log_File: relay-bin.000003
                Relay_Log_Pos: 1072
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1072
                   Last_Error: Error 'Key column 'f1' doesn't exist in table' on query. Default database: 'test'. Query: 'alter table t1 drop column f1'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 17403
              Relay_Log_Space: 1993
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1072
               Last_SQL_Error: Error 'Key column 'f1' doesn't exist in table' on query. Default database: 'test'. Query: 'alter table t1 drop column f1'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100215
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
1 row in set (0.00 sec)
 
MariaDB [test]> alter table t1 drop column f1;
ERROR 1072 (42000): Key column 'f1' doesn't exist in table
MariaDB [test]> select @@version;
+---------------------+
| @@version           |
+---------------------+
| 10.2.11-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)

Regards,
Patrick



 Comments   
Comment by Elena Stepanova [ 2017-12-22 ]

The change in behavior in 10.2 vs 10.1 was intentional, it's a result of a bug fix. The same error would happen if one tried to drop a part of the primary key directly on the 10.2 server:

MariaDB [test]> create table t1(i int not null,f varchar(10) not null, primary key (i,f));
Query OK, 0 rows affected (0.34 sec)
 
MariaDB [test]> alter table t1 drop column f;
ERROR 1072 (42000): Key column 'f' doesn't exist in table

There is a more detailed explanation in comments to MDEV-11114 and MDEV-13613.

Replication error is an unfortunate but probably unavoidable side effect of this change.

However, what is undeniably wrong is the error message that's produced in this case; it should be fixed (it was also discussed in the above mentioned reports).

So, I'm keeping this bug report for at least fixing the error message. I'm not sure if it can be done in 10.2, but whenever it can be done, it should be.

Generated at Thu Feb 08 08:15:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.