Proxysql log gives Table doesn't exist error.

[imad-kh]

hi
i got table not exist error on the proxysql log in this scenario :

proxy sql configured with only 1 backend server, one rule for that backend server is the default .so every query must be routed to that backend server .

queries on php script :

1. create a temporary table
2. insert into that table
3. select from that table.

i read some issues in here, it looks like it somehow related to mysql-multiplexing , and as i understand proxysql should detect temporary table queries and set mysql-multiplexing to false for that connection .
but it does not .

i have done some tests .
i set mysql-multiplexing to false , then it works as i expect it, but i don't want to sacrifice the benefits of mysql-multiplexing,

is there in way to work with temporary tables but have mysql-multiplexing enabled , it is ok to disabled it temporarily for the current session of the script .

thanks :)

[renecannao]

CREATE TEMPORARY TABLE should disable multiplexing in the connection where the table is created.
Can you please share a reproducible test case?
Thanks

[imad-kh]

hi,
well, when i was preparing a reproducible test for you to test with, i tried to make an example with php mysqli (mysqli_connect...)

and when i tested it it was working just as it should do, it worked !

BUT, i am using PHP-MySQLi-Database-Class, so i have this issue only when i use this class, as i can see it uses prepared statment, i guess this is the cause
https://github.com/joshcam/PHP-MySQLi-Database-Class

is proxysql fully supporting stmt ? or i am missing something ?

here are the two exempls , i hope to find any solution :

one more thing , when i test it in a virtual machine , it shows me strange behavior , it uses 1 session for both terminal client connection (mysql -h 127.0.0.1 -P 6033 ...) and the php script, that's resulting the temporary table to never destroyed and it gives an opposite error : table already existed
Example 1 (the one that have the issue):

<?php
include "MysqliDb.php";
$init_db = Array (
                'host' => '127.0.0.1',
                'username' => 'sqlproxy',
                'password' => 'test',
                'db'=> '',
                'port' => 6033,
                'prefix' => '',
                'charset' => 'utf8');

$db = new MysqliDb ($init_db);

$ress = $db->rawQuery(" select  CONNECTION_ID( )");
print_r($ress); //  in VM tests  it prints the same ID every time it executed


$res = $db->rawQuery("CREATE TEMPORARY  TABLE test.tst_table(id  int(11),name varchar(225)) ;");
$i=1;
while($i<=1){
        $res = $db->rawQuery("INSERT INTO test.tst_table values (1,'imad') ");
        $i+=1;
}
$res = $db->get("test.tst_table");
print_r($res);

Example 2 (worked perfectly) :

<?php

$link = mysqli_connect("127.0.0.1", "sqlproxy", "test", "",6033);

$result = mysqli_query ( $link , "select  CONNECTION_ID( ) ;");
if ( $output = mysqli_fetch_assoc ( $result))
        print_r($output); // and in this script it prints different ID




mysqli_query ( $link , "CREATE TEMPORARY  TABLE test.tst_table(id  int(11),name varchar(225)) ;");

mysqli_query ( $link , "INSERT INTO test.tst_table values (1,'imad')");

$result = mysqli_query ( $link , "SELECT * FROM test.tst_table");


while($output = mysqli_fetch_assoc ( $result))
        print_r($output);

thanks for the Support 👍

[renecannao]

Prepared statements are supported as protocol, but the logic that disable multiplexing in case of CREATE TEMPORARY TABLE is not in applied for prepared statements.
With no doubt, this is a bug in ProxySQL.
As there is a workaround (disable multiplexing globally) I will fix this bug in 1.4.1 .

Thank you very much for the report!!

[imad-kh]

ok , we are waiting for the 1.4.1 bug fix , Thanks
You welcome 😄

[renecannao]

This issue wasn't updated for long time.
The support for prepared statements has changed a lot.
We need to investigate if this is still an issue.
@pondix