SHOW REPRODUCTION

Syntax

SQL

SHOW REPRODUCTION [INTO OUTFILE <file_name>];

Remarks

This command collects the information necessary for reproducing the errors that happen during query optimization and code generation, including:
- Data Definition Language (DDL) for all database objects relevant to the query
- Optimizer caches
- Optimizer statistics
- Global and session variables that affect the query plan
- Table statistics metadata
- Sampling cache for filter selectivities
- Sampling cache for row counts
- Autostats results cache
- Query text
- Error code and message
Note that the data collected for each query might be different depending on what is relevant to the query. The data collected will be useful for troubleshooting by the SingleStore Technical Support team.
Prior to running the SHOW REPRODUCTION command, you need to save the error reproduction information for the query for which you want to collect the troubleshooting data. Use the following syntax to save the error reproduction information.
SQL
```
<query> OPTION(save_repro_info={1 | True});
```
After the query fails with an error, run the SHOW REPRODUCTION command to collect and display the troubleshooting data. The troubleshooting information will be displayed in JSON string format.
This command outputs troubleshooting data for queries that fail in the compilation phase as opposed to the profile_for_debug session variable, which outputs query optimizer plans for fully or partially executed queries. The SHOW REPRODUCTION command will not produce error details for an executed query even if the OPTION(save_repro_info=1) clause is attached to the query.

Starting with 7.8, the following commands can be used fully or partially executed queries without setting the profile_for_debug session variable:
SQL
```
PROFILE REPRO <query>;
SHOW REPRODUCTION;
```
SingleStore will continue to support the use of the profile_for_debug session variable with the PROFILE and SHOW PROFILE JSON commands for backward compatibility.
This command works with SELECT and INSERT ... SELECT statements.
This command must be run on a master aggregator node or a child aggregator node. (See Node Requirements for SingleStore Commands for more information.)
To write the output of the SHOW REPRODUCTION command to a file, use the INTO OUTFILE file_name clause and specify the absolute or the relative path to the file as the file_name parameter. This creates a new file at the specified location and writes the error details to the file. (Note that an error will be thrown if the file already exists.) The output file can be a JSON or a plain text file. You can send this output file to the SingleStore Technical Support team for troubleshooting and further analysis.
A subsequent query that produces an error with the OPTION(save_repro_info=1) clause will overwrite the previously collected information, if any.
The error information produced with the OPTION(save_repro_info=1) clause will be cleared after the successful execution of any query or when the connection is closed.

Examples

Collect troubleshooting information for a SELECT query

The following example demonstrates how to collect and display the error information for a query that fails in the compilation phase. In addition, it shows how to output the error details to a file.

SQL

/* The troubleshooting data for query optimizer and code generation issues for a SELECT query is saved using OPTION(save_repro_info=1) */
  
CREATE REFERENCE TABLE customers(id int, orders int, primary key(id));
CREATE TABLE partners(id int, orders int, shard key(id));
EXPLAIN SELECT * FROM customers SEMI_JOIN partners on customers.orders=1 OPTION(save_repro_info=1);

ERROR 1749 (HY000): Feature 'complex join where left side is not a sharded table' is not supported by SingleStore Distributed.

Note that the following output has been tab spaced for better readability and may differ from the actual display.

SQL

/* The SHOW PRODUCTION command collects and displays the error information saved for a query using OPTION(save_repro_info=1) */


