Replication problem

[jacksoct]

I'm having an odd problem while setting up one-way replication between 2 MySQL servers. The setup process seemed to go well, and when I examine everything, like processlists on both servers and the master and slave statuses, it all seems to be working fine. Whenever I make
an update to the master database, the binlog gets updated on both the master and the slave. The relay log on the slave also seems to record the change. The only problem is that there's no actual change in the slave database!

The way it's acting almost makes me think that the I/O thread is working ok, but the SQL thread isn't executing anything it receives. As far as I can tell, though, the SQL thread is working ok. I know it's running, and when I run "show processlist" it's status is listed as "Slave: waiting for binlog update".

Also (and this may be the cause of the problem) when I run "show slave hosts" on the master server it comes back with:
+-----------+---------------------------+------+-------------------+-----------+
| Server_id | Host | Port | Rpl_recovery_rank |
Master_id |
+-----------+---------------------------+------+-------------------+-----------+
| 1 | vmmysql02 | 3306 | 0 | 1
|
+-----------+---------------------------+------+-------------------+-----------+

I noticed that the Server_id and the Master_id are the same, so I tried to change that in the my.cnf files for both servers. Afterwards, I restarted, but it doesn't seem to have changed the server-ids.

Would conflicting server-ids cause the problem I'm having? If so, why can't I change the ids?

 

[PCSAARON]

I am not sure about your ID change problem. It has been a while since I setup replication, however, I DID have problems with replicating data and it was almost the same issue with the software we wrote. It would update the master but not the slave... It would seem like it was working perfect and then the data was just not there in the slave...

I had to re-write the coding that was adding/deleting/modifying the data. We used Visual Basic as a front end and ended up using a different way to run the queries and it worked.

The old style coding used rs.update or rs.new commands,,, they did not replicate for some reason... We changed to using the EXECUTE command, like:
Dim myConn As New ADODB.Connection
myConn.Open DBConnection
myConn.Execute SQLQueryHere
myConn.Close

...and BLING! It started replicating! Don't ask me why or how, that is the story of why our replication wasn't working... Your ID's may be the issue however.. Keep us posted...

Aaron

 

[jacksoct]

Geez, I hope it's not the update code. I don't think it is, though, or at least I know that it's not using ADO. I believe it's using straight SQL statements, so hopefully I'm ok there.

I think the current theory is that it is the server-id conflict that's the cause of my problems. Has anyone ever had this happen? Does MySQL cache that value somewhere?

Thanks.

 

[jacksoct]

Ok, everything's fixed now. It was the server-id conflict, and I figured out why it didn't change when I changed the configuration file. Apparently, mysql was looking at a couple of different 'my.cnf' files and was apparently picking up the server-id specified in the second one it read. All I had to do was change that config file too. Thanks for the help!