[MDEV-17278] CURSOR FOR LOOP - ERROR: unexpected end of stream, read 0 bytes (SERVER CRASH) - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.3.9, 10.3(EOL)
Fix Version/s: 10.3.11
Component/s: Stored routines
Labels:
None
Environment:
CentOS7

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)

Attachments

Issue Links

relates to

MDEV-12098 sql_mode=ORACLE: Implicit cursor FOR loop

Closed

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2018-09-24 08:53

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

Alice Sherepa added a comment - 2018-09-24 08:53 Could you please add your error log and output of SHOW CREATE PROCEDURE and SHOW CREATE TABLE for involved tables. Thanks!

Robert Dyas added a comment - 2018-09-24 18:27

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.

Robert Dyas added a comment - 2018-09-24 18:27 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.

Robert Dyas added a comment - 2018-09-24 18:31

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

Robert Dyas added a comment - 2018-09-24 18:31 I cannot easily add the error log out but if you have any issue reproducing I'll find a way, just let me know.

Alice Sherepa added a comment - 2018-09-25 10:04

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

Alice Sherepa added a comment - 2018-09-25 10:04 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

Robert Dyas added a comment - 2018-10-14 22:22

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

Robert Dyas added a comment - 2018-10-14 22:22 Any idea if this will get fixed in the next release?

Alexander Barkov added a comment - 2018-10-29 15:01

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

Alexander Barkov added a comment - 2018-10-29 15:01 Yes, I will try to fix this by the next release.

Alexander Barkov added a comment - 2018-11-13 16:04

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

Thanks.

Alexander Barkov added a comment - 2018-11-13 16:04 sanja , please review 7b329426b40f2c36e2bf8b347780c0a7854fcc53 in в bb-10.3-bar. Thanks.

Oleksandr Byelkin added a comment - 2018-11-14 10:13

OK to push

Oleksandr Byelkin added a comment - 2018-11-14 10:13 OK to push

People

Assignee:: Alexander Barkov

Reporter:: Robert Dyas

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2018-09-24 00:12

Updated:: 2018-11-14 10:25

Resolved:: 2018-11-14 10:25

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server