mysqldump where condition?

ThaSaltyDawg · 11-10-2008, 01:22 PM

I am running a mysqldump for a table. I am setting a condition in the mysqldump based on a list retrieved from a select query.

I am running a php file using system().

The mysqldump is not working with this criteria. I get this error from the console

mysqldump: Error in field count for table: 'ProposedTo' ! Aborting.

The error comes back as "0" .

Now my select query has approximately 125 rows. I'm thinking that is the issue because I run the same mysqldump with fewer conditions in the where clause.

Is there a limit to how many conditions can be in a where clause or this is case where the mysqldump is timed out? I thought maybe it just timed out, but wouldn't it tell me if it timed out? Because when it does actually time out I get the error message telling me so!

Thanks

ThaSaltyDawg · 11-10-2008, 03:39 PM

Here's my code (partially)

$tstr=NULL;
while ($mysql_array = $db->fetch_array())
{
if($tstr==NULL)
{
$PreOperator="";
}
else
{
$PreOperator=" OR ";
}
$tstr = $tstr.$PreOperator."IndexNumber='".$mysql_array['IndexNumber']."'";
}
///// Create Where Clause
$WHERE = $tstr;

///// Create MySQL Dump (Replace Records Where IndexNumbers Were Updated
$MySQLDump_ProposedTo = "mysqldump -h myhost --user=myuser --password=mypass --no-create-info --opt --where=\"$WHERE\" myDB ProposedTo |sed -e \"s|INSERT INTO|REPLACE INTO|\" 2>&1 > proposedto-replace.sql;";
$error="";
system($MySQLDump_ProposedTo,$error);

Any Ideas?

chrism01 · 11-10-2008, 06:49 PM

1. please use code tags
2. you seem to be dumping the entire table, so why the where clause?
3. try printing out the final $WHERE var and see what it says.
4. personally, I wouldn't try to construct a cmd line all in one. I'd simplify it by doing the sed separately, after you've created the file. You might want to check the dump file is created first.

ThaSaltyDawg · 11-11-2008, 09:26 AM

Quote:

Originally Posted by chrism01

1. please use code tags
2. you seem to be dumping the entire table, so why the where clause?
3. try printing out the final $WHERE var and see what it says.
4. personally, I wouldn't try to construct a cmd line all in one. I'd simplify it by doing the sed separately, after you've created the file. You might want to check the dump file is created first.

I forgot about the code tags. I'll remember next time.
I am not dumping the entire table. The dump is a REPLACE insert only for the records where IndexNumber = $var.

I have done that already. Got this:

Code:

