[MDEV-23094] Multiple calls to a Stored Procedure from another Stored Procedure crashes server - Jira

Björn Möller created issue - 2020-07-04 20:31

Björn Möller made changes - 2020-07-04 20:33

Field	Original Value	New Value
Description	Multiple calls to a Stored Procedure (e.g. from a loop) from another Stored Procedure crashes the database server (i.e. the Maria DB Windows Service stops running). This problem has been present in the latest releases of 10.4, and also in the new 10.5.4 . MariaDB 10.3 is Ok though. {code:sql} //Code Example CREATE PROCEDURE SP1 BEGIN DECLARE p INT SET P = 2; WHILE ( p > 0 ) DO CALL SP2; SET P = P - 1; END WHILE; END {code}	Multiple calls to a Stored Procedure (e.g. from a loop) from another Stored Procedure crashes the database server (i.e. the Maria DB Windows Service stops running). The crash seems to occure on the second call to the inner Stored Procedure. This problem has been present in the latest releases of 10.4, and also in the new 10.5.4 . MariaDB 10.3 is Ok though. {code:sql} //Code Example CREATE PROCEDURE SP1 BEGIN DECLARE p INT SET P = 2; WHILE ( p > 0 ) DO CALL SP2; SET P = P - 1; END WHILE; END {code}

Björn Möller made changes - 2020-07-04 20:37

Description

Multiple calls to a Stored Procedure (e.g. from a loop) from another Stored Procedure crashes the database server (i.e. the Maria DB Windows Service stops running). The crash seems to occure on the second call to the inner Stored Procedure.

This problem has been present in the latest releases of 10.4, and also in the new 10.5.4 .
MariaDB 10.3 is Ok though.

{code:sql}
//Code Example
CREATE PROCEDURE SP1
BEGIN
  DECLARE p INT

  SET P = 2;
  WHILE ( p > 0 ) DO
    CALL SP2;
    SET P = P - 1;
  END WHILE;
END
{code}

Multiple calls to a Stored Procedure (e.g. from a loop) from another Stored Procedure crashes the database server (i.e. the MariaDB Windows Service stops running). The crash seems to occure on the second call to the inner Stored Procedure.

This problem has been present in the latest releases of 10.4, and also in the new 10.5.4 .
MariaDB 10.3 is Ok though.

{code:sql}
//Code Example
CREATE PROCEDURE SP1
BEGIN
  DECLARE p INT

  SET P = 2;
  WHILE ( p > 0 ) DO
    CALL SP2;
    SET P = P - 1;
  END WHILE;
END
{code}

Björn Möller made changes - 2020-07-04 20:45

Attachment

POLLUXPHP2.err [ 52575 ]

Björn Möller made changes - 2020-07-04 20:46

Attachment

POLLUXPHP2.err [ 52575 ]

Björn Möller made changes - 2020-07-04 20:47

Attachment

POLLUXPHP2.err [ 52576 ]

Björn Möller made changes - 2020-07-04 20:49

Component/s

Stored routines [ 13905 ]

Björn Möller made changes - 2020-07-04 21:10

Environment

Win64

Win64 (Windows Server 2019)

Björn Möller made changes - 2020-07-04 21:15

Priority

Major [ 3 ]

Critical [ 2 ]

Alice Sherepa made changes - 2020-07-06 09:28

Link

This issue relates to ~~MDEV-20290~~ [ ~~MDEV-20290~~ ]

Alice Sherepa added a comment - 2020-07-06 09:31 - edited

Probably the same bug as ~~MDEV-20284~~(~~MDEV-20290~~)
from the error log:

Server version: 10.5.4-MariaDB

server.dll!SQL_SELECT::cleanup()[opt_range.cc:1225]

server.dll!st_join_table::cleanup()[sql_select.cc:13391]

server.dll!JOIN::cleanup()[sql_select.cc:13847]

server.dll!JOIN::destroy()[sql_select.cc:4472]

server.dll!st_select_lex::cleanup()[sql_union.cc:2711]

server.dll!subselect_single_select_engine::prepare()[item_subselect.cc:3752]

server.dll!Item_subselect::fix_fields()[item_subselect.cc:285]

server.dll!Item::fix_fields_if_needed_for_scalar()[item.h:982]

server.dll!Item_cond::fix_fields()[item_cmpfunc.cc:4905]

server.dll!Item::fix_fields_if_needed_for_scalar()[item.h:982]

server.dll!Item_cond::fix_fields()[item_cmpfunc.cc:4905]

server.dll!THD::sp_fix_func_item()[sp_head.cc:397]

server.dll!THD::sp_prepare_func_item()[sp_head.cc:383]

server.dll!sp_instr_jump_if_not::exec_core()[sp_head.cc:4062]

server.dll!sp_lex_keeper::reset_lex_and_exec_core()[sp_head.cc:3492]

server.dll!sp_instr_jump_if_not::execute()[sp_head.cc:4052]

server.dll!sp_head::execute()[sp_head.cc:1435]

server.dll!sp_head::execute_procedure()[sp_head.cc:2446]

server.dll!do_execute_sp()[sql_parse.cc:3050]

server.dll!Sql_cmd_call::execute()[sql_parse.cc:3296]

server.dll!mysql_execute_command()[sql_parse.cc:5963]

server.dll!sp_instr_stmt::exec_core()[sp_head.cc:3767]

server.dll!sp_lex_keeper::reset_lex_and_exec_core()[sp_head.cc:3492]

server.dll!sp_instr_stmt::execute()[sp_head.cc:3671]

server.dll!sp_head::execute()[sp_head.cc:1435]

server.dll!sp_head::execute_procedure()[sp_head.cc:2446]

server.dll!do_execute_sp()[sql_parse.cc:3050]

server.dll!Sql_cmd_call::execute()[sql_parse.cc:3296]

server.dll!mysql_execute_command()[sql_parse.cc:5963]

server.dll!mysql_parse()[sql_parse.cc:7997]

server.dll!dispatch_command()[sql_parse.cc:1877]

server.dll!do_command()[sql_parse.cc:1355]

server.dll!threadpool_process_request()[threadpool_common.cc:354]

server.dll!tp_callback()[threadpool_common.cc:194]

ntdll.dll!RtlInitializeCriticalSection()

ntdll.dll!RtlReleaseSRWLockExclusive()

KERNEL32.DLL!BaseThreadInitThunk()

ntdll.dll!RtlUserThreadStart()

Query (0x26e111e20b0): CALL sp_bm_copy_Event( NAME_CONST('p_copyEventID',3302),  NAME_CONST('p_tree',_utf8'tree1' COLLATE 'utf8_swedish_ci'),  NAME_CONST('v_copyToPersfamID',_utf8'i39' COLLATE 'utf8_swedish_ci'),  NAME_CONST('p_updChangeInfo',1),  NAME_CONST('p_changeDate',TIMESTAMP'2020-07-04 22:01:14'),  NAME_CONST('p_changedBy',_utf8'ADMIN' COLLATE 'utf8_swedish_ci'),  NAME_CONST('v_result',0),  NAME_CONST('v_eventCopiedCounter',1))

Connection ID (thread ID): 27

Status: NOT_KILLED

