Hi, i have a problem with laravel and ProxySQL latest version. I saw that ProxySQL has support for prepared statements and it has a table that shows all the queries but when i add a rule to ProxySQL the laravel’s queries not cached. I have tested many things to solve it without any luck and the only way that i found is to enable the “PDO::ATTR_EMULATE_PREPARES” in laravel. Does anyone know if there is a solution without to enable “PDO::ATTR_EMULATE_PREPARES”. Thanks.
Hello @netadmin,
In order to assist we need to see ProxySQL logs, and the rules you created. In general, unless you are re-executing a SELECT statement many 1000s of times, prepared statements will not gain you any performance improvements. Also, prepared statements do not fully protect you from SQL injection attacks; they are a minor mitigation at best.
Hello @matthewb and thank you for your reply. I have tried many rules with digest because i believe it is more accurate and the routing works, for example i have tries with:
INSERT INTO mysql_query_rules(active, username, match_digest, cache_ttl, apply,destination_hostgroup) VALUES(1,'root','0xb26abdf5cde3e4f0',100000,1,0);
It matches the route and the caching is not working until to enable the ATTR_EMULATE_PREPARES and i don’t want to enable the ATTR_EMULATE_PREPARES because i read that returns all the integers as strings. My Logs are:
2024-08-07 14:24:09 [INFO] Creating new server in HG 0 : innodb-cluster-1.innodb-cluster-instances.production.svc.cluster.local:3306 , gtid_port=0, weight=1, status=0
2024-08-07 14:24:09 [INFO] Creating new server in HG 1 : innodb-cluster-0.innodb-cluster-instances.production.svc.cluster.local:3306 , gtid_port=0, weight=1, status=0
2024-08-07 14:24:09 [INFO] Creating new server in HG 1 : innodb-cluster-2.innodb-cluster-instances.production.svc.cluster.local:3306 , gtid_port=0, weight=1, status=0
2024-08-07 14:24:09 [INFO] New mysql_group_replication_hostgroups table
2024-08-07 14:24:09 [INFO] New mysql_galera_hostgroups table
2024-08-07 14:24:09 [INFO] New mysql_aws_aurora_hostgroups table
2024-08-07 14:24:09 [INFO] New mysql_hostgroup_attributes table
2024-08-07 14:24:09 [INFO] New mysql_servers_ssl_params table
2024-08-07 14:24:09 [INFO] Checksum for table mysql_servers_v2 is 0x7A8727AF3E4A887F
2024-08-07 14:24:09 [INFO] Checksum for table mysql_replication_hostgroups is 0x477193CA2C3CA3A7
2024-08-07 14:24:09 [INFO] New computed global checksum for 'mysql_servers_v2' is '0x92D458ED15268454'
2024-08-07 14:24:09 [INFO] Checksum for table mysql_servers is 0x7A8727AF3E4A887F
2024-08-07 14:24:09 [INFO] Rebuilding 'Hostgroup_Manager_Mapping' due to checksums change - mysql_servers { old: 0x0, new: 0x3E4A887F7A8727AF }, mysql_replication_hostgroups { old:0x0, new:0x477193CA2C3CA3A7 }
2024-08-07 14:24:09 [INFO] MySQL_HostGroups_Manager::commit() locked for 1ms
2024-08-07 14:24:09 [INFO] Computed checksum for 'LOAD PROXYSQL SERVERS TO RUNTIME' was '0x0000000000000000', with epoch '1723040649'
Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Mon May 20 08:43:02 2024
2024-08-07 14:24:09 [INFO] Computed checksum for 'LOAD MYSQL QUERY RULES TO RUNTIME' was '0xF13288323786B5EC', with epoch '1723040649'
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Mon May 20 08:43:02 2024
Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Mon May 20 08:43:02 2024
Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Mon May 20 08:43:02 2024
2024-08-07 14:24:09 [INFO] For information about products and services visit: https://proxysql.com/
2024-08-07 14:24:09 [INFO] For online documentation visit: https://proxysql.com/documentation/
2024-08-07 14:24:09 [INFO] For support visit: https://proxysql.com/services/support/
2024-08-07 14:24:09 [INFO] For consultancy visit: https://proxysql.com/services/consulting/
2024-08-07 14:24:10 [INFO] read_only_action_v2() detected RO=0 on server innodb-cluster-1.innodb-cluster-instances.production.svc.cluster.local:3306 for the first time after commit(), but no need to reconfigure
2024-08-07 14:24:10 [INFO] Latest ProxySQL version available: 2.6.3-107-gcdfcfdc
You can’t really cache a prepared statement. A prepared statement is already cached within MySQL. When you execute a prepared statement, you are sending the parameters to MySQL; you are not sending a query, thus there is nothing to cache.
The query cache in proxysql is exactly that: query cache. Executing a prepared statement is not a query. If you want to cache the query, then you need to stop using prepared statements, send the full query, and then add that pattern to proxysql.