Grafana(5.2.3) backend MySQL(5.7) start failure

Hello,

I am trying to bring up Grafana(5.2.3) with MySQL as backend on Windows 64-bit machine, where i followed the steps to configure, but failed multiple times:

  • Configured default.ini with MySQL as backend

  • Created database ‘grafana’ on MySQL

  • Started Grafana-server.exe process

Enabled debug to print queries in the log file.

I encouter an error in the log saying that:

t=2018-09-19T08:36:43+0200 lvl=info msg=“Executing migration” logger=migrator id=“Update temp_user table charset”
t=2018-09-19T08:36:43+0200 lvl=info msg="[SQL] ALTER TABLE temp_user DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, MODIFY email VARCHAR(190) 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(190) 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 ; - took: 1.037ms" logger=sqlstore.xorm
t=2018-09-19T08:36:43+0200 lvl=eror msg=“Executing migration failed” logger=migrator id=“Update temp_user table charset” error=“Error 7: Error on rename of ‘.\grafana\#sql-2db8_11.frm’ to ‘.\grafana\temp_user.frm’ (Errcode: 13 - Permission denied)”
t=2018-09-19T08:36:43+0200 lvl=eror msg=“Exec failed” logger=migrator error=“Error 7: Error on rename of ‘.\grafana\#sql-2db8_11.frm’ to ‘.\grafana\temp_user.frm’ (Errcode: 13 - Permission denied)” sql=“ALTER TABLE temp_user DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, MODIFY email VARCHAR(190) 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(190) 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=2018-09-19T08:36:43+0200 lvl=info msg="[SQL] INSERT INTO migration_log (migration_id,sql,success,error,timestamp) VALUES (?, ?, ?, ?, ?) []interface {}{“Update temp_user table charset”, “ALTER TABLE temp_user DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, MODIFY email VARCHAR(190) 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(190) 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 ;”, false, “Error 7: Error on rename of ‘.\\grafana\\#sql-2db8_11.frm’ to ‘.\\grafana\\temp_user.frm’ (Errcode: 13 - Permission denied)”, “2018-09-19 08:36:43”} - took: 1.0566ms" logger=sqlstore.xorm
t=2018-09-19T08:36:43+0200 lvl=eror msg=“Server shutdown” logger=server reason=“Service init failed: Migration failed err: Error 7: Error on rename of ‘.\grafana\#sql-2db8_11.frm’ to ‘.\grafana\temp_user.frm’ (Errcode: 13 - Permission denied)”

Adding more info to error specifics:

Found the error log in database migration_log table:

‘Error 7: Error on rename of ‘’.\grafana#sql-2db8_11.frm’’ to ‘’.\grafana\temp_user.frm’’ (Errcode: 13 - Permission denied)’

‘ALTER TABLE temp_user DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, MODIFY email VARCHAR(190) 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(190) 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 ;’

Hope this helps to solve this issue soon :).

Resolved the initialisation issue by executing queries with VARCHAR(255) wherever string is used:

First failure resolution:

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 ;

Second failure resolution:

ALTER TABLE annotation DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, MODIFY type VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL , MODIFY title TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL , MODIFY text TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL , MODIFY metric VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL , MODIFY prev_state VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL , MODIFY new_state VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL , MODIFY data TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL ;