Bug 124352 - Cannot drop databases
Cannot drop databases
Status: CLOSED ERRATA
Product: Fedora
Classification: Fedora
Component: mysql (Show other bugs)
2
i686 Linux
medium Severity high
: ---
: ---
Assigned To: Tom Lane
:
: 125801 (view as bug list)
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2004-05-25 16:57 EDT by Jean-Francois Saucier
Modified: 2013-07-02 23:01 EDT (History)
4 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2004-10-20 15:12:45 EDT
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
Sql file (10.79 KB, text/plain)
2004-05-26 08:59 EDT, Jean-Francois Saucier
no flags Details
the sql file for which it also happens (10.61 KB, text/plain)
2004-06-11 12:31 EDT, albert vilella
no flags Details

  None (edit)
Description Jean-Francois Saucier 2004-05-25 16:57:32 EDT
From Bugzilla Helper:
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6)
Gecko/20040510 Epiphany/1.2.4

Description of problem:
Executes the following tricks

mysqladmin create tempo
mysql tempo < my_sql_file.sql
mysqladmin drop tempo

The mysqladmin report that the database is deleted but when you do
"show databases;", tempo is here...

You must delete /var/lib/mysql/tempo by hand to make it dissapear...

Version-Release number of selected component (if applicable):
mysql-server-3.23.58-9

How reproducible:
Always

Steps to Reproduce:
1.mysqladmin create tempo
2.mysql tempo < random_sql_file.sql
3.mysqladmin drop tempo
    

Actual Results:  Database is not completly deleted

Expected Results:  The database is deleted

Additional info:
Comment 1 Jean-Francois Saucier 2004-05-26 08:59:18 EDT
Created attachment 100581 [details]
Sql file

The SQL file that the bug is reproduced
Comment 2 albert vilella 2004-06-11 12:31:01 EDT
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.
Comment 3 Tom Lane 2004-06-11 16:11:10 EDT
Does this happen with both innodb and myisam, or just innodb?
Comment 4 Tom Lane 2004-06-11 16:12:02 EDT
*** Bug 125801 has been marked as a duplicate of this bug. ***
Comment 5 Jean-Francois Saucier 2004-06-12 17:08:01 EDT
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.
Comment 6 albert vilella 2004-06-14 06:05:50 EDT
yes, I have it with InnoDB
Comment 7 Jean-Francois Saucier 2004-06-14 12:52:28 EDT
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.
Comment 8 Bryce 2004-08-19 12:58:39 EDT
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
=--=
Comment 9 Bryce 2004-08-19 15:46:04 EDT
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
=--=
Comment 10 Bryce 2004-08-19 16:01:28 EDT
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
=--=
Comment 11 Bryce 2004-08-19 16:18:58 EDT
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
=--=
Comment 12 Bryce 2004-08-19 16:22:03 EDT
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
=--=
Comment 13 Tom Lane 2004-08-19 20:21:01 EDT
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 ...
Comment 14 Tom Lane 2004-10-06 18:27:09 EDT
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.
Comment 15 Josh Bressers 2004-10-20 15:12:45 EDT
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
Comment 16 Josh Bressers 2004-10-20 15:41:28 EDT
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

Note You need to log in before you can comment on or make changes to this bug.