Issue of temporary table in call procedure #917

Closed
mohammadmohebi opened this Issue Feb 17, 2017 · 12 comments

Comments

Projects
None yet
2 participants
@mohammadmohebi

When calling a procedure from php, the procedure creates a temporary table that is used in another line of code to display information (same connection). But it seems that between two line of code the temporary table doesn't exist anymore (probably not the same connection in the back-end for the two lines of code, even if it's the same connection in the front-end).

I recently installed this tool for a migration and scaling purposes, so I don't really master this tools. Thanks your for telling me if something can be done to fix this issue.

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Feb 17, 2017

Contributor

Hi @mohammadmohebi .
This issue is indeed caused by multiplexing.
ProxySQL automatically disable multiplexing under certain circumstances, for example when a temporary table is explicitly created.
In your case, the temporary table is created inside a stored procedure and ProxySQL cannot know a temporary table was created, therefore doesn't disable multiplexing.
Future releases will be more configurable, for example it will be possible to configure ProxySQL in a way to disable multiplex if a specific stored procedure is executed.
For now, the only suggestion I have is to disable multiplexing globally. In the Admin interface:

SET mysql-multiplexing='false';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Thanks

Contributor

renecannao commented Feb 17, 2017

Hi @mohammadmohebi .
This issue is indeed caused by multiplexing.
ProxySQL automatically disable multiplexing under certain circumstances, for example when a temporary table is explicitly created.
In your case, the temporary table is created inside a stored procedure and ProxySQL cannot know a temporary table was created, therefore doesn't disable multiplexing.
Future releases will be more configurable, for example it will be possible to configure ProxySQL in a way to disable multiplex if a specific stored procedure is executed.
For now, the only suggestion I have is to disable multiplexing globally. In the Admin interface:

SET mysql-multiplexing='false';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Thanks

@mohammadmohebi

This comment has been minimized.

Show comment
Hide comment
@mohammadmohebi

mohammadmohebi Feb 17, 2017

I did the change in the configuration but nothing seems changed the way proxysql manage my request. So I consider that my guess was wrong about the back-end behavior, so does it means that another configuration cause that issue. Because if I test my code, with the master or with slave (configured read only) directly everything works well. But when I use the proxy the request doesn't work.

Here the variable you told me to change
| mysql-multiplexing | false |

Have you another idea where that issue come from. Here the log in the proxysql.log file I get
2017-02-17 12:21:33 MySQL_Session.cpp:2253:handler(): [WARNING] Error during query on (1,xxx.xxx.xxx.xxx,3306): 1146, Table 'dialog_cam_db.unitInfo' doesn't exist
Thanks

mohammadmohebi commented Feb 17, 2017

I did the change in the configuration but nothing seems changed the way proxysql manage my request. So I consider that my guess was wrong about the back-end behavior, so does it means that another configuration cause that issue. Because if I test my code, with the master or with slave (configured read only) directly everything works well. But when I use the proxy the request doesn't work.

Here the variable you told me to change
| mysql-multiplexing | false |

Have you another idea where that issue come from. Here the log in the proxysql.log file I get
2017-02-17 12:21:33 MySQL_Session.cpp:2253:handler(): [WARNING] Error during query on (1,xxx.xxx.xxx.xxx,3306): 1146, Table 'dialog_cam_db.unitInfo' doesn't exist
Thanks

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Feb 17, 2017

Contributor

do you have query rules enabled to perform read/write split? If yes, it is possible that read/write split is sending requests to different connections anyway.
Please copy the output of SELECT * FROM mysql_query_rules .

Contributor

renecannao commented Feb 17, 2017

do you have query rules enabled to perform read/write split? If yes, it is possible that read/write split is sending requests to different connections anyway.
Please copy the output of SELECT * FROM mysql_query_rules .

@mohammadmohebi

This comment has been minimized.

Show comment
Hide comment
@mohammadmohebi

mohammadmohebi Feb 17, 2017

Yes I have to slave that I use only for read and the master for write. Does it means that I wount be able to use that feature ?

This is the output of myslq_query_rules

 
*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^SELECT .* FOR UPDATE$
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 2. row ***************************
              rule_id: 2
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^SELECT
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 1
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 3. row ***************************
              rule_id: 10
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^CALL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 4. row ***************************
              rule_id: 11
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: .*
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: 1
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
 

Host group 0 is for write and host group 1 is for read.

