Awk Issues on Linux

[bedford10]

I've had some issues using this project to migrate my sqlite database to mysql.

Grafana Version: 9.1.6 SQLite Version: 3.35.5

When running the sqlitedump.sh script, it generates this output:

TRUNCATE TABLE alert;
TRUNCATE TABLE ngalert_configuration;
TRUNCATE TABLE alert_configuration;
TRUNCATE TABLE org;
TRUNCATE TABLE alert_instance;
TRUNCATE TABLE org_user;
TRUNCATE TABLE alert_notification;
TRUNCATE TABLE permission;
TRUNCATE TABLE alert_notification_state;
TRUNCATE TABLE playlist;
TRUNCATE TABLE alert_rule;
TRUNCATE TABLE playlist_item;
TRUNCATE TABLE alert_rule_tag;
TRUNCATE TABLE plugin_setting;
TRUNCATE TABLE alert_rule_version;
TRUNCATE TABLE preferences;
TRUNCATE TABLE annotation;
TRUNCATE TABLE quota;
TRUNCATE TABLE annotation_tag;
TRUNCATE TABLE role;
TRUNCATE TABLE api_key;
TRUNCATE TABLE seed_assignment;
TRUNCATE TABLE builtin_role;
TRUNCATE TABLE server_lock;
TRUNCATE TABLE cache_data;
TRUNCATE TABLE session;
TRUNCATE TABLE dashboard;
TRUNCATE TABLE short_url;
TRUNCATE TABLE dashboard_acl;
TRUNCATE TABLE star;
TRUNCATE TABLE dashboard_provisioning;
TRUNCATE TABLE tag;
TRUNCATE TABLE dashboard_snapshot;
TRUNCATE TABLE team;
TRUNCATE TABLE dashboard_tag;
TRUNCATE TABLE team_member;
TRUNCATE TABLE dashboard_version;
TRUNCATE TABLE team_role;
TRUNCATE TABLE data_keys;
TRUNCATE TABLE temp_user;
TRUNCATE TABLE data_source;
TRUNCATE TABLE test_data;
TRUNCATE TABLE kv_store;
TRUNCATE TABLE user;
TRUNCATE TABLE library_element;
TRUNCATE TABLE user_auth;
TRUNCATE TABLE library_element_connection;
TRUNCATE TABLE user_auth_token;
TRUNCATE TABLE login_attempt;
TRUNCATE TABLE user_role;
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();
INSERT INTO `` () VALUES ();

I've managed to narrow it down to the escape.awk script that gets used in the last awk section of the sqlitedump script. I have verified that the sqlite DB I'm testing against contains data.

[wally007]

@bedford10 , i got the same issue - did you ever figure it out ?

I'm on latest CentOS 7.9 and Grafana 9.3.1 and gawk version gawk-4.0.2-4.el7_3.1.x86_64

[bedford10]

@bedford10 , i got the same issue - did you ever figure it out ?

I'm on latest CentOS 7.9 and Grafana 9.3.1 and gawk version gawk-4.0.2-4.el7_3.1.x86_64

I ended up removing the awk call and wrote a super hackish python script to catch and replace what was known bad in my database. It works since our grafana DB is mainly configuration to query external data sources.

[wally007]

thanks for response.

I fixed it by moving the db and the script to the RHEL8.6 host and doing to conversion there. RHEL 8.6 has a newer awk (4.0 vs 4.2)

Worked without issues so far

[davolia]

Hello

I have same problem. I have changed awk to gawk in my bash script but, unfortunately it didn't help. Please help me to solve this problem.

Centos version: 7.9.2009 SQlite version: 3.7.17 Grafana version: 9.3.2 Awk version: 4.0.2

When I ran the script, I receive same error. Line in my grafana.sql looks like this.

INSERT INTO `` () VALUES ();

[wally007]

you can wait and maybe grafana will fix it or install rhel 8.6 (or almalinux/rocky linux) and it will work without any issues

[VergeDX]

Same issues here, I run the script in host's ubuntu podman container.

[evan-chaney]

This worked for me using gawk on Mac from brew. My (g)awk version is 5.2.1. Perhaps the script is using some 4.2+ or 5.0+ specific syntax?

[annelaurefroment]

The issue I had is that.headers on doesn't work with .mode insert on sqlite 3.7.17 which is the latest version on on RHEL 7.9. After I forced the upgrade to 3.31.1 (using this tutorial) it worked for me.

Here is the result of

.headers on
.mode insert org_user
select * from org_user;

with sqlite 3.7.17: INSERT INTO org_user(id,org_id,user_id,role,created,updated) VALUES(1,1,1,'Admin','2022-06-05 14:04:19','2022-06-05 14:04:19');

and sqlite 3.31.1: INSERT INTO org_user VALUES(1,1,1,'Admin','2021-11-17 10:25:16','2021-11-17 10:25:16');

As you can see, in this version we're missing the columns, that's why the regex in escape.awk isn't working. So fixing the regex won't help, as you're missing important information that needs to be in the insert command.

[nickidw]

Have the same issue as the original poster, empty insert statements.

[nickidw]

The issue appears to be that the sqlite insert mode does not generate a column specification for the insert, causing no match to take place. The only purpose of the awk script is to cater for column specification, so it can be removed from the end of the script.

[nickidw]

To confirm: edit sqlitedump.sh and change the last line from done | sqlite3 $DB | sed -e 's/\\[rnut"]/\\&/g' | awk -f escape.awk to done | sqlite3 $DB | sed -e 's/\\[rnut"]/\\&/g'

I was able to successfully migrate data from sqlite to mysql after this change.