MYSQL: weird bug with nested stored procedure

Question

I have two stored procedures. The first one fetch language codes in my language table.

CREATE PROCEDURE `updateCategoryNameAll` (
IN categorieNumber VARCHAR (10), 
IN categoryName VARCHAR (128)
)
BEGIN
DECLARE break BOOLEAN DEFAULT FALSE;
DECLARE languageCode CHAR(5);
DECLARE languageCursor CURSOR FOR
 SELECT `language_code` FROM `ps_lang` WHERE `active` = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET break = TRUE;
DECLARE EXIT HANDLER FOR 1644 RESIGNAL;
OPEN languageCursor;
language_loop: LOOP
 FETCH languageCursor INTO languageCode;
 IF (break) THEN
 LEAVE language_loop;
 ELSE
 CALL updateCategoryName(categorieNumber, categoryName, languageCode);
 END IF;
END LOOP language_loop;
CLOSE languageCursor; 
END//

enter image description here

The second procedure update the category translation based on the selected language.

Here's my problem. In the main procedure, when I'm fetching the languages everything is fine:

enter image description here

enter image description here

But, when I'm trying to fetch the language I want to work with in my nested stored procedure:

SELECT IFNULL(
 ( SELECT `id_category`
 FROM `pc_import_category`
 WHERE `source_category_number` = categorieNumber
 LIMIT 1), 0
 ) AS id_category,
 IFNULL(
 ( SELECT `id_lang`
 FROM `ps_lang`
 WHERE `language_code` = language_code
 LIMIT 1), 0
 ) AS id_lang
 INTO idCategory, idLang; 

Here's the result when I'm fetching for active language in the parent procedure:

enter image description here

There should be only one language (USA english), but it returns both my languages (with the good id, but the second language code should be 'fr-ca'). When I'm fetching for the other language, here's what I got.

enter image description here

On my second iteration (to update the french translation), here's what I got:

enter image description here

enter image description here

As a result, I find myself always updating only my english language (which is the id 1).

Is there some kind of limitation, in MySQL, with the table lock, I'm not aware of?

Thank you

Answer 1

Beware when using the same name:

WHERE `language_code` = language_code

Suggest you precede any arguments or locally DECLAREd variables with a _.

You can probably combine both procedures into a single SQL statement.

• Jonathan Parent Lévesque: You are right about the error. I cannot combine both procedure though before the inner procedure do the data validation and errors managing. In principle, external software should update one translation at the time (the english name of a category is much probably not the same as the chinese or arabic name). If I created the update all procedure, it is mainly because our company main software support only one database translation for product, category, etc. (probably won't change soon). Thank you for your help, I'll be careful next time.