Fw: Error from mysqldump - problem solved

2006-01-21 Thread Rhino 
I've resolved my problem with mysqldump. I read the error message yet again 
and it suddenly dawned on me what the problem might be. I made a slight 
modification in my table name and, sure enough, the problem went away.


The problem lay in the fact that my table name was References. That's right, 
the same word that is a keyword in the Foreign Key clause, as in Foreign 
key (id) references tmp.foo(id) on delete restrict. Using References as a 
table name had initially caused me grief when creating the table and when 
defining foreign keys that used it as a primary table. I'd eventually gotten 
past all those problems by putting backtics around each use of References 
as a table name. It didn't occur to me until I finally reread the error 
message this morning that the table name was biting me again within the 
mysqldump command.


All I did was drop the References table then recreate the tables so that the 
former References was now called Reference and everything worked 
perfectly again. I can't believe I didn't see this right from the start. Oh 
well, live and learn


I just thought I should follow up so that anyone following this thread now 
or in the archives will know how it was resolved.


Rhino

- Original Message - 
From: Rhino [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]; gerald_clark 
[EMAIL PROTECTED]

Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 11:15 AM
Subject: Re: Error from mysqldump


Just as a followup to my own remarks, I've tried running my backup script 
with the new syntax that Gerald suggested. I was going to wait for the 
normal daily backup but I was eager to see if the new version would work 
better so I just ran it from the command line.


Unfortunately, it came back with the same error. The new syntax is still 
cleaner and I'm going to keep it but I'm back to square one in determining 
why the mysqldump of this one database is giving me trouble.


Does anyone have any ideas?

Rhino

- Original Message - 
From: Rhino [EMAIL PROTECTED]

To: gerald_clark [EMAIL PROTECTED]
Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 10:53 AM
Subject: Re: Error from mysqldump




- Original Message - 
From: gerald_clark [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 9:30 AM
Subject: Re: Error from mysqldump



Rhino wrote:

I have an automated backup script that has been running daily for a 
couple of years now. It has never given me trouble until the last two 
days. For the last two days, I have been getting this message when 
backing up my newest database:


/usr/bin/mysqldump: Got error: 1064: 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 'References READ /*!32311 LOCAL */' at 
line 1 when using LOCK TABLES


This is the relevant portion of my backup script:

for ONE_DBNAME in `echo show databases | mysql -s -u 
$USERID -p$PASSWORD`

do
  echo
  echo Backing up database $ONE_DBNAME;
  /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r 
${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


I would look here. This is a dangerous expansion. A space or ';' in any 
of these variables my generate unwanted commands.

Use quotes around the argument to -r.
Try.
 /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


or /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME}   ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql




Okay, fair enough, I've never claimed to be a bash expert ;-) I think 
your proposed change is an improvement: it is clearer and easier to read. 
I'll give this version a try for the next few days and see if it works 
any better.


But I'm still not sure why this version might solve my problem. Wouldn't 
an expansion issue cause problems for all of my databases, not just one? 
I'm trying to understand why only one database is affected and why only 
the newest one when the script has worked fine for many months with the 
older databases.



  echo  Deleting these old backups for this database...
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display 
old backups (if any)
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' 
';' #delete old backups (if any)

done


I'm at a loss to see why I'm getting this error for only one database 
when the exact same logic is applied for each of my databases and works 
fine for all the others.


I've tried doing the backup manually from the command line and found 
that I got the same error when I tried to backup the Maximal database 
that way; a manual backup of another database worked fine.


The only idea I have that seems vaguely plausible is that there is 
something internally

Re: Error from mysqldump

2006-01-20 Thread Gleb Paharenko 
Hello.

If you switch to the debug binary of the mysqldump, you will be able to
find the query which causes 1064 error. See:
  http://dev.mysql.com/doc/refman/5.0/en/debugging-client.html


Rhino wrote:
 Just as a followup to my own remarks, I've tried running my backup
 script with the new syntax that Gerald suggested. I was going to wait
 for the normal daily backup but I was eager to see if the new version
 would work better so I just ran it from the command line.
 
 Unfortunately, it came back with the same error. The new syntax is still
 cleaner and I'm going to keep it but I'm back to square one in
 determining why the mysqldump of this one database is giving me trouble.
 
 Does anyone have any ideas?
 
 Rhino
 

-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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]

