Azure connection reading binlog fails with 'The connection string may not be right. Please visit portal for references' #26

redcape · 2021-01-15T01:59:21Z

Trying to use debezium to read the binlog, but on Azure a MySQL 5.7 instance, am getting "The connection string may not be right. Please visit portal for references."

The debezium part of the connection works using the regular MySQL JDBC driver, but when connecting for the binlog it fails. Any insight into what to do or data you'd like me to provide, please let me know.

Tried to pull the latest and still didn't help:
com.zendesk:mysql-binlog-connector-java:0.23.3
mysql:mysql-connector-java:8.0.22
io.debezium:debezium-connector-mysql:1.4.0.Final
io.debezium:debezium-embedded:1.4.0.Final

Stack Trace:

2021-01-14 17:51:41,154 ERROR i.d.e.EmbeddedEngine [pool-2-thread-1] Unable to initialize and start connector's task class 'io.debezium.connector.mysql.MySqlConnectorTask' with config: {snapshot.mode=never, database.logger=com.mysql.cj.log.Slf4JLogger, database.user=xxx@zzz, database.password=********, offset.storage=org.apache.kafka.connect.storage.FileOffsetBackingStore, database.server.name=me-debezium-testname, database.ssl.mode=required, database.serverTimezone=UTC, snapshot.locking.mode=NONE, offset.storage.file.filename=debeziumOffsetFile, connector.class=io.debezium.connector.mysql.MySqlConnector, database.port=3306, database.history.file.filename=dbhistory.dat, database.hostname=zzz.mysql.database.azure.com, bigint.unsigned.handling.mode=precise, table.include.list=^vvv.*\.vvv$, database.server.id=96406, database.history=io.debezium.relational.history.FileDatabaseHistory, name=me-debezium-test} org.apache.kafka.connect.errors.ConnectException: Failed to authenticate to the MySQL database at zzz.mysql.database.azure.com:3306 with user 'xxx@zzz'
	at io.debezium.connector.mysql.BinlogReader.doStart(BinlogReader.java:444)
	at io.debezium.connector.mysql.AbstractReader.start(AbstractReader.java:127)
	at io.debezium.connector.mysql.ChainedReader.startNextReader(ChainedReader.java:206)
	at io.debezium.connector.mysql.ChainedReader.start(ChainedReader.java:103)
	at io.debezium.connector.mysql.MySqlConnectorTask.start(MySqlConnectorTask.java:272)
	at io.debezium.connector.common.BaseSourceTask.start(BaseSourceTask.java:106)
	at io.debezium.embedded.EmbeddedEngine.run(EmbeddedEngine.java:758)
	at io.debezium.embedded.ConvertingEngineBuilder$2.run(ConvertingEngineBuilder.java:171)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:748)
Caused by: com.github.shyiko.mysql.binlog.network.AuthenticationException: The connection string may not be right. Please visit portal for references.
	at com.github.shyiko.mysql.binlog.network.Authenticator.readResult(Authenticator.java:85)
	at com.github.shyiko.mysql.binlog.network.Authenticator.authenticate(Authenticator.java:70)
	at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:526)
	at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:839)
	... 1 more

Here's a better unit test of the whole issue showing only MySQL JDBC and mysql-binlog-connector-java:

        System.out.println("Test MySQL connection:");
        String connJdcString = "jdbc:mysql://" + dbHost + "?verifyServerCertificate=false&useSSL=true&serverTimezone=UTC";
        try (Connection conn = DriverManager.getConnection(connJdcString, dbUser, dbPass);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SHOW MASTER STATUS")) {
            while (rs.next()) {
                System.out.println("  Log file: " + rs.getString(1));
            }
        }
        System.out.println("Test MySQL binary log:");
        BinaryLogClient client = new BinaryLogClient(
                dbHost,
                3306,
                dbUser,
                dbPass);
        client.setSSLMode(SSLMode.REQUIRED);
        EventDeserializer eventDeserializer = new EventDeserializer();
        eventDeserializer.setCompatibilityMode(
                EventDeserializer.CompatibilityMode.DATE_AND_TIME_AS_LONG,
                EventDeserializer.CompatibilityMode.CHAR_AND_BINARY_AS_BYTE_ARRAY
        );
        client.setEventDeserializer(eventDeserializer);
        client.registerEventListener(new BinaryLogClient.EventListener() {

            @Override
            public void onEvent(Event event) {
                System.out.println(event);
            }
        });
        client.connect();

