[MDEV-19075] ERROR 1194 or server crashes after a SELECT with UNION and TRIM on TEXT fields - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3.13, 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.3.17, 10.4.7
Component/s: Data Manipulation - Subquery, Data types, Server, Storage Engine - Aria
Labels:
Environment:
Centos 7.6.1810, in virtual machine either on VMWARE 6 on VirtualBOX 5.1.14

Description

1/ SUMMARY of the problem :
When performing SELECT with UNION on small amount of data, with TRIM() function applied on TEXT fields IN the "select_expr" list part of the SELECT(s), we get MariaDB server crash with "ERROR 1194 (HY000): Table '(temporary)' is marked as crashed and should be repaired"

2/ cnf file /etc/my.cnf.d/server.cnf (note that /etc/my.cnf is empty : contains only include directive)
I changed the default settings as defined below to increase some stack, heap and buffer resources with NO success.

# These groups are read by MariaDB server.

# Use it for options that only the server (but not clients) should see

# See the examples of server my.cnf files in /usr/share/mysql/

# this is read by the standalone daemon and embedded servers

[server]

datadir=/data/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

bind-address=::

log-error=/var/log/mariadb/mariadb.log

max_heap_table_size=256M

tmp_table_size=256M

tmp_memory_table_size=256M

big_tables=ON

thread_stack=512M

aria_pagecache_buffer_size=1024M

#  aria_pagecache_file_hash_size

aria_sort_buffer_size=512M

innodb_temp_data_file_path = ibtmp1:48M:autoextend

# this is only for the mysqld standalone daemon

[mysqld]

# * Galera-related settings

[galera]

# Mandatory settings

#wsrep_on=ON

#wsrep_provider=

#wsrep_cluster_address=

#binlog_format=row

#default_storage_engine=InnoDB

#innodb_autoinc_lock_mode=2

# Allow server to accept connections on all interfaces.

#bind-address=0.0.0.0

# Optional setting

#wsrep_slave_threads=1

#innodb_flush_log_at_trx_commit=0

# this is only for embedded server

[embedded]

# This group is only read by MariaDB servers, not by MySQL.

# If you use the same .cnf file for MySQL and MariaDB,

# you can put MariaDB-only options here

[mariadb]

# This group is only read by MariaDB-10.3 servers.

# If you use the same .cnf file for MariaDB of different versions,

# use this group for options that older servers don't understand

[mariadb-10.3]

3/ TEST DATA SET : CREATE simple test table in test database :

CREATE TABLE test.txtpeople (id INTEGER, name TEXT, firstname TEXT, age INTEGER, revenue INTEGER, stprop TEXT);
Add a few data inside :
INSERT INTO test.txtpeople VALUES (1, 'ABITBOL', 'PAUL', 32, 1000, NULL), (1, 'DUPONT', 'JEAN', 34, 1100, NULL), (1, 'SMITH','BOB', 35, 1200, NULL), (1, 'ABITBOL ', ' PAUL', 36, 1500, NULL);
To mimic one of our true testcase, we deliberately leave the field "stprop" empty, ie at NULL.

4/ TESTS and FAILURES/SUCCESS

TEST 1 : simple select : SUCCESS, ie NO crash
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) ;

TEST 2 : double select with union DISTINCT : FAILURE (crash)
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
union distinct
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) ;
ERROR 1194 (HY000): Table '(temporary)' is marked as crashed and should be repaired

TEST 3 : double select with union ALL : SUCCESS, ie NO crash
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
union ALL
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) ;

TEST 4 : double select with union ALL NESTED in an outer SELECT : FAILURE (crash)
SELECT * FROM (
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
union ALL
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
) T ;
ERROR 1194 (HY000): Table '(temporary)' is marked as crashed and should be repaired

5/ WORKAROUNDS

5.1/ Workaround : NO trim in field_spec but JUST in "group by" expression

*TEST 2B (workaround) : NO trim in field_spec but in "group by" expression : SUCCESS !
*SELECT id, name, age, sum(revenue), firstname, stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
union distinct
SELECT id, name, age, sum(revenue), firstname, stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) ;

*TEST 4B (workaround) : NO trim in field_spec but in "group by" expression : SUCCESS !
*SELECT * FROM (
SELECT id, name, age, sum(revenue), firstname, stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
union ALL
SELECT id, name, age, sum(revenue), firstname, stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
) T ;

5.2/ Workaround : let TRIM everywhere, but replace TEXT WITH VARCHAR(64) :
CREATE TABLE test.vchrpeople (id INTEGER, name VARCHAR(64), firstname VARCHAR(64), age INTEGER, revenue INTEGER, stprop TEXT);
INSERT INTO test.vchrpeople VALUES (1, 'ABITBOL', 'PAUL', 32, 1000, NULL), (1, 'DUPONT', 'JEAN', 34, 1100, NULL), (1, 'SMITH','BOB', 35, 1200, NULL), (1, 'ABITBOL ', ' PAUL', 36, 1500, NULL);

*TEST 2C : SUCCESS ! so there is a specific problem with TEXT type in temporary tables
*SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.vchrpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
union distinct
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.vchrpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) ;

*TEST 4C : SUCCESS ! so there is a specific problem with TEXT type in temporary tables
*SELECT * FROM (
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.vchrpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
union ALL
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.vchrpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
) T ;

*6/ Diagnostic and GUESSES : *
I guess that there is a problem of TEMPORARY tables managed by the ARIA engine, WHEN optimizing/managing execution plan for requests containing TRIM functions in "select_expr" fields operating on TEXT fields. Clearly, the trim function is not working properly on TEXT fields, it is also documented that TEXT fields push the memory/temporary engine to produce some indexes (see differences between TEXT and VARCHAR in the documentation here : https://mariadb.com/kb/en/library/text/ ) differently than with varchar.

NB1: On mysql for windows version 5.6.17-log, everything is OK.
NB2 : with a "CREATE VIEW" based on the same "SELECT", it is the same problem.

Attachments

Issue Links

duplicates

MDEV-17551 Assertion `(&(&share->intern_lock)->m_mutex)->count > 0 && pthread_equal(pthread_self(), (&(&share->intern_lock)->m_mutex)->thread)' failed in _ma_state_info_write or ER_CRASHED_ON_USAGE upon SELECT with UNION

Closed

relates to

MDEV-20818 ER_CRASHED_ON_USAGE or Assertion `length <= column->length' failed in write_block_record on temporary table

Closed

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2019-03-28 23:05

Thanks for the report and great test cases. Reproducible as described.

Below are the same exact queries as described, just put together for MTR or copy-paste into the command-line client.

Test case 1: ERROR 1194
CREATE TABLE test.txtpeople (id INTEGER, name TEXT, firstname TEXT, age INTEGER, revenue INTEGER, stprop TEXT);
INSERT INTO test.txtpeople VALUES (1, 'ABITBOL', 'PAUL', 32, 1000, NULL), (1, 'DUPONT', 'JEAN', 34, 1100, NULL), (1, 'SMITH','BOB', 35, 1200, NULL), (1, 'ABITBOL ', ' PAUL', 36, 1500, NULL);

SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
union distinct
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) ;

# Cleanup
DROP TABLE test.txtpeople;

Test case 1 causes ER_CRASHED_ON_USAGE both on release and debug builds:

10.3 f3ff45f9
mysqltest: At line 4: query 'SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
union distinct
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) ' failed: 1194: Table '(temporary)' is marked as crashed and should be repaired

Test case 2: ERROR 1194 or assertion failure
CREATE TABLE test.txtpeople (id INTEGER, name TEXT, firstname TEXT, age INTEGER, revenue INTEGER, stprop TEXT);
INSERT INTO test.txtpeople VALUES (1, 'ABITBOL', 'PAUL', 32, 1000, NULL), (1, 'DUPONT', 'JEAN', 34, 1100, NULL), (1, 'SMITH','BOB', 35, 1200, NULL), (1, 'ABITBOL ', ' PAUL', 36, 1500, NULL);

SELECT * FROM (
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
union ALL
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
) T ;

# Cleanup
DROP TABLE test.txtpeople;

Test case 2 causes ER_CRASHED_ON_USAGE on release builds and assertion failure on debug builds:

10.3 non-debug f3ff45f9
mysqltest: At line 4: query 'SELECT * FROM (
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
union ALL
SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)
) T ' failed: 1194: Table '(temporary)' is marked as crashed and should be repaired

