Backup / Restore MySql with mydump.

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Guest

Backup / Restore MySql with mydump.

Post by Guest » Tue 03 Jan 2006 10:41

I´m having problem with backup and restoring.
I think the problem comes from foreign keys.
The backup runs just fine, but when I want to restore the sql-file generated I get errors.

When I compare the result SQL-file made by mydump with mysql administrators backup I see the difference.
The following rows are not included in mydac´s backup file.

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

How can I force MyDump to skip the foreign key check, or to use the above code.
Cant find it in manual, Object inspector or code.
The only thing close is disable keys. But that´s only valid for inserts?

Plz help

/Alex

Guest

Post by Guest » Tue 03 Jan 2006 15:24

This is one of the tables that wont restore... Took it right from the myDump-script.

DROP TABLE IF EXISTS comp_report;
CREATE TABLE `comp_report` (
`comp_report_ID` int(10) unsigned NOT NULL auto_increment,
`comp_ID` int(10) unsigned NOT NULL default '0',
`report_ID` int(10) unsigned NOT NULL default '0',
`user_ID` int(10) unsigned default '0',
`Un_Nr_ID` int(10) unsigned NOT NULL default '0',
`product_rest` double default '0',
PRIMARY KEY (`comp_report_ID`),
KEY `FK_comp_report_1` (`comp_ID`),
KEY `FK_comp_report_2` (`report_ID`),
KEY `FK_comp_report_3` (`user_ID`),
KEY `FK_comp_report_4` (`Un_Nr_ID`),
CONSTRAINT `FK_comp_report_1` FOREIGN KEY (`comp_ID`) REFERENCES `compartment` (`comp_ID`),
CONSTRAINT `FK_comp_report_2` FOREIGN KEY (`report_ID`) REFERENCES `report` (`report_ID`),
CONSTRAINT `FK_comp_report_3` FOREIGN KEY (`user_ID`) REFERENCES `user` (`user_ID`),
CONSTRAINT `FK_comp_report_4` FOREIGN KEY (`Un_Nr_ID`) REFERENCES `un_nr` (`UN_Nr_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Guest

Post by Guest » Tue 03 Jan 2006 15:50

It seems like every table with more than one foreign key fails to restore with myDump.
Any ideas?
I have tried with two different databases.
My first post does not work...

So Ignore this:
---------------------------------------------------------------------------
When I compare the result SQL-file made by mydump with mysql administrators backup I see the difference.
The following rows are not included in mydac´s backup file.

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
--------------------------------------------------------------------------------

Guest

Post by Guest » Tue 03 Jan 2006 15:59

However.
If I backup the database in myDump, and then paste the script in myScript It still crashes, BUT.
If I paste this first:
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

And this last:
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

IT WORKS.

Is there anyway around this perhaps? Something less haxxy :wink:

Ikar
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Post by Ikar » Thu 05 Jan 2006 10:31

Currently MyDAC doesn't use complicated algorithms to analyze CREATE TABLE structure. Maybe we will add this functionality in next MyDAC version.

Post Reply