11.1.1 MySQL HeatWave Issues

Problem: Queries are not offloaded.

Solution A: Your query contains an unsupported predicate, function, operator, or has encountered some other limitation. See Section 5.4.1, “Requirements for Running Queries”.
Solution B: Query execution time is less than the query cost threshold.
MySQL HeatWave is designed for fast execution of large analytic queries. Smaller, simpler queries, such as those that use indexes for quick lookups, often execute faster on the MySQL DB System. To avoid offloading inexpensive queries to MySQL HeatWave, the optimizer uses a query cost estimate threshold value. Only queries that exceed the threshold value on the MySQL DB System are considered for offload.
The query cost threshold unit value is the same unit value used by the MySQL optimizer for query cost estimates. The threshold is 100000.00000. The ratio between a query cost estimate value and the actual time required to execute a query depends on the type of query, the type of hardware, and MySQL DB System configuration.
To determine the cost of a query on the MySQL DB System:
1. Disable use_secondary_engine to force MySQL DB System execution.
```
mysql> SET SESSION use_secondary_engine=OFF;
```
2. Run the query using EXPLAIN.
```
mysql> EXPLAIN select_query;
```
3. Query the Last_query_cost status variable. If the value is less than 100000.00000, the query cannot be offloaded.
```
mysql> SHOW STATUS LIKE 'Last_query_cost';
```

Solution C: The table you are querying is not loaded. You can check the load status of a table in MySQL HeatWave by querying LOAD_STATUS data from MySQL HeatWave Performance Schema tables. For example:

      mysql> USE performance_schema;
      mysql> SELECT NAME, LOAD_STATUS FROM rpd_tables,rpd_table_id
                WHERE rpd_tables.ID = rpd_table_id.ID;
      +------------------------------+---------------------+
      | NAME                         | LOAD_STATUS         |
      +------------------------------+---------------------+
      | tpch.supplier                | AVAIL_RPDGSTABSTATE |
      | tpch.partsupp                | AVAIL_RPDGSTABSTATE |
      | tpch.orders                  | AVAIL_RPDGSTABSTATE |
      | tpch.lineitem                | AVAIL_RPDGSTABSTATE |
      | tpch.customer                | AVAIL_RPDGSTABSTATE |
      | tpch.nation                  | AVAIL_RPDGSTABSTATE |
      | tpch.region                  | AVAIL_RPDGSTABSTATE |
      | tpch.part                    | AVAIL_RPDGSTABSTATE |
      +------------------------------+---------------------+

For information about load statuses, see Section 8.1.10, “The rpd_tables Table”.

Alternatively, run the following statement:

mysql> ALTER TABLE tbl_name SECONDARY_LOAD;

The following error is reported if the table is already loaded:

mysql> ERROR 13331 (HY000): Table is already loaded.

Solution D: The MySQL HeatWave Cluster has failed. To determine the status of the MySQL HeatWave Cluster, run the following statement:
```
      mysql> SHOW GLOBAL STATUS LIKE 'rapid_plugin_bootstrapped';
      +---------------------------------+------------+
      | Variable_name                   | Value      |
      +---------------------------------+------------+
      | rapid_plugin_bootstrapped       | YES        |
      +---------------------------------+------------+
```
See Chapter 9, System and Status Variables for rapid_plugin_bootstrapped status values.
If the MySQL HeatWave Cluster has failed, restart it in the MySQL HeatWave Console and reload the data if necessary. The MySQL HeatWave recovery mechanism should reload the data automatically.

Problem: You have encountered an out-of-memory error when executing a query.

Solution: MySQL HeatWave optimizes for network usage rather than memory. If you encounter out of memory errors when running a query, try running the query with the MIN_MEM_CONSUMPTION strategy by setting rapid_execution_strategy before executing the query:

mysql> SET SESSION rapid_execution_strategy = MIN_MEM_CONSUMPTION;

Also consider checking the size of your data by performing a cluster size estimate. If your data has grown substantially, you may require additional MySQL HeatWave nodes.