10.3 debug f3ff45f9
mysqld: /data/src/10.3/storage/maria/ma_blockrec.c:2801: write_block_record: Assertion `length <= column->length' failed.
190329 1:04:31 [ERROR] mysqld got signal 6 ;

#7 0x00007f4b5b0fbee2 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6
#8 0x0000556cc8856756 in write_block_record (info=0x7f4b381102d0, old_record=0x0, record=0x7f4b38190e48 "\340\001", row=0x7f4b38110338, bitmap_blocks=0x7f4b38110338, head_block_is_read=0 '\000', row_pos=0x7f4b55256410, undo_lsn=1, old_record_checksum=0) at /data/src/10.3/storage/maria/ma_blockrec.c:2801
#9 0x0000556cc8858996 in allocate_and_write_block_record (info=0x7f4b381102d0, record=0x7f4b38190e48 "\340\001", row=0x7f4b38110338, undo_lsn=1) at /data/src/10.3/storage/maria/ma_blockrec.c:3563
#10 0x0000556cc8858aa4 in _ma_write_init_block_record (info=0x7f4b381102d0, record=0x7f4b38190e48 "\340\001") at /data/src/10.3/storage/maria/ma_blockrec.c:3603
#11 0x0000556cc886aac3 in maria_write (info=0x7f4b381102d0, record=0x7f4b38190e48 "\340\001") at /data/src/10.3/storage/maria/ma_write.c:157
#12 0x0000556cc87eea89 in ha_maria::write_row (this=0x7f4b38174fb8, buf=0x7f4b38190e48 "\340\001") at /data/src/10.3/storage/maria/ha_maria.cc:1291
#13 0x0000556cc8372a36 in handler::ha_write_tmp_row (this=0x7f4b38174fb8, buf=0x7f4b38190e48 "\340\001") at /data/src/10.3/sql/sql_class.h:6432
#14 0x0000556cc83d56ff in select_unit::send_data (this=0x7f4b3801a438, values=...) at /data/src/10.3/sql/sql_union.cc:149
#15 0x0000556cc83d69b2 in select_union_direct::send_data (this=0x7f4b3801a520, items=...) at /data/src/10.3/sql/sql_union.cc:553
#16 0x0000556cc835c42e in end_send (join=0x7f4b3801a640, join_tab=0x7f4b380fd818, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:20580
#17 0x0000556cc8359c3e in evaluate_join_record (join=0x7f4b3801a640, join_tab=0x7f4b380fd468, error=0) at /data/src/10.3/sql/sql_select.cc:19616
#18 0x0000556cc836ec70 in AGGR_OP::end_send (this=0x7f4b380fde48) at /data/src/10.3/sql/sql_select.cc:27448
#19 0x0000556cc8358fc7 in sub_select_postjoin_aggr (join=0x7f4b3801a640, join_tab=0x7f4b380fd468, end_of_records=true) at /data/src/10.3/sql/sql_select.cc:19112
#20 0x0000556cc83592f4 in sub_select (join=0x7f4b3801a640, join_tab=0x7f4b380fd0b8, end_of_records=true) at /data/src/10.3/sql/sql_select.cc:19347
#21 0x0000556cc8358abc in do_select (join=0x7f4b3801a640, procedure=0x0) at /data/src/10.3/sql/sql_select.cc:18938
#22 0x0000556cc833161f in JOIN::exec_inner (this=0x7f4b3801a640) at /data/src/10.3/sql/sql_select.cc:4040
#23 0x0000556cc8330a60 in JOIN::exec (this=0x7f4b3801a640) at /data/src/10.3/sql/sql_select.cc:3834
#24 0x0000556cc83da43b in st_select_lex_unit::exec (this=0x7f4b38015510) at /data/src/10.3/sql/sql_union.cc:1480
#25 0x0000556cc829b64f in mysql_derived_fill (thd=0x7f4b38000b00, lex=0x7f4b38004900, derived=0x7f4b38019760) at /data/src/10.3/sql/sql_derived.cc:1116
#26 0x0000556cc82994ad in mysql_handle_single_derived (lex=0x7f4b38004900, derived=0x7f4b38019760, phases=96) at /data/src/10.3/sql/sql_derived.cc:199
#27 0x0000556cc83482c3 in st_join_table::preread_init (this=0x7f4b38106360) at /data/src/10.3/sql/sql_select.cc:12576
#28 0x0000556cc835937e in sub_select (join=0x7f4b38019ed0, join_tab=0x7f4b38106360, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:19364
#29 0x0000556cc8358a56 in do_select (join=0x7f4b38019ed0, procedure=0x0) at /data/src/10.3/sql/sql_select.cc:18936
#30 0x0000556cc833161f in JOIN::exec_inner (this=0x7f4b38019ed0) at /data/src/10.3/sql/sql_select.cc:4040
#31 0x0000556cc8330a60 in JOIN::exec (this=0x7f4b38019ed0) at /data/src/10.3/sql/sql_select.cc:3834
#32 0x0000556cc8331d00 in mysql_select (thd=0x7f4b38000b00, tables=0x7f4b38019760, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f4b38019ea8, unit=0x7f4b380049c8, select_lex=0x7f4b38005138) at /data/src/10.3/sql/sql_select.cc:4239
#33 0x0000556cc8323c20 in handle_select (thd=0x7f4b38000b00, lex=0x7f4b38004900, result=0x7f4b38019ea8, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:385
#34 0x0000556cc82ee617 in execute_sqlcom_select (thd=0x7f4b38000b00, all_tables=0x7f4b38019760) at /data/src/10.3/sql/sql_parse.cc:6548
#35 0x0000556cc82e4f36 in mysql_execute_command (thd=0x7f4b38000b00) at /data/src/10.3/sql/sql_parse.cc:3821
#36 0x0000556cc82f25b5 in mysql_parse (thd=0x7f4b38000b00, rawbuf=0x7f4b38014ce8 "SELECT * FROM (\nSELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) \nunion ALL\nSELECT id, trim(name), age, s"..., length=318, parser_state=0x7f4b552585f0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:8091
#37 0x0000556cc82df884 in dispatch_command (command=COM_QUERY, thd=0x7f4b38000b00, packet=0x7f4b3811ffb1 "", packet_length=319, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1857
#38 0x0000556cc82de26e in do_command (thd=0x7f4b38000b00) at /data/src/10.3/sql/sql_parse.cc:1403
#39 0x0000556cc8446df5 in do_handle_one_connection (connect=0x556ccb5d9cc0) at /data/src/10.3/sql/sql_connect.cc:1402
#40 0x0000556cc8446b79 in handle_one_connection (arg=0x556ccb5d9cc0) at /data/src/10.3/sql/sql_connect.cc:1308
#41 0x0000556cc88e3617 in pfs_spawn_thread (arg=0x556ccb5e31d0) at /data/src/10.3/storage/perfschema/pfs.cc:1862
#42 0x00007f4b5cfea494 in start_thread (arg=0x7f4b55259700) at pthread_create.c:333
#43 0x00007f4b5b1b893f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Elena Stepanova added a comment - 2019-03-28 23:05 Thanks for the report and great test cases. Reproducible as described. Below are the same exact queries as described, just put together for MTR or copy-paste into the command-line client. Test case 1: ERROR 1194 CREATE TABLE test.txtpeople (id INTEGER , name TEXT, firstname TEXT, age INTEGER , revenue INTEGER , stprop TEXT); INSERT INTO test.txtpeople VALUES (1, 'ABITBOL' , 'PAUL' , 32, 1000, NULL ), (1, 'DUPONT' , 'JEAN' , 34, 1100, NULL ), (1, 'SMITH' , 'BOB' , 35, 1200, NULL ), (1, 'ABITBOL ' , ' PAUL' , 36, 1500, NULL ); SELECT id, trim( name ), age, sum (revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull (stprop) GROUP BY trim( name ), trim(firstname) union distinct SELECT id, trim( name ), age, sum (revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull (stprop) GROUP BY trim( name ), trim(firstname) ; # Cleanup DROP TABLE test.txtpeople; Test case 1 causes ER_CRASHED_ON_USAGE both on release and debug builds: 10.3 f3ff45f9 mysqltest: At line 4: query 'SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) union distinct SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) ' failed: 1194: Table '(temporary)' is marked as crashed and should be repaired Test case 2: ERROR 1194 or assertion failure CREATE TABLE test.txtpeople (id INTEGER , name TEXT, firstname TEXT, age INTEGER , revenue INTEGER , stprop TEXT); INSERT INTO test.txtpeople VALUES (1, 'ABITBOL' , 'PAUL' , 32, 1000, NULL ), (1, 'DUPONT' , 'JEAN' , 34, 1100, NULL ), (1, 'SMITH' , 'BOB' , 35, 1200, NULL ), (1, 'ABITBOL ' , ' PAUL' , 36, 1500, NULL ); SELECT * FROM ( SELECT id, trim( name ), age, sum (revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull (stprop) GROUP BY trim( name ), trim(firstname) union ALL SELECT id, trim( name ), age, sum (revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull (stprop) GROUP BY trim( name ), trim(firstname) ) T ; # Cleanup DROP TABLE test.txtpeople; Test case 2 causes ER_CRASHED_ON_USAGE on release builds and assertion failure on debug builds: 10.3 non-debug f3ff45f9 mysqltest: At line 4: query 'SELECT * FROM ( SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) union ALL SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) ) T ' failed: 1194: Table '(temporary)' is marked as crashed and should be repaired 10.3 debug f3ff45f9 mysqld: /data/src/10.3/storage/maria/ma_blockrec.c:2801: write_block_record: Assertion `length <= column->length' failed. 190329 1:04:31 [ERROR] mysqld got signal 6 ; #7 0x00007f4b5b0fbee2 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6 #8 0x0000556cc8856756 in write_block_record (info=0x7f4b381102d0, old_record=0x0, record=0x7f4b38190e48 "\340\001", row=0x7f4b38110338, bitmap_blocks=0x7f4b38110338, head_block_is_read=0 '\000', row_pos=0x7f4b55256410, undo_lsn=1, old_record_checksum=0) at /data/src/10.3/storage/maria/ma_blockrec.c:2801 #9 0x0000556cc8858996 in allocate_and_write_block_record (info=0x7f4b381102d0, record=0x7f4b38190e48 "\340\001", row=0x7f4b38110338, undo_lsn=1) at /data/src/10.3/storage/maria/ma_blockrec.c:3563 #10 0x0000556cc8858aa4 in _ma_write_init_block_record (info=0x7f4b381102d0, record=0x7f4b38190e48 "\340\001") at /data/src/10.3/storage/maria/ma_blockrec.c:3603 #11 0x0000556cc886aac3 in maria_write (info=0x7f4b381102d0, record=0x7f4b38190e48 "\340\001") at /data/src/10.3/storage/maria/ma_write.c:157 #12 0x0000556cc87eea89 in ha_maria::write_row (this=0x7f4b38174fb8, buf=0x7f4b38190e48 "\340\001") at /data/src/10.3/storage/maria/ha_maria.cc:1291 #13 0x0000556cc8372a36 in handler::ha_write_tmp_row (this=0x7f4b38174fb8, buf=0x7f4b38190e48 "\340\001") at /data/src/10.3/sql/sql_class.h:6432 #14 0x0000556cc83d56ff in select_unit::send_data (this=0x7f4b3801a438, values=...) at /data/src/10.3/sql/sql_union.cc:149 #15 0x0000556cc83d69b2 in select_union_direct::send_data (this=0x7f4b3801a520, items=...) at /data/src/10.3/sql/sql_union.cc:553 #16 0x0000556cc835c42e in end_send (join=0x7f4b3801a640, join_tab=0x7f4b380fd818, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:20580 #17 0x0000556cc8359c3e in evaluate_join_record (join=0x7f4b3801a640, join_tab=0x7f4b380fd468, error=0) at /data/src/10.3/sql/sql_select.cc:19616 #18 0x0000556cc836ec70 in AGGR_OP::end_send (this=0x7f4b380fde48) at /data/src/10.3/sql/sql_select.cc:27448 #19 0x0000556cc8358fc7 in sub_select_postjoin_aggr (join=0x7f4b3801a640, join_tab=0x7f4b380fd468, end_of_records=true) at /data/src/10.3/sql/sql_select.cc:19112 #20 0x0000556cc83592f4 in sub_select (join=0x7f4b3801a640, join_tab=0x7f4b380fd0b8, end_of_records=true) at /data/src/10.3/sql/sql_select.cc:19347 #21 0x0000556cc8358abc in do_select (join=0x7f4b3801a640, procedure=0x0) at /data/src/10.3/sql/sql_select.cc:18938 #22 0x0000556cc833161f in JOIN::exec_inner (this=0x7f4b3801a640) at /data/src/10.3/sql/sql_select.cc:4040 #23 0x0000556cc8330a60 in JOIN::exec (this=0x7f4b3801a640) at /data/src/10.3/sql/sql_select.cc:3834 #24 0x0000556cc83da43b in st_select_lex_unit::exec (this=0x7f4b38015510) at /data/src/10.3/sql/sql_union.cc:1480 #25 0x0000556cc829b64f in mysql_derived_fill (thd=0x7f4b38000b00, lex=0x7f4b38004900, derived=0x7f4b38019760) at /data/src/10.3/sql/sql_derived.cc:1116 #26 0x0000556cc82994ad in mysql_handle_single_derived (lex=0x7f4b38004900, derived=0x7f4b38019760, phases=96) at /data/src/10.3/sql/sql_derived.cc:199 #27 0x0000556cc83482c3 in st_join_table::preread_init (this=0x7f4b38106360) at /data/src/10.3/sql/sql_select.cc:12576 #28 0x0000556cc835937e in sub_select (join=0x7f4b38019ed0, join_tab=0x7f4b38106360, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:19364 #29 0x0000556cc8358a56 in do_select (join=0x7f4b38019ed0, procedure=0x0) at /data/src/10.3/sql/sql_select.cc:18936 #30 0x0000556cc833161f in JOIN::exec_inner (this=0x7f4b38019ed0) at /data/src/10.3/sql/sql_select.cc:4040 #31 0x0000556cc8330a60 in JOIN::exec (this=0x7f4b38019ed0) at /data/src/10.3/sql/sql_select.cc:3834 #32 0x0000556cc8331d00 in mysql_select (thd=0x7f4b38000b00, tables=0x7f4b38019760, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f4b38019ea8, unit=0x7f4b380049c8, select_lex=0x7f4b38005138) at /data/src/10.3/sql/sql_select.cc:4239 #33 0x0000556cc8323c20 in handle_select (thd=0x7f4b38000b00, lex=0x7f4b38004900, result=0x7f4b38019ea8, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:385 #34 0x0000556cc82ee617 in execute_sqlcom_select (thd=0x7f4b38000b00, all_tables=0x7f4b38019760) at /data/src/10.3/sql/sql_parse.cc:6548 #35 0x0000556cc82e4f36 in mysql_execute_command (thd=0x7f4b38000b00) at /data/src/10.3/sql/sql_parse.cc:3821 #36 0x0000556cc82f25b5 in mysql_parse (thd=0x7f4b38000b00, rawbuf=0x7f4b38014ce8 "SELECT * FROM (\nSELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM test.txtpeople WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) \nunion ALL\nSELECT id, trim(name), age, s"..., length=318, parser_state=0x7f4b552585f0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:8091 #37 0x0000556cc82df884 in dispatch_command (command=COM_QUERY, thd=0x7f4b38000b00, packet=0x7f4b3811ffb1 "", packet_length=319, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1857 #38 0x0000556cc82de26e in do_command (thd=0x7f4b38000b00) at /data/src/10.3/sql/sql_parse.cc:1403 #39 0x0000556cc8446df5 in do_handle_one_connection (connect=0x556ccb5d9cc0) at /data/src/10.3/sql/sql_connect.cc:1402 #40 0x0000556cc8446b79 in handle_one_connection (arg=0x556ccb5d9cc0) at /data/src/10.3/sql/sql_connect.cc:1308 #41 0x0000556cc88e3617 in pfs_spawn_thread (arg=0x556ccb5e31d0) at /data/src/10.3/storage/perfschema/pfs.cc:1862 #42 0x00007f4b5cfea494 in start_thread (arg=0x7f4b55259700) at pthread_create.c:333 #43 0x00007f4b5b1b893f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Alexander Barkov added a comment - 2019-10-14 04:17

