Not able to split read-write request through procedure calls from .net application

[Rmk93]

I have three EC2 instances for MySQL, 1 master node and 2 replication slave node. I have setup a Proxysql on EC2 instance listening on 6033 port which will route all the write requests to master node and all the read requests to slave node.

If I execute procedures (contains only select statement i.e. read request) from my .net application the proxysql redirect to only Master node, not a single procedure hit on slave nodes i.e. not splitting read request among the slave nodes. Every time it hits only master node.

But if i execute the same procedures from SQLYog, proxysql splits procedures(read request) to slave nodes i.e. read requests are redirect to slave nodes. If i executes read/write request without using procedures then proxysql works properly and split reads to slave nodes and write to master node but not through the procedures call.

In my proxysql server i have defined the possible rules for queries in mysql_query_rules table as below:

Admin> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules; 
+---------+--------+-------------------------------------------+-----------------------+-------+
| rule_id | active | match_digest                              | destination_hostgroup | apply |
+---------+--------+-------------------------------------------+-----------------------+-------+
| 8       | 1      | ^SELECT.*                                 | 1                     | 1     |
| 9       | 1      | ^SELECT.*FOR UPDATE                       | 0                     | 1     |
| 10      | 1      | ^CALL `db1`.`sp1`                         | 1                     | 1     |
| 11      | 1      | ^CALL sp1                                 | 1                     | 1     |
| 12      | 1      | ^CALL db1.sp1                             | 1                     | 1     |
+---------+--------+-------------------------------------------+-----------------------+-------+

I am using MySQL server version 8.0.13 and Proxysql version 2.0.1-4-gac2e710, codename Truls.

In .NET application the connection string is: server=xxx.amazonaws.com;Port=6033;user=xxx;database=abc;password=xxxx;charset=utf8mb4;CheckParameters=False;Connection;

Thanks in advance for any help !!

[renecannao]

There is not enough information in this issue. Please provide a reproducible test case.

[Rmk93]

Dear Renecannao,

As discribe above i have three EC2 instances on which i have setup MySQL Master slave replication. I have one Mysql Master instance and 2 slaves instances.

I have setup proxysql on EC2 instance for above MySQL master - slave replication.

I want to distribute read - write queries through proxysql.

I want to send write queries to Master instance and read queries on slave instances.For that i have inserted rule in mysql_query_rules table on proxysql server instance so that it can distribute read - write queries according to defined rule. Write hostgroup = 0 read hostgroup = 1

mysql_query_rules table :

Admin> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules; 
+---------+--------+-------------------------------------------+-----------------------+-------+
| rule_id | active | match_digest                              | destination_hostgroup | apply |
+---------+--------+-------------------------------------------+-----------------------+-------+
| 8       | 1      | ^SELECT.*                                 | 1                     | 1     |
| 9       | 1      | ^SELECT.*FOR UPDATE                       | 0                     | 1     |
| 10      | 1      | ^CALL `db1`.`sp1`                         | 1                     | 1     |
| 11      | 1      | ^CALL sp1                                 | 1                     | 1     |
| 12      | 1      | ^CALL db1.sp1                             | 1                     | 1     |
+---------+--------+-------------------------------------------+-----------------------+-------+

Now i have connected proxysql through Sqlyog tool, when i call procedure name sp1 from Sqlyog with multiple session then proxysql executes it from reader hostgroup(slaves) which is expected and working fine.

But when i connect proxysql server with my .net application and call the same procedure i.e. sp1 then proxysql execute it from writer hostgrop(Master). I am executing procedures only having select statement

My only concern is that why procedures are not executing from reader hostgroups whenever i call it through .net application but its working properly i.e. executing procedures from reader hostgroup when i call it through Sqlyog tool.

[Rmk93]

@renecannao,

Thanks for showing interest into this . Finally got the solution, issue was from application end.

Thanks

[Rmk93]

As i have earlier described above that I have setup Proxysql for MySQL Master Slave replication environment on EC2 instances. When i used to execute inline queries from .net via proxysql it's working fine, but when i used to execute any queries through procedures call it through an error i.e. "Table 'mysql.proc' doesn't exist".

So to work around this i have added a CheckParameters=false (default is true) in web.config file i.e. in connection string. After that i am able execute procedures also.

I am using,

MySQL version 8.0.13
Proxysql version 2.0.1
MySql.Data(connector) version   8.0.13

My concern is that after MySQL 5.7 there in no table exists named proc in mysql database. Since it has been replaced by routines in information_schema database from MySQL 8.0 . So why is being checked for the same table i.e mysql.proc , is it a bug from MySQL connector or from proxysql.