Re: Debezium in AWS RDS?

1,537 views
Skip to first unread message

Randall Hauch

unread,
Oct 18, 2016, 11:58:46 AM10/18/16
to Scott Ferguson, debezium
The snapshot logic of the MySQL connector requires locking all tables while the structure of the database and tables can be examined atomically, ensuring that the tables are not modified while we’re reading their definition. "FLUSH TABLES WITH READ LOCK” is the easiest way to do this with MySQL, but if there’s an alternative that works in RDS then we can try to add that as a fallback.

The code that does this is here: https://github.com/debezium/debezium/blob/master/debezium-connector-mysql/src/main/java/io/debezium/connector/mysql/SnapshotReader.java#L188. This obtains the lock in Step 2, and releases the lock in Step 7 (or step 9 if minimal locking is not used).

On October 18, 2016 at 10:50:07 AM, Scott Ferguson (sc...@98ws.net) wrote:

Has anyone run Debezium against MySQL (5.6.23) in AWS RDS?
It seems that RDS doesn't allow "FLUSH TABLES WITH READ LOCK", http://stackoverflow.com/a/19571531.
Without that ability Debezium fails immediately.

2016-10-17 18:26:48,799 INFO   MySQL|itw|snapshot  Step 0: disabling autocommit and enabling repeatable read transactions   [io.debezium.connector.mysql.SnapshotReader]
2016-10-17 18:26:48,901 INFO   MySQL|itw|snapshot  Step 1: start transaction with consistent snapshot   [io.debezium.connector.mysql.SnapshotReader]
2016-10-17 18:26:49,092 INFO   MySQL|itw|snapshot  Step 2: flush and obtain global read lock (preventing writes to database)   [io.debezium.connector.mysql.SnapshotReader]
2016-10-17 18:26:49,246 ERROR  MySQL|itw|snapshot  Failed due to error: Aborting snapshot after running 'FLUSH TABLES WITH READ LOCK': Access denied for user 'trinity'@'%' (using password: YES)   [io.debezium.connector.mysql.SnapshotReader]
org.apache.kafka.connect.errors.ConnectException: Access denied for user 'trinity'@'%' (using password: YES) Error code: 1045; SQLSTATE: 28000.
at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:141)
at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:120)
at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:452)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Access denied for user 'trinity'@'%' (using password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963)
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.executeInternal(StatementImpl.java:839)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:739)
at io.debezium.jdbc.JdbcConnection.lambda$execute$1(JdbcConnection.java:242)
at io.debezium.jdbc.JdbcConnection.execute(JdbcConnection.java:259)
at io.debezium.jdbc.JdbcConnection.execute(JdbcConnection.java:236)
at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:197)
... 1 more


These are the grants for user 'trinity':
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'trinity'@'%' IDENTIFIED BY PASSWORD <secret> WITH GRANT OPTION

Thanks,
Scott
--
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 post to this group, send email to debe...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/f8bf5e0e-0904-467c-9c28-408037ae5cf2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Scott Ferguson

unread,
Oct 18, 2016, 12:55:44 PM10/18/16
to debezium
Thanks for the insight. Have you considered the approach Maxwell uses as a fallback?  (querying the information_schema tables)

Randall Hauch

unread,
Oct 18, 2016, 2:04:52 PM10/18/16
to Scott Ferguson, debezium
Yes, but that doesn’t give us the CREATE TABLE statements, which we need to capture the DDL statements for the databases.


On October 18, 2016 at 11:55:46 AM, Scott Ferguson (sc...@98ws.net) wrote:

Thanks for the insight. Have you considered the approach Maxwell uses as a fallback?  (querying the information_schema tables)
--
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 post to this group, send email to debe...@googlegroups.com.

Rodrigo Vieira

unread,
Oct 21, 2016, 9:51:39 AM10/21/16
to debezium
We faced the same issue using Debezium + RDS because of the RDS privileges limitations.

As a workaround we are considering create an EC2 Mysql slave from our RDS, enable the binary log on this Mysql slave and configure the Debezium to use this mysql slave.