Testes with 10.5 as of c1519d62d0fe132c8d81008cc6a686f7f7e327c0.

Data:

CREATE OR REPLACE TABLE t1 (id INTEGER, name TEXT, firstname TEXT, age INTEGER, revenue INTEGER, stprop TEXT);

INSERT INTO t1 VALUES

  (1, 'ABITBOL', 'PAUL', 32, 1000, NULL),

  (1, 'DUPONT', 'JEAN', 34, 1100, NULL),

  (1, 'SMITH','BOB', 35, 1200, NULL),

  (1, 'ABITBOL ', ' PAUL', 36, 1500, NULL);

The following SQL queries do not work well:

# ERROR 1194 (HY000): Table '(temporary)' is marked as crashed and should be repaired

SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM t1 WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)

union distinct

SELECT id, trim(name), age, sum(revenue), trim(firstname), stprop FROM t1 WHERE isnull(stprop) GROUP BY trim(name), trim(firstname) ;

# Crash

SELECT id, trim(name), age, trim(firstname), stprop FROM t1 WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)

union distinct

SELECT id, trim(name), age, trim(firstname), stprop FROM t1 WHERE isnull(stprop) GROUP BY trim(name), trim(firstname);

# Crash

SELECT id, trim(name), trim(firstname), stprop FROM t1 WHERE isnull(stprop) GROUP BY trim(name), trim(firstname)

