MySQL Bugs: #99074: Fail to do PITR with alter schema statement

Bug #99074	Fail to do PITR with alter schema statement
Submitted:	26 Mar 2020 2:49	Modified:	24 May 2020 18:47
Reporter:	Cai Minshen	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
I run below SQL to create a database test03 and then change its default collation server to utf8. But if I do PITR on other restored MySQL, the default collation server of test03 is latin1. Please refer to "How to Repeat".

In my opinion, the root reason is the query event for the alter schema doesn't record the DB information. By this, when I do PITR on the restored database, it doesn't alter database test03 in actual.

I reproduce such issue on V5.6 & V5.7

How to repeat:
Run below Sql on source server. The default collation of the new created database is utf8_bin:
<<<<<<<<<<<<<<<<<<<
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
CREATE DATABASE test03;
use test03;
ALTER SCHEMA DEFAULT COLLATE utf8_bin;
<<<<<<<<<<<<<<<<<<<<

The below is the generated sql from the above binlog events:
<<<<<<<<<<<<<<<<<<<<<<<<<<
# at 120
#200326 10:30:55 server id 1963691799  end_log_pos 220 CRC32 0x097bd8d8 	Query	thread_id=129	exec_time=0	error_code=0
SET TIMESTAMP=1585189855/*!*/;
SET @@session.pseudo_thread_id=129/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@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 DATABASE test03
/*!*/;
# at 220
#200326 10:31:40 server id 1963691799  end_log_pos 326 CRC32 0x4ff16a33 	Query	thread_id=129	exec_time=0	error_code=0
SET TIMESTAMP=1585189900/*!*/;
ALTER SCHEMA DEFAULT COLLATE utf8_bin
/*!*/;
<<<<<<<<<<<<<<<<<<<<<<<<<<

Then do PITR on other MySQL. At this time, the default collation of the new created database test03 is latin1.

Hi Cai,

What exact version of MySQL 5.7 you can reproduce this on. I tried 5.7.28 and while the binlog looks similar

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 DATABASE test05
/*!*/;
# at 490
#200326 20:18:52 server id 100  end_log_pos 555 CRC32 0x1fa353c4        Anonymous_GTID  last_committed=2        sequence_number=3rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 555
#200326 20:18:52 server id 100  end_log_pos 661 CRC32 0xc3940284        Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1585250332/*!*/;
ALTER SCHEMA DEFAULT COLLATE utf8_bin
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[arhimed@localdev master]$

point in time recovery has this database properly with default utf8.

If you send me your full config files I can try to find out where is the problem.

All best
Bogdan

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".