Simple Log Service:How do I fix syntax errors that occur when I pull data from ApsaraDB RDS for MySQL?

Transformation rule

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

Error log

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

Troubleshooting method

The error occurs because the syntax is invalid. The error is common when the resource function res_rds_mysql, res_log_logstore_pull, or res_oss_file function is independently used in the Simple Log Service console.

Solution

Use the resource functions in combination with other functions such as e_table_map or e_search_table_map because the resource functions cannot be independently used in the Simple Log Service console.

Transformation rule

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

Error log

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

Troubleshooting method

Check whether the table or sql parameter is configured.

Solution

If the table parameter is not configured, you must specify an SQL statement to find the required table. If you do not specify the SQL statement in this situation, the required table cannot be found, and the data cannot be pulled. You must configure one of the table and sql parameters.

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 log

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

Troubleshooting method

The fields in the output_fields parameter do not exist in the required table.

Solution

Confirm the fields that are contained in the table and replace the invalid fields in the output_fields parameter.

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 log

username not a string type\nInvalid Settings

Troubleshooting method

The value of the username parameter is not of the string type.

Solution

Specify a value of the string type.

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 log 1

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

Error log 2

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

Troubleshooting method

Check whether the configurations are valid, the network is connected, or the required whitelist is configured on the required ApsaraDB RDS for MySQL instance. If a connection error occurs, the cause field in the error log contains the detailed cause of the error. You can troubleshoot the error based on the cause. Common errors include lack of permissions, incorrect passwords, and invalid addresses.

Solution

Reconfigure the functions and 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 log

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

Troubleshooting method

The SQL syntax is invalid. Check that the SQL syntax used to read data from and write data to databases is valid. If an error is found in the SQL syntax used to write data to databases, the error fetch data error may occur. In this situation, you must analyze the cause of the error.

Solution

Correct the SQL syntax. Resource functions support only SELECT.

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 log

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

Troubleshooting method

The error occurs because a specific whitelist of the required ApsaraDB RDS for MySQL instance is cleared. If an error such as a network interruption occurs during continuous transformation, the data transformation job is automatically retried. If an error such as revoked permissions or incorrectly deleted tables occurs, you must manually grant the permissions or restore the tables.

Solution

Check whether the tables or permissions are changed in the databases on the ApsaraDB RDS for MySQL instance based on the cause of the error.