5.8.8 Autopilot Report Table

The heatwave_autopilot_report table is a temporary table. It contains data from the last execution of Advisor or Auto Parallel Load. Data is only available for the current session and is lost when the session terminates or when the server shuts down.

When Advisor or Auto Parallel Load run, they send detailed output to the heatwave_autopilot_report table in the sys schema. This includes execution logs and generated load scripts.

As of MySQL 8.0.32, MySQL HeatWave uses the heatwave_autopilot_report table. All versions prior to MySQL 8.0.32 use the heatwave_advisor_report and heatwave_load_report tables for reporting Autopilot Advisor and load activities. For more information, refer to Advisor Report Table.

Autopilot Report Table Query Examples

After the user initiates calls to Advisor or Auto Parallel Load, query the heatwave_autopilot_report table to review the generated reports and logs, as shown in the following examples:

View warning information:

mysql> SELECT log FROM sys.heatwave_autopilot_report WHERE type="warn";

View error information if Advisor or Auto Parallel Load stop unexpectedly:
```
mysql> SELECT log FROM sys.heatwave_autopilot_report WHERE type="error";
```
View the generated DDL statements for Advisor recommendations, or to see the commands that would be executed by Auto Parallel Load in normal mode:
```
mysql> SELECT log->>"$.sql" AS "SQL Script" 
          FROM sys.heatwave_autopilot_report 
          WHERE type = "sql" 
          ORDER BY id;
```
Concatenate Advisor or Auto Parallel Load generated DDL statements into a single string to copy and paste for execution. The group_concat_max_len variable sets the result length in bytes for the GROUP_CONCAT() function to accommodate a potentially long string. The default group_concat_max_len setting is 1024 bytes.
```
mysql> SET SESSION group_concat_max_len = 1000000;
mysql> SELECT GROUP_CONCAT(log->>"$.sql" SEPARATOR ' ') 
          FROM sys.heatwave_autopilot_report 
          WHERE type = "sql" 
          ORDER BY id;
```

For Advisor, retrieve Auto Query Time Estimation data in JSON format:

mysql> SELECT log 
          FROM sys.heatwave_autopilot_report 
          WHERE stage = "QUERY_INSIGHTS" AND type = "info";

For Advisor, retrieve Auto Query Time Estimation data in SQL table format:

mysql> SELECT log->>"$.query_id" AS query_id,
          log->>"$.session_id" AS session_id,
          log->>"$.query_text" AS query_text,
          log->>"$.runtime_estimated_ms" AS runtime_estimated_ms,
          log->>"$.runtime_executed_ms" AS runtime_executed_ms,
          log->>"$.comment" AS comment
          FROM sys.heatwave_autopilot_report 
          WHERE stage = "QUERY_INSIGHTS" AND type = "info"
          ORDER BY id;

For Auto Parallel Load, view the number of load commands generated:

mysql> SELECT Count(*) AS "Total Load Commands Generated"
          FROM sys.heatwave_autopilot_report 
          WHERE type = "sql" ORDER BY id;

For Auto Parallel Load, view load script data for a particular table:

mysql> SELECT log->>"$.sql"
          FROM sys.heatwave_autopilot_report 
          WHERE type="sql" AND log->>"$.schema_name" = "db0" AND log->>"$.table_name" = "tbl" 
          ORDER BY id;

What's Next

Learn more about Advisor Report Table.