Re: dump, drop database then merge/aggregate

[Steven Siebert]

Ah, ok, if I understand correctly within this context every record in the
one table _should_ have a unique identifier.  Please verify this is the
case, though, if for example the primary key is an auto increment what I'm
going to suggest is not good and Really Bad Things will, not may, happen.

If you want to do this all in MySQL, and IFF the records are ensured to be
*globally unique*, then what I suggested previously would work but isn't
necessary (and is actually dangerous if global record uniqueness is not
definite).  Uou _could_ do a standard mysqldump (use flags to do data only,
no schema) and on the importing server it will insert the records and if
there are duplicates records they will fail. If there is a chance the
records aren't unique, or if you want to be extra super safe (good idea
anyway), you can add triggers on the ingest server to ensure
uniqueness/capture failures and record them in another table for analysis
or perhaps even to immediate data remediation (update key) and do insert.

Now, for me, using triggers or other business-logic-in-database features is
a code smell.  I loath putting business logic in databases as they tend to
be non-portable and are hard to troubleshoot for people behind me that is
expecting to have logic in code.  Since you're having to script this
behavior out anyway, if it were me I would dump the data in the table to
CSV or similar using INSERT INTO OUTFILE rather than mysqldump, ship the
file, and have a small php script on cron or whatever ingest it, allowing
for your business logic for data validate/etc to be done in code (IMO where
it belongs).

S



On Mon, Feb 29, 2016 at 12:12 PM, lejeczek  wrote:

> On 29/02/16 16:32, Steven Siebert wrote:
>
>> What level of control do you have on the remote end that is
>> collecting/dumping the data?  Can you specify the command/arguments on how
>> to dump?  Is it possible to turn on binary logging and manually ship the
>> logs rather than shipping the dump, effectively manually doing
>> replication?
>>
> in an overview it's a simple php app, a form of a questionnaire that
> collects user manual input, db backend is similarly simple, just one table.
> Yes I can operate mysqldump command but nothing else, I do not have
> control over mysql config nor processes.
>
> It's one of those cases when for now it's too late and you are only
> thinking - ough... that remote box, if compromised would be good to have
> only a minimal set of data on it.
>
> So I can mysqldump any way it'd be best and I'd have to insert ideally not
> replacing anything, instead aggregating, adding data.
> I think developers took care of uniqueness of the rows, and constructed it
> in conformity with good design practices.
>
> What I'm only guessing is when I lock, dump and remove then insert,
> aggregate could there be problems with keys? And no data loss during
> dump+removal?
>
> thanks for sharing your thoughts.
>
>
>> I agree with others, in general this approach smells like a bad idea.
>> However, updating data from a remote system in batch is quite common,
>> except often it's done at the application level polling things like web
>> services and perhaps some business logic to ensure integrity is
>> maintained.  Attempting to do it within the constructs of the database
>> itself is understandable, but there are risks when not adding that "layer"
>> of logic to ensure state is exactly as you expect it during a merge.
>>
>> At risk of giving you too much rope to hang yourself: if you use mysqldump
>> to dump the database, if you use the --replace flag you'll convert all
>> INSERT statements to REPLACE, which when you merge will update or insert
>> the record, effectively "merging" the data.  This may be one approach you
>> want to look at, but may not be appropriate depending on your specific
>> situation.
>>
>> S
>>
>>
>>
>> On Mon, Feb 29, 2016 at 11:12 AM, lejeczek  wrote:
>>
>> On 29/02/16 15:42, Gary Smith wrote:
>>>
>>> On 29/02/2016 15:30, lejeczek wrote:

 On 28/02/16 20:50, lejeczek wrote:
>
> fellow users, hopefully you experts too, could help...
>>
>> ...me to understand how, and what should be the best practice to dump
>> database, then drop it and merge the dumps..
>> What I'd like to do is something probably many have done and I wonder
>> how it's done best.
>> A box will be dumping a database (maybe? tables if it's better) then
>> dropping (purging the data) it and on a different system that dump
>> swill be
>> inserted/aggregated into the same database.
>> It reminds me a kind of incremental backup except for the fact that
>> source data will be dropped/purged on regular basis, but before a
>> drop, a
>> dump which later will be used to sort of reconstruct that same
>> database.
>>
>> How do you recommend to do it? I'm guessing trickiest bit might this
>> reconstruction part, how to merge dumps safely, 

Re: dump, drop database then merge/aggregate

[Steven Siebert]

Totally with you, I had to get up and wash my hands after writing such
filth =)

On Mon, Feb 29, 2016 at 12:14 PM, Gary Smith  wrote:

> On 29/02/2016 16:32, Steven Siebert wrote:
>
>>
>> At risk of giving you too much rope to hang yourself: if you use
>> mysqldump to dump the database, if you use the --replace flag you'll
>> convert all INSERT statements to REPLACE, which when you merge will update
>> or insert the record, effectively "merging" the data.  This may be one
>> approach you want to look at, but may not be appropriate depending on your
>> specific situation.
>>
>> I'd considered mentioning this myself, but this was the root of my
> comment about integrity - if the original database or tables are dropped,
> then the replace command will cause the data to poo all over the original
> dataset. As you mentioned in your (snipped) reply, this can go badly wrong
> in a short space of time without the correct controls in place. Even if
> they are in place, I'd have trouble sleeping at night if this were my
> circus.
>
> Gary
>

Re: dump, drop database then merge/aggregate

[Gary Smith]

On 29/02/2016 16:32, Steven Siebert wrote:


At risk of giving you too much rope to hang yourself: if you use 
mysqldump to dump the database, if you use the --replace flag you'll 
convert all INSERT statements to REPLACE, which when you merge will 
update or insert the record, effectively "merging" the data.  This may 
be one approach you want to look at, but may not be appropriate 
depending on your specific situation.


I'd considered mentioning this myself, but this was the root of my 
comment about integrity - if the original database or tables are 
dropped, then the replace command will cause the data to poo all over 
the original dataset. As you mentioned in your (snipped) reply, this can 
go badly wrong in a short space of time without the correct controls in 
place. Even if they are in place, I'd have trouble sleeping at night if 
this were my circus.


Gary

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: dump, drop database then merge/aggregate

[lejeczek]

On 29/02/16 16:32, Steven Siebert wrote:

What level of control do you have on the remote end that is
collecting/dumping the data?  Can you specify the command/arguments on how
to dump?  Is it possible to turn on binary logging and manually ship the
logs rather than shipping the dump, effectively manually doing replication?
in an overview it's a simple php app, a form of a 
questionnaire that collects user manual input, db backend is 
similarly simple, just one table.
Yes I can operate mysqldump command but nothing else, I do 
not have control over mysql config nor processes.


It's one of those cases when for now it's too late and you 
are only thinking - ough... that remote box, if compromised 
would be good to have only a minimal set of data on it.


So I can mysqldump any way it'd be best and I'd have to 
insert ideally not replacing anything, instead aggregating, 
adding data.
I think developers took care of uniqueness of the rows, and 
constructed it in conformity with good design practices.


What I'm only guessing is when I lock, dump and remove then 
insert, aggregate could there be problems with keys? And no 
data loss during dump+removal?


thanks for sharing your thoughts.


I agree with others, in general this approach smells like a bad idea.
However, updating data from a remote system in batch is quite common,
except often it's done at the application level polling things like web
services and perhaps some business logic to ensure integrity is
maintained.  Attempting to do it within the constructs of the database
itself is understandable, but there are risks when not adding that "layer"
of logic to ensure state is exactly as you expect it during a merge.

At risk of giving you too much rope to hang yourself: if you use mysqldump
to dump the database, if you use the --replace flag you'll convert all
INSERT statements to REPLACE, which when you merge will update or insert
the record, effectively "merging" the data.  This may be one approach you
want to look at, but may not be appropriate depending on your specific
situation.

S



On Mon, Feb 29, 2016 at 11:12 AM, lejeczek  wrote:


On 29/02/16 15:42, Gary Smith wrote:


On 29/02/2016 15:30, lejeczek wrote:


On 28/02/16 20:50, lejeczek wrote:


fellow users, hopefully you experts too, could help...

