List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:June 3 2004 5:57pm
Subject:Re: mysqldump WHERE clause
View as plain text  

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>.

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


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