[MDEV-17278] CURSOR FOR LOOP - ERROR: unexpected end of stream, read 0 bytes (SERVER CRASH) Created: 2018-09-24  Updated: 2018-11-14  Resolved: 2018-11-14

Status: Closed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.3.9, 10.3
Fix Version/s: 10.3.11

Type: Bug Priority: Critical
Reporter: Robert Dyas Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS7

Issue Links:
Relates
relates to MDEV-12098 sql_mode=ORACLE: Implicit cursor FOR ... Closed

 Description   

The following code in a stored procedure produces the correct result when CALLed:

	FOR rec IN (SELECT Orders.Order_ID, Customers.State FROM Orders join Customers using (Customer_ID) where State is not null) DO
 
		set ans = concat(ans, '/', rec.Order_ID, '-', rec.State);
 
	END FOR;

However, if the field list is replaced by * it just crashes the server:

	FOR rec IN (SELECT * FROM Orders join Customers using (Customer_ID) where State is not null) DO
 
		set ans = concat(ans, '/', rec.Order_ID, '-', rec.State);
 
	END FOR;

with the message:

ERROR: (conn=10) unexpected end of stream, read 0 bytes from 4 (socket was closed by server)



 Comments   
Comment by Alice Sherepa [ 2018-09-24 ]

Could you please add your error log and output of SHOW CREATE PROCEDURE and SHOW CREATE TABLE for involved tables. Thanks!

Comment by Robert Dyas [ 2018-09-24 ]

Here is the output from show create table and show create proc:

show create table  Customers
 
 
 
CREATE TABLE "Customers" ( "Customer_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "Name" varchar(30) DEFAULT NULL, "Address" varchar(30) DEFAULT NULL, "City" varchar(30) DEFAULT NULL, "State" varchar(2) DEFAULT NULL, "Zip" varchar(10) DEFAULT NULL, "Email" varchar(120) DEFAULT NULL, "Phone" varchar(30) DEFAULT NULL, "Assigned_To_Email" varchar(60) DEFAULT NULL, "Date_Added" date DEFAULT NULL, "Parent_Customer_ID" bigint(20) DEFAULT NULL COMMENT 'AUTOKEY', PRIMARY KEY ("Customer_ID"), KEY "Customer_ID" ("Customer_ID"), KEY "Parent_Customer_ID" ("Parent_Customer_ID") ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
 
 
show create table Orders
 
 
 
CREATE TABLE "Orders" ( "Order_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "Customer_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "Order_Date" date NOT NULL, "Status" enum('New','Processing','Shipped','Cancelled') DEFAULT NULL, "Shipped_Date" date DEFAULT NULL, "Order_Total" double DEFAULT NULL COMMENT 'CURRENCY', "start_time" time DEFAULT NULL, "end_time" time DEFAULT NULL, "provider_id" varchar(255) DEFAULT NULL, PRIMARY KEY ("Order_ID"), KEY "Order_ID" ("Order_ID"), KEY "Customer_ID" ("Customer_ID"), CONSTRAINT "Orders_ibfk_2" FOREIGN KEY ("Customer_ID") REFERENCES "Customers" ("Customer_ID") ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
 
 
SHOW CREATE PROCEDURE aTestForLoop
 
 
 
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
 
aTestForLoop	ANSI_QUOTES,IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER="u1469"@"%" PROCEDURE "aTestForLoop"()
 
BEGIN
 
	
	declare ans mediumtext default '';
 
	
	DECLARE cust ROW TYPE OF Customers;
 
 
 
	SELECT * INTO cust 
	FROM Customers
 
	LIMIT 1;
 
 
 
	set ans = concat(ans, cust.Name);
 
 
 
	
	FOR rec IN (SELECT Orders.Order_ID, Customers.State FROM Orders join Customers using (Customer_ID) where State is not null) DO
 
		set ans = concat(ans, '/', rec.Order_ID, '-', rec.State);
 
	END FOR;
 
	
	select ans;
 
END	utf8	utf8_general_ci	utf8_general_ci

Note that changing
SELECT Orders.Order_ID, Customers.State
to
SELECT *
in the FOR loop causes the server crash.

Comment by Robert Dyas [ 2018-09-24 ]

I cannot easily add the error log out but if you have any issue reproducing I'll find a way, just let me know.

Comment by Alice Sherepa [ 2018-09-25 ]

Thanks a lot for the report! Reproducible on 10.3 with Innodb/MyIsam

CREATE TABLE t1 (id2 int, id int, en1 enum('aaa','a','b','c'));
 
INSERT INTO t1 VALUES(1,1,'aaa'),(2,2,'aaa'),(3,3,'aaa');
 
 
 
DELIMITER $$;
 
CREATE PROCEDURE p2()
 
BEGIN
 
	FOR rec IN (SELECT en1 FROM t1) DO select 1; END FOR;
 
END$$
 
DELIMITER ;$$
 
 
 
CALL p2();


 10.3 7aba6f8f8853acd18d471793f8b72aa
 
Thread 1 (Thread 0x7f144e656700 (LWP 13227)):
 
#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
 
#1  0x0000556c2e559362 in my_write_core (sig=11) at /10.3/mysys/stacktrace.c:481
 
#2  0x0000556c2dda233f in handle_fatal_signal (sig=11) at /10.3/sql/signal_handler.cc:305
 
#3  <signal handler called>
 
#4  0x0000556c2dbbab7b in find_type2 (typelib=0x7f14040b9908, x=0x7f14040665f8 "New", length=3, cs=0x556c2f1e4240 <my_charset_utf8_general_ci>) at /10.3/sql/strfunc.cc:170
 
#5  0x0000556c2dd87ed1 in Field_enum::store (this=0x7f14040b2d50, from=0x7f14040665f8 "New", length=3, cs=0x556c2f1e4240 <my_charset_utf8_general_ci>) at /10.3/sql/field.cc:9055
 
#6  0x0000556c2dd90d29 in Field::save_in_field_str (this=0x7f140406a830, to=0x7f14040b2d50) at /10.3/sql/field.h:629
 
#7  0x0000556c2dd95bda in Field_enum::save_in_field (this=0x7f140406a830, to=0x7f14040b2d50) at /10.3/sql/field.h:3952
 
#8  0x0000556c2dd95b62 in Field_enum::store_field (this=0x7f14040b2d50, from=0x7f140406a830) at /10.3/sql/field.h:3946
 
#9  0x0000556c2dd9a2a7 in field_conv_incompatible (to=0x7f14040b2d50, from=0x7f140406a830) at /10.3/sql/field_conv.cc:836
 
#10 0x0000556c2dd9a303 in field_conv (to=0x7f14040b2d50, from=0x7f140406a830) at /10.3/sql/field_conv.cc:849
 
#11 0x0000556c2ddce346 in save_field_in_field (from=0x7f140406a830, null_value=0x7f14040bf52e, to=0x7f14040b2d50, no_conversions=false) at /10.3/sql/item.cc:6814
 
#12 0x0000556c2ddce588 in Item_field::save_in_field (this=0x7f14040bf4b8, to=0x7f14040b2d50, no_conversions=false) at /10.3/sql/item.cc:6865
 
#13 0x0000556c2dd69b03 in Field::sp_prepare_and_store_item (this=0x7f14040b2d50, thd=0x7f1404000b00, value=0x7f144e653df8) at /10.3/sql/field.cc:1344
 
#14 0x0000556c2db2573a in Virtual_tmp_table::sp_set_all_fields_from_item_list (this=0x7f14040b18d0, thd=0x7f1404000b00, items=...) at /10.3/sql/sql_select.cc:18037
 
#15 0x0000556c2d9f03e2 in sp_rcontext::set_variable_row (this=0x7f140401aa38, thd=0x7f1404000b00, var_idx=2, items=...) at /10.3/sql/sp_rcontext.cc:655
 
#16 0x0000556c2d9f100f in sp_cursor::Select_fetch_into_spvars::send_data (this=0x7f14040500c0, items=...) at /10.3/sql/sp_rcontext.cc:918
 
#17 0x0000556c2da62e8a in Materialized_cursor::fetch (this=0x7f14040beaf8, num_rows=1) at /10.3/sql/sql_cursor.cc:363
 
#18 0x0000556c2d9f0d03 in sp_cursor::fetch (this=0x7f14040500a0, thd=0x7f1404000b00, vars=0x7f1404052320, error_on_no_data=false) at /10.3/sql/sp_rcontext.cc:830
 
#19 0x0000556c2d9e231d in sp_instr_cfetch::execute (this=0x7f14040522e8, thd=0x7f1404000b00, nextp=0x7f144e654004) at /10.3/sql/sp_head.cc:4365
 
#20 0x0000556c2d9d9d8a in sp_head::execute (this=0x7f140404a268, thd=0x7f1404000b00, merge_da_on_success=true) at /10.3/sql/sp_head.cc:1354
 
#21 0x0000556c2d9dc74b in sp_head::execute_procedure (this=0x7f140404a268, thd=0x7f1404000b00, args=0x7f1404005748) at /10.3/sql/sp_head.cc:2294
 
#22 0x0000556c2dab19ea in do_execute_sp (thd=0x7f1404000b00, sp=0x7f140404a268) at /10.3/sql/sql_parse.cc:2949
 
#23 0x0000556c2dab2568 in Sql_cmd_call::execute (this=0x7f1404014dd8, thd=0x7f1404000b00) at /10.3/sql/sql_parse.cc:3191
 
#24 0x0000556c2dabc798 in mysql_execute_command (thd=0x7f1404000b00) at /10.3/sql/sql_parse.cc:6285
 
#25 0x0000556c2dac1af1 in mysql_parse (thd=0x7f1404000b00, rawbuf=0x7f1404014d18 "CALL p2()", length=9, parser_state=0x7f144e655470, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:8089
 
#26 0x0000556c2daaea96 in dispatch_command (command=COM_QUERY, thd=0x7f1404000b00, packet=0x7f140400b221 "CALL p2()", packet_length=9, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:1850
 
#27 0x0000556c2daad4b3 in do_command (thd=0x7f1404000b00) at /10.3/sql/sql_parse.cc:1395
 
#28 0x0000556c2dc13f3b in do_handle_one_connection (connect=0x556c30c0f570) at /10.3/sql/sql_connect.cc:1402
 
#29 0x0000556c2dc13c8c in handle_one_connection (arg=0x556c30c0f570) at /10.3/sql/sql_connect.cc:1308
 
#30 0x0000556c2e4e9748 in pfs_spawn_thread (arg=0x556c30c22f10) at /10.3/storage/perfschema/pfs.cc:1862
 
#31 0x00007f1455c9d6ba in start_thread (arg=0x7f144e656700) at pthread_create.c:333
 
#32 0x00007f145513241d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Comment by Robert Dyas [ 2018-10-14 ]

Any idea if this will get fixed in the next release?

Comment by Alexander Barkov [ 2018-10-29 ]

Yes, I will try to fix this by the next release.

Comment by Alexander Barkov [ 2018-11-13 ]

sanja, please review 7b329426b40f2c36e2bf8b347780c0a7854fcc53 in в bb-10.3-bar.

Thanks.

Comment by Oleksandr Byelkin [ 2018-11-14 ]

OK to push

Generated at Thu Feb 08 08:35:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.