MariaDB procedure while loop breaking when looping through more than 1 item

Question

Morning all,

Got a question on MariaDB 10.3 stored procedures. I am trying to generate a procedure that can output a table of items, based on logic in the procedure.

For a game i am designing, the procedure should output random rewards to the player. to generate random items, i have an options table with parameters to use during the creation of the random list.

the procedure should do:

get a random number from 1 to 5 from the database.
loop through the total quantity generated to generate a random item each time
return a list of all items (to php, where i will call the proc from)

1: generate quantity

a variable called '@loopquantity' depicts the number of times an item should be created as player reward. these counts are stored in a table like this:

Item Count	Random number range
1	0\|0.59
2	0.59\|0.84
3	0.84\|0.965
4	0.965\|0.99
5	.99\|1.0

(if you want to test, heres the create + insert):

CREATE TABLE IF NOT EXISTS `options` (
  `option_id` int(11) NOT NULL AUTO_INCREMENT,
  `option_class` varchar(100) NOT NULL,
  `option_name` varchar(500) NOT NULL,
  `option_value` varchar(999) NOT NULL,
  `sorter` int(11) DEFAULT NULL,
  PRIMARY KEY (`option_id`)
) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=latin1;

/*!40000 ALTER TABLE `options` DISABLE KEYS */;
INSERT INTO `options` (`option_id`, `option_class`, `option_name`, `option_value`, `sorter`) VALUES
    (169, 'reward rarity', 'basic', '0|.8', 0),
    (170, 'reward rarity', 'rare', '.8|.95', 1),
    (171, 'reward rarity', 'unique', '.95|1', 2),
    (172, 'reward type', 'weapon', '0|.33', 1),
    (173, 'reward type', 'armor', '0.33|.66', 2),
    (174, 'reward type', 'gadget', '.66|1', 3),
    (175, 'reward count', '1', '0|0.29', 1),
    (176, 'reward count', '2', '0.29|0.84', 2),
    (177, 'reward count', '3', '0.84|0.965', 3),
    (178, 'reward count', '4', '0.965|.99', 4),
    (179, 'reward count', '5', '.99|1', 5);
/*!40000 ALTER TABLE `options` ENABLE KEYS */;

Using MariaDB's RAND() function, i can generate a random number between 0 and 1. Using this lucky number the below piece generates a random quantity from the database.

select loopquantity into @loopquantity  
from (
    select CAST(option_name as int) loopquantity,
           CAST(SUBSTRING_INDEX(option_value,'|',1) as decimal(18,4)) AS random_number_start,
           CAST(SUBSTRING_INDEX(option_value,'|',-1) as decimal(18,4)) AS random_number_end
      from options
      where option_class = 'reward count' and option_name != '1' 
) rr  
inner join (
     select cast(rand() as decimal(18,4)) luckynumber 
) rl on rl.luckynumber > rr.random_number_start and rl.luckynumber <= rr.random_number_end;

When running a select @loopquantity, this will always return an integer between 1 and 5. so far so good. (the reason for getting the dimensions from the options table rather than doing it in code, is that i can easily change the reward count chance when the game is running without having to change the code).

2: loop through items

I would use a while loop now to iterate the actual item creation. First, i create a loop int variable by stating SET @loopstart = 1;. Then, I add a while loop like this:

 WHILE @loopstart <= @loopquantity DO
    [create the item]
    SET @loopstart = @loopstart + 1;
 END WHILE;

3: return data

In order to return data, i create a temp table called results, where each iteration of the while loop can do an insert into. then after the while, i just do select * from results.

the create would look like this:

DROP TEMPORARY TABLE IF EXISTS results;
CREATE TEMPORARY TABLE results (
      item_id          INT default null,
      item_name        VARCHAR(50) default null,
      item_description VARCHAR(500) default null,
      rarity           VARCHAR(10) default null,
      type             VARCHAR(10) default null,
      icon             VARCHAR(100) default null,
      item_level       int default null,
      quantity         int default null,
      loopquantity     int default null
);

