[MDEV-17189] Session variable initialization Created: 2018-09-13  Updated: 2018-09-25  Resolved: 2018-09-25

Status: Closed
Project: MariaDB Server
Component/s: Stored routines, Variables
Affects Version/s: 10.3.9
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Klaus Prünster Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Linux CentOs x64


 Description   

It seems as if session variables in stored procedures are not necessarily initialized with 'NULL' on 'BEGIN'.
Example:

CREATE PROCEDURE UPSERT_CloudGroupUserMap(
IN inGroupName text,
OUT outID int)
proc: BEGIN

SELECT GroupID INTO @GroupId
FROM CloudGroups
WHERE Name = inGroupName;

SET outStatus = @GroupId;
LEAVE proc;
END;

If the query does not return any rows (because the 'Name' does not match), the variable '@GroupId' contains the result of the previous query, instead of 'NULL' which I'd expect.

Workaround:
SET @GroupId = NULL;
before the query.

Any Ideas why this might happen?

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

@GroupId here is user-defined variable, it exists until the session will be closed, so it contains previous results.
maybe you need to declare a local variable in this case. DECLARE

Comment by Klaus Prünster [ 2018-09-17 ]

@Alice Sherepa
I'm calling this stored procedure from a PHP code that looks like this:

$inst = new mysqli($host, $user, $pass, $name);

foreach ($groups as $group)
{
...

$sql = /** @lang MySQL */
"
/** Variables **/
SET @CollectorId = {$collectorId};
SET @UserId = {$userId};
SET @TenantId = {$tenantId};
SET @GroupName = '{$groupName}';

SET @CollectorTime = '{$collectorTime}';

/** Statement begin **/
/** Insert or update Company File **/
CALL UPSERT_CloudGroupUserMap(@UserId, @TenantId, @CollectorId, @GroupName, @CollectorTime, @Status);

SELECT @Status as Status;
";
$inst->multi_query($sql);
}

So in my understanding, my code should actually create a session for each element in the for each, correct?

Comment by Alice Sherepa [ 2018-09-24 ]

AFAIU your code creates a connection to the server and then for each $group it sends a set of queries (sets variables and calls procedure), so all this inside the same connection.

Comment by Klaus Prünster [ 2018-09-25 ]

Okay, now I understand that. Thanks!
Taking this into consideration it seems as if the SELECT does not update the variable (with the INTO statement) if the query is empty.

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