Backup/Restore strategy when using Catalog & Taxonomy

[Alan_uk]

My fatal error has got me thinking about backup/restore, particularly in a online production environment [I'm running on a local server for evaluation].

The Catalog guide recommends user's catalog tables being prefixed with cat_ otherwise the a TL system upgrade will wipe the data. I took a backup using the backupDB extension but on restore I got:

Fatal error: Uncaught exception Exception with message Query error: Duplicate entry '7' for key 1 (INSERT INTO `cat_fobmcp_observations ....

I deleted the TL database and recreated an empty schema and tried again:

Fatal error: Uncaught exception Exception with message Query error: Table 'typo.cat_fobmcp_observations' doesn't exist (INSERT INTO `cat_fobmcp_observations` ............

So some other means will have to be used (e.g. MySQL Admin). But it looks like the Catalog extension has its own tl_ prefixed tables that appear to refer to the cat_ tables. So any backup of the _cat tables has to be synchronised with a backup of the tl_ tables. And similarly on restore. I think this mix backup/restore is prone to error and my bet is that it is best to use a single backup/restore which will have to be MySQL Admin [my ISP only allows this].

However, should there be a system upgrade then after the upgrade I need to:
a) restore my cat_tables - I could edit the sql file from a pre-upgrade backup and just keep the cat_ tables

Will I need to restore

• b) the tl_ tables that are associated with catalog and have references to my cat_ tables - which ones?
  [/*:m:2hi4khx5]
• c) the tl_ tables that are associated with taxonomy - which ones?[/*:m:2hi4khx5]

or will the content of these be preserved by the system upgrade?

Have I understood this correctly? Is there a better strategy?

[thyon]

You can still use tl_xxx tables, but then you have to ensure you never accidentally delete them.

Backup of your data really falls outside the scope of any extensions function, because the backup you're using is another extension, so best to modify that or contact the developer to consider catalog tables.

[Alan_uk]

thanks thyon

Backup of your data really falls outside the scope of any extensions function, because the backup you're using is another extension, so best to modify that or contact the developer to consider catalog tables.

Sort of agree and sort of disagree! - pointless having functionality if it is precarious to ensure it's integrity.

I will contact the backup extension mod or see if I can hack the code....

[valentin]

did you have any success? i´m running a site with catalog on a very slow server and want to transfer it to a faster host. but i can´t figure out how to do that. always get errors when trying to import the mysql dump.

[thyon]

try to export the Entire Site as a TEMPLATE instead of a dump. Then install TL, and run install, and import the template. Not only will it copy the data, but also all the configuration, etc. and it tells you which extensions you also need to install...

[Alan_uk]

try to export the Entire Site as a TEMPLATE instead of a dump

I've just tried "Save the website configuration and create a website-template for InstallTool" and only the the tl_ tables are backed up and only 25 of them and only as INSERT - seems to skip the empty tables

I tried the "SQL Backup of TYPOlight database as download" and that did backup 1 cat_ and 45 tl_ tables as CREATE

So that suggests for normal backups use the "SQL Backup of TYPOlight ...." To restore the SQL file will need a DROP for each table

For upgrades it suggests first using the "SQL Backup of TYPOlight .." then run the upgrade (which will destroy the cat_ tables) and then edit the ""SQL Backup of TYPOlight " to have only the cat_ tables and then to run those, using MySQLAdmin or similar.

Note: None of the above tested so use at your own risk. Currently I'm on a local server and using disk image backup