MaxScale BinLog Server compatibility

[adaszko]

Hi!

MaxScale can act as a (among other things) replication proxy between MySQL/MariaDB master and slaves. The reason why you would want to do this is that is it caches binlogs and thanks to that it is a single, consistent source of binlogs for Maxwell in case of a DB failover.

The problem is that MaxScale supports only a limited subset of SQL queries when configured as a binlog server. It does support queries of the form SELECT @@var but apparently not for the variables Maxwell expects it to support:

2017-01-24 12:09:24   error  : Unexpected query from 'repluser'@'172.17.0.1': /* mysql-connector-java-5.1.39 ( Revision: 3289a357af6d09ecc1a10fd3c26e95183e5790ad ) */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, @@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_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@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 tx_isolation, @@wait_timeout AS wait_timeout
2017-01-24 12:09:24   notice : Binlog_Service: Slave 172.17.0.1, server id 0, disconnected after 0 seconds. 1 SQL commands

Just to confirm, same thing happens when I execute the query by hand, via MariaDB CLI client:

MySQL [(none)]> 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, @@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_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@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 tx_isolation, @@wait_timeout AS wait_timeout;
ERROR 1064 (42000): You have an error in your SQL syntax; Check the syntax the MaxScale binlog router accepts.

This happens on Maxwell 1.7.0. I explicitly pass MaxScale Binlog Server port as --replication_port= CLI option.

Now the question: Is there a way around this? Maybe these variables' values can be injected or hard-coded somehow? I can't seem to find the relevant piece of code producing that query in Maxwell sources.

Thanks in advance for any help.

[osheroff]

does maxwell dump a stacktrace? Maxwell does not personally looking for those variables, but maybe the binlog-connector-j does or something.

[adaszko]

I just found by other means that it indeed looks like binlog-connector-j is issuing the query.

Here's the stack trace anyway:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; Check the syntax the MaxScale binlog router accepts.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
        at com.mysql.jdbc.Util.getInstance(Util.java:387)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2503)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1369)
        at com.mysql.jdbc.ConnectionImpl.loadServerVariables(ConnectionImpl.java:3833)
        at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3283)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2297)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)
        at snaq.db.ConnectionPool.create(ConnectionPool.java:256)
        at snaq.db.ConnectionPool.create(ConnectionPool.java:71)
        at snaq.util.ObjectPool.checkOut(ObjectPool.java:377)
        at snaq.db.ConnectionPool.getConnection(ConnectionPool.java:385)
        at com.zendesk.maxwell.MaxwellContext.getReplicationConnection(MaxwellContext.java:71)
        at com.zendesk.maxwell.MaxwellContext.getServerID(MaxwellContext.java:159)
        at com.zendesk.maxwell.MaxwellContext.<init>(MaxwellContext.java:62)
        at com.zendesk.maxwell.Maxwell.<init>(Maxwell.java:36)
        at com.zendesk.maxwell.Maxwell.main(Maxwell.java:188)

[osheroff]

I looked deeper, and I'm honestly not sure we can support this configuration at all. When you connect to the replication mirror, does it contain an active copy of the database schema? I'd presume not; maxwell still needs to be able to snapshot the schema, and it presumes that the replication host has that information.

This configuration could probably be hacked around with an implementation that had certain odd properties (didn't use the connector-j configuration, required an alternate connection to capture the schema, has no bootstrapping functionality), but it's probably not something I'd want to maintain in core.

How interested are you in this functionality? I've been meaning to get it together and release a .jar version of maxwell to maven central. From there, you could probably cobble together the needed hacks and workarounds by swapping out the main Maxwell class for another. Let me know if that approach interests you.

[adaszko]

When you connect to the replication mirror, does it contain an active copy of the database schema? I'd presume not; maxwell still needs to be able to snapshot the schema, and it presumes that the replication host has that information.

This configuration could probably be hacked around with an implementation that had certain odd properties (didn't use the connector-j configuration, required an alternate connection to capture the schema, has no bootstrapping functionality), but it's probably not something I'd want to maintain in core.

That's right, the schema isn't there.

I managed to patch MaxScale so that it issues all the mysql-connector-j's (and several of Maxwell's) queries to the MariaDB master (list below) at MaxScale's initialization and responds to Maxwell with cached responses. Thanks to that, we don't need to abandon mysql-connector-j just yet.

As for the schema capturing, I'm currently stuck precisely at it (note that initialization done in MaxwellMysqlStatus.java and MaxwellContext.java were overcome).

How interested are you in this functionality?

I've been meaning to get it together and release a .jar version of maxwell to maven central. From there, you could probably cobble together the needed hacks and workarounds by swapping out the main Maxwell class for another. Let me know if that approach interests you.

I'm very much interested in this. I'm willing to spend a week or two full-time on a proof of concept work.

As for the .jar publishing, is that any more convenient than building from source? (Sorry, Java is not my primary language.)

I'd go the route of introducing a separate connection just for the purpose of capturing schema, which I'd configure to point to MariaDB master if you don't see any architectural reasons against it.

has no bootstrapping functionality

Also, could you elaborate a bit on what kind of bootstrapping do you have in mind?

---

 /* mysql-connector-java-5.1.39 ( Revision: 3289a357af6d09ecc1a10fd3c26e95183e5790ad ) */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, @@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_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@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 tx_isolation, @@wait_timeout AS wait_timeout

SET character_set_results = NULL

SET sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'

SELECT @@server_id as server_id

SHOW VARIABLES LIKE 'log_bin'

SHOW VARIABLES LIKE 'binlog_format'

SHOW VARIABLES LIKE 'binlog_row_image'

select @@lower_case_table_names

[osheroff]

As for the .jar publishing, is that any more convenient than building from source? (Sorry, Java is not my primary language.)

no, but it's more sustainable. My presumption was that a bunch of these changes won't necessarily fit with "mainline" maxwell, so support for this config would be provided by your external application that consumed maxwell-as-a-library. But maybe if the only change is a separate host for schema-capture, maybe that could be integrated back; I had thought you were going to have to strip out connector-J.

Also, could you elaborate a bit on what kind of bootstrapping do you have in mind?

Just meant http://maxwells-daemon.io/bootstrapping/ won't work.

I'd go the route of introducing a separate connection just for the purpose of capturing schema, which I'd configure to point to MariaDB master if you don't see any architectural reasons against it.

no, there's race conditions against schema updates, but those exist in the capture code anyway. It should be fine to capture on one host, replicate from another.

[adaszko]

PR #559 has the changes I needed to make in order to get MaxScale and Maxwell working together.

[osheroff]

shipped