This topic provides answers to frequently asked questions about development of AnalyticDB for MySQL.

Writes and queries

  • Q: What do I do if a large number of values specified by the IN operator are set?
    A: AnalyticDB for MySQL has a limit on the number of values specified by the IN operator. The default limit is 2,000. You can also execute a statement to modify this limit.
    set adb_config max_in_items_count=3000
  • Q: Why did the results of a query suddenly change?
    A: A sudden change in query results even when no data is updated may occur for the following reasons:
    • No ORDER BY clause is used together with the LIMIT clause. AnalyticDB for MySQL uses a distributed architecture. If a query uses multiple threads, the query ends when a number of rows that meet the value specified by the LIMIT clause are returned. Therefore, if no ORDER BY clause is used together with the LIMIT clause, the query results are random.
    • In a SELECT statement that uses a GROUP BY clause and aggregate functions, if a field is not contained in the GROUP BY clause and aggregate functions, a random value is returned for the field.

    If your problem persists, submit a ticket.

  • Q: How do I configure a query timeout period?
    A: You can execute the following statements to configure a query timeout period for a single query or all queries of a cluster:
    • A single query:
      /*query_timeout=xxxx*/select count(*) from t;
    • All queries of a cluster:
      set adb_config query_timeout=xxx;
  • Q: Why does the number of scanned rows for all tables in an execution plan differ from the total number of rows scanned by the query?

    A: This problem is typically caused by replicated tables. AnalyticDB for MySQL stores replicated tables in each node and repeatedly counts the number of rows when the replicated tables are scanned.

  • Q: Does AnalyticDB for MySQL support full-text indexes?

    Yes, AnalyticDB for MySQL supports full-text indexes.

  • Q: Is a limit imposed on the number of values specified by the IN operator when query results are returned in the JSON format?

    A: For AnalyticDB for MySQL V3.1.4 or earlier, the number of values specified by the IN operator cannot exceed 16. For AnalyticDB for MySQL V3.1.4 or later, no limit is imposed on the number of values specified by the IN operator.

    You can execute the following statement to query the version number of AnalyticDB for MySQL:
    select adb_version();
  • Q: What do I do if ERROR 1815 (HY000): [20034, XXX] : Left side of logical expression must evaluate to a boolean occurs?

    A: Change OR to ||.

  • Q: Why is the time contained in the query results of AnalyticDB for MySQL truncated?

    A: Check whether the time contained in the query results is displayed as expected on your MySQL client. If yes, check whether the query results are processed by other clients.

  • Q: Why does an error occur when the built-in function aes_encrypt() is used?
     SELECTCONVERT(AES_DECRYPT(AES_ENCRYPT('ABC123','key_string'),'key_string'),char(10));
    A: In aes_encrypt (varbinary x, varchar y), the input parameters are in the VARBINARY type. Execute the following statement:
    SELECT CONVERT(AES_DECRYPT(AES_ENCRYPT(CAST('ABC123' AS VARBINARY), 'key_string'), 'key_string'),char(10)); 
  • Q: Can AnalyticDB for MySQL use .csv.gz files from Object Storage Service (OSS) as external table data sources?

    A: Yes, AnalyticDB for MySQL can use these files as external table data sources. You must set compress_type to gzip.

  • Q: Why is data duplicated when the INSERT OVERWRITE INTO statement is used for an AnalyticDB for MySQL table that does not have a primary key?

    A: Automatic deduplication is not supported for tables that do not have primary keys.

  • Q: What do I do if the max_allowed_packet limit is exceeded when Data Transmission Service (DTS) is used to synchronize data?

    A: The default value of max_allowed_packet is 128 MB. If you want to increase this value, submit a ticket.

  • Q: ApsaraDB RDS can be connected when the EasySwoole framework is used. Why am I unable to connect to AnalyticDB for MySQL?

    A: SHOW statements are not supported in PreparedStatement.

  • Q: Why Column 'XXX' not in GROUP BY clause occurs when I use the SELECT * FROM TABLE GROUP BY KEY statement in AnalyticDB for MySQL?

    A: Query results cannot display all fields when you use the GROUP BY clause. You must specify column names in your statement. Example:
    select nation.name from nation group by nation.nationkey
  • Q: How do I view the running import tasks in the current database by executing statements?

    A: You can execute the following statement to view running import tasks:
    select * from INFORMATION_SCHEMA.kepler_meta_async_jobs where status = "RUNNING"
  • Q: Does AnalyticDB for MySQL support the INSERT ON DUPLICATE KEY UPDATE statement?

    A: AnalyticDB for MySQL allows you to assign only constants and column values to variables in the INSERT ON DUPLICATE KEY UPDATE statement. You cannot assign expressions in this statement.

  • Q: Can I use JOIN in UPDATE statements in AnalyticDB for MySQL?

    A: No, you cannot use JOIN in UPDATE statements in AnalyticDB for MySQL.

  • Q: Why am I unable to view the partition information after a table is created?

    A: After you create a partitioned table, you must execute the BUILD TABLE statement to view the partition information.

  • Q: What is the maximum number of connections that is allowed for an AnalyticDB for MySQL cluster?

    A: The maximum number of connections that is allowed for an AnalyticDB for MySQL cluster is 5,000. For more information, see Limits.

  • Q: Why is the hot data usage displayed on the monitoring page greater than the total disk usage displayed?

    A: An AnalyticDB for MySQL cluster contains multiple storage nodes. The total disk usage refers to the maximum amount of disk space occupied by a single storage node. The hot data usage refers to the total disk space occupied by all storage nodes.