Output is:

Test MySQL connection:
  Log file: mysql-bin.000001
Test MySQL binary log:
Jan 14, 2021 6:45:42 PM com.github.shyiko.mysql.binlog.BinaryLogClient tryUpgradeToSSL
INFO: SSL enabled
Exception in thread "main" com.github.shyiko.mysql.binlog.network.AuthenticationException: The connection string may not be right. Please visit portal for references.�
	at com.github.shyiko.mysql.binlog.network.Authenticator.readResult(Authenticator.java:85)
	at com.github.shyiko.mysql.binlog.network.Authenticator.authenticate(Authenticator.java:70)
	at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:526)
	at com.example.debeziumtest.MainTest.main(MainTest.java:46)

Note that without serverTimeZone=UTC, I get: the following as an error during connection with the first JDBC connection.

The server time zone value '' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specific time zone value if you want to utilize time zone support.

The text was updated successfully, but these errors were encountered:

osheroff · 2021-01-15T19:38:34Z

Is there a possibility you could capture a packet trace of a JDBC connection and then the failing connection? Or really just a maxwell boot session. Happy to send a PGP key if you’re concerned about posting in public We’ve had very odd problems with azure through the years, their implementation has been super finicky about packets arriving together or separate, so I’m not totally surprised there’s something else.

…

On Jan 14, 2021, at 17:59, Gil Cottle ***@***.***> wrote: Trying to use debezium to read the binlog, but on Azure a MySQL 5.7 instance, am getting "The connection string may not be right. Please visit portal for references.�" The debezium part of the connection works using the regular MySQL JDBC driver, but when connecting for the binlog it fails. Any insight into what to do or data you'd like me to provide, please let me know. Tried to pull the latest and still didn't help: com.zendesk:mysql-binlog-connector-java:0.23.3 mysql:mysql-connector-java:8.0.22 io.debezium:debezium-connector-mysql:1.4.0.Final io.debezium:debezium-embedded:1.4.0.Final Stack Trace: 2021-01-14 17:51:41,154 ERROR i.d.e.EmbeddedEngine [pool-2-thread-1] Unable to initialize and start connector's task class 'io.debezium.connector.mysql.MySqlConnectorTask' with config: {snapshot.mode=never, database.logger=com.mysql.cj.log.Slf4JLogger, ***@***.***, database.password=********, offset.storage=org.apache.kafka.connect.storage.FileOffsetBackingStore, database.server.name=me-debezium-testname, database.ssl.mode=required, database.serverTimezone=UTC, snapshot.locking.mode=NONE, offset.storage.file.filename=debeziumOffsetFile, connector.class=io.debezium.connector.mysql.MySqlConnector, database.port=3306, database.history.file.filename=dbhistory.dat, database.hostname=zzz.mysql.database.azure.com, bigint.unsigned.handling.mode=precise, table.include.list=^vvv.*\.vvv$, database.server.id=96406, database.history=io.debezium.relational.history.FileDatabaseHistory, name=me-debezium-test} org.apache.kafka.connect.errors.ConnectException: Failed to authenticate to the MySQL database at zzz.mysql.database.azure.com:3306 with user ***@***.***' at io.debezium.connector.mysql.BinlogReader.doStart(BinlogReader.java:444) at io.debezium.connector.mysql.AbstractReader.start(AbstractReader.java:127) at io.debezium.connector.mysql.ChainedReader.startNextReader(ChainedReader.java:206) at io.debezium.connector.mysql.ChainedReader.start(ChainedReader.java:103) at io.debezium.connector.mysql.MySqlConnectorTask.start(MySqlConnectorTask.java:272) at io.debezium.connector.common.BaseSourceTask.start(BaseSourceTask.java:106) at io.debezium.embedded.EmbeddedEngine.run(EmbeddedEngine.java:758) at io.debezium.embedded.ConvertingEngineBuilder$2.run(ConvertingEngineBuilder.java:171) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) Caused by: com.github.shyiko.mysql.binlog.network.AuthenticationException: The connection string may not be right. Please visit portal for references. at com.github.shyiko.mysql.binlog.network.Authenticator.readResult(Authenticator.java:85) at com.github.shyiko.mysql.binlog.network.Authenticator.authenticate(Authenticator.java:70) at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:526) at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:839) ... 1 more — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or unsubscribe.

