Re: Error from mysqldump
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]
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
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
- 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
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]
