alan Posted February 25, 2015 Share Posted February 25, 2015 Hello clever peeps, Anyone seen this/knows where to start trying to solve it? Page: http://mysite.com/?/ User: guest Error: Exception: SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join (in /home/mysite/public_html/wire/core/DatabaseQuery.php line 91) PW config.php has my email address so I am emailed errors and I just received this by email. the site appears fine that URL does not throw a 404 whereas something like http://mysite.com/testfora404etc will, as it should, throw a good 404 Any pointers most appreciated, cheers, -Alan ProcessWire v:2.5.3 + Form Builder v:0.2.2 Link to comment Share on other sites More sharing options...
Entil`zha Posted February 26, 2015 Share Posted February 26, 2015 Hi Alan, What does that URL do? MySQL has join limit set to 61 tables, at least from version 4, so sounds like you page is trying to do some greater join. Link to comment Share on other sites More sharing options...
alan Posted February 26, 2015 Author Share Posted February 26, 2015 Hi Entil`zha, I think that URL was visited by a bot or something to trigger the problem, but weird thing is if I visit myself I don't get the error. Thanks for the info on the limit, I have done no 'manual' db work and so it is either a 'bad' combination of modules that happens to be able to exceed that limit or something else. My guess is it's probably something else, but only a guess. And slightly influenced by that odd situation where the URL is reported but it's use does not recreate the error... Weird :/ Link to comment Share on other sites More sharing options...
Entil`zha Posted February 27, 2015 Share Posted February 27, 2015 Maybe you could check if there's something in MySQL logs. Also if you don't have slow query log enabled it could help you identify problem. I use Zend Server's Z-ray for debuging and development because there's a great feature that tracks every query made on page, in dev server ofc. So that might also help you to identify the problem. 1 Link to comment Share on other sites More sharing options...
alan Posted February 27, 2015 Author Share Posted February 27, 2015 Thanks for the suggestions. I will check with the hoster/site to see what logs are available. But I still assume (wrongly maybe...) that it's something to do with the combination of PW version and Modules (no dis to PW or the Modules I am using, but I just assume it's the first place to look as all other sites share the same VPS and no other errors (I am aware of (yet))). Link to comment Share on other sites More sharing options...
alan Posted March 2, 2015 Author Share Posted March 2, 2015 Yet to ask hoster if their MySQL logs show anything, but meantime I've checked and a couple of other sites, on the same host, also have reported infrequent SQL errors (via the email address in /site/config.php), both have exactly the same format this time (apart from the domain): Page: http://mydomain2and3.com/?/ User: ? Error: Exception: SQLSTATE[HY000] [2002] No such file or directory (in /home/mydomain2and3/public_html/wire/core/ProcessWire.php line 163) I think I am running MySQL 5.5.42 in case that rings any bells as to what might be the cause. As you can see, all 3 instances of errors seem to report that URL /?/ is visited and the error happens, don't know if that is a false clue or something useful. (Will note here if hoster has any clues from their MySQL logs). Any comments most appreciated. Cheers, -Alan Link to comment Share on other sites More sharing options...
BitPoet Posted March 2, 2015 Share Posted March 2, 2015 /?/ is not the url that was called, it's what the shutdown function outputs when $page isn't set. You'll have to dig into the webserver logs to see which url was called for real. 1 Link to comment Share on other sites More sharing options...
alan Posted March 3, 2015 Author Share Posted March 3, 2015 Thanks for that tip BitPoet! Link to comment Share on other sites More sharing options...
alan Posted March 4, 2015 Author Share Posted March 4, 2015 Hmm. What I have found... 1. A wrong setting? (may not address the problems reported but wanted to raise in case of help to me/others) My log file of 1,000 lines or so has about 1/3rd all reading approx (the IP varies): [Warning] IP address '210.212.98.235' could not be resolved: Name or service not known This warning appears to be a symptom of not having skip_name_resolve set in MySQLs my.cnf file according to serverfault.com/.... The manual for MySQL ([sarcasm]that's easy to search isn't it?[/sarcasm]) says: This variable is set from the value of the --skip-name-resolve option. If it is OFF, mysqld resolves host names when checking client connections. If it is ON, mysqld uses only IP numbers; in this case, all Host column values in the grant tables must be IP addresses or localhost. See Section 8.11.5.2, “DNS Lookup Optimization and the Host Cache”. This variable was added in MySQL 5.5.5. Anyone know if it is safe/unsafe to set this option? 2. As regards the errors I have had reported to me by PW config/admin email... One of my reported errors happened on 25 Feb, these are the lines from the MySQL error log around that date: 150224 8:10:28 [Warning] IP address '61.240.144.64' could not be resolved: Temporary failure in name resolution 150224 8:10:46 [Warning] IP address '61.240.144.64' could not be resolved: Temporary failure in name resolution 150224 9:37:24 [Warning] IP address '61.240.144.64' could not be resolved: Temporary failure in name resolution 150224 14:31:43 [Warning] IP address '149.11.34.162' could not be resolved: Name or service not known 150225 5:58:59 [Warning] IP address '112.98.125.133' could not be resolved: Name or service not known 150225 5:59:00 [Warning] IP address '112.98.125.133' could not be resolved: Name or service not known 150225 23:18:11 [Warning] IP address '60.169.73.171' could not be resolved: Name or service not known 150226 2:25:37 [Warning] IP address '222.186.59.197' could not be resolved: Name or service not known 150226 5:24:18 [Warning] IP address '61.240.144.67' could not be resolved: Temporary failure in name resolution 150226 6:03:00 [Warning] IP address '61.240.144.67' could not be resolved: Temporary failure in name resolution 150226 6:06:21 [Warning] IP address '61.240.144.67' could not be resolved: Temporary failure in name resolution 150226 6:06:57 [Warning] IP address '61.240.144.67' could not be resolved: Temporary failure in name resolution 150226 6:32:20 [Warning] IP address '198.154.63.150' could not be resolved: Name or service not known 150226 11:12:52 [Warning] IP address '221.224.34.162' could not be resolved: Name or service not known 150226 15:52:39 [Warning] IP address '125.97.246.6' could not be resolved: Temporary failure in name resolution It looks like nothing 'special' happened on the 25th and yet that was when this error was reported to me by PW. I am continuing to investigate but so far it seems like I am running out of ideas on the causes of these errors. Here's hoping this thread helps someone and there is an explanation. Tks for reading and for any comments, cheers, -Alan Link to comment Share on other sites More sharing options...
BitPoet Posted March 8, 2015 Share Posted March 8, 2015 Huh. This looks like your MySQL server is reachable from everywhere in the internet, which is rarely a good idea. I usually bind only to localhost (if its running on the webserver itself), or add an iptables rule that only allows access from the servers I explicitely grant access to port 3306. For maintenance access, there's always the possibility to use ssh (MySQL CC has that built-in) to forward the connection to *nix systems, or VPN when using Windows hosts. One important measure to be able to pin-point problems quickly is to eliminate as much of the background noise as possible beforehand which port scans and distributed dictionary attacks tend to produce in the logs. Not letting these get to your services in the first place makes your logs much more meaningful. I don't think the database log will tell you much though, and the above lines just say that there was a connection attempt from a host that didn't resolve to a name. The most likely source of information would be the webserver log for that time, where you can see the real URL that was requested. If you can get that, also look out for POST requests in the timeframe in question. Mysterious error like yours can (I don't want to stir up panic there, but it would feel wrong not to mention it) sometimes also point to issues that arise by passing on unsanitized form values. The answer is likely much more less dangerous, but taking a look at that never hurts. Keep in mind that MySQL sits on the end of the food chain. The webserver log is your most important means to deduce what happened, then the application logs may give you further insight about how it happened. 2 Link to comment Share on other sites More sharing options...
alan Posted March 9, 2015 Author Share Posted March 9, 2015 @BitPoet, thanks for that, it's confirmed something I found and have fixed. The MySQL was indeed accessible on the internet (I was surprised this was the default for the new hosting I had switched to). My bad, I should have done a vulnerability test of my sites at my new host :/ Since my last post MySQL is now no longer visible and I hope this is the end of these weird SQL errors, from what you say and from what I've seen my guess is it will be the end. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now