Grafana and ProxySQL

Grafana Configuration
1

I have a MySQL Cluster setup using the Galera library, I provide access to the cluster through a ProxySQL server. (ProxySQL handles checking MySQL node health etc and sends queries to healthy nodes)

I am trying to configure Grafana to talk to ProxySQL for its database and it just refuses to work. If I point Grafana directly to one of the MySQL cluster members, it works fine. When I point it to the proxySQL server Grafana does not seem to finish starting, the log stops after:

t=2017-11-17T21:34:52+0000 lvl=info msg=“Initializing DB” logger=sqlstore dbtype=mysql
t=2017-11-17T21:34:52+0000 lvl=info msg=“Starting DB migration” logger=migrator

I tested the creds through ProxySQL with the MySQL client using the username/password/DB in the grafana config, works fine, I can select rows etc. I have numerous other applications using this DB cluster through ProxySQL.

I have the log level in Grafana set to trace, is there anything else I can do to see more information on what is going on behind the scenes?

The MySQL config in Grafana:

type = mysql
host = 10.10.10.52:3306
name = grafana_db
user = grafana
password = password

Any help appreciated, thanks!

Jon

2

The migration is trying to update the database schema. Is this a permissions problem? Can Grafana write to the database and change the schema when going through ProxySQL?

3

Yes, verified using the MySQL command line client connected through ProxySQL that I could insert data and create tables etc. using the creds configured in the Grafana config file, so I know access wise it works.

If I simply change the IP in the Grafana config file from the ProxySQL server to one of the MySQL nodes directly, it works fine. As stated all my other apps go through this same ProxySQL instance without issue, Grafana is the only one giving a problem here. That being said I cannot rule out that it is some edge case issue with ProxySQL that only Grafana is exploiting. From the MySQL side I may try to capture the queries being run so I can test further.

4

Looks like Grafana is running the following queries on startup:

SELECT @@max_allowed_packet;

SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=‘grafana_db’ and TABLE_NAME=‘migration_log’;

SELECT id, migration_id, sql, success, error, timestamp FROM migration_log;

SELECT ( SELECT COUNT() FROM user ) AS users, ( SELECT COUNT() FROM org ) AS orgs, ( SELECT COUNT() FROM dashboard ) AS dashboards, ( SELECT COUNT() FROM data_source ) AS datasources, ( SELECT COUNT() FROM playlist ) AS playlists, ( SELECT COUNT() FROM alert ) AS alerts, ( SELECT COUNT(*) FROM user where last_seen_at > ‘2017-11-06 15:19:25.556789’ ) as active_users;

I can manually run these statements against ProxySQL and everything works as expected.

5

So it looks like the ‘SELECT @@max_allowed_packet;’ part is not returning any data to Grafana and its getting stuck here as can be seem by these tcpdump comparisons:

SELECT @@max_allowed_packet;

10:19:01.479534 IP 10.166.9.57.42708 > 10.166.4.52.3306: Flags [P.], seq 157:189, ack 254, win 229, options [nop,nop,TS val 499757159 ecr 498421067], length 32
            0x0000:  4508 0054 0b73 4000 4006 0c71 0aa6 0939  E..T.s@.@..q...9
            0x0010:  0aa6 0434 a6d4 0cea f0ba 21c3 e12c 8247  ...4......!..,.G
            0x0020:  8018 00e5 e92f 0000 0101 080a 1dc9 b067  ...../.........g
            0x0030:  1db5 4d4b 1c00 0000 0353 454c 4543 5420  ..MK.....SELECT.
            0x0040:  4040 6d61 785f 616c 6c6f 7765 645f 7061  @@max_allowed_pa
            0x0050:  636b 6574                                cket
    10:19:01.480323 IP 10.166.4.52.3306 > 10.166.9.57.42708: Flags [P.], seq 254:337, ack 189, win 227, options [nop,nop,TS val 498517692 ecr 499757159], length 83
            0x0000:  4500 0087 c1f0 4000 4006 55c8 0aa6 0434  E.....@.@.U....4
            0x0010:  0aa6 0939 0cea a6d4 e12c 8247 f0ba 21e3  ...9.....,.G..!.
            0x0020:  8018 00e3 2332 0000 0101 080a 1db6 c6bc  ....#2..........
            0x0030:  1dc9 b067 0100 0001 012a 0000 0203 6465  ...g.....*....de
            0x0040:  6600 0000 1440 406d 6178 5f61 6c6c 6f77  f....@@max_allow
            0x0050:  6564 5f70 6163 6b65 7400 0c3f 0015 0000  ed_packet..?....
            0x0060:  0008 a080 0000 0005 0000 03fe 0000 0200  ................
            0x0070:  0a00 0004 0931 3334 3231 3737 3238 0500  .....134217728..
            0x0080:  0005 fe00 0002 00                        .......
    10:19:01.480978 IP 10.166.9.57.42708 > 10.166.4.52.3306: Flags [.], ack 337, win 229, options [nop,nop,TS val 499757161 ecr 498517692], length 0
            0x0000:  4508 0034 0b74 4000 4006 0c90 0aa6 0939  E..4.t@.@......9
            0x0010:  0aa6 0434 a6d4 0cea f0ba 21e3 e12c 829a  ...4......!..,..
            0x0020:  8010 00e5 7656 0000 0101 080a 1dc9 b069  ....vV.........i
            0x0030:  1db6 c6bc

Starting Grafana:

10:19:29.956701 IP 10.166.9.57.37700 > 10.166.4.52.6033: Flags [P.], seq 104:136, ack 90, win 229, options [nop,nop,TS val 499785637 ecr 498546168], length 32
        0x0000:  4500 0054 c7c2 4000 4006 5029 0aa6 0939  E..T..@.@.P)...9
        0x0010:  0aa6 0434 9344 1791 fdfa d885 aa97 4344  ...4.D........CD
        0x0020:  8018 00e5 4bc1 0000 0101 080a 1dca 1fa5  ....K...........
        0x0030:  1db7 35f8 1c00 0000 0353 454c 4543 5420  ..5......SELECT.
        0x0040:  4040 6d61 785f 616c 6c6f 7765 645f 7061  @@max_allowed_pa
        0x0050:  636b 6574                                cket
10:19:29.996216 IP 10.166.4.52.6033 > 10.166.9.57.37700: Flags [.], ack 136, win 227, options [nop,nop,TS val 498546208 ecr 499785637], length 0
        0x0000:  4500 0034 771f 4000 4006 a0ec 0aa6 0434  E..4w.@.@......4
        0x0010:  0aa6 0939 1791 9344 aa97 4344 fdfa d8a5  ...9...D..CD....
        0x0020:  8010 00e3 22df 0000 0101 080a 1db7 3620  ....".........6.
        0x0030:  1dca 1fa5

The query is the same, but when Grafana sends it no answer is given back, not sure what it’s doing different.

6

Quickly googled this and looks like there are some difference between MySQL and ProxySQL when it comes to global variables:

Looks like global variable max_allowed_packet is called mysql-max_allowed_packet.

Not really sure how to solve this though.

7

Yes ProxySQL has its own values for many settings such as max allowed packet, when running queries like above you see the value from MySQL as the query is proxied through. I do have the ProxySQL mysql-max_allowed_packet variable set to match. I’ll take this issue over to the ProxySQL support forms, I am having a feeling there might be a bug at play here.

1 Like
8

So it looks like this is an issue in ProxySQL that Grafana 4.5.x exploited:

Thanks for your help.