MySQL Bugs: #87824: Crash when internal_tmp_disk_storage

Bug #87824	Crash when internal_tmp_disk_storage_engine is set to InnoDB
Submitted:	21 Sep 2017 12:29	Modified:	5 Apr 2018 17:30
Reporter:	Travis Hidlay	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.7.19	OS:	Ubuntu (Using latest docker container from DockerHub official MySQL repository)
Assigned to:		CPU Architecture:	Any
Tags:	crash

Description:
2017-09-19 16:49:47 0x7f2084a65700  InnoDB: Assertion failure in thread 139777641174784 in file btr0pcur.cc line 454
InnoDB: Failing assertion: btr_page_get_prev(next_page, mtr) == btr_pcur_get_block(cursor)->page.id.page_no()
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
16:49:47 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68190 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f2054000b10
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 = 7f2084a64e80 thread_stack 0x40000
mysqld(my_print_stacktrace+0x2c)[0xe86e2c]
mysqld(handle_fatal_signal+0x459)[0x7ab4b9]
/lib/x86_64-linux-gnu/libpthread.so.0(+0xf890)[0x7f2099ea2890]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x37)[0x7f20988ab067]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x148)[0x7f20988ac448]
mysqld[0x781bf7]
mysqld[0x10ee1d8]
mysqld(_Z18row_search_no_mvccPh15page_cur_mode_tP14row_prebuilt_tmm+0x3ae)[0x103f84e]
mysqld(_ZN11ha_innobase13general_fetchEPhjj+0x44)[0xf31cf4]
mysqld(_ZN7handler11ha_rnd_nextEPh+0x15c)[0x7f8e5c]
mysqld(_Z13rr_sequentialP11READ_RECORD+0x35)[0xbc50d5]
mysqld(_Z10sub_selectP4JOINP7QEP_TABb+0x147)[0xc2e0d7]
mysqld(_ZN4JOIN4execEv+0x3b8)[0xc26cc8]
mysqld(_ZN18st_select_lex_unit7executeEP3THD+0x1d4)[0xcd93b4]
mysqld(_ZN10TABLE_LIST19materialize_derivedEP3THD+0xa8)[0xc23048]
mysqld(_Z24join_materialize_derivedP7QEP_TAB+0x2f)[0xc2763f]
mysqld(_ZN7QEP_TAB12prepare_scanEv+0x3d)[0xc2710d]
mysqld(_Z10sub_selectP4JOINP7QEP_TABb+0x40)[0xc2dfd0]
mysqld(_ZN4JOIN4execEv+0x3b8)[0xc26cc8]
mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x238)[0xc96f48]
mysqld[0x7741b3]
mysqld(_Z21mysql_execute_commandP3THDb+0x3364)[0xc59384]
mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3bd)[0xc5b4cd]
mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x932)[0xc5be72]
mysqld(_Z10do_commandP3THD+0x18f)[0xc5d68f]
mysqld(handle_connection+0x270)[0xd1af60]
mysqld(pfs_spawn_thread+0x1b4)[0xe9e894]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8064)[0x7f2099e9b064]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f209895e62d]

How to repeat:
In my particular setup, I'm using a Windows 10 machine and a VirtualBox VM.  The VM is generated from the Vagrant "ubuntu/trusty64" box.  Here is the output of "uname -a":

Linux localhost.localdomain 3.13.0-119-generic #166-Ubuntu SMP Wed May 3 12:18:55 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

From inside the VM, I'm running MySQL from a Docker container, using the official "latest" (5.7) docker image here: https://hub.docker.com/_/mysql/
Here is the output from "mysql --version":

mysql  Ver 14.14 Distrib 5.7.19, for Linux (x86_64) using  EditLine wrapper

I can reproduce the issue by executing the same query twice in a row, provided that causes MySQL to use an internal temporary table, and provided that the result of the query is sufficiently large (I think if the query is large enough it causes the internal temp. table to be written to disk).  For example, a query like this runs successfully the first time, then causes the above crash the second time:

