[MDEV-4106] ALTER TABLE races in replication on slave side Created: 2013-01-28  Updated: 2013-03-14  Resolved: 2013-03-14

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.0
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Gordan Bobic Assignee: Elena Stepanova
Resolution: Incomplete Votes: 0
Labels: None
Environment:

Linux RHEL6


 Description   

Setup:

Master: Percona 5.5.24-rel26.0.256
Middle: Percona 5.5.20-rel24.1.217
Slave: MariaDB-10.0.0

On Master:
Say you have the following table:

CREATE TABLE `audit_publication_publication_rtb` (
`audit_publication_publication_rtb_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`publication_id` int(10) unsigned NOT NULL,
`old_publisher_id` int(10) unsigned DEFAULT NULL,
`old_rtb_id` varchar(255) DEFAULT NULL,
`new_publisher_id` int(10) unsigned DEFAULT NULL,
`new_rtb_id` varchar(255) DEFAULT NULL,
`record_action` int(1) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`audit_publication_publication_rtb_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This table is being inserted into constantly.

I then run the following statement:
ALTER TABLE adfonic.audit_publication_publication_rtb
ADD old_status VARCHAR(32) AFTER old_rtb_id,
ADD new_status VARCHAR(32) AFTER new_rtb_id;

This propagates fine to the Middle server.

However, on the Slave server running MariaDB 10.0.0, this seems to cause replication to break:

Slave_SQL_Running is No
Last_Error is Column 4 of table 'db.audit_publication_publication_rtb' cannot be converted from type 'varchar(96)' to type 'int(10) unsigned', Last_SQL_Error is Column 4 of table 'db.audit_publication_publication_rtb' cannot be converted from type 'varchar(96)' to type 'int(10) unsigned'

At a glance, it looks like the inserts in new format are somehow overtaking the alter table statement in the replication stream.

 Comments   
Comment by Elena Stepanova [ 2013-01-28 ]

Hi Gordan,

Can you locate and paste the fragments from the binary log files (master and middle servers) related to guilty statement/event and preceding it?

Finding them in the middle server binlog is simple, since the slave's error log provides the exact binlog name and position where the problem occurred. Finding them in the master log might be trickier, but should also be possible.

Comment by Gordan Bobic [ 2013-01-28 ]

Additional info:

This replication failure also affects MySQL 5.6.6-m9.
Could it be that a commit in the 5.6.x branch of MySQL is responsible for this problem and it got pulled into MariaDB 10.0.0?

Comment by Gordan Bobic [ 2013-01-28 ]

I have uploaded a processed binog extract, with 100 preceeding and 100 following statements to ftp://ftp.askmonty.org/private/. The file name is prefixed with the ticket number. The slave reported the error at binlog position 495830995.

Comment by Elena Stepanova [ 2013-01-28 ]

There has always been a fair share of race conditions in binary logging involving DDL, so generally it's likely to be a legacy bug. What makes your case unusual though, is that if your middle server only works as a slave for 'master' and as a master for 'slave', without any direct inflow, there should be no concurrency at all. If it executes the statements in the correct order, it should write them the same way. That's why it's interesting to see the binary logs from both servers.

Do I understand correctly that you have the simple replication setup master => middle => slave, nothing fancy with slave using multi-master replication and getting updates from both master and middle?

Comment by Elena Stepanova [ 2013-01-28 ]

Thank you, I will check the logs shortly.

Comment by Gordan Bobic [ 2013-01-28 ]

The MySQL 5.6 server only had one master, but the MariaDB 10.0 server had extra masters (this was the main reason why we deployed them). None of the other masters, however, would have had that table on them.

Comment by Elena Stepanova [ 2013-01-28 ]

Gordan,

For the binary log file, this fragment does not contain the ALTER statement. Could you please extract the part where both ALTER and the guilty event (and some more after it) are included?
While doing so, please run mysqlbinlog with "--base64-output=DECODE-ROWS --verbose" options, it will make the result readable.

Also, if you have access to the middle and slave servers, could you run SHOW CREATE TABLE for the table in question on each of them and provide the output?

Finally, if possible, please attach your middle and slave cnf files.

Thanks!

Comment by Elena Stepanova [ 2013-03-08 ]

Hi Gordan,

Are you still having the problem?

Looking at the description again, I got a question. Are using any replicate-do-db, replicate-ignore-db, or replicate-rewrite-db options?
Your ALTER TABLE works on adfonic.audit_publication_publication_rtb, while the replication error is about db.audit_publication_publication_rtb, so I assume you have (or expect to have) adfonic => db mapping?
But then again, replicate-rewrite-db only works if the source database is a default database on masteer, which might well not to be true in your setup. It's possible that ALTER ends up being ignored.

So, if you are still interested in this report, please provide config files from all involved servers, and if possible the fragments of binary logs which contain the ALTER statement.

Thanks.

Comment by Elena Stepanova [ 2013-03-14 ]

Closing it as incomplete for now, if you have any new information, please add it to re-open the report.

Generated at Thu Feb 08 06:53:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.