If you see this error it is most likely due to a stated limitation in older MySql and MariaDb versions. The existing install of Mysql/MariaDb is likely to be an older install before the limits were raised. The change was made in MySql 5.7.7 and MariaDb 10.2.2 and later. To confirm check your database version with this query:
Run this query from workbench or the mysql command line tool
select version();
or
run this command in a terminal/command window
mysql -V
To fix use a newer version of the database server for QIE.
This error may also be seen as:
Caused by: java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes
NOTE:
This may be encountered when the following lineup:
o Using versoins of MySql earlier than 5.7.7 or Mariadb ealier than 10.2.2. These versions had limit on the size of indexes that did not account for indexes on utf8mb4 fields.
o Move to QIE version .49 or later. This version adds some indexes for performance reasons on fields that are larger than VARCHAR(191)
o The database or specifically the fields indexed use utf8mb4. utf8mb4 is nessecary on fields (that need it) for full unicode support. Fields will inherent from the database collation/character set if not specifically declared.
This link explains the issue with utfbmb4 and indexes: https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes
It states:
"767 bytes is the stated prefix limitation for InnoDB tables in MySQL version 5.6 (and prior versions). It's 1,000 bytes long for MyISAM tables. In MySQL version 5.7 and upwards this limit has been increased to 3072 bytes.
You also have to be aware that if you set an index on a big char or varchar field which is utf8mb4 encoded, you have to divide the max index prefix length of 767 bytes (or 3072 bytes) by 4 resulting in 191. This is because the maximum length of a utf8mb4 character is four bytes. For a utf8 character it would be three bytes resulting in max index prefix length of 254."
Resources:
https://dev.mysql.com/doc/refman/8.0/en/create-index.html
http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes
https://dba.stackexchange.com/questions/11393/mysql-error-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-whil?noredirect=1&lq=1
https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes
