Troubleshoot RDS MySQL data retrieval errors in data transformation

Transformation rule

res_rds_mysql(address="xx",username="xx",password="xx",database="xx")

Error message

aliyun.log.logexception.LogException: {"errorCode": "InvalidEtlConfig", "errorMessage": "ETL config doesn't pass security check, detail: invalid type detected: <class '_ast.Expr'>", "requestId": ""}

Cause

The res_rds_mysql, res_log_logstore_pull, or res_oss_file function is called independently in the Simple Log Service console. Resource functions cannot run on their own and must be wrapped inside a transformation function.

Solution

Wrap the resource function inside the e_table_map function. For example, use e_table_map(res_rds_mysql(...), ...) instead of calling res_rds_mysql(...) alone.

Transformation rule

e_table_map(res_rds_mysql(address="xx",username="xx",password="xx",database="xx"),field="processid",output_fields=["name","xixi"])

Error message

When sql is not set, table must be set\nDetail: None

Cause

Neither the table nor the sql parameter is specified. The function requires at least one of these parameters to identify the target database table.

Solution

Add the table parameter to specify the database table name, or add the sql parameter with a SELECT statement. At least one of these two parameters must be specified.

Transformation rule

e_table_map(res_rds_mysql(address="x",username="xx",password="xx",database="xx",table="test"),field="processid",output_fields=["name","xixi"])

Error message

trans_comp_lookup: output field xixi doesn't exist in lookup table\nDetail: None

Cause

One or more fields specified in the output_fields parameter do not exist in the MySQL database table.

Solution

Verify the column names in the MySQL database table and update the output_fields values to match the actual column names exactly.

Transformation rule

e_table_map(res_rds_mysql(address="xxx",username=1234,password="xx",database="xx",table="xx"),field="processid",output_fields=["name","xixi"])

Error message

username not a string type\nInvalid Settings

Cause

The username parameter value is an integer (1234) instead of a string. All connection parameters require string values.

Solution

Enclose all string-type parameter values in double quotation marks. For example, change username=1234 to username="1234".

Transformation rule

e_table_map(res_rds_mysql(address="xxx",username=xxx,password="xx",database="test999",table="xx"),field="processid",output_fields=["name","xixi"])

Error message 1

message:  Database connection failed, cause: (1045, "Access denied for user 'user1'@'192.0.2.1' (using password: YES)")

Error message 2

message:  Database connection failed, cause: (1049, "Unknown database 'test999')

Cause

The database connection failed. The cause field in the error message indicates the specific reason. Common causes include an incorrect username or password, an invalid database address or name, a missing whitelist entry on the ApsaraDB RDS for MySQL instance, or network connectivity issues between Simple Log Service and the RDS instance.

Solution

Examine the cause field in the error message to identify the issue. Verify that the address, username, password, and database parameter values are correct. Confirm that the IP address of Simple Log Service is added to the whitelist of the ApsaraDB RDS for MySQL instance. After fixing the configuration, restart the data transformation job.

Transformation rule

e_table_map(res_rds_mysql(address="xxx",username=xxx,password="xx",database="xx",sql="inset into test values(1,"aini")",field="processid",output_fields=["name","xixi"]))

Error message

\"errorMessage\": \"The sql_query field only supports database query syntax\\nInvalid Settings \\\"insert into test values(1,aini)\\\"\\nDetail: None\", \"requestId\": \"\"}

Cause

The sql parameter contains an INSERT statement. Resource functions only support SELECT statements for reading data. Write operations such as INSERT, UPDATE, and DELETE are not allowed. Invalid SQL syntax may also trigger a fetch data error.

Solution

Replace the SQL statement with a valid SELECT query. For example, use sql="SELECT * FROM test" instead of INSERT, UPDATE, or DELETE statements.

Transformation rule

e_table_map(res_rds_mysql(address="xxx",username=xxx,password="xx",database="xx",table="test,field="processid",output_fields=["name","xixi"],refresh_interval=30))

Error message

\"errorMessage\": \"Database connection failed, cause: (2003, \\\"Can't connect to MySQL server on 'rm-wz9z68i4itrk4v8d9yo.mysql.rds.aliyuncs.com' (timed out))

Cause

The whitelist of the ApsaraDB RDS for MySQL instance was modified or cleared after the data transformation job started. During continuous transformation, transient errors such as brief network interruptions trigger automatic retries. However, persistent errors such as revoked whitelist permissions or deleted database tables require manual intervention.

Solution

Examine the cause field in the error message. Verify that the whitelist configuration on the ApsaraDB RDS for MySQL instance has not changed. Confirm that the database tables and user permissions are intact. Restore the whitelist, table, or permissions as needed, and then restart the data transformation job.