[MDEV-11935] Queries in stored procedures with and EXISTS(SELECT * FROM VIEW) crashes and closes hte conneciton. Created: 2017-01-29  Updated: 2017-02-27  Resolved: 2017-02-27

Status: Closed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.0, 10.1, 10.1.21, 10.2
Fix Version/s: 10.0.30, 10.1.22, 10.2.5

Type: Bug Priority: Major
Reporter: Nicholas Denning Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 1
Labels: None
Environment:

Centos 7 x64

Attachments: File ip_fault_example_proc.sql     File mariadb_error_log.err     File testdb_20170211.sql    
Issue Links:
Blocks
blocks CONJ-348 Could not read resultset: unexpected ... Closed
Duplicate
duplicates MDEV-11993 Server Crash with VIEW in WHERE EXIST... Closed
Sprint: 10.0.30

 Description   

I am getting an error:

(conn:7) Could not read packet: unexpected end of stream, read 0 bytes from 4

I see a reference to a similar problem reported in https://jira.mariadb.org/browse/CONJ-348 for the connector.

this recommends the solution of adding ?useServerPrepStmts=false to the connection string.

Unfortunately I am making heavy use of stored procedures and stored procedures automatically have their prepared statemsnts buffered so the second time I use the procedure it crashes with the above error.

this error comes from a statement similar to:

SELECT *
FROM MY_TABLE MT
WHERE EXISTS ( SELECT *
FROM MYVIEW MV
WHERE MV.FIELD1 = MT.FIELD1 )
AND MT.FIELD2 = V_VALUE;

The view is quite complicated and essentially implements a security system I have been working on for some months so its a bit of blocker to rolling out a system.

the text in /CONJ-348 suggests this server error is to be fixed in 10.1.21 so I have upgraded to that but the issue still remains.

 Comments   
Comment by Elena Stepanova [ 2017-01-30 ]

nicholasdenning,

What exactly do you mean by "it crashes"? Does the server crash, or does the connector return an exception?
If the server crashes, could you please paste the crash report from the server error log?

Comment by Nicholas Denning [ 2017-02-11 ]

I have added a mysqldump of the test database I have created to illustrate the problem, plus details of the table definitions and the stored procedures that illustrate teh issue.

SP_EXAMPLE_SELECT works and has the full query that I am using.

SP_EXAMPLE_SELECT2 has the vebose part of the query replaced with a VIEW. SP_EXAMPLE_SELECT2 succeeds the first time it is called, but because the procedure has then been compiled and the plan stored, it then fails on the second call with the error loosing the connection below.

error 2013 (HY000) Lost connection to MySQL Server during query.

Comment by Nicholas Denning [ 2017-02-12 ]

Elena, when I say it crashes, what I mean is that I get an error

error 2013 (HY000) Lost connection to MySQL Server during query.

and the connection is lost.

this is consistent, whether I am running mysql on the linux machine where the database is located, or else running a tool such as Heidi to connect from my development environment.

So perhaps your implication is correct that I should not have used the phrase crash. if I try to re-run on using mysql I get a message

ERROR 2006 (HY000) : MySQL Server has gone away.
No Connection. Trying to reconnect ....
Connection id 3
Current database : testdb

So my session reconnects, runs the query again the first time but fails on a second try.

Incidentally did I say, I am running on Centos 7, configured as a minimum server with all yum updates up to date. Mariadb is installed by deploying the MariaDB.repo file and then using yum to install / update. I am currently running MariaDB 10.1.21

Comment by Nicholas Denning [ 2017-02-12 ]

mariadb_error_log.err

Sorry did not read your note properly. Attached is the error log. For some reason on my system there was no error log being produced as far as i could see so I have defined log_error to be a file in the /tmp directory so I can get some output.

I fired the query a couple of times to show it erroring then reconnecting then erroring again.

Comment by Nicholas Denning [ 2017-02-12 ]

Elena, sorry about the two account thing. Not quite sure what happened there, but i can get to this via the link. do you have enough information now? I note your reference to a SHOW CREATE ... and I am not quite sure what is meant there. Anyway hopefully the exmple in the code and data in the example dump file will be sufficient as it is i hope repeatable for you.

Comment by Elena Stepanova [ 2017-02-13 ]

nick.denning@diegesis.co.uk,

Thanks a lot for the report and test case. Yes, I can reproduce it now.

