I've inheritied maintenance duties on a Fuel app that suffers from intermittent "SQLSTATE[HY000] [2002] Connection timed out" and also "SQLSTATE[42S02]: Base table or view not found:..."
It's worth noting that the site owners on their internal, local machine have a cron job running every 5 minutes that uploads a dump file to this website and invokes a shell script that feeds the dumpfile into our database. My first hypothesis was that these recurring events are causing the errors, but after adding more verbose logging to the shell script and examining and comparing its log to my Fuel error logs, I find that the database errors often occur outside the window when the dump process is running. I've also timed the dumps and they usually take well under 10 seconds -- except sometimes they don't, and take 127 seconds instead! (When this happens I do not think it's because it's processing all that much more data, but I have begun logging the dump file byte size as well just to be sure.)
Now I suspect that this cron job and the Fuel both suffer from the same issue. The question is -- what? I just turned on profiling on my development machine, and I see pages that run 60 or 70 SELECT queries. Most of those look very much like the ORM being used inefficiently. However, I am not an expert in MySQL performance tuning, having only had to dive into optimizing queries on rare occasions when I've hit an exceptionally slow one. So, before I attack the database overuse issue, I want to ask if that's a likely source of this pain. I doubt it, because although this app is a bit resource-wasteful, MySQL is a workhorse and handles a lot of queries, and this is not a high-traffic site. I might add that the page feels perfectly responsive.
Yes, I know MySQL has logging capabilities that might be useful, but this is on a virtual private server provided by Dreamhost I think I have to ask them to turn on logging for us. So I thought I would first check here for general ideas.
And yes, I should consider database result caching and I assume Fuel provides that, but I haven't gone there yet.
Is there a way to get Fuel to log all its SQL queries to a file, along with profiling information?
Connection time out is controlled by a server setting. In a datacenter setup, it could also be a firewall or a loadbalancer that closes the connection prematurely.
If you know the rough time at which the error is occuring (you should be able to get the start of the request timestamp and the error timestamp from the logs), you can check configs. 127sec is very close to 120 seconds, which is a common configuration value. This is a good overview: https://www.pythian.com/blog/connection-timeout-parameters-mysql/
"Base table or view not found" I haven't seen in my life. Is the process creating new or temporary tables perhaps? If this happens in a transaction, and something fails, then the transaction might roll back, delete the table, so the next SQL statement fails on a missing table?
I don't know if you can upgrade (if only for test purposes) to 1.9/dev? It has the option to write profiler data to a logfile you asked for. If not, you might have to litter the code with logger() calls to write app log entries at strategic points, in order to figure out what goes wrong.
The ORM does result caching by default, only in 1.8+ you have the option to switch it off, and only in 1.9/dev you can disable it completely. Because of this, the ORM isn't very well suited for batch processing that involves lots of records.
The timestamps on the error logs do tell me when this is happening, and I realize I can cross-check them against the Apache server logs to see the URLs where this is happening. I'm not sure I know what you mean by "check configs." Thanks for the reference to the blog post. I read it, and may read it again to really get it fully. My immediate reaction is that this is something that should be addressed by some means other than tweaking timeout settings.
As for "Base table or view not found," I do know that the tables in question are going in and out of existence because many times per day, at 5-minute intervals, there is as I mentioned a cron job that loads into the db a file generated by mysqldump, and as a typical mysqldump file, it says "DROP TABLE IF EXISTS,... CREATE TABLE..." but this is followed by "LOCK TABLES ...[insert data] UNLOCK TABLE ..." so, if LOCK TABLES means what I think it means it per the documentation, this error not happen just because this other process is going on. And in fact I've found cases where the error happens during the execution of this other dump process, and cases where it happens when the other process is not executing.
Sorry for getting OT, since this isn't specific to FuelPHP. I am thinking I would like to handle these errors in a more robust manner, irrespective of the resolution of the underlying issue, e.g., by catching the db exception in the controller, logging it verbosely, and if possible serving a cached copy of the requested page -- the cache item having been saved on a previous successful request -- otherwise display a page with a thingy that says "will try again automatically in 60 seconds," then starting counting down using Javascript. Sound like a plan?
And yes, I intend to recommend upgrading to 1.8 if not 1.9dev -- but my instinct tells me I should settle this first.
I'm a little confused by your comment about result caching. If caching is on by default, and cannot be disabled, I wonder why a controller action that uses the ORM and executes 77 SELECT queries does it again when you reload it, even without any data having been changed. Maybe I should be hunting down in the application the place where it unconditionally, explicity purges the cache on every request -- which seems silly. Or I'm missing something.
"check configs" was a typo, I meant "check logs". ;)
I can imagine with a process design like that, it can't cope very well with multiple processes running simultaneously. Perhaps you need to create a semaphore mechanism, to make sure that if a second process starts, it waits until the first one finishes, or it terminates if the next one can pick up the work.
And it gets worse if your frontend application expects the table to be there, and it often isn't because of the cron job. I don't envie your job atm. ;-)
The result cache doesn't prevent queries being executed, as you can't write code to "predict" what records will come back from a query. What it does it prevents the hydration of the record, instead it will do a lookup in the cache with the primary key for every record, and return the cached object instead of creating a new one. Hydration of results takes a lot more time than running a SELECT statement.
Donate
