transferring data between servers

sfullman

Hi,

I have moved database files via the linux scp command from one server to another, i.e. all the files in the folder (which represent the data and indexes etc.)

The problem is, the server they came from runs mysql 5.0.45-community. The server they were sent to runs mysql 4.1.16 - and I get this error:

SQL Error
:
Query failed with the following: (Error #1033) Table './cpm074/mlsre_sitesearches' was created with a different version of MySQL and cannot be read(Query:
SELECT * FROM mlsre_sitesearches WHERE 1 ORDER BY Priority ASC, EditDate DESC)

Any suggestions on converting these tables? They're pretty large. phpMyAdmin access on the receiving server is dicey, and I don't want to do data imports if possible when the data is right there.

Thanks,
Samuel

nemonoman

It would be a better idea to do a mysqldump of the 5.x database, using the 4.0 compatible option
--compatible=mysql40, and then load the new 4.x db from that. An SCP of the db files is, as you have found , DOOMED TO FAIL.

Like:

mysqldump -uxxx -pxxx --compatible=mysql40 --opt -Q nameOfDatabse> dump.sql

sfullman

so, the file is pretty straightforward and it's 12Megs, but I can't even figure out what's going on. In mysql I enter:

load data infile 'dump.sql'

and rec. this error
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

so I cut dump.sql into a tiny piece to try to get in, SAME ERROR THOUGH. wHAT IS GOING ON!

sql code is below:

DROP TABLE IF EXISTS `addr_contacts`;
CREATE TABLE `addr_contacts` (
  `ID` int(7) unsigned NOT NULL auto_increment,
  `Version` varchar(10) NOT NULL default 'v00.00.00',
  `Owner` varchar(30) NOT NULL default 'cpm035',
  `CreateDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `Creator` varchar(30) NOT NULL default 'cpm035',
  `EditDate` timestamp NOT NULL,
  `Editor` varchar(30) NOT NULL default '',
  `Active` tinyint(1) unsigned NOT NULL default '1',
  `Category` varchar(35) NOT NULL default '',
  `FirstName` varchar(35) NOT NULL default '',
  `MiddleName` varchar(35) NOT NULL default '',
  `LastName` varchar(40) NOT NULL default '',
  `Title` varchar(50) NOT NULL default '',
  `Display` varchar(50) NOT NULL default '',
  `Nickname` varchar(30) NOT NULL default '',
  `Email` varchar(85) NOT NULL default '',
  `Email2` varchar(85) NOT NULL default '',
  `NewsletterOK` tinyint(1) unsigned NOT NULL default '1',
  `NewsletterFormat` varchar(4) NOT NULL default '',
  `NewsletterImageFormat` varchar(15) NOT NULL default '',
  `NewsletterFrequency` varchar(15) NOT NULL default '',
  `NewsletterInterest` text NOT NULL,
  `HomeAddress` text,
  `HomeCity` varchar(35) NOT NULL default '',
  `HomeState` char(3) NOT NULL default '',
  `HomeZip` varchar(10) NOT NULL default '',
  `HomeCountry` char(3) NOT NULL default '',
  `HomeDefault` smallint(1) unsigned NOT NULL default '0',
  `HomePhone` varchar(24) NOT NULL default '',
  `HomeFax` varchar(24) NOT NULL default '',
  `HomeMobile` varchar(24) NOT NULL default '',
  `HomeWebsite` varchar(255) NOT NULL default '',
  `Clients_ID` mediumint(6) NOT NULL default '0',
  `Company` varchar(75) NOT NULL default '',
  `BusAddress` text,
  `BusCity` varchar(35) NOT NULL default '',
  `BusState` char(3) NOT NULL default '',
  `BusZip` varchar(10) NOT NULL default '',
  `BusCountry` char(3) NOT NULL default '',
  `BusTitle` varchar(35) NOT NULL default '',
  `BusDepartment` varchar(45) NOT NULL default '',
  `BusOffice` varchar(40) NOT NULL default '',
  `BusPhone` varchar(24) NOT NULL default '',
  `BusFax` varchar(24) NOT NULL default '',
  `BusPager` varchar(24) NOT NULL default '',
  `BusWebsite` varchar(85) NOT NULL default '',
  `WholesaleAccess` tinyint(1) unsigned NOT NULL default '0',
  `WholesaleNumber` varchar(30) NOT NULL default '',
  `WholesaleState` char(3) NOT NULL default '',
  `WholesaleNotes` text,
  `Spouse` varchar(100) NOT NULL default '',
  `Children` text,
  `Gender` smallint(1) unsigned NOT NULL default '0',
  `Birthday` date NOT NULL default '0000-00-00',
  `Anniversary` date NOT NULL default '0000-00-00',
  `Notes` text,
  `StaffNotes` text,
  `EntryType` mediumint(3) NOT NULL default '0',
  `Familiarity` varchar(25) NOT NULL default '',
  `LastContactMailer` int(9) unsigned NOT NULL default '0',
  `LastContactDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `ToBeExported` tinyint(1) unsigned NOT NULL default '1',
  `ExportDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `Exporter` varchar(20) NOT NULL default '',
  `ImportSource` varchar(20) NOT NULL default '',
  `EntrySource` varchar(35) NOT NULL default '',
  `UserName` varchar(30) default NULL,
  `Password` varchar(30) NOT NULL default '',
  `PasswordMD5` varchar(32) NOT NULL default '',
  `EnrollmentAuthToken` varchar(32) default NULL,
  `EnrollmentAuthDuration` char(2) NOT NULL default '',
  `Password_MD5` varchar(32) NOT NULL default '',
  `Image_Files_ID` int(7) unsigned NOT NULL default '0',
  `ReferralSource` varchar(25) NOT NULL default '',
  `ReferralCode` varchar(16) NOT NULL default '',
  `ReferralTerm` varchar(45) NOT NULL default '',
  `ReferralHTTP` text NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `UserName` (`UserName`),
  KEY `FirstName` (`FirstName`),
  KEY `LastName` (`LastName`),
  KEY `Email` (`Email`),
  KEY `Email2` (`Email2`),
  KEY `Image_Files_ID` (`Image_Files_ID`)
) TYPE=MyISAM AUTO_INCREMENT=9 COMMENT='*Ctc Ver.1.0 [Updated 2005-10-15] registered 2006-10-19 19:4';

nemonoman

I don't know the Load data infile command.

From the command line

mysql -uxxx -p

use databaseToUse;
source pathTo/dump.sql;

That should cause mysql load to load your file OK.

I don't typically see the long comment lines in my dumps -- did you use the --compatible=mysql40 option?

nemonoman

The create table command works OK on my server --