Alice Sherepa added a comment - 2020-07-06 09:31 - edited Probably the same bug as MDEV-20284 ( MDEV-20290 ) from the error log: Server version: 10.5.4-MariaDB server.dll!SQL_SELECT::cleanup()[opt_range.cc:1225] server.dll!st_join_table::cleanup()[sql_select.cc:13391] server.dll!JOIN::cleanup()[sql_select.cc:13847] server.dll!JOIN::destroy()[sql_select.cc:4472] server.dll!st_select_lex::cleanup()[sql_union.cc:2711] server.dll!subselect_single_select_engine::prepare()[item_subselect.cc:3752] server.dll!Item_subselect::fix_fields()[item_subselect.cc:285] server.dll!Item::fix_fields_if_needed_for_scalar()[item.h:982] server.dll!Item_cond::fix_fields()[item_cmpfunc.cc:4905] server.dll!Item::fix_fields_if_needed_for_scalar()[item.h:982] server.dll!Item_cond::fix_fields()[item_cmpfunc.cc:4905] server.dll!THD::sp_fix_func_item()[sp_head.cc:397] server.dll!THD::sp_prepare_func_item()[sp_head.cc:383] server.dll!sp_instr_jump_if_not::exec_core()[sp_head.cc:4062] server.dll!sp_lex_keeper::reset_lex_and_exec_core()[sp_head.cc:3492] server.dll!sp_instr_jump_if_not::execute()[sp_head.cc:4052] server.dll!sp_head::execute()[sp_head.cc:1435] server.dll!sp_head::execute_procedure()[sp_head.cc:2446] server.dll!do_execute_sp()[sql_parse.cc:3050] server.dll!Sql_cmd_call::execute()[sql_parse.cc:3296] server.dll!mysql_execute_command()[sql_parse.cc:5963] server.dll!sp_instr_stmt::exec_core()[sp_head.cc:3767] server.dll!sp_lex_keeper::reset_lex_and_exec_core()[sp_head.cc:3492] server.dll!sp_instr_stmt::execute()[sp_head.cc:3671] server.dll!sp_head::execute()[sp_head.cc:1435] server.dll!sp_head::execute_procedure()[sp_head.cc:2446] server.dll!do_execute_sp()[sql_parse.cc:3050] server.dll!Sql_cmd_call::execute()[sql_parse.cc:3296] server.dll!mysql_execute_command()[sql_parse.cc:5963] server.dll!mysql_parse()[sql_parse.cc:7997] server.dll!dispatch_command()[sql_parse.cc:1877] server.dll!do_command()[sql_parse.cc:1355] server.dll!threadpool_process_request()[threadpool_common.cc:354] server.dll!tp_callback()[threadpool_common.cc:194] ntdll.dll!RtlInitializeCriticalSection() ntdll.dll!RtlReleaseSRWLockExclusive() KERNEL32.DLL!BaseThreadInitThunk() ntdll.dll!RtlUserThreadStart() Query (0x26e111e20b0): CALL sp_bm_copy_Event( NAME_CONST('p_copyEventID',3302), NAME_CONST('p_tree',_utf8'tree1' COLLATE 'utf8_swedish_ci'), NAME_CONST('v_copyToPersfamID',_utf8'i39' COLLATE 'utf8_swedish_ci'), NAME_CONST('p_updChangeInfo',1), NAME_CONST('p_changeDate',TIMESTAMP'2020-07-04 22:01:14'), NAME_CONST('p_changedBy',_utf8'ADMIN' COLLATE 'utf8_swedish_ci'), NAME_CONST('v_result',0), NAME_CONST('v_eventCopiedCounter',1)) Connection ID (thread ID): 27 Status: NOT_KILLED

Elena Stepanova made changes - 2020-07-20 23:11

Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Assignee		Oleksandr Byelkin [ sanja ]

Oleksandr Byelkin made changes - 2020-07-21 08:56

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2020-07-21 09:08

I fixed several syntax errors in the test suite, added second procedure but it does not crash

CREATE PROCEDURE SP2()

BEGIN

END;

$$

CREATE PROCEDURE SP1()

BEGIN

  DECLARE p INT;

  SET P = 2;

  WHILE ( p > 0 ) DO

    CALL SP2;

    SET P = P - 1;

  END WHILE;

END;

$$

Oleksandr Byelkin added a comment - 2020-07-21 09:08 I fixed several syntax errors in the test suite, added second procedure but it does not crash CREATE PROCEDURE SP2() BEGIN END; $$ CREATE PROCEDURE SP1() BEGIN DECLARE p INT; SET P = 2; WHILE ( p > 0 ) DO CALL SP2; SET P = P - 1; END WHILE; END; $$

Oleksandr Byelkin made changes - 2020-07-21 09:08

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Oleksandr Byelkin added a comment - 2020-07-21 09:11

I need better test suite or I can try to fix bugs claimed to be duplicate (which can be only guess because there are triggers)

Oleksandr Byelkin added a comment - 2020-07-21 09:11 I need better test suite or I can try to fix bugs claimed to be duplicate (which can be only guess because there are triggers)

Björn Möller added a comment - 2020-07-21 09:48 - edited

Here is the code for the actual Stored Procedures which are causing the crash on my database:

