MySQL Connector incorrectly reports SQL warning after a successful SQL statement (which app throws as an error)

[ronaldbradford]

Describe the problem

Our [Go] application is throwing an ERROR because it checks MySQL Warnings after SQL queries. I don't feel its a ReadySet error, more likely the (noria) adaptor error.

MySQL Docs: SHOW WARNINGS

displays information about the conditions resulting from execution of the most recent nondiagnostic statement in the current session.

To Reproduce

Reproducible behavior:

1. Set up ReadySet (docker example) UPSTREAM_URL="mysql://${DBA_USER}:${DBA_PASSWD}@${INSTANCE_ENDPOINT}:${PORT}"
2. Connect via MySQL client mysql -u$DBA_USER -p$DBA_PASSWD -h127.0.0.1 -P3307
3. Run the following statements.

USE airport;
SELECT DISTINCT region,sub_region FROM country; /* This is supposed to error */
SHOW WARNINGS;
SELECT /* Select countries with a specific region and sub-region */ country_id, name, capital FROM location.country WHERE region = 'Europe' AND sub_region = 'Southern Europe'; /* Works as expected */
SHOW WARNINGS; /* THIS SHOULD BE EMPTY */

You can setup these two demo schemas via these associated queries.
https://github.com/ronaldbradford/data/blob/main/schema/airport/mysql/02-tables.sql
https://github.com/ronaldbradford/data/blob/main/schema/location/mysql/02-tables.sql

Expected behavior

This issue is actually uncovered with the Go connector, but I can now reproduce via MySQL client.
NOTE: It's reproducible every time now, but previously (prior days) and even earlier after a pull update it didn't present, so it's "inconsistent"

This is expected behavior using the same mysql client connected directly to RDS.

$ mysql -h${INSTANCE_ENDPOINT} -u${DBA_USER} -p${DBA_PASSWD}
15:30:23 (8.0.40) [(none)]  > USE airport;
Database changed
15:30:37 (8.0.40) [airport]  > SELECT DISTINCT region,sub_region FROM country;
ERROR 1146 (42S02): Table 'airport.country' doesn't exist
15:31:11 (8.0.40) [airport]  > SHOW WARNINGS;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1146 | Table 'airport.country' doesn't exist |
+-------+------+---------------------------------------+
1 row in set (0.00 sec)

15:31:14 (8.0.40) [airport]  > SELECT /* Select countries with a specific region and sub-region */ country_id, name, capital FROM location.country WHERE region = 'Europe' AND sub_region = 'Southern Europe';
+------------+------------------------+------------------+
| country_id | name                   | capital          |
+------------+------------------------+------------------+
| AD         | Andorra                | Andorra la Vella |
| AL         | Albania                | Tirana           |
| BA         | Bosnia and Herzegovina | Sarajevo         |
| ES         | Spain                  | Madrid           |
| GI         | Gibraltar              | Gibraltar        |
| GR         | Greece                 | Athens           |
| HR         | Croatia                | Zagreb           |
| IT         | Italy                  | Rome             |
| ME         | Montenegro             | Podgorica        |
| MK         | North Macedonia        | Skopje           |
| MT         | Malta                  | Valletta         |
| PT         | Portugal               | Lisbon           |
| RS         | Serbia                 | Belgrade         |
| SI         | Slovenia               | Ljubljana        |
| SM         | San Marino             | San Marino       |
| VA         | Holy See               | Vatican City     |
| XK         | Kosovo                 | Pristina         |
+------------+------------------------+------------------+
17 rows in set (0.00 sec)

15:31:25 (8.0.40) [airport]  > SHOW WARNINGS;
Empty set (0.00 sec)

ReadySet behaviour

$  mysql -u$DBA_USER -p$DBA_PASSWD -h127.0.0.1  -P3307

mysql> SHOW READYSET VERSION;
+--------------------+------------------------------------------+
| ReadySet           | Version Information                      |
+--------------------+------------------------------------------+
| release version    | stable-250123                            |
| commit id          | 804128f2ccf859a0e1efb281aceeaba0d34b15ae |
| platform           | x86_64-unknown-linux-gnu                 |
| rustc version      | rustc 1.84.0 (9fc6b4312 2025-01-07)      |
| profile            | release                                  |
| optimization level | 3                                        |
+--------------------+------------------------------------------+
6 rows in set (0.00 sec)

mysql> USE airport;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT DISTINCT region,sub_region FROM country;
ERROR 1146 (42S02): Table 'airport.country' doesn't exist
mysql>  SHOW WARNINGS;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1146 | Table 'airport.country' doesn't exist |
+-------+------+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT /* Select countries with a specific region and sub-region */ country_id, name, capital FROM location.country WHERE region = 'Europe' AND sub_region = 'Southern Europe';
+------------+------------------------+------------------+
| country_id | name                   | capital          |
+------------+------------------------+------------------+
| AD         | Andorra                | Andorra la Vella |
| AL         | Albania                | Tirana           |
| BA         | Bosnia and Herzegovina | Sarajevo         |
| ES         | Spain                  | Madrid           |
| GI         | Gibraltar              | Gibraltar        |
| GR         | Greece                 | Athens           |
| HR         | Croatia                | Zagreb           |
| IT         | Italy                  | Rome             |
| ME         | Montenegro             | Podgorica        |
| MK         | North Macedonia        | Skopje           |
| MT         | Malta                  | Valletta         |
| PT         | Portugal               | Lisbon           |
| RS         | Serbia                 | Belgrade         |
| SI         | Slovenia               | Ljubljana        |
| SM         | San Marino             | San Marino       |
| VA         | Holy See               | Vatican City     |
| XK         | Kosovo                 | Pristina         |
+------------+------------------------+------------------+
17 rows in set (0.00 sec)

mysql>  SHOW WARNINGS;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1146 | Table 'airport.country' doesn't exist |
+-------+------+---------------------------------------+
1 row in set (0.00 sec)

FYI, query is cached

mysql> SHOW CACHES WHERE query_id='q_d59fbd81771430ea'\G
*************************** 1. row ***************************
         query id: q_d59fbd81771430ea
       cache name: q_2a35efe29f560359
       query text: SELECT `location`.`country`.`country_id`, `location`.`country`.`name`, `location`.`country`.`capital` FROM `location`.`country` WHERE ((`location`.`country`.`region` = $1) AND (`location`.`country`.`sub_region` = $2))
fallback behavior: fallback allowed
            count: 0
1 row in set (0.01 sec)

Environment

• ReadySet version

+--------------------+------------------------------------------+
| ReadySet           | Version Information                      |
+--------------------+------------------------------------------+
| release version    | stable-250123                            |
| commit id          | 804128f2ccf859a0e1efb281aceeaba0d34b15ae |
| platform           | x86_64-unknown-linux-gnu                 |
| rustc version      | rustc 1.84.0 (9fc6b4312 2025-01-07)      |
| profile            | release                                  |
| optimization level | 3                                        |
+--------------------+------------------------------------------+

• ReadySet deployment method [e.g, local orchestrator, Helm chart, binaries]

$ docker images | grep readyset
readysettech/readyset           latest    fc149fc2bcfb   24 hours ago   503MB
readysettech/readyset           <none>    4802dc96f1ef   6 weeks ago    504MB

• Upstream database and version [e.g., Postgres 14]

AWS
            "Engine": "mysql", 
            "EngineVersion": "8.0.40",
           "DBInstanceArn": "arn:aws:rds:us-east-2:414340713341:db:mysql-cable",

• Client app [e.g., psql, mysql, SQLAlchemy, TypeORM]

AL2 + MySQL Client (to reproduce)

$ uname -a
Linux marvin 4.14.355-275.570.amzn2.x86_64 #1 SMP Sat Nov 30 09:51:35 UTC 2024 x86_64 x86_64 x86_64 GNU/Linux
$ mysql --version
mysql  Ver 8.0.40 for Linux on x86_64 (MySQL Community Server - GPL)

Problem is actually discovered from Go MySQL Adaptor, however this doesn't happen with direct connection to same RDS instance.

github.com/go-sql-driver/mysql v1.8.1

Additional context

No actual impact on ReadySet, just the application that catches warnings and reports these are ERRORS, when in this case it's not an error.

[altmannmarcelo]

@ronaldbradford first of all, thanks for taking the time to test and report this bug.
On a quick look, it seems like we are reporting the same error as before, meaning that the issue here is that we are not cleaning the warning after executing the next query.

We will look into it.

[ronaldbradford]

I've not looked at any of the source code, but yes, it presents as not being cleared. As I can see it in two sources (mysql client and Go), I'll consider the RC is upstream. The crazy thing is the warning does clear eventually.
I'm happy to share my SQL test suite, its a goal to make it OSS anyway at some time.

At this time, query 14 and query 21 fail intentionally (55 queries total). In this use case there are no queries that should throw warnings, an error in my code never sees the subsequent warning.

14 fails
15,16 same warning which is in error????
17-20 work as expected
21 fails different error (but same cause, mismatched schema, so table is missing)
22-25 same second warning which is error??
26+ work

but then the rest operate as expected. Go figure?

More apps are becoming aware of 2 decades of ignoring MySQL warnings (which are generally bad and should be errors)