MySQL 8 - ERROR 1071 (42000) in file: 'create_db_tables.sql': Specified key was too long; max key length is 1000 bytes

[bourtemb]

A problem has been reported on the forum when using MySQL community 8.
https://www.tango-controls.org/community/forum/c/general/installation/tango-9-windows-demo-installation-problem/?page=1#post-3999

It looks like a workaround can be to edit create_db.sql.in and to replace the following line

CREATE DATABASE tango;
with
CREATE DATABASE tango DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Workaround found on https://stackoverflow.com/questions/20014106/how-to-create-this-table-1071-specified-key-was-too-long-max-key-length-is/42497309

Deeper investigations are needed to see if this workaround is the right approach and if it works with MariaDB and older MySQL versions.

[bourtemb]

We also need to consider the following use case:

• a user was using an older version of the TangoDatabase schema.
• the user made a backup of its old Tango Database (using Tango 8 DB schema for instance)
• the user upgrades to a recent version of MySQL or MariaDB
• the user restores its backup into the new MySQL or MariaDB database
• the user executes the update scripts to alter the schema to be tango 9 compatible

In this case, the user should be able to update the schema without receiving errors.

[mliszcz]

Hi @bourtemb,
Can we use this ticket to track "db upgrade" scenarios (blocking TangoSourceDistribution release) that we discussed last week?
Could you provide a db upgrade scenario (Tango version, Mysql/Mariadb version) that shows the problem?

edit: I've just tried the following, and it is working fine on ubuntu:xenial:

• install MariaDB 10.3.15 (latest stable)
• install database ds (tango-db/xenial,now 8.1.2c+dfsg-7ubuntu3 amd64)
• run /usr/share/tango-db/create_db_tables.sql (Tango 8, OK)
• run /usr/share/tango-db/stored_proc.sql (Tango 8, OK)
• run update_db8.sql (Tango 9, OK)

[bourtemb]

Hi @bourtemb,
Can we use this ticket to track "db upgrade" scenarios (blocking TangoSourceDistribution release) that we discussed last week?

Yes, we can.

Could you provide a db upgrade scenario (Tango version, Mysql/Mariadb version) that shows the problem?

edit: I've just tried the following, and it is working fine on ubuntu:xenial:

* install MariaDB 10.3.15 (latest stable)

* install database ds (tango-db/xenial,now 8.1.2c+dfsg-7ubuntu3 amd64)

* run /usr/share/tango-db/create_db_tables.sql (Tango 8, OK)

* run /usr/share/tango-db/stored_proc.sql (Tango 8, OK)

* run update_db8.sql (Tango 9, OK)

Good to know that there is no problem in this case.

Could you please try the same as you did with the latest MySQL server version (8.x) instead of latest MariaDB latest stable version?

[mliszcz]

I've tried the same with mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz

• Tango 8 schema cannot be created due to NO_ZERO_IN_DATE,NO_ZERO_DATE in default sql_mode. It worked after I've removed these two options.
• I also needed to apply: CREATE DATABASE tango DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
• Then I ran update_db8.sql without any errors/warnings.

Note that my database is empty (i.e. there are no devices/servers/etc.)

[mliszcz]

I've also looked a bit into the key size issue.

Key size limit is 1000 bytes for MyISAM tables in both MySQL [1] (see Column Prefix) and MariaDB [2].

In a few tables index is created for a VARCHAR(255) column, e.g.:

CREATE TABLE IF NOT EXISTS property_hist (
  id bigint unsigned NOT NULL default '0',
  date timestamp NOT NULL,
  object varchar(255) NOT NULL default '',
  name varchar(255) NOT NULL default '',
  count int(11) NOT NULL default '0',
  value text,
  KEY index_id (id),
  KEY index_object (object),
  KEY index_name (name)
) ENGINE=MyISAM;

In utf8mb4 charset, each character has 4 bytes (255*4 > 1000 bytes).

MariaDB [tango]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_general_ci   |
+--------------------------+----------------------+

Proposed workaround was to use utf8mb3 (aka utf8) which has 3-byte characters.

We have at least two other options:

• decrease the size of some columns from 255 to 250 characters
• set up index on prefix of a column (e.g. just on first 100 characters)

So in the example table above we will have:

  KEY index_id (id),
  KEY index_object (object(100)),
  KEY index_name (name(100))
) ENGINE=MyISAM;

I suggest to go with the last option (index on prefix) but I don't know how long the index should be (probably there will be impact on performance / memory consumption).

Why is the issue only visible on MySQL and not on MariaDB?

