1

When trying to do replication with 5.6 as the master and 5.7 as the slave, after source dump.sql I entered this;

mysql> CHANGE MASTER TO MASTER_HOST="<MASTER_HOSTNAME>", MASTER_USER="slave_user", MASTER_PASSWORD="password", MASTER_AUTO_POSITION = 1;

obviously I changed to an actual hostname, I got the following error;

ERROR 1805 (HY000): Column count of mysql.slave_relay_log_info is wrong. Expected 9, found 8. The table is probably corrupted

Is this a problem with using 5.6 as the master and 5.7 as the slave? I have tried the dump several times with the same error. Although is says it's supported here http://dev.mysql.com/doc/refman/5.7/en/replication-compatibility.html

2
  • which exact versions of mysql 5.6 and 5.7 are you using? Oct 28, 2016 at 10:39
  • Master - mysql Ver 14.14 Distrib 5.6.33, for debian-linux-gnu (x86_64) using EditLine wrapper. Slave - mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper
    – eekfonky
    Oct 28, 2016 at 11:05

2 Answers 2

2

Drop the mysql.slave_relay_log_info table on the 5.7 slave. Log out of mysql, run mysql_upgrade -uroot -p -s to recreate the dropped table. Log back in to mysql instance then stop slave / start slave all should be good.

The problem was when you source dump.sql it somehow changed the table definition in 5.7 hence the error you were getting.

A simple test I tried was on a working 5.6 master to 5.7 slave repl, I dropped the slave_relay_log_info table on 5.7 and recreated it with 5.6's table structure and got the same error when I started slave replication.

mysql [localhost] {msandbox} ((none)) > use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql [localhost] {msandbox} (mysql) > CREATE TABLE `slave_relay_log_info` (
    ->   `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
    ->   `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
    ->   `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
    ->   `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
    ->   `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
    ->   `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
    ->   `Number_of_workers` int(10) unsigned NOT NULL,
    ->   `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
    ->   PRIMARY KEY (`Id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
Query OK, 0 rows affected (0.04 sec)

mysql [localhost] {msandbox} (mysql) > start slave;
ERROR 1805 (HY000): Column count of mysql.slave_relay_log_info is wrong. Expected 9, found 8. The table is probably corrupted

EDIT: You can drop the table with the wrong structure, and recreate it with this:

CREATE TABLE `mysql`.`slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  `Channel_name` char(64) NOT NULL COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication',
  PRIMARY KEY (`Channel_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
7
  • The --upgrade-system-tables option was used, databases won't be touched. Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. The sys schema is already up to date (version 1.5.1). Found 0 sys functions, but expected 22. Re-installing the sys schema. Upgrading the sys schema. Upgrade process completed successfully. Could not create the upgrade info file '/var/lib/mysql/mysql_upgrade_info' in the MySQL Servers datadir, errno: 13
    – eekfonky
    Oct 28, 2016 at 12:46
  • was the problematic table re-created with the correct table structure? you can also try my other suggestion. regarding errno: 13 it could be that the system user you ran mysql_upgrade didn't have write privs on the datadir. Oct 28, 2016 at 12:50
  • I ran it as root. Sorry I'm more sysadmin than DBA. Which mysqldump options should I use on the master on 5.6?
    – eekfonky
    Oct 28, 2016 at 12:53
  • i don't think you missed anything, but i think mysql_upgrade should check and update the system tables accordingly Oct 28, 2016 at 13:34
  • My other option is to create a new server with 5.7 on it and copy the data across and upgrade in place before I make it 'live' for the customer. Is this easier?
    – eekfonky
    Oct 28, 2016 at 13:45
0

Suggest you file a bug at http://bugs.mysql.com

It seems that the problem has to do with:

`Channel_name` char(64) NOT NULL COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication'

in the 5.7 version of mysql.slave_relay_log_info, but not in 5.6.

https://bugs.mysql.com/bug.php?id=82384 suggests that mysql.slave_master_info has trouble, too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.