grafana 4.3+ broken on MySQL < 5.7.7

[Roguelazer]

Grafana 4.3 includes a migration that changes the types of a number of columns to force the charset to utf8mb4. These columns are all VARCHAR(255).

MySQL servers using the Antelope file format (or any MySQL server running a version older than 5.7.7, which added the innodb_large_prefix setting) cannot have indexes of more than 767 bytes; therefore, all of these migrations fail on MySQL 5.6 with an error like

INFO[05-23|23:54:45] Executing migration                      logger=migrator id="Update temp_user table charset"
EROR[05-23|23:54:45] Executing migration failed               logger=migrator id="Update temp_user table charset" error="Error 1071: Specified key was too long; max key length is 767 bytes"
EROR[05-23|23:54:45] Exec failed                              logger=migrator error="Error 1071: Specified key was too long; max key length is 767 bytes" sql="ALTER TABLE `temp_user` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, MODIFY `email` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL , MODIFY `name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL , MODIFY `role` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL , MODIFY `code` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL , MODIFY `status` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL , MODIFY `remote_addr` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL ;"
EROR[05-23|23:54:45] Fail to initialize orm engine            logger=sqlstore error="Sqlstore::Migration failed err: Error 1071: Specified key was too long; max key length is 767 bytes\n"

This is incredibly irritating to try and work around because grafana bakes the migrations into the bloody binary, so I can't just modify the migration script to drop it from VARCHAR(255) to VARCHAR(128) or to change the index to be a partial index.

[Roguelazer]

I've worked around this in our install (for now) by running the following statements to change the indexes to be prefix indexes:

ALTER TABLE temp_user DROP INDEX IDX_temp_user_email, ADD INDEX IDX_temp_user_email (`email` (64));
ALTER TABLE temp_user DROP INDEX IDX_temp_user_code, ADD INDEX IDX_temp_user_code (`code` (64));
ALTER TABLE dashboard DROP KEY UQE_dashboard_org_id_slug, ADD UNIQUE KEY UQE_dashboard_org_id_slug (`org_id`, `slug` (160));
ALTER TABLE dashboard DROP KEY IDX_dashboard_org_id_plugin_id, ADD KEY IDX_dashboard_org_id_plugin_id (`org_id`, `plugin_id` (64));
ALTER TABLE alert DROP KEY IDX_alert_state, ADD KEY IDX_alert_state (`state` (64));

[torkelo]

we did change length on all indexed columns to work around this problem.Looks like we missed modifying the length of email field on temp_user

[torkelo]

this is strange, trying to replicate in mysql 5.6 and 5.5 without any success, migrations complete without issue.

Do you have to have data that exceeds the limit for the error to appear?

[evoncken]

Just wanted to confirm I see the same issue:

• Attempted an upgrade from Grafana 2.6.x to 4.3.1
• Database is MySQL: mysql-5.6.14-linux-glibc2.5-x86_64

Encountered the same error:

t=2017-05-24T10:12:30+0100 lvl=eror msg="Exec failed" logger=migrator error="Error 1071: Specified key was too long; max key length is 767 bytes" sql="ALTER TABLE `temp_user` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, MODIFY `email` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL , MODIFY `name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL , MODIFY `role` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL , MODIFY `code` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL , MODIFY `status` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL , MODIFY `remote_addr` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL ;"

Followed by:

t=2017-05-24T10:12:31+0100 lvl=eror msg="Fail to initialize orm engine" logger=sqlstore error="Sqlstore::Migration failed err: Error 1071: Specified key was too long; max key length is 767 bytes\n"

[azhiltsov]

We are using 5.6.27-log MySQL Community Server and DBAs told me to execute
alter table ${table} ROW_FORMAT=DYNAMIC;
on each failed table.
It could be something specific to our case, but it helped

[hydro-b]

Exactly the same errors as reported here on Ubuntu precise with Percona 5.6.15-rel63.0-519.precise. @Roguelazer thanks for the workaround.

[yuyutime]

Not directly related to migrations but Grafana 4.3.1 has also problem adding MySQL datasource for old MariaDB version (5.2.7-MariaDB-mariadb101-log in ly case) - only utf8 unicode charset is supported.

Error 1115: Unknown character set: 'utf8mb4'

Same error is returned for queries.
Is there a way to use utf8 charset instead of 'utf8mb4' for datasource or let user specify charset explicitely in addition to port?
For example, instead of
dbhostname:3306 allow dbhostname:3306:utf8 in datasource setup form.
Or add optional separate charset field in form.

[DanCech]

@yuyutime it might be time to upgrade your mariadb server, 5.2.7 was released almost 6 years ago.

[DanCech]

It looks like there are 3 columns that might be causing the problem, will open a PR shortly.

[DanCech]

@Roguelazer did you have a problem with the dashboard slug index? I see that you changed it but didn't see anything in the logs pointing to it?

[Roguelazer]

I'm pretty certain that I changed all of those in response to error messages printed out.

[Roguelazer]

slug is 190 characters (@utf8mb4 = 760bytes) and orgid is a bigint (=8 bytes), which leads to a 768-byte index, which is indeed one byte over the 767 byte maximum.

I just created a test table with those columns in that index and confirmed that MySQL <5.7.7 returns

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

[DanCech]

@Roguelazer #8483 should do the trick, it's working for me against the mysql:5.5 official docker image.

[torkelo]

PR is merged to master, and fix is cherry-picked it into v4.3.x 7004a84 will be included in 4.3.2 patch release

[Roguelazer]

Thanks for the fast response!

[Blank-v233]

environment

System:  CentOS release 6.9 (Final)
Database: mysql  Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using  EditLine wrapper

purpose
Initial installation

