MySQL Bugs: #52963: Omitting "Database=_____" in Connection String Causes Eventual MySQL Hang

Bug #52963	Omitting "Database=_____" in Connection String Causes Eventual MySQL Hang
Submitted:	20 Apr 2010 5:01	Modified:	12 May 2010 8:11
Reporter:	Lukas Tan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	5.1.45	OS:	Windows (Server 2003)
Assigned to:		CPU Architecture:	Any
Tags:	access denied, connection string, null

Description:
If a default database ("database=_____") is not specified in the connection string, the below error occurs in the general log:

87 Init DB Access denied for user 'someuser'@'%' to database 'null' 

The statements preceeeding the error can vary, and include:

Query select database()
Init DB

After many hundreds of these access denied messages, MySQL stops responding, and must be restarted to become responsive.

The scenario where this occurs involves a batch that runs many hundreds of queries. Each logical query involves about 5 actual queries, as prepared queries are being used, so there are PREPARE, EXECUTE, etc queries for each logical query. Each logical query that occurs is also logged, causing another INSERT query. Estimate that MySQL hanging due to access denied errors occurs after about 2,600 actual queries.

This applies for all of:
MySQL 5.0.51a
MySQL 5.1.45
MyODBC 3.51
MyODBC 5.1.6

Using classic ASP on IIS6 as the scripting language.

How to repeat:
Set up the above, i.e.:

1) Set up atest DB, doesn;t much matter what's in it

2) Run MySQL with general logging

3) Use a connection string without "Database=_____"

4) Run lots of queries against the DB

5) Watch for MySQL to eventually stop responding (ODBC calls will fail at scripting langauge level)

6) Inspect general log for "Access denied for user 'someuser'@'%' to database 'null'" errors

Suggested fix:
a) Throw an ODBC error if "Database=_____" is omited from the connection string, or don't allow that usage.

b) Don't let MySQL hang after lots of access denied errors, or if this is deliberate behaviour to somehow prevent DOS, document it and allow the setting of how many access denied errors can occur.

"After many hundreds of these access denied messages, MySQL stops responding, and must be restarted to become responsive."  

I wonder if this is because of 'max_connect_errors' been exceeded? Refer FLUSH HOSTS here:
http://dev.mysql.com/doc/refman/5.1/en/flush.html

Good point - I had not seen that parameter in the my.ini, so had assumed it was not configurable.

Note though that the batch script I was using when the problem occurred did successfully process queries, it just generated lots of access denied errors at the same time.

It is possible that the number of access denied errors that occurred in sequence (before a successful connect, which resets the error count to 0) exceeded the default of 10 at some point, but the script always stopped at exactly the same point, so I'm not so sure that this is the case.

Would it help if I sent someone a part of the general log?

Lukas, what would help is a script (including DDL/DML) that causes this. Can you attach it please.

MySQL backup of the sample database and data

Attachment: MySQL DB Backup.sql (text/plain), 1.75 KiB.

Classic ASP page used to access data in the sample DB

Attachment: No-Default-Database.asp (text/plain), 1.75 KiB.

Output of the ASP page - MySQL breaks at query 3953

Attachment: Page Output.txt (text/plain), 172.81 KiB.

Have just uploaded 3 files - the MySQL backup of the DB and data, a classic ASP page that runs lots of queries against the data, and the output of that page, which shows MySQL hanging at query 3953.

Note that the ASP page re-creates the DB connection for each query - this replicates our internal DB access module which does the same thing.

Note also that the ASP page's connection string does not specify Database=____ - if that is added, no error occurs and all 5000 queries run successfully. The page also executes significantly faster (empirically, about 4 times faster).

Lukas

possible that local ports ran out?
http://dev.mysql.com/doc/mysql-windows-excerpt/5.1/en/can-not-connect-to-server-on-windows...

Re local ports - that fixes it. After making the registry changes in the linked article, the test script completes successfully and repeatedly.

Lukas

Thanks Shane. Lukas, closing the report now.