I'm trying to connect to a MySQL 5.7.25 database a...
# feedback-and-requests
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
Copy code
show variables where Variable_name = 'binlog_row_image'
@Subodh (Airbyte) The database is hosted at our vendor PureCloud.
Comparing with your output the
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.
Copy code
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
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
command in the extractor to use a
instead of
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
clause works with other managed services like Amazon Aurora/RDS. We can change the query to use
instead of
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
Yes, I will reach out to PureCloud. It is an anomaly with their instance. I did try the
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 😄