Connect to maxscale binlog-router via mysql-connector-j?

[Ben Osheroff]

Hello, (hopefully) MaxScale devs.  My name is Ben Osheroff, I write Maxwell (https://github.com/zendesk/maxwell).  I've been trying to work on https://github.com/zendesk/maxwell/issues/1585, and after a bit of work have narrowed it down to this:

- when I init a mysql-connector-j connection, the horribly helpful connector-j library insists (and I see no way to disable it) on querying maxscale with this SQL: 

 /* mysql-connector-java-8.0.17 (Revision: 16a712ddb3f826a1933ab42b0039f7fb9eebc6ec) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection; // And a bunch more variables.

Maxscale, being only configured to really deal with replication queries, fails.  my usage of the maxscale connection should really boil down to: 

- getting the current "master" position with "SHOW MASTER STATUS"

- making sure binlog_format is ROW, and getting a few other server variables.

- getting the mysql version.

- playing binlogs.

any ideas on how I might get past the initial connection failure?  Maxscale simply responds:

"You have an error in your SQL syntax; Check the syntax the MaxScale binlog router accepts."

[Markus Mäkelä]

Hi,

Based on the error message, you're using the 2.4 binlogrouter or older. This might work better with the 2.5 release of MaxScale where the binlogrouter was rewritten to support a wider range of queries. I'd recommend testing 2.5 to see if it behaves better.

Another idea would be to add a regexfilter to the binlogrouter service to change the SQL to something that the binlogrouter accepts. The problem is that multi-field SELECTs aren't supported by the older binlogrouter; this was improved in 2.5 where multi-field SELECTs are processed and any unknown values are echoed back as-is.

Markus

--
You received this message because you are subscribed to the Google Groups "MaxScale" group.
To unsubscribe from this group and stop receiving emails from it, send an email to maxscale+u...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/maxscale/35a81454-3a82-459e-b3d2-e7c128585ac9n%40googlegroups.com.

-- 
Markus Mäkelä, Senior Software Engineer
MariaDB Corporation
t: +358 40 7740484

[Ben Osheroff]

ok, I got further down this road.

Got 2.5 up and running locally, fixed odd problems (maxscale doesn't tell you that you haven't granted it REPLICATION SLAVE privs, just throws a generic auth error), now I'm getting:

SQLException: #42000

some strace logs:

pid 11492] read(31, "\314\3\0\0\3/* mysql-connector-java-8.0.17 (Revision: 16a712ddb3f826a1933ab42b0039f7fb9eebc6ec) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS
 character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server,
@@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_pac
ket AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode,
@@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout", 976) = 976
[pid 11492] ioctl(31, FIONREAD, [0])    = 0
[pid 11492] write(31, "\t\0\0\1\377(\4#42000", 13) = 13
[pid 11492] ioctl(32, FIONREAD, [976])  = 0
[pid 11492] read(32, "\314\3\0\0\3/* mysql-connector-java-8.0.17 (Revision: 16a712ddb3f826a1933ab42b0039f7fb9eebc6ec) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS
 character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server,
@@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_pac
ket AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode,
@@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout", 976) = 976
[pid 11492] ioctl(32, FIONREAD, [0])    = 0
[pid 11492] write(32, "\t\0\0\1\377(\4#42000", 13) = 13
[pid 11492] epoll_wait(14, [{EPOLLIN|EPOLLOUT|EPOLLRDHUP, {u32=939643720, u64=140472140026696}}, {EPOLLIN|EPOLLOUT|EPOLLRDHUP, {u32=939632984, u64=140472140015960}}], 1000, 844) = 2
[pid 11492] ioctl(31, FIONREAD, [0])    = 0
[pid 11492] recvfrom(31, "", 1, MSG_PEEK, NULL, NULL) = 0
[pid 11492] write(31, "&\0\0\1\377\207\7#08S01Connection killed by MaxScale", 42) = 42
[pid 11492] ioctl(32, FIONREAD, [0])    = 0

You received this message because you are subscribed to a topic in the Google Groups "MaxScale" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/maxscale/ngEIt4yDJEY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to maxscale+u...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/maxscale/7bda2616-6508-a9bc-b321-38630749d678%40mariadb.com.

[Ben Osheroff]

for what it's worth, nothing is logged.  trying to do a debug build...

[Markus Mäkelä]

Hi,

You can enable verbose logging by adding log_info=true under the [maxscale] section and log_debug=true for an even more verbose log that's only enabled for debug builds.

Regarding the authentication error, did MaxScale log any errors in the log before the correct grants were given?

Markus

[Ben Osheroff]

> Regarding the authentication error, did MaxScale log any errors in the log before the correct grants were given?

Yes, but it didn't help me track down what privileges were needed; it just logged:

"2020-10-15 22:55:49   warning: (4) [mariadbclient] Authentication failed for user 'maxscale'@[::ffff:192.168.33.1] to service 'Replication'. Originating listener: 'Binlog-Listener'. MariaDB error: 'Access denied for u

ser 'maxscale'@'::ffff:192.168.33.1' (using password: YES)'."

Here's some logs from the 42000 error session:

2020-10-20 18:58:46   debug  : (9) Read 36 bytes from dcb 0x7fca1c01bcb0 in state DCB::State::POLLING fd 32.
2020-10-20 18:58:46   debug  : (9) Read 186 bytes from dcb 0x7fca1c01bcb0 in state DCB::State::POLLING fd 32.
2020-10-20 18:58:46   debug  : (8) Read 36 bytes from dcb 0x7fca2001adf0 in state DCB::State::POLLING fd 31.
2020-10-20 18:58:46   debug  : (8) Read 186 bytes from dcb 0x7fca2001adf0 in state DCB::State::POLLING fd 31.
2020-10-20 18:58:47   info   : (9) Found matching user 'maxscale'@'%' for client 'maxscale'@'::ffff:192.168.33.1' with sufficient privileges.
2020-10-20 18:58:47   info   : (9) Started Replication client session [9] for 'maxscale' from ::ffff:192.168.33.1
2020-10-20 18:58:47   debug  : (7) Read 36 bytes from dcb 0x7fca1c014dd0 in state DCB::State::POLLING fd 4.
2020-10-20 18:58:47   debug  : (7) Read 186 bytes from dcb 0x7fca1c014dd0 in state DCB::State::POLLING fd 4.
2020-10-20 18:58:47   info   : (8) Found matching user 'maxscale'@'%' for client 'maxscale'@'::ffff:192.168.33.1' with sufficient privileges.
2020-10-20 18:58:47   info   : (8) Started Replication client session [8] for 'maxscale' from ::ffff:192.168.33.1
2020-10-20 18:58:47   debug  : (8) Read 976 bytes from dcb 0x7fca2001adf0 in state DCB::State::POLLING fd 31.
2020-10-20 18:58:47   info   : (8) (Replication) COM_QUERY: /* mysql-connector-java-8.0.17 (Revision: 16a712ddb3f826a1933ab42b0039f7fb9eebc6ec) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2020-10-20 18:58:47   debug  : Ready to close session 8
2020-10-20 18:58:47   debug  : Closed socket 31 on dcb 0x7fca2001adf0.
2020-10-20 18:58:47   info   : Stopped Replication client session [8]
2020-10-20 18:58:47   info   : (7) Found matching user 'maxscale'@'%' for client 'maxscale'@'::ffff:192.168.33.1' with sufficient privileges.
2020-10-20 18:58:47   info   : (7) Started Replication client session [7] for 'maxscale' from ::ffff:192.168.33.1
2020-10-20 18:58:47   debug  : (9) Read 976 bytes from dcb 0x7fca1c01bcb0 in state DCB::State::POLLING fd 32.
2020-10-20 18:58:47   info   : (9) (Replication) COM_QUERY: /* mysql-connector-java-8.0.17 (Revision: 16a712ddb3f826a1933ab42b0039f7fb9eebc6ec) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2020-10-20 18:58:47   debug  : (7) Read 976 bytes from dcb 0x7fca1c014dd0 in state DCB::State::POLLING fd 4.
2020-10-20 18:58:47   info   : (7) (Replication) COM_QUERY: /* mysql-connector-java-8.0.17 (Revision: 16a712ddb3f826a1933ab42b0039f7fb9eebc6ec) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2020-10-20 18:58:47   debug  : Ready to close session 9
2020-10-20 18:58:47   debug  : Closed socket 32 on dcb 0x7fca1c01bcb0.
2020-10-20 18:58:47   info   : Stopped Replication client session [9]
2020-10-20 18:58:47   debug  : Ready to close session 7
2020-10-20 18:58:47   debug  : Closed socket 4 on dcb 0x7fca1c014dd0.
2020-10-20 18:58:47   info   : Stopped Replication client session [7]
2020-10-20 18:58:48   debug  : (10) Read 36 bytes from dcb 0x7fca2001adf0 in state DCB::State::POLLING fd 4.
2020-10-20 18:58:48   debug  : (10) Read 186 bytes from dcb 0x7fca2001adf0 in state DCB::State::POLLING fd 4.
2020-10-20 18:58:48   debug  : (11) Read 36 bytes from dcb 0x7fca1c014dd0 in state DCB::State::POLLING fd 32.
2020-10-20 18:58:48   debug  : (11) Read 186 bytes from dcb 0x7fca1c014dd0 in state DCB::State::POLLING fd 32.
2020-10-20 18:58:48   info   : (11) Found matching user 'maxscale'@'%' for client 'maxscale'@'::ffff:192.168.33.1' with sufficient privileges.
2020-10-20 18:58:48   info   : (11) Started Replication client session [11] for 'maxscale' from ::ffff:192.168.33.1
2020-10-20 18:58:48   debug  : (12) Read 36 bytes from dcb 0x7fca1c01bcb0 in state DCB::State::POLLING fd 33.
2020-10-20 18:58:48   debug  : (12) Read 186 bytes from dcb 0x7fca1c01bcb0 in state DCB::State::POLLING fd 33.
2020-10-20 18:58:48   info   : (10) Found matching user 'maxscale'@'%' for client 'maxscale'@'::ffff:192.168.33.1' with sufficient privileges.
2020-10-20 18:58:48   info   : (10) Started Replication client session [10] for 'maxscale' from ::ffff:192.168.33.1
2020-10-20 18:58:48   debug  : (10) Read 976 bytes from dcb 0x7fca2001adf0 in state DCB::State::POLLING fd 4.
2020-10-20 18:58:48   info   : (10) (Replication) COM_QUERY: /* mysql-connector-java-8.0.17 (Revision: 16a712ddb3f826a1933ab42b0039f7fb9eebc6ec) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2020-10-20 18:58:48   debug  : Ready to close session 10
2020-10-20 18:58:48   debug  : Closed socket 4 on dcb 0x7fca2001adf0.
2020-10-20 18:58:48   info   : Stopped Replication client session [10]
2020-10-20 18:58:48   info   : (12) Found matching user 'maxscale'@'%' for client 'maxscale'@'::ffff:192.168.33.1' with sufficient privileges.
2020-10-20 18:58:48   info   : (12) Started Replication client session [12] for 'maxscale' from ::ffff:192.168.33.1
2020-10-20 18:58:48   debug  : (11) Read 976 bytes from dcb 0x7fca1c014dd0 in state DCB::State::POLLING fd 32.
2020-10-20 18:58:48   info   : (11) (Replication) COM_QUERY: /* mysql-connector-java-8.0.17 (Revision: 16a712ddb3f826a1933ab42b0039f7fb9eebc6ec) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2020-10-20 18:58:48   debug  : Ready to close session 12
2020-10-20 18:58:48   debug  : Closed socket 33 on dcb 0x7fca1c01bcb0.
2020-10-20 18:58:48   info   : Stopped Replication client session [12]
2020-10-20 18:58:48   debug  : Ready to close session 11
2020-10-20 18:58:48   debug  : Closed socket 32 on dcb 0x7fca1c014dd0.
2020-10-20 18:58:48   info   : Stopped Replication client session [11]

[Markus Mäkelä]

Hi,

Could you open a bug on the MariaDB Jira under the MaxScale project? This would be easier to track there.

Markus

[Ben Osheroff]

ok, I created https://jira.mariadb.org/browse/MXS-3299.