Forums

MySQL error when importing the database from local to online host

Permalink
Hello.

I am getting the following error when I am trying to import the local database to an online environment. I am using Concrete5 8.5.2 and Mamp Pro 5.5.1 on MacOS Mojave.

SQL-query:
CREATE TABLE  `AreaLayoutPresets` (
 `arLayoutPresetID` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
 `arLayoutID` INT( 10 ) UNSIGNED NOT NULL DEFAULT  '0',
 `arLayoutPresetName` VARCHAR( 255 ) COLLATE utf8mb4_unicode_ci DEFAULT NULL ,
PRIMARY KEY (  `arLayoutPresetID` ) ,
KEY  `arLayoutID` (  `arLayoutID` ) ,
KEY  `arLayoutPresetName` (  `arLayoutPresetName` )
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
MySQL meldt: Documentatie
#1709 - Index column size too large. The maximum column size is 767 bytes.
 
hutman replied on at Permalink Reply
hutman
It looks like your server has an older version of MySQL than your MAMP uses. The server uses utf8 where your MAMP uses utf8mb4.

A VARCHAR(255) column can take up to 765 (255*3) bytes using utf8 and 1020 (255*4) bytes using utf8mb4. This is because in MySQL utf8 takes up to 3 bytes and utf8mb4 up to 4 bytes (the real UTF8).

I'm not sure how to fix this from a C5 standpoint unless you can either upgrade the server MySQL version or downgrade the MAMP MySQL version.
juddc replied on at Permalink Reply
juddc
I get a similar one (update to 8.5 on MAMP via Mac):

--
-- Indexes for table `AreaPermissionAssignments`
--
ALTER TABLE `AreaPermissionAssignments`
  ADD PRIMARY KEY (`cID`,`arHandle`,`pkID`,`paID`),
  ADD KEY `paID` (`paID`),
  ADD KEY `pkID` (`pkID`)
MySQL said: 
#1071 - Specified key was too long; max key length is 767 bytes