Where to add innodb_large_prefix

Question

I am running into a bit of an issue.

You see I have made a WordPress website locally using WAMP and everything seemed to be working fine, until I tried to get the MySQL database imported onto the new live site where it gave an error:

"#1709 - Index column size is to large, The maximum column size is 767 bytes"

See image of the complete error here:

Now I have found some answers to what may be causing this here:

MySQL error: The maximum column size is 767 bytes

And here:

mysql change innodb_large_prefix

And although I understand what needs to be imlemented code wise, I am none the wiser as to where the code actually needs to be placed.

As aside from importing and exporting and editing the database credentials I never had to do anything else with MySQL, it is all a bit foreign to me. And though I am more than happy to look more deeply into it at a later point in time, at this point I rather just want my live site to be working.

If you know what to do: can you share these thoughts such that we can help you implement them? — Nico Haase, Jun 20 '18 at 11:37

score 1 · Answer 1 · answered Jun 21 '18 at 12:46

Well I figured it out, apparently I had to edit the SQL file itself and had to add ROW_FORMAT=DYNAMIC at the end of every CREATE TABLE Query which uses the INNODB engine.

So I changed this:

CREATE TABLE `xxx` (
  `visit_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `visitor_cookie` mediumtext NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `subscriber_id` bigint(20) NOT NULL,
  `url` mediumtext NOT NULL,
  `ip` tinytext NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`visit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Into

CREATE TABLE `xxx` (
  `visit_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `visitor_cookie` mediumtext NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `subscriber_id` bigint(20) NOT NULL,
  `url` mediumtext NOT NULL,
  `ip` tinytext NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`visit_id`)
) ROW_FORMAT=DYNAMIC ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Then I re-imported the file onto the local server and then did a new export to the live server... and it is live now...finally.

I still find it a bit strange that mySQL doesn't automatically set rows to dynamic, once you exceed a certain amount of characters ( 747) and that it still works inside the existing database eventhough it shouldn't work...but maybe WAMP just has different environment settings vs the live server.

Anyway thanks all!

Where to add innodb_large_prefix

1 Answers1