union distinct

SELECT id, trim(name), trim(firstname), stprop FROM t1 WHERE isnull(stprop) GROUP BY trim(name), trim(firstname);

# Crash

SELECT id, trim(name), trim(firstname), stprop FROM t1 GROUP BY trim(name), trim(firstname)

union distinct

SELECT id, trim(name), trim(firstname), stprop FROM t1 GROUP BY trim(name), trim(firstname);

# Crash

SELECT id, trim(name), stprop FROM t1 GROUP BY trim(name), trim(firstname)

union distinct

SELECT id, trim(name), stprop FROM t1 GROUP BY trim(name), trim(firstname);

# Crash

SELECT trim(name), stprop FROM t1 GROUP BY trim(name), trim(firstname)

union distinct

SELECT trim(name), stprop FROM t1 GROUP BY trim(name), trim(firstname);

Alexander Barkov added a comment - 2019-10-14 04:17 Testes with 10.5 as of c1519d62d0fe132c8d81008cc6a686f7f7e327c0. Data: CREATE OR REPLACE TABLE t1 (id INTEGER , name TEXT, firstname TEXT, age INTEGER , revenue INTEGER , stprop TEXT); INSERT INTO t1 VALUES (1, 'ABITBOL' , 'PAUL' , 32, 1000, NULL ), (1, 'DUPONT' , 'JEAN' , 34, 1100, NULL ), (1, 'SMITH' , 'BOB' , 35, 1200, NULL ), (1, 'ABITBOL ' , ' PAUL' , 36, 1500, NULL ); The following SQL queries do not work well: # ERROR 1194 (HY000): Table '(temporary)' is marked as crashed and should be repaired SELECT id, trim( name ), age, sum (revenue), trim(firstname), stprop FROM t1 WHERE isnull (stprop) GROUP BY trim( name ), trim(firstname) union distinct SELECT id, trim( name ), age, sum (revenue), trim(firstname), stprop FROM t1 WHERE isnull (stprop) GROUP BY trim( name ), trim(firstname) ; # Crash SELECT id, trim( name ), age, trim(firstname), stprop FROM t1 WHERE isnull (stprop) GROUP BY trim( name ), trim(firstname) union distinct SELECT id, trim( name ), age, trim(firstname), stprop FROM t1 WHERE isnull (stprop) GROUP BY trim( name ), trim(firstname); # Crash SELECT id, trim( name ), trim(firstname), stprop FROM t1 WHERE isnull (stprop) GROUP BY trim( name ), trim(firstname) union distinct SELECT id, trim( name ), trim(firstname), stprop FROM t1 WHERE isnull (stprop) GROUP BY trim( name ), trim(firstname); # Crash SELECT id, trim( name ), trim(firstname), stprop FROM t1 GROUP BY trim( name ), trim(firstname) union distinct SELECT id, trim( name ), trim(firstname), stprop FROM t1 GROUP BY trim( name ), trim(firstname); # Crash SELECT id, trim( name ), stprop FROM t1 GROUP BY trim( name ), trim(firstname) union distinct SELECT id, trim( name ), stprop FROM t1 GROUP BY trim( name ), trim(firstname); # Crash SELECT trim( name ), stprop FROM t1 GROUP BY trim( name ), trim(firstname) union distinct SELECT trim( name ), stprop FROM t1 GROUP BY trim( name ), trim(firstname);