SHOW REPRODUCTION;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {
  "context_database": "x_db", 
  "ddl": [
    "CREATE DATABASE `x_db` PARTITIONS 3", 
    "USING `x_db` CREATE REFERENCE TABLE `customers` (\n  `id` int(11) NOT NULL DEFAULT '0',\n  `orders` int(11) DEFAULT NULL,\n  PRIMARY KEY (`id`)\n) AUTOSTATS_CARDINALITY_MODE=OFF AUTOSTATS_HISTOGRAM_MODE=OFF SQL_MODE='STRICT_ALL_TABLES'", 
    "USING `x_db` CREATE TABLE `partners` (\n  `id` int(11) DEFAULT NULL,\n  `orders` int(11) DEFAULT NULL,\n  SHARD KEY `id` (`id`)\n) AUTOSTATS_CARDINALITY_MODE=OFF AUTOSTATS_HISTOGRAM_MODE=OFF SQL_MODE='STRICT_ALL_TABLES'"
  ], 
  "error_msg": "Feature 'complex join where left side is not a sharded table' is not supported by SingleStore Distributed.", 
  "error_no": "1749", 
  "optimizer_caches": {
    "sampling_rowcount_cache": [
      {
        "db": "x_db", 
        "rowcount": "0", 
        "table": "customers"
      }, 
      {
        "db": "x_db", 
        "rowcount": "0", 
        "table": "partners"
      }
    ]
  }, 
  "query": "explain select * from customers semi_join partners on customers.b=1 option(save_repro_info=1)", 
  "singlestore_version": "normalized", 
  "singlestore_version_hash": "normalized", 
  "variables": {
    "as_aggregator": 1, 
    "as_leaf": 0, 
    "binary_serialization": 1, 
    "cardinality_estimation_level": 3, 
    "client_found_rows": 0, 
    "collation_server": 2, 
    "data_conversion_compatibility_level": 0, 
    "datetime_precision_mode": 0, 
    "debug_mode": "0", 
    "default_columnstore_table_lock_threshold": 0, 
    "default_partitions_per_leaf": 3, 
    "disable_histogram_estimation": 0, 
    "disable_sampling_estimation": 0, 
    "disable_sampling_estimation_with_histograms": 2, 
    "disable_subquery_merge_with_straight_joins": 2, 
    "disable_update_delete_distributed_transactions": 0, 
    "display_full_estimation_stats": 0, 
    "distributed_optimizer_broadcast_mult": 0, 
    "distributed_optimizer_estimated_restricted_search_cost_bound": 125, 
    "distributed_optimizer_max_join_size": 22, 
    "distributed_optimizer_min_join_size_run_initial_heuristics": 18, 
    "distributed_optimizer_nodes": 0, 
    "distributed_optimizer_old_selectivity_table_threshold": 22, 
    "distributed_optimizer_run_legacy_heuristic": 0, 
    "distributed_optimizer_selectivity_fallback_threshold": 50000000, 
    "distributed_optimizer_unrestricted_search_threshold": 22, 
    "enable_alias_space_trim": 0, 
    "enable_broadcast_left_join": 1, 
    "enable_local_shuffle_group_by": 1, 
    "enable_skiplist_sampling_for_selectivity": 1, 
    "enable_spilling": 0, 
    "explicit_defaults_for_timestamp": 1, 
    "force_bloom_filters": 0, 
    "force_bushy_join_table_limit": 18, 
    "force_bushy_joins": 0, 
    "force_heuristic_rewrites": 0, 
    "force_table_pushdown": 0, 
    "hash_groupby_segment_distinct_values_threshold": 10000, 
    "ignore_insert_into_computed_column": 0, 
    "inlist_precision_limit": 10000, 
    "interpreter_mode": 4, 
    "json_extract_string_collation": 3, 
    "leaf_pushdown_default": 0, 
    "leaf_pushdown_enable_rowcount": 120000, 
    "materialize_ctes": 0, 
    "max_broadcast_tree_rowcount": 120000, 
    "max_subselect_aggregator_rowcount": 0, 
    "node_degree_of_parallelism": 0, 
    "old_local_join_optimizer": 0, 
    "optimize_constants": 1, 
    "optimize_stmt_threshold": 50, 
    "optimizer_beam_width": 10, 
    "optimizer_cross_join_cost": 1.0, 
    "optimizer_disable_right_join": 0, 
    "optimizer_disable_subselect_to_join": 0, 
    "optimizer_empty_tables_limit": 0, 
    "optimizer_hash_join_cost": 1.0, 
    "optimizer_merge_join_cost": 1.0, 
    "optimizer_nested_join_cost": 1.0, 
    "optimizer_num_partitions": 0, 
    "quadratic_rewrite_size_limit": 200, 
    "query_rewrite_loop_iterations": 1, 
    "reshuffle_group_by_base_cost": 0, 
    "resource_pool_is_auto": 0, 
    "resource_pool_statement_selector_function": 4294967300, 
    "sampling_estimates_for_complex_filters": 1, 
    "singlebox_optimizer_cost_based_threshold": 18, 
    "sql_mode": 4194304, 
    "sql_select_limit": 1, 
    "use_joincolumnstore": 1
  }
}
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-

Run the following command to write the output to the error-reproduction.json file in the tmp directory.

SQL

SHOW REPRODUCTION INTO OUTPUT '/tmp/error-reproduction.json';

Run the following command to write the output to the error-reproduction.txt file in the tmp directory.

SQL

SHOW REPRODUCTION INTO OUTPUT '/tmp/error-reproduction.txt';

Collect troubleshooting information including optimizer statistics for a SELECT query

The following example demonstrates how to collect and display the error reproduction information including optimizer statistics for a query that fails in the compilation phase. In addition, it shows how to output the error details to a file.

SQL

/* The error details for query optimizer and code generation issues for a SELECT query are saved using OPTION(save_repro_info=1). Additionally, ANALYZE is run to enable SHOW REPRODUCTION to collect optimizer statistics in its output. */

CREATE TABLE orders(id int primary key);
CREATE TABLE customers(id varchar(100) primary key);
INSERT INTO orders VALUES (1),(5),(20),(100),(121);
ANALYZE TABLE orders;
ANALYZE TABLE orders COLUMNS ALL ENABLE;
SELECT * FROM orders WHERE id = (SELECT * FROM orders UNION ALL SELECT * FROM customers) OPTION(save_repro_info=1);

ERROR 1749 (HY000): Feature 'Scalar subselect with Unions' is not supported by SingleStore Distributed.

The following output additionally contains the optimizer statistics that were collected on the orders table by using the ANALYZE command. Note that the following output has been tab spaced for better readability and may differ from the actual display.

SQL

/* The SHOW PRODUCTION command collects and displays the error information saved for a query using OPTION(save_repro_info=1) */

