All Products
Search
Document Center

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

Last Updated:Mar 18, 2024

If a transformation rule requires data to be pulled from ApsaraDB RDS for MySQL, errors may occur when the data is pulled or updated. This topic describes the errors and provides solutions.

After the data transformation engine reads data from a Logstore, the engine transforms the data. If the transformation rule requires data to be pulled from external resources such as Object Storage Service (OSS), ApsaraDB RDS, and other Logstores, errors may occur when the data is pulled or updated.

Incorrect use of resource functions in the Simple Log Service console

  • 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.

Invalid parameter settings

  • 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.

Invalid output fields

  • 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.

Invalid parameter type settings

  • 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.

Network or permission errors

  • 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.

SQL syntax errors

  • 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.

Errors during continuous transformation

  • 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.