Access Denied for User to Schema in MySQL as Source
This page describes how to resolve the insufficient permission error occurred when BladePipe tries to obtain the schema of a specified table in MySQL as Source.
Issue
The following error message occurs in the DataJob log:
Caused by: java.io.IOException: ErrorPacket [errorNumber=1044, fieldCount=-1, message=Access denied for user 'cloudcanal'@'11.0.0.0' to database 'performance_schema', sqlState=42000, sqlStateMarker=#]
with command: show full tables from `performance_schema` where Table_type = 'BASE TABLE'
at com.clougence.cloudcanal.mysql.worker.reader.vendor.driver.MysqlQueryExecutor.getResBody(MysqlQueryExecutor.java:88)
at com.clougence.cloudcanal.mysql.worker.reader.vendor.driver.MysqlQueryExecutor.query(MysqlQueryExecutor.java:47)
at com.clougence.cloudcanal.mysql.worker.reader.vendor.parse.conn.MysqlConnection.query(MysqlConnection.java:132)
at com.clougence.cloudcanal.mysql.worker.reader.incre.RemoteTableMeta.dumpTableMeta(RemoteTableMeta.java:217)
at com.clougence.cloudcanal.mysql.worker.reader.incre.RemoteTableMeta.rollback(RemoteTableMeta.java:174)
at com.clougence.cloudcanal.mysql.worker.reader.vendor.parse.AbstractMysqlEventParser.processTableMeta(AbstractMysqlEventParser.java:153)
at com.clougence.cloudcanal.mysql.worker.reader.vendor.parse.AbstractEventParser$1.run(AbstractEventParser.java:235)
Cause
The permissions on some schema are insufficient when BladePipe tries to obtain the schema in a Incremental task.
- The SHOW DATABASES permission is granted.
- The SELECT permission is not granted, and thus the
SHOW FULL TABLES
statement can not be executed.
Solution
Go to the Details page of the DataJob. Click Functions > Modify Parameters.
Select the Source tab. Enter userName in the search box to query the user name of MySQL account.
Obtain the host of MySQL account.
SELECT host FROM mysql.user WHERE user = '<userName>';
Grant the SELECT permission on the schema.
GRANT SELECT ON *.* TO '<user>'@'<host>';