[MDEV-20862] ERROR 2013 when SELECT with DATE_ADD Created: 2019-10-19  Updated: 2020-07-08  Resolved: 2020-07-08

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.4.8
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Myst Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: crash, innodb
Environment:
  1. cat /etc/debian_version
    10.1
  2. dpkg -l | grep -i mariadb-server
    ii mariadb-server 1:10.4.8+maria~buster all MariaDB database server (metapackage depending on the latest version)
    ii mariadb-server-10.4 1:10.4.8+maria~buster amd64 MariaDB database server binaries
    ii mariadb-server-core-10.4 1:10.4.8+maria~buster amd64 MariaDB database core server files

Attachments: File mariadb.cnf     File my.cnf    
Issue Links:
Duplicate
duplicates MDEV-20252 Specific SQL query consistently crash... Closed

 Description   

Hi,

When I try to select some data from a InnoDB table, I got a :

ERROR 2013 (HY000): Lost connection to MySQL server during query

with the query 

SELECT illuminance.datetime, illuminance.value illuminance FROM illuminance WHERE illuminance.ieeeAddr='0x00<REDACTED>' AND illuminance.datetime >= DATE_ADD(CURDATE(), INTERVAL -7 DAY) ORDER BY datetime;

If I remove the "illuminance.datetime >= DATE_ADD(CURDATE(), INTERVAL -7 DAY)", query is OK.

/var/log/syslog message :

Oct 19 07:43:58 mysql mysqld[9783]: 191019  7:43:58 [ERROR] mysqld got signal 11 ;
 
Oct 19 07:43:58 mysql mysqld[9783]: This could be because you hit a bug. It is also possible that this binary
 
Oct 19 07:43:58 mysql mysqld[9783]: or one of the libraries it was linked against is corrupt, improperly built,
 
Oct 19 07:43:58 mysql mysqld[9783]: or misconfigured. This error can also be caused by malfunctioning hardware.
 
Oct 19 07:43:58 mysql mysqld[9783]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
Oct 19 07:43:58 mysql mysqld[9783]: We will try our best to scrape up some info that will hopefully help
 
Oct 19 07:43:58 mysql mysqld[9783]: diagnose the problem, but since we have already crashed,
 
Oct 19 07:43:58 mysql mysqld[9783]: something is definitely wrong and this may fail.
 
Oct 19 07:43:58 mysql mysqld[9783]: Server version: 10.4.8-MariaDB-1:10.4.8+maria~buster-log
 
Oct 19 07:43:58 mysql mysqld[9783]: key_buffer_size=134217728
 
Oct 19 07:43:58 mysql mysqld[9783]: read_buffer_size=2097152
 
Oct 19 07:43:58 mysql mysqld[9783]: max_used_connections=1
 
Oct 19 07:43:58 mysql mysqld[9783]: max_threads=102
 
Oct 19 07:43:58 mysql mysqld[9783]: thread_count=8
 
Oct 19 07:43:58 mysql mysqld[9783]: It is possible that mysqld could use up to
 
Oct 19 07:43:58 mysql mysqld[9783]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 760242 K  bytes of memory
 
Oct 19 07:43:58 mysql mysqld[9783]: Hope that's ok; if not, decrease some variables in the equation.
 
Oct 19 07:43:58 mysql mysqld[9783]: Thread pointer: 0x7fa4b80013a8
 
Oct 19 07:43:58 mysql mysqld[9783]: Attempting backtrace. You can use the following information to find out
 
Oct 19 07:43:58 mysql mysqld[9783]: where mysqld died. If you see no messages after this, something went
 
Oct 19 07:43:58 mysql mysqld[9783]: terribly wrong...
 
Oct 19 07:43:58 mysql mysqld[9783]: stack_bottom = 0x7fa520132dd8 thread_stack 0x49000
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x55ba35f45a3e]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(handle_fatal_signal+0x54d)[0x55ba35a3c26d]
 
