|
I can not reproduce the crash. Could you please provide error log and your .cnf file(s)
MariaDB [test]> drop table if exists t;
|
Query OK, 0 rows affected, 1 warning (0.001 sec)
|
|
MariaDB [test]> create table t (i int, j int);
|
Query OK, 0 rows affected (0.050 sec)
|
|
MariaDB [test]> insert into t values (1, 1), (2, 1), (3, 2), (4, 2);
|
Query OK, 4 rows affected (0.006 sec)
|
Records: 4 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> select
|
-> i,
|
-> max(i) over (partition by (select 1))
|
-> from t
|
-> order by i asc;
|
+------+---------------------------------------+
|
| i | max(i) over (partition by (select 1)) |
|
+------+---------------------------------------+
|
| 1 | 4 |
|
| 2 | 4 |
|
| 3 | 4 |
|
| 4 | 4 |
|
+------+---------------------------------------+
|
4 rows in set (0.001 sec)
|
|
MariaDB [test]> select version();
|
+----------------+
|
| version() |
|
+----------------+
|
| 10.4.4-MariaDB |
|
+----------------+
|
1 row in set (0.000 sec)
|
|
|
|
Interesting, I couldn't reproduce it again when I re-created the schema. But then it happened again with this query:
select
|
i,
|
max(i) over (partition by 1),
|
max(i) over (partition by (select 1))
|
from t
|
order by i asc;
|
Attached my.cnf and the result of "docker logs MARIADB & > log.txt" log.txt
|
|
... I'll be very happy to provide any additional info that you may need
|
|
Notice, I'm connecting to MariaDB using DBeaver, a JDBC based SQL client, in case this is relevant.
|
|
With the second query I am getting the crash, but stacktrace is different and it is the same as MDEV-15837 (fixed in 10.4.5)
Server version: 10.4.4-MariaDB
|
|
sql/sql_window.cc:435(compare_order_elements)[0x5643cec9b21b]
|
sql/sql_window.cc:572(compare_window_funcs_by_window_specs(Item_window_func*, Item_window_func*, void*))[0x5643cec9b374]
|
sql/sql_list.h:595(bubble_sort<Item_window_func>)[0x5643cec9e862]
|
sql/sql_select.cc:3627(JOIN::make_aggr_tables_info())[0x5643ceb97b8a]
|
sql/sql_select.cc:2919(JOIN::optimize_stage2())[0x5643ceb9f6b0]
|
sql/sql_select.cc:2217(JOIN::optimize_inner())[0x5643ceba115a]
|
sql/sql_select.cc:1563(JOIN::optimize())[0x5643ceba32cd]
|
sql/sql_select.cc:4588(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*))[0x5643ceba3dd1]
|
sql/sql_select.cc:424(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5643ceba49d7]
|
sql/sql_parse.cc:6603(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5643cea3fe76]
|
sql/sql_parse.cc:3891(mysql_execute_command(THD*))[0x5643ceb4b4d0]
|
sql/sql_parse.cc:8154(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5643ceb50b42]
|
sql/sql_audit.h:168(mysql_audit_general)[0x5643ceb540c9]
|
sql/sql_parse.cc:1365(do_command(THD*))[0x5643ceb5555c]
|
sql/sql_connect.cc:1398(do_handle_one_connection(CONNECT*))[0x5643cec3267a]
|
sql/sql_connect.cc:1303(handle_one_connection)[0x5643cec327dd]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f1ffd6b26ba]
|
x86_64/clone.S:111(clone)[0x7f1ffcb5941d]
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7f1f8c014170): select i, max(i) over (partition by 1), max(i) over (partition by (select 1)) from t order by i asc LIMIT 0, 200
|
from log.txt
Server version: 10.4.4-MariaDB-1:10.4.4+maria~bionic
|
|
mysqld(+0x73bb1a)[0x56398f5ebb1a]
|
mysqld(_ZN24Window_funcs_computation5setupEP3THDP4ListI16Item_window_funcEP13st_join_table+0x1bd)[0x56398f5eed4d]
|
mysqld(_ZN4JOIN21make_aggr_tables_infoEv+0x15a3)[0x56398f4e5c33]
|
mysqld(_ZN4JOIN15optimize_stage2Ev+0x1b7d)[0x56398f4ed33d]
|
mysqld(_ZN4JOIN14optimize_innerEv+0xdf1)[0x56398f4eea01]
|
mysqld(_ZN4JOIN8optimizeEv+0x9b)[0x56398f4f12ab]
|
mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xe0)[0x56398f4f1df0]
|
mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x141)[0x56398f4f27f1]
|
mysqld(+0x5e04d1)[0x56398f4904d1]
|
mysqld(_Z21mysql_execute_commandP3THD+0x16b5)[0x56398f498805]
|
mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x22a)[0x56398f4a029a]
|
mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x166d)[0x56398f4a2a5d]
|
mysqld(_Z10do_commandP3THD+0x148)[0x56398f4a3ec8]
|
mysqld(_Z24do_handle_one_connectionP7CONNECT+0x2c2)[0x56398f57d612]
|
mysqld(handle_one_connection+0x3d)[0x56398f57d6dd]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7f420f25a6db]
|
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f420e65888f]
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7f41bc014a50): select i, max(i) over (partition by 1), max(i) over (partition by (select 1)) from t order by i asc LIMIT 0, 200
|
|
|
OK, thank you. Very cool if it's already fixed!
|
|
varun, is there anything still to be done in the scope of this item? Otherwise, please close.
|
|
This is easily repeatable with InnoDB table on 10.3.16 (or ColumnStore 1.2.5-1 ):
openxs@ao756:~$ mcsmysql -uroot test
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 11
|
Server version: 10.3.16-MariaDB-log Columnstore 1.2.5-1
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [test]> CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cid` int(11) DEFAULT NULL, `d` datetime DEFAULT NULL, `c` decimal(12,4) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
|
Query OK, 0 rows affected (0,243 sec)
|
|
MariaDB [test]> insert into t(cid, d, c) values (1, '2018-01-01', 20000.05);
|
Query OK, 1 row affected (0,056 sec)
|
|
MariaDB [test]> insert into t(cid, d, c) select round(rand()*100), date_sub(now(), interval rand()*100 week), round(rand()*100000,2) from t;
|
Query OK, 1 row affected (0,051 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> insert into t(cid, d, c) select round(rand()*100), date_sub(now(), interval rand()*100 week), round(rand()*100000,2) from t;
|
Query OK, 2 rows affected (0,040 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
...
|
|
\MariaDB [test]> insert into t(cid, d, c) select round(rand()*100), date_sub(now(), interval rand()*100 week), round(rand()*100000,2) from t;
|
Query OK, 32768 rows affected (0,794 sec)
|
Records: 32768 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> insert into t(cid, d, c) select round(rand()*100), date_sub(now(), interval rand()*100 week), round(rand()*100000,2) from t;
|
Query OK, 65536 rows affected (1,400 sec)
|
Records: 65536 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> select count(*) from t;
|
+----------+
|
| count(*) |
|
+----------+
|
| 131072 |
|
+----------+
|
1 row in set (0,070 sec)
|
|
MariaDB [test]> select distinct cid, week(d) as week, max(c) over (partition by cid, week(d)) as c_mx, sum(c) over (partition by cid, week(d)) as c_sum, count(c) over (partition by cid, week(d)) as c_cnt, max(week(d)) over (partition by year(d)) as w_max from t where year(d) = '2018';
|
ERROR 2013 (HY000): Lost connection to MySQL server during query
|
In the error log we see:
190810 13:58:35 [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.16-MariaDB-log
|
key_buffer_size=536870912
|
read_buffer_size=4194304
|
max_used_connections=1
|
max_threads=153
|
thread_count=8
|
It is possible that mysqld could use up to
|
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1781135 K bytes of memory
|
Hope that's ok; if not, decrease some variables in the equation.
|
|
Thread pointer: 0x7f93740010b8
|
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 = 0x7f93c086fe98 thread_stack 0x80000
|
/usr/local/mariadb/columnstore/mysql//bin/mysqld(my_print_stacktrace+0x29)[0x563a83082259]
|
/usr/local/mariadb/columnstore/mysql//bin/mysqld(handle_fatal_signal+0x307)[0x563a82bcbe57]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7f93fb01c390]
|
/usr/local/mariadb/columnstore/mysql//bin/mysqld(+0x6997e7)[0x563a82b2e7e7]
|
sql/sql_window.cc:436(compare_order_elements)[0x563a82b31d06]
|
sql/sql_window.cc:588(compare_window_funcs_by_window_specs)[0x563a82a4c5e2]
|
sql/sql_select.cc:3342(JOIN::make_aggr_tables_info())[0x563a82a548f4]
|
sql/sql_select.cc:2637(JOIN::optimize_stage2())[0x563a82a5606a]
|
sql/sql_select.cc:1533(JOIN::optimize_inner())[0x563a82a5632f]
|
sql/sql_select.cc:1455(JOIN::optimize())[0x563a82a5835a]
|
sql/sql_select.cc:4514(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*))[0x563a82a58517]
|
sql/sql_select.cc:386(handle_select(THD*, LEX*, select_result*, unsigned long))[0x563a82a8d674]
|
sql/sql_table.cc:11114(Sql_cmd_create_table_like::execute(THD*))[0x563a82a0243f]
|
sql/sql_parse.cc:6064(mysql_execute_command(THD*))[0x563a82a08469]
|
sql/sql_parse.cc:7871(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x563a82a086a2]
|
sql/sql_class.h:2885(THD::set_row_count_func(long long))[0x563a82a0a037]
|
sql/sql_parse.cc:10666(idb_vtable_process(THD*, unsigned long long, Statement*))[0x563a82a0d139]
|
sql/sql_parse.cc:1875(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x563a82a0e9d0]
|
sql/sql_parse.cc:1425(do_command(THD*))[0x563a82ad6054]
|
sql/sql_connect.cc:1402(do_handle_one_connection(CONNECT*))[0x563a82ad61d4]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f93fb0126ba]
|
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f93fa4a741d]
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7f93740136a8): is an invalid pointer
|
Connection ID (thread ID): 11
|
Status: NOT_KILLED
|
|
|
valerii I cannot reproduce it on 10.3.16
MariaDB [test]> select count(*) from t;
|
+----------+
|
| count(*) |
|
+----------+
|
| 131072 |
|
+----------+
|
1 row in set (1.751 sec)
|
|
MariaDB [test]> explain
|
-> select distinct cid, week(d) as week, max(c) over (partition by cid, week(d)) as c_mx, sum(c) over (partition by cid, week(d)) as c_sum, count(c) over (partition by cid, week(d)) as c_cnt, max(week(d)) over (partition by year(d)) as w_max from t where year(d) = '2018';
|
+------+-------------+-------+------+---------------+------+---------+------+--------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+--------+------------------------------+
|
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 131540 | Using where; Using temporary |
|
+------+-------------+-------+------+---------------+------+---------+------+--------+------------------------------+
|
1 row in set (0.004 sec)
|
|
|
|
I can easily reproduce it for InnoDB table on Columnstore 1.2.5 using MariaDB 10.3.16. However, it doesn't break on vanilla MariaDB 10.3.16. Bummer.
This breaks:
select avg(comm_total) over (partition by customer) as wk_comm_avg, max(week(trade_date)) over (partition by year(trade_date)) as max_week from book_entry_cs;
|
this does not break:
(remove the partition from first or function from partition of second)
select avg(comm_total) over () as wk_comm_avg, max(week(trade_date)) over (partition by year(trade_date)) as max_week from book_entry_cs;
|
--or--
|
select avg(comm_total) over (partition by customer) as wk_comm_avg, max(week(trade_date)) over (partition by trade_date) as max_week from book_entry_cs;
|
|
|
In debug mode, it asserts the following in compare_order_elements():
DBUG_ASSERT(item1->type() == Item::FIELD_ITEM &&
item2->type() == Item::FIELD_ITEM);
In actuality, item2->type() == item::FUNC_ITEM
Not sure how this gets this way.
|
|
In sql_select.cc optimize_stage2(), Columnstore has added:
// @InfiniDB We don't need tmp table for vtable create phase. Plus
|
// to build tmp table may corrupt some field table_name & db_name (for some reason)
|
if (thd->infinidb_vtable.vtable_state == THD::INFINIDB_CREATE_VTABLE)
|
need_tmp = false;
|
Removing this allows the query to run against InnoDB correcty. Investigating other things.
As far as I can tell, this is a Columnstore build only problem. MCOL-3434 has been opened to track it.
|
log.txt
my.cnf