Proxysql: packets out of order. expected 1 received 27

[Barbery]

Hi, in my production environment, if I run some migration file(create table or alter field), sometimes PHP will throw the exception "packets out of order. expected xx received xx".
And I found that:

1. if I restart the service of proxysql, all if fine, but I need to restart every time when I deploy.
2. if I change PDO::ATTR_EMULATE_PREPARES => true , all is fine, but that will make all int type force cover to string

Is something way to solve this problem and don't force cover int to string?

[renecannao]

Hi @Barbery .
This sounds like an issue related to prepared statements metadata.
Can you provide a reproducible test case?
Thanks

[Barbery]

Hi @renecannao ,
I try to reproduce the error, but it seems to happen randomly.

[hellracer]

I've seen this in laravel application specifically using percona DB. I'd try to switch to mysql 5.6 and can't reproduce this.

PDO::ATTR_EMULATE_PREPARES => true

still the workaround I use since I can't use mysql plain, for political reason.

[jmillerarvig]

I'm also running into this issue at my workplace, and also unable to reliably reproduce it. We have three web servers behind a load balancer, where each web server is running Laravel and using its own instance of proxysql to connect to a Percona database cluster of 3 database servers. The issue has occurred exactly 6 times on each web server over the last week; it had previously occurred a handful of times in May, and then not at all for about 2 months.

This most recent set of errors occurred after a Laravel database migration, though we have no idea why this migration would cause these "packets out of order" errors (so maybe the migration didn't actually have anything to do with the errors).

[jasperjorna]

We are experiencing the same problem and also have trouble debugging it.

Our setup consists of 2 web servers running Laravel 5.6, with ProxySQL on each of them. They connect with 3 MySQL 5.7 servers where one of them acts as the master.

It seems to occur after Laravel ran a database migration and queries are being run on migrated tables afterwards. As soon as I restarted both instances of ProxySQL the errors disappeared.

[robdewit]

We've seen this happen twice right after a Laravel migration. Code that Laravel was executing is nothing special and problems disappear immediately after rolling back the schema update:

alter table `media` add `expire_at` timestamp null comment 'Stores the ttl for the media item'

Sounds to me as if ProxySQL and the underlying Percona XtraDB Cluster have different ideas about the size of the records after the migration. Could there be a way that Laravel constructs the query that makes ProxySQL miss flushing some cache? (I have not read the code, just voicing some thoughts)

[robdewit]

Having had a brief look at the code, it looks like queries are cached and only purged based on their TTL or when the maximum amount of cache memory is reached.

Ideally there should be some code that discards cached queries that use tables that are used in an 'ALTER' statement. Code that would just flush all caches on an 'ALTER' statement would also suffice.

[cofyc]

hi, @renecannao

I crafted a reproducible test case here: https://gist.github.com/cofyc/6608768c353e208428afef12dda01cb7

PTAL

1, start MySQL 5.6

docker run -it --net host -e MYSQL_ROOT_PASSWORD=root mysql:5.6

2, start a proxysql (2.0.9)

proxysql -c proxysql.conf -f

(this bug also exists in proxysql 2.0.14)

3, run test script with php

php -f test.php

https://gist.github.com/cofyc/6608768c353e208428afef12dda01cb7#file-test-php

4，change the schema of the table to trigger the error

mysql -h 127.0.0.1 -P6033 -uroot -proot -e 'alter table test.sbtest10 add f11 bigint AFTER id;'

5, check the error logs

proxysql outputs the follow error message:

2020-09-23 11:30:41 MySQL_PreparedStatement.cpp:386:update_metadata(): [WARNING] Updating metadata for stmt 11 , user root, query select * from sbtest10 where f1 = ?
2020-09-23 11:30:41 MySQL_Session.cpp:3388:handler(): [ERROR] RECEIVED AN UNKNOWN COMMAND: 28 -- PLEASE REPORT A BUG

php -f test.php outputs the follow error message:

2, Packets out of order. Expected 1 received 5. Packet size=5, /data/testing/taptap/proxysql/test.php, 42

[mjbnz]

proxysql outputs the follow error message:

2020-09-23 11:30:41 MySQL_PreparedStatement.cpp:386:update_metadata(): [WARNING] Updating metadata for stmt 11 , user root, query select * from sbtest10 where f1 = ?
2020-09-23 11:30:41 MySQL_Session.cpp:3388:handler(): [ERROR] RECEIVED AN UNKNOWN COMMAND: 28 -- PLEASE REPORT A BUG

It does indeed look like the switch statement here is missing a case for _MYSQL_COM_STMT_FETCH (int value 28 in the enum).

[haihq88]

I got the same problem with Laravel migration to alter add new columns
proxysql version: proxysql-2.0.15-1.x86_64 (2.0.15-20-g32bb92c)
OS: Centos 7
Mysql backend: mysql-community-server-5.7

[haihq88]

@renecannao
Could you please help to take a look?
Thank you!
For now i have to set fast_forward=1 in mysql_users to workaround this problem.

[renecannao]

Cross reference to #1128 .
ProxySQL informs the client that there is no cursors (https://github.com/sysown/proxysql/commit/62180d72216cde8a998c5010d2e7271f63253823) , but the client (Laravel) seems to ignore that and tries to use the cursor anyway.
This seems a Laravel bug. We will try to reproduce it, and if confirmed we will open a bug report with Laravel (or find a workaround)

[mjbnz]

ProxySQL informs the client that there is no cursors

Purely out of interest, why does ProxySQL not support client side cursors? is it a technical limitation, or just not implemented?

This seems a Laravel bug. We will try to reproduce it, and if confirmed we will open a bug report with Laravel (or find a workaround)

Laravel's Database access is via Eloquent ORM, which is using Doctrine underneath, which directly uses php mysqli functions, if that helps. https://github.com/doctrine/dbal/