redcape · 2021-01-16T03:25:23Z

Had a separate conversation, thanks for being responsive @osheroff and giving me the right idea for looking into this further.

I ended up turning off TLS for a temp DB and using Wireshark which thankfully has a MySQL protocol interpeter already written to compare the differences. The failure was at the login step. The diff between logins is below. The piece that Azure wanted was specifying the Client Auth Plugin of mysql_native_password after the password and schema.

I'm not sure if this should be behind a flag or something based on the MySQL version to maintain backwards compatibility, but this is the change that works.

Change to make to AuthenticateSecurityPasswordCommand:

        buffer.writeZeroTerminatedString(username);
        byte[] passwordSHA1 = passwordCompatibleWithMySQL411(password, salt);
        buffer.writeInteger(passwordSHA1.length, 1);
        buffer.write(passwordSHA1);
        if (schema != null) {
            buffer.writeZeroTerminatedString(schema);
        }
        buffer.writeZeroTerminatedString("mysql_native_password"); // this line fixes it
        return buffer.toByteArray();

Diff between login requests:

% diff login-jdbc login-binlog 
2c2
<     Packet Length: 234
---
>     Packet Length: 75
5,7c5,7
<         Client Capabilities: 0xa207
<             .... .... .... ...1 = Long Password: Set
<             .... .... .... ..1. = Found Rows: Set
---
>         Client Capabilities: 0x8204
>             .... .... .... ...0 = Long Password: Not set
>             .... .... .... ..0. = Found Rows: Not set
19c19
<             ..1. .... .... .... = Knows about transactions: Set
---
>             ..0. .... .... .... = Knows about transactions: Not set
22c22
<         Extended Client Capabilities: 0x003e
---
>         Extended Client Capabilities: 0x0008
24,25c24,25
<             .... .... .... ..1. = Multiple results: Set
<             .... .... .... .1.. = PS Multiple results: Set
---
>             .... .... .... ..0. = Multiple results: Not set
>             .... .... .... .0.. = PS Multiple results: Not set
27,28c27,28
<             .... .... ...1 .... = Connect attrs: Set
<             .... .... ..1. .... = Plugin Auth LENENC Client Data: Set
---
>             .... .... ...0 .... = Connect attrs: Not set
>             .... .... ..0. .... = Plugin Auth LENENC Client Data: Not set
33c33
<         MAX Packet: 16777215
---
>         MAX Packet: 0
35c35,40
<         Unused: 0000000000000000000000000000000000000000000000
---
>         Unused: 00000000000000000000000000000000000000
>         MariaDB Extended Client Capabilities: 0x00000000
>             .... .... .... .... .... .... .... ...0 = Progress indication: Not set
>             .... .... .... .... .... .... .... ..0. = Multi commands: Not set
>             .... .... .... .... .... .... .... .0.. = Bulk Operations: Not set
>             .... .... .... .... .... .... .... 0... = Extended metadata: Not set
38,45c43,44
<         Client Auth Plugin: mysql_native_password
<         Connection Attributes
<             Connection Attributes length: 136
<             Connection Attribute - _runtime_version: 1.8.0_131
<             Connection Attribute - _client_version: 8.0.22
<             Connection Attribute - _client_license: GPL
<             Connection Attribute - _runtime_vendor: Oracle Corporation
<             Connection Attribute - _client_name: MySQL Connector/J
---
>         Client Auth Plugin: 
>

