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
Backup / Restore MySql with mydump.
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;
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;
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' */;
--------------------------------------------------------------------------------
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' */;
--------------------------------------------------------------------------------
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
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