Debezium MySQL connector

[Bohdan Bolshakov]

Hi, every body!

I have a problem connecting my connector to MySQL Servers.

I am getting the following error:

org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped.
at io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:50)
at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:116)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)

at java.base/java.lang.Thread.run(Thread.java:829)

Caused by: io.debezium.DebeziumException: java.sql.SQLSyntaxErrorException: Unknown error 1227

at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:85)

at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:155)

at io.debezium.pipeline.ChangeEventSourceCoordinator.executeChangeEventSources(ChangeEventSourceCoordinator.java:137)

at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:109)    ... 5 more

Caused by: java.sql.SQLSyntaxErrorException: Unknown error 1227

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)

at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)

at io.debezium.jdbc.JdbcConnection.executeWithoutCommitting(JdbcConnection.java:1405)

at io.debezium.connector.mysql.MySqlSnapshotChangeEventSource.tableLock(MySqlSnapshotChangeEventSource.java:460)

at io.debezium.connector.mysql.MySqlSnapshotChangeEventSource.readTableStructure(MySqlSnapshotChangeEventSource.java:315)

at io.debezium.connector.mysql.MySqlSnapshotChangeEventSource.readTableStructure(MySqlSnapshotChangeEventSource.java:47)

at io.debezium.relational.RelationalSnapshotChangeEventSource.doExecute(RelationalSnapshotChangeEventSource.java:114)

at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:76)

Does anyone know what this might mean?

[Chris Cranford]

Hi Bohdan -

MySQL error 1227 is an "access denied" error.  This seems to come from the point where the table requires locking, so perhaps there is a permission issue with the user you are using to connect to MySQL not having the ability to lock the tables.  Please review the user setup documentation to make sure that the user has the right permissions.

Thanks,
Chris

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/e8bad566-67b1-428c-97a0-ceb71be291a0n%40googlegroups.com.

[Bohdan Bolshakov]

Yes you are right. At first I had an error related to LOCK TABLES.

But, according to the documentation, I installed a GRANT for the user with the command:

mysql> GRANT LOCK TABLES ON my_database.* TO 'USER_NAME' IDENTIFIED BY 'PASSWORD';
mysql> FLUSH PRIVILEGES;

After that, I started to get an error, which was shown in the message above.

Perhaps a small piece of the log will help you, I replaced some values. To keep your information private:

