MySQL Bugs: #59262: broken replication - 5.1 master, 5.5 slave with SBR and timestamp(N)

Bug #59262	broken replication - 5.1 master, 5.5 slave with SBR and timestamp(N)
Submitted:	3 Jan 2011 18:11	Modified:	10 Feb 2011 11:12
Reporter:	Lig Isler-Turmelle	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1 and 5.5	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
Situation:
1) Master - 5.1
2) Slave - 5.5
3) SBR used (maybe Mixed also - though not tested.  Will let you know about that)
4) Issue a statement that that includes timestamp(N) (Ex: ALTER, CREATE)  This is deprecated in 5.1 and removed in 5.5.3 - see http://dev.mysql.com/doc/refman/5.5/en/news-5-5-3.html
5) statement is written to binary log *as is*
6) statement gets to slave and breaks replication with a *syntax error*

This is expected behavior since both servers are operating correctly but it is not documented anywhere that I have been able to find.  

This is a gotcha that could be hit more often as 5.5 is tested and moved into production.

How to repeat:
On 5.1 master, statement given:

mysql> create table test (timestamp timestamp(14));
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'TIMESTAMP(14)' is deprecated and will be removed in a future release. Please use 'TIMESTAMP' instead |
+---------+------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Binary log contents:

Macintosh-7:~ ligaya$ ./mysql_installs/mysql-advanced-gpl-5.1.54-osx10.5-x86/bin/mysqlbinlog ./mysql_installs/mysql-advanced-gpl-5.1.54-osx10.5-x86/data/mysql-bin.000003 
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#110103 12:48:49 server id 1  end_log_pos 106 	Start: binlog v 4, server v 5.1.54-enterprise-gpl-advanced-log created 110103 12:48:49
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
AQwiTQ8BAAAAZgAAAGoAAAABAAQANS4xLjU0LWVudGVycHJpc2UtZ3BsLWFkdmFuY2VkLWxvZwAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#110103 12:49:25 server id 1  end_log_pos 212 	Query	thread_id=1	exec_time=0	error_code=0
use test/*!*/;
SET TIMESTAMP=1294076965/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table test (timestamp timestamp(14))     <<<--- HERE
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Suggested fix:
Document gotcha and Note that the timestamp should no longer use (N).

Or if people feel energetic and have time - alter binary log logic to strip the (N) value out...

Did a quick test and it also shows up in the binary log in Mixed mode.

If I merely document the current behaviour, I'll be enshrining an unnecessary "gotcha" in the docs. I really think it's preferable that the 5.5 or later server should strip out the "(N)" or otherwise handle this issue gracefully. 

Therefore, I'm asking that this be considered a Server bug, and I've changed category/status to Server:Replication/Open, and removed verifier/assignee/lead accordingly.

(Note: Once the bug is [hopefully] fixed, I'll naturally include a note in the docs that this was an issue when replicating from <=5.1.x to 5.5.1 <= ... <= 5.5.{$fixed}.)

Initial deprecation statement for TIMESTAMP(N) found in http://dev.mysql.com/doc/refman/5.1/en/news-5-1-8.html

Some additional information: 

- TIMESTAMP(N) was deprecated in 5.1.8:
  http://dev.mysql.com/doc/refman/5.1/en/news-5-1-8.html

- TIMESTAMP(N) was removed in 5.5.3:
  http://bugs.mysql.com/bug.php?id=48048
  http://dev.mysql.com/doc/mysqld-version-reference/en/ch05s03s06.html

This is actually a documentation issue; (re)setting category/status/lead accordingly.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html