News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

SMF 2.1 RC2 Upgrade script fails on MySql 8 - table qanda - key was too long

Started by PhilGer, June 21, 2020, 09:51:59 AM

Previous topic - Next topic

PhilGer

Problem: Upgrade script fails on MySql 8 - table qanda

Source: upgrade_2-1_mysql.sql

Step:
/******************************************************************************/
--- 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)

Arantor

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...

PhilGer

Quote from: Arantor on June 21, 2020, 11:33:45 AM
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.

SychO

Yes, it hasn't been reported and fixed up until now.

Thank you for the report PhilGer.
Checkout My Themes:
-

Potato  •  Ackerman  •  SunRise  •  NightBreeze

Suki

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.

albertlast

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 ...

Advertisement: