Grafana 4.2.0から4.3.1へのUpgradeで"Specified key was too long; max key length is 767 bytes"エラー


2017/05/23 4.3.1がstableでリリースされました。
https://grafana.com/blog/2017/05/23/grafana-4.3-release/
データソースにMySQLが入ったり（※アルファ）、Elasticsearchソースでhistgramがのパネル入ったりと楽しみです。
(ヒートマップパネルをよくわかっていない…)

upgrade後のmigrationで若干エラーがあったため、メモ書きとして残します。

# 環境
||バージョン|
|:---|:---|
|Amazon Linux|2014.09|
|MySQL|5.6.21|
|grafana|4.2.0 → 4.3.1|

# 状況
- grafana4.2.0を運用中
- データはMySQL管理
- yumでupgrade

## 調査
- yumでupgrade後にgrafana-serverを再起動。  
しかしFAILEDとなってしまった。

```
# /etc/init.d/grafana-server restart
Stopping Grafana Server: ...                               [  OK  ]
Starting Grafana Server: ...                               [  OK  ]
FAILED
```

- ログ調査

```/var/log/grafana/grafana.log
t=2017-05-24T11:37:07+0900 lvl=info msg="Executing migration" logger=migrator id="Update user table charset"
t=2017-05-24T11:37:07+0900 lvl=info msg="Executing migration" logger=migrator id="Update temp_user table charset"
t=2017-05-24T11:37:07+0900 lvl=eror msg="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"
t=2017-05-24T11:37:07+0900 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 ;"
t=2017-05-24T11:37:07+0900 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"
```

丁寧にsqlが出ていて優しい。
temp_userテーブルのALTERに失敗しており、エラーメッセージがわかる

## "Specified key was too long; max key length is 767 bytes"とは？
> By default, the index key prefix length limit is 767 bytes

767byteを超えるUNIQUE KEYの作成ができない。
innodb_large_prefixをONにし、テーブルのRow_formatを"DYNAMIC"or"COMPRESSED"にすることで3072byteまで設定できるようになる

- 参考  
https://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html  
https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

# 対応
### 1. my.cnfに下記を追加しMySQL再起動

```my.cnf
innodb_large_prefix=ON
innodb_file_format=Barracuda
innodb_file_format_max=Barracuda
```

### 2. 対象のテーブルのROW_FORMATを変更する
結局下記3テーブルを対応(DB:grafana)

|対象テーブル|SQL|
|:---|:---|
|temp_user|ALTER TABLE temp_user ROW_FORMAT=DYNAMIC;|
|dashboard|ALTER TABLE dashboard ROW_FORMAT=DYNAMIC;|
|alert|ALTER TABLE alert ROW_FORMAT=DYNAMIC;|


```確認サンプル
mysql> SHOW TABLE STATUS like 'dashboard'\G
*************************** 1. row ***************************
           Name: dashboard
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 21
 Avg_row_length: 60074
    Data_length: 1261568
Max_data_length: 0
   Index_length: 65536
      Data_free: 4194304
 Auto_increment: 44
    Create_time: 2016-07-13 11:07:01
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> ALTER TABLE dashboard ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW TABLE STATUS like 'dashboard'\G
*************************** 1. row ***************************
           Name: dashboard
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 22
 Avg_row_length: 72238
    Data_length: 1589248
Max_data_length: 0
   Index_length: 65536
      Data_free: 0
 Auto_increment: 44
    Create_time: 2017-05-24 12:01:03
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment:
```

### 3. grafana起動

```
# /etc/init.d/grafana-server start
Starting Grafana Server: ...                               [  OK  ]
# tail  /var/log/grafana/grafana.log
t=2017-05-24T12:07:12+0900 lvl=info msg="Executing migration" logger=migrator id="Update annotation table charset"
t=2017-05-24T12:07:12+0900 lvl=info msg="Executing migration" logger=migrator id="Add column region_id to annotation table"
t=2017-05-24T12:07:12+0900 lvl=info msg="Executing migration" logger=migrator id="create test_data table"
t=2017-05-24T12:07:12+0900 lvl=info msg="Starting plugin search" logger=plugins
t=2017-05-24T12:07:12+0900 lvl=info msg="Registering plugin" logger=plugins name=Clock
t=2017-05-24T12:07:12+0900 lvl=info msg="Registering plugin" logger=plugins name=Diagram
t=2017-05-24T12:07:12+0900 lvl=info msg="Initializing Alerting" logger=alerting.engine
t=2017-05-24T12:07:12+0900 lvl=info msg="Initializing CleanUpService" logger=cleanup
t=2017-05-24T12:07:12+0900 lvl=info msg="Initializing Stream Manager"
t=2017-05-24T12:07:12+0900 lvl=info msg="Initializing HTTP Server" logger=http.server address=0.0.0.0:3000 protocol=https subUrl= socket=
```

完了！

# まとめ
Grafanaの進化が止まらない。