[MDEV-27753] Incorrect ENGINE type of table after crash for CONNECT table Created: 2022-02-05  Updated: 2022-03-14  Resolved: 2022-03-14

Status: Closed
Project: MariaDB Server
Component/s: Partitioning
Affects Version/s: 10.6.4
Fix Version/s: 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4, 10.8.3

Type: Bug Priority: Major
Reporter: Mathew Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

Debian


 Description   

We had a mariadb crash today due to out of disk.

After restoring the server all CONNECT tables had become ENGINE=SEQUENCE in their definition.

Example schema:

CREATE TABLE `history` (
 
  `itemid` bigint(20) unsigned NOT NULL,
 
  `clock` int(11) NOT NULL DEFAULT 0,
 
  `value` double(16,4) NOT NULL DEFAULT 0,
 
  `ns` int(11) NOT NULL DEFAULT 0
 
) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin `table_type`=MYSQL `option_list`='connect=mysql://[...]'
 
 PARTITION BY RANGE (`clock`)
 
(PARTITION `p202202042100` VALUES LESS THAN (1644012000) ENGINE = CONNECT,
 
[...])

After crash:

CREATE TABLE `history` (
 
  `itemid` bigint(20) unsigned NOT NULL,
 
  `clock` int(11) NOT NULL DEFAULT 0,
 
  `value` double(16,4) NOT NULL DEFAULT 0,
 
  `ns` int(11) NOT NULL DEFAULT 0
 
) ENGINE=SEQUENCE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin `table_type`=MYSQL `option_list`='connect=mysql://[...]'
 
 PARTITION BY RANGE (`clock`)
 
(PARTITION `p202202042100` VALUES LESS THAN (1644012000) ENGINE = CONNECT,
 
[...])

I've checked a few things, the frm file has a refernce to CONNECT and the header doesnt indicate a Sequence either.

00000020: 0005 0000 0000 2e00 0000 0000 0000 0010

My guess is that it's the table repair process?

 Comments   
Comment by Sergei Golubchik [ 2022-02-07 ]

was mariadb that crashed and mariadb that you started after the crash exactly the same executable? Or have you upgraded mariadb meanwhile and the first and second outputs were created by different mariadb versions?

Comment by Mathew [ 2022-02-07 ]

Same version, same configuration. Crash was caused by disk being full not
any upgrade or change.

On Tue, 8 Feb 2022, 5:23 am Sergei Golubchik (Jira), <jira@mariadb.org>

Comment by Mathew [ 2022-02-08 ]

On further testing I can confirm that any crash triggers the bug, not just out of disk.

This was tested for by purposefully causing an OOM in a testing environment.

After automatic restart (by systemd) CONNECT table types had changed to SEQUENCE.

Comment by Sergei Golubchik [ 2022-02-09 ]

can you attach one of these frm's ?

Comment by Mathew [ 2022-02-09 ]

Sergei,

CONNECT frm's include mysql passwords.

Please create your own frm's using the provided schema.

Please also note I long since fixed the crash (ALTER TABLE)

Comment by Sergei Golubchik [ 2022-02-15 ]

Unfortunately, I failed to repeat this using the provided schema. Here's my test case, for the reference

source include/have_partition.inc;
 
 
 
install soname 'ha_connect';
 
 
 
CREATE TABLE t1 (
 
  `itemid` bigint(20) unsigned NOT NULL,
 
  `clock` int(11) NOT NULL DEFAULT 0,
 
  `value` double(16,4) NOT NULL DEFAULT 0,
 
  `ns` int(11) NOT NULL DEFAULT 0
 
);
 
 
 
CREATE TABLE `history` (
 
  `itemid` bigint(20) unsigned NOT NULL,
 
  `clock` int(11) NOT NULL DEFAULT 0,
 
  `value` double(16,4) NOT NULL DEFAULT 0,
 
  `ns` int(11) NOT NULL DEFAULT 0
 
) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin `table_type`=MYSQL 
`option_list`='connect=mysql://root@localhost/test/t1'
 
 PARTITION BY RANGE (`clock`)
 
