Store procedure runs but doesn't run it depends how to start it

Describe the bug

I have a stored procedure that will work or not work depending on how it is started. So the parameter is the same and without changing the stored procedure and without changing the data in the tables.

To Reproduce

I've written a stored procedure (all code below) and if I run it by typing in a call statement it doesn't work. call getSettingsUploadStock("fred")

Error

Static analysis:

1 errors were found during analysis.

Missing expression. (near "ON" at position 25)

SQL query: Edit Edit

SET FOREIGN_KEY_CHECKS = ON;

MySQL said: Documentation #2014 - Commands out of sync; you can't run this command now

However if I click on the left hand side of phpMyAdmin to list my stored procedures and then click on the "execute" link then it will run perfectly fine.

Expected behavior

The stored procedure should run and return 3 tables the first one is empty.

Screenshots

Didn't work by typing call getSettingsUploadStock("fred")

Look at list of stored procedures and see the execute link It worked...

Server configuration

(Note: Don't worry about not having SSL this is on a local test machine, that is not important.)

Operating system: Ubuntu 20.04

Database server Server: Localhost via UNIX socket Server type: MySQL Server connection: SSL is not being used Documentation Server version: 8.0.18 - MySQL Community Server - GPL Protocol version: 10 Server charset: UTF-8 Unicode (utf8mb4)

Web server Apache Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation PHP version: 7.3.11

phpMyAdmin Version information: 4.9.1, latest stable version: 4.9.5

Client configuration

Firefox 80.0.1 Ubuntu 20.04

Additional context

/********************
*   create tables   *
********************/

create database db_settings;

use db_settings;

DROP TABLE IF EXISTS set_upload_type;

CREATE TABLE set_upload_type (
    upl_id INT AUTO_INCREMENT PRIMARY KEY,
    upl_name VARCHAR(255) NOT NULL,
    upl_version VARCHAR(255) not null default '1.0',
    upl_filetype VARCHAR(255) NOT NULL default 'csv',
    upl_has_header boolean not null  default true,
    upl_prefix VARCHAR(255) NOT NULL
); 

insert into set_upload_type (upl_name, upl_version, upl_filetype, upl_has_header, upl_prefix)
values("fred", '1.0', 'csv', true, 'tule'); 

SET @iUploadTypeID := last_insert_id();

DROP TABLE IF EXISTS  set_upload_type_field;

CREATE TABLE set_upload_type_field (
    fld_id INT AUTO_INCREMENT PRIMARY KEY,
    fld_upload_type_id int, 
    fld_name VARCHAR(255) NOT NULL,
    fld_header_text VARCHAR(255) NOT NULL,
    fld_mysql_name VARCHAR(255) NOT NULL,
    fld_field_type VARCHAR(255) NOT NULL
); 

insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'parent_category', 'parent_category', 'parent_category', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'category', 'category', 'category', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'partscode', 'partscode', 'partscode', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'brand_name', 'brand_name', 'brand_name', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'ean', 'ean', 'ean', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'sku', 'sku', 'sku', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'quantity', 'quantity', 'quantity', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'estimated_arrival_date', 'estimated_arrival_date', 'estimated_arrival_date', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'phase_out', 'phase_out', 'phase_out', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'modelname', 'modelname', 'modelname', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'option', 'option', 'option', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'customization', 'customization', 'customization', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'customization_char_limit', 'customization_char_limit', 'customization_char_limit', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'description', 'description', 'description', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'unit_measure', 'unit_measure', 'unit_measure', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'unit_weight', 'unit_weight', 'unit_weight', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'item_length', 'item_length', 'item_length', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'item_height', 'item_height', 'item_height', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'item_width', 'item_width', 'item_width', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'item_weight', 'item_weight', 'item_weight', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'shipping_length', 'shipping_length', 'shipping_length', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'shipping_height', 'shipping_height', 'shipping_height', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'shipping_width', 'shipping_width', 'shipping_width', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'shipping_weight', 'shipping_weight', 'shipping_weight', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'currency', 'currency', 'currency', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'retailprice', 'retailprice', 'retailprice', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'retailspecialprice', 'retailspecialprice', 'retailspecialprice', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'retailexpiredate', 'retailexpiredate', 'retailexpiredate', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'resellerprice', 'resellerprice', 'resellerprice', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'resellerspecialprice', 'resellerspecialprice', 'resellerspecialprice', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'resellerexpiredate', 'resellerexpiredate', 'resellerexpiredate', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'publish_date', 'publish_date', 'publish_date', ''); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_1', 'image_1', 'image_1', 'image'); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_2', 'image_2', 'image_2', 'image'); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_3', 'image_3', 'image_3', 'image'); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_4', 'image_4', 'image_4', 'image'); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_5', 'image_5', 'image_5', 'image'); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_6', 'image_6', 'image_6', 'image'); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_7', 'image_7', 'image_7', 'image'); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_8', 'image_8', 'image_8', 'image'); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_9', 'image_9', 'image_9', 'image'); 
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_10', 'image_10', 'image_10', 'image');

