Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.13, 10.3(EOL), 10.4(EOL)
-
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