Outer SP:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_bm_copy_EventToMany`(

	IN `p_copyEventID` INT,

	IN `p_tree` VARCHAR(20),

	IN `p_copyToPersfamIDList` VARCHAR(21845),

	IN `p_updChangeInfo` TINYINT,

	IN `p_changeDate` DATETIME,

	IN `p_changedBy` VARCHAR(20)

LANGUAGE SQL

NOT DETERMINISTIC

MODIFIES SQL DATA

SQL SECURITY INVOKER

COMMENT ''

BEGIN

	DECLARE v_copyToPersfamID VARCHAR(22);

	DECLARE v_copyToPersfamIDList VARCHAR(21845);

	DECLARE v_sepPos INT;

	DECLARE v_result TINYINT;

	DECLARE v_eventCopiedCounter INT DEFAULT 0;

	SET v_copyToPersfamIDList = TRIM(p_copyToPersfamIDList);

	WHILE ( CHAR_LENGTH(v_copyToPersfamIDList) > 0 ) DO

		SET v_sepPos = INSTR(v_copyToPersfamIDList, ',');

		IF ( v_sepPos > 0 ) THEN

			SET v_copyToPersfamID = TRIM( LEFT(v_copyToPersfamIDList, v_sepPos - 1) );

			SET v_copyToPersfamIDList = TRIM( SUBSTRING(v_copyToPersfamIDList, v_sepPos + 1, CHAR_LENGTH(v_copyToPersfamIDList) - v_sepPos) );

		ELSE

			SET v_copyToPersfamID = TRIM(v_copyToPersfamIDList);

			SET v_copyToPersfamIDList = '';

		END IF;

		IF ( v_copyToPersfamID <> '' ) THEN

		  CALL sp_bm_copy_Event(p_copyEventID, p_tree, v_copyToPersfamID, p_updChangeInfo, p_changeDate, p_changedBy, v_result, v_eventCopiedCounter);

		END IF;

	END WHILE;

	SELECT v_eventCopiedCounter As copiedCounter;

END

Inner SP:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_bm_copy_Event`(

	IN `p_copyEventID` INT,

	IN `p_tree` VARCHAR(20),

	IN `p_copyToPersfamID` VARCHAR(22),

	IN `p_updChangeInfo` TINYINT,

	IN `p_changeDate` DATETIME,

	IN `p_changedBy` VARCHAR(20),

	OUT `p_result` TINYINT,

	INOUT `p_numEventsCopied` INT

LANGUAGE SQL

NOT DETERMINISTIC

MODIFIES SQL DATA

SQL SECURITY INVOKER

COMMENT ''

BEGIN

	DECLARE v_eventID int DEFAULT 0;

	DECLARE v_addressID VARCHAR(10) DEFAULT '';

	DECLARE v_gedcom VARCHAR(20) DEFAULT '';

	DECLARE v_note TEXT;

	DECLARE v_noteID INT;

	DECLARE v_secret TINYINT;

	DECLARE v_ordernum FLOAT;

	DECLARE v_languageID INT;

	DECLARE vout_newNoteID INT;

	DECLARE v_newNoteID VARCHAR(10) DEFAULT '';

	DECLARE v_exitNotesLoop BOOL DEFAULT FALSE;

	DECLARE v_exitLoop2 BOOL DEFAULT FALSE;

	DECLARE v_rollback BOOL DEFAULT 0;

	DECLARE notesCursor CURSOR FOR

		SELECT n.ID, n.note, nl.secret, nl.ordernum, n.languageID

	   FROM tng_xnotes n

		JOIN tng_notelinks nl ON n.ID = nl.xnoteID

		WHERE nl.eventID = p_copyEventID;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_exitNotesLoop = TRUE;

   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_rollback = 1;

   IF ( EXISTS( SELECT * FROM tng_people p   WHERE p.personID = p_copyToPersfamID AND p.gedcom = p_tree )   OR

   	  EXISTS( SELECT * FROM tng_families f WHERE f.familyID = p_copyToPersfamID AND f.gedcom = p_tree ) ) AND

	   NOT EXISTS( SELECT * FROM tng_events ev1 WHERE ev1.eventID = p_copyEventID AND ev1.persfamID = p_copyToPersfamID AND ev1.gedcom = p_tree ) THEN

		START TRANSACTION;

   -- Duplicate possible Addresses -----------------------------------------------------------------------------------------------------------

		SELECT TRIM(ev2.addressID), ev2.gedcom INTO v_addressID, v_gedcom

			FROM tng_events ev2

			WHERE ev2.eventID = p_copyEventID LIMIT 1;

		IF ( v_addressID <> '' ) THEN

			INSERT INTO tng_addresses (address1, address2, city, state, zip, country, www, email, phone, gedcom)

				( SELECT ad.address1, ad.address2, ad.city, ad.state, ad.zip, ad.country, ad.www, ad.email, ad.phone, p_tree

				  FROM tng_addresses ad

				  WHERE ad.addressID = v_addressID );

			SET v_addressID = LAST_INSERT_ID();

		END IF;

	-- Copy Event ----------------------------------------------------------------------------------------------------------------------------

		INSERT INTO tng_events (gedcom, persfamID, eventtypeID, eventdate, eventdatetr, eventplace, age, agency, cause, addressID, parenttag, info, bm_overrideCollapse)

	   	( SELECT p_tree, p_copyToPersfamID, ev.eventtypeID, ev.eventdate, ev.eventdatetr, ev.eventplace, ev.age, ev.agency, ev.cause, v_addressID, '', ev.info, ev.bm_overrideCollapse

	   	  FROM tng_events ev

			  WHERE ev.eventID = p_copyEventID );

		SET v_eventID = LAST_INSERT_ID();

	-- Copy Notes ----------------------------------------------------------------------------------------------------------------------------

		OPEN notesCursor;

		notesLoop: LOOP

			FETCH notesCursor INTO v_noteID, v_note, v_secret, v_ordernum, v_languageID;

			IF v_exitNotesLoop THEN LEAVE notesLoop; END IF;

			CALL sp_bm_add_Note(p_copyToPersfamID, p_tree, v_eventID, v_note, v_secret, v_ordernum, v_languageID, 0, vout_newNoteID);

			SET v_newNoteID = CONCAT('N', vout_newNoteID);

			-- Copy Note Citations

			INSERT INTO tng_citations (gedcom, persfamID, eventID, sourceID, ordernum, description, citedate, citedatetr, citetext, page, quay, note)

				( SELECT p_tree, p_copyToPersfamID, v_newNoteID, nc.sourceID, nc.ordernum, nc.description, nc.citedate, nc.citedatetr, nc.citetext, nc.page, nc.quay, nc.note

				  FROM tng_citations nc

				  WHERE nc.eventID = CONCAT('N', v_noteID) );

		END LOOP;

		CLOSE notesCursor;

	-- Copy Event Citations ------------------------------------------------------------------------------------------------------------------

		INSERT INTO tng_citations (gedcom, persfamID, eventID, sourceID, ordernum, description, citedate, citedatetr, citetext, page, quay, note)

			( SELECT p_tree, p_copyToPersfamID, v_eventID, ec.sourceID, ec.ordernum, ec.description, ec.citedate, ec.citedatetr, ec.citetext, ec.page, ec.quay, ec.note

			  FROM tng_citations ec

			  WHERE ec.eventID = CAST(p_copyEventID AS CHAR(10)) );

	-- Copy Media Links ----------------------------------------------------------------------------------------------------------------------

		INSERT INTO tng_medialinks (gedcom, linktype, personID, eventID, mediaID, altdescription, altnotes, ordernum, dontshow, defphoto)

			( SELECT p_tree, ml.linktype, p_copyToPersfamID, v_eventID, ml.mediaID, ml.altdescription, ml.altnotes, ml.ordernum, ml.dontshow, ml.defphoto

			  FROM tng_medialinks ml

			  WHERE ml.eventID = CAST(p_copyEventID AS CHAR(10)) );

	-- Update Change Info --------------------------------------------------------------------------------------------------------------------

		IF ( p_updChangeInfo <> 0 ) THEN

			UPDATE tng_people tp

				SET tp.changedate = p_changeDate, tp.changedby = p_changedBy

				WHERE tp.personID = p_copyToPersfamID AND tp.gedcom = p_tree;

			UPDATE tng_families tf

				SET tf.changedate = p_changeDate, tf.changedby = p_changedBy

				WHERE tf.familyID = p_copyToPersfamID AND tf.gedcom = p_tree;

		END IF;

	-- ---------------------------------------------------------------------------------------------------------------------------------------

		IF v_rollback THEN

	      ROLLBACK;

	      SET p_result = 1;

	   ELSE

	    	COMMIT;

	    	SET p_result = 0;

   		SET p_numEventsCopied = p_numEventsCopied + 1;

	   END IF;

	END IF;

END

I don't use triggers anywhere in this database.

The crash always occure on the second call to the inner SP.

This error has been present at least since MariaDB 10.4.8, when I started to test version 10.4. It's currently preventing me from upgrading my Genealogy database from MariaDB 10.3, where this code works without any problems.
If there are changes to the SQL Syntax causing this, at least the whole database process (i.e. the Windows Service) shouldn't crash as it does now.

One more thing. When I first discovered this bug (probably when testing v 10.4.8 in the beginning of november 2019), I searched Jira and found a similar bug being filed and later marked as fixed.

Björn Möller added a comment - 2020-07-21 09:48 - edited Here is the code for the actual Stored Procedures which are causing the crash on my database: Outer SP: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_bm_copy_EventToMany`( IN `p_copyEventID` INT, IN `p_tree` VARCHAR( 20 ), IN `p_copyToPersfamIDList` VARCHAR( 21845 ), IN `p_updChangeInfo` TINYINT, IN `p_changeDate` DATETIME, IN `p_changedBy` VARCHAR( 20 ) ) LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT '' BEGIN DECLARE v_copyToPersfamID VARCHAR( 22 ); DECLARE v_copyToPersfamIDList VARCHAR( 21845 ); DECLARE v_sepPos INT; DECLARE v_result TINYINT; DECLARE v_eventCopiedCounter INT DEFAULT 0 ; SET v_copyToPersfamIDList = TRIM(p_copyToPersfamIDList); WHILE ( CHAR_LENGTH(v_copyToPersfamIDList) > 0 ) DO SET v_sepPos = INSTR(v_copyToPersfamIDList, ',' ); IF ( v_sepPos > 0 ) THEN SET v_copyToPersfamID = TRIM( LEFT(v_copyToPersfamIDList, v_sepPos - 1 ) ); SET v_copyToPersfamIDList = TRIM( SUBSTRING(v_copyToPersfamIDList, v_sepPos + 1 , CHAR_LENGTH(v_copyToPersfamIDList) - v_sepPos) ); ELSE SET v_copyToPersfamID = TRIM(v_copyToPersfamIDList); SET v_copyToPersfamIDList = '' ; END IF; IF ( v_copyToPersfamID <> '' ) THEN CALL sp_bm_copy_Event(p_copyEventID, p_tree, v_copyToPersfamID, p_updChangeInfo, p_changeDate, p_changedBy, v_result, v_eventCopiedCounter); END IF; END WHILE; SELECT v_eventCopiedCounter As copiedCounter; END Inner SP: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_bm_copy_Event`( IN `p_copyEventID` INT, IN `p_tree` VARCHAR( 20 ), IN `p_copyToPersfamID` VARCHAR( 22 ), IN `p_updChangeInfo` TINYINT, IN `p_changeDate` DATETIME, IN `p_changedBy` VARCHAR( 20 ), OUT `p_result` TINYINT, INOUT `p_numEventsCopied` INT ) LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT '' BEGIN DECLARE v_eventID int DEFAULT 0 ; DECLARE v_addressID VARCHAR( 10 ) DEFAULT '' ; DECLARE v_gedcom VARCHAR( 20 ) DEFAULT '' ; DECLARE v_note TEXT; DECLARE v_noteID INT; DECLARE v_secret TINYINT; DECLARE v_ordernum FLOAT; DECLARE v_languageID INT; DECLARE vout_newNoteID INT; DECLARE v_newNoteID VARCHAR( 10 ) DEFAULT '' ; DECLARE v_exitNotesLoop BOOL DEFAULT FALSE; DECLARE v_exitLoop2 BOOL DEFAULT FALSE; DECLARE v_rollback BOOL DEFAULT 0 ; DECLARE notesCursor CURSOR FOR SELECT n.ID, n.note, nl.secret, nl.ordernum, n.languageID FROM tng_xnotes n JOIN tng_notelinks nl ON n.ID = nl.xnoteID WHERE nl.eventID = p_copyEventID; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_exitNotesLoop = TRUE; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_rollback = 1 ; IF ( EXISTS( SELECT * FROM tng_people p WHERE p.personID = p_copyToPersfamID AND p.gedcom = p_tree ) OR EXISTS( SELECT * FROM tng_families f WHERE f.familyID = p_copyToPersfamID AND f.gedcom = p_tree ) ) AND NOT EXISTS( SELECT * FROM tng_events ev1 WHERE ev1.eventID = p_copyEventID AND ev1.persfamID = p_copyToPersfamID AND ev1.gedcom = p_tree ) THEN START TRANSACTION; -- Duplicate possible Addresses ----------------------------------------------------------------------------------------------------------- SELECT TRIM(ev2.addressID), ev2.gedcom INTO v_addressID, v_gedcom FROM tng_events ev2 WHERE ev2.eventID = p_copyEventID LIMIT 1 ; IF ( v_addressID <> '' ) THEN INSERT INTO tng_addresses (address1, address2, city, state, zip, country, www, email, phone, gedcom) ( SELECT ad.address1, ad.address2, ad.city, ad.state, ad.zip, ad.country, ad.www, ad.email, ad.phone, p_tree FROM tng_addresses ad WHERE ad.addressID = v_addressID ); SET v_addressID = LAST_INSERT_ID(); END IF; -- Copy Event ---------------------------------------------------------------------------------------------------------------------------- INSERT INTO tng_events (gedcom, persfamID, eventtypeID, eventdate, eventdatetr, eventplace, age, agency, cause, addressID, parenttag, info, bm_overrideCollapse) ( SELECT p_tree, p_copyToPersfamID, ev.eventtypeID, ev.eventdate, ev.eventdatetr, ev.eventplace, ev.age, ev.agency, ev.cause, v_addressID, '' , ev.info, ev.bm_overrideCollapse FROM tng_events ev WHERE ev.eventID = p_copyEventID ); SET v_eventID = LAST_INSERT_ID(); -- Copy Notes ---------------------------------------------------------------------------------------------------------------------------- OPEN notesCursor; notesLoop: LOOP FETCH notesCursor INTO v_noteID, v_note, v_secret, v_ordernum, v_languageID; IF v_exitNotesLoop THEN LEAVE notesLoop; END IF; CALL sp_bm_add_Note(p_copyToPersfamID, p_tree, v_eventID, v_note, v_secret, v_ordernum, v_languageID, 0 , vout_newNoteID); SET v_newNoteID = CONCAT( 'N' , vout_newNoteID); -- Copy Note Citations INSERT INTO tng_citations (gedcom, persfamID, eventID, sourceID, ordernum, description, citedate, citedatetr, citetext, page, quay, note) ( SELECT p_tree, p_copyToPersfamID, v_newNoteID, nc.sourceID, nc.ordernum, nc.description, nc.citedate, nc.citedatetr, nc.citetext, nc.page, nc.quay, nc.note FROM tng_citations nc WHERE nc.eventID = CONCAT( 'N' , v_noteID) ); END LOOP; CLOSE notesCursor; -- Copy Event Citations ------------------------------------------------------------------------------------------------------------------ INSERT INTO tng_citations (gedcom, persfamID, eventID, sourceID, ordernum, description, citedate, citedatetr, citetext, page, quay, note) ( SELECT p_tree, p_copyToPersfamID, v_eventID, ec.sourceID, ec.ordernum, ec.description, ec.citedate, ec.citedatetr, ec.citetext, ec.page, ec.quay, ec.note FROM tng_citations ec WHERE ec.eventID = CAST(p_copyEventID AS CHAR( 10 )) ); -- Copy Media Links ---------------------------------------------------------------------------------------------------------------------- INSERT INTO tng_medialinks (gedcom, linktype, personID, eventID, mediaID, altdescription, altnotes, ordernum, dontshow, defphoto) ( SELECT p_tree, ml.linktype, p_copyToPersfamID, v_eventID, ml.mediaID, ml.altdescription, ml.altnotes, ml.ordernum, ml.dontshow, ml.defphoto FROM tng_medialinks ml WHERE ml.eventID = CAST(p_copyEventID AS CHAR( 10 )) ); -- Update Change Info -------------------------------------------------------------------------------------------------------------------- IF ( p_updChangeInfo <> 0 ) THEN UPDATE tng_people tp SET tp.changedate = p_changeDate, tp.changedby = p_changedBy WHERE tp.personID = p_copyToPersfamID AND tp.gedcom = p_tree; UPDATE tng_families tf SET tf.changedate = p_changeDate, tf.changedby = p_changedBy WHERE tf.familyID = p_copyToPersfamID AND tf.gedcom = p_tree; END IF; -- --------------------------------------------------------------------------------------------------------------------------------------- IF v_rollback THEN ROLLBACK; SET p_result = 1 ; ELSE COMMIT; SET p_result = 0 ; SET p_numEventsCopied = p_numEventsCopied + 1 ; END IF; END IF; END I don't use triggers anywhere in this database. The crash always occure on the second call to the inner SP. This error has been present at least since MariaDB 10.4.8, when I started to test version 10.4. It's currently preventing me from upgrading my Genealogy database from MariaDB 10.3, where this code works without any problems. If there are changes to the SQL Syntax causing this, at least the whole database process (i.e. the Windows Service) shouldn't crash as it does now. One more thing. When I first discovered this bug (probably when testing v 10.4.8 in the beginning of november 2019), I searched Jira and found a similar bug being filed and later marked as fixed.

Björn Möller added a comment - 2020-07-21 09:58

I just noticed there actually is a third level of Stored Procedures being called from the Inner SP above (sp_bm_add_Note).
If required I could add this code to the ticket as well

Björn Möller added a comment - 2020-07-21 09:58 I just noticed there actually is a third level of Stored Procedures being called from the Inner SP above (sp_bm_add_Note). If required I could add this code to the ticket as well

Oleksandr Byelkin added a comment - 2020-07-21 11:08

In ideal world I need some instructions or SQL sequence to repeat the crash to fix it. So far I could not.

Oleksandr Byelkin added a comment - 2020-07-21 11:08 In ideal world I need some instructions or SQL sequence to repeat the crash to fix it. So far I could not.

Björn Möller added a comment - 2020-07-21 17:13

If required I could always upload my database. However, since it's about genealogy it contains information about now living people.
I will probably need a couple of days to make the testdata compliant with GDPR before I could upload it.

Björn Möller added a comment - 2020-07-21 17:13 If required I could always upload my database. However, since it's about genealogy it contains information about now living people. I will probably need a couple of days to make the testdata compliant with GDPR before I could upload it.

Oleksandr Byelkin added a comment - 2020-07-22 05:33

Usually to repeat the crash whole dataset is not needed.

We need schema of used tables (you can even rename fields if it is important, but in this case it shoud be checked) and tables filled with random junk. Of course there are cases when data distributin is important but statistically it is rare case and we can go step by step.

So if you have time send please SHOW CREATE TABLE output (or any other way to get the info) for used by Stored procedure tables (if some of them has foreign keys then connected tables also). If you have more time you can check that stored procedures at least pass syntaxically with the empty table created.

Even more helpful will be if you fill the tables with some test data and get crash.

But I will be happy if I get even minimum info above mentioned.

Oleksandr Byelkin added a comment - 2020-07-22 05:33 Usually to repeat the crash whole dataset is not needed. We need schema of used tables (you can even rename fields if it is important, but in this case it shoud be checked) and tables filled with random junk. Of course there are cases when data distributin is important but statistically it is rare case and we can go step by step. So if you have time send please SHOW CREATE TABLE output (or any other way to get the info) for used by Stored procedure tables (if some of them has foreign keys then connected tables also). If you have more time you can check that stored procedures at least pass syntaxically with the empty table created. Even more helpful will be if you fill the tables with some test data and get crash. But I will be happy if I get even minimum info above mentioned.

Julien Fritsch made changes - 2020-07-24 08:21

Labels

innodb

innodb need_feedback

Björn Möller added a comment - 2020-07-24 12:30 - edited

I have now prepared a test database with GDPR compliant data.

Is there a way I can upload the database to you without making it available to the public at the same time?
The database is originally from a commercial genealogy product, with my own improvements added.

[EDIT]
I just found the information about uploading files to FTP. I will prepare an upload for you.

Björn Möller added a comment - 2020-07-24 12:30 - edited I have now prepared a test database with GDPR compliant data. Is there a way I can upload the database to you without making it available to the public at the same time? The database is originally from a commercial genealogy product, with my own improvements added. [EDIT] I just found the information about uploading files to FTP. I will prepare an upload for you.

Björn Möller added a comment - 2020-07-24 14:08 - edited

I have now uploaded the test database (with testdata) to FTP (private), file ~~MDEV-23094~~.zip .
You will also find some information and instructions in the Zip.

Kind regards
Björn Möller

Björn Möller added a comment - 2020-07-24 14:08 - edited I have now uploaded the test database (with testdata) to FTP (private), file MDEV-23094 .zip . You will also find some information and instructions in the Zip. Kind regards Björn Möller

Björn Möller made changes - 2020-07-29 11:12

Labels

innodb need_feedback

innodb

Björn Möller added a comment - 2020-07-29 11:14

Did you manage to recreate the error from the database I uploaded?
Is there anything more I can do to help?

Regards
Björn Möller

Björn Möller added a comment - 2020-07-29 11:14 Did you manage to recreate the error from the database I uploaded? Is there anything more I can do to help? Regards Björn Möller

Oleksandr Byelkin added a comment - 2020-07-29 11:40 - edited

Sorry but I had not time yet (I first have to finish the bugs I have started and then will be able to switch to this one). I hope our support will check it (ccalender ping).

Oleksandr Byelkin added a comment - 2020-07-29 11:40 - edited Sorry but I had not time yet (I first have to finish the bugs I have started and then will be able to switch to this one). I hope our support will check it ( ccalender ping).

Björn Möller added a comment - 2020-07-29 17:05

Please take your time. I can manage with MariaDB v 10.3.
I just wanted to check if the database was uploaded correctly to FTP.

Regards
Björn

Björn Möller added a comment - 2020-07-29 17:05 Please take your time. I can manage with MariaDB v 10.3. I just wanted to check if the database was uploaded correctly to FTP. Regards Björn

Oleksandr Byelkin added a comment - 2020-07-30 13:07 - edited

I got the file, restored database.

Now I am trying to guess parameters of sp_bm_copy_EventToMany call to get the crash. If you know fow to get it fast, please say.

Oleksandr Byelkin added a comment - 2020-07-30 13:07 - edited I got the file, restored database. Now I am trying to guess parameters of sp_bm_copy_EventToMany call to get the crash. If you know fow to get it fast, please say.

Björn Möller added a comment - 2020-07-30 14:29 - edited

Please check the text file ('Information & Test instructions.txt") I included in the zip. It contains all information you need to know to do a full test.

Otherwise I will also add the same instructions below as in the text file:

CALL sp_bm_copy_EventToMany (2722, 'tree1', 'i9,i34', 1, '2020-07-24', 'USER');

The same call to the Stored Procedure can be executed several times.

Regards
/Björn

Björn Möller added a comment - 2020-07-30 14:29 - edited Please check the text file (' Information & Test instructions.txt ") I included in the zip. It contains all information you need to know to do a full test. Otherwise I will also add the same instructions below as in the text file: CALL sp_bm_copy_EventToMany (2722, 'tree1', 'i9,i34', 1, '2020-07-24', 'USER'); The same call to the Stored Procedure can be executed several times. Regards /Björn

Oleksandr Byelkin added a comment - 2020-07-30 14:37

Thank you I will check at once...

Oleksandr Byelkin added a comment - 2020-07-30 14:37 Thank you I will check at once...

Oleksandr Byelkin added a comment - 2020-07-30 14:43

repeatable on current 10.4:

#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50

#1  0x00007ffff76a5859 in __GI_abort () at abort.c:79

#2  0x00007ffff76a5729 in __assert_fail_base (fmt=0x7ffff783b588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x555556a9a0b8 "sl->join == 0", file=0x555556a998c0 "sql/sql_prepare.cc", line=3000, function=<optimized out>) at assert.c:92

#3  0x00007ffff76b6f36 in __GI___assert_fail (assertion=0x555556a9a0b8 "sl->join == 0", file=0x555556a998c0 "sql/sql_prepare.cc", line=3000, function=0x555556a9a090 "void reinit_stmt_before_use(THD*, LEX*)") at assert.c:101

#4  0x0000555555da40d3 in reinit_stmt_before_use (thd=0x7fffe0000d90, lex=0x7fffe03bb4f8) at sql/sql_prepare.cc:3000

#5  0x0000555555c877d0 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fffe03c2a80, thd=0x7fffe0000d90, nextp=0x7ffff1684b0c, open_tables=true, instr=0x7fffe03c2a28) at sql/sp_head.cc:3392

#6  0x0000555555c8958d in sp_instr_jump_if_not::execute (this=0x7fffe03c2a28, thd=0x7fffe0000d90, nextp=0x7ffff1684b0c) at sql/sp_head.cc:3963

#7  0x0000555555c81846 in sp_head::execute (this=0x7fffe0389588, thd=0x7fffe0000d90, merge_da_on_success=true) at sql/sp_head.cc:1364

#8  0x0000555555c845fe in sp_head::execute_procedure (this=0x7fffe0389588, thd=0x7fffe0000d90, args=0x7fffe0361010) at sql/sp_head.cc:2370

#9  0x0000555555d784e9 in do_execute_sp (thd=0x7fffe0000d90, sp=0x7fffe0389588) at sql/sql_parse.cc:3005

#10 0x0000555555d7919b in Sql_cmd_call::execute (this=0x7fffe034def0, thd=0x7fffe0000d90) at sql/sql_parse.cc:3247

#11 0x0000555555d83465 in mysql_execute_command (thd=0x7fffe0000d90) at sql/sql_parse.cc:6098

#12 0x0000555555c885c2 in sp_instr_stmt::exec_core (this=0x7fffe0353d20, thd=0x7fffe0000d90, nextp=0x7ffff168607c) at sql/sp_head.cc:3687

#13 0x0000555555c8794b in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fffe0353d68, thd=0x7fffe0000d90, nextp=0x7ffff168607c, open_tables=false, instr=0x7fffe0353d20) at sql/sp_head.cc:3419

#14 0x0000555555c8816a in sp_instr_stmt::execute (this=0x7fffe0353d20, thd=0x7fffe0000d90, nextp=0x7ffff168607c) at sql/sp_head.cc:3593

#15 0x0000555555c81846 in sp_head::execute (this=0x7fffe0333c78, thd=0x7fffe0000d90, merge_da_on_success=true) at sql/sp_head.cc:1364

#16 0x0000555555c845fe in sp_head::execute_procedure (this=0x7fffe0333c78, thd=0x7fffe0000d90, args=0x7fffe0005b80) at sql/sp_head.cc:2370

#17 0x0000555555d784e9 in do_execute_sp (thd=0x7fffe0000d90, sp=0x7fffe0333c78) at sql/sql_parse.cc:3005

#18 0x0000555555d7919b in Sql_cmd_call::execute (this=0x7fffe0013590, thd=0x7fffe0000d90) at sql/sql_parse.cc:3247

#19 0x0000555555d83465 in mysql_execute_command (thd=0x7fffe0000d90) at sql/sql_parse.cc:6098

#20 0x0000555555d88a0b in mysql_parse (thd=0x7fffe0000d90, rawbuf=0x7fffe0013438 "CALL sp_bm_copy_EventToMany (2722, 'tree1', 'i9,i34', 1, '2020-07-24', 'USER')", length=78, parser_state=0x7ffff1687550, is_com_multi=false, is_next_command=false) at sql/sql_parse.cc:7896

#21 0x0000555555d74f1b in dispatch_command (command=COM_QUERY, thd=0x7fffe0000d90, packet=0x7fffe0162a31 "CALL sp_bm_copy_EventToMany (2722, 'tree1', 'i9,i34', 1, '2020-07-24', 'USER')", packet_length=78, is_com_multi=false, is_next_command=false) at sql/sql_parse.cc:1834

#22 0x0000555555d73783 in do_command (thd=0x7fffe0000d90) at sql/sql_parse.cc:1352

#23 0x0000555555f0051b in do_handle_one_connection (connect=0x5555580eb180) at sql/sql_connect.cc:1412

#24 0x0000555555f00264 in handle_one_connection (arg=0x5555580eb180) at sql/sql_connect.cc:1316

#25 0x0000555556916172 in pfs_spawn_thread (arg=0x5555580389d0) at storage/perfschema/pfs.cc:1869

#26 0x00007ffff7f37609 in start_thread (arg=<optimized out>) at pthread_create.c:477

#27 0x00007ffff77a2103 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Oleksandr Byelkin added a comment - 2020-07-30 14:43 repeatable on current 10.4: #0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50 #1 0x00007ffff76a5859 in __GI_abort () at abort.c:79 #2 0x00007ffff76a5729 in __assert_fail_base (fmt=0x7ffff783b588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x555556a9a0b8 "sl->join == 0", file=0x555556a998c0 "sql/sql_prepare.cc", line=3000, function=<optimized out>) at assert.c:92 #3 0x00007ffff76b6f36 in __GI___assert_fail (assertion=0x555556a9a0b8 "sl->join == 0", file=0x555556a998c0 "sql/sql_prepare.cc", line=3000, function=0x555556a9a090 "void reinit_stmt_before_use(THD*, LEX*)") at assert.c:101 #4 0x0000555555da40d3 in reinit_stmt_before_use (thd=0x7fffe0000d90, lex=0x7fffe03bb4f8) at sql/sql_prepare.cc:3000 #5 0x0000555555c877d0 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fffe03c2a80, thd=0x7fffe0000d90, nextp=0x7ffff1684b0c, open_tables=true, instr=0x7fffe03c2a28) at sql/sp_head.cc:3392 #6 0x0000555555c8958d in sp_instr_jump_if_not::execute (this=0x7fffe03c2a28, thd=0x7fffe0000d90, nextp=0x7ffff1684b0c) at sql/sp_head.cc:3963 #7 0x0000555555c81846 in sp_head::execute (this=0x7fffe0389588, thd=0x7fffe0000d90, merge_da_on_success=true) at sql/sp_head.cc:1364 #8 0x0000555555c845fe in sp_head::execute_procedure (this=0x7fffe0389588, thd=0x7fffe0000d90, args=0x7fffe0361010) at sql/sp_head.cc:2370 #9 0x0000555555d784e9 in do_execute_sp (thd=0x7fffe0000d90, sp=0x7fffe0389588) at sql/sql_parse.cc:3005 #10 0x0000555555d7919b in Sql_cmd_call::execute (this=0x7fffe034def0, thd=0x7fffe0000d90) at sql/sql_parse.cc:3247 #11 0x0000555555d83465 in mysql_execute_command (thd=0x7fffe0000d90) at sql/sql_parse.cc:6098 #12 0x0000555555c885c2 in sp_instr_stmt::exec_core (this=0x7fffe0353d20, thd=0x7fffe0000d90, nextp=0x7ffff168607c) at sql/sp_head.cc:3687 #13 0x0000555555c8794b in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fffe0353d68, thd=0x7fffe0000d90, nextp=0x7ffff168607c, open_tables=false, instr=0x7fffe0353d20) at sql/sp_head.cc:3419 #14 0x0000555555c8816a in sp_instr_stmt::execute (this=0x7fffe0353d20, thd=0x7fffe0000d90, nextp=0x7ffff168607c) at sql/sp_head.cc:3593 #15 0x0000555555c81846 in sp_head::execute (this=0x7fffe0333c78, thd=0x7fffe0000d90, merge_da_on_success=true) at sql/sp_head.cc:1364 #16 0x0000555555c845fe in sp_head::execute_procedure (this=0x7fffe0333c78, thd=0x7fffe0000d90, args=0x7fffe0005b80) at sql/sp_head.cc:2370 #17 0x0000555555d784e9 in do_execute_sp (thd=0x7fffe0000d90, sp=0x7fffe0333c78) at sql/sql_parse.cc:3005 #18 0x0000555555d7919b in Sql_cmd_call::execute (this=0x7fffe0013590, thd=0x7fffe0000d90) at sql/sql_parse.cc:3247 #19 0x0000555555d83465 in mysql_execute_command (thd=0x7fffe0000d90) at sql/sql_parse.cc:6098 #20 0x0000555555d88a0b in mysql_parse (thd=0x7fffe0000d90, rawbuf=0x7fffe0013438 "CALL sp_bm_copy_EventToMany (2722, 'tree1', 'i9,i34', 1, '2020-07-24', 'USER')", length=78, parser_state=0x7ffff1687550, is_com_multi=false, is_next_command=false) at sql/sql_parse.cc:7896 #21 0x0000555555d74f1b in dispatch_command (command=COM_QUERY, thd=0x7fffe0000d90, packet=0x7fffe0162a31 "CALL sp_bm_copy_EventToMany (2722, 'tree1', 'i9,i34', 1, '2020-07-24', 'USER')", packet_length=78, is_com_multi=false, is_next_command=false) at sql/sql_parse.cc:1834 #22 0x0000555555d73783 in do_command (thd=0x7fffe0000d90) at sql/sql_parse.cc:1352 #23 0x0000555555f0051b in do_handle_one_connection (connect=0x5555580eb180) at sql/sql_connect.cc:1412 #24 0x0000555555f00264 in handle_one_connection (arg=0x5555580eb180) at sql/sql_connect.cc:1316 #25 0x0000555556916172 in pfs_spawn_thread (arg=0x5555580389d0) at storage/perfschema/pfs.cc:1869 #26 0x00007ffff7f37609 in start_thread (arg=<optimized out>) at pthread_create.c:477 #27 0x00007ffff77a2103 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Oleksandr Byelkin added a comment - 2020-08-10 12:52

Here is a normal size test suite for the bug:

create table t1 (id1 int primary key, data1 int);

create table t2 (id2 int primary key, data2 int);

delimiter //;

create procedure p1(IN id int, IN dt int)

begin

  if (exists(select * from t1 where id1 = id and data1 = dt) or

      not exists (select * from t2 where id2 = id and data2 = dt))

  then

      select 1;

  end if;

end //

delimiter ;//

call p1(1,2);

call p1(1,2);

drop procedure p1;

drop table t1,t2;

Oleksandr Byelkin added a comment - 2020-08-10 12:52 Here is a normal size test suite for the bug: create table t1 (id1 int primary key, data1 int); create table t2 (id2 int primary key, data2 int); delimiter //; create procedure p1(IN id int, IN dt int) begin if (exists(select * from t1 where id1 = id and data1 = dt) or not exists (select * from t2 where id2 = id and data2 = dt)) then select 1; end if; end // delimiter ;// call p1(1,2); call p1(1,2); drop procedure p1; drop table t1,t2;

Oleksandr Byelkin added a comment - 2020-08-10 13:14

bjomol if you need workaround, you can move condition with EXISTS/OR/NOT EXISTS (whall condition) out of IF.

i.e. make something like:

...

declare cond int;

set cond= ((EXISTS(SELECT...) AND EXISTS(SELECT...)) OR NOT EXISTS (SELECT ...));

IF (cond)

...

Oleksandr Byelkin added a comment - 2020-08-10 13:14 bjomol if you need workaround, you can move condition with EXISTS/OR/NOT EXISTS (whall condition) out of IF. i.e. make something like: ... declare cond int; set cond= ((EXISTS(SELECT...) AND EXISTS(SELECT...)) OR NOT EXISTS (SELECT ...)); IF (cond) ...

Oleksandr Byelkin added a comment - 2020-08-10 15:56

The problem is that SELECT_LEX of the subquery is not connected to main UNION, and so is not cleaned up properly...

Oleksandr Byelkin added a comment - 2020-08-10 15:56 The problem is that SELECT_LEX of the subquery is not connected to main UNION, and so is not cleaned up properly...

Oleksandr Byelkin added a comment - 2020-08-11 06:20 - edited

yet another test case:

delimiter //;

create procedure p1(IN id int, IN dt int)

begin

case (exists(select * from t1 where id1 = id and data1 = dt) or

      not exists (select * from t2 where id2 = id and data2 = dt))

when 1 then

  select 1;

else

  select 0;

end case;

end //

delimiter ;//

call p1(1,2);

call p1(1,2);

drop table t1,t2;

Oleksandr Byelkin added a comment - 2020-08-11 06:20 - edited yet another test case: delimiter //; create procedure p1(IN id int, IN dt int) begin case (exists(select * from t1 where id1 = id and data1 = dt) or not exists (select * from t2 where id2 = id and data2 = dt)) when 1 then select 1; else select 0; end case; end // delimiter ;// call p1(1,2); call p1(1,2); drop table t1,t2;

Oleksandr Byelkin added a comment - 2020-08-11 06:25 - edited

delimiter //;

create procedure p1(IN id int, IN dt int)

begin

while (exists(select * from t1 where id1 = id and data1 = dt) or

      not exists (select * from t2 where id2 = id and data2 = dt))

DO

  select 1;

end while;

end //

delimiter ;//

call p1(1,2);

call p1(1,2);

Oleksandr Byelkin added a comment - 2020-08-11 06:25 - edited delimiter //; create procedure p1(IN id int, IN dt int) begin while (exists(select * from t1 where id1 = id and data1 = dt) or not exists (select * from t2 where id2 = id and data2 = dt)) DO select 1; end while; end // delimiter ;// call p1(1,2); call p1(1,2);

Oleksandr Byelkin added a comment - 2020-08-11 06:29

delimiter //;

create procedure p1(IN id int, IN dt int)

begin

repeat

  select 1;

until (exists(select * from t1 where id1 = id and data1 = dt) or

      not exists (select * from t2 where id2 = id and data2 = dt))

end repeat;

end //

delimiter ;//

call p1(1,2);

call p1(1,2);

Oleksandr Byelkin added a comment - 2020-08-11 06:29 delimiter //; create procedure p1(IN id int, IN dt int) begin repeat select 1; until (exists(select * from t1 where id1 = id and data1 = dt) or not exists (select * from t2 where id2 = id and data2 = dt)) end repeat; end // delimiter ;// call p1(1,2); call p1(1,2);

Oleksandr Byelkin added a comment - 2020-08-11 06:31

delimiter //;

create procedure p1(IN id int, IN dt int)

begin

for i in 1..(exists(select * from t1 where id1 = id and data1 = dt) or

             not exists (select * from t2 where id2 = id and data2 = dt))

do

  select 1;

end for;

end //

delimiter ;//

call p1(1,2);

call p1(1,2);

Oleksandr Byelkin added a comment - 2020-08-11 06:31 delimiter //; create procedure p1(IN id int, IN dt int) begin for i in 1..(exists(select * from t1 where id1 = id and data1 = dt) or not exists (select * from t2 where id2 = id and data2 = dt)) do select 1; end for; end // delimiter ;// call p1(1,2); call p1(1,2);

Oleksandr Byelkin added a comment - 2020-08-11 14:39

ommit 1b61b41d6aaffebedbad330a84b5196091724956 (HEAD ~~> bb-10.4MDEV-23094~~, 10.4)
Author: Oleksandr Byelkin <sanja@mariadb.com>
Date: Tue Aug 11 16:37:48 2020 +0200

~~MDEV-23094~~: Multiple calls to a Stored Procedure from another Stored Procedure crashes server

Added system-SELECT to IF/WHILE/REPET/FOR for correct subqueries connecting.

Added control of system/usual selects for correct error detection.

Oleksandr Byelkin added a comment - 2020-08-11 14:39 ommit 1b61b41d6aaffebedbad330a84b5196091724956 (HEAD > bb-10.4 MDEV-23094 , 10.4) Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Tue Aug 11 16:37:48 2020 +0200 MDEV-23094 : Multiple calls to a Stored Procedure from another Stored Procedure crashes server Added system-SELECT to IF/WHILE/REPET/FOR for correct subqueries connecting. Added control of system/usual selects for correct error detection.

Oleksandr Byelkin made changes - 2020-08-11 14:42

Assignee	Oleksandr Byelkin [ sanja ]	Alexander Barkov [ bar ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Alexander Barkov added a comment - 2020-08-13 08:17

This script also crashes the server:

create or replace table t1 (id1 int primary key, data1 int);

create or replace table t2 (id2 int primary key, data2 int);

delimiter //

set sql_mode=ORACLE;

create or replace procedure p1(id int, dt int) as

begin

  while (1)

  loop

    exit when (exists(select * from t1 where id1 = id and data1 = dt) or

               not exists (select * from t2 where id2 = id and data2 = dt));

  end loop;

end;

//

delimiter ;

call p1(1,2);

call p1(1,2);

Alexander Barkov added a comment - 2020-08-13 08:17 This script also crashes the server: create or replace table t1 (id1 int primary key , data1 int ); create or replace table t2 (id2 int primary key , data2 int ); delimiter // set sql_mode=ORACLE; create or replace procedure p1(id int , dt int ) as begin while (1) loop exit when (exists( select * from t1 where id1 = id and data1 = dt) or not exists ( select * from t2 where id2 = id and data2 = dt)); end loop; end ; // delimiter ; call p1(1,2); call p1(1,2);

Alexander Barkov added a comment - 2020-08-13 09:28

Review done. The patch is generally OK. A few comments made on slack.

Alexander Barkov added a comment - 2020-08-13 09:28 Review done. The patch is generally OK. A few comments made on slack.

Alexander Barkov made changes - 2020-08-13 09:28

Assignee	Alexander Barkov [ bar ]	Oleksandr Byelkin [ sanja ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2020-08-28 14:08

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2020-09-01 06:54

Fix Version/s		10.4.15 [ 24507 ]
Fix Version/s		10.5.6 [ 24508 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]

Oleksandr Byelkin made changes - 2020-09-01 06:56

Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Oleksandr Byelkin made changes - 2020-09-01 06:56

Component/s		Parser [ 10201 ]
Component/s	Stored routines [ 13905 ]

soohyun added a comment - 2020-09-28 07:57

Hello.

In the case of MariaDB 10.4.13 in Win64 (windows server 2019) environment on our test development server, MariaDB forcibly encountered a shutdown issue when executing multiple calling procedures.

You said there was no problem in version 10.3.
We also downgraded to version 10.3.24, but the same problem occurred.

Will there be a stable version 10.3?

soohyun added a comment - 2020-09-28 07:57 Hello. In the case of MariaDB 10.4.13 in Win64 (windows server 2019) environment on our test development server, MariaDB forcibly encountered a shutdown issue when executing multiple calling procedures. You said there was no problem in version 10.3. We also downgraded to version 10.3.24, but the same problem occurred. Will there be a stable version 10.3?

Ralf Gebhardt made changes - 2020-10-06 18:35

Fix Version/s

10.5.7 [ 25019 ]

Ralf Gebhardt made changes - 2020-10-06 18:38

Fix Version/s

10.5.6 [ 24508 ]

Ralf Gebhardt made changes - 2020-10-06 18:40

Fix Version/s

10.4.16 [ 25020 ]

Ralf Gebhardt made changes - 2020-10-06 18:42

Fix Version/s

10.4.15 [ 24507 ]

Alice Sherepa made changes - 2020-10-08 09:13

Link

This issue is duplicated by ~~MDEV-23914~~ [ ~~MDEV-23914~~ ]

Oleksandr Byelkin added a comment - 2020-10-08 09:40

Han Are you sure that you have this problem? You probably have to make bugreport of your problem.

Oleksandr Byelkin added a comment - 2020-10-08 09:40 Han Are you sure that you have this problem? You probably have to make bugreport of your problem.

Sergei Golubchik made changes - 2020-10-08 11:07

Link

This issue duplicates ~~MDEV-20290~~ [ ~~MDEV-20290~~ ]

Sergei Golubchik made changes - 2020-10-08 11:07

Link

This issue relates to ~~MDEV-20290~~ [ ~~MDEV-20290~~ ]

Elena Stepanova made changes - 2020-10-27 12:21

Link

This issue relates to ~~MDEV-22000~~ [ ~~MDEV-22000~~ ]

Elena Stepanova made changes - 2020-10-27 12:21

Link

This issue relates to ~~MDEV-22039~~ [ ~~MDEV-22039~~ ]

Sergei Golubchik made changes - 2021-05-05 16:12

Link

This issue causes ~~MDEV-25601~~ [ ~~MDEV-25601~~ ]

Sergei Golubchik made changes - 2021-12-06 21:51

Workflow

MariaDB v3 [ 110867 ]

MariaDB v4 [ 158058 ]

Jira Automation (IT) made changes - 2024-07-04 03:03

Zendesk Related Tickets

191933

MariaDB Server

Multiple calls to a Stored Procedure from another Stored Procedure crashes server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration