Jump to content

Weird/rare "SQLSTATE[HY000]: General error: 1116 Too many tables" seen


alan
 Share

Recommended Posts

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.

Any pointers most appreciated, cheers, -Alan

ProcessWire v:2.5.3 + Form Builder v:0.2.2

Link to comment
Share on other sites

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

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.

  • Like 1
Link to comment
Share on other sites

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

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

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

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.

  • Like 2
Link to comment
Share on other sites

@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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...