select * from (select * from large_table_1 union select * from large_table_2) limit 10

If I change the query to use a "union all" instead of a "union", I don't observe the crash, presumably because a "union all" does not require the use of an internal temporary table.

Another note about my container configuration, is that I'm mounting the MySQL data directory to a shared drive that uses the vboxfs filesystem.  I'm doing this by taking the shared folder that Vagrant sets up (which for Virtual Box uses vboxfs), and mounting it when running the Docker container by using the "-v /vagrant/mysqldatadir:/var/lib/mysql" option.

Suggested fix:
I don't have a suggested fix, but I do have a workaround.  Setting the internal_tmp_disk_storage_engine to MYISAM prevent the crash:

mysql> set global internal_tmp_disk_storage_engine = MYISAM;

After setting that variable, I was able to run my query many times, and I didn't see a crash.  Setting the variable back to InnoDB:

mysql> set global internal_tmp_disk_storage_engine = InnoDB;

After this, I observed the same behavior as before, that is, running my query twice in a row caused the crash.

Please let me know if you need any more information.  I hope I've included enough detail to make this a useful report.

Cheers!

Thank you for the bug report, please provide the create table statement of the tables involved. Thanks in advance.

Here are the table schemas:

CREATE TABLE `patients_query` (
  `id_` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) NOT NULL DEFAULT '0',
  `external_id` varchar(50) NOT NULL DEFAULT '0',
  `master_patient_id` varchar(50) NOT NULL DEFAULT '0',
  `list_type` int(11) NOT NULL DEFAULT '0',
  `list_name` varchar(100) NOT NULL DEFAULT '0',
  `NameFirst` varchar(64) NOT NULL,
  `NameLast` varchar(255) NOT NULL,
  `company_id` int(11) NOT NULL,
  `company_ids` varchar(255) NOT NULL,
  `dob` varchar(50) NOT NULL,
  `AddrStreet` varchar(255) NOT NULL,
  `AddrStreet2` varchar(100) NOT NULL,
  `AddrStreet3` varchar(100) NOT NULL,
  `AddrZip` varchar(15) NOT NULL,
  `AddrCity` varchar(63) NOT NULL,
  `AddrState` varchar(50) NOT NULL,
  `account_number` varchar(255) NOT NULL,
  `admit_service_date` varchar(255) NOT NULL,
  `location_service` varchar(255) NOT NULL,
  `has_patient_list_history` tinyint(4) NOT NULL,
  `is_active` tinyint(4) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `email` varchar(63) NOT NULL,
  `do_not_share_my_data` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_`),
  KEY `list_type` (`list_type`),
  KEY `company_id` (`company_id`),
  KEY `list_type_company_id` (`list_type`,`company_id`),
  KEY `master_patient_id` (`master_patient_id`),
  KEY `id` (`id`),
  KEY `master_patien_listy_type` (`master_patient_id`,`list_type`),
  KEY `NameFirst` (`NameFirst`),
  KEY `NameLast` (`NameLast`),
  KEY `dob` (`dob`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `patients_query_removed` (
  `id_` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) NOT NULL DEFAULT '0',
  `external_id` varchar(50) NOT NULL DEFAULT '0',
  `master_patient_id` varchar(50) NOT NULL DEFAULT '0',
  `list_type` int(11) NOT NULL DEFAULT '0',
  `list_name` varchar(100) NOT NULL DEFAULT '0',
  `NameFirst` varchar(64) NOT NULL,
  `NameLast` varchar(255) NOT NULL,
  `company_id` int(11) NOT NULL,
  `company_ids` varchar(255) NOT NULL,
  `dob` varchar(50) NOT NULL,
  `AddrStreet` varchar(255) NOT NULL,
  `AddrStreet2` varchar(100) NOT NULL,
  `AddrStreet3` varchar(100) NOT NULL,
  `AddrZip` varchar(15) NOT NULL,
  `AddrCity` varchar(63) NOT NULL,
  `AddrState` varchar(50) NOT NULL,
  `account_number` varchar(255) NOT NULL,
  `admit_service_date` varchar(255) NOT NULL,
  `location_service` varchar(255) NOT NULL,
  `has_patient_list_history` tinyint(4) NOT NULL,
  `is_active` tinyint(4) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `email` varchar(63) NOT NULL,
  `do_not_share_my_data` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_`),
  KEY `list_type` (`list_type`),
  KEY `company_id` (`company_id`),
  KEY `list_type_company_id` (`list_type`,`company_id`),
  KEY `master_patient_id` (`master_patient_id`),
  KEY `id` (`id`),
  KEY `master_patien_listy_type` (`master_patient_id`,`list_type`),
  KEY `NameFirst` (`NameFirst`),
  KEY `NameLast` (`NameLast`),
  KEY `dob` (`dob`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

The query I did was:

select * from (select * from patients_query union select * from patients_query_removed) limit 10;

patients_query had about 450k rows, and patients_query_removed had about 5k

Thank you for the requested details.
I tried to reproduce with dummy data with no luck.
Could you please provide config details, and subset of data to reproduce this issue at our end? You may want to mark it as private after posting here.

regards,
Umesh

test results - 5.7.19

Attachment: 87824_5.7.19.results (application/octet-stream, text), 10.79 KiB.

Umesh,

The test file you attached has this query at the bottom:

select * from (select * from patients_query_removed union select * from patients_query_removed) A limit 10;

I don't see you selecting from the "patients_query" table anywhere, only from the "patients_query_removed" table.  Could you try the test again with this query:

select * from (select * from patients_query union select * from patients_query_removed) A limit 10;

Also note that my setup is using a Docker container (the official MySQL 5.7 latest container hosted on Dockerhub), mounted with a vboxfs volume on top of the /var/lib/mysql data directory.  Here is my docker run command:

docker run -v /vagrant/tmp_mysql:/var/lib/mysql -d

Thank you, even tried your query as is with no luck(tried on a relatively small boxes with just 4G/8G/16 and bigger as much 256G RAM)

-- after setting internal_tmp_disk_storage_engine to innodb, with default, and non-default settings

select * from (select * from patients_query union select * from
patients_query_removed) A limit 10;
.
Ran some 15+ times but still no luck.
I'll try this on docker tomorrow but could you please provide MySQL's config file from your environment to see if it helps to reproduce?

Thanks,
Umesh

Quickly attempted using Docker(only diff was that I didn't mount anywhere but let it run on defaults), sorry but still not seeing the issue.
Joining the activity log shortly.

Regards,
Umesh

Docker based test result

Attachment: Docker_87824.results (application/octet-stream, text), 44.24 KiB.

Docker based test result - Big dataset

Attachment: Docker_87824_01.results (application/octet-stream, text), 17.23 KiB.

I'm attaching my configuration variables in a second, taken from a "show variables" query.

It could be that the key in repro'ing this is in mounting a vboxfs volume on top of the /var/lib/mysql directory on the docker container.

Thanks for investigating this Umesh, I really appreciate it!

Configuration variables from "show variables"

Attachment: mysql_config.csv (application/vnd.ms-excel, text), 14.28 KiB.

"It could be that the key in repro'ing this is in mounting a vboxfs
volume on top of the /var/lib/mysql directory on the docker container."

I agree! Actually I know positively that there are no guarantees that things (network, disk functionalities) will work if you install a VM in Virtualbox in a VM in Virtualbox (statement by VB developer in their support Forums). Here you replace the "inner VM" of the described setup with a container. Is this supposed to work. Has anybody ever promised that it will? Or even tested it?

-- Peter
-- not a MySQL/Oracle person

I couldn't repeat with most recent server source. Please try 5.7.21. Thanks.