slave replication fails, cannot create user
I'm transitioning from one mysql server to another, in production use, by setting the new server up as a slave, so that when it's time to switch, we'll have very little downtime. Then we can turn off replication and the new slave will become the new production server. I set up a replication slave, tested it, and everything was fine. Then I stopped it, reset slave, and used it for something else. Now I want to make it a slave again, but it stops partway through catching up on the binary logs, with this error: Operation CREATE USER failed for 'replication'@'[host]' (where [host] is the slave's hostname) Here is the procedure I followed to make it a slave: 1. drop database our_main_db; create database our_main_db; 2. Brought over the most recent mysqldump from the master, which was created from a command like this: mysqldump --flush-logs --master-data=2 -uuser -ppasswd our_main_db ... and fed the dump's contents into the slave-to-be server. 3. Using the log file name and position from the master data in that dump file, issed a change master statement: CHANGE MASTER TO MASTER_HOST='masterserver.domain.name', MASTER_USER='replication', MASTER_PASSWORD='replicationuserpassword', MASTER_LOG_FILE='binlog.11', MASTER_LOG_POS=98; START SLAVE; ... everything was running fine for an hour or so, and the slave caught up through several days worth of logs from the master, but then it stopped with this error: Last_Errno: 1396 Last_Error: Error 'Operation CREATE USER failed for 'replication'@'[host]'' on query. Default database: 'mysql'. Query: 'create user 'replication'@'[host]' identified by 'replicationuserpassword'' (again, [host] is the slave's own hostname). I checked the mysql.user table and found that the [EMAIL PROTECTED] user *did* exist. So I removed it from the table, then tried to restart replication ... and got the same error. So then I went to the binary log on the master and, using mysqlbinlog, found the exact create user statement, and tried to run it by hand on the slave, which looked like this: create user 'replication'@'[host]' identified by 'replicationuserpasswd'; I tried running that by hand on the slave server (from the mysql root user) and got the error again: mysql create user 'replication'@'[host]' identified by 'replicationuserpasswd'; ERROR 1396 (HY000): Operation CREATE USER failed for 'replication'@'[host]' ... I solved the problem by adding slave_skip_errors=1396 to my.cnf and restarting the slave server. It was able to pick up replication and is now caught up with the master and seems to be fine. However, 1. I don't understand what caused the problem 2. I fear that after I un-slave it (we're planning to switch masters) I still won't be able to create users on this new server. -- Cos (Ofer Inbar) -- [EMAIL PROTECTED] So you're one of Jehovah's Witnesses. I'm Cthulhu's defence lawyer. prepare for cross-questioning -- Tanuki, on alt.sysadmin.recovery -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slave replication fails, cannot create user
Hi, Ofer Inbar wrote: I'm transitioning from one mysql server to another, in production use, by setting the new server up as a slave, so that when it's time to switch, we'll have very little downtime. Then we can turn off replication and the new slave will become the new production server. I set up a replication slave, tested it, and everything was fine. Then I stopped it, reset slave, and used it for something else. Now I want to make it a slave again, but it stops partway through catching up on the binary logs, with this error: Operation CREATE USER failed for 'replication'@'[host]' (where [host] is the slave's hostname) Here is the procedure I followed to make it a slave: 1. drop database our_main_db; create database our_main_db; 2. Brought over the most recent mysqldump from the master, which was created from a command like this: mysqldump --flush-logs --master-data=2 -uuser -ppasswd our_main_db ... and fed the dump's contents into the slave-to-be server. 3. Using the log file name and position from the master data in that dump file, issed a change master statement: CHANGE MASTER TO MASTER_HOST='masterserver.domain.name', MASTER_USER='replication', MASTER_PASSWORD='replicationuserpassword', MASTER_LOG_FILE='binlog.11', MASTER_LOG_POS=98; START SLAVE; ... everything was running fine for an hour or so, and the slave caught up through several days worth of logs from the master, but then it stopped with this error: Last_Errno: 1396 Last_Error: Error 'Operation CREATE USER failed for 'replication'@'[host]'' on query. Default database: 'mysql'. Query: 'create user 'replication'@'[host]' identified by 'replicationuserpassword'' (again, [host] is the slave's own hostname). I checked the mysql.user table and found that the [EMAIL PROTECTED] user *did* exist. So I removed it from the table, then tried to restart replication ... and got the same error. So then I went to the binary log on the master and, using mysqlbinlog, found the exact create user statement, and tried to run it by hand on the slave, which looked like this: create user 'replication'@'[host]' identified by 'replicationuserpasswd'; I tried running that by hand on the slave server (from the mysql root user) and got the error again: mysql create user 'replication'@'[host]' identified by 'replicationuserpasswd'; ERROR 1396 (HY000): Operation CREATE USER failed for 'replication'@'[host]' ... I solved the problem by adding slave_skip_errors=1396 to my.cnf and restarting the slave server. It was able to pick up replication and is now caught up with the master and seems to be fine. However, 1. I don't understand what caused the problem 2. I fear that after I un-slave it (we're planning to switch masters) I still won't be able to create users on this new server. What version of MySQL are you running on each machine? The statement might have failed because the user already existed, or because the slave's version was pre-5.0.3... just a guess. Otherwise, I guess I'm an old-timer, because I always use GRANT to create users, so I don't know a lot about any subtleties or problems there might be with CREATE USER :-) Have you checked the server's error log to see if there's more information there? Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slave replication fails, cannot create user
Baron Schwartz [EMAIL PROTECTED] wrote: What version of MySQL are you running on each machine? Sorry, I should've included this information. Both of them are running 5.0.24, installed from exactly the same .rpm file. I wanted to avoid any issues related to different MySQL versions during this transition. The statement might have failed because the user already existed, You can see that was my first guess too. That's why I tried removing the user from mysql.user and starting the slave threads again, as I described. Unless it was trying to create the same user twice, that should've fixed it (if this was the cause), but it didn't seem to. -- Cos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slave replication fails, cannot create user
Ofer Inbar wrote: Baron Schwartz [EMAIL PROTECTED] wrote: What version of MySQL are you running on each machine? Sorry, I should've included this information. Both of them are running 5.0.24, installed from exactly the same .rpm file. I wanted to avoid any issues related to different MySQL versions during this transition. The statement might have failed because the user already existed, You can see that was my first guess too. That's why I tried removing the user from mysql.user and starting the slave threads again, as I described. Unless it was trying to create the same user twice, that should've fixed it (if this was the cause), but it didn't seem to. Have you tried creating another user, which you know does not exist and never has? Removing the user from mysql.user table with DELETE is not a really clean way to do it, if that's what you did. You are better off using DROP USER. Even after you removed the user, unless you ran FLUSH PRIVILEGES, it was still cached in memory. The built-in commands like DROP USER, GRANT, REVOKE etc will flush the in-memory caches. The user and privilege data is cached in memory because it is accessed constantly and must be very fast. I don't know if this would have caused the problem, but I'm curious to see if you can create a different user. Regards Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
