Description:
[ERROR] Error creating relay log info: Error transfering information.
Upon a slave server crash, if the relay_log_info_repository variable used TABLE and not FILE and set via GLOBAL variables the upon restart the server dynamically tries to switch to FILE and a reference to log files is lost in the relay-bin.index file resulting in failure of slave start with the error "[ERROR] Error creating relay log info: Error transfering information."
ie: ( SET GLOBAL relay_log_info_repository = 'TABLE'; only used not in my.cnf )
How to repeat:
SO I have a master to slave set up on my laptop. ( laptop to local Virtual box instance both running the latest DMR.)
I set the following in the servers :
SET GLOBAL master_info_repository = 'TABLE';
SET GLOBAL relay_log_info_repository = 'TABLE';
SET GLOBAL slave_parallel_workers=4;
I then created a table and event . It just populated a simple table every 5 seconds.
CREATE EVENT e_test
ON SCHEDULE
EVERY 1 SECOND
DO
INSERT INTO test.t1 VALUES (NULL);
I then killed the virtual box instance to mimic a power outage. Once the Virtual box was back up and mysql was up ( I had the slave off to start ) I then started the slave. I tried to skip a query to get past a duplicate error as well but no luck. I removed the conflict on the slave then the error showed up.
I then got the following information below in my logs.
[ERROR] Error creating relay log info: Error transfering information.
mysql> select * from slave_relay_log_info;
+-----------+-----------------+--------------------------------------+---------------+-----------------+----------------+-----------+-------------------+
| Master_id | Number_of_lines | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos | Sql_delay | Number_of_workers |
+-----------+-----------------+--------------------------------------+---------------+-----------------+----------------+-----------+-------------------+
| 2 | 6 | /var/lib/mysql/luke-relay-bin.000006 | 1043 | yoda-bin.000004 | 146242 | 0 | 4 |
+-----------+-----------------+--------------------------------------+---------------+-----------------+----------------+-----------+-------------------+
1 row in set (0.00 sec)
## Log info below ##
InnoDB: Last MySQL binlog file position 0 130615, file name /var/lib/mysql/luke-bin.000006
120112 16:51:28 InnoDB: 128 rollback segment(s) are active.
120112 16:51:28 InnoDB: Waiting for the background threads to start
120112 16:51:29 InnoDB: 1.2.4 started; log sequence number 26459100
120112 16:51:29 [Note] Recovering after a crash using /var/lib/mysql/luke-bin
120112 16:51:30 [Note] Starting crash recovery...
120112 16:51:30 [Note] Crash recovery finished.
...
120112 16:54:47 [Note] Slave SQL thread initialized, starting replication in log 'yoda-bin.000004' at position 145983, relay log '/var/lib/mysql/luke-relay-bin.000006' position: 784
120112 16:54:47 [Note] 'SQL_SLAVE_SKIP_COUNTER=1' executed at relay_log_file='/var/lib/mysql/luke-relay-bin.000006', relay_log_pos='784', master_log_name='yoda-bin.000004', master_log_pos='145983' and new position at relay_log_file='/var/lib/mysql/luke-relay-bin.000006', relay_log_pos='1043', master_log_name='yoda-bin.000004', master_log_pos='146242'
120112 16:54:47 [ERROR] Slave SQL: Error 'Duplicate entry '1063' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO test.t1 VALUES (NULL)', Error_code: 1062
120112 16:54:47 [Warning] Slave: Duplicate entry '1063' for key 'PRIMARY' Error_code: 1062
120112 16:54:47 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'yoda-bin.000004' position 146242
120112 16:56:02 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
120112 16:56:02 [Note] Slave I/O thread killed while reading event
120112 16:56:02 [Note] Slave I/O thread exiting, read up to log 'yoda-bin.000004', position 230397
....
120112 17:04:40 [ERROR] Error creating relay log info: Error transfering information.
120112 17:07:26 [ERROR] Error creating relay log info: Error transfering information.
120112 17:12:12 [ERROR] Error creating relay log info: Error transfering information.
Suggested fix:
Since the master.info file is removed when master_info_repository = 'TABLE' is set any reference to data resulting from "select * from slave_relay_log_info" should default the system to TABLE.
The my.cnf file should have this information anyway but would not be needed if the dynamic switch at startup checked the slave_relay_log_info table.
Description: [ERROR] Error creating relay log info: Error transfering information. Upon a slave server crash, if the relay_log_info_repository variable used TABLE and not FILE and set via GLOBAL variables the upon restart the server dynamically tries to switch to FILE and a reference to log files is lost in the relay-bin.index file resulting in failure of slave start with the error "[ERROR] Error creating relay log info: Error transfering information." ie: ( SET GLOBAL relay_log_info_repository = 'TABLE'; only used not in my.cnf ) How to repeat: SO I have a master to slave set up on my laptop. ( laptop to local Virtual box instance both running the latest DMR.) I set the following in the servers : SET GLOBAL master_info_repository = 'TABLE'; SET GLOBAL relay_log_info_repository = 'TABLE'; SET GLOBAL slave_parallel_workers=4; I then created a table and event . It just populated a simple table every 5 seconds. CREATE EVENT e_test ON SCHEDULE EVERY 1 SECOND DO INSERT INTO test.t1 VALUES (NULL); I then killed the virtual box instance to mimic a power outage. Once the Virtual box was back up and mysql was up ( I had the slave off to start ) I then started the slave. I tried to skip a query to get past a duplicate error as well but no luck. I removed the conflict on the slave then the error showed up. I then got the following information below in my logs. [ERROR] Error creating relay log info: Error transfering information. mysql> select * from slave_relay_log_info; +-----------+-----------------+--------------------------------------+---------------+-----------------+----------------+-----------+-------------------+ | Master_id | Number_of_lines | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos | Sql_delay | Number_of_workers | +-----------+-----------------+--------------------------------------+---------------+-----------------+----------------+-----------+-------------------+ | 2 | 6 | /var/lib/mysql/luke-relay-bin.000006 | 1043 | yoda-bin.000004 | 146242 | 0 | 4 | +-----------+-----------------+--------------------------------------+---------------+-----------------+----------------+-----------+-------------------+ 1 row in set (0.00 sec) ## Log info below ## InnoDB: Last MySQL binlog file position 0 130615, file name /var/lib/mysql/luke-bin.000006 120112 16:51:28 InnoDB: 128 rollback segment(s) are active. 120112 16:51:28 InnoDB: Waiting for the background threads to start 120112 16:51:29 InnoDB: 1.2.4 started; log sequence number 26459100 120112 16:51:29 [Note] Recovering after a crash using /var/lib/mysql/luke-bin 120112 16:51:30 [Note] Starting crash recovery... 120112 16:51:30 [Note] Crash recovery finished. ... 120112 16:54:47 [Note] Slave SQL thread initialized, starting replication in log 'yoda-bin.000004' at position 145983, relay log '/var/lib/mysql/luke-relay-bin.000006' position: 784 120112 16:54:47 [Note] 'SQL_SLAVE_SKIP_COUNTER=1' executed at relay_log_file='/var/lib/mysql/luke-relay-bin.000006', relay_log_pos='784', master_log_name='yoda-bin.000004', master_log_pos='145983' and new position at relay_log_file='/var/lib/mysql/luke-relay-bin.000006', relay_log_pos='1043', master_log_name='yoda-bin.000004', master_log_pos='146242' 120112 16:54:47 [ERROR] Slave SQL: Error 'Duplicate entry '1063' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO test.t1 VALUES (NULL)', Error_code: 1062 120112 16:54:47 [Warning] Slave: Duplicate entry '1063' for key 'PRIMARY' Error_code: 1062 120112 16:54:47 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'yoda-bin.000004' position 146242 120112 16:56:02 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 120112 16:56:02 [Note] Slave I/O thread killed while reading event 120112 16:56:02 [Note] Slave I/O thread exiting, read up to log 'yoda-bin.000004', position 230397 .... 120112 17:04:40 [ERROR] Error creating relay log info: Error transfering information. 120112 17:07:26 [ERROR] Error creating relay log info: Error transfering information. 120112 17:12:12 [ERROR] Error creating relay log info: Error transfering information. Suggested fix: Since the master.info file is removed when master_info_repository = 'TABLE' is set any reference to data resulting from "select * from slave_relay_log_info" should default the system to TABLE. The my.cnf file should have this information anyway but would not be needed if the dynamic switch at startup checked the slave_relay_log_info table.