mysqldump -h myhost --user=myuser --password=mypass --no-create-info --opt --where="IndexNumber='50012754000000' OR IndexNumber='50013037000000' OR IndexNumber='50013030000000' OR IndexNumber='50013124000000' OR IndexNumber='50010651010000' OR IndexNumber='50013134000000' OR IndexNumber='50013097010000' OR IndexNumber='50013155000000' OR IndexNumber='50013156000000' OR IndexNumber='50000988020000' OR IndexNumber='50013157000000' OR IndexNumber='50013171000000' OR IndexNumber='50013169000000' OR IndexNumber='50008262040000' OR IndexNumber='50010968010200' OR IndexNumber='50013013000000' OR IndexNumber='50012269010000' OR IndexNumber='50012265010000' OR IndexNumber='50012925010000' OR IndexNumber='50013140000100' OR IndexNumber='50012994010000' OR IndexNumber='50013097030000' OR IndexNumber='50012630000100' OR IndexNumber='50013159000000' OR IndexNumber='50009794000001' OR IndexNumber='50013097020000' OR IndexNumber='50006328020001' OR IndexNumber='50013153010000' OR IndexNumber='50009668060000' OR IndexNumber='50011104010000' OR IndexNumber='50008521050000' OR IndexNumber='50012919000000' OR IndexNumber='50012939000000' OR IndexNumber='50013162000000' OR IndexNumber='50013165000000' OR IndexNumber='50012938000000' OR IndexNumber='50013106000000' OR IndexNumber='50013119000000' OR IndexNumber='50012616010000' OR IndexNumber='50012023030000' OR IndexNumber='50013149000000' OR IndexNumber='50007324020000' OR IndexNumber='50012705000002' OR IndexNumber='50011718000004' OR IndexNumber='50005495030001' OR IndexNumber='50013144000000' OR IndexNumber='50002722110000' OR IndexNumber='50009062020000' OR IndexNumber='50013181000000' OR IndexNumber='50009805010000' OR IndexNumber='50011772010000' OR IndexNumber='50013183000000' OR IndexNumber='50013182000000' OR IndexNumber='50000997011600' OR IndexNumber='50009796040100' OR IndexNumber='50010041160000' OR IndexNumber='50009614030000' OR IndexNumber='50010533000001' OR IndexNumber='50009614040000' OR IndexNumber='50008836020000' OR IndexNumber='50012850020000' OR IndexNumber='50008057000100' OR IndexNumber='50013121000000' OR IndexNumber='50011951020100' OR IndexNumber='50010250030000' OR IndexNumber='50013142000000' OR IndexNumber='50012960010000' OR IndexNumber='50012957000000' OR IndexNumber='50012070010000' OR IndexNumber='50013145000000' OR IndexNumber='50013145010000' OR IndexNumber='50012961000000' OR IndexNumber='50013047000000' OR IndexNumber='50005086040200' OR IndexNumber='50013175000000' OR IndexNumber='50002093070100' OR IndexNumber='50007776090000' OR IndexNumber='50013179000000' OR IndexNumber='50013173000000' OR IndexNumber='50013154000000' OR IndexNumber='50009795010400' OR IndexNumber='50009319010000' OR IndexNumber='50012850030000' OR IndexNumber='50013118000000' OR IndexNumber='50013122000000' OR IndexNumber='50004968010000' OR IndexNumber='50009302020000' OR IndexNumber='50013137000000' OR IndexNumber='50013136000000' OR IndexNumber='50013141000000' OR IndexNumber='50008637050000' OR IndexNumber='50011421010000' OR IndexNumber='50013146000000' OR IndexNumber='50013148000000' OR IndexNumber='50013152000000' OR IndexNumber='50011663020000' OR IndexNumber='50003777030000' OR IndexNumber='50003259060000' OR IndexNumber='50012967000000' OR IndexNumber='50003259030100' OR IndexNumber='50003146000001' OR IndexNumber='50012964000100' OR IndexNumber='50013184000000' OR IndexNumber='50012942000000' OR IndexNumber='50011639010000' OR IndexNumber='50013178000000' OR IndexNumber='50006418040001' OR IndexNumber='50012917000000' OR IndexNumber='50012030020000' OR IndexNumber='50012885010000' OR IndexNumber='50013158000000' OR IndexNumber='50013153000000' OR IndexNumber='50010423010000' OR IndexNumber='50003090020000' OR IndexNumber='50013185000000' OR IndexNumber='50012030030000' OR IndexNumber='50013186000000' OR IndexNumber='50013111010000' OR IndexNumber='50012260010000' OR IndexNumber='50013187000000' OR IndexNumber='50001371000001' OR IndexNumber='50012806010000' OR IndexNumber='50005086040300' OR IndexNumber='50013187010000' OR IndexNumber='50012948000000' OR IndexNumber='50013021020000' OR IndexNumber='50012793000001' OR IndexNumber='50010986040000' OR IndexNumber='50011451070000' OR IndexNumber='50003440110000' OR IndexNumber='50012951000000'" MyDB ProposedTo |sed -e "s|INSERT INTO|REPLACE INTO|" 2>&1 > proposedto-replace.sql;

This routine works because I use it for others. This one just happens to output many conditions whereas the others only output a couple. This is what prompts me ask the question "Is there a limit to how many where conditions you can have?"

How do I not construct a cmd line all in one and simplify it by doing the sed separately, after I've created the file. Its these areas that I lack experience.
Thanks

chrism01 · 11-11-2008, 07:25 PM

Re 4.

Code:

$MySQLDump_ProposedTo = "mysqldump -h myhost --user=myuser --password=mypass --no-create-info --opt --where=\"$WHERE\" myDB ProposedTo 2>&1 > proposedto-replace.sql;";
$error="";
system($MySQLDump_ProposedTo,$error);
# check the error code here. In fact, for dev purposes only, stop here and check file  manually
# then add the sed
$sed_cmd = "sed -i -e 's|INSERT INTO|REPLACE INTO|'  proposedto-replace.sql"
$error="";
system($sed_cmd,$error);
# check error var

not a PHP programmer, so you may have to tweak that, but you get the idea.