23 October 2009, 11:38

Hi, 

I am moving my mahara install from a test machine to live.

I took a copy of my files and made a backup of the mysql database using this command

mysqldump -u root --password=$MYSQLPASS -C -Q -e -a $instance > $backupfile

When I try to import the  $backupfile on the new host I get this mysql error

# mysql -D$instance < $backupfile

ERROR 1071 (42000) at line 2688: Specified key was too long; max key length is 1000 bytes

I am not sure how accurate the line number is regarding the error.

This is an extract of the file around that line - line is marked with (#LINE 2688#):

################################################################ 

CREATE TABLE IF NOT EXISTS `artefact_config` (

  `plugin` varchar(100) COLLATE utf8_bin NOT NULL,

  `field` varchar(100) COLLATE utf8_bin NOT NULL,

  `value` text COLLATE utf8_bin NOT NULL,

  PRIMARY KEY (`plugin`,`field`),

  KEY `arteconf_plu_ix` (`plugin`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--

-- Dumping data for table `artefact_config`

--

INSERT INTO `artefact_config` (`plugin`, `field`, `value`) VALUES

('file', 'defaultquota', 0x3532343238383030);

-- --------------------------------------------------------

(#LINE 2688#)--

-- Table structure for table `artefact_cron`

--

CREATE TABLE IF NOT EXISTS `artefact_cron` (

  `plugin` varchar(255) COLLATE utf8_bin NOT NULL,

  `callfunction` varchar(255) COLLATE utf8_bin NOT NULL,

  `nextrun` datetime DEFAULT NULL,

  `minute` varchar(25) COLLATE utf8_bin NOT NULL DEFAULT '*',

  `hour` varchar(25) COLLATE utf8_bin NOT NULL DEFAULT '*',

  `day` varchar(25) COLLATE utf8_bin NOT NULL DEFAULT '*',

  `month` varchar(25) COLLATE utf8_bin NOT NULL DEFAULT '*',

  `dayofweek` varchar(25) COLLATE utf8_bin NOT NULL DEFAULT '*',

  PRIMARY KEY (`plugin`,`callfunction`),

  KEY `artecron_plu_ix` (`plugin`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--

-- Dumping data for table `artefact_cron`

--

INSERT INTO `artefact_cron` (`plugin`, `callfunction`, `nextrun`, `minute`, `hour`, `day`, `month`, `dayofweek`) VALUES

('blog', 'clean_post_files', NULL, '40', '4', '*', '*', '*'),

('internal', 'clean_email_validations', NULL, '10', '4', '*', '*', '*');

################################################################### 

From what I could find on the web about this error ..

it seems that it is related to the utf8 encoding ?

I thought it was best to use utf8 encoding ?

Is it possible for me to resolve this error or

will I need to change the encoding to do the import .. and if so what is the best way to do this ? 

Thanks for your help,

Justin 

26 October 2009, 18:41

MySQL for the win again..

One thing you could try is making sure you created the database with a UTF8 encoding. How did you create the database? If you're using the shell, you can use this command:

create database (databasename) character set UTF8;

18 December 2009, 7:52

Hi Nigel,

Sorry I did not get back to this for a while ...

The test system is on MySQL Server version: 5.1.33 

and remote system is on  MySQL Server version: 5.0.58

The database was created using UTF8 

I upgraded my mahara test and remote site to the latest version - 1.2.2 ..

and now the dump and import work ok .. which is great. 

Thanks for your help.