SHOW REPRODUCTION;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {
  "context_database": "x_db", 
  "ddl": [
    "CREATE DATABASE `x_db` PARTITIONS 3", 
    "USING `x_db` CREATE TABLE `orders` (\n  `id` int(11) NOT NULL,\n  PRIMARY KEY (`id`)\n) AUTOSTATS_CARDINALITY_MODE=OFF AUTOSTATS_HISTOGRAM_MODE=OFF SQL_MODE='STRICT_ALL_TABLES'", 
    "USING `x_db` CREATE TABLE `customers` (\n  `id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n  PRIMARY KEY (`id`)\n) AUTOSTATS_CARDINALITY_MODE=OFF AUTOSTATS_HISTOGRAM_MODE=OFF SQL_MODE='STRICT_ALL_TABLES'"
  ], 
  "error_msg": "Feature 'Scalar subselect with Unions' is not supported by SingleStore Distributed.", 
  "error_no": "1749", 
  "optimizer_caches": {}, 
  "optimizer_stats": [
    {
      "databaseName": "x_db", 
      "stats": [
        {
          "columns": [
            {
              "columnName": "id", 
              "histogram2": {
                "crc": "1758217204", 
                "serialized": "ZAAAAAkAAAAAAAAAAADwPwAAAAAAAPA/AAAAAAAAAAAAAAAAAAAAAAAAAAAAAPA/AAAAAAAA8D8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA8D8AAAAAAADwPwAAAAAAAAAAAAAAAAAAAAAAAAAAAADwPwAAAAAAAPA/AAAAAAAAAAAAAAAAAAAAAAAAAAAAAPA/AAAAAAAA8D8AAAAAAAAAAAEAAAAAAAAAAgAAAAAAAAAFAAAAAAAAAAYAAAAAAAAAFAAAAAAAAAAVAAAAAAAAAGQAAAAAAAAAZQAAAAAAAAB5AAAAAAAAAHoAAAAAAAAA", 
                "type": "INT64", 
                "version": "100"
              }
            }
          ], 
          "tableName": "orders"
        }
      ], 
      "tables": [
        {
          "columns": [
            {
              "cardinality": "5", 
              "columnName": "id", 
              "density": "0x0p+0", 
              "lastUpdated": "0", 
              "maxValue": "", 
              "minValue": "", 
              "nullCount": "0", 
              "sampleSize": "5"
            }
          ], 
          "rowCount": "5", 
          "tableName": "orders"
        }
      ], 
      "version": 2
    }
  ], 
  "query": "select * from orders where id = (select * from orders union all select * from customers) option(save_repro_info=1)", 
  "singlestore_version": "normalized", 
  "singlestore_version_hash": "normalized", 
  "variables": {
    "as_aggregator": 1, 
    "as_leaf": 0, 
    "binary_serialization": 1, 
    "cardinality_estimation_level": 3, 
    "client_found_rows": 0, 
    "collation_server": 2, 
    "data_conversion_compatibility_level": 0, 
    "datetime_precision_mode": 0, 
    "debug_mode": "0", 
    "default_columnstore_table_lock_threshold": 0, 
    "default_partitions_per_leaf": 3, 
    "disable_histogram_estimation": 0, 
    "disable_sampling_estimation": 0, 
    "disable_sampling_estimation_with_histograms": 2, 
    "disable_subquery_merge_with_straight_joins": 2, 
    "disable_update_delete_distributed_transactions": 0, 
    "display_full_estimation_stats": 0, 
    "distributed_optimizer_broadcast_mult": 0, 
    "distributed_optimizer_estimated_restricted_search_cost_bound": 125, 
    "distributed_optimizer_max_join_size": 22, 
    "distributed_optimizer_min_join_size_run_initial_heuristics": 18, 
    "distributed_optimizer_nodes": 0, 
    "distributed_optimizer_old_selectivity_table_threshold": 22, 
    "distributed_optimizer_run_legacy_heuristic": 0, 
    "distributed_optimizer_selectivity_fallback_threshold": 50000000, 
    "distributed_optimizer_unrestricted_search_threshold": 22, 
    "enable_alias_space_trim": 0, 
    "enable_broadcast_left_join": 1, 
    "enable_local_shuffle_group_by": 1, 
    "enable_skiplist_sampling_for_selectivity": 1, 
    "enable_spilling": 0, 
    "explicit_defaults_for_timestamp": 1, 
    "force_bloom_filters": 0, 
    "force_bushy_join_table_limit": 18, 
    "force_bushy_joins": 0, 
    "force_heuristic_rewrites": 0, 
    "force_table_pushdown": 0, 
    "hash_groupby_segment_distinct_values_threshold": 10000, 
    "ignore_insert_into_computed_column": 0, 
    "inlist_precision_limit": 10000, 
    "interpreter_mode": 4, 
    "json_extract_string_collation": 3, 
    "leaf_pushdown_default": 0, 
    "leaf_pushdown_enable_rowcount": 120000, 
    "materialize_ctes": 0, 
    "max_broadcast_tree_rowcount": 120000, 
    "max_subselect_aggregator_rowcount": 0, 
    "node_degree_of_parallelism": 0, 
    "old_local_join_optimizer": 0, 
    "optimize_constants": 1, 
    "optimize_stmt_threshold": 50, 
    "optimizer_beam_width": 10, 
    "optimizer_cross_join_cost": 1.0, 
    "optimizer_disable_right_join": 0, 
    "optimizer_disable_subselect_to_join": 0, 
    "optimizer_empty_tables_limit": 0, 
    "optimizer_hash_join_cost": 1.0, 
    "optimizer_merge_join_cost": 1.0, 
    "optimizer_nested_join_cost": 1.0, 
    "optimizer_num_partitions": 0, 
    "quadratic_rewrite_size_limit": 200, 
    "query_rewrite_loop_iterations": 1, 
    "reshuffle_group_by_base_cost": 0, 
    "resource_pool_is_auto": 0, 
    "resource_pool_statement_selector_function": 4294967300, 
    "sampling_estimates_for_complex_filters": 1, 
    "singlebox_optimizer_cost_based_threshold": 18, 
    "sql_mode": 4194304, 
    "sql_select_limit": 1, 
    "use_joincolumnstore": 1
  }
}
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-