Comment by Elena Stepanova [ 2017-02-13 ]

sanja,
A shorter test case (extracted from the original one) is below, but please also check the original one after the bug is fixed!

CREATE TABLE ANY_TABLE (
 
  ENTITY_UID    BIGINT NOT NULL
 
);
 
CREATE TABLE SECURITY_PATH(
 
origid BIGINT UNSIGNED NOT NULL, 
destid BIGINT UNSIGNED NOT NULL, 
KEY (destid)
 
);
 
CREATE VIEW ENTITY_ACCESS (
 
ENTITY_UID,
 
OWNER_UID 
) AS
 
SELECT SP1.origid,
 
       SP2.destid
 
FROM SECURITY_PATH SP1 
JOIN SECURITY_PATH SP2 ON SP1.destid = SP2.origid 
;
 
--delimiter //
 
CREATE PROCEDURE SP_EXAMPLE_SELECT ()
 
BEGIN
 
   SELECT *
 
   FROM ANY_TABLE AT1
 
   WHERE EXISTS ( SELECT *
 
                  FROM ENTITY_ACCESS EA
 
                  WHERE AT1.ENTITY_UID = EA.ENTITY_UID
 
                  AND   EA.OWNER_UID IS NULL );
 
END
 
//
 
--delimiter ;
 
CALL SP_EXAMPLE_SELECT ();
 
CALL SP_EXAMPLE_SELECT ();

10.0 4e82aaab2f65

 
#3  <signal handler called>
 
#4  0x00000000005eefdf in find_field_in_tables (thd=0x7f065cbdd070, item=0x7f0659fb1088, first_table=0x7f0659faf5e8, last_table=0x0, ref=0x7f0659fb1228, report_error=IGNORE_EXCEPT_NON_UNIQUE, check_privileges=true, register_tree_change=true) at /data/src/10.0/sql/sql_base.cc:6638
 
#5  0x000000000085134f in Item_field::fix_fields (this=0x7f0659fb1088, thd=0x7f065cbdd070, reference=0x7f0659fb1228) at /data/src/10.0/sql/item.cc:5188
 
#6  0x00000000008936a3 in Item_func::fix_fields (this=0x7f0659fb1190, thd=0x7f065cbdd070, ref=0x7f0659d9f2b8) at /data/src/10.0/sql/item_func.cc:202
 
#7  0x0000000000873656 in Item_cond::fix_fields (this=0x7f0659d9f178, thd=0x7f065cbdd070, ref=0x7f0659d9f810) at /data/src/10.0/sql/item_cmpfunc.cc:4411
 
#8  0x00000000005f364d in setup_conds (thd=0x7f065cbdd070, tables=0x7f0659f50020, leaves=..., conds=0x7f0659d9f810) at /data/src/10.0/sql/sql_base.cc:8590
 
#9  0x00000000006bfa04 in setup_without_group (thd=0x7f065cbdd070, ref_pointer_array=0x7f0659f56d08, tables=0x7f0659f50020, leaves=..., fields=..., all_fields=..., conds=0x7f0659d9f810, order=0x0, group=0x0, hidden_group_fields=0x7f0659d9f6f0, reserved=0x7f0659faeadc) at /data/src/10.0/sql/sql_select.cc:634
 
#10 0x000000000067bcb6 in JOIN::prepare (this=0x7f0659d9f3d8, rref_pointer_array=0x7f0659faeab8, tables_init=0x7f0659f50020, wild_num=0, conds_init=0x7f0659d9f178, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f0659fae840, unit_arg=0x7f0659fae150) at /data/src/10.0/sql/sql_select.cc:794
 
#11 0x0000000000684a4c in mysql_select (thd=0x7f065cbdd070, rref_pointer_array=0x7f0659faeab8, tables=0x7f0659f50020, wild_num=0, fields=..., conds=0x7f0659d9f178, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147749632, result=0x7f0659d9f3b8, unit=0x7f0659fae150, select_lex=0x7f0659fae840) at /data/src/10.0/sql/sql_select.cc:3292
 
#12 0x000000000067ae22 in handle_select (thd=0x7f065cbdd070, lex=0x7f0659fae088, result=0x7f0659d9f3b8, setup_tables_done_option=0) at /data/src/10.0/sql/sql_select.cc:373
 
