CentOS 7, Rackspace cloud using OnMetal servers. Percona MySQL 5.6 behind Maxscale and PHP application in front.

Looks like the leading comment somehow confuses the query classifier.

Do you have some filters enabled? It might explain why the query classifier won't process the insert if the newlines are removed by a filter.

Please attach your configuration with all sensitive information removed from it.

No filters other than a tee filter on that service. I attached my config as requested.

Looks like we're on the same page here, I submitted MXS-2270 on the same day. Please see my C test program.

Unconfirming this for now as my initial success was actually caused by the fact that I didn't add a newline after my comment.

If possible, please try and see if adding query_classifier_cache_size=0 in the [maxscale] section helps.

I added that line to config as requested and it does not look like anything changed as far as classifying the queries is concerned, though it does look like the logs are showing the newlines now.

2019-01-18 15:36:55   info   : (590) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 229, type: QUERY_TYPE_READ, stmt: # myABTest::RecordTestChoice()

INSERT IGNORE INTO ab_test_results ( ABTestID, ABTestOptionID, RequestHash)

                        VALUES (6438, 16364, 'e773e2d7cexai2yn')

# /opt/InternalJobsAPI/release/code/ABTesting/obj.myABTest.php(122)

Can you provide a minimal viable example that reproduces this with the command line client? We've had no luck in reproducing this in our environments. Note that to enable comments in the command line client you must provide the -c flag and to pass multiple statements use the DELIMITER command to change it from the default semicolon delimiter to something else.

OK I got it.

Initial setup,
Create maxscale server
setup two mysql 5.6 servers behind it, one master, one slave
use attached minimum configs

run the following on the master or on maxscale:

create database write_test;

use write_test

create table test (test1 int, PRIMARY KEY (test1));

Then use the attached sql file with the following command line

mysql -u maxscale -h 172.24.0.211 -pMaxscale -c -D write_test < test_insert1.sql

The latest test to provide minimal viable example was done on maxscale 2.3.3 and not counting environment setup took about 10 seconds to confirm based on already provided information.

Also mysql configs are all default except log-bin was uncommented and server_id was added for replication.

Tested with the exact SQL but was not able to reproduce on MariaDB 10.3:

2019-02-05 08:19:08   info   : Servers and router connection counts:

2019-02-05 08:19:08   info   : current operations : 0 in        [127.0.0.1]:3000 RUNNING MASTER

2019-02-05 08:19:08   info   : current operations : 0 in        [127.0.0.1]:3001 RUNNING SLAVE

2019-02-05 08:19:08   info   : Selected Master: server1

2019-02-05 08:19:08   info   : Selected Slave: server2

2019-02-05 08:19:08   info   : Started Read-Write-Service client session [3] for 'maxuser' from ::ffff:127.0.0.1

2019-02-05 08:19:08   info   : (3) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 21, type: QUERY_TYPE_READ, stmt: # comment before 

2019-02-05 08:19:08   info   : (3) Route query to slave: server2        [127.0.0.1]:3001 <

2019-02-05 08:19:08   info   : (3) Reply complete, last reply from server2

2019-02-05 08:19:08   info   : (3) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 42, type: QUERY_TYPE_WRITE, stmt: INSERT INTO test ( test1 ) VALUES (1) 

2019-02-05 08:19:08   info   : (3) Route query to master: server1       [127.0.0.1]:3000 <

2019-02-05 08:19:08   info   : (3) Reply complete, last reply from server1

2019-02-05 08:19:08   info   : (3) > Autocommit: [enabled], trx is [not open], cmd: (0x01) COM_QUIT, plen: 5, type: QUERY_TYPE_SESSION_WRITE, stmt:  

2019-02-05 08:19:08   info   : (3) Session write, routing to all servers.

2019-02-05 08:19:08   info   : (3) Route query to master: server1       [127.0.0.1]:3000

2019-02-05 08:19:08   info   : (3) Route query to slave: server2        [127.0.0.1]:3001

2019-02-05 08:19:08   info   : Stopped Read-Write-Service client session [3]

I tested with both master_accept_reads commented out and with the default config but with the order of the servers reversed to make sure the slave server would be preferred over the master for reads when no load is present.

A notable difference is that the client appears to automatically split the queries at comment boundaries regardless of how the query is fed via the command line or what delimiter is used. To reproduce the single-command execution with the leading comment, a literal newline character (C-j) had to be inserted:

