[MDEV-21059] Running Query lost connection and then restart mariadb Created: 2019-11-15  Updated: 2019-11-19  Resolved: 2019-11-18

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

Type: Bug Priority: Critical
Reporter: Seungwon Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Environment:

Linux & RDS

Attachments: Text File error.log    
Issue Links:
Duplicate
duplicates MDEV-15178 Filesort::make_sortorder: Assertion `... Closed

 Description   

When I execute some queries ...
I got the error message /* SQL Error (2013): Lost connection to MySQL server during query */
and the my maria db restart / reboot the process..
but other queries fine...

<here is the query>

SELECT A.CONNECT_SYSTEM, B.USER_NO, B.USER_NAME, A.CONNECT_POSITION_NAME,
A.CONNECT_DEPT_CODE, A.CONNECT_DEPT_NAME, COUNT(DISTINCT(DATE_FORMAT(A.CONNECT_DATE, '%Y.%m.%d'))) AS DAY_CNT, COUNT AS CONNECT_CNT, ROW_NUMBER() OVER (PARTITION BY A.CONNECT_SYSTEM ORDER BY A.CONNECT_SYSTEM,COUNT DESC)
FROM rndp.TB_SPT_GCLOUD_CONNECT_LOG A
LEFT JOIN rndp.TB_SYS_USER B ON B.USER_NO = A.CONNECT_USER_NO AND A.CONNECT_USER_NO IS NOT NULL
WHERE 1=1 AND A.CONNECT_RESULT = '1' AND A.CONNECT_MSG = '[ ].'
GROUP BY A.CONNECT_SYSTEM, B.USER_NO, B.USER_NAME, A.CONNECT_POSITION_NAME,
A.CONNECT_DEPT_CODE, A.CONNECT_DEPT_NAME;

 Comments   
Comment by Seungwon [ 2019-11-15 ]

When i remove some function in this query. that is ROW_NUMBER() query part.
when i remove count part then, that query execute well..
what is the problem? and how can i trace that problem?

Comment by Alice Sherepa [ 2019-11-15 ]

Could you please upload error.log?

Comment by Seungwon [ 2019-11-16 ]

okay. i uploaded.

Comment by Alice Sherepa [ 2019-11-18 ]

from the error log:

Server version: 10.3.8-MariaDB-log
 
 
 
mysqld(_Z8filesortP3THDP5TABLEP8FilesortP16Filesort_trackerP4JOINy+0x584)[0x5603a06f0a44]
 
sql/filesort.cc:1954(sortlength)[0x5603a056a89e]
 
sql/sql_select.cc:22413(create_sort_index(THD*, JOIN*, st_join_table*, Filesort*))[0x5603a065832d]
 
sql/sql_window.cc:2938(Window_funcs_sort::exec(JOIN*))[0x5603a0658ba8]
 
sql/sql_window.cc:3063(Window_funcs_computation::exec(JOIN*))[0x5603a057a35a]
 
sql/sql_select.cc:27242(AGGR_OP::end_send())[0x5603a057a54d]
 
sql/sql_select.cc:18988(sub_select_postjoin_aggr(JOIN*, st_join_table*, bool))[0x5603a057ae7b]
 
sql/sql_select.cc:18813(do_select)[0x5603a057b1f9]
 
sql/sql_select.cc:3816(JOIN::exec())[0x5603a057b34a]
 
sql/sql_select.cc:4222(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*))[0x5603a04f2094]
 
sql/sql_derived.cc:1152(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x5603a04f1d01]
 
sql/sql_derived.cc:197(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x5603a055cf37]
 
sql/sql_select.cc:12448(st_join_table::preread_init())[0x5603a055d448]
 
sql/sql_select.cc:19239(sub_select(JOIN*, st_join_table*, bool))[0x5603a057ae4e]
 
sql/sql_select.cc:18812(do_select)[0x5603a057b1f9]
 
sql/sql_select.cc:3816(JOIN::exec())[0x5603a057b34a]
 
sql/sql_select.cc:4222(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*))[0x5603a057bce6]
 
sql/sql_select.cc:370(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5603a051edc8]
 
sql/sql_parse.cc:6649(execute_sqlcom_select)[0x5603a052c0f8]
 
sql/sql_parse.cc:3787(mysql_execute_command(THD*))[0x5603a052e6cc]
 
sql/sql_parse.cc:8236(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5603a0530fad]
 
sql/sql_parse.cc:1851(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5603a0531b6f]
 
sql/sql_parse.cc:1399(do_command(THD*))[0x5603a05faeec]
 
sql/sql_connect.cc:1445(do_handle_one_connection(CONNECT*))[0x5603a05fb0c4]
 
sql/sql_connect.cc:1353(handle_one_connection)[0x5603a0c116fa]
 
pthread_create.c:0(start_thread)[0x2b9e4e12bdc5]
 
/lib64/libc.so.6(clone+0x6d)[0x2b9e4e94ec9d]
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x2ba197e98da0): #              SELECT *    FROM (  SELECT A.CONNECT_SYSTEM       , B.USER_NO       , B.USER_NAME       , A.CONNECT_POSITION_NAME       , A.CONNECT_DEPT_CODE       , A.CONNECT_DEPT_NAME       , COUNT(DISTINCT(DATE_FORMAT(A.CONNECT_DATE, '%Y.%m.%d'))) AS DAY_CNT       , COUNT(*) AS CONNECT_CNT       , ROW_NUMBER() OVER (PARTITION BY A.CONNECT_SYSTEM ORDER BY A.CONNECT_SYSTEM , COUNT(*) DESC)    FROM TB_SPT_GCLOUD_CONNECT_LOG A    LEFT JOIN TB_SYS_USER B ON B.USER_NO = A.CONNECT_USER_NO AND A.CONNECT_USER_NO IS NOT NULL        WHERE 1=1     AND A.CONNECT_RESULT = '1'     AND A.CONNECT_MSG = '[      ]                 .'   GROUP BY A.CONNECT_SYSTEM, B.USER_NO, B.USER_NAME, A.CONNECT_POSITION_NAME, A.CONNECT_DEPT_CODE, A.CONNECT_DEPT_NAME  ) AS AA    LEFT JOIN (        WITH RECURSIVE CTE (DEPT_CODE, DEPT_NAME, PARENT_DEPT_CODE, PARENT_DEPT_NAME, DEPTH, SORT) AS              (SELECT RT.DEPT_CODE                    , RT.DEPT_NAME                    , RT.PARENT_DEPT_CODE                    , RT.PARENT_DEPT_NAME                    , RT.DEPTH                    , RT.DEPT_CODE AS SORT                 FROM TB_SYS_DEPT RT                WHERE RT.DEPT_CODE = '50000000'                  AND RT.USE_YN = 'Y'                  AND RT.IS_ERP_ORG = 'Y'              UNION ALL               SELECT RT.DEPT_CODE                    , RT.DEPT_NAME                    , RT.PARENT_DEPT_CODE                    , RT.PARENT_DEPT_NAME                    , RT.DEPTH                    , CE.DEPTH + '>' + RT.DEPTH SORT                 FROM TB_SYS_DEPT RT, CTE CE                WHERE RT.PARENT_DEPT_CODE = CE.DEPT_CODE                  AND RT.USE_YN = 'Y'                  AND RT.IS_ERP_ORG = 'Y'               )     SELECT TM.DEPT_CODE           ,TM.DEPT_NAME           ,TM.PARENT_DEPT_CODE           ,TM.PARENT_DEPT_NAME           ,DEPTH           ,SORT       FROM CTE TM      WHERE 1=1          #TM.DEPTH = '3'    ) AS BB ON BB.DEPT_CODE = AA.CONNECT_DEPT_CODE

Comment by Alice Sherepa [ 2019-11-18 ]

Thanks!
This is the same bug as MDEV-15178, so I close the current report. If you'd like to follow the progress, please, watch MDEV-15178

Comment by Seungwon [ 2019-11-18 ]

Thank you very much.
I watched that issue. but there is no action.
how can i escape that situation?

Comment by Seungwon [ 2019-11-19 ]

hi, this is Jang.
This is additional information.
@Alice Sherepa, when i execute that query with "ORDER BY a.connect_system, connect_cnt desc" end of that query.
That execute well..

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