[MXS-2203]  update with subquery route to slave Created: 2018-12-03  Updated: 2018-12-03  Resolved: 2018-12-03

Status: Closed
Project: MariaDB MaxScale
Component/s: qc_sqlite
Affects Version/s: None
Fix Version/s: 2.2.17

Type: Bug Priority: Major
Reporter: dapeng huang Assignee: Johan Wikman
Resolution: Fixed Votes: 0
Labels: None
Environment:

2.2.12


 Description   

Following query parsed as QUERY_OP_SELECT

UPDATE test1 AS log_funnel
 
                 INNER JOIN (SELECT inner_funnel.idvisit, min(step_position) as minstep, max(step_position) as maxstep 
                        FROM test1 inner_funnel
 
                        LEFT JOIN pwk_log_visit log_visit on inner_funnel.idvisit = log_visit.idvisit
 
                        WHERE inner_funnel.idfunnel = '10' 
                              AND log_visit.idsite = '8' 
                              AND log_visit.visit_last_action_time >= '2018-12-02 16:00:00'
 
                              AND log_visit.visit_last_action_time <= '2018-12-03 15:59:59'
 
                        GROUP BY inner_funnel.idvisit) lf
 
                  SET log_funnel.min_step = lf.minstep, log_funnel.max_step = lf.maxstep 
                  WHERE log_funnel.idvisit = lf.idvisit
 
                        AND log_funnel.idfunnel = '10' 
                        AND log_funnel.min_step != lf.minstep
 
                        AND log_funnel.max_step != lf.maxstep



 Comments   
Comment by markus makela [ 2018-12-03 ]

This seems to have been fixed in one of the releases as it gets routed correctly in 2.2.17 but is wrong in 2.2.12.

Comment by markus makela [ 2018-12-03 ]

The commit that fixes it is this:

commit 2aca6226d916dd91b2fd900b6ba2035f9a7a278e (HEAD)
 
Author: Johan Wikman <johan.wikman@mariadb.com>
 
Date:   Tue Sep 25 18:26:45 2018 +0300
 
 
 
    MXS-2043 Classify SELECT ... FOR UPDATE as QUERY_TYPE_WRITE
 
    
    SELECT...FOR UPDATE locks the rows for update, but only if
 
    autocommit==0 or a transaction is active, so in principle even if
 
    it were classified as READ it'd still be sent to master when it
 
    actually matters.
 
    
    However, even if autocommit==1 and/or no transaction is active, a
 
    slave in read only mode will reject the statement if the user is
 
    subject to the read only restriction (a user with super privileges
 
    is not), which might be considered a server bug. By classifying the
 
    statement as a write, it'll be sent to master and always succeed.

This is was not an intentional fix to the current problem so it seems some statements are confused with SELECT ... FOR UPDATE and UPDATE .. SELECT.

Comment by markus makela [ 2018-12-03 ]

Simplified query:

UPDATE t1 JOIN (SELECT 1) l SET t1.a = 1;

The problem seems to be with updates that have subqueries.

Comment by Johan Wikman [ 2018-12-03 ]

This is fixed in MaxScale version 2.2.17.

Generated at Thu Feb 08 04:12:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.