ProxySQL does not propagate database warnings

[pali]

When SQL command cause warning on database server, then server sends in response packet indication with number of warnings. MySQL C library provides function mysql_warning_count which returns number of warnings from that response packet: https://dev.mysql.com/doc/refman/8.0/en/mysql-warning-count.html

If user's application wants to fetch those warnings, it needs to issue SHOW WARNINGS SQL command.

But ProxySQL always returns empty list of warnings when warning was issued from server. Basically SHOW WARNINGS from ProxySQL always returns empty result set, which means that number of rows returned by SHOW WARNINGS and mysql_warning_count() does not match.

Incorrect number is handled by SQL applications as server problem and close connection. In attachment test-warnings.c.gz is a testing C program. It passes when running directly against MySQL or MariaDB server and fails when running against ProxySQL. So problem is in ProxySQL.

Problem was detected by Perl's DBI database driver DBD::MariaDB used for connecting to the MySQL or MariaDB server.

DBD::MariaDB is not able to retrieve warning or errors from MySQL or MariaDB server when DBD::MariaDB is running against ProxySQL as ProxySQL does not propagate SHOW WARNINGS correctly.

---

Test result of the test-warnings.c.gz program:

When running directly against MariaDB server:

$ gcc test-warnings.c -o test-warnings -W -Wall -O2 `mysql_config --libs --cflags`
$ ./test-warnings 127.0.0.1 3306 pali pali test
Warning count from mysql_warning_count(): 1
Warning count from SHOW WARNINGS: 1

=== BEGIN: list of warnings ===
Level=Warning Code=1366 Message=Incorrect integer value: 'a' for column 'id' at row 1 
=== END: list of warnings ===

Number of warnings reported by mysql_warning_count and SHOW WARNINGS matches. And list of warnings really contain one warning.

When running against ProxySQL behind which is MariaDB server:

$ ./test-warnings 127.0.0.1 6033 pali pali test
Warning count from mysql_warning_count(): 1
Warning count from SHOW WARNINGS: 0
Error: warning count from mysql_warning_count() and SHOW WARNINGS does not match!!

=== BEGIN: list of warnings ===
=== END: list of warnings ===

Number of warnings reported by mysql_warning_count and SHOW WARNINGS are different (0 vs 1) and list of warnings is empty. But there should be one generated warning.

[pali]

This problem can be also demonstrated by mysql command line tool:

---

$ mysql -h 127.0.0.1 -P 3306 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15920
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> \W
Show warnings enabled.
MariaDB [test]> CREATE TABLE t(value INT);
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> INSERT INTO t VALUES ("a");
Query OK, 1 row affected, 1 warning (0.00 sec)

Warning (Code 1366): Incorrect integer value: 'a' for column 'value' at row 1
MariaDB [test]> SELECT @@warning_count;

+-----------------+
| @@warning_count |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

MariaDB [test]> SHOW WARNINGS;

+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'a' for column 'value' at row 1 |
+---------+------+----------------------------------------------------------+

1 row in set (0.00 sec)

---

$ mysql -h 127.0.0.1 -P 6033 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 152
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [test]> \W
Show warnings enabled.
MySQL [test]> CREATE TABLE t(value INT);
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> INSERT INTO t VALUES ("a");
Query OK, 1 row affected, 1 warning (0.00 sec)

MySQL [test]> SELECT @@warning_count;

+-----------------+
| @@warning_count |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

MySQL [test]> SHOW WARNINGS;
Empty set (0.00 sec)

---

As can be seen ProxySQL on port 6033 does not return warnings via SHOW WARNINGS. Also mysql client does not show warning even when \W was enabled. And all is working fine on port 3306 on which is running MariaDB server.

[jermlinden]

This still seems to be an issue in 2.4.2
If I had to pick one, I'd rather have the warning go to the log, but both would be even better.

Thanks