How to call MySQL 8 stored procedure with several named parameters?

Question

Looking at this very old question: Named parameters for stored procedures in MySQL

I try to call mysql 8 stored procedure with several parameters:

create definer = lardev@localhost procedure sp_getFilteredProductsWithDiscounts(IN in_status varchar(1), IN in_discountPriceAllowed tinyint unsigned, IN in_in_stock varchar(1), IN in_stock_qty mediumint, IN in_discounts_qty mediumint)
BEGIN


SELECT products.id, products.title, products.sale_price,
    GROUP_CONCAT(CONCAT(discounts.name, ': ', discounts.min_qty, ': ', discounts.max_qty, ': ', discounts.percent)) AS discount_info
FROM products
    LEFT JOIN discount_product ON discount_product.product_id = products.id
    LEFT JOIN discounts on discounts.id = discount_product.discount_id
    WHERE ( products.status = in_status OR ISNULL(in_status) ) AND
      ( products.discount_price_allowed = in_discountPriceAllowed OR ISNULL(in_discountPriceAllowed)) AND
      ( products.in_stock = 1 OR ISNULL(in_in_stock) ) AND
      ( products.stock_qty >= in_stock_qty OR ISNULL(in_stock_qty) ) AND
      ( in_discounts_qty BETWEEN discounts.min_qty  AND discounts.max_qty OR ISNULL(in_discounts_qty))
    -- from 200 till 300
    GROUP BY products.id, products.title, products.sale_price;

END;

and calling with named parameters in phpstorm 2023 :

CALL sp_getFilteredProductsWithDiscounts(@in_status := 'A', @in_discountPriceAllowed := 1, @in_in_stock := 1, @in_stock_qty := 2, @in_discounts_qty := 3 );

I got this error :

[HY000][1267] Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

It looks like this:

Why have I got this error and how it can be fixed ?

ADDITIVE INFO : I recreated a database with utf8mb4_unicode_ci collation :

Next I check procedure code :

I recreated the procedure with set @@local.collation_server parameter:

DROP PROCEDURE IF EXISTS sp_getFilteredProductsWithDiscounts;

set @@local.collation_server = utf8mb4_unicode_ci;

create definer = lardev@localhost procedure sp_getFilteredProductsWithDiscounts(IN in_status varchar(1), IN in_discountPriceAllowed tinyint unsigned, IN in_in_stock varchar(1), IN in_stock_qty mediumint, IN in_discounts_qty mediumint)
BEGIN


SELECT products.id, products.title, products.sale_price,
    GROUP_CONCAT(CONCAT(discounts.name, ': ', discounts.min_qty, ': ', discounts.max_qty, ': ', discounts.percent)) AS discount_info
FROM products
    LEFT JOIN discount_product ON discount_product.product_id = products.id
    LEFT JOIN discounts on discounts.id = discount_product.discount_id
    WHERE ( products.status = in_status OR ISNULL(in_status) ) AND
      ( products.discount_price_allowed = in_discountPriceAllowed OR ISNULL(in_discountPriceAllowed)) AND
      ( products.in_stock = 1 OR ISNULL(in_in_stock) ) AND
      ( products.stock_qty >= in_stock_qty OR ISNULL(in_stock_qty) ) AND
      ( in_discounts_qty BETWEEN discounts.min_qty  AND discounts.max_qty OR ISNULL(in_discounts_qty))
    -- from 200 till 300
    GROUP BY products.id, products.title, products.sale_price
    COLLATE utf8mb4_unicode_ci; -- I ADDED COLLATE IN REQUEST MANUALLY

END;

But anyway I got error :

SQL Error [1267] [HY000]: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

In both cases when I run command :

CALL sp_getFilteredProductsWithDiscounts(@in_status := 'A', @in_discountPriceAllowed := 1, @in_in_stock := 1, @in_stock_qty := 2, @in_discounts_qty := 3 );

from phpstorm 2023 or dbeaver Version 23.2.2. Even after I added collate in request manually.

How to fix it ?

OUTPUT :

sp_getFilteredProductsWithDiscounts ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION   "CREATE DEFINER=`lardev`@`localhost` PROCEDURE `sp_getFilteredProductsWithDiscounts`(IN in_status varchar(1), IN in_discountPriceAllowed tinyint unsigned, IN in_in_stock varchar(1), IN in_stock_qty mediumint, IN in_discounts_qty mediumint)
BEGIN


SELECT products.id, products.title, products.sale_price,
    GROUP_CONCAT(CONCAT(discounts.name, ': ', discounts.min_qty, ': ', discounts.max_qty, ': ', discounts.percent)) AS discount_info
FROM products
    LEFT JOIN discount_product ON discount_product.product_id = products.id
    LEFT JOIN discounts on discounts.id = discount_product.discount_id
    WHERE ( products.status = in_status OR ISNULL(in_status) ) AND
      ( products.discount_price_allowed = in_discountPriceAllowed OR ISNULL(in_discountPriceAllowed)) AND
      ( products.in_stock = 1 OR ISNULL(in_in_stock) ) AND
      ( products.stock_qty >= in_stock_qty OR ISNULL(in_stock_qty) ) AND
      ( in_discounts_qty BETWEEN discounts.min_qty  AND discounts.max_qty OR ISNULL(in_discounts_qty))
    -- from 200 till 300
    GROUP BY products.id, products.title, products.sale_price
     COLLATE utf8mb4_unicode_ci;

END"    utf8mb4 utf8mb4_0900_ai_ci  utf8mb4_unicode_ci

Answer 1

The error is basically saying that you are trying to compare 2 values that have different encodings. You can encode a value on the fly, just check the MySQL docs, encode both sides the same and you should be fine.

You'll have to look at the columns to see which ones your comparing and which ones are different.