|
I ran the referenced query in a replication slave, running on a seperate server with the same version on MariaDB and OS. It returned without issue 6718 rows in set (1.044 sec)
A describe of the query is as follows:
+------+--------------------+--------------+--------+---------------+-----------+---------+---------------------------------------------------+-------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+--------------+--------+---------------+-----------+---------+---------------------------------------------------+-------+--------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 68034 | Using filesort |
|
| 2 | DERIVED | a | ref | practices | practices | 3 | const | 22678 | Using where |
|
| 2 | DERIVED | ci | ref | PRIMARY | PRIMARY | 4 | ddx_lab_801317.a.ref_id | 1 | Using where; Using index |
|
| 2 | DERIVED | i | eq_ref | PRIMARY | PRIMARY | 4 | ddx_lab_801317.a.ref_id | 1 | Using where; Using index |
|
| 3 | DEPENDENT UNION | a | ref | practices | practices | 3 | const | 22678 | Using where |
|
| 3 | DEPENDENT UNION | ci | ref | PRIMARY | PRIMARY | 4 | ddx_lab_801317.a.ref_id | 1 | Using where; Using index |
|
| 3 | DEPENDENT UNION | i | eq_ref | PRIMARY | PRIMARY | 4 | ddx_lab_801317.a.ref_id | 1 | Using where; Using index |
|
| 3 | DEPENDENT UNION | b | ref | refs | refs | 7 | ddx_lab_801317.a.ref_type,ddx_lab_801317.a.ref_id | 1 | Using where |
|
| 6 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
|
| 5 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
|
| 4 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
|
| 7 | UNCACHEABLE UNION | a | ref | practices | practices | 3 | const | 22678 | Using where |
|
| 7 | UNCACHEABLE UNION | ci | ref | PRIMARY | PRIMARY | 4 | ddx_lab_801317.a.ref_id | 1 | Using where; Using index |
|
| 7 | UNCACHEABLE UNION | i | eq_ref | PRIMARY | PRIMARY | 4 | ddx_lab_801317.a.ref_id | 1 | Using where; Using index |
|
| NULL | UNION RESULT | <union2,3,7> | ALL | NULL | NULL | NULL | NULL | NULL | |
|
+------+--------------------+--------------+--------+---------------+-----------+---------+---------------------------------------------------+-------+--------------------------+
|
|
|
|
Server hit similar crash, this time against a different DB, but same query:
211030 23:06:26 [ERROR] mysqld got signal 11 ;
|
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.
|
|
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
|
|
We will try our best to scrape up some info that will hopefully help
|
diagnose the problem, but since we have already crashed,
|
something is definitely wrong and this may fail.
|
|
Server version: 10.3.31-MariaDB-1:10.3.31+maria~focal-log
|
key_buffer_size=134217728
|
read_buffer_size=131072
|
max_used_connections=635
|
max_threads=752
|
thread_count=69
|
It is possible that mysqld could use up to
|
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1784362 K bytes of memory
|
Hope that's ok; if not, decrease some variables in the equation.
|
|
Thread pointer: 0x7ef828853c48
|
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 = 0x7ef93180edd8 thread_stack 0x49000
|
/usr/sbin/mysqld(my_print_stacktrace+0x32)[0x558fa9eeb3a2]
|
/usr/sbin/mysqld(handle_fatal_signal+0x55d)[0x558fa99c2e8d]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x153c0)[0x7f0a569fa3c0]
|
/usr/sbin/mysqld(+0x978f4f)[0x558fa9b57f4f]
|
/usr/sbin/mysqld(_ZN7handler10ha_rnd_posEPhS0_+0x50)[0x558fa99c86d0]
|
/usr/sbin/mysqld(_Z16rr_from_pointersP11READ_RECORD+0x3e)[0x558fa9af8b2e]
|
/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x1db)[0x558fa9802a0b]
|
/usr/sbin/mysqld(_ZN4JOIN10exec_innerEv+0xbee)[0x558fa982cdce]
|
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x37)[0x558fa982d137]
|
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xf2)[0x558fa982d282]
|
/usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x14b)[0x558fa982dbcb]
|
/usr/sbin/mysqld(+0x5de9f1)[0x558fa97bd9f1]
|
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x5414)[0x558fa97cb6e4]
|
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x1f3)[0x558fa97ce253]
|
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x105d)[0x558fa97d062d]
|
/usr/sbin/mysqld(_Z10do_commandP3THD+0x12d)[0x558fa97d25dd]
|
/usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x246)[0x558fa98bb186]
|
/usr/sbin/mysqld(handle_one_connection+0x3f)[0x558fa98bb35f]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x9609)[0x7f0a569ee609]
|
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f0a56913293]
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7ef82937be00): SELECT * FROM ((
|
#
|
# get postings from last statement and postings not on statements
|
#
|
SELECT a.*, COALESCE(ci.id, i.id) as invoice_id
|
FROM ddx_lab_801777.postings a
|
LEFT JOIN ddx_lab_801777.cases_invoices ci
|
ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id
|
LEFT JOIN ddx_lab_801777.invoices i
|
ON a.ref_type = 'INVOICE' AND a.ref_id = i.id
|
WHERE (
|
a.statement_date IS NULL
|
OR a.statement_date = '2021-10-15'
|
)
|
AND a.practice_id='21085'
|
ORDER BY post_date
|
) UNION (
|
#
|
# get postings that have remaining balances
|
#
|
SELECT DISTINCT a.*, COALESCE(ci.id, i.id) as invoice_id
|
FROM ddx_lab_801777.postings a
|
LEFT JOIN ddx_lab_801777.cases_invoices ci
|
ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id
|
LEFT JOIN ddx_lab_801777.invoices i
|
ON a.ref_type = 'INVOICE' AND a.ref_id = i.id
|
LEFT JOIN ddx_lab_801777.postings b
|
ON a.ref_type = b.ref_type
|
AND a.ref_id = b.ref_id
|
AND a.id != b.id
|
|
WHERE NOT (a.type = 'CREDIT' OR a.type = 'PAYMENT')
|
AND ((SELECT SUM(b.amount)) IS NULL OR (SELECT SUM(a.amount)) + (SELECT SUM(b.amount)) > 0)
|
AND a.practice_id='21085'
|
GROUP BY a.id
|
ORDER BY post_date
|
) UNION (
|
#
|
# get partial payments and credits from older statements
|
#
|
SELECT a.*, COALESCE(ci.id, i.id) as invoice_id
|
FROM ddx_lab_801777.postings a
|
LEFT JOIN ddx_lab_801777.cases_invoices ci
|
ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id
|
LEFT JOIN ddx_lab_801777.invoices i
|
ON a.ref_type = 'INVOICE' AND a.ref_id = i.id
|
WHERE (
|
a.statement_date < '2021-10-15'
|
)
|
AND (a.type='PAYMENT' OR a.type='CREDIT')
|
AND a.practice_id='21085' )) AS t1 ORDER BY post_date ASC, invoice_id ASC
|
|
Connection ID (thread ID): 63072525
|
Status: NOT_KILLED
|
|
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,derived_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=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
|
|
The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
|
information that should help you find out what is causing the crash.
|
Writing a core file...
|
Working directory at /var/lib/mysql
|
Resource Limits:
|
Limit Soft Limit Hard Limit Units
|
Max cpu time unlimited unlimited seconds
|
Max file size unlimited unlimited bytes
|
Max data size unlimited unlimited bytes
|
Max stack size 8388608 unlimited bytes
|
Max core file size 0 unlimited bytes
|
Max resident set unlimited unlimited bytes
|
Max processes unlimited unlimited processes
|
Max open files 1048576 1048576 files
|
Max locked memory unlimited unlimited bytes
|
Max address space unlimited unlimited bytes
|
Max file locks unlimited unlimited locks
|
Max pending signals 514974 514974 signals
|
Max msgqueue size 819200 819200 bytes
|
Max nice priority 0 0
|
Max realtime priority 0 0
|
Max realtime timeout unlimited unlimited us
|
Core pattern: core
|
|
|
|
Could you please add the output of SHOW CREATE TABLE postings, cases_invoices, invoices;
Is the crash reproducible for you if you run this query?
|
|
Hi Alice,
The query gets run thousands of times throughout any given month. I cannot reproduce it, unfortunately.
Below are the table definitions.
MariaDB [ddx_lab_801317]> SHOW CREATE TABLE postings\G
|
*************************** 1. row ***************************
|
Table: postings
|
Create Table: CREATE TABLE `postings` (
|
`id` int(10) NOT NULL AUTO_INCREMENT,
|
`parent_posting_id` int(10) DEFAULT NULL,
|
`practice_id` mediumint(8) unsigned NOT NULL,
|
`amount` decimal(10,2) NOT NULL,
|
`type` enum('PAYMENT','DEBIT','CREDIT','FINANCE_CHARGE','START_BALANCE') DEFAULT NULL,
|
`statement_date` date DEFAULT NULL,
|
`payment_method_id` smallint(5) DEFAULT NULL,
|
`ref_type` enum('INVOICE','CASE_INVOICE') DEFAULT NULL,
|
`ref_id` int(8) unsigned DEFAULT NULL,
|
`post_date` datetime NOT NULL,
|
`reference_no` varchar(32) DEFAULT NULL,
|
`memo` varchar(1000) DEFAULT NULL,
|
`user_id` int(10) NOT NULL,
|
`moneris_transaction` tinyint(1) NOT NULL DEFAULT 0,
|
`type_ref_id` smallint(5) DEFAULT NULL,
|
`type_ref_desc` varchar(512) DEFAULT NULL,
|
`add_date` timestamp NOT NULL DEFAULT current_timestamp(),
|
`update_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
PRIMARY KEY (`id`),
|
KEY `practices` (`practice_id`),
|
KEY `refs` (`ref_type`,`ref_id`,`id`),
|
KEY `parent_posting_id` (`parent_posting_id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=240114 DEFAULT CHARSET=utf8
|
1 row in set (0.000 sec)
|
|
MariaDB [ddx_lab_801317]> SHOW CREATE TABLE cases_invoices\G
|
*************************** 1. row ***************************
|
Table: cases_invoices
|
Create Table: CREATE TABLE `cases_invoices` (
|
`id` int(8) unsigned zerofill NOT NULL AUTO_INCREMENT,
|
`case_id` int(10) unsigned NOT NULL,
|
`statement_date` date DEFAULT NULL,
|
`practice_id` mediumint(8) unsigned DEFAULT NULL,
|
`invoice_date` date NOT NULL,
|
`taxes` decimal(6,2) NOT NULL DEFAULT 0.00,
|
`total` decimal(10,2) NOT NULL DEFAULT 0.00,
|
`line_items` enum('WORKCODES','PROCEDURES') NOT NULL DEFAULT 'PROCEDURES'
|
`note` varchar(1200) DEFAULT NULL,
|
`payment` tinyint(1) NOT NULL DEFAULT 0,
|
`payment_id` varchar(64) DEFAULT NULL,
|
`remaining` decimal(10,2) DEFAULT 0.00,
|
PRIMARY KEY (`id`,`case_id`),
|
KEY `IDX_cases_invoices_1` (`case_id`),
|
KEY `IDX_cases_invoices_2` (`statement_date`,`practice_id`),
|
CONSTRAINT `cases_cases_invoices` FOREIGN KEY (`case_id`) REFERENCES `cases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
CONSTRAINT `statements_cases_invoices` FOREIGN KEY (`statement_date`, `practice_id`) REFERENCES `statements` (`statement_date`, `practice_id`) ON DELETE SET NULL ON UPDATE CASCADE
|
) ENGINE=InnoDB AUTO_INCREMENT=123734 DEFAULT CHARSET=utf8
|
1 row in set (0.000 sec)
|
|
MariaDB [ddx_lab_801317]> SHOW CREATE TABLE invoices\G
|
*************************** 1. row ***************************
|
Table: invoices
|
Create Table: CREATE TABLE `invoices` (
|
`id` int(8) unsigned zerofill NOT NULL AUTO_INCREMENT,
|
`statement_date` date DEFAULT NULL,
|
`practice_id` mediumint(8) unsigned DEFAULT NULL,
|
`invoice_date` date NOT NULL,
|
`taxes` decimal(6,2) NOT NULL DEFAULT 0.00,
|
`total` decimal(10,2) NOT NULL DEFAULT 0.00,
|
`note` varchar(1200) DEFAULT NULL,
|
`remaining` decimal(10,2) DEFAULT 0.00,
|
`add_date` timestamp NOT NULL DEFAULT current_timestamp(),
|
`update_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
PRIMARY KEY (`id`),
|
KEY `IDX_invoices_1` (`statement_date`,`practice_id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=114188 DEFAULT CHARSET=utf8
|
1 row in set (0.003 sec)
|
|
|
|
For what it is worth, this crash is starting to appear with greater frequency. Here are the logs for today.
Please note: each one of these crashes has been for a different database. Each database has the same table schemas. The data itself would vary by database.
211104 11:01:37 [ERROR] mysqld got signal 11 ;
|
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.
|
|
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
|
|
We will try our best to scrape up some info that will hopefully help
|
diagnose the problem, but since we have already crashed,
|
something is definitely wrong and this may fail.
|
|
Server version: 10.3.31-MariaDB-1:10.3.31+maria~focal-log
|
key_buffer_size=134217728
|
read_buffer_size=131072
|
max_used_connections=661
|
max_threads=752
|
thread_count=121
|
It is possible that mysqld could use up to
|
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1784362 K bytes of memory
|
Hope that's ok; if not, decrease some variables in the equation.
|
|
Thread pointer: 0x7f556c0008d8
|
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 = 0x7f584f942dd8 thread_stack 0x49000
|
/usr/sbin/mysqld(my_print_stacktrace+0x32)[0x5588e28843a2]
|
/usr/sbin/mysqld(handle_fatal_signal+0x55d)[0x5588e235be8d]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x153c0)[0x7f695c1c63c0]
|
/usr/sbin/mysqld(+0x978f4f)[0x5588e24f0f4f]
|
/usr/sbin/mysqld(_ZN7handler10ha_rnd_posEPhS0_+0x50)[0x5588e23616d0]
|
/usr/sbin/mysqld(_Z16rr_from_pointersP11READ_RECORD+0x3e)[0x5588e2491b2e]
|
/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x1db)[0x5588e219ba0b]
|
/usr/sbin/mysqld(_ZN4JOIN10exec_innerEv+0xbee)[0x5588e21c5dce]
|
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x37)[0x5588e21c6137]
|
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xf2)[0x5588e21c6282]
|
/usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x14b)[0x5588e21c6bcb]
|
/usr/sbin/mysqld(+0x5de9f1)[0x5588e21569f1]
|
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x5414)[0x5588e21646e4]
|
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x1f3)[0x5588e2167253]
|
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x105d)[0x5588e216962d]
|
/usr/sbin/mysqld(_Z10do_commandP3THD+0x12d)[0x5588e216b5dd]
|
/usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x246)[0x5588e2254186]
|
/usr/sbin/mysqld(handle_one_connection+0x3f)[0x5588e225435f]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x9609)[0x7f695c1ba609]
|
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f695c0df293]
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7f556d62ca00): SELECT * FROM ((
|
#
|
# get postings from last statement and postings not on statements
|
#
|
SELECT a.*, COALESCE(ci.id, i.id) as invoice_id
|
FROM ddx_lab_804273.postings a
|
LEFT JOIN ddx_lab_804273.cases_invoices ci
|
ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id
|
LEFT JOIN ddx_lab_804273.invoices i
|
ON a.ref_type = 'INVOICE' AND a.ref_id = i.id
|
WHERE (
|
a.statement_date IS NULL
|
OR a.statement_date = '2021-10-29'
|
)
|
AND a.practice_id='52481'
|
ORDER BY post_date
|
) UNION (
|
#
|
# get postings that have remaining balances
|
#
|
SELECT DISTINCT a.*, COALESCE(ci.id, i.id) as invoice_id
|
FROM ddx_lab_804273.postings a
|
LEFT JOIN ddx_lab_804273.cases_invoices ci
|
ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id
|
LEFT JOIN ddx_lab_804273.invoices i
|
ON a.ref_type = 'INVOICE' AND a.ref_id = i.id
|
LEFT JOIN ddx_lab_804273.postings b
|
ON a.ref_type = b.ref_type
|
AND a.ref_id = b.ref_id
|
AND a.id != b.id
|
|
WHERE NOT (a.type = 'CREDIT' OR a.type = 'PAYMENT')
|
AND ((SELECT SUM(b.amount)) IS NULL OR (SELECT SUM(a.amount)) + (SELECT SUM(b.amount)) > 0)
|
AND a.practice_id='52481'
|
GROUP BY a.id
|
ORDER BY post_date
|
) UNION (
|
#
|
# get partial payments and credits from older statements
|
#
|
SELECT a.*, COALESCE(ci.id, i.id) as invoice_id
|
FROM ddx_lab_804273.postings a
|
LEFT JOIN ddx_lab_804273.cases_invoices ci
|
ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id
|
LEFT JOIN ddx_lab_804273.invoices i
|
ON a.ref_type = 'INVOICE' AND a.ref_id = i.id
|
WHERE (
|
a.statement_date < '2021-10-29'
|
)
|
AND (a.type='PAYMENT' OR a.type='CREDIT')
|
AND a.practice_id='52481' )) AS t1 ORDER BY post_date ASC, invoice_id ASC
|
|
Connection ID (thread ID): 18870080
|
Status: NOT_KILLED
|
|
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,derived_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=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
|
|
The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
|
information that should help you find out what is causing the crash.
|
Writing a core file...
|
Working directory at /var/lib/mysql
|
Resource Limits:
|
Limit Soft Limit Hard Limit Units
|
Max cpu time unlimited unlimited seconds
|
Max file size unlimited unlimited bytes
|
Max data size unlimited unlimited bytes
|
Max stack size 8388608 unlimited bytes
|
Max core file size 0 unlimited bytes
|
Max resident set unlimited unlimited bytes
|
Max processes unlimited unlimited processes
|
Max open files 1048576 1048576 files
|
Max locked memory unlimited unlimited bytes
|
Max address space unlimited unlimited bytes
|
Max file locks unlimited unlimited locks
|
Max pending signals 514974 514974 signals
|
Max msgqueue size 819200 819200 bytes
|
Max nice priority 0 0
|
Max realtime priority 0 0
|
Max realtime timeout unlimited unlimited us
|
Core pattern: core
|
|
2021-11-04 11:02:18 0 [Note] InnoDB: Using Linux native AIO
|
2021-11-04 11:02:18 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
|
2021-11-04 11:02:18 0 [Note] InnoDB: Uses event mutexes
|
2021-11-04 11:02:18 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
|
2021-11-04 11:02:18 0 [Note] InnoDB: Number of pools: 1
|
2021-11-04 11:02:18 0 [Note] InnoDB: Using SSE2 crc32 instructions
|
2021-11-04 11:02:18 0 [Note] InnoDB: Initializing buffer pool, total size = 65G, instances = 8, chunk size = 128M
|
2021-11-04 11:02:21 0 [Note] InnoDB: Completed initialization of buffer pool
|
2021-11-04 11:02:21 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
|
2021-11-04 11:02:21 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=11110976136220
|
2021-11-04 11:02:25 0 [Note] InnoDB: Starting final batch to recover 14222 pages from redo log.
|
2021-11-04 11:02:27 0 [Note] InnoDB: Last binlog file '/var/lib/mysql/mysql-bin.014176', position 702472003
|
2021-11-04 11:03:28 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
|
2021-11-04 11:03:28 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
|
2021-11-04 11:03:28 0 [Note] InnoDB: Creating shared tablespace for temporary tables
|
2021-11-04 11:03:28 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
|
2021-11-04 11:03:28 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
|
2021-11-04 11:03:28 0 [Note] InnoDB: Waiting for purge to start
|
2021-11-04 11:03:28 0 [Note] InnoDB: 10.3.31 started; log sequence number 11110976909802; transaction id 13737671287
|
2021-11-04 11:03:28 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
|
2021-11-04 11:03:28 0 [Note] Plugin 'FEEDBACK' is disabled.
|
2021-11-04 11:03:28 0 [Note] Recovering after a crash using /var/lib/mysql/mysql-bin
|
2021-11-04 11:03:30 0 [Note] Starting crash recovery...
|
2021-11-04 11:03:30 0 [Note] Crash recovery finished.
|
2021-11-04 11:03:30 0 [Note] Server socket created on IP: '::'.
|
2021-11-04 11:03:30 0 [Warning] 'user' entry 'root@usaddxprdsqlpm1' ignored in --skip-name-resolve mode.
|
2021-11-04 11:03:30 0 [Warning] 'proxies_priv' entry '@% root@usaddxprdsqlpm1' ignored in --skip-name-resolve mode.
|
2021-11-04 11:03:30 0 [Note] Reading of all Master_info entries succeeded
|
2021-11-04 11:03:30 0 [Note] Added new Master_info '' to hash table
|
2021-11-04 11:03:30 0 [Note] /usr/sbin/mysqld: ready for connections.
|
Version: '10.3.31-MariaDB-1:10.3.31+maria~focal-log' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution
|
|
|
|
Thank you! I repeated the bug on 10.3-10.6
CREATE TABLE t1 ( id int, d1 datetime) ;
|
insert into t1 values (1,'2021-11-05 11:35:55'), (2,'2021-11-05 11:35:55'), (3,'2021-11-05 11:35:55');
|
|
SELECT * FROM ((SELECT d1 FROM t1 WHERE (SELECT sum(id) >0) GROUP BY id) ) dt ORDER BY d1;
|
|
|
10.3 1203b65849cd1ecefe5f2 -DWITH_ASAN=ON
|
Version: '10.3.32-MariaDB-debug-log'
|
=================================================================
|
==690319==ERROR: AddressSanitizer: heap-use-after-free on address 0x633000054878 at pc 0x559daf8874f8 bp 0x7f924cbd9f00 sp 0x7f924cbd9ef0
|
READ of size 1 at 0x633000054878 thread T27
|
#0 0x559daf8874f7 in heap_rrnd /10.3/src/storage/heap/hp_rrnd.c:40
|
#1 0x559daf871d7f in ha_heap::rnd_pos(unsigned char*, unsigned char*) /10.3/src/storage/heap/ha_heap.cc:353
|
#2 0x559daf33a88c in handler::ha_rnd_pos(unsigned char*, unsigned char*) /10.3/src/sql/handler.cc:2881
|
#3 0x559daf7355c5 in rr_from_pointers(READ_RECORD*) /10.3/src/sql/records.cc:551
|
#4 0x559daea21367 in READ_RECORD::read_record() /10.3/src/sql/records.h:70
|
#5 0x559daecee7f2 in join_init_read_record(st_join_table*) /10.3/src/sql/sql_select.cc:20789
|
#6 0x559daece79c1 in sub_select(JOIN*, st_join_table*, bool) /10.3/src/sql/sql_select.cc:19843
|
#7 0x559daece5d15 in do_select /10.3/src/sql/sql_select.cc:19386
|
#8 0x559daec78f04 in JOIN::exec_inner() /10.3/src/sql/sql_select.cc:4142
|
#9 0x559daec76881 in JOIN::exec() /10.3/src/sql/sql_select.cc:3936
|
#10 0x559daec7a37f in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/src/sql/sql_select.cc:4345
|
#11 0x559daec50a0a in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/src/sql/sql_select.cc:372
|
#12 0x559daebc1ef4 in execute_sqlcom_select /10.3/src/sql/sql_parse.cc:6339
|
#13 0x559daebaff2f in mysql_execute_command(THD*) /10.3/src/sql/sql_parse.cc:3870
|
#14 0x559daebcbc51 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.3/src/sql/sql_parse.cc:7870
|
#15 0x559daeba2b2e in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.3/src/sql/sql_parse.cc:1852
|
#16 0x559daeb9f671 in do_command(THD*) /10.3/src/sql/sql_parse.cc:1398
|
#17 0x559daef6f8f2 in do_handle_one_connection(CONNECT*) /10.3/src/sql/sql_connect.cc:1403
|
#18 0x559daef6f1ac in handle_one_connection /10.3/src/sql/sql_connect.cc:1308
|
#19 0x559db059af88 in pfs_spawn_thread /10.3/src/storage/perfschema/pfs.cc:1869
|
#20 0x7f92633a9608 in start_thread /build/glibc-eX1tMB/glibc-2.31/nptl/pthread_create.c:477
|
#21 0x7f92632ce292 in __clone (/lib/x86_64-linux-gnu/libc.so.6+0x122292)
|
|
0x633000054878 is located 120 bytes inside of 104964-byte region [0x633000054800,0x63300006e204)
|
freed by thread T27 here:
|
#0 0x7f9263be97cf in __interceptor_free (/lib/x86_64-linux-gnu/libasan.so.5+0x10d7cf)
|
#1 0x559db06e7cc7 in free_memory /10.3/src/mysys/safemalloc.c:279
|
#2 0x559db06e7283 in sf_free /10.3/src/mysys/safemalloc.c:197
|
#3 0x559db06b55ba in my_free /10.3/src/mysys/my_malloc.c:223
|
#4 0x559daf88cd99 in hp_free_level /10.3/src/storage/heap/hp_block.c:151
|
#5 0x559daf88cf4b in hp_clear /10.3/src/storage/heap/hp_clear.c:35
|
#6 0x559daf88cdf9 in heap_clear /10.3/src/storage/heap/hp_clear.c:27
|
#7 0x559daf87259f in ha_heap::delete_all_rows() /10.3/src/storage/heap/ha_heap.cc:404
|
#8 0x559daf347723 in handler::ha_delete_all_rows() /10.3/src/sql/handler.cc:4439
|
#9 0x559daeaf133e in mysql_derived_fill(THD*, LEX*, TABLE_LIST*) /10.3/src/sql/sql_derived.cc:1140
|
#10 0x559daeaeb1c0 in mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int) /10.3/src/sql/sql_derived.cc:193
|
#11 0x559daecb8093 in st_join_table::preread_init() /10.3/src/sql/sql_select.cc:12894
|
#12 0x559daecee6b0 in join_init_read_record(st_join_table*) /10.3/src/sql/sql_select.cc:20784
|
#13 0x559daece79c1 in sub_select(JOIN*, st_join_table*, bool) /10.3/src/sql/sql_select.cc:19843
|
#14 0x559daece5d15 in do_select /10.3/src/sql/sql_select.cc:19386
|
#15 0x559daec78f04 in JOIN::exec_inner() /10.3/src/sql/sql_select.cc:4142
|
#16 0x559daec76881 in JOIN::exec() /10.3/src/sql/sql_select.cc:3936
|
#17 0x559daec7a37f in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/src/sql/sql_select.cc:4345
|
#18 0x559daec50a0a in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/src/sql/sql_select.cc:372
|
#19 0x559daebc1ef4 in execute_sqlcom_select /10.3/src/sql/sql_parse.cc:6339
|
#20 0x559daebaff2f in mysql_execute_command(THD*) /10.3/src/sql/sql_parse.cc:3870
|
#21 0x559daebcbc51 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.3/src/sql/sql_parse.cc:7870
|
#22 0x559daeba2b2e in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.3/src/sql/sql_parse.cc:1852
|
#23 0x559daeb9f671 in do_command(THD*) /10.3/src/sql/sql_parse.cc:1398
|
#24 0x559daef6f8f2 in do_handle_one_connection(CONNECT*) /10.3/src/sql/sql_connect.cc:1403
|
#25 0x559daef6f1ac in handle_one_connection /10.3/src/sql/sql_connect.cc:1308
|
#26 0x559db059af88 in pfs_spawn_thread /10.3/src/storage/perfschema/pfs.cc:1869
|
#27 0x7f92633a9608 in start_thread /build/glibc-eX1tMB/glibc-2.31/nptl/pthread_create.c:477
|
|
previously allocated by thread T27 here:
|
#0 0x7f9263be9bc8 in malloc (/lib/x86_64-linux-gnu/libasan.so.5+0x10dbc8)
|
#1 0x559db06e6c37 in sf_malloc /10.3/src/mysys/safemalloc.c:118
|
#2 0x559db06b4ac3 in my_malloc /10.3/src/mysys/my_malloc.c:101
|
#3 0x559daf88c6e7 in hp_get_new_block /10.3/src/storage/heap/hp_block.c:81
|
#4 0x559daf88a944 in next_free_record_pos /10.3/src/storage/heap/hp_write.c:163
|
#5 0x559daf889434 in heap_write /10.3/src/storage/heap/hp_write.c:45
|
#6 0x559daf870b02 in ha_heap::write_row(unsigned char*) /10.3/src/storage/heap/ha_heap.cc:235
|
#7 0x559daed30ec8 in handler::ha_write_tmp_row(unsigned char*) /10.3/src/sql/sql_class.h:6490
|
#8 0x559daee411ba in select_unit::send_data(List<Item>&) /10.3/src/sql/sql_union.cc:150
|
#9 0x559daecf0e0d in end_send /10.3/src/sql/sql_select.cc:21033
|
#10 0x559daece9265 in evaluate_join_record /10.3/src/sql/sql_select.cc:20073
|
#11 0x559daed2682b in AGGR_OP::end_send() /10.3/src/sql/sql_select.cc:28023
|
#12 0x559daece6a47 in sub_select_postjoin_aggr(JOIN*, st_join_table*, bool) /10.3/src/sql/sql_select.cc:19562
|
#13 0x559daece73fa in sub_select(JOIN*, st_join_table*, bool) /10.3/src/sql/sql_select.cc:19797
|
#14 0x559daece5e1b in do_select /10.3/src/sql/sql_select.cc:19388
|
#15 0x559daec78f04 in JOIN::exec_inner() /10.3/src/sql/sql_select.cc:4142
|
#16 0x559daec76881 in JOIN::exec() /10.3/src/sql/sql_select.cc:3936
|
#17 0x559daec7a37f in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/src/sql/sql_select.cc:4345
|
#18 0x559daeaf19eb in mysql_derived_fill(THD*, LEX*, TABLE_LIST*) /10.3/src/sql/sql_derived.cc:1179
|
#19 0x559daeaeb1c0 in mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int) /10.3/src/sql/sql_derived.cc:193
|
#20 0x559daecb8093 in st_join_table::preread_init() /10.3/src/sql/sql_select.cc:12894
|
#21 0x559daecff98b in create_sort_index(THD*, JOIN*, st_join_table*, Filesort*) /10.3/src/sql/sql_select.cc:23039
|
#22 0x559daeceede8 in st_join_table::sort_table() /10.3/src/sql/sql_select.cc:20828
|
#23 0x559daecee2ea in join_init_read_record(st_join_table*) /10.3/src/sql/sql_select.cc:20769
|
#24 0x559daece79c1 in sub_select(JOIN*, st_join_table*, bool) /10.3/src/sql/sql_select.cc:19843
|
#25 0x559daece5d15 in do_select /10.3/src/sql/sql_select.cc:19386
|
#26 0x559daec78f04 in JOIN::exec_inner() /10.3/src/sql/sql_select.cc:4142
|
#27 0x559daec76881 in JOIN::exec() /10.3/src/sql/sql_select.cc:3936
|
#28 0x559daec7a37f in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/src/sql/sql_select.cc:4345
|
#29 0x559daec50a0a in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/src/sql/sql_select.cc:372
|
|
Thread T27 created by T0 here:
|
#0 0x7f9263b16805 in pthread_create (/lib/x86_64-linux-gnu/libasan.so.5+0x3a805)
|
#1 0x559db059b379 in spawn_thread_v1 /10.3/src/storage/perfschema/pfs.cc:1919
|
#2 0x559dae8c818e in inline_mysql_thread_create /10.3/src/include/mysql/psi/mysql_thread.h:1275
|
#3 0x559dae8e0f4b in create_thread_to_handle_connection(CONNECT*) /10.3/src/sql/mysqld.cc:6664
|
#4 0x559dae8e16e6 in create_new_thread /10.3/src/sql/mysqld.cc:6734
|
#5 0x559dae8e2878 in handle_connections_sockets() /10.3/src/sql/mysqld.cc:6992
|
#6 0x559dae8e023c in mysqld_main(int, char**) /10.3/src/sql/mysqld.cc:6286
|
#7 0x559dae8c698c in main /10.3/src/sql/main.cc:25
|
#8 0x7f92631d30b2 in __libc_start_main (/lib/x86_64-linux-gnu/libc.so.6+0x270b2)
|
|
SUMMARY: AddressSanitizer: heap-use-after-free /10.3/src/storage/heap/hp_rrnd.c:40 in heap_rrnd
|
Shadow bytes around the buggy address:
|
0x0c66800028b0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
|
0x0c66800028c0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
|
0x0c66800028d0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
|
0x0c66800028e0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
|
0x0c66800028f0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
|
=>0x0c6680002900: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd[fd]
|
0x0c6680002910: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
|
0x0c6680002920: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
|
0x0c6680002930: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
|
0x0c6680002940: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
|
0x0c6680002950: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
|
Shadow byte legend (one shadow byte represents 8 application bytes):
|
Addressable: 00
|
Partially addressable: 01 02 03 04 05 06 07
|
Heap left redzone: fa
|
Freed heap region: fd
|
Stack left redzone: f1
|
Stack mid redzone: f2
|
Stack right redzone: f3
|
Stack after return: f5
|
Stack use after scope: f8
|
Global redzone: f9
|
Global init order: f6
|
Poisoned by user: f7
|
Container overflow: fc
|
Array cookie: ac
|
Intra object redzone: bb
|
ASan internal: fe
|
Left alloca redzone: ca
|
Right alloca redzone: cb
|
Shadow gap: cc
|
==690319==ABORTING
|
----------SERVER LOG END-------------
|
|
|
|
That is some very impressive reduction of the scenario to the root variables. This is huge!
|
|
Just putting this out there. Since upgrading to 10.3 from 10.2 we have been experiencing another issue related to this query. We have seen related processes being fed (what we believe) is an incomplete result set to operate on. Put another way, the query on occasion seemingly does not return all the rows that it should have found in the DB, and a re-run of the query would return more (all rows).
That is a hypothesis that we have been considering. Does it have any related merit to the bug initially described here? Love to be able to cross off this hypothesis.
Thanks,
Mike
|
|
@alice we modified our query so that we thought we were sidestepping the issue. But, we still are crashing:
We modified:
AND ((SELECT SUM(b.amount)) IS NULL OR (SELECT SUM(a.amount)) + (SELECT SUM(b.amount)) > 0)
|
To:
AND ((SELECT SUM(b.amount)) IS NULL OR (ci.remaining > 0 OR i.remaining > 0))
|
Are we looking in the right spot?
Here is the full error log:
211201 13:44:04 [ERROR] mysqld got signal 11 ;
|
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.
|
|
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
|
|
We will try our best to scrape up some info that will hopefully help
|
diagnose the problem, but since we have already crashed,
|
something is definitely wrong and this may fail.
|
|
Server version: 10.3.31-MariaDB-1:10.3.31+maria~focal-log
|
key_buffer_size=134217728
|
read_buffer_size=131072
|
max_used_connections=413
|
max_threads=752
|
thread_count=94
|
It is possible that mysqld could use up to
|
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1784362 K bytes of memory
|
Hope that's ok; if not, decrease some variables in the equation.
|
|
Thread pointer: 0x7fd241058708
|
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 = 0x7fd64c089dd8 thread_stack 0x49000
|
/usr/sbin/mysqld(my_print_stacktrace+0x32)[0x56340b2e33a2]
|
/usr/sbin/mysqld(handle_fatal_signal+0x55d)[0x56340adbae8d]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x153c0)[0x7fe76482e3c0]
|
/usr/sbin/mysqld(+0x978f4f)[0x56340af4ff4f]
|
/usr/sbin/mysqld(_ZN7handler10ha_rnd_posEPhS0_+0x50)[0x56340adc06d0]
|
/usr/sbin/mysqld(_Z16rr_from_pointersP11READ_RECORD+0x3e)[0x56340aef0b2e]
|
/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x1db)[0x56340abfaa0b]
|
/usr/sbin/mysqld(_ZN4JOIN10exec_innerEv+0xbee)[0x56340ac24dce]
|
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x37)[0x56340ac25137]
|
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xf2)[0x56340ac25282]
|
/usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x14b)[0x56340ac25bcb]
|
/usr/sbin/mysqld(+0x5de9f1)[0x56340abb59f1]
|
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x5414)[0x56340abc36e4]
|
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x1f3)[0x56340abc6253]
|
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x105d)[0x56340abc862d]
|
/usr/sbin/mysqld(_Z10do_commandP3THD+0x12d)[0x56340abca5dd]
|
/usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x246)[0x56340acb3186]
|
/usr/sbin/mysqld(handle_one_connection+0x3f)[0x56340acb335f]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x9609)[0x7fe764822609]
|
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7fe764747293]
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7fd24003b6a0): SELECT * FROM ((
|
#
|
# get postings from last statement and postings not on statements
|
#
|
SELECT a.*, COALESCE(ci.id, i.id) as invoice_id
|
FROM ddx_lab_801317.postings a
|
LEFT JOIN ddx_lab_801317.cases_invoices ci
|
ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id
|
LEFT JOIN ddx_lab_801317.invoices i
|
ON a.ref_type = 'INVOICE' AND a.ref_id = i.id
|
WHERE (
|
a.statement_date IS NULL
|
OR a.statement_date = '2021-11-26'
|
)
|
AND a.practice_id='31570'
|
ORDER BY post_date
|
) UNION (
|
#
|
# get postings that have remaining balances
|
#
|
SELECT DISTINCT a.*, COALESCE(ci.id, i.id) as invoice_id
|
FROM ddx_lab_801317.postings a
|
LEFT JOIN ddx_lab_801317.cases_invoices ci
|
ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id
|
LEFT JOIN ddx_lab_801317.invoices i
|
ON a.ref_type = 'INVOICE' AND a.ref_id = i.id
|
LEFT JOIN ddx_lab_801317.postings b
|
ON a.ref_type = b.ref_type
|
AND a.ref_id = b.ref_id
|
AND a.id != b.id
|
|
WHERE NOT (a.type = 'CREDIT' OR a.type = 'PAYMENT')
|
AND (
|
(SELECT SUM(b.amount)) IS NULL
|
OR (ci.remaining > 0 OR i.remaining > 0)
|
)
|
AND a.practice_id='31570'
|
GROUP BY a.id
|
ORDER BY post_date
|
) UNION (
|
#
|
# get partial payments and credits from older statements
|
#
|
SELECT a.*, COALESCE(ci.id, i.id) as invoice_id
|
FROM ddx_lab_801317.postings a
|
LEFT JOIN ddx_lab_801317.cases_invoices ci
|
ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id
|
LEFT JOIN ddx_lab_801317.invoices i
|
ON a.ref_type = 'INVOICE' AND a.ref_id = i.id
|
WHERE (
|
a.statement_date < '2021-11-26'
|
)
|
AND (a.type='PAYMENT' OR a.type='CREDIT')
|
AND a.practice_id='31570' )) AS t1 ORDER BY post_date ASC, invoice_id ASC
|
|
Connection ID (thread ID): 5964345
|
Status: NOT_KILLED
|
|
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,derived_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=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
|
|
The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
|
information that should help you find out what is causing the crash.
|
Writing a core file...
|
Working directory at /var/lib/mysql
|
Resource Limits:
|
Limit Soft Limit Hard Limit Units
|
Max cpu time unlimited unlimited seconds
|
Max file size unlimited unlimited bytes
|
Max data size unlimited unlimited bytes
|
Max stack size 8388608 unlimited bytes
|
Max core file size 0 unlimited bytes
|
Max resident set unlimited unlimited bytes
|
Max processes unlimited unlimited processes
|
Max open files 1048576 1048576 files
|
Max locked memory unlimited unlimited bytes
|
Max address space unlimited unlimited bytes
|
Max file locks unlimited unlimited locks
|
Max pending signals 514974 514974 signals
|
Max msgqueue size 819200 819200 bytes
|
Max nice priority 0 0
|
Max realtime priority 0 0
|
Max realtime timeout unlimited unlimited us
|
Core pattern: core
|
|
|
|
|
Hi!
I had a similar crash yesterday:
Ubuntu 20.04.3 LTS
mysqld Ver 10.5.13-MariaDB-1:10.5.13+maria~focal-log for debian-linux-gnu on x86_64 (mariadb.org binary distribution)
/usr/sbin/mariadbd(my_print_stacktrace+0x32)[0x558a8f555e42]
|
/usr/sbin/mariadbd(handle_fatal_signal+0x485)[0x558a8efa59a5]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x153c0)[0x7fc3e4ced3c0]
|
/lib/x86_64-linux-gnu/libstdc++.so.6(_ZTVN10__cxxabiv120__si_class_type_infoE+0x10)[0x7fc3e4cc3c98]
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7faa100f1d40): SELECT
|
p_number
|
FROM
|
m_fv
|
WHERE TRUE
|
AND p_identifier IN
|
("8032111178729", "8032111197134" ....)
|
|
AND gb_st <> "HC"
|
|
GROUP BY p_number
|
|
LIMIT ?
|
|
OFFSET ?
|
|
Connection ID (thread ID): 516251
|
Status: NOT_KILLED
|
|
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,derived_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_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
|
|
The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
|
information that should help you find out what is causing the crash.
|
Writing a core file...
|
Working directory at /var/lib/mysql
|
Resource Limits:
|
Limit Soft Limit Hard Limit Units
|
Max cpu time unlimited unlimited seconds
|
Max file size unlimited unlimited bytes
|
Max data size unlimited unlimited bytes
|
Max stack size 8388608 unlimited bytes
|
Max core file size 0 unlimited bytes
|
Max resident set unlimited unlimited bytes
|
Max processes 483038 483038 processes
|
Max open files 32768 32768 files
|
Max locked memory 65536 65536 bytes
|
Max address space unlimited unlimited bytes
|
Max file locks unlimited unlimited locks
|
Max pending signals 483038 483038 signals
|
Max msgqueue size 819200 819200 bytes
|
Max nice priority 0 0
|
Max realtime priority 0 0
|
Max realtime timeout unlimited unlimited us
|
Core pattern: |/usr/share/apport/apport %p %s %c %d %P %E
|
|
|
Still seeing this error in our production environment. No apparent rhyme or reason when the query fails. Works 99% of the time.
Tried refactoring as above. No luck. Looking for a work around. Any suggestions?
|
|
zfodor@tarhely.eu Your backtrace and query structure seems pretty different then what I am seeing. I wounder if there is any commonality, or if you have hit a different error
|
|
michaelcaplan, it's not even a workaround, it'll be a correct fix.
You cannot do (this is very truncated part of your query)
SELECT *
|
FROM a
|
WHERE (SELECT SUM(a.amount)) > 0)
|
GROUP BY a.id
|
Note, that if you do
SELECT *
|
FROM a
|
WHERE SUM(a.amount) > 0
|
GROUP BY a.id
|
it'll be an error straight away. By wrapping SUM() into a subquery you trick MariaDB into accepting an invalid query. It is invalid according to the SQL standard and our fix for this bug will be do disallow the first invalid syntax too, not only the second.
You have to put conditions with aggregate functions into HAVING. It's not a workaround for the crash, it's a proper fix. Do something like
SELECT *
|
FROM a
|
GROUP BY a.id
|
HAVING SUM(a.amount) > 0
|
(or with a subquery, if you'd like, even though it doesn't add any value here)
|
|
@serg this is highly helpful! We are refactoring
Thanks!
|
|
Let's consider the following test case:
create table t1 (a int, b int) engine=myisam;
|
insert into t1 values (1,10), (3,30), (2,20), (3,31), (1,11);
|
create table t2 (c int) engine=myisam;
|
insert into t2 values (3), (1);
|
|
explain
|
select * from
|
((select b from t1 where (select sum(a) from t2) > 2 group by a) ) dt
|
order by b;
|
|
select * from
|
((select b from t1 where (select sum(a) from t2) > 2 group by a) ) dt
|
order by b;
|
EXPLAIN returns:
MariaDB [test]> explain
|
-> select * from
|
-> ((select b from t1 where (select sum(a) from t2) > 2 group by a) ) dt
|
-> order by b;
|
+------+--------------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |
|
| 2 | LATERAL DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 5 | Using where; Using temporary; Using filesort |
|
| 3 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | |
|
+------+--------------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
|
This is already not good, because 'LATERAL DERIVED' may appear only when the split optimization is used. Yet it cannot be used because t1 does not have any indexes. Besides it used only for join queries.
If we execute the query using mysql client we get an unexpected result set:
MariaDB [test]> select * from
|
-> ((select b from t1 where (select sum(a) from t2) > 2 group by a) ) dt
|
-> order by b;
|
+------+
|
| b |
|
+------+
|
| NULL |
|
| NULL |
|
+------+
|
The same result set is returned if we use mysqltest when executing the test case.
If we execute the test case using mtr the returned result set is correct. Yet if mtr uses the VALGRIND build execution with --valgrind shows the same problems that were observed with ASAN.
|
|
It has to be noted that although the query does not conform to the the SQL Standard MariaDB can execute it aggregating sum(a) in the most inner select
considering 'a' above as an outer reference referring column t1.a.
|
|
An analysis with debugger shows that 'LATERAL DERIVED' appears in the EXPLAIN output because dt is erroneously marked as uncacheable. The mark is set as the result of the call:
thd->lex->current_select->mark_as_dependent(thd, aggr_sel, NULL)
|
in the function Item_sum::register_sum_fun(). Here NULL is passed as the second parameter and this does not make any sense.
|
|
If we change optimizer_switch setting 'split_materialized' to off;
we still have the same bizarre output for explain:
MariaDB [test]> set optimizer_switch='split_materialized=off';
|
|
MariaDB [test]> explain
|
-> select * from
|
-> ((select b from t1 where (select sum(a) from t2) > 2 group by a) ) dt
|
-> order by dt.b;
|
+------+--------------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |
|
| 2 | LATERAL DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 5 | Using where; Using temporary; Using filesort |
|
| 3 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | |
|
+------+--------------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
|
In fact in debugger we see that the above mentioned call of st_select_lex::mark_as_dependent()
changes the value of SELECT_LEX::uncacheable for the the specification of the derived table adding the flag UNCACHEABLE_DEPENDENT_GENERATED. As a result the function typst_select_lex::set_explain_type() prints "LATERAL DERIVED" as the type of the derived table dt.
|
|
no crash on current 10.4 11abc2191149a76702ef5d919e2e8940e47e5ef9 -11.2
|
variables.txt