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

  • which exact versions of mysql 5.6 and 5.7 are you using? – jerichorivera Oct 28 '16 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 '16 at 11:05
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';
| improve this answer | |
  • 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 '16 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. – jerichorivera Oct 28 '16 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 '16 at 12:53
  • i don't think you missed anything, but i think mysql_upgrade should check and update the system tables accordingly – jerichorivera Oct 28 '16 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 '16 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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