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

[mtwalsh]

Hi Josh,

I think the SQL code below is causing an issue with MySQL 5.6 as it is trying to put an UNIQUE index on a VARCHAR(500) field, this isn't a problem in MySQL 5.7 but does cause the Specified key was too long; max key length is 767 bytes error in MySQL 5.6:

DROP TABLE IF EXISTS `portal_targets_sites`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `portal_targets_sites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `targetId` int(11) NOT NULL,
  `siteId` int(11) DEFAULT NULL,
  `template` varchar(500) DEFAULT NULL,
  `dateCreated` datetime NOT NULL,
  `dateUpdated` datetime NOT NULL,
  `uid` char(36) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `portal_targets_sites_targetId_siteId_unq_idx` (`targetId`,`siteId`),
  UNIQUE KEY `portal_targets_sites_targetId_siteId_template_unq_idx` (`targetId`,`siteId`,`template`),
  KEY `portal_targets_sites_siteId_idx` (`siteId`),
  CONSTRAINT `portal_targets_sites_siteId_fk` FOREIGN KEY (`siteId`) REFERENCES `sites` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `portal_targets_sites_targetId_fk` FOREIGN KEY (`targetId`) REFERENCES `portal_targets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Changing this:

`template` varchar(500) DEFAULT NULL,

To this (255), would likely fix it but with unknown consequences!:

`template` varchar(255) DEFAULT NULL,

Or remove the template field from the UNIQUE if that's possible.

[joshangell]

Thanks @mtwalsh, it won’t be ideal to remove it from the unique index because otherwise you could have the same template / site combo related to the same target ... if you see what I mean.

Shouldn’t be an issue to convert it to 255 though - could you re-install the plugin from develop and let me know if that fixes it? You’ll need to uninstall first.