1

I am currently running a master - slave replication where on my slave there is an sql error due to which my replication was stopped.

when I tried to run the following good old solution

SHOW SLAVE STATUS \G; STOP SLAVE; SET GLOBAL sql_slave_skip_counter = 1; START SLAVE; SHOW SLAVE STATUS \G;

it didn't work. The Skip_Counter shows a value of 1 but when I again ran SHOW SLAVE STATUS the value was decreased to 0 but the error was still there. Along with that, Exec_Master_Log_Pos doesn't change.

is there any thing wrong with my expectation ?

EDIT

Error is as follows:

Error 'String 'abcde_abcdefgh_abcd' is too long for user name (should be no longer than 16)' on query. Default database: ''. Query: 'GRANT SELECT ON mydb.* TO 'abcde_abcdefgh_abcd'@'192.168.1.4' IDENTIFIED WITH 'mysql_native_password' AS 'PASSWORD_HASH'

My Master version is 5.7 and that on slave is 5.6. Upgrading the slave did solve the problem as the query could be executed on slave as well but my question is still there. Why couldn't skip counter solve the replication blockage ?

Improve this question

2 Answers 2

Reset to default
1

From the release notes for 5.7.8:

"The maximum length of MySQL user names has been increased from 16 characters to 32 characters, which provides greater flexibility in choosing the user name part of MySQL account names. The change affects permitted user names in these contexts: ..."

followed by a long list of "Account Management Notes". That was in addition to some "Incompatible Changes" in 5.7.6.

I guess that is one reason for advising against the Master running a newer version than the Slave.

It should be possible to rummage through the binlog using mysqldumpbinlog and find the pos of the next command. Then do CHANGE MASTER ... to change just the position.

(Meanwhile, I am surprised that the SKIP... failed.)

Bottom line: Previously, it was often safe to mix Master/Slave versions. But 5.7 has so many significant changes that I suspect this is just one of many potential problems.

Improve this answer
0

What was the exact error you faced ? Did Exec_Master_Log_Pos change post skipping the error ? It may have happened that you did skip the error and there was another error post that (like in case of duplicate key errors)

Improve this answer
5
  • as mentioned in the question Exec_Master_Log_Pos didn't change Commented Jun 25, 2016 at 13:04
  • also the error remained all the same when I started the slave after setting skip counter. Commented Jun 29, 2016 at 10:47
  • What was the error ? Commented Jun 30, 2016 at 3:56
  • Error 'String 'abcde_abcdefgh_abcd' is too long for user name (should be no longer than 16)' on query. Default database: ''. Query: 'GRANT SELECT ON mydb.* TO 'abcde_abcdefgh_abcd'@'192.168.1.4' IDENTIFIED WITH 'mysql_native_password' AS 'PASSWORD_HASH' Commented Jul 1, 2016 at 6:29
  • @KinaanKhanSherwani - I've tested the scenario and mysql skips the error as it should. I don't know what could've caused it not to do so on your side but fwiw it is not recommended to have a master with a higher version than its slaves (ie 5.7 master and 5.6 slaves) Commented Jul 3, 2016 at 12:55

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.