For a nonunique index, either an error occurs (if strict SQL mode is enabled), or the index length is reduced to lie within the maximum column data type size and a warning is produced (if strict SQL mode is not enabled).

MariaDB truncates index by default (Sub_part is 250):

MariaDB [tango]> CREATE TABLE testing ( name varchar(250) NOT NULL default '', KEY index_name (name) ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

MariaDB [tango]> drop table testing;
Query OK, 0 rows affected (0.00 sec)

MariaDB [tango]> CREATE TABLE testing ( name varchar(251) NOT NULL default '', KEY index_name (name) ) ENGINE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1071): Specified key was too long; max key length is 1000 bytes
MariaDB [tango]> show index from testing;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| testing |          1 | index_name |            1 | name        | A         |        NULL |      250 | NULL   |      | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

Whereas MySQL throws an error:

mysql> CREATE TABLE testing ( name varchar(255) NOT NULL default '', KEY index_name (name) ) ENGINE=MyISAM;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
mysql> CREATE TABLE testing ( name varchar(255) NOT NULL default '', KEY index_name (name(250)) ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> show index from testing;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| testing |          1 | index_name |            1 | name        | A         |        NULL |      250 |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

[mliszcz]

Just checked mysql 5.7 from ubuntu:xenial repos. It uses latin1 (with 1-byte characters) by default:

mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| latin1                   | latin1_swedish_ci    |
+--------------------------+----------------------+

[bourtemb]

Yeah. It looks like they changed all the character set default values to utf8mb4 in MySQL 8.0.1.

There is a workaround explained in MySQL 8.0.1 changes web page:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html#mysqld-8-0-1-charset

One way to preserve the previous defaults is to start the server with these lines in the my.cnf file:

[mysqld]
character_set_server=latin1
collation_server=latin1_swedish_ci

[mliszcz]

Shall we then update my.cnf.in?

[bourtemb]

Shall we then update my.cnf.in?

Good question...
It looks like this would be the easiest way to get the same behaviour as before.

In the future, I think it would be interesting to support different character sets in Tango (in the DB, in the tools) for the people using Tango in some countries using characters not included in latin1 characters set.
But this is probably not straight-forward.
So I would be in favour of updating my.cnf.in for the moment.

[mliszcz]

Ok! I'll do some tests and send a PR tomorrow. Still, I think it would be good to document this somewhere, as some people may not use config file distributed with Tango.

[bourtemb]

Ok! I'll do some tests and send a PR tomorrow.

Thanks

Still, I think it would be good to document this somewhere, as some people may not use config file distributed with Tango.

I agree. This should be documented in the install section of the doc (README/INSTALL file?).
We still need to agree on how do we re-organize all these files.

[mliszcz]

Btw why do you prefer to change charset globally for all databases instead of setting it only for tango database (in create database statement) as in issue description?

[bourtemb]

Btw why do you prefer to change charset globally for all databases instead of setting it only for tango database (in create database statement) as in issue description?

I just reported in the comment what MySQL was suggesting as workaround to get easily the same behaviour as before. It was not really a preference.
TANGO is using MySQL stored procedures feature. These stored procedures are stored in MySQL system database named mysql.
To be on the safe side, I think it is safer to get a mysql server behaving as before.
I understand this could be annoying for users having multiple MySQL databases on the same host and who would like to use utf8...
They can still use the other work-around you mentioned. It might work.

[mliszcz]

my.cnf distributed with Tango is not working.

MySQL 8.0.16 does not recognize following options:

• table_cache
• skip-locking
• thread_concurrency
• query_cache_size
• query_cache_limit
• default_table_type
• myisam_recover
• skip-bdb

MariaDB 10.3.15 does not like:

• skip-locking
• default_table_type

Were there any reports of this? If no, then it looks like no one is using that file anyway.

Also, there are some more differences in default charset between MySQL and MariaDB.

• MySQL by default is using utf8mb4
• MariaDB by default is using latin1
• But e.g. Ubuntu provides their own config file in mariadb-common package where they set the charset to utf8mb4.

If we still want to provide my.cnf then I think we should reduce it to a bare minimum (i.e. just keep options that are mandatory for Tango to work) and then let users to unclude it in their config files. Users could then configure their database as they see fit (or keep the defaults).

[bourtemb]

my.cnf distributed with Tango is not working.

MySQL 8.0.16 does not recognize following options:

* table_cache

* skip-locking

* thread_concurrency

* query_cache_size

* query_cache_limit

* default_table_type

* myisam_recover

* skip-bdb

MariaDB 10.3.15 does not like:

* skip-locking

* default_table_type

Were there any reports of this? If no, then it looks like no one is using that file anyway.
I wouldn't say that noone is using that file.
Almost noone is using MySQL 8 for the moment in the Tango community.
mysql.cnf file provided with the distribution is just a file which can be used to help the user to configure MySQL. The user can use it and tweak it to his needs.

There have been many changes in recent MySQL versions so one will have to look at all these options and understand whether we really need them or not.

Maybe @taurel can help us and tell us on what options he remembers are critical for a Tango Database?
Maybe most of these options are not needed anymore?

[bourtemb]

default_table_type have been replaced with default-storage-engine option so we could already replace that.
skip-locking has been renamed in skip-external-locking

[mliszcz]

I've tried to check what happened to those options:

    table_cache            renamed to table_open_cache in 5.1.3, default 2000 in MariaDB, 4000 in MySQL (https://dba.stackexchange.com/questions/104025/mysql-unknown-variable-table-cache-64)
    skip-locking           renamed to skip-external-locking 5.5, default  (https://support.plesk.com/hc/en-us/articles/213941625-Unable-to-start-MySQL-Error-unknown-option-skip-locking-)
    thread_concurrency     removed in 5.7, Solaris 8 specific (https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_thread_concurrency)
    query_cache_size       removed in 8.0, disabled in MariaDB (https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/)
    query_cache_limit      removed in 8.0
    default_table_type     renamed to default-storage-engine, defaults to InnoDB (https://serverfault.com/questions/93559/how-do-i-set-the-default-table-type-as-innodb-in-my-cnf)
    myisam_recover         renamed to myisam-recover-options, defaults to OFF (https://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_myisam-recover-options)
    skip-bdb               removed in 5.1 (https://bugs.mysql.com/bug.php?id=50336)

We would need to address these at least:

• table_cache - we use 512. Is default 2000/4000 OK?
• query cache - MySQL dropped support, do we need separate file for MariaDB?
• default_table_type - we have ENGINE=MyISAM set explicitly in CREATE TABLE statements, maybe we do not need this option?

[mliszcz]

default_table_type have been replaced with default-storage-engine option so we could already replace that.
skip-locking has been renamed in skip-external-locking

Does that mean we will drop support for older MySQL / MariaDB versions? Which versions will be supported?

[bourtemb]

We would need to address these at least:

* table_cache  - we use 512. Is default 2000/4000 OK?

I think default is probably fine.

* query cache - MySQL dropped support, do we need separate file for MariaDB?

I am not sure... We need to see whether we need this feature, whether it has been replaced by something else...

* default_table_type - we have ENGINE=MyISAM set explicitly in CREATE TABLE statements, maybe we do not need this option?

I agree with you. It looks like we don't need this option.

What we need to know/understand is: are there any options which are really essential for a Tango Database to run smoothly?
If there is nothing special, then I think we should remove this file and let the user use the default file provided with MySQL and MariaDB.

default_table_type have been replaced with default-storage-engine option so we could already replace that.
skip-locking has been renamed in skip-external-locking

Does that mean we will drop support for older MySQL / MariaDB versions? Which versions will be supported?

We don't really drop support... The user is free to use this file or not... And if a user has already a Tango database running on a very old MySQL version, then he already configured his database for his version of MySQL so he will not use this file coming from the distribution again.
Still, I think we should not keep options which are deprecated since MySQL 5.

[bourtemb]

After talking with @taurel, it seems like we don't really need this file anymore.
It looks like we could remove it.
The only references in the documentation (TangoDatabase README file) to my.cnf files are just to specify the mysql user and password used to connect to the database.
There is no mention of special options which are really essential to get a Tango Database running smoothly.

If we remove it, we will need to update configure.ac and cppserver/database/Makefile.am in the TangoSourceDistribution so it is no longer taken into account.

[mliszcz]

So, do you propose to remove the file and simply update documentation that we recommend to use:

[mysqld]
character_set_server=latin1
collation_server=latin1_swedish_ci

Or do we keep the file but remove everything except charset configuration?

[bourtemb]

I'm in favour of removing the file and updating the documentation.

[mliszcz]

I second that. I'll make a PR for TangoDatabase and for TangoSourceDistribution. We can then wait a bit for the others to share their opinions.

[bourtemb]

As specified in the README, the current recommended work-around for this issue is to configure MySQL 8 to use a character set with character <= 3 bytes.
As written above, this can be configured at the my.cnf configuration file level using:

[mysqld]
character_set_server=latin1
collation_server=latin1_swedish_ci

I'm closing this issue. If someone has a better solution, please do not hesitate to re-open and comment.