MySQL Bugs: #14371: use <db>; problem.

Bug #14371	use <db>; problem.
Submitted:	27 Oct 2005 5:19	Modified:	21 Jul 2006 17:06
Reporter:	Jeff C	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.016-BK, 5.0.15	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Creating a database and then "use database;" fails occassionally.

(root@localhost) [test]> create database db8;
Query OK, 1 row affected (0.03 sec)

(root@localhost) [test]> use db8;
ERROR 1049 (42000): Unknown database 'db8;'
(root@localhost) [test]> use db8 ;
Database changed
(root@localhost) [db8]> create database db9; use db9;
Query OK, 1 row affected (0.03 sec)

Database changed

(root@localhost) [db5]> create database db10;
Query OK, 1 row affected (0.03 sec)

(root@localhost) [db5]> use db10;
Database changed

(root@localhost) [db10]> create database db2;
Query OK, 1 row affected (0.03 sec)

(root@localhost) [db10]> use db2;
ERROR 1049 (42000): Unknown database 'db2;'

####  Weird

What's weird is if you use the statement on one line it works, if you do it on multiple lines it doesn't.

(root@localhost) [db10]> drop database db5;
Query OK, 0 rows affected (0.01 sec)

(root@localhost) [db10]> create database db5; use db5;
Query OK, 1 row affected (0.00 sec)

Database changed
(root@localhost) [db5]> drop database db5;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> create database db5;
Query OK, 1 row affected (0.00 sec)

(root@localhost) [(none)]> use db5;
ERROR 1049 (42000): Unknown database 'db5;'

Thanks,
Jeff C.

How to repeat:
See above.

Suggested fix:
Not sure.

It will work with 4 or more characters:

(root@localhost) [(none)]> create database db55;
Query OK, 1 row affected (0.00 sec)

(root@localhost) [(none)]> use db55;
Database changed

NOT with 3.

<lokus@freenode> use `abc;`  will wait for the semicolon to end the statement

Thank you for a bug report. Weird, but repeatable with 5.0.16-BK (ChangeSet@1.1932.338.1, 2005-10-25 19:04:31+02:00, pgalbraith@mysql.com) too:

mysql> create database db5;
Query OK, 1 row affected (0,00 sec)

mysql> use db5;
ERROR 1049 (42000): Unknown database 'db5;'
mysql> create database db6; use db6;
Query OK, 1 row affected (0,00 sec)

Database changed
mysql> create database db55;
Query OK, 1 row affected (0,00 sec)

mysql> use db55;
Database changed
mysql> create database d5;
Query OK, 1 row affected (0,00 sec)

mysql> use d5;
ERROR 1049 (42000): Unknown database 'd5;'

Similar directories are created for the databases in both cases:

[openxs@Fedora 5.0]$ ls -l var/d5
total 4
-rw-rw----    1 openxs   openxs         65 Oct 27 12:58 db.opt
[openxs@Fedora 5.0]$ ls -l var/db6
total 4
-rw-rw----    1 openxs   openxs         65 Oct 27 12:58 db.opt

As for your last comment, this waiting it is not a bug, but expected behaviour: you can put any chracters into quoted identifier (`db5;`).

Bug report http://bugs.mysql.com/bug.php?id=14504 may be a duplicate of this one or closely related.

I'd like to confirm that it works on WinXP 5.0.15-nt [provided binary], and I'd also like to add that running an sql script file using the \. 'filename' using the mysql client [connecting locally] also causes nearly the same problem.

Here is the script I used:
<start>
CREATE DATABASE a;

USE a;

USE a

CREATE DATABASE aa;

USE aa;

USE aa

CREATE DATABASE aaa;

USE aaa;

USE aaa

CREATE DATABASE aaaa;

USE aaaa;

USE aaaa

CREATE DATABASE aaaaa;

USE aaaaa;

USE aaaaa
<end>

and here are the results: [logging it with the \T command]

<start>
mysql> \. c:/Documents and Settings/Jonathan/Desktop/test.txt
Query OK, 1 row affected (0.02 sec)

ERROR 1102 (42000): Incorrect database name 'a;'
ERROR 1102 (42000): Incorrect database name 'a'
Query OK, 1 row affected (0.00 sec)

ERROR 1102 (42000): Incorrect database name 'aa;'
ERROR 1102 (42000): Incorrect database name 'aa'
Query OK, 1 row affected (0.00 sec)

Database changed
ERROR 1102 (42000): Incorrect database name 'aaa'
Query OK, 1 row affected (0.00 sec)

Database changed
ERROR 1102 (42000): Incorrect database name 'aaaa'
Query OK, 1 row affected (0.02 sec)

Database changed
Database changed
<end>

As you can see, running it in a script results in 
use <db>; [w/ semi-colon]
failing for database names of 1 or 2 charecters in length
and the command
use <db>  [w/o semi-colon] 
fails for database names of 1 to 4 charecters in length

This was fixed in 5.0.16, it was a duplicate of Bug #14358.