I have a MySql-based application that works fine. It uses stored procs, and all parameter names start with an underscore _
My hosting provider recently "upgraded" my development database from MySql to MariaDB, claiming 100% compatibility. Now I can't even log in on the development system, getting an error that the first-named parameter is "unknown". Testing shows that if I change all the parameter names to start @ instead of _, all works fine.
However to do this would mean updating scores of stored procedures, and hundreds of lines of code, impacting the client's live systems and probably requiring some downtime.
In the longer term, I will ditch the hosting provider who made this change without warning or notification and refuses to accept that MariaDB and MySql are NOT the same thing.
In the short term, to allow me to continue development testing on the MariaDB database, is there a workaround that will allow me to declare a setting per-procedure, or at worst to modify each parameter reference in the procedure, without needing to rename all parameters?
What I have tried:
Renaming all parameters (e.g. _ID to @ID) works but isn't an acceptable solution.
I've tried escaping all parameter names within the stored procedure (e.g. `_ID`) but this does not resolve the problem; still get
SQL Error (1054): Unknown column '_ID' in 'field list'
Simplified version of SP:
CREATE PROCEDURE `spUserLoad`(
IN `_ID` INT,
IN `_UserName` VARCHAR(50)
)
BEGIN
SELECT * FROM tUser
WHERE `_ID` = ID
OR (`_ID` = 0 AND UserName = `_UserName`);
END