MySQL Bugs: #20450: Problem of replication after having added 1 column in a table on slave side

Bug #20450	Problem of replication after having added 1 column in a table on slave side
Submitted:	14 Jun 2006 8:32	Modified:	15 Oct 2009 9:21
Reporter:	Daniel Stricot	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Cluster: Replication	Severity:	S2 (Serious)
Version:	mysql-5.1	OS:	Linux (LINUX)
Assigned to:		CPU Architecture:	Any
Tags:	5.1.12-BK, 5.1.8

Description:
After having launched the replication between a Master and a Slave, we add a column in a table on the slave side. Then, we launch modifications in the same table on the master side (no access to the new column added) , which update or delete some records.
Some modifications on the server side are not replicated !
It works correctly with the release V5.1.2 DROP5V14.

Our test reference : MDSXMVC032.

See attached files.

How to repeat:
/* mated pair replication with compatible schemas       */
1) launch 2 vmware in configuration simplex/mated pair :
  vm10 : MASTER (createMasterSimplex)
  vm11 : SLAVE  (createSlaveSimplex)
2) launch createtable.sql and populate the database (basic-type-popul) on the vm10 (MASTER)
3) init MASTER_LOG_FILE and MASTER_LOG_POS on the slave by the command
   'change master' and start the slave on the vm11
4) on the slave side, modify the schema by adding a column in a table
   alter table IMSI add vInt2 int(11);
5) launch some operations modifying the database (basic-type-ope) on the master side
6) check that the replication fails, for example record 2 in table hlr.

trace execution master side

Attachment: tracevm10 (application/octet-stream, text), 10.41 KiB.

trace execution slave side

Attachment: tracevm11 (application/octet-stream, text), 11.56 KiB.

C++ program to populate table on master side

Attachment: basic-type-popul.cpp (text/x-c++), 22.97 KiB.

C++ program to modify table

Attachment: basic-type-ope.cpp (text/x-c++), 16.99 KiB.

configuration file

Attachment: config.ini (application/octet-stream, text), 1.50 KiB.

Thank you for a problem report. Can it be so that you use row-based replication? Please, send the results of 

SHOW VARIABLES LIKE 'binlog%';

Read http://dev.mysql.com/doc/refman/5.1/en/replication-row-based.html for the details.

Can you try to repeat with a newer version, 5.1.11?

On master side :
mysql> SHOW VARIABLES LIKE 'binlog%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
| binlog_format     | ROW   |
+-------------------+-------+
2 rows in set (0.00 sec)
mysql>

On slave side:
mysql> SHOW VARIABLES LIKE 'binlog%';
+-------------------+-----------+
| Variable_name     | Value     |
+-------------------+-----------+
| binlog_cache_size | 32768     |
| binlog_format     | STATEMENT |
+-------------------+-----------+
2 rows in set (0.01 sec)

The binlog_format is different. I tried to launch mysqld process on the slave side with the option binlog_format = ROW, it was unsuccessful.
After I played the mysql statement (after having launched cluster processes like before) : 
SET GLOBAL binlog_format = 'ROW';

So, I got the same binlog_format on the 2 sides (ROW).
it was successful. I tried another time the test I did, it was always unsuccessful.

I shall redo the test with the version 5.1.12 .

I did the test with the version 5.1.12-beta, I got the same results.
I confirm the problem.

Is this still valid on latest version?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This should now work in later version of MySQL with binary log format set to 'ROW'. If still problem, need to open new bug report.