[MDEV-14738] Replication breaks when dropping a column of a combined primary key (with a confusing error message) - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Minor
Resolution: Unresolved
Affects Version/s: 10.2.11
Fix Version/s: 10.4(EOL)
Component/s: Replication
Labels:
None

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

Attachments

Issue Links

duplicates

MDEV-13613 Dropping column from table that is part of a key fails in 10.2.8

Closed

MDEV-21097 Incorrect error message when trying to delete column which is part of unique key constraint

Closed

is caused by

MDEV-11114 Cannot drop column referenced by CHECK constraint

Closed

Activity

Elena Stepanova added a comment - 2017-12-22 17:48

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.

Elena Stepanova added a comment - 2017-12-22 17:48 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.

MariaDB Server

Replication breaks when dropping a column of a combined primary key (with a confusing error message)

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration