SQL export from shared server for localhost

[Don Chambers]

Finally getting around to what should have happened a year ago. I finally installed XAMPP and now want to export the data from my current s9y shared server install, and import that into a localhost database.

Are there any special settings for exporting? From my shared server phpmyadmin, I simply selected "export", left all check boxes at their defaults, and selected a file format of sql. In my localhost version, I selected import, browsed to the zip file created by the export, left character set of the file at utf8, but get the following error:

Code: Select all

Error

SQL query:

-- phpMyAdmin SQL Dump
-- version 2.7.0-pl2
-- http://www.phpmyadmin.net
-- 
-- Generation Time: Feb 09, 2007 at 02:22 PM
-- Server version: 4.0.27
-- PHP Version: 4.4.4
-- 
-- Database: `19982992_chambe`
-- 
-- --------------------------------------------------------
-- 
-- Table structure for table `serendipity_access`
-- 
CREATE TABLE `serendipity_access` (
`groupid` int( 10 ) unsigned NOT NULL default '0',
`artifact_id` int( 10 ) unsigned NOT NULL default '0',
`artifact_type` varchar( 64 ) NOT NULL default '',
`artifact_mode` varchar( 64 ) NOT NULL default '',
`artifact_index` varchar( 64 ) NOT NULL default '',
KEY `accessgroup_idx` ( `groupid` ) ,
KEY `accessgroupT_idx` ( `artifact_id` , `artifact_type` , `artifact_mode` ) ,
KEY `accessforeign_idx` ( `artifact_id` )
) TYPE = MYISAM ;

MySQL said: Documentation
#1046 - No database selected 

[garvinhicking]

Hi!

You must first create an empty new database before you import the dump.

Best regards,
Garvin

[Don Chambers]

DOH!!!

Thanks Garvin - that got me part way there. Imported the first 15 of 28 tables, then get this error:

Code: Select all

Error

SQL query:

-- --------------------------------------------------------
-- 
-- Table structure for table `serendipity_permalinks`
-- 
CREATE TABLE `serendipity_permalinks` (
`permalink` varchar( 255 ) NOT NULL default '',
`entry_id` int( 10 ) unsigned NOT NULL default '0',
`type` varchar( 200 ) NOT NULL default '',
`data` text,
KEY `pl_idx` ( `permalink` ) ,
KEY `ple_idx` ( `entry_id` ) ,
KEY `plt_idx` ( `type` ) ,
KEY `plcomb_idx` ( `permalink` , `type` )
) TYPE = MYISAM ;

MySQL said: Documentation
#1071 - Specified key was too long; max key length is 1000 bytes 

[garvinhicking]

Hi!

Hm, seems your SQL server runs with UTF-16.

You could change it to this:

CREATE TABLE `serendipity_permalinks` (
`permalink` varchar( 255 ) NOT NULL default '',
`entry_id` int( 10 ) unsigned NOT NULL default '0',
`type` varchar( 200 ) NOT NULL default '',
`data` text,
KEY `pl_idx` ( `permalink`(128) ) ,
KEY `ple_idx` ( `entry_id` (10)) ,
KEY `plt_idx` ( `type` (40)) ,
KEY `plcomb_idx` ( `permalink` (128), `type`(40) )
) TYPE = MYISAM ;

I added size limits to the keys so that they do not add up to more than 1000 chars...

For details see http://bugs.mysql.com/bug.php?id=4541

HTH,
Garvin

[Don Chambers]

Still no go.

Code: Select all

Error

SQL query:

CREATE TABLE `serendipity_permalinks` (
`permalink` varchar( 255 ) NOT NULL default '',
`entry_id` int( 10 ) unsigned NOT NULL default '0',
`type` varchar( 200 ) NOT NULL default '',
`data` text,
KEY `pl_idx` ( `permalink` ( 128 ) ) ,
KEY `ple_idx` ( `entry_id` ( 10 ) ) ,
KEY `plt_idx` ( `type` ( 40 ) ) ,
KEY `plcomb_idx` ( `permalink` ( 128 ) , `type` ( 40 ) )
) TYPE = MYISAM ;

MySQL said: Documentation
#1089 - Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys 

Assuming this had worked, would I just have then tried to import the entire database again, or would I be doing it one table at a time for the remaining 13 tables?

[garvinhicking]

Hi!

Maybe you can set your DB collations to UTF8 instead of UTF16?

Regards,
Garvin

Still no go.

Code: Select all

Error

SQL query:

CREATE TABLE `serendipity_permalinks` (
`permalink` varchar( 255 ) NOT NULL default '',
`entry_id` int( 10 ) unsigned NOT NULL default '0',
`type` varchar( 200 ) NOT NULL default '',
`data` text,
KEY `pl_idx` ( `permalink` ( 128 ) ) ,
KEY `ple_idx` ( `entry_id` ( 10 ) ) ,
KEY `plt_idx` ( `type` ( 40 ) ) ,
KEY `plcomb_idx` ( `permalink` ( 128 ) , `type` ( 40 ) )
) TYPE = MYISAM ;

MySQL said: Documentation
#1089 - Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys 

Assuming this had worked, would I just have then tried to import the entire database again, or would I be doing it one table at a time for the remaining 13 tables?

[Don Chambers]

Hi!

Maybe you can set your DB collations to UTF8 instead of UTF16?

Regards,
Garvin

The collations do not appear on myphpadmin screen for my shared server database as they do in my localhost version.

[garvinhicking]

Hi!

Yes, you must change the collation on the Target server (your local one), not the online version!

Best regards,
Garvin