2019-02-05 08:19:37   info   : Servers and router connection counts:

2019-02-05 08:19:37   info   : current operations : 0 in        [127.0.0.1]:3000 RUNNING MASTER

2019-02-05 08:19:37   info   : current operations : 0 in        [127.0.0.1]:3001 RUNNING SLAVE

2019-02-05 08:19:37   info   : Selected Master: server1

2019-02-05 08:19:37   info   : Selected Slave: server2

2019-02-05 08:19:37   info   : Started Read-Write-Service client session [4] for 'maxuser' from ::ffff:127.0.0.1

2019-02-05 08:19:37   info   : (4) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 19, type: QUERY_TYPE_SHOW_DATABASES, stmt: show databases 

2019-02-05 08:19:37   info   : (4) Route query to slave: server2        [127.0.0.1]:3001 <

2019-02-05 08:19:37   info   : (4) Reply complete, last reply from server2

2019-02-05 08:19:37   info   : (4) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 16, type: QUERY_TYPE_SHOW_TABLES, stmt: show tables 

2019-02-05 08:19:37   info   : (4) Route query to slave: server2        [127.0.0.1]:3001 <

2019-02-05 08:19:37   info   : (4) Reply complete, last reply from server2

2019-02-05 08:19:37   info   : (4) > Autocommit: [enabled], trx is [not open], cmd: (0x04) COM_FIELD_LIST, plen: 10, type: , stmt:  

2019-02-05 08:19:37   info   : (4) Route query to master: server1       [127.0.0.1]:3000 <

2019-02-05 08:19:37   info   : (4) Reply complete, last reply from server1

2019-02-05 08:19:37   info   : (4) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 37, type: QUERY_TYPE_READ|QUERY_TYPE_SYSVAR_READ, stmt: select @@version_comment limit 1 

2019-02-05 08:19:37   info   : (4) Route query to slave: server2        [127.0.0.1]:3001 <

2019-02-05 08:19:37   info   : (4) Reply complete, last reply from server2

2019-02-05 08:19:59   info   : (4) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 60, type: QUERY_TYPE_WRITE, stmt: # comment before

INSERT INTO test ( test1 ) VALUES (1); 

2019-02-05 08:19:59   info   : (4) Route query to master: server1       [127.0.0.1]:3000 <

2019-02-05 08:19:59   info   : (4) Reply complete, last reply from server1

Even then I was not able to reproduce the routing of queries to slaves. Next I'll try if the MySQL 5.6 client behaves in some odd manner that causes this.

The client that was used is below

mysql  Ver 15.1 Distrib 10.3.12-MariaDB, for Linux (x86_64) using readline 5.1

only the servers behind maxscale were mysql 5.6

After retrying on my end I see that you are probably not using CentOS 7, as the issue does not seem to show up when I used Ubuntu.

To my surprise, it does appear to be platform specific problem. I managed to reproduce the issue on CentOS 7 with 2.3.3 but not on Fedora 28. Initial analysis suggests it's somewhere in the parser code.

Upon further analysis there is no systematic problem in the parser as it is able to classify a query with a statement correctly. Some other form of interference is taking place that is not known at this point in time. More investigation is require.

It appears the difference is in the debug and release builds. For a still unknown reason a debug build classifies the statement correctly as a write whereas a release build on CentOS 7 causes it to be classified as a read.

I've been investigating the behavior of the release builds and, based on my observations, the problem seems to be caused by the -fpeephole2 flag which is enabled when the -O2 optimization level is used. I am not 100% confident that this is the culprit as the problem doesn't appear to always be reproducible.

Regardless of my lack of definitive proof, I've built release mode packages with the aforementioned optimization disabled for the query classifier. You can find the packages here: http://max-tst-01.mariadb.com/ci-repository/MXS-2.3-markusjm-feb6/mariadb-maxscale/centos/7/x86_64/
The packages were built from commit df6bc9d60f4da236ae64267ca5153e8d9923d1c7. Please test these packages and let us know whether you see an improvement. Meanwhile, we'll continue our investigation into this matter

We found some code in the query classifier that resulted in hard to detect undefined behavior. Having found the bug we fixed it and built packages which can be found here: http://max-tst-01.mariadb.com/ci-repository/MXS-2268/mariadb-maxscale/
Please test with these packages instead of the ones mentioned in the earlier comment.

The new code definitely works correctly in my test environment.

That's good to hear. The fix will be in the next 2.3 and 2.2 releases.