Rodney added a comment - 2020-04-21 17:05 - edited

I have exantly the same problem. And of course you can't "repair" this table.
Ver 15.1 Distrib 10.4.11-MariaDB, for Win64 (AMD64), source revision 7c2c420b70b19cc02b5281127205e876f3919dad

I'm using it through node:
(node:4120) UnhandledPromiseRejectionWarning: Error: ER_CRASHED_ON_USAGE: Table '(temporary)' is marked as crashed and should be repaired
at Query.Sequence._packetToError (node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
at Query.ErrorPacket (node_modules\mysql\lib\protocol\sequences\Query.js:77:18)
at Protocol._parsePacket (node_modules\mysql\lib\protocol\Protocol.js:278:23)
at Parser.write (node_modules\mysql\lib\protocol\Parser.js:76:12)
at Protocol.write (node_modules\mysql\lib\protocol\Protocol.js:38:16)
at Socket.<anonymous> (node_modules\mysql\lib\Connection.js:91:28)
at Socket.<anonymous> (node_modules\mysql\lib\Connection.js:502:10)
at Socket.emit (events.js:310:20)
at addChunk (_stream_readable.js:286:12)
at readableAddChunk (_stream_readable.js:268:9)
--------------------
at Protocol._enqueue (node_modules\mysql\lib\protocol\Protocol.js:144:48)
at PoolConnection.query (node_modules\mysql\lib\Connection.js:200:25)
at old_Search.js:68:7
at new Promise (<anonymous>)
at getTableColumns (old_Search.js:67:9)
at runMicrotasks (<anonymous>)
at processTicksAndRejections (internal/process/task_queues.js:97:5)
at async old_Search.js:111:21
(node:4120) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
(node:4120) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

Rodney added a comment - 2020-04-21 17:05 - edited I have exantly the same problem. And of course you can't "repair" this table. Ver 15.1 Distrib 10.4.11-MariaDB, for Win64 (AMD64), source revision 7c2c420b70b19cc02b5281127205e876f3919dad I'm using it through node: (node:4120) UnhandledPromiseRejectionWarning: Error: ER_CRASHED_ON_USAGE: Table '(temporary)' is marked as crashed and should be repaired at Query.Sequence._packetToError (node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14) at Query.ErrorPacket (node_modules\mysql\lib\protocol\sequences\Query.js:77:18) at Protocol._parsePacket (node_modules\mysql\lib\protocol\Protocol.js:278:23) at Parser.write (node_modules\mysql\lib\protocol\Parser.js:76:12) at Protocol.write (node_modules\mysql\lib\protocol\Protocol.js:38:16) at Socket.<anonymous> (node_modules\mysql\lib\Connection.js:91:28) at Socket.<anonymous> (node_modules\mysql\lib\Connection.js:502:10) at Socket.emit (events.js:310:20) at addChunk (_stream_readable.js:286:12) at readableAddChunk (_stream_readable.js:268:9) -------------------- at Protocol._enqueue (node_modules\mysql\lib\protocol\Protocol.js:144:48) at PoolConnection.query (node_modules\mysql\lib\Connection.js:200:25) at old_Search.js:68:7 at new Promise (<anonymous>) at getTableColumns (old_Search.js:67:9) at runMicrotasks (<anonymous>) at processTicksAndRejections (internal/process/task_queues.js:97:5) at async old_Search.js:111:21 (node:4120) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode ). (rejection id: 1) (node:4120) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

