0

I maintain an application we use at work. Last week I updated my homestead that I use to run the application local. For some reason I now get an error when some queries run. The query used to work but it now gives the following error: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes.

The query that produces the error is:

CREATE TABLE IF NOT EXISTS surveyswitch_data (
    `survey_from` INT( 11)NOT NULL ,
    `survey_to` INT( 11)NOT NULL ,
    `name` VARCHAR( 255)NOT NULL ,
    `value` VARCHAR( 255)NOT NULL ,
    PRIMARY KEY (`survey_from` ,`survey_to` ,`name`) 
) ENGINE=MYISAM;

The only thing that has changed is that I updated homestead, that includes apache and mysql. The mysql version on homestead is 5.7.28 now. On our server, where the query still works, we have mysql 5.7.27.

I already tried to change to innodb_large_prefix on. That didn't help. What setting should I change to make it work again?

Bart Bergmans
  • 4,061
  • 3
  • 28
  • 56
  • Probably your charset has changed? – Nico Haase Dec 11 '19 at 09:38
  • 1
    Does this answer your question? [#1071 - Specified key was too long; max key length is 767 bytes](https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes) – Nico Haase Dec 11 '19 at 09:38
  • Kudos for tuning innodb with myisam tables. – Mike Doe Dec 11 '19 at 09:40
  • @NicoHaase But then I have to change the query. Why is the query working on our mysql 5.7.27 server but not on 5.7.28? Can I fix it without changing the query. – Bart Bergmans Dec 11 '19 at 09:43
  • Have you tried to check whether all configuration matches? What about the charset - you haven't specified it explicitly in your query – Nico Haase Dec 11 '19 at 10:05
  • @NicoHaase The charset is utf8mb4 for the database. I will check the configuration. – Bart Bergmans Dec 11 '19 at 10:10
  • 1
    Well, if the charset is mb4-based, then the error message is pretty obvious to read: using mb4, the size of `value` is already 1020 bytes which is more than 1000 bytes. And according to the documentation, `innodb_large_prefix`only works for specific row formats and is, immediately after being introduced, already deprecated – Nico Haase Dec 11 '19 at 10:14
  • My bad, I thought the same character set was used on the production server. Seems it was just utf8 and not utf8mb4. Changed my Homestead to utf8 and now it works. Thanks a lot for pointing it out! – Bart Bergmans Dec 11 '19 at 10:17

0 Answers0