CODEWITHSUNDEEP
databasemysql
Robin Rodricks
Robin Rodricks

Reputation: 113996

Mysql dump - How to execute a long query with escaping?

I'm exporting a roundcube MySQL database using Mysqldump but sadly it fails when I use a long query. How can I escape this so it works?

mysqldump -t --skip-add-drop-table roundcube contacts --where="select * from contacts where user_id in (select user_id from users where username like '%mysite.com%')" > roundcube_contacts.sql

Running the above gives me this error, so how do I escape the query properly?

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE / /!50084 SQL_NO_FCACHE */ * FROM contacts WHERE select * from contacts where user_id in (select user_id from users where username like '%mysite.com%')': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from contacts where user_id in (select user_id from users where usernam' at line 1 (1064)

Upvotes: 0

Views: 2565

Answers (1)

spencer7593
spencer7593

Reputation: 108410

The mysqldump --where option specifies the text to be included in the WHERE clause.

You've got a whole statement there, and that generates a syntax error,

... WHERE SELECT ... 
          ^

mysqldump is already writing the SELECT * FROM roundcube.contacts. The --where option is telling mysqldump to include a WHERE keyword, followed by the text you supply, as part of the statement it generates.

You want something more along the lines of:

--where="user_id in (select user_id from users where username like '%mysite.com%')"

so the generated statement would be...

... WHERE user_id in ...
          ^

Also, the database name and table name should follow the options...

mysqldump -t --skip-add-drop-table --where="1=1" roundcube contacts \
  > roundcube_contacts.sql

Upvotes: 3

Related Questions