(PARTITION `p202202042100` VALUES LESS THAN (1644012000) ENGINE = CONNECT);
 
 
 
source include/crash_mysqld.inc;
 
 
 
show create table history;

Comment by Mathew [ 2022-02-16 ]

Thats one crazy transition list. Not sure how you expect to get feedback from a reporter in a few in a few miutes.

Anyway I repeated a test on a install and import of schema on a Cloud VM and replicated issue after a dd to fill the disk.

OS Debian bullseye (latest stable), not a fresh VM however. That makes three seperate systems I've replicated this on now. I'm going to do one final test on a fresh VM when I have a free moment.

however at this point I'm less than impressed. How many defects that get reported are arbitarily closed in this bug tracker I wonder?

Comment by Mathew [ 2022-02-16 ]

Fresh Debian Buster (Linode).

1. Install Repostories
2. Install mariadb-plugin-connect (and server)
3. "SET PASSWORD FOR 'root'@'localhost' = '';"
4. Create database test
5. Import your schema, and create a test table
6. Perform test INSERT "INSERT INTO history VALUES (1,1,1,1);"
7. Fill disk: "dd if=/dev/zero of=testfile2 bs=1000000 count=100000000"
8. Perform page fill INSERT. I used a script for this

# cat crash.sh
 
while [[ 1 ]]; do
 
echo 'INSERT INTO history VALUES (RAND()*1000000,RAND()*1000000,RAND()*100000,1)' | mysql test
 
done

9. This started to return table is full errors, rather than crashing (sometimes it crashes, sometimes it errors - either way). As this time it didnt crash on insert to full disk (usually it does, probably to do with binary log) I did a DDL operation (which usually crashes if the disk is full). "DROP TABLE test" this crashed mariadb successfully.

10. Delete disk occupying file

11. Restart mariadb (it has crashed). systemctl restart mariadb

12. Check table

> SHOW CREATE TABLE history;
 
No connection. Trying to reconnect...
 
Connection id:    31
 
Current database: test
 
 
 
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                               |
 
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| history | CREATE TABLE `history` (
 
  `itemid` bigint(20) unsigned NOT NULL,
 
  `clock` int(11) NOT NULL DEFAULT 0,
 
  `value` double(16,4) NOT NULL DEFAULT 0.0000,
 
  `ns` int(11) NOT NULL DEFAULT 0
 
) ENGINE=SEQUENCE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin `table_type`=MYSQL `option_list`='connect=mysql://root@localhost/test/t1'
 
 PARTITION BY RANGE (`clock`)
 
(PARTITION `p202202042100` VALUES LESS THAN (1644012000) ENGINE = CONNECT) |
 
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Comment by Sergei Golubchik [ 2022-02-16 ]

Oh, the status isn't cut in the stone. You provided more feedback — I'll reopen the bug report, no problem.

Thanks will try again with the new info.

Comment by Mathew [ 2022-02-16 ]

It does need to be a proper crash of mysql

kill -9 / kill -11 does NOT cause the issue. So I'm not sure I can do for example a shell script to replicate. I tested killall -11 10 times in a loop with no luck.

Disk full crashes have the highest rate of replicating this issue (not 100%). Perhaps the table has to be in the table cache or pending some frm write? I'm not certain what I should be trying here (this isnt an area of the mariadb engine that is familar to me).

The hardest part of replicating this is making mariadb crash on demand.

I have however managed to corrupt the database once while creating out of space crashes

Not sure if related so I'll include it here.

# journalctl -xn200 -u mariadb
 
-- Journal begins at Wed 2022-02-16 11:45:41 UTC, ends at Wed 2022-02-16 13:05:17 UTC. --
 
Feb 16 13:03:10 localhost mariadbd[155397]: 2022-02-16 13:03:10 0 [ERROR] InnoDB: Unable to create the shared innodb_temporary
 
