MySQL :: MySQL HeatWave User Guide :: 2.15 Troubleshooting

Problem: Queries are not offloaded.

Solution A: Your query contains an unsupported predicate, function, operator, or has encountered some other limitation. See Section 2.3.1, “Query Prerequisites”.
Solution B: Query execution time is less than the query cost threshold.

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 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 HeatWave by querying LOAD_STATUS data from 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 6.4.8, “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 HeatWave Cluster has failed. To determine the status of the HeatWave Cluster, run the following statement:
```
mysql> SHOW GLOBAL STATUS LIKE 'rapid_plugin_bootstrapped';
+---------------------------------+------------+
| Variable_name                   | Value      |
+---------------------------------+------------+
| rapid_plugin_bootstrapped       | YES        |
+---------------------------------+------------+
```
See Chapter 5, System and Status Variables for rapid_plugin_bootstrapped status values.

If the HeatWave Cluster has failed, restart it in the Console and reload the data if necessary. The HeatWave recovery mechanism should reload the data automatically.

Problem: You cannot alter the table definition to exclude a column, define a string column encoding, or define data placement keys.

Solution: Before MySQL 8.0.31, DDL operations are not permitted on tables defined with a secondary engine. In those releases, column attributes must be defined before or at the same that you define a secondary engine for a table. If you need to perform a DDL operation on a table that is defined with a secondary engine in those releases, remove the SECONDARY_ENGINE option first:

mysql> ALTER TABLE orders SECONDARY_ENGINE = NULL;

For more information, see Section 2.4, “Modifying Tables”.

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

Solution: 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 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 Autopilot, in the MySQL HeatWave on AWS Service Guide.
For HeatWave in Oracle Database Service for Azure (ODSA), see Provisioning HeatWave Nodes.

Avoid or rewrite queries that produce a Cartesian product. For more information, see Section 2.9.7, “Running Queries”.

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

Solution: A TEXT-type column value larger than 65532 bytes is rejected during SECONDARY_LOAD operations. Reduce the size of the TEXT-type column value to less than 65532 bytes or exclude the column before loading the table. See Section 2.2.2.1, “Excluding 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 65532 bytes.”

Solution: TEXT-type values larger than 65532 bytes are rejected during change propagation. Reduce the size of TEXT-type values to less than 65532 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 2.2.7, “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 HeatWave Cluster or service is not active or online, this means that the load cannot start because a HeatWave Cluster is not attached to the MySQL DB System or is not active. In this case, provision and enable a 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 HeatWave Cluster memory is insufficient to load all of the tables, the estimated table size is too large for 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 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.