osheroff · 2021-01-16T06:02:11Z

nice work @redcape. This totally matches what the spec for the handshake is; according to https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::HandshakeResponse we should always be sending the 'auth plugin name' in our scenario. I can't find any documentation that that field was added later than any conceivable mysql version we care about, so I think we're pretty safe to simply add it. Go ahead and PR the one liner and I'll get a release out.

I do wonder just what those azure-mysql db devs are up to, but hey.

…ions PLUGIN_AUTH is listed as one of the capabilities and connections to Azure MySQL require the Auth Plugin Name to be specified when that capability is present. If it is not there, Azure MySQL instances respond with "The connection string may not be right. Please visit portal for references.". See https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::HandshakeResponse and osheroff#26 for more details about the investigation.

redcape · 2021-01-21T20:53:45Z

Hey @osheroff , thanks for merging and creating new version 0.23.4. Any ETA on when this will make it maven central or another public repo?

osheroff · 2021-01-21T21:10:21Z

Hi @redcape I got stuck yak-shaving an issue with jdk11 and tls-hostname-verifying and oh-god-it's-a-tarpit. I'll see if I can get openjdk8 running somewhere so I can get a build out sooner than later.

osheroff · 2021-01-22T06:34:34Z

0.23.4 released.

redcape · 2021-01-25T17:46:49Z

Hi @osheroff can you point to where this is released, both maven central seems to have the latest version at 0.23.3

osheroff · 2021-01-25T18:38:38Z

harumph. the release seemed to go ok. trying again...

osheroff · 2021-01-25T18:47:10Z

ok this time it showed up at https://repo1.maven.org/maven2/com/zendesk/mysql-binlog-connector-java/0.23.4/ at least. so i think the artifact should be there...

Pulls in the changes from osheroff/mysql-binlog-connector-java#27, which fix osheroff/mysql-binlog-connector-java#26, which made the connector impossible to use on Azure.

redcape mentioned this issue Jan 16, 2021

Add Auth Plugin Name to for native password auth to fix Azure connections redcape/mysql-binlog-connector-java#1

Merged

redcape mentioned this issue Jan 16, 2021

Add Auth Plugin Name to for native password auth to fix Azure connections #27

Merged

osheroff closed this as completed Jan 25, 2021

C0urante mentioned this issue May 16, 2021

RCCA-3187: Bump binlog reader dependency patch version confluentinc/debezium#14

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Azure connection reading binlog fails with 'The connection string may not be right. Please visit portal for references' #26

Azure connection reading binlog fails with 'The connection string may not be right. Please visit portal for references' #26

redcape commented Jan 15, 2021 •

edited

Loading

osheroff commented Jan 15, 2021 via email

redcape commented Jan 16, 2021

osheroff commented Jan 16, 2021

redcape commented Jan 21, 2021

osheroff commented Jan 21, 2021

osheroff commented Jan 22, 2021

redcape commented Jan 25, 2021

osheroff commented Jan 25, 2021

osheroff commented Jan 25, 2021

Azure connection reading binlog fails with 'The connection string may not be right. Please visit portal for references' #26

Azure connection reading binlog fails with 'The connection string may not be right. Please visit portal for references' #26

Comments

redcape commented Jan 15, 2021 • edited Loading

osheroff commented Jan 15, 2021 via email

redcape commented Jan 16, 2021

osheroff commented Jan 16, 2021

redcape commented Jan 21, 2021

osheroff commented Jan 21, 2021

osheroff commented Jan 22, 2021

redcape commented Jan 25, 2021

osheroff commented Jan 25, 2021

osheroff commented Jan 25, 2021

redcape commented Jan 15, 2021 •

edited

Loading