Elena Stepanova added a comment - 2021-02-10 15:17

The originally reported bug was fixed in 10.3.17 and 10.4.7 in the scope of ~~MDEV-17551~~:

commit 9053047f3db37a174f6c1333acf189b6558c50c4

Author: Monty

Date:   Thu Jun 27 18:51:34 2019 +0300

    MDEV-17551 assert or crashed table when using blobs

The test cases above were failing before this fix, stopped failing after, and the issues are obviously related, so it all makes sense.
Whoever gets a similar problem on later versions, it's a different bug and should be reported separately (or linked to a different open ticket, if there is one).

Elena Stepanova added a comment - 2021-02-10 15:17 The originally reported bug was fixed in 10.3.17 and 10.4.7 in the scope of MDEV-17551 : commit 9053047f3db37a174f6c1333acf189b6558c50c4 Author: Monty Date: Thu Jun 27 18:51:34 2019 +0300 MDEV-17551 assert or crashed table when using blobs The test cases above were failing before this fix, stopped failing after, and the issues are obviously related, so it all makes sense. Whoever gets a similar problem on later versions, it's a different bug and should be reported separately (or linked to a different open ticket, if there is one).

MariaDB Server

ERROR 1194 or server crashes after a SELECT with UNION and TRIM on TEXT fields

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration