MySQL Bugs: #24718: drop database really dropping database

Bug #24718	drop database really dropping database
Submitted:	30 Nov 2006 10:08	Modified:	18 Dec 2006 21:02
Reporter:	mark lindseth	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5-0-27	OS:	Windows (Windows XP)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Tags:	create database, drop database, key

Description:
First off! If this has already been reported I'm really sorry...

Scenario: I am using a single script to drop/recreate db. The script is approx 600 lines so far containing creation of the db, adding tables, adding primary keys (no other key types used so far), creating function and procs. The idea being that when I've corrupted my db enough I just go back to the create script and restart.

Problem I am having is that when I run the script I keep seeing Multiple primary key defined when I create my primary keys... Nothing special when I create them just ALTER TABLE tableName ADD PRIMARY KEY (column); My key creations are in one part of my script nowhere else so I know it's not duplicate key creation. How can I have multiple keys on the brand new db that has just been dropped/recreated?

Another symptom I am see is that my drop database does not seem to be working because I see Can't drop database 'dbName'; database doesn't exist on the drop then on the create I see 'Can't create database 'dbName'; database exists. There is no code between these two statements!

With this db I have been screwing around I could have dropped a table with a PK on it and it's causing problems now I just don't know. I am willing to give any additional information if people have questions. I originally created this db with 5-0-24 but have since updated to 27 since this problem started happening.

Any assistance would be greatly appreciated.

Regards.

Mark Lindseth

How to repeat:
no idea sorry but if people have questions I can answer them as best as possible

Suggested fix:
no idea

Mark,

do you get any errors from the DROP TABLE and DROP DATABASE commands? Do you use InnoDB tables with foreign keys?

Regards,

Heikki

Hi,

When I try and drop/create database from the command line I get:

mysql> drop database fasttakeuser;
Query OK, 31 rows affected (1.53 sec)

mysql> create database fasttakeuser;
Query OK, 1 row affected (0.00 sec)

When I try it from query browser I get:

DROP DATABASE fastTakeUser;
Can't drop database 'fasttakeuser'; database doesn't exist
CREATE DATABASE fastTakeUser;
Can't create database 'fasttakeuser'; database exists

My tables are regular CREATE TABLE tableName (col1 INT, col2 VARCHAR(x)....);

If I drop the database from the command line I still get the Multiple primary key defined

Tx Mark

sorry just to clarify I get the Multiple primary key defined error message on every ALTER TABLE tableName ADD PRIMARY KEY that's in my script.

Hmm... maybe you get the errors about multiple PRIMARY KEY definitions because the program did not actually succeed in dropping the tables and they already have the old primary key defined!

If DROP DATABASE works from the mysql interactive SQL client, but not from the query browser, then this might be a problem in the query browser.

Please post a full self-contained example input and the full output of the query browser from a failure.

I am leaving this bug report for a MySQL AB engineer to handle.

Could you please provide what Heikki asked in his last comment. Thanks
in advance.

First off thank you for the help you've given me in working out what is causing this problem.  I think I've narrowed it down to query browser.  I am running 1.1.20 of the browser (I haven't tried going up to 1.2 beta yet, how stable is it and is there a feature sheet/bug fix sheet available?).  I haven't updated the category in case you find a problem with my logic.

I'm going on the above idea because of the following:-

I have chopped my script down to a sample approx 20 lines (shown below, if you can't reproduce it e-mail my account liner80@hotmail.com and I will send you a copy of the text file).

If I run the source I get the following output from command line:

mysql> source c:\test.txt
Database changed
Query OK, 31 rows affected (1.89 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.41 sec)

Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

If I run from sql browser I get:
2   Can't drop database 'fasttakeuser'; database doesn't exist 1008
28  Multiple primary key defined 1068

Even though the primary key error appears by looking at the schemata window in query browser I do see the PK get created properly.

Source:

USE mysql;
DROP DATABASE fastTakeUser;
CREATE DATABASE fastTakeUser;
USE fastTakeUser;

-- **********************************************************************************************************************************
-- Create tables
-- **********************************************************************************************************************************

-- ****************************************
-- General site details
-- ****************************************

CREATE TABLE siteCurrency
(
  siteCurrencyId                     INT NOT NULL,
  siteCurrencyCode                   VARCHAR(3) NOT NULL,
  siteCurrencyName                   VARCHAR(20) NOT NULL,
  siteCurrencySymbol                 VARCHAR(3) NOT NULL
);

-- **********************************************************************************************************************************
--  PRIMARY KEYS
-- **********************************************************************************************************************************

ALTER TABLE siteCurrency ADD PRIMARY KEY (siteCurrencyId);

Thank you for the feedback. I tried with mysql.exe client and MySQL Query
Browser and I wasn't able to repeat, however notice with Query Browser I
used a script tab because the SQL query area creates a new connection for
every query.

correct I used script tab.  I know in my research I did come across one other person who had the same problem.  

Now whenever I want to rebuild my db I just run it from command line rather than browser to avoid this issue or just comment out the primary keys.  I don't know what else I can give you to help track this issue down but if you have any ideas please do not hesitate contacting me.

Regards.

Mark

Thank you for the feedback. Still I am unable to repeat:

C:\mydb\bin>mysql -uroot -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.0.27-community-nt-log MySQL Community Edition (GPL)

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

mysql> source c:/test.txt
Database changed
Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.31 sec)

Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show tables;
+------------------------+
| Tables_in_fasttakeuser |
+------------------------+
| sitecurrency           |
+------------------------+
1 row in set (0.00 sec)

mysql> show create table sitecurrency\G
*************************** 1. row ***************************
       Table: sitecurrency
Create Table: CREATE TABLE `sitecurrency` (
  `siteCurrencyId` int(11) NOT NULL,
  `siteCurrencyCode` varchar(3) NOT NULL,
  `siteCurrencyName` varchar(20) NOT NULL,
  `siteCurrencySymbol` varchar(3) NOT NULL,
  PRIMARY KEY  (`siteCurrencyId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql>

and also with Query Browser. The script I used is:

USE mysql;
DROP DATABASE fastTakeUser;
CREATE DATABASE fastTakeUser;
USE fastTakeUser;

CREATE TABLE siteCurrency
(
  siteCurrencyId                     INT NOT NULL,
  siteCurrencyCode                   VARCHAR(3) NOT NULL,
  siteCurrencyName                   VARCHAR(20) NOT NULL,
  siteCurrencySymbol                 VARCHAR(3) NOT NULL
);

ALTER TABLE siteCurrency ADD PRIMARY KEY (siteCurrencyId);