Moran Shemesh

unread,
Dec 1, 2016, 3:17:48 AM12/1/16
to debezium
+1

"FLUSH TABLES WITH READ LOCK" requires the SUPER privilege, which is not allowed in RDS, not even for the master user.

A possible alternative can be to execute "FLUSH TABLES <table_name> WITH READ LOCK" (one table at a time), which only requires the LOCK TABLES privilege.

Will it be possible to please consider this as a fallback mechanism?

Thanks,
Moran

Randall Hauch

unread,
Dec 1, 2016, 9:01:10 AM12/1/16
to debe...@googlegroups.com, Moran Shemesh
Yes, it may be possible to try to sequentially lock each table until all tables are locked, then read the schema of each table, and finally to sequentially unlock each table. Thanks for the suggestion! I’ve updated https://issues.jboss.org/browse/DBZ-140 with these details.

What about "UNLOCK TABLES”? There is no corresponding “UNLOCK TABLE <table_name>”. Is RDS set up to allow any user to unlock the table locks it has acquired, even through “FLUSH TABLES …”?

Finally, is there any information in the system variables (or something else) that the connector can use to know it is talking to an RDS server? It’d be great if someone could contribute some pseudocode that determines when that is the case. Again, please add to https://issues.jboss.org/browse/DBZ-140.

Thanks!
This e-mail, as well as any attached document, may contain material which is confidential and privileged and may include trademark, copyright and other intellectual property rights that are proprietary to Kenshoo Ltd,  its subsidiaries or affiliates ("Kenshoo"). This e-mail and its attachments may be read, copied and used only by the addressee for the purpose(s) for which it was disclosed herein. If you have received it in error, please destroy the message and any attachment, and contact us immediately. If you are not the intended recipient, be aware that any review, reliance, disclosure, copying, distribution or use of the contents of this message without Kenshoo's express permission is strictly prohibited. --

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 post to this group, send email to debe...@googlegroups.com.

Moran Shemesh

unread,
Dec 3, 2016, 2:28:25 PM12/3/16
to debezium
The "lock tables" privilege also allows the user to execute "unlock tables", which will unlock all table locks it has acquired.

As for knowing if the server is an RDS server, we can do that by checking if the @@basedir system variable is "/rdsdbbin/mysql/" (though this is not an official documented method).


Thanks,
Moran

Randall Hauch

unread,
Dec 5, 2016, 10:06:58 AM12/5/16
to debe...@googlegroups.com, Moran Shemesh
Thanks, Moran!

Cheng Chen

unread,
Dec 19, 2016, 7:38:02 AM12/19/16
to debezium
Any update about this? +1 for request :)

Randall Hauch

unread,
Dec 20, 2016, 11:21:16 PM12/20/16
to debe...@googlegroups.com, Cheng Chen
Unfortunately, still no additional updates. 0.3.6 will likely go out the door tomorrow, but it’d be nice to figure out this soon. Are there any people with RDS experience willing to work on a patch and contribute a pull request with the proposed suggestions? If anyone is interested but needs some pointers or a bit of guidance, please ping me on https://gitter.im/debezium/dev.

Lakshmi G.S

unread,
Mar 17, 2017, 6:16:31 AM3/17/17
to debezium
+1
I am facing the same issue in AWS RDS. Is there an update on this?

Lakshmi G.S

unread,
Mar 17, 2017, 6:38:31 AM3/17/17
to debezium
I am using 0.4

Randall Hauch

unread,
Mar 17, 2017, 10:05:38 AM3/17/17
to debe...@googlegroups.com, Lakshmi G.S
The RDS support added in 0.4.0 and described by https://issues.jboss.org/browse/DBZ-140 had an issue, and DBZ-140 was reopened, and the fix was applied to the master branch and will be incorporated in the 0.4.1 release that should be out later today.

Best regards,

Randall

Lakshmi G.S

unread,
Mar 20, 2017, 4:01:50 AM3/20/17
to debezium
Thanks. Its working fine now.
Reply all
Reply to author
Forward
0 new messages