This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes How to prevent a MySQL slave accepting queries? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to prevent a MySQL slave accepting queries?" Watch "How to prevent a MySQL slave accepting queries?" New topic
Author

How to prevent a MySQL slave accepting queries?

Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
Hi all,
If I have a setup with one master and one slave MySQL instance. When the master goes down, the application moves to using the slave instance for its JDBC connections. Now, the problem is that the JDBC connection is quicker than the "stop slave" operation -- that is, after a crash, the application starts pounding the slave server with SQL UPDATEs before the slave has synched itself from the master's transaction log.
This is rather unpleasant because the application might update a record, be told it's fine, and then the change would be overridden by an update coming from the synchronization process.
This begs the question, is there any way to temporarily prevent the application from executing SQL statements against the slave until it has finished the replication (anything between 1 second and 5 minutes)?


Author of Test Driven (2007) and Effective Unit Testing (2013) [Blog] [HowToAskQuestionsOnJavaRanch]
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
I'll just repeat here a suggestion I got from the official mailing list:
only grant the user privileges after STOP SLAVE has been executed and the replica has synched itself from the master's transaction log
So obvious... Sure it's a bit ugly, but I guess it's better than nothing.
Further suggestions still welcome
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to prevent a MySQL slave accepting queries?
 
Similar Threads
OneToOne shared PK isn't setting key in related table during insertion
Failover with MySQL
remote display of file select dialog
Check status of MySQL replication with JDBC
How to connect second hard disk?