Error from mysqldump

2006-01-19 Thread Rhino 
I have an automated backup script that has been running daily for a couple 
of years now. It has never given me trouble until the last two days. For the 
last two days, I have been getting this message when backing up my newest 
database:


/usr/bin/mysqldump: Got error: 1064: 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 'References READ /*!32311 LOCAL */' at line 1 when using 
LOCK TABLES


This is the relevant portion of my backup script:

for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD`
do
  echo
  echo Backing up database $ONE_DBNAME;
  /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql

  echo  Deleting these old backups for this database...
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display old 
backups (if any)
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' ';' 
#delete old backups (if any)

done


I'm at a loss to see why I'm getting this error for only one database when 
the exact same logic is applied for each of my databases and works fine for 
all the others.


I've tried doing the backup manually from the command line and found that I 
got the same error when I tried to backup the Maximal database that way; a 
manual backup of another database worked fine.


The only idea I have that seems vaguely plausible is that there is something 
internally wrong with my database but I'm darned if I know what the problem 
could be. When I do 'select *' against each of the five small tables in this 
database, each returns exactly the right data and there are no errors or 
warnings of any kind.


Can anyone suggest queries or commands that would reveal the status of my 
database and its tables to make sure something is not messed up?


Any suggestions on resolving this problem would be greatly appreciated.

---
Rhino



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 18/01/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Error from mysqldump

2006-01-19 Thread gerald_clark 

Rhino wrote:

I have an automated backup script that has been running daily for a 
couple of years now. It has never given me trouble until the last two 
days. For the last two days, I have been getting this message when 
backing up my newest database:


/usr/bin/mysqldump: Got error: 1064: 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 'References READ /*!32311 LOCAL */' 
at line 1 when using LOCK TABLES


This is the relevant portion of my backup script:

for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD`
do
  echo
  echo Backing up database $ONE_DBNAME;
  /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r 
${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


I would look here. This is a dangerous expansion. A space or ';' in any 
of these variables my generate unwanted commands.

Use quotes around the argument to -r.
Try.
 /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


or 
/usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME}   ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql



  echo  Deleting these old backups for this database...
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; 
#display old backups (if any)
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' 
';' #delete old backups (if any)

done


I'm at a loss to see why I'm getting this error for only one database 
when the exact same logic is applied for each of my databases and 
works fine for all the others.


I've tried doing the backup manually from the command line and found 
that I got the same error when I tried to backup the Maximal database 
that way; a manual backup of another database worked fine.


The only idea I have that seems vaguely plausible is that there is 
something internally wrong with my database but I'm darned if I know 
what the problem could be. When I do 'select *' against each of the 
five small tables in this database, each returns exactly the right 
data and there are no errors or warnings of any kind.


Can anyone suggest queries or commands that would reveal the status of 
my database and its tables to make sure something is not messed up?


Any suggestions on resolving this problem would be greatly appreciated.

---
Rhino






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Error from mysqldump

2006-01-19 Thread Rhino 