Feb 16 13:03:10 localhost mariadbd[155397]: 2022-02-16 13:03:10 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
 
Feb 16 13:03:10 localhost mariadbd[155397]: 2022-02-16 13:03:10 0 [Note] InnoDB: FTS optimize thread exiting.
 
Feb 16 13:03:10 localhost mariadbd[155397]: 220216 13:03:10 [ERROR] mysqld got signal 11 ;
 
Feb 16 13:03:10 localhost mariadbd[155397]: This could be because you hit a bug. It is also possible that this binary
 
Feb 16 13:03:10 localhost mariadbd[155397]: or one of the libraries it was linked against is corrupt, improperly built,
 
Feb 16 13:03:10 localhost mariadbd[155397]: or misconfigured. This error can also be caused by malfunctioning hardware.
 
Feb 16 13:03:10 localhost mariadbd[155397]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
Feb 16 13:03:10 localhost mariadbd[155397]: We will try our best to scrape up some info that will hopefully help
 
Feb 16 13:03:10 localhost mariadbd[155397]: diagnose the problem, but since we have already crashed,
 
Feb 16 13:03:10 localhost mariadbd[155397]: something is definitely wrong and this may fail.
 
Feb 16 13:03:10 localhost mariadbd[155397]: Server version: 10.6.7-MariaDB-1:10.6.7+maria~bullseye
 
Feb 16 13:03:10 localhost mariadbd[155397]: key_buffer_size=134217728
 
Feb 16 13:03:10 localhost mariadbd[155397]: read_buffer_size=131072
 
Feb 16 13:03:10 localhost mariadbd[155397]: max_used_connections=0
 
Feb 16 13:03:10 localhost mariadbd[155397]: max_threads=153
 
Feb 16 13:03:10 localhost mariadbd[155397]: thread_count=0
 
Feb 16 13:03:10 localhost mariadbd[155397]: It is possible that mysqld could use up to
 
Feb 16 13:03:10 localhost mariadbd[155397]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467957 K  bytes of memory
 
Feb 16 13:03:10 localhost mariadbd[155397]: Hope that's ok; if not, decrease some variables in the equation.
 
Feb 16 13:03:10 localhost mariadbd[155397]: Thread pointer: 0x0
 
Feb 16 13:03:10 localhost mariadbd[155397]: Attempting backtrace. You can use the following information to find out
 
Feb 16 13:03:10 localhost mariadbd[155397]: where mysqld died. If you see no messages after this, something went
 
Feb 16 13:03:10 localhost mariadbd[155397]: terribly wrong...
 
Feb 16 13:03:10 localhost mariadbd[155397]: stack_bottom = 0x0 thread_stack 0x49000
 
Feb 16 13:03:10 localhost mariadbd[155397]: /usr/sbin/mariadbd(my_print_stacktrace+0x2e)[0x560628621d1e]
 
Feb 16 13:03:10 localhost mariadbd[155397]: /usr/sbin/mariadbd(handle_fatal_signal+0x485)[0x5606280db6e5]
 
Feb 16 13:03:10 localhost systemd[1]: mariadb.service: Failed with result 'signal'.
 
â–‘â–‘ Subject: Unit failed
 
â–‘â–‘ Defined-By: systemd
 
â–‘â–‘ Support: https://www.debian.org/support
 
â–‘â–‘
 
â–‘â–‘ The unit mariadb.service has entered the 'failed' state with result 'signal'.
 
Feb 16 13:03:10 localhost mariadbd[155397]: /lib/x86_64-linux-gnu/libpthread.so.0(+0x14140)[0x7f2e588ac140]
 
Feb 16 13:03:10 localhost mariadbd[155397]: /usr/sbin/mariadbd(thd_get_ha_data+0x7)[0x560627e55267]
 
Feb 16 13:03:10 localhost mariadbd[155397]: /usr/sbin/mariadbd(+0xca57bd)[0x5606283c07bd]
 
