List:General Discussion« Previous MessageNext Message »
From:McKeever Chris Date:June 3 2004 6:44pm
Subject:SOLVED Re: mysqldump WHERE clause
View as plain text  

On Thu, 03 Jun 2004 13:57 , Michael Stassen <Michael.Stassen@stripped> sent:

>
>
>McKeever Chris wrote:
>
>> MYSQL 4.0.13
>> 
>> I must be doing something wrong - I am trying to do a mysql dump and it
> > keeps yelling that tables dont exist whenever I put a where clause in..
>> it runs fine when I leave the where clause off - any help here?
>> 
>> mysqldump -u root -p hotswap EmailDatabase_k EmailMessage_k
>"--where=EmailDatabase_k.Account='kvanoni' and
>> EmailDatabase_k.id=EmailMessage_k.id" > kvanoni.db
>> 
>> if I make it just a single clause, it then says that table doesnt exist etc...
>> 
>> as I said, I take out the where and it works...thanks
>> 
>> -------------------------------------------
>> Chris McKeever
>
>I expect mysqldump processes your command line one table at a time and does 
>something like
>
>   SELECT * FROM db.table;
>
>to get the row data.  Adding --where adds a WHERE clause to that.  The 
>problem is that you cannot refer to a table in the WHERE clause that wasn't 
>mentioned in the FROM clause, and there's only the one table in there.
>
>This is because mysqldump is designed to make table backups.  It is not 
>designed to run arbitrary queries.  In other words, I think mysqldump is not 
>designed to do what you are trying to do.  Instead, you probably need to 
>look into
>
>   SELECT ... INTO OUTFILE
>
>http://dev.mysql.com/doc/mysql/en/SELECT.html> or, if you're trying to make 
>a new table with a subset of the rows from the old table,
>
>   CREATE ... SELECT
>
>http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html>.


Michael - I owe you a big wet sloppy kiss - that worked perfectly - created the new table,
dropped the old, swapped the new one in and ran 
the dump and then imported what I needed back to the production DB.......

>
>If you really want a file with SQL statements, I suppose you could use 
>CREATE...SELECT to make the subset table, then use mysqldump without a where 
>clause on that, and finally DROP the subset table.
>
>Michael
>
>
>



---- Prudential Preferred Properties   www.prupref.com
Thread
Tuning MySQL 4.0.20 for large full table scansDossy3 Jun
  • Re: Tuning MySQL 4.0.20 for large full table scansDan Nelson3 Jun
    • mysqldump WHERE clauseMcKeever Chris3 Jun
      • Re: mysqldump WHERE clauseMcKeever Chris3 Jun
      • Re: mysqldump WHERE clausegerald_clark3 Jun
        • Re: mysqldump WHERE clauseMcKeever Chris3 Jun
          • Re: mysqldump WHERE clauseMcKeever Chris3 Jun
      • Re: mysqldump WHERE clauseMichael Stassen3 Jun
        • SOLVED Re: mysqldump WHERE clauseMcKeever Chris3 Jun
    • Re: Tuning MySQL 4.0.20 for large full table scansDossy3 Jun
      • Re: Tuning MySQL 4.0.20 for large full table scansDan Nelson3 Jun
  • Re: Tuning MySQL 4.0.20 for large full table scansMatt W3 Jun