Advertisement:

Author Topic: SMF 2.1 RC2 Upgrade script fails on MySql 8 - table qanda - key was too long  (Read 1321 times)

Offline PhilGer

  • Semi-Newbie
  • *
  • Posts: 25
Problem: Upgrade script fails on MySql 8 - table qanda

Source: upgrade_2-1_mysql.sql

Step:
Code: [Select]
/******************************************************************************/
--- Upgrading "verification questions" feature
/******************************************************************************/
---# Creating qanda table
CREATE TABLE IF NOT EXISTS {$db_prefix}qanda (
id_question SMALLINT(5) UNSIGNED AUTO_INCREMENT,
lngfile VARCHAR(255) NOT NULL DEFAULT '',
question VARCHAR(255) NOT NULL DEFAULT '',
answers TEXT NOT NULL,
PRIMARY KEY (id_question),
INDEX idx_lngfile (lngfile)
) ENGINE=MyISAM;
---#

-> Error: Specified key was too long; max key length is 1000 bytes

Cause: MySQL 8 uses the utf8mb4 charset by default. Varchar(255)*4 exceeds 1000 bytes.

Is there really any lngfile remotely in the region of 255 charactes length?
If yes, does the index on the lngfile actually provide any benefit?

Workaround: Change the lngfile column to VARCHAR(250)

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 72,651
    • StoryBB/StoryBB on GitHub
The index is needed for performance on larger sites that have a lot of Q&A (since the recommendation is 30 questions per language installed)

The length of index was largely historical and has probably been fixed in the year since RC2 came out...
Please don’t PM me for paid work, I’m not for hire, and even if I was, I doubt you could afford me.
USD$150 per hour. Typical waiting list 3 months.

Offline PhilGer

  • Semi-Newbie
  • *
  • Posts: 25
The length of index was largely historical and has probably been fixed in the year since RC2 came out...
I cannot find any previous report addressing this issue.
As far as I can see, the code causing the issue is still in the current source at GitHub.

Online SychO

  • Lead Customizer
  • SMF Hero
  • *
  • Posts: 1,811
  • Gender: Male
    • SychO9 on GitHub
    • SychO
Yes, it hasn't been reported and fixed up until now.

Thank you for the report PhilGer.
Take the initiative

Offline Suki

  • Carthago delenda est
  • Developer
  • SMF Super Hero
  • *
  • Posts: 15,699
  • Oh, wouldn't it be great if I *was* crazy?
    • MissAllSunday on GitHub
    • SMF mods
Hi PhilGer, thank you for your recent bug reports. This one has been tracked: https://github.com/SimpleMachines/SMF2.1/issues/6163

As previously mentioned, it is kinda difficult to track down all changed and fixes introduced since RC2 even by taking a look at the closed issues since not all fixes/improvements are logged as an issue. Is it doable?, sure but then the omnipresence "not enough time/hands" card kicks in :/

Nonetheless, thank you, valid reports truly help us to focus our limited resources better.
Disclaimer: unless otherwise stated, all my posts are personal and does not represent any views or opinions held by Simple Machines.

No todos son tan malos, no todo está mal
No todos son villanos queriéndote matar
No todo está perdido ni se va a acabar
La vida es un pícnic

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 616
smf2.1 is the definition of focusing..
only support php from 5.4 - 7.4 ( 8 version)

mysql 5.0 - 8.0 (6 version)
mariadb all version (10 version)

myisam and innodb

postgres 9.4 - 12 (6 version) i play with the idea to drop 9.4 and 9.5 support ...