Feb 16 13:03:10 localhost mariadbd[155397]: /usr/sbin/mariadbd(+0xe6f815)[0x56062858a815]
 
Feb 16 13:03:10 localhost mariadbd[155397]: /usr/sbin/mariadbd(+0x65747f)[0x560627d7247f]
 
Feb 16 13:03:10 localhost mariadbd[155397]: /usr/sbin/mariadbd(+0x65a3dc)[0x560627d753dc]
 
Feb 16 13:03:10 localhost mariadbd[155397]: Printing to addr2line failed
 
Feb 16 13:03:10 localhost mariadbd[155397]: /usr/sbin/mariadbd(+0xcb7fc1)[0x5606283d2fc1]
 
Feb 16 13:03:10 localhost mariadbd[155397]: /usr/sbin/mariadbd(_Z24ha_initialize_handlertonP13st_plugin_int+0x7e)[0x5606280de3ce]
 
Feb 16 13:03:10 localhost mariadbd[155397]: /usr/sbin/mariadbd(+0x7a2ae6)[0x560627ebdae6]
 
Feb 16 13:03:10 localhost mariadbd[155397]: /usr/sbin/mariadbd(_Z11plugin_initPiPPci+0x8b5)[0x560627ebec35]
 
Feb 16 13:03:10 localhost mariadbd[155397]: /usr/sbin/mariadbd(+0x6bdd12)[0x560627dd8d12]
 
Feb 16 13:03:10 localhost mariadbd[155397]: /usr/sbin/mariadbd(_Z11mysqld_mainiPPc+0x409)[0x560627dde589]
 
Feb 16 13:03:10 localhost mariadbd[155397]: /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xea)[0x7f2e583e0d0a]
 
Feb 16 13:03:10 localhost mariadbd[155397]: /usr/sbin/mariadbd(_start+0x2a)[0x560627dd37aa]
 
Feb 16 13:03:10 localhost mariadbd[155397]: The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
 
Feb 16 13:03:10 localhost mariadbd[155397]: information that should help you find out what is causing the crash.
 
Feb 16 13:03:10 localhost mariadbd[155397]: Writing a core file...
 
Feb 16 13:03:10 localhost mariadbd[155397]: Working directory at /var/lib/mysql
 
Feb 16 13:03:10 localhost mariadbd[155397]: Resource Limits:
 
Feb 16 13:03:10 localhost mariadbd[155397]: Limit                     Soft Limit           Hard Limit           Units
 
Feb 16 13:03:10 localhost mariadbd[155397]: Max cpu time              unlimited            unlimited            seconds
 
Feb 16 13:03:10 localhost mariadbd[155397]: Max file size             unlimited            unlimited            bytes
 
Feb 16 13:03:10 localhost mariadbd[155397]: Max data size             unlimited            unlimited            bytes
 
Feb 16 13:03:10 localhost mariadbd[155397]: Max stack size            8388608              unlimited            bytes
 
Feb 16 13:03:10 localhost mariadbd[155397]: Max core file size        0                    unlimited            bytes
 
Feb 16 13:03:10 localhost mariadbd[155397]: Max resident set          unlimited            unlimited            bytes
 
Feb 16 13:03:10 localhost mariadbd[155397]: Max processes             3780                 3780                 processes
 
Feb 16 13:03:10 localhost mariadbd[155397]: Max open files            32768                32768                files
 
Feb 16 13:03:10 localhost mariadbd[155397]: Max locked memory         524288               524288               bytes
 
Feb 16 13:03:10 localhost mariadbd[155397]: Max address space         unlimited            unlimited            bytes
 
Feb 16 13:03:10 localhost mariadbd[155397]: Max file locks            unlimited            unlimited            locks
 
Feb 16 13:03:10 localhost systemd[1]: Failed to start MariaDB 10.6.7 database server.

It's beyond me so i'll do a fresh install with a reset the DB for any further testing

Generated at Thu Feb 08 09:55:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.