INFO Kafka version: 7.2.1-ccs (org.apache.kafka.common.utils.AppInfoParser)
INFO Kafka commitId: 80334008361592c187b36a27536f10f43fe93aa3 (org.apache.kafka.common.utils.AppInfoParser)
INFO Kafka startTimeMs: 1662974016161 (org.apache.kafka.common.utils.AppInfoParser)
INFO Database history topic '(name=dev.mysql.__debezium-dbhistory.my_source_name, numPartitions=1, replicationFactor=default, replicasAssignments=null, configs={cleanup.policy=delete, retention.ms=9223372036854775807, retention.bytes=-1})' created (io.debezium.relational.history.KafkaDatabaseHistory)
INFO App info kafka.admin.client for my_source_name-dbhistory unregistered (org.apache.kafka.common.utils.AppInfoParser)
INFO Metrics scheduler closed (org.apache.kafka.common.metrics.Metrics)
INFO Closing reporter org.apache.kafka.common.metrics.JmxReporter (org.apache.kafka.common.metrics.Metrics)
INFO Metrics reporters closed (org.apache.kafka.common.metrics.Metrics)
INFO Reconnecting after finishing schema recovery (io.debezium.connector.mysql.MySqlConnectorTask)
INFO Requested thread factory for connector MySqlConnector, id = my_source_name named = change-event-source-coordinator (io.debezium.util.Threads)
INFO Creating thread debezium-mysqlconnector-my_source_name-change-event-source-coordinator (io.debezium.util.Threads)
INFO WorkerSourceTask{id=dev-my_source_name-0} Source task finished initialization and start (org.apache.kafka.connect.runtime.WorkerSourceTask)
INFO WorkerSourceTask{id=dev-my_source_name-0} Executing source task (org.apache.kafka.connect.runtime.WorkerSourceTask)
INFO Metrics registered (io.debezium.pipeline.ChangeEventSourceCoordinator)
INFO Context created (io.debezium.pipeline.ChangeEventSourceCoordinator)
INFO No previous offset has been found (io.debezium.connector.mysql.MySqlSnapshotChangeEventSource)
INFO According to the connector configuration both schema and data will be snapshotted (io.debezium.connector.mysql.MySqlSnapshotChangeEventSource)
INFO Snapshot step 1 - Preparing (io.debezium.relational.RelationalSnapshotChangeEventSource)
INFO Snapshot step 2 - Determining captured tables (io.debezium.relational.RelationalSnapshotChangeEventSource)
INFO Read list of available databases (io.debezium.connector.mysql.MySqlSnapshotChangeEventSource)
INFO   list of available databases is: [my_database] (io.debezium.connector.mysql.MySqlSnapshotChangeEventSource)
INFO Read list of available tables in each database (io.debezium.connector.mysql.MySqlSnapshotChangeEventSource)
INFO  snapshot continuing with database(s): [my_database] (io.debezium.connector.mysql.MySqlSnapshotChangeEventSource)
INFO Adding table my_database.my_table1 to the list of capture schema tables (io.debezium.relational.RelationalSnapshotChangeEventSource)
INFO Adding table my_database.my_table2 to the list of capture schema tables (io.debezium.relational.RelationalSnapshotChangeEventSource)
INFO Adding table my_database.my_table3 to the list of capture schema tables (io.debezium.relational.RelationalSnapshotChangeEventSource)
INFO Adding table my_database.my_table4 to the list of capture schema tables (io.debezium.relational.RelationalSnapshotChangeEventSource)
INFO Snapshot step 3 - Locking captured tables [my_database.my_table1, my_database.my_table2, my_database.my_table3, my_database.my_table4] (io.debezium.relational.RelationalSnapshotChangeEventSource)
INFO Flush and obtain global read lock to prevent writes to database (io.debezium.connector.mysql.MySqlSnapshotChangeEventSource)
INFO Unable to flush and acquire global read lock, will use table read locks after reading table names (io.debezium.connector.mysql.MySqlSnapshotChangeEventSource)
INFO Snapshot step 4 - Determining snapshot offset (io.debezium.relational.RelationalSnapshotChangeEventSource)
INFO Snapshot step 5 - Reading structure of captured tables (io.debezium.relational.RelationalSnapshotChangeEventSource)
INFO Flush and obtain read lock for [my_database.my_table1, my_database.my_table2, my_database.my_table3, my_database.my_table4] tables (preventing writes) (io.debezium.connector.mysql.MySqlSnapshotChangeEventSource)
INFO Snapshot - Final stage (io.debezium.pipeline.source.AbstractSnapshotChangeEventSource)
ERROR Producer failure (io.debezium.pipeline.ErrorHandler)

io.debezium.DebeziumException: java.sql.SQLSyntaxErrorException: Unknown error 1227
    at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:85)
    at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:155)
    at io.debezium.pipeline.ChangeEventSourceCoordinator.executeChangeEventSources(ChangeEventSourceCoordinator.java:137)
    at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:109)
    at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:829)

Caused by: java.sql.SQLSyntaxErrorException: Unknown error 1227
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
    at io.debezium.jdbc.JdbcConnection.executeWithoutCommitting(JdbcConnection.java:1405)
    at io.debezium.connector.mysql.MySqlSnapshotChangeEventSource.tableLock(MySqlSnapshotChangeEventSource.java:460)
    at io.debezium.connector.mysql.MySqlSnapshotChangeEventSource.readTableStructure(MySqlSnapshotChangeEventSource.java:315)
    at io.debezium.connector.mysql.MySqlSnapshotChangeEventSource.readTableStructure(MySqlSnapshotChangeEventSource.java:47)
    at io.debezium.relational.RelationalSnapshotChangeEventSource.doExecute(RelationalSnapshotChangeEventSource.java:114)
    at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:76)

    ... 8 more

[jiri.p...@gmail.com]

Hi,

what version of MySQL are we talking about? Could you please also enable TRACE level logging so we see what exact SQL statement is done? Given the content of the log you are using a cloud-provider MySQL not on-premise. Is it correct?

J.

[Bohdan Bolshakov]

Hello!

My MySQL version is 5.7

Could you please also enable TRACE level logging so we what exact SQL statement is done see? Do you mean, should these settings be done for MySQL or can something be added at the connector level in the configuration?

Yes, it is cloud MySQL.

I understand you correctly., that error 1227 is related to the fact that access is denied, or in my case, is it a different error?

According to the MySQL documentation:

Error number: 1227; Symbol: ER_SPECIFIC_ACCESS_DENIED_ERROR; SQLSTATE: 42000
Message: Access denied; you need (at least one of) the %s privilege(s) for this operation

