AWS Glue MySQLSyntaxErrorException while storing data into AWS RDS / Aurora

Question

Recently restarted using AWS Glue.

Ran into a sudden com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException while the code o125.pyWriteDynamicFrame was executed. The error indicated Access denied for user 'admin'@'%' to database 'mysql' at ...

File "/tmp/Dump to S3", line 40, in datasink4 = glueContext.write_dynamic_frame.from_jdbc_conf(frame = dropnullfields3, catalog_connection = "gsipre_output", connection_options = {"dbtable": "myxp_speaker__myxp_livestream_sessions", "database": "mysql"}, transformation_ctx = "datasink4") File "/opt/amazon/lib/python3.6/site-packages/awsglue/dynamicframe.py", line 665, in from_jdbc_conf redshift_tmp_dir, transformation_ctx) File "/opt/amazon/lib/python3.6/site-packages/awsglue/context.py", line 312, in write_dynamic_frame_from_jdbc_conf catalog_id) File "/opt/amazon/lib/python3.6/site-packages/awsglue/context.py", line 328, in write_from_jdbc_conf return DataSink(j_sink, self).write(frame_or_dfc) File "/opt/amazon/lib/python3.6/site-packages/awsglue/data_sink.py", line 35, in write return self.writeFrame(dynamic_frame_or_dfc, info) File "/opt/amazon/lib/python3.6/site-packages/awsglue/data_sink.py", line 31, in writeFrame return DynamicFrame(self._jsink.pyWriteDynamicFrame(dynamic_frame._jdf, callsite(), info), dynamic_frame.glue_ctx, dynamic_frame.name + "_errors") File "/opt/amazon/spark/python/lib/py4j-0.10.7-src.zip/py4j/java_gateway.py", line 1257, in call answer, self.gateway_client, self.target_id, self.name) File "/opt/amazon/spark/python/lib/pyspark.zip/pyspark/sql/utils.py", line 63, in deco return f(*a, **kw) File "/opt/amazon/spark/python/lib/py4j-0.10.7-src.zip/py4j/protocol.py", line 328, in get_return_value format(target_id, ".", name), value) py4j.protocol.Py4JJavaError: An error occurred while calling o125.pyWriteDynamicFrame. : com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'admin'@'%' to database 'mysql' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942) 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.StatementImpl.executeUpdateInternal(StatementImpl.java:1540) at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2595) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1468) at org.apache.spark.sql.jdbc.glue.GlueJDBCSink$.createTable(GlueJDBCSink.scala:112) at org.apache.spark.sql.jdbc.glue.GlueJDBCSink$.save(GlueJDBCSink.scala:39) at com.amazonaws.services.glue.util.JDBCWrapper.writeDF(JDBCUtils.scala:823) at com.amazonaws.services.glue.sinks.MySQLDataSinkHelpers$.write(MySqlDataSink.scala:119) at com.amazonaws.services.glue.sinks.MySqlDataSink.writeDynamicFrame(MySqlDataSink.scala:146) at com.amazonaws.services.glue.DataSink.pyWriteDynamicFrame(DataSink.scala:58) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244) at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357) at py4j.Gateway.invoke(Gateway.java:282) at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132) at py4j.commands.CallCommand.execute(CallCommand.java:79) at py4j.GatewayConnection.run(GatewayConnection.java:238) at java.lang.Thread.run(Thread.java:748)



Here is the AWS Glue auto-generated script 
`datasink4 = glueContext.write_dynamic_frame.from_jdbc_conf(frame = dropnullfields3, catalog_connection = "gsipre_output", connection_options = {"dbtable": "tbl__name", "database": "mysql"}, transformation_ctx = "datasink4") `

Unsure where I have gone wrong.

Pam Pam · Accepted Answer · 2020-11-06T16:16:08

Make sure you granted all privileges in mysql.tbl__name to the user/group that you are using in 'gsipre_output'.

To do that, go to your MySQL Workbench and execute:

GRANT ALL on mysql.tbl__name to group your_user_group;

There could be other problems too like your IAM role without sufficient permission (check if it has AmazonRedshiftDataFullAccess) or even the ownership of the table, but the one I mentioned first is usually the most common issue.

AWS Glue MySQLSyntaxErrorException while storing data into AWS RDS / Aurora

1 Answers