Community
Participate
Working Groups
The DB API does a great way of sending SELECT queries to the slave db, but there are some cases where we need to force a SELECT to go to the master. For instance, when checking for the presence of a key before adding it. The current code has this: if(strtoupper(substr(trim($query), 0, 6)) == 'SELECT') { // Try to use read-only when possible $classfile = $this->get($key . '_db_classfile_ro'); $class = $this->get($key . '_db_class_ro'); } I see two possible solutions: 1. We can either use a SQL comment to tell the API to use the master. SELECT /*HIGH PRIORITY*/ field from table.... 2. We simply use SELECT HIGH_PRIORITY From the MySQL docs: HIGH_PRIORITY gives the SELECT higher priority than a statement that updates a table. You should use this only for queries that are very fast and must be done at once. I vote for #2, as the statement will fail if there's a typo in HIGH_PRIORITY (which is good), whereas #1 is error prone and will not fail if there is a typo (the query will work, but go to the slave).
Created attachment 121080 [details] Patch Here's a patch. Karl, Nate, does this make sense?
Looks good +1
The code looks good to me and I like the idea of being able to force it to go to the master. The only thing is that the MySQL docs on HIGH_PRIORITY make me wonder if it's the best way to do it. The reason I say that is that it says: "HIGH_PRIORITY gives the SELECT higher priority than a statement that updates a table. You should use this only for queries that are very fast and must be done at once. A SELECT HIGH_PRIORITY query that is issued while the table is locked for reading runs even if there is an update statement waiting for the table to be free. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE). HIGH_PRIORITY cannot be used with SELECT statements that are part of a UNION." The last statement means that using this method, a certain number of queries (those using a UNION) can't be done against the master and if we're enabling a method for doing that then it should probably support any query. Also the earlier statement that it will take priority over even an updating query is a little concerning if you're just using HIGH_PRIORITY to force it to the master. What if you have a fairly lengthy query that needs to go to the master? My preference would be to make this as general as possible by either: 1. Having a switch like $App->query_master that needs to be flipped one way or ther other. 2. Better yet, having an option to the *_sql() functions that forces the individual query to go to the master. 3. Or maybe better yet, using Denis' other idea about an SQL comment to trigger it What do you guys think?
Created attachment 121083 [details] Patch v2 My thoughts were that only quick key lookups should be forced to the master. If the query is lengthy, or complex enough to use UNIONS, then you shouldn't be forcing it the master anyway. But I'll concede that all queries should work regardless of the server. In trying to not hack the API, let's go with a comment, but let's make the comment explicitly describe what we want to achieve (ie, not HIGH PRIORITY, but use master): SELECT /* USE_MASTER */ field from table Here's another patch.
(In reply to comment #4) > Created an attachment (id=121083) [details] > Patch v2 > > My thoughts were that only quick key lookups should be forced to the master. > If the query is lengthy, or complex enough to use UNIONS, then you shouldn't be > forcing it the master anyway. But I'll concede that all queries should work > regardless of the server. > > In trying to not hack the API, let's go with a comment, but let's make the > comment explicitly describe what we want to achieve (ie, not HIGH PRIORITY, but > use master): > > SELECT /* USE_MASTER */ field from table > > Here's another patch. > +1!
+1 for the new patch.
This is committed on Phoenix and will be live shortly. I have updated the Phoenix docs, and I'll send an email to phoenix-dev http://wiki.eclipse.org/Using_Phoenix#Querying_the_master_database
I added /* USE MASTER */ to my session code, and it works swimmingly if the slave database is stopped.
Cool!
