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)
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...
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.
Yes, it hasn't been reported and fixed up until now.
Thank you for the report PhilGer.
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.
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 ...