Bug 124352

Created attachment 100581 [details]
Sql file

The SQL file that the bug is reproduced

Created attachment 101066 [details]
the sql file for which it also happens

It happens the same to me: I can run the create script, but it won't run twice
because the "DROP DATABASE IF EXISTS ortho" would fail.

I'm using InnoDB. I have added this line to the original my.cnf:

innodb_data_file_path = ibdata1:10M:autoextend

I can manually remove the database with: 

rm /var/lib/mysql/ortho/ -rf

But then, after some create/rm calls, it complains that it cannot create
cds.frm file.

Does this happen with both innodb and myisam, or just innodb?

*** Bug 125801 has been marked as a duplicate of this bug. ***

It happens to me with no innodb at all ( one system that I've
previously try innodb and one that I've not try it ).

And albert vilella seems to have it with innodb.

yes, I have it with InnoDB

Just try with 2 fresh install on different PC and the same result
happens : it doesn't drop the BD.

I install each PC with a new set of CD that was burn with different iso.

I too have seen this behavior in FC2 current.

[root@ZenIV mysql]# mysqlshow
+-----------+
| Databases |
+-----------+
| mysql     |
| test      |
| todo      |
+-----------+
[root@ZenIV mysql]# mysqladmin  drop todo
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'todo' database [y/N] y
Database "todo" dropped
[root@ZenIV mysql]# mysqlshow
+-----------+
| Databases |
+-----------+
| mysql     |
| test      |
| todo      |
+-----------+

From an strace....

write(1, "Do you really want to drop the \'"..., 53Do you really want
to drop the 'todo' database [y/N] ) = 53
fstat64(0, {st_mode=S_IFCHR|0620, st_rdev=makedev(136, 27), ...}) = 0
mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,
0) = 0xf7031000
read(0, "y\n", 1024)                    = 2
y
fcntl64(3, F_SETFL, O_RDWR|O_NONBLOCK)  = 0
read(3, 0x8d1cd70, 8192)                = -1 EAGAIN (Resource
temporarily unavailable)
fcntl64(3, F_SETFL, O_RDWR)             = 0
write(3, "\25\0\0\0\3drop database `todo`", 25) = 25
read(3, "\5\0\0\1", 4)                  = 4
read(3, "\0\0\0\2\0", 5)                = 5
write(1, "Database \"todo\" dropped\n", 24Database "todo" dropped


Phil
=--=

Digging around  there is one referance to this behaviour in the
mysql.com archives

http://lists.mysql.com/mysql/167633

So based on this...
created a new database (new)
Contents of /var/lib/mysql/new
[root@dhcppc1 mysql]# ls -la new
total 28
drwx------  2 mysql mysql 4096 Aug 19 20:44 .
drwxr-xr-x  6 mysql mysql 4096 Aug 19 20:44 ..
-rw-rw----  1 mysql mysql 8600 Aug 19 20:44 category.frm
-rw-rw----  1 mysql mysql  356 Aug 19 20:44 category.MYD
-rw-rw----  1 mysql mysql 3072 Aug 19 20:44 category.MYI

[root@dhcppc1 mysql]# mysqladmin  drop new
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'new' database [y/N] y
Database "new" dropped
[root@dhcppc1 mysql]# ls -la new
total 12
drwx------  2 mysql mysql 4096 Aug 19 20:45 .
drwxr-xr-x  6 mysql mysql 4096 Aug 19 20:44 ..
-rw-rw----  1 mysql mysql 3072 Aug 19 20:45 category.MYI
[root@dhcppc1 mysql]# 

for some reason the category.MYI file didn't get nuked, consequently
the final cleanup of the directory didn't occur.

Phil
=--=

Ok so after pratting around a bit....
It would seem you cannot just up and drop a database

For to get "mysqladmin drop database" to work correctly
you need to be in mysql and drop all the tables *first*
eg
 mysql> drop table category;
 Query OK, 0 rows affected (0.00 sec)

Then come out and issue the mysqladmin drop database

CLEARLY this is wrong. I've databases of over 300 tables! I ain't
gonna log in and delete them manually by hand first.

[root@dhcppc1 mysql]# rpm -q mysql
mysql-3.23.58-9

Phil
=--=

And after a little more poking I found this
http://bugs.mysql.com/bug.php?id=3594

Where in the last two updates to the bug, (the prologue is mostly
irrelevant), we find out that mysql folk admit 'ah...'
and then provice a bugfix which is available in mysql-4.0.19

Tom, either you're going to bit the bullet and release mysql-4
(upstream) or backport the fix which doesn't seem to be easy to find
from the way their repository works,... 

As for me,.. it's tar.gz/compile time

Phil
=--=

Oh I'm such a helpful person

http://lists.mysql.com/internals/13765

Theres a reasoning and a patch there BUT it was for version 4 so you
may need to hand patch.

Phil
=--=

FWIW, there does seem to be some progress happening on the license
front, so maybe we'll get to put MySQL 4 in Fedora sometime soon ...

The upstream patch Bryce found does apply to MySQL 3 and seems to
resolve the problem.  I've included it in mysql-3.23.58-11 and later,
also 3.23.58-2.2 for the RHEL3 release series.

An errata has been issued which should help the problem 
described in this bug report. This report is therefore being 
closed with a resolution of ERRATA. For more information
on the solution and/or where to find the updated files, 
please follow the link below. You may reopen this bug report 
if the solution does not work for you.

http://rhn.redhat.com/errata/RHSA-2004-597.html

An errata has been issued which should help the problem 
described in this bug report. This report is therefore being 
closed with a resolution of ERRATA. For more information
on the solution and/or where to find the updated files, 
please follow the link below. You may reopen this bug report 
if the solution does not work for you.

http://rhn.redhat.com/errata/RHSA-2004-569.html