Thanks

Yes I have to slave that I use only for read and the master for write. Does it means that I wount be able to use that feature ?

This is the output of myslq_query_rules

 
*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^SELECT .* FOR UPDATE$
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 2. row ***************************
              rule_id: 2
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^SELECT
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 1
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 3. row ***************************
              rule_id: 10
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^CALL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 4. row ***************************
              rule_id: 11
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: .*
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: 1
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
 

Host group 0 is for write and host group 1 is for read.

Thanks

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Feb 17, 2017

Contributor

You can still use this feature, you just need to instruct proxysql to send the query for your temporary table on hostgroup 0.
For example, if your temporary table is called mytmptable , you can create a rule like this:

UPDATE mysql_query_rules SET rule_id=5 WHERE rule_id=2;
INSERT INTO mysql_query_rules (rule_id, match_digest, destination_hostgroup, apply) VALUES
(3, '^SELECT .* FROM mytmptable', 0, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Note that this ^^ also moves rule number 2 to a different position, so we can create a rule before it.

I also noticed that rule_id=11 has mirror_hostgroup=1 : this seems dangerous, because it means that all queries sent to master are also sent to a slave.
If you don't have read_only=ON on slaves, this could corrupt data.

Contributor

renecannao commented Feb 17, 2017

You can still use this feature, you just need to instruct proxysql to send the query for your temporary table on hostgroup 0.
For example, if your temporary table is called mytmptable , you can create a rule like this:

UPDATE mysql_query_rules SET rule_id=5 WHERE rule_id=2;
INSERT INTO mysql_query_rules (rule_id, match_digest, destination_hostgroup, apply) VALUES
(3, '^SELECT .* FROM mytmptable', 0, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Note that this ^^ also moves rule number 2 to a different position, so we can create a rule before it.

I also noticed that rule_id=11 has mirror_hostgroup=1 : this seems dangerous, because it means that all queries sent to master are also sent to a slave.
If you don't have read_only=ON on slaves, this could corrupt data.

@mohammadmohebi

This comment has been minimized.

Show comment
Hide comment
@mohammadmohebi

mohammadmohebi Feb 17, 2017

Yes I understand, but the create temporary table is inside the procedure. So after theCALL request is send to proxysql that would change anything, because the temporary table is called inside the server where the CALL is executed (the master). So because the call is interne to the MySQL server I don't see how the proxyslq will know about the CREATE TEMPORARY TABLE.

Yes my slaves are in read_only=ON mode. But I think I will remove the mirror_hostgroup and put it back at null.

Thanks

Yes I understand, but the create temporary table is inside the procedure. So after theCALL request is send to proxysql that would change anything, because the temporary table is called inside the server where the CALL is executed (the master). So because the call is interne to the MySQL server I don't see how the proxyslq will know about the CREATE TEMPORARY TABLE.

Yes my slaves are in read_only=ON mode. But I think I will remove the mirror_hostgroup and put it back at null.

Thanks

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Feb 17, 2017

Contributor

@mohammadmohebi : you are absolutely right, ProxySQL does not know about the temporary table.
For this reason you have to create a rule (like the one I wrote in my previous comment) that instruct ProxySQL to send to the master the SELECTs on the temporary table created in the stored procedure (in my example I called it mytmptable, I don't know its real name).

Contributor

renecannao commented Feb 17, 2017

@mohammadmohebi : you are absolutely right, ProxySQL does not know about the temporary table.
For this reason you have to create a rule (like the one I wrote in my previous comment) that instruct ProxySQL to send to the master the SELECTs on the temporary table created in the stored procedure (in my example I called it mytmptable, I don't know its real name).

@mohammadmohebi

This comment has been minimized.

Show comment
Hide comment
@mohammadmohebi

mohammadmohebi Feb 17, 2017

@renecannao : I did the change, but I still have the issue. I really tankful that the proxysql guy help me for that because I can say that I don't find any information online. Here the changes I did with my tables unitInfo. But I still have the probleme.

*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^SELECT .* FOR UPDATE$
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 2. row ***************************
              rule_id: 3
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT .* FROM ScoresMeters
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 3. row ***************************
              rule_id: 4
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT .* FROM unitInfo
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 4. row ***************************
              rule_id: 5
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT .* FROM dialog_cam_db.unitInfo
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 5. row ***************************
              rule_id: 9
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^SELECT
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 1
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 6. row ***************************
              rule_id: 10
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^CALL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 7. row ***************************
              rule_id: 11
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: .*
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: 1
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
7 rows in set (0.00 sec)
 

Error log

2017-02-17 15:54:50 MySQL_Session.cpp:2253:handler(): [WARNING] Error during query on (1,xxx.xxx.xxx.xxx,3306): 1146, Table 'dialog_cam_db.unitInfo' doesn't exist

@renecannao : I did the change, but I still have the issue. I really tankful that the proxysql guy help me for that because I can say that I don't find any information online. Here the changes I did with my tables unitInfo. But I still have the probleme.

*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^SELECT .* FOR UPDATE$
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 2. row ***************************
              rule_id: 3
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT .* FROM ScoresMeters
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 3. row ***************************
              rule_id: 4
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT .* FROM unitInfo
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 4. row ***************************
              rule_id: 5
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT .* FROM dialog_cam_db.unitInfo
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 5. row ***************************
              rule_id: 9
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^SELECT
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 1
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 6. row ***************************
              rule_id: 10
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^CALL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 7. row ***************************
              rule_id: 11
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: .*
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: 1
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
7 rows in set (0.00 sec)
 

Error log

2017-02-17 15:54:50 MySQL_Session.cpp:2253:handler(): [WARNING] Error during query on (1,xxx.xxx.xxx.xxx,3306): 1146, Table 'dialog_cam_db.unitInfo' doesn't exist
@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Feb 17, 2017

Contributor

Error during query on (1,xxx.xxx.xxx.xxx,3306)

The 1 there means that the query is being sent to the hostgroup 1 and it is not being sent to hostgroup . Can you please share the query?

Also, please copy the output of:

SELECT * FROM stats_mysql_query_rules;
Contributor

renecannao commented Feb 17, 2017

Error during query on (1,xxx.xxx.xxx.xxx,3306)

The 1 there means that the query is being sent to the hostgroup 1 and it is not being sent to hostgroup . Can you please share the query?

Also, please copy the output of:

SELECT * FROM stats_mysql_query_rules;
@mohammadmohebi

This comment has been minimized.

Show comment
Hide comment
@mohammadmohebi

mohammadmohebi Feb 17, 2017

Yes you are right, when I look at the IP address it's sent to the slave. OK I will show you the query. But is it a way to force proxysql to keep the same back-end connection for a front-end connection. I know that this kind of behaviour is one of the most best of proxysql to load balance properly, but in this case it create me this issue. Here the query I send.

$query = "select fieldName, dataTypeId from unitInfo";
+---------+------+
| rule_id | hits |
+---------+------+
| 1       | 0    |
| 9       | 42   |
| 10      | 1    |
| 11      | 13   |
+---------+------+
4 rows in set (0.00 sec)

I see that the two rule I added is not working. But the other issue I see here is how I will manage that for all client. We have 15 client, each of them has at least 12 different request like that, would I need to add 180 SQL rule in the configuration?

Yes you are right, when I look at the IP address it's sent to the slave. OK I will show you the query. But is it a way to force proxysql to keep the same back-end connection for a front-end connection. I know that this kind of behaviour is one of the most best of proxysql to load balance properly, but in this case it create me this issue. Here the query I send.

$query = "select fieldName, dataTypeId from unitInfo";
+---------+------+
| rule_id | hits |
+---------+------+
| 1       | 0    |
| 9       | 42   |
| 10      | 1    |
| 11      | 13   |
+---------+------+
4 rows in set (0.00 sec)

I see that the two rule I added is not working. But the other issue I see here is how I will manage that for all client. We have 15 client, each of them has at least 12 different request like that, would I need to add 180 SQL rule in the configuration?

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Feb 17, 2017

Contributor

But is it a way to force proxysql to keep the same back-end connection for a front-end connection.

If I am reading this correctly, you are basically asking to disable read/write split. Of course it is possible: don't send anything to slave, and use mysql-multiplexing=false.

About table stats_mysql_query_rules : it should list all the query rules. If it doesn't show the new rules you added, maybe you forgot to run LOAD MYSQL QUERY RULES TO RUNTIME .

About creating a lot of rules: you surely have that option, but I generally recommend a different approach.
I recommend to send everything to the master, and with the help of stats_mysql_query_digest you can identify the most time consuming SELECT with a query like this:

SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 20;

Once you identify the most time consuming SELECTs you can decide which one to sent to the slave.

Contributor

renecannao commented Feb 17, 2017

But is it a way to force proxysql to keep the same back-end connection for a front-end connection.

If I am reading this correctly, you are basically asking to disable read/write split. Of course it is possible: don't send anything to slave, and use mysql-multiplexing=false.

About table stats_mysql_query_rules : it should list all the query rules. If it doesn't show the new rules you added, maybe you forgot to run LOAD MYSQL QUERY RULES TO RUNTIME .

About creating a lot of rules: you surely have that option, but I generally recommend a different approach.
I recommend to send everything to the master, and with the help of stats_mysql_query_digest you can identify the most time consuming SELECT with a query like this:

SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 20;

Once you identify the most time consuming SELECTs you can decide which one to sent to the slave.

@mohammadmohebi

This comment has been minimized.

Show comment
Hide comment
@mohammadmohebi

mohammadmohebi Feb 17, 2017

I really did.

2017-02-17 15:52:23 [INFO] Received LOAD MYSQL QUERY RULES TO RUNTIME command
2017-02-17 15:52:27 [INFO] Received SAVE MYSQL QUERY RULES TO DISK command

OK I understand, the why I'm forced to disable read/write, because the proxysql need to know the request before to choose a connection. I dont I want to disable it, it's one of the reasons I want use proxysql, because it split read write for me without doing anything else.

So I did the save but it's not working. I added new rules with lower case and that didn't change anything. I decided to disable the rule 9 (standard reads) and now it's working well. It's like my select rules with the specific table is not working. You can see the new configuration. Does my match_pattern is right ?


mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

mysql> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM mysql_query_rules\G
*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^SELECT .* FOR UPDATE$
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 2. row ***************************
              rule_id: 3
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT .* FROM ScoresMeters
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 3. row ***************************
              rule_id: 4
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT .* FROM unitInfo
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 4. row ***************************
              rule_id: 5
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT .* FROM dialog_cam_db.unitInfo
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 5. row ***************************
              rule_id: 6
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^select .* from unitInfo
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 6. row ***************************
              rule_id: 7
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: select fieldName, dataTypeId from unitInfo
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 7. row ***************************
              rule_id: 8
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^select .* from dialog_cam_db.unitInfo
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 8. row ***************************
              rule_id: 9
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^SELECT
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 1
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 9. row ***************************
              rule_id: 10
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^CALL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 10. row ***************************
              rule_id: 11
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: .*
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: 1
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
10 rows in set (0.00 sec)


How does sql patterns works, is it a standard regex format?

Thanks

I really did.

2017-02-17 15:52:23 [INFO] Received LOAD MYSQL QUERY RULES TO RUNTIME command
2017-02-17 15:52:27 [INFO] Received SAVE MYSQL QUERY RULES TO DISK command

OK I understand, the why I'm forced to disable read/write, because the proxysql need to know the request before to choose a connection. I dont I want to disable it, it's one of the reasons I want use proxysql, because it split read write for me without doing anything else.

So I did the save but it's not working. I added new rules with lower case and that didn't change anything. I decided to disable the rule 9 (standard reads) and now it's working well. It's like my select rules with the specific table is not working. You can see the new configuration. Does my match_pattern is right ?


mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

mysql> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM mysql_query_rules\G
*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^SELECT .* FOR UPDATE$
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 2. row ***************************
              rule_id: 3
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT .* FROM ScoresMeters
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 3. row ***************************
              rule_id: 4
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT .* FROM unitInfo
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 4. row ***************************
              rule_id: 5
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT .* FROM dialog_cam_db.unitInfo
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 5. row ***************************
              rule_id: 6
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^select .* from unitInfo
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 6. row ***************************
              rule_id: 7
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: select fieldName, dataTypeId from unitInfo
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 7. row ***************************
              rule_id: 8
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^select .* from dialog_cam_db.unitInfo
        match_pattern: NULL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 8. row ***************************
              rule_id: 9
               active: 0
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^SELECT
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 1
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 9. row ***************************
              rule_id: 10
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^CALL
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 10. row ***************************
              rule_id: 11
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: .*
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 0
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: 1
            error_msg: NULL
                  log: NULL
                apply: 1
              comment: NULL
10 rows in set (0.00 sec)


How does sql patterns works, is it a standard regex format?

Thanks

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment