MySQL Bugs: #99234: Crash on a specific query after upgrading to 8.0.19

Bug #99234	Crash on a specific query after upgrading to 8.0.19
Submitted:	12 Apr 2020 2:57	Modified:	14 Apr 2020 11:56
Reporter:	Baptiste Massemin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	8.0.19	OS:	Ubuntu (18.04)
Assigned to:		CPU Architecture:	Any (amd64)

Description:
We upgraded our MySQL Server from 8.0.1? (not sure if it was 17 or 18) to the 8.0.19, using apt upgrade.

When the mysql server restarted, everything went well, until we had a crash.
This is the message we got in the errors.log:

2020-04-11T23:11:56.970273Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
23:12:14 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7f76181d67a0
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 = 7f7dfe006d50 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x562e62ba278d]
/usr/sbin/mysqld(handle_fatal_signal+0x303) [0x562e61c3c553]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12890) [0x7f838a2f4890]
/usr/sbin/mysqld(actual_key_parts(KEY const*)+0x13) [0x562e61b5f533]
/usr/sbin/mysqld(calculate_key_len(TABLE*, unsigned int, unsigned long)+0x28) [0x562e61d56ed8]
/usr/sbin/mysqld(handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function)+0x295) [0x562e61d57765]
/usr/sbin/mysqld(check_unique_constraint(TABLE*)+0xb0) [0x562e61ac4890]
/usr/sbin/mysqld(do_sj_dups_weedout(THD*, SJ_TMP_TABLE*)+0x129) [0x562e61ac4b19]
/usr/sbin/mysqld(WeedoutIterator::Read()+0xa9) [0x562e61cfdc79]
/usr/sbin/mysqld(NestedLoopIterator::Read()+0xee) [0x562e61cfe28e]
/usr/sbin/mysqld(MaterializeIterator::MaterializeQueryBlock(MaterializeIterator::QueryBlock const&, unsigned long long*)+0x116) [0x562e61d03336]
/usr/sbin/mysqld(MaterializeIterator::Init()+0x287) [0x562e61d03c27]
/usr/sbin/mysqld(MaterializeIterator::MaterializeQueryBlock(MaterializeIterator::QueryBlock const&, unsigned long long*)+0x80) [0x562e61d032a0]
/usr/sbin/mysqld(MaterializeIterator::Init()+0x287) [0x562e61d03c27]
/usr/sbin/mysqld(AggregateIterator::Init()+0x16) [0x562e61cfdf36]
/usr/sbin/mysqld(SELECT_LEX_UNIT::ExecuteIteratorQuery(THD*)+0x230) [0x562e61bc47b0]
/usr/sbin/mysqld(SELECT_LEX_UNIT::execute(THD*)+0x139) [0x562e61bc6b49]
/usr/sbin/mysqld(Sql_cmd_dml::execute_inner(THD*)+0x21b) [0x562e61b57d1b]
/usr/sbin/mysqld(Sql_cmd_dml::execute(THD*)+0x458) [0x562e61b61438]
/usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0x2811) [0x562e61b11741]
/usr/sbin/mysqld(mysql_parse(THD*, Parser_state*)+0x361) [0x562e61b138f1]
/usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x127f) [0x562e61b150af]
/usr/sbin/mysqld(do_command(THD*)+0x1c4) [0x562e61b16964]
/usr/sbin/mysqld(+0xfbd800) [0x562e61c2d800]
/usr/sbin/mysqld(+0x244b909) [0x562e630bb909]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db) [0x7f838a2e96db]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7f838851088f]

Query (7f76181e36e8): [REDACTED]
Connection ID (thread ID): 5233
Status: NOT_KILLED

This crash happens always on the same query, we managed to isolated it (the query parameters are differents).
If we temporarly isolate the server to test the problematic query, it's fine, there's no crash. So it seems the issue is happening under heavy load only.

Note we ran innochecksum and mysqlcheck too, and the tables are not corrupted.

How to repeat:
There's only one way to repeat the issue, it's under heavy load using our website.

Thank you for the bug report. To verify this bug report it's necessary you provide a repeatable test case.

Hi,

Yes, we do need a fully repeatable test case in ordre to verify this bug.

Just for your information, it seems that you have either used UNION or some stored routine. Hope that this might help you.

We tried to reproduce the issue without any success, except when the query is running in production.

We decided to change the query (the query was autogenerated by Drupal), and there's no crash anymore, even under heavy load.

I don't know if you want to close the bug or not, I will not be able to reproduce it anyway.

Thanks.

FYI there's no UNION or stored procedure used in the query.

Hi Mr. Massemin,

Thank you for your feedback.

We would still like to see what kind of query is that. We would also like to know the structures of the tables involved and how big are they.

Thanks in advance.

Couple of things in 8.0.20 release notes seem to touch functions visible in this crash trace, like SELECT_LEX_UNIT, weedout optimization, etc.
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html

Unfortunately, the mentioned bug numbers there, refer to hidden reports. 
So I wonder if it can be officially confirmed, if this kind of crash is fixed as of 8.0.20?

We can not answer that question without a fully repeatable test case.

Anyway, on dev.mysql.com, all Release Notes are published, so you can check them yourself.

Dear MySQL Verification Team,

Are you referring to the Release Notes I mentioned the link for, or some other ones? Because I cannot check much there, as the referred bug ids are not public and their descriptions are often avaricious and vague.

So, for example, I would love to see more details about this one:

"The internal array of materialized query blocks SELECT_LEX_UNIT::m_query_blocks_to_materialize was not reset between executions, which meant that it pointed to objects which were no longer valid when a prepared statement was executed a second time, causing the second execution to fail. (Bug #30438038)"

Unfortunately, we cannot read more details about Bug #30438038 and whether "execution to fail" means a server crash maybe and if so, whether the crash trace happens to be similar to the one mentioned in this particular bug report.

Hi,

There are bugs that are internal only and those can not be made available for public inspection. For many reasons ....

However, description itself is sufficient.

Hi Mr. Massemin,

It turns out that your bug is a duplicate of our internal bug, that is fixed in 8.0.20. Hence, all you need to do is to upgrade.