Oct 19 07:43:58 mysql mysqld[9783]: /lib/x86_64-linux-gnu/libpthread.so.0(+0x12730)[0x7fa527aed730]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(handler_index_cond_check+0x7f)[0x55ba35a476ef]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(+0xb09730)[0x55ba35caf730]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(+0xb0c195)[0x55ba35cb2195]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(+0xa39dc2)[0x55ba35bdfdc2]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(_ZN7handler10ha_rnd_posEPhS0_+0x132)[0x55ba35a42a22]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(_Z16rr_from_pointersP11READ_RECORD+0x3a)[0x55ba35b659aa]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x17a)[0x55ba3586cc9a]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(_ZN4JOIN10exec_innerEv+0xba2)[0x55ba35891d22]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(_ZN4JOIN4execEv+0x33)[0x55ba35891f93]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x131)[0x55ba35890441]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x151)[0x55ba35890d21]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(+0x6847ac)[0x55ba3582a7ac]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x17bd)[0x55ba35832aed]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x219)[0x55ba3583a029]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1546)[0x55ba3583c5b6]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(_Z10do_commandP3THD+0x15e)[0x55ba3583dc1e]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x25e)[0x55ba3591dd7e]
 
Oct 19 07:43:58 mysql mysqld[9783]: /usr/sbin/mysqld(handle_one_connection+0x3d)[0x55ba3591de4d]
 
Oct 19 07:43:58 mysql mysqld[9783]: /lib/x86_64-linux-gnu/libpthread.so.0(+0x7fa3)[0x7fa527ae2fa3]
 
Oct 19 07:43:58 mysql mysqld[9783]: /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7fa526ff74cf]
 
Oct 19 07:43:58 mysql mysqld[9783]: Trying to get some variables.
 
Oct 19 07:43:58 mysql mysqld[9783]: Some pointers may be invalid and cause the dump to abort.
 
Oct 19 07:43:58 mysql mysqld[9783]: Query (0x7fa4b8012ab0): SELECT illuminance.datetime, illuminance.value illuminance FROM illuminance WHERE illuminance.ieeeAddr='0x00158d0002b71f9b' AND illuminance.datetime >= DATE_ADD(CURDATE(), INTER                                                                                VAL -7 DAY) ORDER BY datetime
 
Oct 19 07:43:58 mysql mysqld[9783]: Connection ID (thread ID): 732
 
Oct 19 07:43:58 mysql mysqld[9783]: Status: NOT_KILLED
 
Oct 19 07:43:58 mysql mysqld[9783]: Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,d                                                                                erived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_                                                                                join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_f                                                                                or_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on
 
Oct 19 07:43:58 mysql mysqld[9783]: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
 
Oct 19 07:43:58 mysql mysqld[9783]: information that should help you find out what is causing the crash.
 
Oct 19 07:43:58 mysql mysqld[9783]: Writing a core file...
 
Oct 19 07:43:58 mysql mysqld[9783]: Working directory at /var/lib/mysql
 
Oct 19 07:43:58 mysql mysqld[9783]: Resource Limits:
 
Oct 19 07:43:58 mysql mysqld[9783]: Limit                     Soft Limit           Hard Limit           Units
 
Oct 19 07:43:58 mysql mysqld[9783]: Max cpu time              unlimited            unlimited            seconds
 
Oct 19 07:43:58 mysql mysqld[9783]: Max file size             unlimited            unlimited            bytes
 
Oct 19 07:43:58 mysql mysqld[9783]: Max data size             unlimited            unlimited            bytes
 
Oct 19 07:43:58 mysql mysqld[9783]: Max stack size            8388608              unlimited            bytes
 
Oct 19 07:43:58 mysql mysqld[9783]: Max core file size        0                    unlimited            bytes
 
Oct 19 07:43:58 mysql mysqld[9783]: Max resident set          unlimited            unlimited            bytes
 
Oct 19 07:43:58 mysql mysqld[9783]: Max processes             15671                15671                processes
 
Oct 19 07:43:58 mysql mysqld[9783]: Max open files            16364                16364                files
 
Oct 19 07:43:58 mysql kernel: [558383.720722] mysqld[9830]: segfault at 0 ip 000055ba35a476ef sp 00007fa52012e390 error 4 in mysqld[55ba35700000+8d3000]
 
