[mythtv-users] Database Backup file not restoring

[Michael T. Dean]

On 07/19/2011 01:28 AM, mlt wrote:
>> On 07/18/2011 03:31 AM, mlt wrote:
>> > I have just reinstalled mythtv and am having trouble restoring my
>> > database backup - I am using mythbuntu 11.04.
>> >
>> > When I run restore I get the following message:
>> >
>> > ERROR 1064 (42000) at line 2794: 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
>> > '308:64,9),(1002,'2010-12-28 08:33:00',15726,(10402,'910-12-28
>> > 08:33:00',12066,32' at line 1
>> >
>> > ERROR: Cannot write to mysql, stopped at ./mythconverg_restore.pl
>> > line 1720, <BACKUP> line 2797.
>> >
>> >
>> > I have tried to do a partial restore and get the same message.
>> >
>> > When I try to open the file I get this message:
>> >
>> > gzip: mythconverg-1264-20110716142445.sql.gz: invalid compressed
>> > data--crc error
>> >
>> > gzip: mythconverg-1264-20110716142445.sql.gz: invalid compressed
>> > data--length error
>> >
>> >
>> > Is there anything that I can do with this or is the backup file
>> > corrupted in a way beyond recall -mysql is not my thing...
>
> >Looks like your file got truncated somehow. Does gzip -d leave an
> >uncompressed "partial" file?
> >
> >gzip -d mythconverg-1264-20110716142445.sql.gz
> >
> >Do you have other backups or even the originaly mysql binary data files?
>
> >Mike
>
> Thanks for the reply.
>
> I was able to unzip the archive using ark in KDE, and their seem to be 
> a number of errors in the syntax to do with recordings.

What do you mean "errors in the syntax to do with recordings"?  
mysqldump should know how to write proper SQL...

Did you create the backup using the backup script?  ( 
http://www.mythtv.org/wiki/Database_Backup_and_Restore )  I'm guessing 
you did, based on the file name.

Are you restoring it using the restore script?  ( 
http://www.mythtv.org/wiki/Database_Backup_and_Restore#Database_Restore 
)  You should use the approach at 
http://www.mythtv.org/wiki/Database_Backup_and_Restore#Full_restore_of_a_specific_backup_file 
(specifically the --drop_database --create_database part).  Otherwise, 
you may be trying to write a backup of one schema version into a 
database of a different schema version, which can result in SQL errors.

Note that the restore script can restore a compressed or uncompressed 
backup.

> Unfortunately, this really was the backup as I trashed the previous 
> installation (apart from the recordings).
>
> Anyway of tidying up the syntax automatically?  As I say MySQL isn't 
> something I have any knowledge of...

Since there's no way I can imagine that mysqldump (as called by the 
backup script) would write invalid SQL, we should first figure out what 
caused the problem.  That may make it easier to figure out how to fix it 
without creating an invalid database schema or without corrupting 
database data.

The "invalid compressed data--crc error" and "invalid compressed 
data--length error" makes me think the file itself was corrupted (after 
creation of the data, but possibly during writing to disk).

If you'd like to send me a link (off list) to the backup file, I can 
take a look at it and see if I can make more sense of what happened and 
possibly figure out how much you can recover from it (or--with luck--why 
the restore is failing so we can recover all of it).

Mike