Problems getting MySqlDump to use sub-queries
I am having a problem using MySqlDump. Context: I am having to export some very specific records from our database We have a table called BIN which has, amongst others, an ID column and an ACCOUNT_ID column. When I use this mysqldump -u root -p --complete-insert --where=account_id='19444' -t otm bin /otm/reports/datadump/mediaowner_bin.txt it does properly create Insert statements matching the results that the where clause should return. However, we have another table called BIN_DATA which has ID and BIN_ID columns (the BIN_ID being a link to the BIN table mentioned above). If I fire off the following query from within MySql: select * from bin_data where bin_id in (select id from bin where account_id=19444); it works great, returning the rows I'd expect. However, where I am getting stuck is getting MySqlDump to handle this obviously more complex query statement, it having a sub-query. I have tried lots of variations on the following: mysqldump -u root -p --complete-insert --where=bin_id in (select id from bin where account_id=19444) -t otm bin_data /otm/reports/datadump/mediaowner_bin_data.txt Am I not wrapping the where portion of the command correctly (though I have tried every possible wrapping with single-quotes that I could come up with) OR are sub-queries simply not supported OR is there something else that I should know. Any help you can give me would be way appreciated. Henry Dall [EMAIL PROTECTED]
RE: Problems getting MySqlDump to use sub-queries
Henry, Maybe the shell is doing something funky with the parens? maybe try escaping them with the \ ?? Just a wild guess :) -Original Message- From: Henry Dall [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 15, 2006 12:06 PM To: mysql@lists.mysql.com Subject: Problems getting MySqlDump to use sub-queries I am having a problem using MySqlDump. Context: I am having to export some very specific records from our database We have a table called BIN which has, amongst others, an ID column and an ACCOUNT_ID column. When I use this mysqldump -u root -p --complete-insert --where=account_id='19444' -t otm bin /otm/reports/datadump/mediaowner_bin.txt it does properly create Insert statements matching the results that the where clause should return. However, we have another table called BIN_DATA which has ID and BIN_ID columns (the BIN_ID being a link to the BIN table mentioned above). If I fire off the following query from within MySql: select * from bin_data where bin_id in (select id from bin where account_id=19444); it works great, returning the rows I'd expect. However, where I am getting stuck is getting MySqlDump to handle this obviously more complex query statement, it having a sub-query. I have tried lots of variations on the following: mysqldump -u root -p --complete-insert --where=bin_id in (select id from bin where account_id=19444) -t otm bin_data /otm/reports/datadump/mediaowner_bin_data.txt Am I not wrapping the where portion of the command correctly (though I have tried every possible wrapping with single-quotes that I could come up with) OR are sub-queries simply not supported OR is there something else that I should know. Any help you can give me would be way appreciated. Henry Dall [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with mysqldump
See http://dev.mysql.com/doc/mysql/en/GRANT.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
Problems with mysqldump
Hello all, I have a remote server that has a harddrive almost full. Therefore I need to dump a huge table to my other host. OK, I've set the permissions so that I can log in to the server as root from my host. I've try to connect to it and it works. I have to do the dump to the host because the remote server hard drive has almost no more space. I've set the privileges so that I can log on from root from any host within my network like this: GRANT ALL ON db_mydb.* TO 'root'@'192.168.0.%' IDENTIFIED by 'algo'; flush privileges; Then, I try to run the following command to dump the database: mysqldump -h 192.168.0.2 -u root -palgo --tab=/tmp --fields-terminated-by=; db_parts tbl_config tbl_config.csv What I've got in return is: mysqldump: Got error: 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) when executing 'SELECT INTO OUTFILE' If I run this command in the server it works just fine (when I had space), the problem is that I cannot run it on the other host as I cannot run it in the server due to its lack of space to generate the outputfile. Any ideas to where I am doing it wrong? Thanks in advance, C.F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with mysqldump
At 16:03 -0600 10/16/04, C.F. Scheidecker Antunes wrote: Hello all, I have a remote server that has a harddrive almost full. Therefore I need to dump a huge table to my other host. OK, I've set the permissions so that I can log in to the server as root from my host. I've try to connect to it and it works. I have to do the dump to the host because the remote server hard drive has almost no more space. I've set the privileges so that I can log on from root from any host within my network like this: GRANT ALL ON db_mydb.* TO 'root'@'192.168.0.%' IDENTIFIED by 'algo'; flush privileges; Then, I try to run the following command to dump the database: mysqldump -h 192.168.0.2 -u root -palgo --tab=/tmp --fields-terminated-by=; db_parts tbl_config tbl_config.csv What I've got in return is: mysqldump: Got error: 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) when executing 'SELECT INTO OUTFILE' Apparently the account you're using doesn't have the FILE privilege, which is a global privilege. (Remember, GRANT ALL ON db_mydb.* grants database-level privileges, not global privileges.) If I run this command in the server it works just fine (when I had space), the problem is that I cannot run it on the other host as I cannot run it in the server due to its lack of space to generate the outputfile. Any ideas to where I am doing it wrong? Thanks in advance, C.F. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