Oct 19 07:43:58 mysql kernel: [558383.720727] Code: 00 00 85 c0 7f 34 48 8b 43 10 48 8b 40 48 48 83 80 d8 10 00 00 01 48 8b 43 10 48 8b 78 48 e8 38 3a 00 00 48 8b bb e8 03 00 00 <48> 8b 07 ff 90 00 01 00 00 31 d2 48 85 c0 75 11 5b 89 d0                                                                                 41 5c 5d
 
Oct 19 07:43:58 mysql mysqld[9783]: Max locked memory         65536                65536                bytes
 
Oct 19 07:43:58 mysql mysqld[9783]: Max address space         unlimited            unlimited            bytes
 
Oct 19 07:43:58 mysql mysqld[9783]: Max file locks            unlimited            unlimited            locks
 
Oct 19 07:43:58 mysql mysqld[9783]: Max pending signals       15671                15671                signals
 
Oct 19 07:43:58 mysql mysqld[9783]: Max msgqueue size         819200               819200               bytes
 
Oct 19 07:43:58 mysql mysqld[9783]: Max nice priority         0                    0
 
Oct 19 07:43:58 mysql mysqld[9783]: Max realtime priority     0                    0
 
Oct 19 07:43:58 mysql mysqld[9783]: Max realtime timeout      unlimited            unlimited            us
 
Oct 19 07:43:58 mysql mysqld[9783]: Core pattern: core
 
Oct 19 07:43:58 mysql systemd[1]: mariadb.service: Main process exited, code=killed, status=11/SEGV
 
Oct 19 07:43:58 mysql systemd[1]: mariadb.service: Failed with result 'signal'.
 
Oct 19 07:44:03 mysql systemd[1]: mariadb.service: Service RestartSec=5s expired, scheduling restart.
 
Oct 19 07:44:03 mysql systemd[1]: mariadb.service: Scheduled restart job, restart counter is at 7.
 
Oct 19 07:44:03 mysql systemd[1]: Stopped MariaDB 10.4.8 database server.
 
Oct 19 07:44:03 mysql systemd[1]: Starting MariaDB 10.4.8 database server...
 
Oct 19 07:44:03 mysql mysqld[10737]: 2019-10-19  7:44:03 0 [Note] /usr/sbin/mysqld (mysqld 10.4.8-MariaDB-1:10.4.8+maria~buster-log) starting as process 10737 ...
 
Oct 19 07:44:03 mysql mysqld[10737]: 2019-10-19  7:44:03 0 [Note] InnoDB: Using Linux native AIO
 
Oct 19 07:44:03 mysql mysqld[10737]: 2019-10-19  7:44:03 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 
Oct 19 07:44:03 mysql mysqld[10737]: 2019-10-19  7:44:03 0 [Note] InnoDB: Uses event mutexes
 
Oct 19 07:44:03 mysql mysqld[10737]: 2019-10-19  7:44:03 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
 
Oct 19 07:44:03 mysql mysqld[10737]: 2019-10-19  7:44:03 0 [Note] InnoDB: Number of pools: 1
 
Oct 19 07:44:03 mysql mysqld[10737]: 2019-10-19  7:44:03 0 [Note] InnoDB: Using generic crc32 instructions
 
Oct 19 07:44:03 mysql mysqld[10737]: 2019-10-19  7:44:03 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts)
 
Oct 19 07:44:03 mysql mysqld[10737]: 2019-10-19  7:44:03 0 [Note] InnoDB: Initializing buffer pool, total size = 256M, instances = 1, chunk size = 128M
 
Oct 19 07:44:03 mysql mysqld[10737]: 2019-10-19  7:44:03 0 [Note] InnoDB: Completed initialization of buffer pool
 
Oct 19 07:44:03 mysql mysqld[10737]: 2019-10-19  7:44:03 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
 
Oct 19 07:44:03 mysql mysqld[10737]: 2019-10-19  7:44:03 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=58727614
 
