Description:
There are realy two errors:
- SQL-dump is not correct generated.
- Data for a not existig table are ignored because they could not been stored.
Here the description:
I wanted to upgrade from a Novell Server Version 6.50 with MySQL-Server 4.0.26 to a openSuSE 11.1 Linux Server with MySQL-Server 5.0.67. I use the MySQL Administrator Versin 1.2.17 (german) under Windows XP SP3
I made a Backup from the database on the Novell-Server MySQL and a Restore this file in the Linux-Server. Then I was surprised:
The data are transfered, but one table (schema) 'faplz' there is no data transfered. There was no message of a error, but it was the message, that all tables where written.
tablename engine rows datasize indexsize
Source (MySQL 4.0.26): faplz MyISAM 8600 260,2 kB 374 kB
destination (MySQL 5.0.67): faplz MyISAM 0 0B 1kB
The other 29 tables are ok. All other data is korrectly transferred.
Then I load the file from the source, made by MySQL Administrator Versin 1.2.17 (german) under Windows XP SP3 with a separate MySQL-Editor (HeidiSQL version 3.2) and then I cannot transfer the data to the Destination, because of "Table 'intratypo3.faPLZ' does not exist."
How to repeat:
The file content is:
...
CREATE DATABASE IF NOT EXISTS intratypo3;
USE intratypo3;
--
-- Definition of table `faplz`
--
DROP TABLE IF EXISTS `faplz`;
CREATE TABLE `faplz` ( <-- // See here! // -->
`ID` int(10) unsigned NOT NULL auto_increment,
`L` varchar(10) NOT NULL default '',
`PLZ` varchar(10) NOT NULL default '',
`Ort` varchar(255) NOT NULL default '',
`Teilort` varchar(255) NOT NULL default '',
`Vorwahl` varchar(15) NOT NULL default '',
`pwIDAktuell` int(10) unsigned NOT NULL default '0',
`DatumAktuell` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`ID`),
KEY `Teilort` (`Teilort`),
KEY `Ort` (`Ort`),
KEY `L` (`L`),
KEY `PLZ` (`PLZ`),
KEY `Vorwahl` (`Vorwahl`)
) TYPE=MyISAM COMMENT='Postleitzahlen Deutschland';
--
-- Dumping data for table `faplz`
--
/*!40000 ALTER TABLE `faplz` DISABLE KEYS */;
INSERT INTO `faPLZ` <-- // ERROR !!!! // -->
(`ID`,`L`,`PLZ`,`Ort`,`Teilort`,`Vorwahl`,`pwIDAktuell`,`DatumAktuell`)
VALUES
(1,'D','01067','Dresden','','',0,'0000-00-00 00:00:00'),
(2,'D','01069','Dresden','','',0,'0000-00-00 00:00:00'),
...);
The line
CREATE TABLE `faplz` (
doesn't korrespond with
INSERT INTO `faPLZ`
because 'faplz' is not equal with 'faPLZ'!!!!!
That means, that the table ist realy created, but there are no data for the table transfered. But the data of an other table couldn't transfered, because the needed table didn't exist. - Why here no errormessage from MySQL Administrator? - Why get I here the message "all transfered!"? - that's not right!
Suggested fix:
The error is in the creating of the source SQL-file.
There it must be checked, that the name of the insert-table is the same as the insert into-table name.
Here it was the writing of the names different in upper and lower cases!
Description: There are realy two errors: - SQL-dump is not correct generated. - Data for a not existig table are ignored because they could not been stored. Here the description: I wanted to upgrade from a Novell Server Version 6.50 with MySQL-Server 4.0.26 to a openSuSE 11.1 Linux Server with MySQL-Server 5.0.67. I use the MySQL Administrator Versin 1.2.17 (german) under Windows XP SP3 I made a Backup from the database on the Novell-Server MySQL and a Restore this file in the Linux-Server. Then I was surprised: The data are transfered, but one table (schema) 'faplz' there is no data transfered. There was no message of a error, but it was the message, that all tables where written. tablename engine rows datasize indexsize Source (MySQL 4.0.26): faplz MyISAM 8600 260,2 kB 374 kB destination (MySQL 5.0.67): faplz MyISAM 0 0B 1kB The other 29 tables are ok. All other data is korrectly transferred. Then I load the file from the source, made by MySQL Administrator Versin 1.2.17 (german) under Windows XP SP3 with a separate MySQL-Editor (HeidiSQL version 3.2) and then I cannot transfer the data to the Destination, because of "Table 'intratypo3.faPLZ' does not exist." How to repeat: The file content is: ... CREATE DATABASE IF NOT EXISTS intratypo3; USE intratypo3; -- -- Definition of table `faplz` -- DROP TABLE IF EXISTS `faplz`; CREATE TABLE `faplz` ( <-- // See here! // --> `ID` int(10) unsigned NOT NULL auto_increment, `L` varchar(10) NOT NULL default '', `PLZ` varchar(10) NOT NULL default '', `Ort` varchar(255) NOT NULL default '', `Teilort` varchar(255) NOT NULL default '', `Vorwahl` varchar(15) NOT NULL default '', `pwIDAktuell` int(10) unsigned NOT NULL default '0', `DatumAktuell` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`ID`), KEY `Teilort` (`Teilort`), KEY `Ort` (`Ort`), KEY `L` (`L`), KEY `PLZ` (`PLZ`), KEY `Vorwahl` (`Vorwahl`) ) TYPE=MyISAM COMMENT='Postleitzahlen Deutschland'; -- -- Dumping data for table `faplz` -- /*!40000 ALTER TABLE `faplz` DISABLE KEYS */; INSERT INTO `faPLZ` <-- // ERROR !!!! // --> (`ID`,`L`,`PLZ`,`Ort`,`Teilort`,`Vorwahl`,`pwIDAktuell`,`DatumAktuell`) VALUES (1,'D','01067','Dresden','','',0,'0000-00-00 00:00:00'), (2,'D','01069','Dresden','','',0,'0000-00-00 00:00:00'), ...); The line CREATE TABLE `faplz` ( doesn't korrespond with INSERT INTO `faPLZ` because 'faplz' is not equal with 'faPLZ'!!!!! That means, that the table ist realy created, but there are no data for the table transfered. But the data of an other table couldn't transfered, because the needed table didn't exist. - Why here no errormessage from MySQL Administrator? - Why get I here the message "all transfered!"? - that's not right! Suggested fix: The error is in the creating of the source SQL-file. There it must be checked, that the name of the insert-table is the same as the insert into-table name. Here it was the writing of the names different in upper and lower cases!