...me to understand how, and what should be the best practice to dump
database, then drop it and merge the dumps..
What I'd like to do is something probably many have done and I wonder
how it's done best.
A box will be dumping a database (maybe? tables if it's better) then
dropping (purging the data) it and on a different system that dump swill be
inserted/aggregated into the same database.
It reminds me a kind of incremental backup except for the fact that
source data will be dropped/purged on regular basis, but before a drop, a
dump which later will be used to sort of reconstruct that same database.

How do you recommend to do it? I'm guessing trickiest bit might this
reconstruction part, how to merge dumps safely, naturally while maintaining
consistency & integrity?
Actual syntax, as usually any code examples are, would be best.

many thanks.


I guess dropping a tables is not really what I should even consider -

should I just be deleting everything from tables in order to remove data?
And if I was to use dumps of such a database (where data was first
cleansed then some data was collected) to merge data again would it work
and merge that newly collected data with what's already in the database


This sounds like a remarkably reliable way to ensure no data integrity.
What exactly are you trying to achieve? Would replication be the magic word
you're after?

I realize this all might look rather like a bird fiddling with a worm

instead of lion going for quick kill. I replicate wherever I need and can,
here a have very little control over one end.
On that end with little control there is one simple database, which data
I'll need to be removed on regular basis, before removing I'll be dumping
and I need to use those dumps to add, merge, aggregate data to a database
on the other end, like:
today both databases are mirrored/identical
tonight awkward end will dump then remove all the data, then collect some
and again, dump then remove
and these dumps should reconstruct the database on the other box.

Pointers on what to pay the attention to, how to test for consistency &
integrity, would be of great help.


Gary



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: dump, drop database then merge/aggregate

[Steven Siebert]

What level of control do you have on the remote end that is
collecting/dumping the data?  Can you specify the command/arguments on how
to dump?  Is it possible to turn on binary logging and manually ship the
logs rather than shipping the dump, effectively manually doing replication?

I agree with others, in general this approach smells like a bad idea.
However, updating data from a remote system in batch is quite common,
except often it's done at the application level polling things like web
services and perhaps some business logic to ensure integrity is
maintained.  Attempting to do it within the constructs of the database
itself is understandable, but there are risks when not adding that "layer"
of logic to ensure state is exactly as you expect it during a merge.

At risk of giving you too much rope to hang yourself: if you use mysqldump
to dump the database, if you use the --replace flag you'll convert all
INSERT statements to REPLACE, which when you merge will update or insert
the record, effectively "merging" the data.  This may be one approach you
want to look at, but may not be appropriate depending on your specific
situation.

S



On Mon, Feb 29, 2016 at 11:12 AM, lejeczek  wrote:

> On 29/02/16 15:42, Gary Smith wrote:
>
>> On 29/02/2016 15:30, lejeczek wrote:
>>
>>> On 28/02/16 20:50, lejeczek wrote:
>>>
 fellow users, hopefully you experts too, could help...

 ...me to understand how, and what should be the best practice to dump
 database, then drop it and merge the dumps..
 What I'd like to do is something probably many have done and I wonder
 how it's done best.
 A box will be dumping a database (maybe? tables if it's better) then
 dropping (purging the data) it and on a different system that dump swill be
 inserted/aggregated into the same database.
 It reminds me a kind of incremental backup except for the fact that
 source data will be dropped/purged on regular basis, but before a drop, a
 dump which later will be used to sort of reconstruct that same database.

 How do you recommend to do it? I'm guessing trickiest bit might this
 reconstruction part, how to merge dumps safely, naturally while maintaining
 consistency & integrity?
 Actual syntax, as usually any code examples are, would be best.

 many thanks.


 I guess dropping a tables is not really what I should even consider -
>>> should I just be deleting everything from tables in order to remove data?
>>> And if I was to use dumps of such a database (where data was first
>>> cleansed then some data was collected) to merge data again would it work
>>> and merge that newly collected data with what's already in the database
>>>
>> This sounds like a remarkably reliable way to ensure no data integrity.
>> What exactly are you trying to achieve? Would replication be the magic word
>> you're after?
>>
>> I realize this all might look rather like a bird fiddling with a worm
> instead of lion going for quick kill. I replicate wherever I need and can,
> here a have very little control over one end.
> On that end with little control there is one simple database, which data
> I'll need to be removed on regular basis, before removing I'll be dumping
> and I need to use those dumps to add, merge, aggregate data to a database
> on the other end, like:
> today both databases are mirrored/identical
> tonight awkward end will dump then remove all the data, then collect some
> and again, dump then remove
> and these dumps should reconstruct the database on the other box.
>
> Pointers on what to pay the attention to, how to test for consistency &
> integrity, would be of great help.
>
>
> Gary
>>
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: dump, drop database then merge/aggregate

[Johan De Meersman]

- Original Message -
> From: "lejeczek" <pelj...@yahoo.co.uk>
> Subject: Re: dump, drop database then merge/aggregate
> 
> today both databases are mirrored/identical
> tonight awkward end will dump then remove all the data, then
> collect some and again, dump then remove
> and these dumps should reconstruct the database on the other
> box.

It sounds like a horrible mess, to be honest. It's also pretty hard to 
recommend possible paths without knowing what's inside. Is it an option for you 
to simply import the distinct dumps into different schemas? That way there 
would be no need for merging the data, you just query the particular dataset 
you're interested in.


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: dump, drop database then merge/aggregate

[lejeczek]

On 29/02/16 15:42, Gary Smith wrote:

On 29/02/2016 15:30, lejeczek wrote:

On 28/02/16 20:50, lejeczek wrote:

fellow users, hopefully you experts too, could help...

...me to understand how, and what should be the best 
practice to dump database, then drop it and merge the 
dumps..
What I'd like to do is something probably many have done 
and I wonder how it's done best.
A box will be dumping a database (maybe? tables if it's 
better) then dropping (purging the data) it and on a 
different system that dump swill be inserted/aggregated 
into the same database.
It reminds me a kind of incremental backup except for 
the fact that source data will be dropped/purged on 
regular basis, but before a drop, a dump which later 
will be used to sort of reconstruct that same database.


How do you recommend to do it? I'm guessing trickiest 
bit might this reconstruction part, how to merge dumps 
safely, naturally while maintaining consistency & 
integrity?
Actual syntax, as usually any code examples are, would 
be best.


many thanks.


I guess dropping a tables is not really what I should 
even consider - should I just be deleting everything from 
tables in order to remove data?
And if I was to use dumps of such a database (where data 
was first cleansed then some data was collected) to merge 
data again would it work and merge that newly collected 
data with what's already in the database
This sounds like a remarkably reliable way to ensure no 
data integrity. What exactly are you trying to achieve? 
Would replication be the magic word you're after?


I realize this all might look rather like a bird fiddling 
with a worm instead of lion going for quick kill. I 
replicate wherever I need and can, here a have very little 
control over one end.
On that end with little control there is one simple 
database, which data I'll need to be removed on regular 
basis, before removing I'll be dumping and I need to use 
those dumps to add, merge, aggregate data to a database on 
the other end, like:

today both databases are mirrored/identical
tonight awkward end will dump then remove all the data, then 
collect some and again, dump then remove
and these dumps should reconstruct the database on the other 
box.


Pointers on what to pay the attention to, how to test for 
consistency & integrity, would be of great help.



Gary




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: dump, drop database then merge/aggregate

[Gary Smith]

On 29/02/2016 15:30, lejeczek wrote:

On 28/02/16 20:50, lejeczek wrote:

fellow users, hopefully you experts too, could help...

...me to understand how, and what should be the best practice to dump 
database, then drop it and merge the dumps..
What I'd like to do is something probably many have done and I wonder 
how it's done best.
A box will be dumping a database (maybe? tables if it's better) then 
dropping (purging the data) it and on a different system that dump 
swill be inserted/aggregated into the same database.
It reminds me a kind of incremental backup except for the fact that 
source data will be dropped/purged on regular basis, but before a 
drop, a dump which later will be used to sort of reconstruct that 
same database.


How do you recommend to do it? I'm guessing trickiest bit might this 
reconstruction part, how to merge dumps safely, naturally while 
maintaining consistency & integrity?

Actual syntax, as usually any code examples are, would be best.

many thanks.


I guess dropping a tables is not really what I should even consider - 
should I just be deleting everything from tables in order to remove data?
And if I was to use dumps of such a database (where data was first 
cleansed then some data was collected) to merge data again would it 
work and merge that newly collected data with what's already in the 
database
This sounds like a remarkably reliable way to ensure no data integrity. 
What exactly are you trying to achieve? Would replication be the magic 
word you're after?


Gary

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: dump, drop database then merge/aggregate

[lejeczek]

On 28/02/16 20:50, lejeczek wrote:

fellow users, hopefully you experts too, could help...

...me to understand how, and what should be the best 
practice to dump database, then drop it and merge the dumps..
What I'd like to do is something probably many have done 
and I wonder how it's done best.
A box will be dumping a database (maybe? tables if it's 
better) then dropping (purging the data) it and on a 
different system that dump swill be inserted/aggregated 
into the same database.
It reminds me a kind of incremental backup except for the 
fact that source data will be dropped/purged on regular 
basis, but before a drop, a dump which later will be used 
to sort of reconstruct that same database.


How do you recommend to do it? I'm guessing trickiest bit 
might this reconstruction part, how to merge dumps safely, 
naturally while maintaining consistency & integrity?
Actual syntax, as usually any code examples are, would be 
best.


many thanks.


I guess dropping a tables is not really what I should even 
consider - should I just be deleting everything from tables 
in order to remove data?
And if I was to use dumps of such a database (where data was 
first cleansed then some data was collected) to merge data 
again would it work and merge that newly collected data with 
what's already in the database?



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

dump, drop database then merge/aggregate

[lejeczek]

fellow users, hopefully you experts too, could help...

...me to understand how, and what should be the best 
practice to dump database, then drop it and merge the dumps..
What I'd like to do is something probably many have done and 
I wonder how it's done best.
A box will be dumping a database (maybe? tables if it's 
better) then dropping (purging the data) it and on a 
different system that dump swill be inserted/aggregated into 
the same database.
It reminds me a kind of incremental backup except for the 
fact that source data will be dropped/purged on regular 
basis, but before a drop, a dump which later will be used to 
sort of reconstruct that same database.


How do you recommend to do it? I'm guessing trickiest bit 
might this reconstruction part, how to merge dumps safely, 
naturally while maintaining consistency & integrity?

Actual syntax, as usually any code examples are, would be best.

many thanks.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Can't drop database that shows up in show databases

[Waynn Lue]

SHOW DATABASES; shows that I have a database called test, but when I
call DROP DATABASE test, I get

ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist

When I run any queries on that database, I get errors like:

mysql select * from Users;
ERROR 1017 (HY000): Can't find file: './test/Users.frm' (errno: 13)

My previous solution of deleting the ./test folder worked only
temporarily, but restarting mysqld seemed to recreate it.  Anyone have
ideas on how to drop this database?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't drop database that shows up in show databases

[Martin Gainty]

Waynn-

does the OS user you use to execute mysql have create/write/read/execute
rights to the ./test folder?

Martin-
- Original Message -
From: Waynn Lue [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Saturday, March 08, 2008 6:58 AM
Subject: Can't drop database that shows up in show databases


 SHOW DATABASES; shows that I have a database called test, but when I
 call DROP DATABASE test, I get

 ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist

 When I run any queries on that database, I get errors like:

 mysql select * from Users;
 ERROR 1017 (HY000): Can't find file: './test/Users.frm' (errno: 13)

 My previous solution of deleting the ./test folder worked only
 temporarily, but restarting mysqld seemed to recreate it.  Anyone have
 ideas on how to drop this database?

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't drop database that shows up in show databases

[Craig Huffstetler]

Waynn:

What user are you using to execute mysql from the command line? Perhaps run
mysql (from command line) as root then execute the desired actions (such as
drop). This will make sure you can drop the table and avoid any further
problems.

Sincerely,

Craig Huffstetler

On Sat, Mar 8, 2008 at 6:58 AM, Waynn Lue [EMAIL PROTECTED] wrote:

 SHOW DATABASES; shows that I have a database called test, but when I
 call DROP DATABASE test, I get

 ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist

 When I run any queries on that database, I get errors like:

 mysql select * from Users;
 ERROR 1017 (HY000): Can't find file: './test/Users.frm' (errno: 13)

 My previous solution of deleting the ./test folder worked only
 temporarily, but restarting mysqld seemed to recreate it.  Anyone have
 ideas on how to drop this database?

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't drop database that shows up in show databases

[Baron Schwartz]

Hi,

On Sat, Mar 8, 2008 at 6:58 AM, Waynn Lue [EMAIL PROTECTED] wrote:
 SHOW DATABASES; shows that I have a database called test, but when I
  call DROP DATABASE test, I get

  ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist

  When I run any queries on that database, I get errors like:

  mysql select * from Users;
  ERROR 1017 (HY000): Can't find file: './test/Users.frm' (errno: 13)

Whenever you see errno: X, run perror and see what the error is:

[EMAIL PROTECTED]:~$ perror 13
OS error code  13:  Permission denied

That is why other posters are suggesting it might be a permissions
problem.  It sounds to me like there might be an InnoDB problem mixed
in with this: did you delete any .frm files (or databases) from the
filesystem without first dropping the InnoDB files that use them?
InnoDB will complain about this if you did.


  My previous solution of deleting the ./test folder worked only
  temporarily, but restarting mysqld seemed to recreate it.  Anyone have
  ideas on how to drop this database?

  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't drop database that shows up in show databases

[Waynn Lue]

This problem actually started after I moved the datadir to another
folder, so that's quite possible. We stopped everything, rsynced the
folders over, then restarted mysql. But I do notice a permissions
problem, since the owner currently is root. I'll chown it to mysql
then see what happens.

I'll post back with results.

Thanks for all the advice,
Waynn



On 3/8/08, Baron Schwartz [EMAIL PROTECTED] wrote:
 Hi,

 On Sat, Mar 8, 2008 at 6:58 AM, Waynn Lue [EMAIL PROTECTED] wrote:
  SHOW DATABASES; shows that I have a database called test, but when I
   call DROP DATABASE test, I get
 
   ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist
 
   When I run any queries on that database, I get errors like:
 
   mysql select * from Users;
   ERROR 1017 (HY000): Can't find file: './test/Users.frm' (errno: 13)

 Whenever you see errno: X, run perror and see what the error is:

 [EMAIL PROTECTED]:~$ perror 13
 OS error code  13:  Permission denied

 That is why other posters are suggesting it might be a permissions
 problem.  It sounds to me like there might be an InnoDB problem mixed
 in with this: did you delete any .frm files (or databases) from the
 filesystem without first dropping the InnoDB files that use them?
 InnoDB will complain about this if you did.

 
   My previous solution of deleting the ./test folder worked only
   temporarily, but restarting mysqld seemed to recreate it.  Anyone have
   ideas on how to drop this database?
 
   --
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

DROP DATABASE

[krishna chandra prajapati]

hi all,

Does drop database command removes all the users permissions related with
that databases or those user permissions has to be revoke manually.

Regards,
Krishna

Re: DROP DATABASE

[Aleksandar Bradaric]

Hi,

 Does drop database command removes all the users permissions related with
 that databases or those user permissions has to be revoke manually.

DROP  DATABASE  does  not remove the privileges. You have to
remove that separately.


Best regards,
Aleksandar


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re[2]: DROP DATABASE

[Aleksandar Bradaric]

Hi,

 What will be the impact if i don't remove the users privileges. Does mysql
 will restart successfully  or not.  OR should i remove the users privileges
 before dropping the database.

It  will  restart  sucessfully. The only impact I can see is
that next time a database with the same name is created, the
not-removed users will have access to it.


Best regards,
Aleksandar


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: DROP DATABASE

[krishna chandra prajapati]

Hi Bradaric,

On my system innodb storage engine is used. If i drop all the databases even
then also i cannot reclaim the free space. For that i need to remove the
ibdata , ib_logfile0, ib_logfile1 and other databases folder.  then restart
mysql.

What will be the impact if i don't remove the users privileges. Does mysql
will restart successfully  or not.  OR should i remove the users privileges
before dropping the database.

Please guide me.
Regards,
krishna



On 8/17/07, Aleksandar Bradaric [EMAIL PROTECTED] wrote:

 Hi,

  Does drop database command removes all the users permissions related
 with
  that databases or those user permissions has to be revoke manually.

 DROP  DATABASE  does  not remove the privileges. You have to
 remove that separately.


 Best regards,
 Aleksandar

weird drop database error

[Les Schaffer]

We are getting a weird DROP DATABASE error


2007-06-10 11:25:41 db 527 DEBUGDROP DATABASE BCS

OperationalError: (1051, Unknown table
'assignments,chemo,choicelists,dia,forms
,horm,id,permissions,qhistory,qnotes,qstates,questionnaires,r')


the thing is, there is NO table 'r' ... can't tell if that's just the
truncation of long string, since we do have tables that begin with the
letter 'r'. but if its a truncation, which is the offending table, since
the ones in the string as shown are there as advertised. also, is that
correct that MySQL says 'table' (not plural), but then lists a set of
tables??? Has MySQL made itself believe taht string is one long table
name. please note: we are acccessing MySQL through the python mysqldb
wrapper. but we did simply execute the query 'DROP DATABASE BCS' which i
THINK is passed straight through to the MySQL engine.

query: does drop database XXX delete tables regardless of foreign key
constraints? we are using innodb. but i thought drop database xxx
deletes tables with extreme prejudice. but if its a constraint issue,
then why the unknown table error?

thanks.

Les Schaffer

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

DROP DATABASE weirdness

[Bruce Ferrell]

I'm running MySQL 5.0.24a and I have a database I need to drop.  When I 
issue the command DROP DATABASE webdb the client seems to just hang.  I 
see the command hit the server in the full log but the database never 
drops.  Anyone have any suggestions?


--
One day at a time, one second if that's what it takes


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: DROP DATABASE weirdness

[Logan, David (SST - Adelaide)]

Hi Bruce,

Do you have any messages in the MySQL log?

Regards 


---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +61 8 8408 4273
  _/  _/  _/_/_/  Mobile: +61 417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia 
invent   
---

-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 30 November 2006 9:42 AM
To: mysql@lists.mysql.com
Subject: DROP DATABASE weirdness


I'm running MySQL 5.0.24a and I have a database I need to drop.  When I 
issue the command DROP DATABASE webdb the client seems to just hang.  I 
see the command hit the server in the full log but the database never 
drops.  Anyone have any suggestions?

-- 
One day at a time, one second if that's what it takes


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: DROP DATABASE weirdness

[Bruce Ferrell]

I get this error:

/usr/sbin/mysqld-max: relocation error: /usr/sbin/mysqld-max: undefined 
symbol: zlibCompileFlags



Logan, David (SST - Adelaide) wrote:

Hi Bruce,

Do you have any messages in the MySQL log?

Regards 



---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database

*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +61 8 8408 4273
  _/  _/  _/_/_/  Mobile: +61 417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,

   _/ **  Adelaide SA 5001
  Australia 
invent   
---


-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 30 November 2006 9:42 AM

To: mysql@lists.mysql.com
Subject: DROP DATABASE weirdness


I'm running MySQL 5.0.24a and I have a database I need to drop.  When I 
issue the command DROP DATABASE webdb the client seems to just hang.  I 
see the command hit the server in the full log but the database never 
drops.  Anyone have any suggestions?




--
One day at a time, one second if that's what it takes


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: DROP DATABASE weirdness

[Logan, David (SST - Adelaide)]

Hi Bruce,

Sounds like you are missing a library or two on the system. Did you
compile the server yourself?

Regards 


---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +61 8 8408 4273
  _/  _/  _/_/_/  Mobile: +61 417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia 
invent   
---

-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 30 November 2006 9:52 AM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: Re: DROP DATABASE weirdness

I get this error:

/usr/sbin/mysqld-max: relocation error: /usr/sbin/mysqld-max: undefined 
symbol: zlibCompileFlags


Logan, David (SST - Adelaide) wrote:
 Hi Bruce,
 
 Do you have any messages in the MySQL log?
 
 Regards 
 
 
 ---
 ** _/ **  David Logan 
 ***   _/ ***  ITO Delivery Specialist - Database
 *_/*  Hewlett-Packard Australia Ltd
 _/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
    _/  _/  _/  _/     Desk:   +61 8 8408 4273
   _/  _/  _/_/_/  Mobile: +61 417 268 665
 *_/   **
 **  _/    Postal: 148 Frome Street,
    _/ **  Adelaide SA 5001
   Australia 
 invent   
 ---
 
 -Original Message-
 From: Bruce Ferrell [mailto:[EMAIL PROTECTED] 
 Sent: Thursday, 30 November 2006 9:42 AM
 To: mysql@lists.mysql.com
 Subject: DROP DATABASE weirdness
 
 
 I'm running MySQL 5.0.24a and I have a database I need to drop.  When
I 
 issue the command DROP DATABASE webdb the client seems to just hang.
I 
 see the command hit the server in the full log but the database never 
 drops.  Anyone have any suggestions?
 

-- 
One day at a time, one second if that's what it takes


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: DROP DATABASE weirdness

[Bruce Ferrell]

Yes, built with SSL support the build seemed to go OK and sometimes it 
works.  I'm building on SuSE 9.2 using the src.rpm and the included spec 
file


Logan, David (SST - Adelaide) wrote:

Hi Bruce,

Sounds like you are missing a library or two on the system. Did you
compile the server yourself?

Regards 



---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database

*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +61 8 8408 4273
  _/  _/  _/_/_/  Mobile: +61 417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,

   _/ **  Adelaide SA 5001
  Australia 
invent   
---


-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 30 November 2006 9:52 AM

To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: Re: DROP DATABASE weirdness

I get this error:

/usr/sbin/mysqld-max: relocation error: /usr/sbin/mysqld-max: undefined 
symbol: zlibCompileFlags



Logan, David (SST - Adelaide) wrote:


Hi Bruce,

Do you have any messages in the MySQL log?

Regards 



---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database

*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +61 8 8408 4273
  _/  _/  _/_/_/  Mobile: +61 417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,

   _/ **  Adelaide SA 5001
 Australia 
invent   
---


-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 30 November 2006 9:42 AM

To: mysql@lists.mysql.com
Subject: DROP DATABASE weirdness


I'm running MySQL 5.0.24a and I have a database I need to drop.  When


I 


issue the command DROP DATABASE webdb the client seems to just hang.


I 

see the command hit the server in the full log but the database never 
drops.  Anyone have any suggestions?







--
One day at a time, one second if that's what it takes


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: DROP DATABASE weirdness

[Bruce Ferrell]

I think I found it.  The system has zlib 1.1.4  the sources have zlib 
1.2.3.  Trying a static build now.  Anyone wanna bet? :)


Bruce Ferrell wrote:
Yes, built with SSL support the build seemed to go OK and sometimes it 
works.  I'm building on SuSE 9.2 using the src.rpm and the included spec 
file


Logan, David (SST - Adelaide) wrote:


Hi Bruce,

Sounds like you are missing a library or two on the system. Did you
compile the server yourself?

Regards

---
** _/ **  David Logan ***   _/ 
***  ITO Delivery Specialist - Database

*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +61 8 8408 4273
  _/  _/  _/_/_/  Mobile: +61 417 268 665
*_/   ****  _/    Postal: 
148 Frome Street,

   _/ **  Adelaide SA 5001
  Australia inve
nt   
---


-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 30 
November 2006 9:52 AM

To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: Re: DROP DATABASE weirdness

I get this error:

/usr/sbin/mysqld-max: relocation error: /usr/sbin/mysqld-max: 
undefined symbol: zlibCompileFlags



Logan, David (SST - Adelaide) wrote:


Hi Bruce,

Do you have any messages in the MySQL log?

Regards

---
** _/ **  David Logan ***   _/ 
***  ITO Delivery Specialist - Database

*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +61 8 8408 4273
  _/  _/  _/_/_/  Mobile: +61 417 268 665
*_/   ****  _/    Postal: 
148 Frome Street,

   _/ **  Adelaide SA 5001
 Australia inve
nt   
---


-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 30 
November 2006 9:42 AM

To: mysql@lists.mysql.com
Subject: DROP DATABASE weirdness


I'm running MySQL 5.0.24a and I have a database I need to drop.  When



I


issue the command DROP DATABASE webdb the client seems to just hang.



I

see the command hit the server in the full log but the database never 
drops.  Anyone have any suggestions?









--
One day at a time, one second if that's what it takes


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: DROP DATABASE weirdness

[Bruce Ferrell]

Nope, That didn't fix it.  I'd uses MySQl provided rpms but they don't 
seem to have them got glib23 anymore.


Suggestions?

Bruce Ferrell wrote:
I think I found it.  The system has zlib 1.1.4  the sources have zlib 
1.2.3.  Trying a static build now.  Anyone wanna bet? :)


Bruce Ferrell wrote:

Yes, built with SSL support the build seemed to go OK and sometimes it 
works.  I'm building on SuSE 9.2 using the src.rpm and the included 
spec file


Logan, David (SST - Adelaide) wrote:


Hi Bruce,

Sounds like you are missing a library or two on the system. Did you
compile the server yourself?

Regards

---
** _/ **  David Logan ***   _/ 
***  ITO Delivery Specialist - Database

*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +61 8 8408 4273
  _/  _/  _/_/_/  Mobile: +61 417 268 665
*_/   ****  _/    Postal: 
148 Frome Street,

   _/ **  Adelaide SA 5001
  Australia inve
nt   
---


-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 30 
November 2006 9:52 AM

To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: Re: DROP DATABASE weirdness

I get this error:

/usr/sbin/mysqld-max: relocation error: /usr/sbin/mysqld-max: 
undefined symbol: zlibCompileFlags



Logan, David (SST - Adelaide) wrote:


Hi Bruce,

Do you have any messages in the MySQL log?

Regards

---
** _/ **  David Logan ***   _/ 
***  ITO Delivery Specialist - Database

*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +61 8 8408 4273
  _/  _/  _/_/_/  Mobile: +61 417 268 665
*_/   ****  _/    
Postal: 148 Frome Street,

   _/ **  Adelaide SA 5001
 Australia inve
nt   
---


-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 
30 November 2006 9:42 AM

To: mysql@lists.mysql.com
Subject: DROP DATABASE weirdness


I'm running MySQL 5.0.24a and I have a database I need to drop.  When




I


issue the command DROP DATABASE webdb the client seems to just hang.




I

see the command hit the server in the full log but the database 
never drops.  Anyone have any suggestions?











--
One day at a time, one second if that's what it takes


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: DROP DATABASE weirdness

[Logan, David (SST - Adelaide)]

Hi Bruce,

Have a squiz here http://bugs.mysql.com/bug.php?id=16586 sounds
suspiciously like your issue.

Regards 


---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +61 8 8408 4273
  _/  _/  _/_/_/  Mobile: +61 417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia 
invent   
---

-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 30 November 2006 11:17 AM
To: mysql@lists.mysql.com
Cc: Logan, David (SST - Adelaide)
Subject: Re: DROP DATABASE weirdness

Nope, That didn't fix it.  I'd uses MySQl provided rpms but they don't 
seem to have them got glib23 anymore.

Suggestions?

Bruce Ferrell wrote:
 I think I found it.  The system has zlib 1.1.4  the sources have zlib 
 1.2.3.  Trying a static build now.  Anyone wanna bet? :)
 
 Bruce Ferrell wrote:
 
 Yes, built with SSL support the build seemed to go OK and sometimes
it 
 works.  I'm building on SuSE 9.2 using the src.rpm and the included 
 spec file

 Logan, David (SST - Adelaide) wrote:

 Hi Bruce,

 Sounds like you are missing a library or two on the system. Did you
 compile the server yourself?

 Regards

 ---
 ** _/ **  David Logan ***   _/ 
 ***  ITO Delivery Specialist - Database
 *_/*  Hewlett-Packard Australia Ltd
 _/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
    _/  _/  _/  _/     Desk:   +61 8 8408 4273
   _/  _/  _/_/_/  Mobile: +61 417 268 665
 *_/   ****  _/  
Postal: 
 148 Frome Street,
    _/ **  Adelaide SA 5001
   Australia inve
 nt   
 ---

 -Original Message-
 From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday,
30 
 November 2006 9:52 AM
 To: Logan, David (SST - Adelaide)
 Cc: mysql@lists.mysql.com
 Subject: Re: DROP DATABASE weirdness

 I get this error:

 /usr/sbin/mysqld-max: relocation error: /usr/sbin/mysqld-max: 
 undefined symbol: zlibCompileFlags


 Logan, David (SST - Adelaide) wrote:

 Hi Bruce,

 Do you have any messages in the MySQL log?

 Regards

 ---
 ** _/ **  David Logan ***   _/ 
 ***  ITO Delivery Specialist - Database
 *_/*  Hewlett-Packard Australia Ltd
 _/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
    _/  _/  _/  _/     Desk:   +61 8 8408 4273
   _/  _/  _/_/_/  Mobile: +61 417 268 665
 *_/   ****  _/    
 Postal: 148 Frome Street,
    _/ **  Adelaide SA 5001
  Australia inve
 nt   
 ---

 -Original Message-
 From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 
 30 November 2006 9:42 AM
 To: mysql@lists.mysql.com
 Subject: DROP DATABASE weirdness


 I'm running MySQL 5.0.24a and I have a database I need to drop.
When



 I

 issue the command DROP DATABASE webdb the client seems to just
hang.



 I

 see the command hit the server in the full log but the database 
 never drops.  Anyone have any suggestions?




 

-- 
One day at a time, one second if that's what it takes


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: DROP DATABASE weirdness

[Bruce Ferrell]

Yeah, that is the bug exactly.  I can't even get 5.0.27 to start it 
comes up with the exact same error.  Maybe if I get rid of the SSL 
option but this sucks!  Thanks for the help


Logan, David (SST - Adelaide) wrote:

Hi Bruce,

Have a squiz here http://bugs.mysql.com/bug.php?id=16586 sounds
suspiciously like your issue.

Regards 



---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database

*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +61 8 8408 4273
  _/  _/  _/_/_/  Mobile: +61 417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,

   _/ **  Adelaide SA 5001
  Australia 
invent   
---


-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 30 November 2006 11:17 AM

To: mysql@lists.mysql.com
Cc: Logan, David (SST - Adelaide)
Subject: Re: DROP DATABASE weirdness

Nope, That didn't fix it.  I'd uses MySQl provided rpms but they don't 
seem to have them got glib23 anymore.


Suggestions?

Bruce Ferrell wrote:

I think I found it.  The system has zlib 1.1.4  the sources have zlib 
1.2.3.  Trying a static build now.  Anyone wanna bet? :)


Bruce Ferrell wrote:



Yes, built with SSL support the build seemed to go OK and sometimes


it 

works.  I'm building on SuSE 9.2 using the src.rpm and the included 
spec file


Logan, David (SST - Adelaide) wrote:



Hi Bruce,

Sounds like you are missing a library or two on the system. Did you
compile the server yourself?

Regards

---
** _/ **  David Logan ***   _/ 
***  ITO Delivery Specialist - Database

*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +61 8 8408 4273
  _/  _/  _/_/_/  Mobile: +61 417 268 665
*_/   ****  _/  


Postal: 


148 Frome Street,
   _/ **  Adelaide SA 5001
 Australia inve
nt   
---


-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday,


30 


November 2006 9:52 AM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: Re: DROP DATABASE weirdness

I get this error:

/usr/sbin/mysqld-max: relocation error: /usr/sbin/mysqld-max: 
undefined symbol: zlibCompileFlags



Logan, David (SST - Adelaide) wrote:



Hi Bruce,

Do you have any messages in the MySQL log?

Regards

---
** _/ **  David Logan ***   _/ 
***  ITO Delivery Specialist - Database

*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +61 8 8408 4273
  _/  _/  _/_/_/  Mobile: +61 417 268 665
*_/   ****  _/    
Postal: 148 Frome Street,

   _/ **  Adelaide SA 5001
Australia inve
nt   
---


-Original Message-
From: Bruce Ferrell [mailto:[EMAIL PROTECTED] Sent: Thursday, 
30 November 2006 9:42 AM

To: mysql@lists.mysql.com
Subject: DROP DATABASE weirdness


I'm running MySQL 5.0.24a and I have a database I need to drop.


When




I



issue the command DROP DATABASE webdb the client seems to just


hang.




I


see the command hit the server in the full log but the database 
never drops.  Anyone have any suggestions?









--
One day at a time, one second if that's what it takes


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysql crashes on drop database?

[Duzenbury, Rich]

Hi,

Each night I replicate a server running a 4.1 version of mysql via
mysqldump and then import to a box running a 5.0.18-standard.  It has
been working well for some four or five months.  Suddenly, the job
started failing during the import.  After stepping through the job, I
determined that attempting to drop the first database causes mysql to
crash.

There are about seven or eight tables in said database, mostly empty,
with about five or six records total in the database in question

mysql drop database AccountingAdminDB;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Per the mysql docs, I first tried rebooting the machine, to no avail.

Interestingly, I can read all of the records in the all of the
associated files.  Check table on each table did not turn up anything.

File permissions look ok.  I use innodb file per table for most tables.
The data directories and subordinates all use mysql.mysql as the owner
and group.  The data files are rw by owner.

A mysql.error.log file is being generated:

060901  9:45:18  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060901  9:45:24  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 68 3744135056.
InnoDB: Doing recovery: scanned up to log sequence number 68 3744135056
InnoDB: Last MySQL binlog file position 0 0, file name 
060901  9:45:24  InnoDB: Started; log sequence number 68 3744135056
060901  9:45:24 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.18-standard'  socket: '/srv/mysql/lx07sock'  port: 3307
MySQL Community Edition - Standard (GPL)
InnoDB: Error: trying to access page number 464309120 in space 0,
InnoDB: space name /srv/mysql/lx07/ibdata1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10
060901 10:03:37InnoDB: Assertion failure in thread 2934508464 in file
fil0fil.c line 3869
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this
binary
or one of the libraries it was linked against is corrupt, improperly
built,
or misconfigured. This error can also be caused by malfunctioning
hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail.

key_buffer_size=268435456
read_buffer_size=8384512
max_used_connections=2
max_connections=100
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
= 2719343 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8a40170
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xaee8cd1c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x815bbb0
0xe420
0x82f2c3e
0x82f2c3e
0x82ea23c
0x8308090
0x83074ee
0x82b5e4f
0x826ff01
0x82a7ed6
0x82a813c
0x82a825c
0x8282ec0
0x8282bfb
0x82979e2
0x820cd73
0x81fffdb
0x8212f00
0x82128b4
0x8211cce
0x8211509
0x81728bf
Stack trace seems successful - bottom reached
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do

resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x8a5fde8 = drop database AccountingAdminDB
thd-thread_id=57
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
060901 10:03:37  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060901 10:03:37  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 68 3744135066.
InnoDB: Doing recovery: scanned up to log sequence number 68 3744135066
InnoDB: Last MySQL binlog file position 0 0, file name 
060901 10:03:37  InnoDB: Started; log sequence number 68 3744135066
060901 10:03:38 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.18-standard'  socket: '/srv/mysql/lx07sock'  port: 3307
MySQL Community Edition

DROP DATABASE doesn't actually drop the database?

[Stephen Cook]

I am scripting out the creation of a database so I can make changes and then
run the script to generate a clean copy (by running it in MySQL Query
Browser).

The script DROPs all the tables, then CREATEs them again along with all the
indices and whatnot.  However, if I run the script after having run it once
(if I close the Query Browser and then open it again and reload the script
later), I get error 1061 Duplicate key name on all of the indices.

So I figured I missed something, and I'll just DROP the whole database and
then run the script.  I add a DROP DATABASE databasename; and a CREATE
DATABASE databasename; at the start of my script and try to run it again.
Now I get errors 1008 Can't drop database 'databasename'; database doesn't
exist and 1007 Can't create database 'databasename'; database exists,
followed by the index errors above.

If I drop the database from MySQL Administrator (or the command-line
client), and then run the script again I get the same errors.  The database
and all of the scripted objects are created (properly as far as I can tell),
but why would I get these errors?

I am using the latest version of MySQL (5.0.19) with InnoDB as the storage
engine on Windows 2000 Professional SP4, but I have the same problem with
5.0.17.

If there is any other information you need I'd be happy to supply it.

Re: DROP DATABASE doesn't actually drop the database?

[Stephen Cook]

Thanks for your comments!

I ran my entire script (DROP DATABASE and all) with the command-line client,
and got no errors.  Perhaps there is something with the Query Browser that
is causing this problem.

I added the IF EXISTS and IF NOT EXISTS in appropriate places (although I
can see the database there beforehand, and see it disappear in MySQL
Administrator, it shouldn't be necessary in this case because I know what is
there).  Of course, I still get the duplicate index errors in Query Browser,
but I suspect it is that program and not the database server that is causing
this.

The reason I am scripting this manually is because I am trying to learn and
practice MySQL (I come from a MS SQL Server background), and this database
is very small (3 or 4 tables, a view and 2 stored procedures so far).  It is
not difficult to make a change and re-run the script.  When I get anything
good (and of decent size) going I'll start using the other options; right
now I'm just playing with a throwaway database to get a feel for the syntax.



On 3/13/06, Andreas Krüger [EMAIL PROTECTED] wrote:

 Stephen,

 the behavior of MySQL server sounds bizarre, as to what information you
 give us.

 For the DROP DATABASE and DROP TABLE statements, there is an option that
 prevents an error, if database or table don' t exist:

 DROP DATABASE* IF EXISTS *db_name

 DROP TABLE* IF EXISTS *tbl_name

 You might also want to have a look at the 5.0 manual:
 http://dev.mysql.com/doc/refman/5.0/en/drop-database.html
 http://dev.mysql.com/doc/refman/5.0/en/drop-table.html

 I am further astonished that you do script files manually for re-creating
 databases and tables.*
 Why don' t you use mysqldump for dumping a database and its tables  mysql
 for loading the dumped information?*

 You can dump a database by
 mysqldump db_name  db_name.sql

 There are many handy options to mysqldump as --add-drop-table and -all
 that you should consider to use

 You then can manually update the dump files in a text editor.

 mysql db_name  db_name.sql

 recreates the dumped data with all tables.

 Andy


 Stephen Cook wrote:

 I am scripting out the creation of a database so I can make changes and then
 run the script to generate a clean copy (by running it in MySQL Query
 Browser).

 The script DROPs all the tables, then CREATEs them again along with all the
 indices and whatnot.  However, if I run the script after having run it once
 (if I close the Query Browser and then open it again and reload the script
 later), I get error 1061 Duplicate key name on all of the indices.

 So I figured I missed something, and I'll just DROP the whole database and
 then run the script.  I add a DROP DATABASE databasename; and a CREATE
 DATABASE databasename; at the start of my script and try to run it again.
 Now I get errors 1008 Can't drop database 'databasename'; database doesn't
 exist and 1007 Can't create database 'databasename'; database exists,
 followed by the index errors above.

 If I drop the database from MySQL Administrator (or the command-line
 client), and then run the script again I get the same errors.  The database
 and all of the scripted objects are created (properly as far as I can tell),
 but why would I get these errors?

 I am using the latest version of MySQL (5.0.19) with InnoDB as the storage
 engine on Windows 2000 Professional SP4, but I have the same problem with
 5.0.17.

 If there is any other information you need I'd be happy to supply it.

Re: cannot drop database

[Bruce Dembecki]

On Aug 15, 2005, at 5:07 AM, Logan, David (SST - Adelaide) wrote:



Hi Gary,

If you are running unix (or variants thereof), you can go to the  
data directory and remove it at the operating system level if the  
mysql client can't do it. Not sure about windows though but I would  
think the same thing would apply.


If you do that and then do a show databases, it should be gone.




David is right in that once you do this it won't show in the show  
databases list any more, but if you use a data engine that stores  
data in a shared table space (such as InnoDB to name one) doing this  
deletes the database as far as MySQL is concerned, but the data is  
still sitting in the shared spaces with no practical way of getting  
it out and freeing your space.


The correct way will be to make sure you enclose the database name in  
backticks as previously mentioned in this thread.


Best Regards, Bruce



Regards


David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Gary Huntress [mailto:[EMAIL PROTECTED]
Sent: Monday, 15 August 2005 9:09 AM
To: mysql@lists.mysql.com
Subject: cannot drop database

I need to drop a database named  ÃáãÃáà using the mysql client.   
I'm

getting you have an error in your sql syntax for the command

DROP database ÃáãÃáÃ;

I'm sure this is a character set issue.  How can I drop this database?


Regards,

Gary H.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[EMAIL PROTECTED]








--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: cannot drop database

[Terence]

Try

DROP database `ÃáãÃáÃ`;

Gary Huntress wrote:
I need to drop a database named  ÃáãÃáà using the mysql client.  I'm 
getting you have an error in your sql syntax for the command


DROP database ÃáãÃáÃ;

I'm sure this is a character set issue.  How can I drop this database?


Regards,

Gary H.






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: cannot drop database

[Martijn Tonies]

 I need to drop a database named  ÃáãÃáà using the mysql client.  I'm
 getting you have an error in your sql syntax for the command

 DROP database ÃáãÃáÃ;

 I'm sure this is a character set issue.  How can I drop this database?

What about using backticks around it:

drop database `yourname`

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle  MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: cannot drop database

[Logan, David (SST - Adelaide)]

Hi Gary,

If you are running unix (or variants thereof), you can go to the data directory 
and remove it at the operating system level if the mysql client can't do it. 
Not sure about windows though but I would think the same thing would apply.

If you do that and then do a show databases, it should be gone.

Regards 


David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Gary Huntress [mailto:[EMAIL PROTECTED] 
Sent: Monday, 15 August 2005 9:09 AM
To: mysql@lists.mysql.com
Subject: cannot drop database

I need to drop a database named  ÃáãÃáà using the mysql client.  I'm 
getting you have an error in your sql syntax for the command

DROP database ÃáãÃáÃ;

I'm sure this is a character set issue.  How can I drop this database?


Regards,

Gary H.




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

cannot drop database

[Gary Huntress]

I need to drop a database named  ÃáãÃáà using the mysql client.  I'm 
getting you have an error in your sql syntax for the command


DROP database ÃáãÃáÃ;

I'm sure this is a character set issue.  How can I drop this database?


Regards,

Gary H.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: drop database after lost of synchro in innodb

[Heikki Tuuri]

Johanne,

DROP DATABASE is rather safe, if it succeeds.

http://dev.mysql.com/doc/mysql/en/InnoDB.html is the best reference.

Best regards,

Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL support from http://www.mysql.com/support/index.html


- Alkuperäinen viesti - 
Lähettäjä: Duhaime Johanne [EMAIL PROTECTED]
Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]
Lähetetty: Thursday, September 16, 2004 10:01 PM
Aihe: RE : drop database after lost of synchro in innodb


Thank a lot

I succeeded in dropping the tablespace after deleting manually the files.

I was just wondering if this is a good way to do thing. Nothing left in the
bd that way?

Would you have any suggestion on any book that would go deep in the
mysql/innodb stuff. For example what to do in the present case?

Thank you very much and have a nice day.

Johanne




-Message d'origine-
De : Heikki Tuuri [mailto:[EMAIL PROTECTED]
Envoyé : 16-Sep-04 11:14 AM
À : [EMAIL PROTECTED]
Objet : Re: drop database after lost of synchro in innodb


Johanne,

have you moved .ibd files and .frm files around?

Looks like the .frm file and the .ibd file exists, but the InnoDB table is
missing from the InnoDB data dictionary inside the ibdata1 file.

Try removing BOTH the .frm file and .ibd file manually.

Regards,

Heikki


- Alkuperäinen viesti - 
Lähettäjä: Duhaime Johanne [EMAIL PROTECTED]
Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]
Lähetetty: Thursday, September 16, 2004 3:27 PM
Aihe: RE : drop database after lost of synchro in innodb


Thank you for your answer.

But both frm and ibd exist for every tables.

I tried to delete the frm for every tables (copy somewhere else out ouf the
databases) but still the drop database did not work. I then have:

ERROR 1010: Error dropping database (can't rmdir './taptag', errno: 17)
because the tablespace files exists

Thank you very much for your help.
JOhanne

-Message d'origine-
De : Heikki Tuuri [mailto:[EMAIL PROTECTED]
Envoyé : 16-Sep-04 3:46 AM
À : [EMAIL PROTECTED]
Objet : Re: drop database after lost of synchro in innodb


Johanne,

looks like the file Alias.frm exists, but the corresponding InnoDB table
does not exist.

Delete manually the .frm file. Then it should be in sync again.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL InnoDB Hot
Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/


- Original Message - 
From: Duhaime Johanne [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, September 15, 2004 10:26 PM
Subject: drop database after lost of synchro in innodb


 --_=_NextPart_001_01C49B59.D21B539E
 Content-Type: text/plain;
 charset=iso-8859-1
 Content-Transfer-Encoding: quoted-printable

 Hello
 =20
 I am trying to drop a database but I have the following: ERROR 1016: =
 Can't open file: 'Alias.InnoDB' (errno: 1) =20 In fact it looks like
 the synchro is lost between FRM and IBD. (I use a = multiple
 tablespace configuration) according to=20
 http://www.innodb.com/ibman.php   section 15.1.
 =20
 =20
 What is the proper way to drop the database in that case. Can I use
 the = unix command on the file system?=20 =20
 Thank you in advance for your help.
 =20
 =20
 Johanne Duhaime
 IRCM
 110 avenue des Pins Ouest
 Montr=E9al, Qu=E9bec
 Canada
 H2W 1R7
 courrier: [EMAIL PROTECTED]
 tel: (514) 987-5556
 fax: (514) 987-5644
 =20
 =20

 --_=_NextPart_001_01C49B59.D21B539E--


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: drop database after lost of synchro in innodb

[Heikki Tuuri]

Johanne,

looks like the file Alias.frm exists, but the corresponding InnoDB table
does not exist.

Delete manually the .frm file. Then it should be in sync again.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/


- Original Message - 
From: Duhaime Johanne [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, September 15, 2004 10:26 PM
Subject: drop database after lost of synchro in innodb


 --_=_NextPart_001_01C49B59.D21B539E
 Content-Type: text/plain;
 charset=iso-8859-1
 Content-Transfer-Encoding: quoted-printable

 Hello
 =20
 I am trying to drop a database but I have the following: ERROR 1016: =
 Can't open file: 'Alias.InnoDB' (errno: 1)
 =20
 In fact it looks like the synchro is lost between FRM and IBD. (I use a =
 multiple tablespace configuration) according to=20
 http://www.innodb.com/ibman.php   section 15.1.
 =20
 =20
 What is the proper way to drop the database in that case. Can I use the =
 unix command on the file system?=20
 =20
 Thank you in advance for your help.
 =20
 =20
 Johanne Duhaime
 IRCM
 110 avenue des Pins Ouest
 Montr=E9al, Qu=E9bec
 Canada
 H2W 1R7
 courrier: [EMAIL PROTECTED]
 tel: (514) 987-5556
 fax: (514) 987-5644
 =20
 =20

 --_=_NextPart_001_01C49B59.D21B539E--


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: drop database after lost of synchro in innodb

[Heikki Tuuri]

Johanne,

have you moved .ibd files and .frm files around?

Looks like the .frm file and the .ibd file exists, but the InnoDB table is
missing from the InnoDB data dictionary inside the ibdata1 file.

Try removing BOTH the .frm file and .ibd file manually.

Regards,

Heikki


- Alkuperäinen viesti - 
Lähettäjä: Duhaime Johanne [EMAIL PROTECTED]
Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]
Lähetetty: Thursday, September 16, 2004 3:27 PM
Aihe: RE : drop database after lost of synchro in innodb


Thank you for your answer.

But both frm and ibd exist for every tables.

I tried to delete the frm for every tables (copy somewhere else out ouf the
databases) but still the drop database did not work. I then have:

ERROR 1010: Error dropping database (can't rmdir './taptag', errno: 17)
because the tablespace files exists

Thank you very much for your help.
JOhanne

-Message d'origine-
De : Heikki Tuuri [mailto:[EMAIL PROTECTED]
Envoyé : 16-Sep-04 3:46 AM
À : [EMAIL PROTECTED]
Objet : Re: drop database after lost of synchro in innodb


Johanne,

looks like the file Alias.frm exists, but the corresponding InnoDB table
does not exist.

Delete manually the .frm file. Then it should be in sync again.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL InnoDB Hot
Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/


- Original Message - 
From: Duhaime Johanne [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, September 15, 2004 10:26 PM
Subject: drop database after lost of synchro in innodb


 --_=_NextPart_001_01C49B59.D21B539E
 Content-Type: text/plain;
 charset=iso-8859-1
 Content-Transfer-Encoding: quoted-printable

 Hello
 =20
 I am trying to drop a database but I have the following: ERROR 1016: =
 Can't open file: 'Alias.InnoDB' (errno: 1) =20
 In fact it looks like the synchro is lost between FRM and IBD. (I use a =
 multiple tablespace configuration) according to=20
 http://www.innodb.com/ibman.php   section 15.1.
 =20
 =20
 What is the proper way to drop the database in that case. Can I use the =
 unix command on the file system?=20
 =20
 Thank you in advance for your help.
 =20
 =20
 Johanne Duhaime
 IRCM
 110 avenue des Pins Ouest
 Montr=E9al, Qu=E9bec
 Canada
 H2W 1R7
 courrier: [EMAIL PROTECTED]
 tel: (514) 987-5556
 fax: (514) 987-5644
 =20
 =20

 --_=_NextPart_001_01C49B59.D21B539E--


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

drop database after lost of synchro in innodb

[Duhaime Johanne]

Hello
 
I am trying to drop a database but I have the following: ERROR 1016: Can't open file: 
'Alias.InnoDB' (errno: 1)
 
In fact it looks like the synchro is lost between FRM and IBD. (I use a multiple 
tablespace configuration) according to 
http://www.innodb.com/ibman.php   section 15.1.
 
 
What is the proper way to drop the database in that case. Can I use the unix command 
on the file system? 
 
Thank you in advance for your help.
 
 
Johanne Duhaime
IRCM
110 avenue des Pins Ouest
Montréal, Québec
Canada
H2W 1R7
courrier: [EMAIL PROTECTED]
tel: (514) 987-5556
fax: (514) 987-5644
 
 

Drop database

[Michael Satterwhite]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

In mysql 4.0.18

I have a database with innodb tables and foreign keys. I need to drop / 
recreate the database for testing purposes. When I enter the command

drop database xxx

I get an error message indicating a foreign key violation. As I am dropping 
the entire database, this makes no sense. Is there a way around this, or is 
it simply a bug in the mysql logic?

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFBQjXFjeziQOokQnARAjpcAKCnWPAaYgU3uZbFci1a/yIecNXHFgCgmnWu
sA+NH+4v2SY70PayCV1GksU=
=1ptQ
-END PGP SIGNATURE-

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Can't drop database

[michael watson (IAH-C)]

Hi

I am running Suse Linux 8.2 and MySQL 3.23.55

A funny thing happens:

% mysqladmin drop base
Database base dropped
% mysqladmin create base
Can't create database 'base'. Database exists.

And if I log in to MySQL, there it is - undropped!

So why is mysqladmin telling me the database is dropped when it isn't?

Thanks in advance!

Mick

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't drop database

[Michael Stassen]

When you DROP DATABASE 'base', mysql drops all its tables then drops the db. 
 At the filesystem, this means deleting all the table files from the 'base' 
subdirectory of mysql's data directory, then deleting the 'base directory 
itself.  If 'base' contains a file that isn't a table file, however, that 
file won't be deleted by mysql, which causes the attempt to delete the 
directory to fail.  Since directory 'base' still exists, mysql sees it as db 
'base' still exists.  So check in 'base' for any leftover files and either 
delete them or move them somewhere else, then try again.

Michael
michael watson (IAH-C) wrote:
Hi
I am running Suse Linux 8.2 and MySQL 3.23.55
A funny thing happens:
% mysqladmin drop base
Database base dropped
% mysqladmin create base
Can't create database 'base'. Database exists.
And if I log in to MySQL, there it is - undropped!
So why is mysqladmin telling me the database is dropped when it isn't?
Thanks in advance!
Mick

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

can't drop database: ERROR 1217

[Dean A. Hoover]

version: mysql  Ver 12.22 Distrib 4.0.18, for pc-linux (i686)

mysql DROP DATABASE xxx;
ERROR 1217: Cannot delete or update a parent row: a foreign key 
constraint fails

what is this all about? how the heck can I drop the database?
Dean Hoover


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: can't drop database: ERROR 1217

[Ansari, Raza \(GEI, GEFA\)]

Dean,
   Seems your database has a table with foreign key constraints. You need to delete 
that table first and then you will be able to drop database successfully. 

Hope that helps!!!
Raza

-Original Message-
From: Dean A. Hoover [mailto:[EMAIL PROTECTED]
Sent: Friday, May 14, 2004 4:13 AM
To: [EMAIL PROTECTED]
Subject: can't drop database: ERROR 1217


version: mysql  Ver 12.22 Distrib 4.0.18, for pc-linux (i686)

mysql DROP DATABASE xxx;
ERROR 1217: Cannot delete or update a parent row: a foreign key 
constraint fails

what is this all about? how the heck can I drop the database?
Dean Hoover



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

'drop database' failing silently?

[Kevin O' Riordan]

Hi all,

this morning, I had a problem dropping a database from within mysql.

mysql drop database mis;
Query OK, 0 rows affected (0.00 sec)
 
mysql show databases;
+--+
| Database |
+--+
| mis  |
| mysql|
+--+
2 rows in set (0.00 sec)

mysql use mis;
Database changed

mysql show tables;
Empty set (0.00 sec)

I was logged in as mysql's root user at the time, and had successfully
deleted other databases.  I also tried dropping the database with
'mysqladmin', which likewise told me the database had been dropped, but
didn't actually drop it.

The solution I used was to stop mysqld and then rm the database from the
filesystem.

I'm using mysql version 3.23.58 on redhat 7.3.

Apologies if this has been discussed before, but I couldn't find a
reference to it in the archive.  Elsewhere, I found that someone else
has seen this problem[1], but found no answer.

If anyone can give any hints as to what I should've done, or what went
might've gone wrong at the time, I'd be grateful.

thanks in advance,
-kev


[1] 
http://forums.devarticles.com/showthread.php?s=d1026b7f0c6df0f6f10524dafb23p=30160#post30160

-- 
jc never send a butcher to the comms room

Timeless wisdom from JC, on #supporteers (irc.linux.ie)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 'drop database' failing silently?

[Victoria Reznichenko]

Kevin O' Riordan [EMAIL PROTECTED] wrote:
 this morning, I had a problem dropping a database from within mysql.
 
mysql drop database mis;
Query OK, 0 rows affected (0.00 sec)
 
mysql show databases;
+--+
| Database |
+--+
| mis  |
| mysql|
+--+
2 rows in set (0.00 sec)
 
mysql use mis;
Database changed
 
mysql show tables;
Empty set (0.00 sec)
 
 I was logged in as mysql's root user at the time, and had successfully
 deleted other databases.  I also tried dropping the database with
 'mysqladmin', which likewise told me the database had been dropped, but
 didn't actually drop it.
 
 The solution I used was to stop mysqld and then rm the database from the
 filesystem.
 
 I'm using mysql version 3.23.58 on redhat 7.3.
 
 Apologies if this has been discussed before, but I couldn't find a
 reference to it in the archive.  Elsewhere, I found that someone else
 has seen this problem[1], but found no answer.
 
 If anyone can give any hints as to what I should've done, or what went
 might've gone wrong at the time, I'd be grateful.
 

Are there any non-mysql files in the directory of the database 'mis'?


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 'drop database' failing silently?

[Kevin O' Riordan]

  this morning, I had a problem dropping a database from within mysql.

 Are there any non-mysql files in the directory of the database 'mis'?

Should've added this.  There was only one file in the 'mis' directory:

-rw-rw-rw-1 mysqlmysql  44 Apr 30 09:51 file.out

Which consisted of the following 186 char long line:

2   0   0   0   0  
 2004032200  no
  no  no


The file's access/mod/change times looked like:

Access: Wed May  5 07:28:38 2004
Modify: Fri Apr 30 09:51:16 2004
Change: Fri Apr 30 09:51:16 2004


thanks,
-kev


-- 
eth0_ we sail inthe esat ship!
eth0_ on grand canal dock!

eth0_, declaring her loyalty in #phishtank (irc.indigo.ie)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 'drop database' failing silently? - solved.

[Kevin O' Riordan]

  this morning, I had a problem dropping a database from within mysql.
[snip]
  The solution I used was to stop mysqld and then rm the database from the
  filesystem.

 Are there any non-mysql files in the directory of the database 'mis'?

After a while, I realised you weren't asking a question, but rather
giving an answer.

In case it's of any use to anyone else here:  if a database's dir
contains non-database files, the dir will not be removed following a
'DROP DATABASE'.

thanks Victoria!

-kev


-- 
My lord, this is our first offense, but not our last.  If you will
be easy with us this once, we promise, on our word as gentlemen,
to try to do better next time.  And next time sure we won't be
fools to get caught.
-Thomas Francis Meagher, being sentenced for treason, 1848.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

1051: Unknown table on running drop database

[Adam Hardy]

I'm getting this error:

ERROR 1051: Unknown table '#sql-279_28,#sql-333c_1'

after trying to drop a database. I found nothing in the docs or the 
archives about how to sort it out.

Can anybody tell me what steps I should take to remedy this?

I'm using 4.1.0

Thanks,
Adam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: style question: drop database in an install script

[Arthur Fuller]

I don't know about anyone else, but for this sort of thing I love the
program called DeZign, which is a data-modeling tool. You can draw your
tables and their columns and set up relations and so on. DeZign will then
write your code for you.

Personally, I like to keep all my drop statements in one script, all my
create scripts in another, and all my populate scripts in yet another.

Arthur

-Original Message-
From: David T-G [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 27, 2002 8:15 PM
To: mysql users
Subject: style question: drop database in an install script


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi, all --

I'm working up the schema for my application and, as you can imagine,
starting over and reloading numerous times :-)  In fact, I intend for the
script to be an install script, run on a new installation of the software
to set things up.

The script starts out

  drop database if exist dbname ;
  create database dbname ; use dbname ;
  create table tablename
  (
...
  ) ;
  ...

to set things up and then continues

  insert into tablename (field,field,field)
( data , data , data ) ,
...
( data , data , data ) ;
  ...

to set up the predefined things (credit card types and so on).

To reload the database and try something new, all I have to do is

  mysql -udroot -p  script.sql

and what was there goes away and is rebuilt fresh.  That's mighty
convenient as I'm building up a table's design and trying it out.

Soo...  When you experienced folks write software that's meant to be
installed from a script or such, do you lead off with a 'drop database'
command, or is that just too dangerous to put in a script and you make
your users clean up any old installation by hand?  This will have to be
run by a root user, so we can figure that this user might have read the
docs and know that he's going to start [over] from scratch, but then,
again, users are users :-)

Meanwhile, is there if/then/else functionailty in SQL so that I can say

  if exist dbname exit some error ;
  create database dbname ;
  ...

and not try to create and populate a database on top of an existing one?
Or is there perhaps a RENAME DATABASE command (I saw RENAME TABLE) so
that I could rename the old one to get it out of the way but not toss it?


TIA  HAND

mysql query,
:-D
- --
David T-G  * There is too much animal courage in
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, Science and Health
http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (FreeBSD)

iD8DBQE+DPsqGb7uCXufRwARAsp8AKC7BuVdyO7Dl5fkbvEM51o+i/BAEgCeI5iI
dI7HQb5oywLHuZIjLxXYZwY=
=QTHL
-END PGP SIGNATURE-

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: style question: drop database in an install script

[David T-G]

Arthur, et al --

...and then Arthur Fuller said...
% 
% I don't know about anyone else, but for this sort of thing I love the
% program called DeZign, which is a data-modeling tool. You can draw your
% tables and their columns and set up relations and so on. DeZign will then
% write your code for you.

Thanks!

I actually broke out a Windows box to try it out so that I could see how
it would relate keys :-)  Now I have some sample syntax for foreign keys,
if nothing else.

Anyone know of a design tool which runs on Linux and FreeBSD?


% 
% Personally, I like to keep all my drop statements in one script, all my
% create scripts in another, and all my populate scripts in yet another.

Yeah, I think I'll do something like that.


% 
% Arthur


Thanks  HAND  Happy Holidays

mysql query,
:-D
-- 
David T-G  * There is too much animal courage in 
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, Science and Health
http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg!


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: style question: drop database in an install script

[David T-G]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Benjamin, et al --

[I couldn't find your public key...  Is it on a keyserver?]

...and then Benjamin Pflugmann said...
% 
% Hello.

Hi!


% 
% On Fri 2002-12-27 at 20:15:22 -0500, [EMAIL PROTECTED] wrote:
% [...]
...
%  Soo...  When you experienced folks write software that's meant to be
%  installed from a script or such, do you lead off with a 'drop database'
%  command, or is that just too dangerous to put in a script
% 
% I would think so.

I'm going to figure you think too dangerous rather than think lead
off based on your comments below :-)


% 
%  and you make your users clean up any old installation by hand?
% 
% I would at least require them to do something special (use a --force
% flag or call a seperate cleanup script, or whatever).

I was thinking about a cleanup script; it sounds like that's the way to
go.


% 
%  This will have to be run by a root user, so we can figure that this
%  user might have read the docs and know that he's going to start
%  [over] from scratch, but then, again, users are users :-)
% 
% Even if most people would read the docs (that's not my experience, not
% even for those having root privileges ;), the problem is that the

Yeah, yeah :-)  One *hopes* that with power would come responsibility,
but in this loose age...


% damage could be relativly big. And the greater the possible damage the
% more conservative your scripts should be.

OK.  Sounds like sage advice.


% 
%  Meanwhile, is there if/then/else functionailty in SQL so that I can say
%  
%if exist dbname exit some error ;
%create database dbname ;
%...
%  
%  and not try to create and populate a database on top of an existing one?
% 
% Since there is a flag --force to the mysql command line client, which
% will force continuation in case of sql error, it sounds as if the
% default behaviour of the tool already is to bail out on error. If not,
% it's a bug (either documentation of behaviour).

Oh :-)  Well, so I tried it; I commented out the drop and reran my
script, and it quit at

  ERROR 1007 at line 3: Can't create database 'dbname'. Database exists

so it looks like it doesn't continue.  Good; I won't be trying to make
stuff on top of stuff.


% 
%  Or is there perhaps a RENAME DATABASE command (I saw RENAME TABLE) so
%  that I could rename the old one to get it out of the way but not toss it?
% 
% If you do not restrict yourself to a SQL script, but a shell script
% which calls mysql to execute some SQL, you could react very flexible
% on the concerns you mentioned.

Yeah.  There will be an installation script (probably shell, because of
its portability, but perhaps perl for power) so I can just take the extra
steps of detecting the database and getting rid of it as a subtask.


% 
% HTH,

It does; thanks!


% 
%   Benjamin.
% 
% -- 
% [EMAIL PROTECTED]


HAND  Happy Holidays

:-D
- -- 
David T-G  * There is too much animal courage in 
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, Science and Health
http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (FreeBSD)

iD8DBQE+DXJ9Gb7uCXufRwARAvBPAKDIg9bz9cHpSPDt06kGZ99pgxH+EwCggMNr
TRignFqywNlCFDLVrkRMO0Q=
=HCwW
-END PGP SIGNATURE-

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: style question: drop database in an install script

[Stefan Hinz, iConnect \(Berlin\)]

David,

 Or is there perhaps a RENAME DATABASE command (I saw RENAME TABLE)

in MySQL 4.1, you will find ALTER DATABASE:
http://www.mysql.com/documentation/mysql/bychapter/manual_News.html#News
-4.1.0

Regards,
--
  Stefan Hinz [EMAIL PROTECTED]
  Geschäftsführer / CEO iConnect GmbH http://iConnect.de
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: David T-G [EMAIL PROTECTED]
To: mysql users [EMAIL PROTECTED]
Sent: Saturday, December 28, 2002 2:15 AM
Subject: style question: drop database in an install script


 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1

 Hi, all --

 I'm working up the schema for my application and, as you can imagine,
 starting over and reloading numerous times :-)  In fact, I intend for
the
 script to be an install script, run on a new installation of the
software
 to set things up.

 The script starts out

   drop database if exist dbname ;
   create database dbname ; use dbname ;
   create table tablename
   (
 ...
   ) ;
   ...

 to set things up and then continues

   insert into tablename (field,field,field)
 ( data , data , data ) ,
 ...
 ( data , data , data ) ;
   ...

 to set up the predefined things (credit card types and so on).

 To reload the database and try something new, all I have to do is

   mysql -udroot -p  script.sql

 and what was there goes away and is rebuilt fresh.  That's mighty
 convenient as I'm building up a table's design and trying it out.

 Soo...  When you experienced folks write software that's meant to
be
 installed from a script or such, do you lead off with a 'drop
database'
 command, or is that just too dangerous to put in a script and you make
 your users clean up any old installation by hand?  This will have to
be
 run by a root user, so we can figure that this user might have read
the
 docs and know that he's going to start [over] from scratch, but then,
 again, users are users :-)

 Meanwhile, is there if/then/else functionailty in SQL so that I can
say

   if exist dbname exit some error ;
   create database dbname ;
   ...

 and not try to create and populate a database on top of an existing
one?
 Or is there perhaps a RENAME DATABASE command (I saw RENAME TABLE)
so
 that I could rename the old one to get it out of the way but not toss
it?


 TIA  HAND

 mysql query,
 :-D
 - --
 David T-G  * There is too much animal courage in
 (play) [EMAIL PROTECTED] * society and not sufficient moral
courage.
 (work) [EMAIL PROTECTED]  -- Mary Baker Eddy, Science and
Health
 http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl
Npg!

 -BEGIN PGP SIGNATURE-
 Version: GnuPG v1.0.7 (FreeBSD)

 iD8DBQE+DPsqGb7uCXufRwARAsp8AKC7BuVdyO7Dl5fkbvEM51o+i/BAEgCeI5iI
 dI7HQb5oywLHuZIjLxXYZwY=
 =QTHL
 -END PGP SIGNATURE-

 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

style question: drop database in an install script

[David T-G]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi, all --

I'm working up the schema for my application and, as you can imagine,
starting over and reloading numerous times :-)  In fact, I intend for the
script to be an install script, run on a new installation of the software
to set things up.

The script starts out

  drop database if exist dbname ;
  create database dbname ; use dbname ;
  create table tablename
  (
...
  ) ;
  ...

to set things up and then continues

  insert into tablename (field,field,field)
( data , data , data ) ,
...
( data , data , data ) ;
  ...

to set up the predefined things (credit card types and so on).

To reload the database and try something new, all I have to do is

  mysql -udroot -p  script.sql

and what was there goes away and is rebuilt fresh.  That's mighty
convenient as I'm building up a table's design and trying it out.

Soo...  When you experienced folks write software that's meant to be
installed from a script or such, do you lead off with a 'drop database'
command, or is that just too dangerous to put in a script and you make
your users clean up any old installation by hand?  This will have to be
run by a root user, so we can figure that this user might have read the
docs and know that he's going to start [over] from scratch, but then,
again, users are users :-)

Meanwhile, is there if/then/else functionailty in SQL so that I can say

  if exist dbname exit some error ;
  create database dbname ;
  ...

and not try to create and populate a database on top of an existing one?
Or is there perhaps a RENAME DATABASE command (I saw RENAME TABLE) so
that I could rename the old one to get it out of the way but not toss it?


TIA  HAND

mysql query,
:-D
- -- 
David T-G  * There is too much animal courage in 
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, Science and Health
http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (FreeBSD)

iD8DBQE+DPsqGb7uCXufRwARAsp8AKC7BuVdyO7Dl5fkbvEM51o+i/BAEgCeI5iI
dI7HQb5oywLHuZIjLxXYZwY=
=QTHL
-END PGP SIGNATURE-

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Bug in drop database mysql 4.0.1-2

[Scott Olson]

I posted a couple of days ago on what appears to be a bug in 
drop database of mysql-max-4.0.1-2.  Here is a recipe to
see if others are seeing the same problem.

This is on Red Hat Linux 7.2 and is extremely repeatable
nor is it limited to a single linux box (ie. not likely
hardware problem).

When drop database is called in a loop it will eventually cause
a hang and most commands will fail thereafter. (except stuff
like show variables, show processlist)

show processlist shows drop database sitting around waiting for
something that never comes :)  The server is pretty much dead
in the water at this point since nothing else is processed
as far as selects, updates etc.

While composing this email I've discovered that adding
a flush tables command immediately before the drop database
seems to solve the problem.  While that is a workaround it
would be nice to solve the underlying problem :)

I've included test.sql and rundeath.csh to help replicate
the problem.  


contents of rundeath.csh

#!/bin/csh
while 1
date
mysql -uroot  test.sql
end

contents of test.sql

drop database if exists death;

create database death;

use death;

create table bogus (
  crap int default '' not null
);


-- 
Scott Olson
Senior Systems Analyst
Pason Systems Corp.
Phone: (403) 301-3418
e-mail: [EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Unable to drop database

[Scott Olson]

I have an application database that I'm unable to drop after a period of running 
several applications against that are using the MySQL++ api.

Wondering if anyone has seen this, if it's a known bug and/or if there is a fix.

mysqladmin -uroot -p drop db and rm -rf /var/lib/mysql/dbname do succesfully 
remove the database it's just the mysql client has the problem.  drop database returns 
ok but the database is still there (both show databases and ls -al 
/var/lib/mysql/dbname say so).  

Logging in to mysql with mysql -uroot -p to do the drop so it shouldn't be a user 
permission issue.  The directory permissions and file permissions within seem fine too
-rw---1 mysqlmysql dirname

The database is largely unpopulated (10Mb) with about 50 tables (most of which are 
MyISAM with 2 or 3 HEAP tables).   

Linux redhat 7.2

rpms 

MySQL-Max-4.0.1-2
MySQL-4.0.1-2
MySQL-shared-4.0.1-2
MySQL-devel-4.0.1-2
MySQL-client-4.0.1-2



-- 
Scott Olson

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: DROP DATABASE executes but fails to remove database!

[Victoria Reznichenko]

Gartside,
Friday, May 10, 2002, 3:45:07 AM, you wrote:

G Windows 2K MySQL V ? (recent). Sorry, I'm not at my own machine at present,
G can't check actual version number.

G Installed, running and everything is ok, except I created a database called
G 'xyz' as a test then decided to delete it.
G I used the command: DROP DATABASE 'xzy';which returns a message that
G the command executed successfully, affecting however many rows were in the
G database at the time.
G SHOW DATABASES; shows that the database is still there.

G I then re-tried using command:   DROP DATABASE if exists 'xyz'; with
G the same result (no error messages).

Please, check you database directory if there are any other files than
MySQL table files (*.MYD. *.MYI, .frm etc.). MySQL doesn't remove
database if there are any non-MySQL files in the dir.

G I then tried using mysqladmin to do the job, with the same result.

G I then used:DELETE
G FROM mysql.db
G WHERE Db = xyz;
G which also executes successfully.

:) You removed _users_ with _privileges_ on 'xyz' database, it's not
related with database removal.

G I test by using:SELECT db
G FROM mysql.Db;
G which reveals that the database is NOT there.

Sure, you removed all matched  rows from table 'db' :)

G However! SHOW DATABASES; still
G shows that the database is there!

G I have also tried right click drop database in the GUI MySQL management
G window. It fails to remove it.

G I have stopped and re-started the service and the computer several times.
G DROP DATABASE works fine for other databases I create and remove, just not
G this one.

Please, check your database directory! You have posted your letter 3
times and Heikki already asked you to check the dir.

G Is this a curly one? Any help appreciated.
G Andrew




-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

DROP DATABASE executes but fails to remove database!

[Gartside, Andrew]

Windows 2K MySQL V ? (recent). Sorry, I'm not at my own machine at present,
can't check actual version number.

Installed, running and everything is ok, except I created a database called
'xyz' as a test then decided to delete it.
I used the command: DROP DATABASE 'xzy';which returns a message that
the command executed successfully, affecting however many rows were in the
database at the time. 
SHOW DATABASES; shows that the database is still there.

I then re-tried using command:   DROP DATABASE if exists 'xyz'; with
the same result (no error messages).

I then tried using mysqladmin to do the job, with the same result.

I then used:DELETE
FROM mysql.db
WHERE Db = xyz;
which also executes successfully.

I test by using:SELECT db
FROM mysql.Db;
which reveals that the database is NOT there. However! SHOW DATABASES; still
shows that the database is there!

I have also tried right click drop database in the GUI MySQL management
window. It fails to remove it.

I have stopped and re-started the service and the computer several times.
DROP DATABASE works fine for other databases I create and remove, just not
this one.

Is this a curly one? Any help appreciated.
Andrew



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Couldn't Drop database

[jialiang]

After I've dropped the database using mysqladmin, the 
following  message appeared:

%mysqladmin drop databasename
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 databasename database [y/N] Y
Database databasename dropped

However when I wanted to create a new database with the same 
name, it said that the database still exist

%mysqladmin create MRP
mysqladmin: CREATE DATABASE failed; error: 'Can't create 
database 'databasename'. Database exists'


Have I did something wrong?

Best Regards,
Dennis

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Couldn't Drop database

[Van]

[EMAIL PROTECTED] wrote:
 
 After I've dropped the database using mysqladmin, the
 following  message appeared:
 
 %mysqladmin drop databasename
 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 databasename database [y/N] Y
 Database databasename dropped
 
 However when I wanted to create a new database with the same
 name, it said that the database still exist
 
 %mysqladmin create MRP
 mysqladmin: CREATE DATABASE failed; error: 'Can't create
 database 'databasename'. Database exists'
 
 
 Have I did something wrong?
 
 Best Regards,
 Dennis
 

Dennis:

Are there files in the database directory?  Remove them.

In the directory that holds your dbs:
rm -Rf databasename

Van

-- 
=
Linux rocks!!!   http://www.dedserius.com/
=

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: How to drop table from InnoDB after drop database from MySQL?

[Heikki Tuuri]

Dave,

DROP DATABASE for InnoDB type tables is in my
TODO list for August, and I am determined that
the September distribution will have it implemented.

But meanwhile, if you accidentally lose the .frm file
of your table, but the table still exists inside InnoDB,
you can use the following trick to drop it from the
InnoDB tablespace:

Create an InnoDB table with the same name in
another MySQL database (= directory). Then copy
the .frm file from that directory to your original
directory. The .frm file will make MySQL think
that your table exists, and DROP TABLE succeeds
then.

Below I have used this method to drop an orphaned
InnoDB table bbb from database test.

Regards,

Heikki
http://www.innodb.com

heikki@donna:~/mysqlt/client  mysql test2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 3.23.41

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

mysql create table bbb (a int) type = innodb;
Query OK, 0 rows affected (0.13 sec)

mysql exit
Bye
heikki@donna:~/mysqlt/client  cd
heikki@donna:~  cd data
heikki@donna:~/data  cd test2
heikki@donna:~/data/test2  cp bbb.frm ../test
heikki@donna:~/data/test2  cd
heikki@donna:~  cd mysqlt
heikki@donna:~/mysqlt  cd client
heikki@donna:~/mysqlt/client  mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 3.23.41

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

mysql drop table bbb;
Query OK, 0 rows affected (0.16 sec)

mysql create table bbb (a int not null, b int, primary key (a), index (b))
type
 = innodb;
Query OK, 0 rows affected (0.16 sec)

mysql insert into bbb values (3, 7);
Query OK, 1 row affected (0.02 sec)

mysql select * from bbb;
+---+--+
| a | b|
+---+--+
| 3 |7 |
+---+--+
1 row in set (0.01 sec)

mysql

Copied message:

I drop the database from MySQL without drop the individual InnoDB
table within it, so the tables still in the InnoDB datafile.  Is there
a way to delete these table from the InnoDB?

Thanks,
Dave_Stockton




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

How to recover from accidental 'drop database mysql'

[Anonymous Individual]

Greetings:

I accidently dropped (my thunderous stupidity!!!) our mysql 
database. There were a few usernames and other databases.

I expected hell to break loose (it may still happen!), but
strangely enough, I noticed that I was still able to login,
probably because mysql server caches usernames, passwords, etc ??

I quickly backed up the remaining databases using mysqldump.

I did a make install to update the database and everything still
seems ok.

Now, anyone know if there is any way to create or repair my existing
mysql database with the information that is currently cached in
the server ? 

For example, if I do "show databases", I see all the databases that
should exist, but when I do a "select * from mysql.db", I only see
the "test" database!.

Any ideas will be gratefully appreciated and accepted !






--== Sent via Deja.com ==--
http://www.deja.com/



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: How to recover from accidental 'drop database mysql'

[Quentin Bennett]

Hi,

Its called your backup tape.

Quentin

-Original Message-
From: Anonymous Individual [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, 3 April 2001 4:16 p.m.
To: [EMAIL PROTECTED]
Subject: How to recover from accidental 'drop database mysql'


Greetings:

I accidently dropped (my thunderous stupidity!!!) our mysql 
database. There were a few usernames and other databases.

I expected hell to break loose (it may still happen!), but
strangely enough, I noticed that I was still able to login,
probably because mysql server caches usernames, passwords, etc ??

I quickly backed up the remaining databases using mysqldump.

I did a make install to update the database and everything still
seems ok.

Now, anyone know if there is any way to create or repair my existing
mysql database with the information that is currently cached in
the server ? 

For example, if I do "show databases", I see all the databases that
should exist, but when I do a "select * from mysql.db", I only see
the "test" database!.

Any ideas will be gratefully appreciated and accepted !






--== Sent via Deja.com ==--
http://www.deja.com/



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

The information contained in this email is privileged and confidential
and intended for the addressee only. If you are not the intended 
recipient, you are asked to respect that confidentiality and not 
disclose, copy or make use of its contents. If received in error 
you are asked to destroy this email and contact the sender immediately. 
Your assistance is appreciated.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: How to recover from accidental 'drop database mysql'

[Mohamad Ilhami]

On Mon, 2 Apr 2001, Anonymous Individual wrote:

 Greetings:
 
 I accidently dropped (my thunderous stupidity!!!) our mysql 
 database. There were a few usernames and other databases.
 
 I expected hell to break loose (it may still happen!), but
 strangely enough, I noticed that I was still able to login,
 probably because mysql server caches usernames, passwords, etc ??
 
 I quickly backed up the remaining databases using mysqldump.
 
 I did a make install to update the database and everything still
 seems ok.
 
 Now, anyone know if there is any way to create or repair my existing
 mysql database with the information that is currently cached in
 the server ? 
 
 For example, if I do "show databases", I see all the databases that
 should exist, but when I do a "select * from mysql.db", I only see
 the "test" database!.

It sounds like you did not loose your data at all, only mysql. Database
mysql only contains user privileges. so it's
okay. you can grant new user to your database. Not a big problem.

 
 Any ideas will be gratefully appreciated and accepted !
 
 
 
 
 
 
 --== Sent via Deja.com ==--
 http://www.deja.com/
 
 
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail 
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Drop Database

[David Lidström]

 Hi!

 I was testing some queries and now I've created a database 
 named "#Muffin", and I cannot drop it!

 I've tried the following:
 DROP DATABASE #Muffin
 DROP DATABASE '#Muffin'
 DROP DATABASE "#Muffin"

 But it doesn't work!

Please help!


 / David



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Drop Database

[Website4S]

David,

Firstly are you sure it`s a database you have created and not a table?

Either way try the following

DROP DATABASE #Muffin;
or
DROP TABLE #Muffin;

HTH
Ade

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Drop Database

[David Lidstrom]

Hi!

Yes, it is a database.

I recieve these errors, regardless if I use the semi-colon:

  Query: DROP DATABASE #Muffin
  Returns: ERROR 1064: You have an error in your SQL syntax near ''

  Query: DROP DATABASE '#Muffin'
  Returns: ERROR 1064: You have an error in your SQL syntax near ''#Muffin''

I assume the # character is the bandit
 I can also mention it's mysql-version 3.23.23-beta

Regards David

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: den 25 januari 2001 11:45
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Drop Database


David,

Firstly are you sure it`s a database you have created and not a table?

Either way try the following

DROP DATABASE #Muffin;
or
DROP TABLE #Muffin;

HTH
Ade


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Drop Database

[David Lidström]

Yes I've tried that, but it didn't work either...
  But now I recieved a "Database Muffin doesn't exist"-error instead!

/d


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
Mikel King
Sent: den 25 januari 2001 09:14
To: David Lidstrm
Cc: Mysql@Lists. Mysql. Com
Subject: Re: Drop Database


Hi David,

Have you tried DROP DATABASE "\#Muffin" ;   ?

Cheers,
Mikel

David Lidstrm wrote:

  Hi!

  I was testing some queries and now I've created a database
  named "#Muffin", and I cannot drop it!

  I've tried the following:
  DROP DATABASE #Muffin
  DROP DATABASE '#Muffin'
  DROP DATABASE "#Muffin"

  But it doesn't work!

 Please help!

  / David

 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Drop Database

[Kent Hoover]

Outside of MySQL, use a native OS command to rename or remove the
directory named '#Muffin' . If you rename it 'foobar', you should be
able to DROP it with
MySQL.

Cheers,

Kent Hoover


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Drop Database

[David Lidstrom]

 Yes!

 This worked!
 I shut down mysqld, renamed the folder and then I could
 use a regular DROP query to get rid of the database!

 Thank you for the help, and all answers!

Regards David


-Original Message-
From: Kent Hoover [mailto:[EMAIL PROTECTED]]
Sent: den 25 januari 2001 14:55
To: [EMAIL PROTECTED]
Subject: RE: Drop Database


Outside of MySQL, use a native OS command to rename or remove the
directory named '#Muffin' . If you rename it 'foobar', you should be
able to DROP it with
MySQL.

Cheers,

Kent Hoover


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php