Oct 19 07:44:03 mysql mysqld[10737]: 2019-10-19  7:44:03 0 [Note] InnoDB: Last binlog file '/var/log/mysql/mariadb-bin.000054', position 1162
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] InnoDB: Creating shared tablespace for temporary tables
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] InnoDB: Waiting for purge to start
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] InnoDB: 10.4.8 started; log sequence number 58727623; transaction id 10790
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] Plugin 'FEEDBACK' is disabled.
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] Recovering after a crash using /var/log/mysql/mariadb-bin
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] Starting crash recovery...
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] Crash recovery finished.
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] Server socket created on IP: '0.0.0.0'.
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] Reading of all Master_info entries succeeded
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] Added new Master_info '' to hash table
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] /usr/sbin/mysqld: ready for connections.
 
Oct 19 07:44:04 mysql mysqld[10737]: Version: '10.4.8-MariaDB-1:10.4.8+maria~buster-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
 
Oct 19 07:44:04 mysql /etc/mysql/debian-start[10772]: Upgrading MySQL tables if necessary.
 
Oct 19 07:44:04 mysql systemd[1]: Started MariaDB 10.4.8 database server.
 
Oct 19 07:44:04 mysql /etc/mysql/debian-start[10776]: Looking for 'mysql' as: /usr/bin/mysql
 
Oct 19 07:44:04 mysql /etc/mysql/debian-start[10776]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
 
Oct 19 07:44:04 mysql /etc/mysql/debian-start[10776]: This installation of MariaDB is already upgraded to 10.4.8-MariaDB, use --force if you still need to run mysql_upgrade
 
Oct 19 07:44:04 mysql /etc/mysql/debian-start[10785]: Checking for insecure root accounts.
 
Oct 19 07:44:04 mysql /etc/mysql/debian-start[10789]: Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables
 
Oct 19 07:44:04 mysql mysqld[10737]: 2019-10-19  7:44:04 0 [Note] InnoDB: Buffer pool(s) load completed at 191019  7:44:04



 Comments   
Comment by Elena Stepanova [ 2019-11-05 ]

Could you please provide the output of 

SHOW CREATE TABLE illuminance;
 
SHOW INDEX IN illuminance;

and also paste or attach your config file(s)?
Thanks.

Comment by Myst [ 2019-11-05 ]

Hello Elena,

MariaDB [xiaomi]> SHOW CREATE TABLE illuminance;
 
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table       | Create Table                                                                                                                                                                                                                     |
 
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| illuminance | CREATE TABLE `illuminance` (
 
  `ieeeAddr` tinytext NOT NULL,
 
  `datetime` datetime NOT NULL,
 
  `value` int(11) NOT NULL,
 
  KEY `ieeeAddr` (`ieeeAddr`(18)),
 
  KEY `datetime` (`datetime`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [xiaomi]> SHOW INDEX IN illuminance;
 
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
| illuminance |          1 | ieeeAddr |            1 | ieeeAddr    | A         |           2 |       18 | NULL   |      | BTREE      |         |               |
 
| illuminance |          1 | datetime |            1 | datetime    | A         |       11849 |     NULL | NULL   |      | BTREE      |         |               |
 
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
2 rows in set (0.000 sec)

I've tested my previous SELECT and it works, but if I increase the INTERVAL to "-9 DAY" or further it crash again, so it seem I can't show some dates/data.

I'll add my my.cnf and maria.cnf config files, if you need anything else, just ask.

Comment by Myst [ 2019-11-05 ] 

MariaDB [xiaomi]> SELECT 1 FROM illuminance WHERE illuminance.ieeeAddr='0x00158d0002b71f9b' AND illuminance.datetime >= DATE_ADD(CURDATE(), INTERVAL -9 DAY) ORDER BY datetime;
 
ERROR 2013 (HY000): Lost connection to MySQL server during query

recreating the table without the indexes :

CREATE TABLE illuminance2 SELECT * FROM illuminance;

and the "SELECT" is OK.

recreating the table with the indexes :

CREATE TABLE illuminance2 LIKE illuminance;
 
INSERT illuminance2 SELECT * FROM illuminance;

and the select fail again.

Comment by Alice Sherepa [ 2020-07-08 ]

It looks like this is a duplicate of MDEV-20252, fixed in 10.4.11

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