Page 1 of 1
SQL export from shared server for localhost
Posted: Fri Feb 09, 2007 10:54 pm
by 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
Re: SQL export from shared server for localhost
Posted: Sat Feb 10, 2007 10:53 am
by garvinhicking
Hi!
You must first create an empty new database before you import the dump.
Best regards,
Garvin
Posted: Sat Feb 10, 2007 4:48 pm
by 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
Posted: Mon Feb 12, 2007 1:27 pm
by 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
Posted: Mon Feb 12, 2007 3:42 pm
by 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?
Posted: Mon Feb 12, 2007 4:12 pm
by garvinhicking
Hi!
Maybe you can set your DB collations to UTF8 instead of UTF16?
Regards,
Garvin
Owen Stubbs wrote: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?
Posted: Mon Feb 12, 2007 7:31 pm
by Don Chambers
garvinhicking wrote: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.
Posted: Tue Feb 13, 2007 9:54 am
by garvinhicking
Hi!
Yes, you must change the collation on the Target server (your local one), not the online version!
Best regards,
Garvin