[jiri.p...@gmail.com]

Yes, I suppose that it is still not possible to lock tables.

J.

[Bohdan Bolshakov]

The problem turned out to be on the Manage MySQL side.

Locking LOCK TABLES was not enough to connect the connector. So how do I get the following error:

        ... 8 more

When issuing all GRANTS to the user that is used in the connection of the connector (which can be issued by the administrator of this Manage SQL):

+-----------------------------------------------------------------------------------------------------------------------------+

| Grants for test@%                                                                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------+

| GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'test'@'%' WITH GRANT OPTION      |  

| GRANT ALL PRIVILEGES ON `testdwh`.* TO 'test'@'%' WITH GRANT OPTION                                                            |           

| GRANT SELECT ON `mysql`.* TO 'test'@'%'                                                                                                                        | 

| GRANT SELECT ON `sys`.* TO 'test'@'%'                                                                                                                             |

| GRANT SELECT ON `performance_schema`.* TO 'test'@'%'                                                                                            |

| GRANT EXECUTE ON FUNCTION `sys`.`format_path` TO 'test'@'%'                                                                               |

| GRANT EXECUTE ON FUNCTION `sys`.`sys_get_config` TO 'test'@'%'                                                                          |

| GRANT EXECUTE ON FUNCTION `sys`.`format_time` TO 'test'@'%'                                                                               |

| GRANT EXECUTE ON FUNCTION `sys`.`format_bytes` TO 'test'@'%'                                                                             |

| GRANT EXECUTE ON FUNCTION `sys`.`ps_thread_account` TO 'test'@'%'                                                                   |

| GRANT EXECUTE ON FUNCTION `sys`.`format_statement` TO 'test'@'%'                                                                     |

+------------------------------------------------------------------------------------------------------------------------------+

I got the following error:

org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped.

        at io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:50)

        at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:116)

        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)

        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)

        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)

        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)

        at java.base/java.lang.Thread.run(Thread.java:829)

Caused by: io.debezium.DebeziumException: java.sql.SQLSyntaxErrorException: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation

        at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:85)

        at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:155)

        at io.debezium.pipeline.ChangeEventSourceCoordinator.executeChangeEventSources(ChangeEventSourceCoordinator.java:137)

        at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:109)

        ... 5 more

Caused by: java.sql.SQLSyntaxErrorException: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation

        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)

        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

        at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)

        at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)

        at io.debezium.jdbc.JdbcConnection.executeWithoutCommitting(JdbcConnection.java:1405)

        at io.debezium.connector.mysql.MySqlSnapshotChangeEventSource.tableLock(MySqlSnapshotChangeEventSource.java:460)

        at io.debezium.connector.mysql.MySqlSnapshotChangeEventSource.readTableStructure(MySqlSnapshotChangeEventSource.java:315)

        at io.debezium.connector.mysql.MySqlSnapshotChangeEventSource.readTableStructure(MySqlSnapshotChangeEventSource.java:47)

        at io.debezium.relational.RelationalSnapshotChangeEventSource.doExecute(RelationalSnapshotChangeEventSource.java:114)

        at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:76)

        ... 8 more

Must be issued to the RELOAD user. Manage MySQL of this cloud does not support such a GRANT.

Thanks everyone for the help!

[jiri.p...@gmail.com]

Thank you for the report - you've uncovered https://issues.redhat.com/browse/DBZ-5619

As a workaround please use https://debezium.io/documentation/reference/1.9/connectors/mysql.html#mysql-property-snapshot-locking-mode set to `none`. If you can guarantee that schema will not change during schea snapshot then it is safe option.

I am sorry for the inconvenience

J.

[jiri.p...@gmail.com]

Hi,

I am sorry it is Monday morning so pleas disregrard the bug descritpion. It is in fact correct. You need to have either RELOAD or FLUSH_TABLES privilege. If any of those two cannot be provided then the wrokaround mentioned still applies.

J.

[Bohdan Bolshakov]

Hi,

Thanks for the possible solutions to the problem. But in my case, "snapshot.mode": "none", it will not work, the data is constantly written in the database. I just need the option "snapshot.mode": "initial". I will ask the cloud provider whether we can do something or not. If this does not work, then there is an option with the “snapshot.mode” option: “none”.

[jiri.p...@gmail.com]

Please it is snapshot.locking.mode - different option from snapshot.mode. And the problem is not related to data change but schema change only -so if no DDLs are executed then you are safe to use it.

J.

[Bohdan Bolshakov]

I understand you, I'll have to try. Thank you.