complete proc create code (export from phpmyadmin)

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `mission_get_unidentified_rewards`()
BEGIN 
        SET @loopstart = 1;
        
        select loopquantity into @loopquantity
        from (select cast(option_name as int) loopquantity, 
                     CAST(SUBSTRING_INDEX(option_value,'|',1) as decimal(18,4)) AS random_number_start,
                     CAST(SUBSTRING_INDEX(option_value,'|',-1) as decimal(18,4)) AS random_number_end
              from options
              where option_class = 'reward count') rr
        inner join (select cast(rand() as decimal(18,4)) luckynumber) rl on rl.luckynumber > rr.random_number_start and rl.luckynumber <= rr.random_number_end;
        
        if @loopquantity is null then set @loopquantity = 1; end if;
        
        DROP TEMPORARY TABLE IF EXISTS results;
        CREATE TEMPORARY TABLE results (
            item_id INT default null,
            item_name VARCHAR(50) default null,
            item_description VARCHAR(500) default null,
            rarity VARCHAR(10) default null,
            type VARCHAR(10) default null,
            icon VARCHAR(100) default null,
            item_level int default null,
            quantity int default null,
            loopquantity int default null
        );

        WHILE @loopstart <= @loopquantity DO
            insert into results
            with rarity_rates as (
                select option_name rarity, 
                CAST(SUBSTRING_INDEX(option_value,'|',1) as decimal(18,4)) AS random_number_start,
                CAST(SUBSTRING_INDEX(option_value,'|',-1) as decimal(18,4)) AS random_number_end
                from options
                where option_class = 'reward rarity'
            ),
            rarity_luckynum as (
                select cast(rand() as decimal(18,4)) luckynumber
            ),
            rarity as (
                select rarity
                from rarity_rates rr
                inner join rarity_luckynum rl on rl.luckynumber > rr.random_number_start and rl.luckynumber <= rr.random_number_end
            ),
            type_rates as (
                select option_name `type`, 
                CAST(SUBSTRING_INDEX(option_value,'|',1) as decimal(18,4)) AS random_number_start,
                CAST(SUBSTRING_INDEX(option_value,'|',-1) as decimal(18,4)) AS random_number_end
                from options
                where option_class = 'reward type'
            ),
            type_luckynum as (
                select cast(rand() as decimal(18,4)) luckynumber
            ),
            item_type as (
                select `type`
                from type_rates rr
                inner join type_luckynum rl on rl.luckynumber > rr.random_number_start and rl.luckynumber <= rr.random_number_end
            ),
            item_level as (
                select item_level
                from (
                    select option_name item_level, 
                           CAST(SUBSTRING_INDEX(option_value,'|',1) as decimal(18,4)) AS random_number_start,
                           CAST(SUBSTRING_INDEX(option_value,'|',-1) as decimal(18,4)) AS random_number_end
                    from options
                    where option_class = 'reward count'
                ) rr
                inner join (select cast(rand() as decimal(18,4)) luckynumber) rl on rl.luckynumber > rr.random_number_start and rl.luckynumber <= rr.random_number_end

            )
            select gi.item_id, gi.item_name, gi.item_description, gi.rarity, gi.type, gi.icon, item_level, 1 quantity, @loopquantity
            from rarity ra
            cross join item_type it
            cross join item_level il
            inner join game_items gi on ra.rarity = gi.rarity and it.type = gi.type and item_name like 'unidentified%';

            SET @loopstart = @loopstart + 1;
        END WHILE;
        select * from results;
END$$
DELIMITER ;

The problem

When @loopquantity is 1, the proc works as expected. When @loopquantity is higher than 1, it will take a longer time and produce errors:

2 errors were found during analysis.

Unrecognized statement type. (near "WHILE" at position 0)

Unrecognized statement type. (near "END" at position 2584)

MySQL said: Documentation

#2006 - MySQL server has gone away

so the while loop is great, as long as the quantity is 1. When it needs to loop through more than 1, it breaks.

The proc / code breaks when the @loopquantity is > 1. Best i can tell is that the while loop goes on indefintely and therefore MariaDB times out. the code run for 1 iteration is in milliseconds so its not taking multiple seconds per iteration. I can't spot anything wrong with the proc either; the fact that a single run works perfectly shows me that everything works.

Does anyone have a clue why the while breaks when needing to do multiple iterations?

Edit:

for completeness, heres the create and a value for the game_items table:

CREATE TABLE IF NOT EXISTS `game_items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) NOT NULL DEFAULT 0,
  `item_name` varchar(50) DEFAULT NULL,
  `item_description` varchar(250) DEFAULT NULL,
  `faction` int(11) DEFAULT NULL ,
  `icon` varchar(150) DEFAULT NULL,
  `flags` text DEFAULT NULL ,
  `type` varchar(50) DEFAULT NULL ,
  `subtype` varchar(50) DEFAULT NULL ,
  `rarity` varchar(50) DEFAULT NULL ,
  `level_requirement` int(11) DEFAULT NULL,
  `vendor_value` int(11) DEFAULT NULL 
  PRIMARY KEY (`item_id`)
)

INSERT INTO game_items (created_by, item_name, item_description, faction, icon, flags, type, subtype, rarity, level_requirement, vendor_value) VALUES (0, 'M3 trench knife', 'M3 trench knife: U.S. military combat knife used in World War II, with a 6.75-inch blade and a brass knuckle handle.', 1, NULL, NULL, 'Weapon', 'Knife', 'basic', 1, 10);


Also, I have tried to switch the WHILE to a FOR as its numerical between 1 and 5, but i cant get that to work at all. Proc wont save with that.

Can you share the rarity & game_itmes table schema & some values? — MD TAREK HOSSEN, May 01 '23 at 08:44
the rarity and type is in the same create as for the count (under header 1: generate quantity). I added the create and value for game_items in the question — Stephan212, May 01 '23 at 08:49
note that mariadb 10.3 reaches end of life this month; plan to upgrade soon. I would suggest to one of the long term support releases 10.11 or 10.6. — ysth, May 01 '23 at 13:28
@ysth Synology still is on 10.3 with their package for DSM. its only for testing though - free dev environment :) — Stephan212, May 01 '23 at 15:27

MD TAREK HOSSEN · Answer 1 · 2023-05-01T09:54:26.237

0

Use the bellow line of code in the bottom of you mysql config file. ( my.ini ) or simply change the value of max_allowed_packet

max_allowed_packet=10240M

Location depends on your installation and operation system.

For example for Xampp, its located at xampp\mysql\bin\my.ini

Then restart your mysql service

Also you can check by updating global value

SET GLOBAL max_allowed_packet=1073741824;

edited May 01 '23 at 09:54

answered May 01 '23 at 09:38

MD TAREK HOSSEN

129
2
11

To no avail; set it in the my.cnf file (mariadb10 hosted on synology nas) and can confirm the setting in phpmyadmin; still the error " The following query has failed: "CALL `mission_get_unidentified_rewards`();" – Stephan212 May 01 '23 at 15:20
can you explain how max allowed packet is related to this problem? – ysth May 01 '23 at 15:26

Item Count	Random number range
1	0\|0.59
2	0.59\|0.84
3	0.84\|0.965
4	0.965\|0.99
5	.99\|1.0