- Original Message - 
From: gerald_clark [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 9:30 AM
Subject: Re: Error from mysqldump



Rhino wrote:

I have an automated backup script that has been running daily for a 
couple of years now. It has never given me trouble until the last two 
days. For the last two days, I have been getting this message when 
backing up my newest database:


/usr/bin/mysqldump: Got error: 1064: 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 'References READ /*!32311 LOCAL */' at 
line 1 when using LOCK TABLES


This is the relevant portion of my backup script:

for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD`
do
  echo
  echo Backing up database $ONE_DBNAME;
  /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r 
${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


I would look here. This is a dangerous expansion. A space or ';' in any of 
these variables my generate unwanted commands.

Use quotes around the argument to -r.
Try.
 /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


or /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME}   ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql




Okay, fair enough, I've never claimed to be a bash expert ;-) I think your 
proposed change is an improvement: it is clearer and easier to read. I'll 
give this version a try for the next few days and see if it works any 
better.


But I'm still not sure why this version might solve my problem. Wouldn't an 
expansion issue cause problems for all of my databases, not just one? I'm 
trying to understand why only one database is affected and why only the 
newest one when the script has worked fine for many months with the older 
databases.



  echo  Deleting these old backups for this database...
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display 
old backups (if any)
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' ';' 
#delete old backups (if any)

done


I'm at a loss to see why I'm getting this error for only one database 
when the exact same logic is applied for each of my databases and works 
fine for all the others.


I've tried doing the backup manually from the command line and found that 
I got the same error when I tried to backup the Maximal database that 
way; a manual backup of another database worked fine.


The only idea I have that seems vaguely plausible is that there is 
something internally wrong with my database but I'm darned if I know what 
the problem could be. When I do 'select *' against each of the five small 
tables in this database, each returns exactly the right data and there 
are no errors or warnings of any kind.


Can anyone suggest queries or commands that would reveal the status of my 
database and its tables to make sure something is not messed up?


Any suggestions on resolving this problem would be greatly appreciated.

---
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 18/01/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Error from mysqldump

2006-01-19 Thread Rhino 
Just as a followup to my own remarks, I've tried running my backup script 
with the new syntax that Gerald suggested. I was going to wait for the 
normal daily backup but I was eager to see if the new version would work 
better so I just ran it from the command line.


Unfortunately, it came back with the same error. The new syntax is still 
cleaner and I'm going to keep it but I'm back to square one in determining 
why the mysqldump of this one database is giving me trouble.


Does anyone have any ideas?

Rhino

- Original Message - 
From: Rhino [EMAIL PROTECTED]

To: gerald_clark [EMAIL PROTECTED]
Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 10:53 AM
Subject: Re: Error from mysqldump




- Original Message - 
From: gerald_clark [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 9:30 AM
Subject: Re: Error from mysqldump



Rhino wrote:

I have an automated backup script that has been running daily for a 
couple of years now. It has never given me trouble until the last two 
days. For the last two days, I have been getting this message when 
backing up my newest database:


/usr/bin/mysqldump: Got error: 1064: 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 'References READ /*!32311 LOCAL */' at 
line 1 when using LOCK TABLES


This is the relevant portion of my backup script:

for ONE_DBNAME in `echo show databases | mysql -s -u 
$USERID -p$PASSWORD`

do
  echo
  echo Backing up database $ONE_DBNAME;
  /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r 
${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


I would look here. This is a dangerous expansion. A space or ';' in any 
of these variables my generate unwanted commands.

Use quotes around the argument to -r.
Try.
 /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


or /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME}   ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql




Okay, fair enough, I've never claimed to be a bash expert ;-) I think your 
proposed change is an improvement: it is clearer and easier to read. I'll 
give this version a try for the next few days and see if it works any 
better.


But I'm still not sure why this version might solve my problem. Wouldn't 
an expansion issue cause problems for all of my databases, not just one? 
I'm trying to understand why only one database is affected and why only 
the newest one when the script has worked fine for many months with the 
older databases.



  echo  Deleting these old backups for this database...
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display 
old backups (if any)
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' ';' 
#delete old backups (if any)

done


I'm at a loss to see why I'm getting this error for only one database 
when the exact same logic is applied for each of my databases and works 
fine for all the others.


I've tried doing the backup manually from the command line and found 
that I got the same error when I tried to backup the Maximal database 
that way; a manual backup of another database worked fine.


The only idea I have that seems vaguely plausible is that there is 
something internally wrong with my database but I'm darned if I know 
what the problem could be. When I do 'select *' against each of the five 
small tables in this database, each returns exactly the right data and 
there are no errors or warnings of any kind.


Can anyone suggest queries or commands that would reveal the status of 
my database and its tables to make sure something is not messed up?


Any suggestions on resolving this problem would be greatly appreciated.

---

Rhino


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 
18/01/2006



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 
18/01/2006







--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 18/01/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]