I had the following problem yesterday.

rpm -ivh grafana-4.3.1-1.x86_64.rpm
service grafana-server start

t=2017-05-31T12:50:32+0800 lvl=info msg="Executing migration" logger=migrator id="create index IDX_temp_user_email - v1-7"
t=2017-05-31T12:50:32+0800 lvl=eror msg="Executing migration failed" logger=migrator id="create index IDX_temp_user_email - v1-7" error="Error 1071: Specified key was too long; max key length is 767 bytes"
t=2017-05-31T12:50:32+0800 lvl=eror msg="Exec failed" logger=migrator error="Error 1071: Specified key was too long; max key length is 767 bytes" sql="CREATE INDEX `IDX_temp_user_email` ON `temp_user` (`email`);"
t=2017-05-31T12:50:32+0800 lvl=eror msg="Fail to initialize orm engine" logger=sqlstore error="Sqlstore::Migration failed err: Error 1071: Specified key was too long; max key length is 767 bytes\n"

I waited a day for the 4.3.2 patch
Today the results are as follows

t=2017-06-01T10:03:08+0800 lvl=info msg="Executing migration" logger=migrator id="Add index for plugin_id in dashboard"
t=2017-06-01T10:03:08+0800 lvl=eror msg="Executing migration failed" logger=migrator id="Add index for plugin_id in dashboard" error="Error 1071: Specified key was too long; max key length is 767 bytes"
t=2017-06-01T10:03:08+0800 lvl=eror msg="Exec failed" logger=migrator error="Error 1071: Specified key was too long; max key length is 767 bytes" sql="CREATE INDEX `IDX_dashboard_org_id_plugin_id` ON `dashboard` (`org_id`,`plugin_id`);"
t=2017-06-01T10:03:08+0800 lvl=eror msg="Fail to initialize orm engine" logger=sqlstore error="Sqlstore::Migration failed err: Error 1071: Specified key was too long; max key length is 767 bytes\n

[gtt116]

My environment:

Centos 6.4
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Using Grafana 4.3.2, looks like db migration failed:

INFO[06-02|09:45:50] Starting Grafana                         logger=main version=4.3.2 commit=ed4d170 compiled=2017-05-31T16:48:16+0800
INFO[06-02|09:45:50] Config loaded from                       logger=settings file=/home/graphite/grafana/conf/defaults.ini
INFO[06-02|09:45:50] Config loaded from                       logger=settings file=../conf/defaults.ini
INFO[06-02|09:45:50] Path Home                                logger=settings path=/home/graphite/grafana
INFO[06-02|09:45:50] Path Data                                logger=settings path=/home/graphite/grafana/data
INFO[06-02|09:45:50] Path Logs                                logger=settings path=/home/graphite/grafana/data/log
INFO[06-02|09:45:50] Path Plugins                             logger=settings path=/home/graphite/grafana/data/plugins
INFO[06-02|09:45:50] Initializing DB                          logger=sqlstore dbtype=mysql
INFO[06-02|09:45:50] Starting DB migration                    logger=migrator
EROR[06-02|09:45:50] Fail to initialize orm engine            logger=sqlstore error="Sqlstore::Migration failed err: Error 1115: Unknown character set: 'utf8mb4'\n"

Sorry for the very old mysql-server, we can not update it at this moment since production is running on it.

[robsonsnjr]

Trying to upgrade from 4.1.0 to 4.3.2, db migration failed:

t=2017-06-08T11:31:53-0300 lvl=eror msg="Executing migration failed" logger=migrator id="Update dashboard table charset" error="Error 1071: Specified key was too long; max key length is 767 bytes"

lvl=eror msg="Exec failed" logger=migrator error="Error 1071: Specified key was too long; max key length is 767 bytes" sql="ALTER TABLE `dashboard` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, MODIFY `slug` VARCHAR(189) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL , MODIFY `title` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL , MODIFY `plugin_id` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL , MODIFY `data` MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL ;"

Using Mysql 5.6.24.

[shubhamod1]

Hey,
I started using Grafana with MySQL as datasource . But while quering data is coming on series mode . But i am not able to set the time range and ZOOM IN from UI . It says data-point outside the time range ,but they are in the time range.

[rymoore]

I've been running Grafana 4.6.2 against AWS Aurora (mysql) 5.6.10 and it's been working great. Recently, I tried switching to the Grafana 5.0 beta and I'm now getting the " Specified key was too long; max key length is 767 bytes" error. This thread isn't exceptionally clear, is there any way for me to migrate / resolve this issue?

[DanCech]

This has been fixed since b1 was released and will be patched in b2, in the meantime you can use the currently nightly build which includes the fix.

https://grafana.com/grafana/download/nightly

[nemosupremo]

I got hit with this bug too. I've been using grafana/grafana:latest on Docker, and latest now points to 5.0.0 beta 1.

[santi8ago8]

Same issue with 5.0.0 beta 2, I fix modifying the size (to 64) of the column name:

@nemosupremo @DanCech @rymoore

[bergquist]

Opening a new issue for the beta-2 problem. #10931

[xlson]

@nemosupremo @santi8ago8: what version of mysql are you using?

[santi8ago8]

@nemosupremo @xlson I'm using:

SHOW VARIABLES LIKE "%version%";

Variable_name	Value
innodb_version	5.6.36
protocol_version	10
slave_type_conversions
version	5.6.36-log
version_comment	MySQL Community Server (GPL)
version_compile_machine	x86_64
version_compile_os	Linux

[nemosupremo]

I'm using Amazon Aurora MySQL 5.6.10a. I'm assuming its compatible with its sister MySql version.

[xlson]

@nemosupremo @santi8ago8 Thanks for sharing.