Table schemas

  • Q: How do I modify the lifecycle of a table that has list partitions?

    A: You can execute the following statement to modify the lifecycle:
    ALTER TABLE db_name.table_name partitions N;
  • Q: Can I modify distribution or partition fields in AnalyticDB for MySQL?

    A: No, distribution or partition fields cannot be modified in AnalyticDB for MySQL. To modify distribution or partition fields, you can create a temporary table and execute the INSERT INTO SELECT statement to import data from your table to the temporary table. Delete your table, use new distribution or partition fields to create a table, and then execute the INSERT INTO SELECT statement to import data from the temporary table to the new table.

  • Q: Does an internal or public endpoint need to be selected when I create an external table of OSS?

    A: An internal endpoint must be selected because AnalyticDB for MySQL connects to OSS by using an internal network.

  • Q: How do I drop indexes?

    A: For information about how to drop indexes, see ALTER TABLE.

Cluster information acquisition

  • Q: How do I obtain the AccessKey ID and AccessKey secret of an Alibaba Cloud account or Resource Access Management (RAM) user?
    A: Perform the following steps:
    • Alibaba Cloud account
      1. Log on to the Alibaba Cloud Management Console with an Alibaba Cloud account.
      2. Move the pointer over the profile picture in the upper-right corner and click AccessKey Management.
      3. In the dialog box that appears, click Use Current AccessKey Pair. AccessKey ID and AccessKey Secret are displayed.
    • RAM user
      1. Log on to the RAM console.
      2. Click a user name to go to the user management page. Find User AccessKeys in the lower part of the page.
  • Q: How do I monitor the disk usage?

    A: You can use CloudMonitor to configure alert rules on the disk usage of a cluster in reserved mode. For example, you can configure the following alert rule: If the disk usage is equal to or greater than 90%, AnalyticDB for MySQL blocks write requests and only processes read requests.

    Note In elastic mode, you do not need to configure alert rules on the disk usage.

    To prevent the disk usage from reaching the threshold, you can configure an alert rule to monitor the disk usage. When the disk usage reaches the specified threshold, an alert is immediately sent to the specified contacts. This helps you manage the disk space as soon as issues arise to ensure the normal operation of your business. For more information, see Set an alert rule.

  • Q: How do I check from which user or client IP address burst connections are established?
    A: In the left-side navigation pane, click Diagnostics and Optimization. On the page that appears, click the Connection Information tab. Then, you can view the statistics of Number of User Connections and Number of Connections to Client IP Address.2
  • Q: How do I check the table size?
    A: In the left-side navigation pane, click Monitoring Information. On the page that appears, click the Table Information Statistics tab. Then, you can view table statistics such as the table size.1
  • Q: What is the maximum number of tables?

    A: For information about the maximum number of tables, see Limits.

Others

  • Q: Can I set SQL variables in AnalyticDB for MySQL?

    A: No, you cannot set SQL variables in AnalyticDB for MySQL.

  • Q: How long does it take to complete an upgrade in AnalyticDB for MySQL? Does an upgrade affect my business?

    A: It typically takes about 30 minutes to 2 hours to complete an upgrade. The time consumed is determined by the cluster specifications and the volume of data. Your business may be interrupted during the upgrade process. Make sure that your applications are configured with automatic reconnection policies. If your business requires a high success rate for queries, we recommend that you perform upgrades during nighttime or off-peak hours.

  • Q: How do I stop an asynchronous import or export task?
    A: In the left-side navigation pane, click Diagnostics and Optimization. On the page that appears, click the Import/Export Task tab to view the asynchronous task name in the Asynchronous Task Name column. Then, execute the CANCEL JOB "${name of the asynchronous task}" statement to cancel the asynchronous task. For more information about asynchronous import or export tasks, see Asynchronously submit an import or export task. 3