#13 0x000000000064f6b9 in execute_sqlcom_select (thd=0x7f065cbdd070, all_tables=0x7f0659f50020) at /data/src/10.0/sql/sql_parse.cc:5285
 
#14 0x0000000000647c1e in mysql_execute_command (thd=0x7f065cbdd070) at /data/src/10.0/sql/sql_parse.cc:2563
 
#15 0x00000000009752a2 in sp_instr_stmt::exec_core (this=0x7f0659fb1518, thd=0x7f065cbdd070, nextp=0x7f066385aa78) at /data/src/10.0/sql/sp_head.cc:3202
 
#16 0x00000000009749b1 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7f0659fb1558, thd=0x7f065cbdd070, nextp=0x7f066385aa78, open_tables=false, instr=0x7f0659fb1518) at /data/src/10.0/sql/sp_head.cc:2969
 
#17 0x0000000000974f6c in sp_instr_stmt::execute (this=0x7f0659fb1518, thd=0x7f065cbdd070, nextp=0x7f066385aa78) at /data/src/10.0/sql/sp_head.cc:3118
 
#18 0x0000000000970dbd in sp_head::execute (this=0x7f0659f4f088, thd=0x7f065cbdd070, merge_da_on_success=true) at /data/src/10.0/sql/sp_head.cc:1369
 
#19 0x0000000000972a71 in sp_head::execute_procedure (this=0x7f0659f4f088, thd=0x7f065cbdd070, args=0x7f065cbe1698) at /data/src/10.0/sql/sp_head.cc:2157
 
#20 0x000000000064dbdc in mysql_execute_command (thd=0x7f065cbdd070) at /data/src/10.0/sql/sql_parse.cc:4715
 
#21 0x000000000065233a in mysql_parse (thd=0x7f065cbdd070, rawbuf=0x7f0659cfa088 "CALL SP_EXAMPLE_SELECT ()", length=25, parser_state=0x7f066385b650) at /data/src/10.0/sql/sql_parse.cc:6567
 
#22 0x0000000000644e74 in dispatch_command (command=COM_QUERY, thd=0x7f065cbdd070, packet=0x7f065cbd3071 "CALL SP_EXAMPLE_SELECT ()", packet_length=25) at /data/src/10.0/sql/sql_parse.cc:1309
 
#23 0x0000000000644137 in do_command (thd=0x7f065cbdd070) at /data/src/10.0/sql/sql_parse.cc:999
 
#24 0x000000000076273e in do_handle_one_connection (thd_arg=0x7f065cbdd070) at /data/src/10.0/sql/sql_connect.cc:1377
 
#25 0x00000000007624b0 in handle_one_connection (arg=0x7f065cbdd070) at /data/src/10.0/sql/sql_connect.cc:1292
 
#26 0x00000000009fc126 in pfs_spawn_thread (arg=0x7f065b591370) at /data/src/10.0/storage/perfschema/pfs.cc:1860
 
#27 0x00007f06634980a4 in start_thread (arg=0x7f066385c700) at pthread_create.c:309
 
#28 0x00007f066165087d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

Comment by Oleksandr Byelkin [ 2017-02-22 ]

looks like a bit deja vu (some of 10.2 bugs)

Comment by Oleksandr Byelkin [ 2017-02-23 ]

WHERE condition is impossible, so probably subselect also is reboved

Comment by Oleksandr Byelkin [ 2017-02-23 ]

Problem is that Item taken out of subselect in first execution on second start finding SELECT_LEX from current (which is top SELECT).
Solution to start from select_lex from name resolution context

Comment by Oleksandr Byelkin [ 2017-02-23 ]

revision-id: 5eb9c19cfe3d5d0150d63a0fa3d5ea4f7a63f680 (mariadb-10.0.29-34-g5eb9c19)
parent(s): a0ce92ddc7d3f147c5103b9470d10bad194b41e4
committer: Oleksandr Byelkin
timestamp: 2017-02-23 21:50:55 +0100
message:

MDEV-11935: Queries in stored procedures with and EXISTS(SELECT * FROM VIEW) crashes and closes hte conneciton.

Use correct start point even for taken out from subselect items in process of exists2in conversion.

—

Comment by Oleksandr Byelkin [ 2017-02-23 ]

github branch bb-10.0-MDEV-11935

Comment by Igor Babaev [ 2017-02-26 ]

Sanja,

Ok to push, but see my question in email.

Generated at Thu Feb 08 07:53:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.