MySQL Bugs: #33346: Drop Database Bug

Bug #33346	Drop Database Bug
Submitted:	18 Dec 2007 20:40	Modified:	18 Dec 2007 22:20
Reporter:	MuraliDharan V	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.20-beta-max-debug-log	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	Drop Database Bug

Description:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| categories]        |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

mysql> use categories];
Database changed
mysql> drop database categories];
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ']' at line 1
mysql> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 5.1.20-beta-max-debug-log |
+---------------------------+
1 row in set (0.00 sec)

How to repeat:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| categories]        |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

mysql> use categories];
Database changed
mysql> drop database categories];
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ']' at line 1
mysql> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 5.1.20-beta-max-debug-log |
+---------------------------+
1 row in set (0.00 sec)

what is this closing bracket for?

Try

drop database categories

not 

drop database categories]

btw: maybe you are used to MS Access or SQLite and are used to using [brackets] for enclosing identifiers?  In MySQL you use `backticks`.  So also 

drop database `categories`

should work!

(written by a non-mysql person)

Hello,

Iam sure my Database name is Categories], this is the name i have created.

Thanks,
Murali.

I am sorry!  I did not notice that it was a 5.1.x server and not a 5.0.x server!

5.1 supports the "]" character in identifiers.

However you will still need to enclose in `backticks`! (or "doublequotes" in ANSI sql_mode)

This works on 5.1.22:

create database `categories]`;
drop database `categories]`;

Thanks for your quick reply.But it still not works for me.

Here it is,

mysql> drop database 'categories]';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''categories]'' at line 1
mysql> drop database "categories]";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"categories]"' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| categories]        |
| mysql              |
| pso                |
| rep                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 5.1.20-beta-max-debug-log |
+---------------------------+
1 row in set (0.00 sec)

Hey Peter Laursen,

Sorry ...Yes it works.

mysql> drop database `categories]`;
Query OK, 0 rows affected (0.00 sec)

Thanks,
Murali.

please note the difference between single quotes " ' " and backticks/backquotes " ` ".  In MySQL you need to enclose identifiers with `backticks` if the name contains special characters (SPACE, (BACK)SLASH, QUOTES, BRACKETS etc.)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

c:\dbs>5.1\bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.23-rc-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| categories]        |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> drop database `categories]`;
Query OK, 0 rows affected (0.09 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql>

Thanks once again to all and spending your worthfull time to Identify my mistake.

I will not repeat this again.

Thanks,
Murali