MySQL Bugs: #94013: Add PAD_CHAR_TO_FULL_LENGTH will cause master/slave replication status is error

Bug #94013	Add PAD_CHAR_TO_FULL_LENGTH will cause master/slave replication status is error
Submitted:	23 Jan 2019 9:08	Modified:	31 Jan 2019 16:31
Reporter:	Lei Peng Peng	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.7.24	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
I create a master and slave instance of msyql and I set master_info_repository=TABLE 
and relay_log_info_repository=TABLE in master and slave. if i add PAD_CHAR_TO_FULL_LENGTH to sql_mode and restart the slave, master/slave replication status is abnormal. I found Slave_IO_Running=No and Slave_SQL_Running=No by show slave status.I also found the relay log named "xxx-relay-bin-@0020@0020@0020@0020@0020.....00001" in data directory. I found the error message "[ERROR] Failed to open the relay log './xxx-relay-bin.000001' (relay_log_pos 4), Could not find target log file mentioned in relay log info in the index file './xxx-relay-bin-@002    @0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020.index' during relay log initialization" in error log.

How to repeat:
step 1:
 create master and slave mysql .
 in my.cnf ,need add the following parameters:
 master_info_repository=TABLE
 relay_log_info_repository=TABLE
 sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH"

step 2:on master
 CREATE USER 'penglei'@'192.168.1.149' IDENTIFIED BY 'penglei';
 GRANT REPLICATION SLAVE ON *.* TO 'penglei'@'192.168.1.149';

step 3:on slave
 CHANGE MASTER TO MASTER_HOST='192.168.1.149', MASTER_PORT=33061,MASTER_USER='penglei',MASTER_PASSWORD='penglei', MASTER_LOG_FILE='binlog.000001',MASTER_LOG_POS=0

 start slave

step 3:on master
 create database test;

step 4:on slave 
 show slave status\G;
 It is ok.

step 5:on slave 
 service mysqld restart

step 6:on slave
 show slave status\G;
 it is abnormal.Slave_IO_Running=No and Slave_SQL_Running=No

Suggested fix:
nothing

Hi,
I don't believe this is a bug, what is your Last_IO_Error in the show slave status?

all best
Bogdan

p.s. note that PAD_CHAR_TO_FULL_LENGTH can interfere with storing replication data in table

Hi Bogdan Kecman
show slave status\G; I got:
Last_IO_Errno: 1871
Last_IO_Error: Slave failed to initialize master info structure from the repository

in error.log:

199 2019-01-31T08:43:30.843523Z 0 [ERROR] Error writing master configuration.
200 2019-01-31T08:43:30.843536Z 0 [ERROR] Error reading master configuration.
201 2019-01-31T08:43:30.843704Z 0 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL se    rver acts as a slave and has his hostname changed!! Please use '--relay-log=ecs-dt-relay-bin' to avoid this problem.
202 2019-01-31T08:43:30.854594Z 0 [ERROR] Failed to open the relay log './ecs-dt-relay-bin.000001' (relay_log_pos 4).
203 2019-01-31T08:43:30.854605Z 0 [ERROR] Could not find target log file mentioned in relay log info in the index file './ecs-dt-relay-bin-@    0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0    020@0020@0020@0020@0020@0020@0020@0020@0020@0020@0020.index' during relay log initialization.
204 2019-01-31T08:43:30.859913Z 0 [ERROR] Slave: Failed to initialize the master info structure for channel '                                                                    '; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
205 2019-01-31T08:43:30.860246Z 0 [ERROR] Failed to create or recover replication info repositories.
206 2019-01-31T08:43:30.860273Z 0 [ERROR] Slave I/O for channel '                                                                ': Slave fa    iled to initialize master info structure from the repository, Error_code: 1871
207 2019-01-31T08:43:30.860279Z 0 [ERROR] /usr/local/mysql/bin/mysqld: Slave failed to initialize master info structure from the repository
208 2019-01-31T08:43:30.860282Z 0 [ERROR] Failed to start slave threads for channel '

I think this is a bug.
If I config master_info_repository=TABLE,relay_log_info_repository=TABLE,sql_mode="PAD_CHAR_TO_FULL_LENGTH" in master and slave, I will get an error after restart slave. I think this is a bug.I also retry to start slave , but SLAVE_IO and SQL_IO ard not running.

all best
Lei Peng

Hi,

The padding on and table storage makes the channel name 
'                                                                '
and that is what I think creates the problem. As I said not sure if this is a bug or not but since I can reproduce the problem and since the replication don't work in this case I'll verify the bug, and let replication engineers say if they'll fix it or not. Now, dropping it to S3 as this def. is not S2 type bug.

Thanks for your report
Bogdan