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 ?

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.

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)

  • as mentioned in the question Exec_Master_Log_Pos didn't change – Kinaan Khan Sherwani Jun 25 '16 at 13:04
  • also the error remained all the same when I started the slave after setting skip counter. – Kinaan Khan Sherwani Jun 29 '16 at 10:47
  • What was the error ? – AnonDude Jun 30 '16 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' – Kinaan Khan Sherwani Jul 1 '16 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) – jerichorivera Jul 3 '16 at 12:55

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.