search

grafana / database-migrator

Code to export grafana.db (sqlite) to MySQL-compatible SQL file, to assist in migration of Grafana data to MySQL-compatible DB.
Apache License 2.0
97 stars 15 forks source link

Timezone identifiers exported on come columns causing error on mysql/mariadb import. #11

Open judilsteve opened 2 years ago

judilsteve commented 2 years ago

I'm using this script to migrate a Grafana 6.6.1 installation from SQLite to MariaDB. When I attempt to import the dump with the mysql client I get the following error:

ERROR 1292 (22007) at line 368: Incorrect datetime value: '2022-07-12 08:16:10.96344947+00:00' for column `grafana`.`alert`.`new_state_date` at row 1

It looks like this column (and potentially others) has been set up to print timezone info when selected, making the string literals incompatible with the mysql/mariadb DATETIME type.

A simple find/replace of +00:00 with an empty string fixed this for my case, but it might be worth integrating this into the script or at least calling it out in the README.

erdong commented 1 year ago

I had the same problem when I'm using this script to migrate a Grafana 9.3.2 installation from SQLite to MySQL 。

A simple find/replace of +00:00（ and +08:00） with an empty string fixed this for my case。It work ok。

Write it down。

Thanks judilsteve 。

kiraniyer8 commented 1 year ago

For folks who visit this later and want a handy command, this can be useful: sed -i 's/\+00:00//g' db_dump.sql

TheAlienKnight commented 2 months ago

Thanks for this, I was trying to understand why it was failing, and this filled in the gap of knowledge for me. Removing the additional -04:00 in my case resolved the issue.