/*****************************************************
*   create stored procedure getSettingsUploadStock   *
*****************************************************/

/*
this call statement should work but doesn't
call getSettingsUploadStock('fred');
*/

DROP PROCEDURE if exists getSettingsUploadStock;

DELIMITER //

CREATE PROCEDURE getSettingsUploadStock(IN p_supplier_name VARCHAR(255))
BEGIN 
DECLARE bIsOk boolean DEFAULT true;  

CREATE TEMPORARY TABLE temp_errors(
    err_ID INT AUTO_INCREMENT PRIMARY KEY,
    err_Category varchar(1024) not null default '',
    err_Name varchar(1024) not null default '',
    err_Long_Description varchar(1024) not null default '',
    err_Values varchar(1024) not null default ''
);

CREATE TEMPORARY TABLE temp_upload_type (
    tmp_id INT AUTO_INCREMENT PRIMARY KEY,
    tmp_name VARCHAR(255) NOT NULL,
    tmp_version VARCHAR(255) not null default '1.0',
    tmp_filetype VARCHAR(255) NOT NULL default 'csv',
    tmp_has_header boolean not null  default true,
    tmp_prefix VARCHAR(255) NOT NULL
);

CREATE TEMPORARY TABLE temp_upload_type_field (
    tmp_id INT AUTO_INCREMENT PRIMARY KEY,
    tmp_supplier_id int, 
    tmp_name VARCHAR(255) NOT NULL,
    tmp_header_text VARCHAR(255) NOT NULL,
    tmp_mysql_name VARCHAR(255) NOT NULL,
    tmp_field_type VARCHAR(255) NOT NULL
);

insert into temp_upload_type (tmp_name, tmp_version, tmp_filetype, tmp_has_header, tmp_prefix) 
select upl_name, upl_version, upl_filetype, upl_has_header, upl_prefix 
from set_upload_type 
where upl_name = p_supplier_name;

if not exists (select * from temp_upload_type) then
    insert into temp_errors (err_Category, err_Name, err_Long_Description, err_Values) 
    values ('settings', 'Cant find supplier', concat('Cant find supplier ', p_supplier_name , ' in table set_upload_type'), p_supplier_name);
    set bIsOk = false;
end if;

if bIsOk then
    insert into temp_upload_type_field (tmp_supplier_id, tmp_name, tmp_header_text, tmp_mysql_name, tmp_field_type) 
    select fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type 
    from set_upload_type_field 
    where fld_upload_type_id in (select tmp_id from temp_upload_type);

    if not exists (select * from temp_upload_type) then
        insert into temp_errors (err_Category, err_Name, err_Long_Description, err_Values) 
        values ('settings', 'Cant find supplier fields', concat('Cant find supplier fields ', p_supplier_name , ' in table set_upload_type_field'), p_supplier_name);
        set bIsOk = false;
    end if;
end if;

select err_ID, err_Category, err_Name, err_Long_Description, err_Values 
from temp_errors;

select tmp_id, tmp_name, tmp_version, tmp_filetype, tmp_has_header, tmp_prefix 
from temp_upload_type;

select tmp_id, tmp_supplier_id, tmp_name, tmp_header_text, tmp_mysql_name, tmp_field_type
from temp_upload_type_field;

END// 

DELIMITER ;