For MySQL HeatWave on OCI, see Generating a Node Count Estimate in the MySQL HeatWave on OCI Service Guide.
For MySQL HeatWave on AWS, see Estimating Cluster Size with MySQL HeatWave Autopilot in the MySQL HeatWave on AWS Service Guide.
For MySQL HeatWave for Azure, see Provisioning MySQL HeatWave Nodes in the MySQL HeatWave for Azure Service Guide.

Avoid or rewrite queries that produce a Cartesian product. For more information, see Diagnosing and Resolving Out-of-Memory (OOM) Errors in MySQL Query Execution.

Problem: A table load operation fails with “ERROR HY000: Error while running parallel scan.”

Solution: A TEXT type column value larger than 65535 bytes is rejected during SECONDARY_LOAD operations. Reduce the size of the TEXT type column value to less than 65535 bytes or exclude the column before loading the table. See Section 4.2.6.1, “Exclude Table Columns”.

Problem: Change propagation fails with the following error: “Blob/text value of n bytes was encountered during change propagation but RAPID supports text values only up to 65535 bytes.”

Solution: TEXT type values larger than 65335 bytes are rejected during change propagation. Reduce the size of TEXT type values to less than 65535 bytes. Should you encounter this error, check the change propagation status for the affected table. If change propagation is disabled, reload the table. See Section 4.2.7, “About Change Propagation”.

Problem: A warning was encountered when running Auto Parallel Load.

Solution: When Auto Parallel Load encounters an issue that produces a warning, it automatically switches to dryrun mode to prevent further problems. In this case, the load statements generated by the Auto Parallel Load utility can still be obtained using the SQL statement provided in the utility output, but avoid those load statements or use them with caution, as they may be problematic.

If a warning message indicates that the MySQL HeatWave Cluster or service is not active or online, this means that the load cannot start because a MySQL HeatWave Cluster is not attached to the MySQL DB System or is not active. In this case, provision and enable a MySQL HeatWave Cluster and run Auto Parallel Load again.
If a warning message indicates that MySQL host memory is insufficient to load all of the tables, the estimated dictionary size for dictionary-encoded columns may be too large for MySQL host memory. Try changing column encodings to VARLEN to free space in MySQL host memory.
If a warning message indicates that MySQL HeatWave Cluster memory is insufficient to load all of the tables, the estimated table size is too large for MySQL HeatWave Cluster memory. Try excluding certain schemas or tables from the load operation or increase the size of the cluster.
If a warning message indicates that a concurrent table load is in progress, this means that another client session is currently loading tables into MySQL HeatWave. While the concurrent load operation is in progress, the accuracy of Auto Parallel Load estimates cannot be guaranteed. Wait until the concurrent load operation finishes before running Auto Parallel Load.

Problem: During retrieval of the generated Auto Parallel Load or Advisor DDL statements, an error message indicates that the heatwave_autopilot_report table or the heatwave_advisor_report table or the heatwave_load_report table does not exist. For example:

      mysql> SELECT log->>"$.sql" AS "SQL Script" 
                FROM sys.heatwave_autopilot_report 
                WHERE type = "sql" 
                ORDER BY id;
      ERROR 1146 (42S02): Table 'sys.heatwave_autopilot_report' does not exist
      
      mysql> SELECT log->>"$.sql" AS "SQL Script" 
                FROM sys.heatwave_advisor_report 
                WHERE type = "sql" 
                ORDER BY id;
      ERROR 1146 (42S02): Table 'sys.heatwave_advisor_report' does not exist
      
      
      mysql> SELECT log->>"$.sql" AS "SQL Script" 
                FROM sys.heatwave_load_report 
                WHERE type = "sql" 
                ORDER BY id;
      ERROR 1146 (42S02): Table 'sys.heatwave_load_report' does not exist

Solution: This error can occur when querying a report table from a different session. Query the report table using the same session that issued the Auto Parallel Load or Advisor CALL statement. This error also occurs if the session used to call Auto Parallel Load or Advisor has timed out or was terminated. In this case, run Auto Load or Advisor again before querying the report table.