MySQL Bugs: #97172: Unable to access any table with unsupported histogram type.

Bug #97172	Unable to access any table with unsupported histogram type.
Submitted:	10 Oct 2019 6:51	Modified:	12 Nov 2019 13:54
Reporter:	Kaiwang CHen (OCA)	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	8.0.16, 8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
MySQL 8.0 currently has singleton and equi-height histogram types. New histograms types might be introduced, for example Oracle top frequency and hybrid histograms were added in later versions.

If any new type was persisted, current executable will not be able to access the table. Debug version will crashes with assertion failure, and release version silently skip any operation on the table.

Such forward compatibility issue could be considered to support replacement of binary executables between different versions.

How to repeat:
To simulate the problem in the debug version,

mysql [localhost] {msandbox} (test) > SET debug='+d,skip_dd_table_access_check';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > CREATE TABLE tbl_int (col1 int);
Query OK, 0 rows affected (0.04 sec)

mysql [localhost] {msandbox} (test) > INSERT mysql.column_statistics
    -> VALUES (1, 1, unhex("746573741F74626C5F696E741F636F6C31"),
    ->         "test", "tbl_int", "col1",
    ->         "{\"histogram-type\":\"dummy\",\"data-type\":\"int\"}",
    ->         NULL);
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT tbl_int VALUES (1);
ERROR 2013 (HY000): Lost connection to MySQL server during query

Then in the error log, we can find the following assertion failure:

Assertion failed: (thd->is_system_thread() || thd->killed || thd->is_error()), function get, file /Users/kaiwang.ckw/Projects/mysql-server/sql/dd/impl/cache/storage_adapter.cc, line 188.
06:18:45 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7fba53912400
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7000014e4df8 thread_stack 0x46000
0   mysqld                              0x00000001058fe9d8 my_print_stacktrace(unsigned char*, unsigned long) + 88
1   mysqld                              0x0000000104300002 handle_fatal_signal + 562
2   libsystem_platform.dylib            0x00007fff771d0b5d _sigtramp + 29
3   ???                                 0x0000000000000000 0x0 + 0
4   libsystem_c.dylib                   0x00007fff7708a6a6 abort + 127
5   libsystem_c.dylib                   0x00007fff7705320d basename_r + 0
6   mysqld                              0x0000000105562dc8 bool dd::cache::Storage_adapter::get<dd::Item_name_key, dd::Column_statistics>(THD*, dd::Item_name_key const&, enum_tx_isolation, bool, dd::
Column_statistics const**) + 1272
7   mysqld                              0x000000010553a0cc bool dd::cache::Shared_dictionary_cache::get_uncached<dd::Item_name_key, dd::Column_statistics>(THD*, dd::Item_name_key const&, enum_tx_isolation, dd::Column_statistics const**) const + 108
8   mysqld                              0x000000010553a027 bool dd::cache::Shared_dictionary_cache::get<dd::Item_name_key, dd::Column_statistics>(THD*, dd::Item_name_key const&, dd::cache::Cache_element<dd::Column_statistics>**) + 167
9   mysqld                              0x00000001053b4561 bool dd::cache::Dictionary_client::acquire<dd::Item_name_key, dd::Column_statistics>(dd::Item_name_key const&, dd::Column_statistics const**, bool*, bool*) + 993
10  mysqld                              0x00000001053b3f55 bool dd::cache::Dictionary_client::acquire<dd::Column_statistics>(std::__1::basic_string<char, std::__1::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const&, dd::Column_statistics const**) + 293
11  mysqld                              0x0000000103ac8c07 histograms::find_histogram(THD*, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, histograms::Histogram const**) + 359
12  mysqld                              0x0000000103f1ec91 read_histograms(THD*, TABLE_SHARE*, dd::Schema const*, dd::Abstract_table const*) + 1505
13  mysqld                              0x0000000103f1d988 get_table_share(THD*, char const*, char const*, char const*, unsigned long, bool, bool) + 2728
14  mysqld                              0x0000000103f28307 get_table_share_with_discover(THD*, TABLE_LIST*, char const*, unsigned long, bool, int*) + 119
15  mysqld                              0x0000000103f2594c open_table(THD*, TABLE_LIST*, Open_table_context*) + 5532
16  mysqld                              0x0000000103f2cc00 open_and_process_table(THD*, LEX*, TABLE_LIST*, unsigned int*, Prelocking_strategy*, bool, Open_table_context*) + 1440
17  mysqld                              0x0000000103f2ba0f open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) + 943
18  mysqld                              0x0000000103f3035a open_tables_for_query(THD*, TABLE_LIST*, unsigned int) + 266
19  mysqld                              0x00000001040e7210 Sql_cmd_dml::prepare(THD*) + 880
20  mysqld                              0x00000001040e8112 Sql_cmd_dml::execute(THD*) + 322
21  mysqld                              0x000000010405b4f8 mysql_execute_command(THD*, bool) + 13112
22  mysqld                              0x000000010405632f mysql_parse(THD*, Parser_state*) + 1567
23  mysqld                              0x0000000104052e4e dispatch_command(THD*, COM_DATA const*, enum_server_command) + 7214
24  mysqld                              0x0000000104055355 do_command(THD*) + 1013
25  mysqld                              0x00000001042cf398 handle_connection(void*) + 488
26  mysqld                              0x000000010635df28 pfs_spawn_thread(void*) + 408
27  libsystem_pthread.dylib             0x00007fff771d92eb _pthread_body + 126
28  libsystem_pthread.dylib             0x00007fff771dc249 _pthread_start + 66
29  libsystem_pthread.dylib             0x00007fff771d840d thread_start + 13

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fba53105a28): INSERT tbl_int VALUES (1)
Connection ID (thread ID): 9
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2019-10-10T06:18:46.6NZ mysqld_safe mysqld restarted

With that dummy histogram persisted, the release version skips any operation on the table:

mysql [localhost] {msandbox} (test) > select * from information_schema.column_statistics;
+-------------+------------+-------------+-------------------------------------------------+
| SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | HISTOGRAM                                       |
+-------------+------------+-------------+-------------------------------------------------+
| test        | tbl_int    | col1        | {"data-type": "int", "histogram-type": "dummy"} |
+-------------+------------+-------------+-------------------------------------------------+
1 row in set (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT tbl_int VALUES (1);
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > select * from tbl_int;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > drop table tbl_int;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
| tbl_int        |
+----------------+
3 rows in set (0.02 sec)

Suggested fix:
The column statistics dictionary code could be changed to validate new types as OK, and ignore them instead of crash.

The histogram-type field needs special treatment in that it is designed to be a switch field to load different kinds of histograms. Other histogram fields should be handled in specific histogram classes.

See enclosed for a fix. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: histogram_type_compat.patch (application/octet-stream, text), 13.43 KiB.

Hello Kaiwang,

Thank you for the reasonable feature request and contribution!

regards,
Umesh

Test results - 8.0.17

Attachment: 97172.results.txt (text/plain), 9.21 KiB.

Posted by developer:
 
Hi Kaiwang,

Thanks for the request and for the patch!

MySQL Server does not support in-place downgrade. You are correct in stating that the current server would not understand the data dictionary of a future version if a new histogram type were added. And that is by design. The data dictionary has a version number that is checked when the server starts to avoid exactly this situation, and as you mention, there is also an assertion in the histogram code to guard against unknown histogram types.

If we were to support downgrades, there would be many more dictionary problems than this that would need fixing, so I'm afraid this patch wouldn't be sufficient. Also, ignoring dictionary entries that are not understood is a dangerous path and may lead to further problems down the road when decisions and new dictionary entries are made on incomplete understanding of existing dictionary entries. This complexity is one of the reasons MySQL doesn't support in-place downgrades.

Because of the above, I'm sorry to say that we've decided not to take the patch. But still, thank you very much for raising your concerns. It got us to re-evaluate the code and think through the process once more.

Best regards,

Norvald