I'm trying to connect to a MySQL 5.7.25 database at PureCloud Genesys. I can successfully connect with my general purpose database tool, but I get the following error when I try to use the Airbyte MySQL connector.

Could not connect with provided configuration. Error: Could not query the variable binlog_row_image

Any help would be appreciated as I have zero experience with MySQL.

Hey @Nathan Atkins where is this mysql instnace hosted? also can you try running this query on your DB and share the output

show variables where Variable_name = 'binlog_row_image'

@Subodh (Airbyte) The database is hosted at our vendor PureCloud.

Comparing with your output the

binlog_row_image

was configured to value FULL. @Subodh (Airbyte) any idea here?

Just typing out loud here. The last time I did anything in Java we didn't have <generics> so I'm not completely following the code. This looks like the problem area.

checkOperations.add(database -> {
      List<String> image = database.resultSetQuery(connection -> {
        final String sql = "show variables where Variable_name = 'binlog_row_image'";

        return connection.createStatement().executeQuery(sql);
      }, resultSet -> resultSet.getString("Value")).collect(toList());

      if (image.size() != 1) {
        throw new RuntimeException("Could not query the variable binlog_row_image");
      }

      String binlogRowImage = image.get(0);
      if (!binlogRowImage.equalsIgnoreCase("FULL")) {
        throw new RuntimeException("The variable binlog_row_image should be set to FULL, but it is : " + binlogRowImage);
      }
    });

While the MySQL documentation for

show variables

says that the were clause should work it doesn't seem to and we get 500+ varaibles (name, value) rows back. It looks like the code is looking for a single row and then selecting the value here

resultSet.getString("Value")).collect(toList())

If I change the query to

show variables like 'binlog_row_image'

I get just one row back and I think the code would work correctly from there. The documentation for both 5.7 and 8 say that the where clause should work, so I don't know what is going on there. I guess one option would be to change the code that gets the value out of the list to find the row with Variable_name == "binlog_row_image" and then get the value. Then if the where clause worked or not it would find the value "FULL" and move on.

Did some experiments on my local machine. I spun up docker images of 5.7.25, 5.7.34, and latest (8.something) and the where clause works correctly in all cases. It only returns the single row with binlog_row_image. So it is clearly something about how that database is configured. Since, this database is totally out of my control, what would it take to change this

show

command in the extractor to use a

like

instead of

where

or deal with the possibility of getting a list of variables back? It will be a bit before I can carve out the time to refresh on java and how to build, and test the changes to make a PR.

hey @Nathan Atkins am surprised that this doesn't work with your mysql instance. Perhaps cause its a managed service thats why. But the query with

where

clause works with other managed services like Amazon Aurora/RDS. We can change the query to use

like

instead of

where

clause but what if that change doesn't work on other managed versions (need to check this), can you once reach out to the service providers and ask them why is the query not working for their mysql but works for generic mysql and other hosted versions such as Amazon Aurora? I dont have experience with

PureCloud

vendor

Yes, I will reach out to PureCloud. It is an anomaly with their instance. I did try the

like

version on the three versions I mention above and it works fine. Agree that this isn’t really something we should have to deal with at the Airbyte level, but we all know that this space is full of weird one off issues. Let me know if you get the change made and I can test it with PureCloud.

@Subodh (Airbyte) Well I never thought that would happen and so quickly. PureCloud made some change on their end and we are good to go!

nice! Let us know if you experience any issues with the CDC or if you have any feedback 😄