WARNING to those relying on pleskbackup for MySQL <= 4.1.20 RHEL4 or <= 5.0.22 RHEL5!

[jaredfine]

After a days worth of frustrations I finally figured out why my backup didn't fully restore my databases correctly. Namely my MySQL databases were all missing their auto_increment settings. At first I thought this was a Plesk problem though I soon found out that its only partially Plesk's fault. When pleskbackup (current and presumably earlier versions) runs it dumps the MySQL user databases with the following command:

/usr/bin/mysqldump -h 'localhost' -u 'admin' -p'****' -P '3306' --quick --quote-names --add-drop-table --compatible=mysql323 --default-character-set=utf8 --set-charset 'db_name'

The primary problem with this command is due to the way MySQL versions <= 4.1.20 RHEL4 or <= 5.0.22 RHEL5 handle the --compatible=mysql323. Apparently this was a heavily debated bug (which at one time was actually considered a feature!) in which the compatible flag did not output auto_increment definitions in the table creation sql although those versions of MySQL always supported that feature. Please see the URL below for full details:

http://bugs.mysql.com/bug.php?id=14515

I had never experienced this problem because I only recently upgraded to RHEL4 and all my backups from RHEL3 came over fine. It wasn't until I tried a full backup and restore that I ran into this. I consider this a fairly serious problem that many people should be aware of due to the popularity of RHEL and frankly I'm upset that SWSoft hasn't done a better job documenting this problem even if it isn't directly their fault.

My hope is that SWSoft can add additional features to the backup/restore utilities so that a user can override these settings. Frankly I'd be fine if the backup files required they be restored on a current or newer version of Plesk, MySQL, Postgresql, OS, etc if it meant more reliable backups. In the meantime I will create my own separate db backups and will tar /var just to be double safe. I encourage those of you like myself running on effected versions to do similar to fully protect your data.

 

[breun]

Have you filed a bug report with SWsoft?

 

[jaredfine]

No, though I would gladly, but I can't seem to find a place to do so.

 

[breun]

I forgot were I found the address (somewhere buried behind the support form I believe), but you can send reports to [email protected].

 

[jaredfine]

Sent. Ya I was looking for something like a bugtrac and couldn't find anything, thanks for the address.

 

[rmogstad]

Do you know which file this option is set in? This the e-mail I got back from support about it

As a workaround you may comment out this option invocation in PleskX.pl and restart the migration, i.e.:

# /usr/local/psa/PMM/PleskX.pl:

# if ($mysqlversion =~ /Distrib (\d+)\.(\d+)\./) {
# if ($1 > 4 or ($1 == 4 and $2 > 0)) {
# $dumpopts .= " --compatible=mysql323";
# }
# }
#

Unfortunately, the file doesn't exist in the location they give, and the PleskX.pl file in the 'agents' directory below PMM doesn't have contain those lines.

Any idea how I could fix this before I migrate the rest of my sites? Having to manually dump/upload the databases myself kind of sucks....

 

[jaredfine]

I did a...

grep -ri 'mysql323' /usr/local/psa/

...and found the following...

/usr/local/psa/PMM/agents/shared/Db/MysqlShellBackend.pm: $dumpoptions .= " --compatible=mysql323 --default-character-set=utf8 --set-charset" if $self->_mysql41OrNewer();

I suspect removing --compatible=mysql323 from the $dumpoptions variable may do the trick, but I haven't had a chance try it out. I'll probably give it a try tomorrow.

I'll let you know.

 

[rmogstad]

I am doing a migration tonight, so I will give it a shot and let you know.

 

[rmogstad]

After removing that parameter, it seems that the migration worked perfectly (at least as far as MySQL goes.... )

 

[jaredfine]

Great news. I think I may do the same, or at the very least alter how their mysql version check function works. If I do update the code I'll release it here, though I hope SWSoft does it and releases an update.

Though it still doesn't hurt to keep a tar of the data files around or rsync, just in case there is something else that pops up with the backup script.

 

[rmogstad]

Is there a way to do an entire server backup via the Plesk interface, or do you have to do it via command line?

I would like to keep a backup of my old server before I shut off the service....

 

[breun]

pleskbackup is a command line only tool. Check the backup/restore docs.

 

[jaredfine]

Originally posted by rmogstad
Is there a way to do an entire server backup via the Plesk interface, or do you have to do it via command line?

I would like to keep a backup of my old server before I shut off the service....

Typically the best way is to use the command line with the all switch, somthing like this...

/usr/local/psa/bin/pleskbackup -v all ./plesk_all.psabu

Add more v's ie '-vv' for more verbosity and add a '--skip-logs' right after the 'all' if you don't care about logs and would rather save the space.

 

[rmogstad]

Excellent, thanks for the info.

 

[breun]

I suggest you just read the backup/restore docs, it's all in there.

 

[rmogstad]

Will do.

 

[rmogstad]

I know this is a bit off topic for the thread, but is there a reason that backup/migration fails anytime there is an alias for a domain?

 

[thewolf]

Hi,

Can anyone explain what problems missing auto_increment definitions would cause after the restore?

Thanks.

 

[rmogstad]

Originally posted by thewolf
Hi,

Can anyone explain what problems missing auto_increment definitions would cause after the restore?

Thanks.

Any tables that are supposed to auto_increment their key will start over at 1. When it tries to insert a value with 1, it will fail because it was a duplicate entry. Essentially, you won't be able to insert data....

 

[timtrott]

It may be "in the documentation" but it's somewhat of a hidden landmine because nobody knows about it. In my case this little issue was VERY expensive.

I have been running psadumps and restores for a decade and I had no way of knowing things had suddenly changed, and everybody who SHOULD have known about it ("Support" techs at 2 different datacenters) didn't and still don't.

I worked with a number of MySQL programmers in solving the problem and none of them were aware of the problem either. Everyone I encountered while dealing with the problem proclaimed firmly that it didn't exist.