Mariadb legacy support

I have old version of php site, which uses unescaped 'row_number' column number in its sql queries. It seems in the new version of mariadb there has been new function called "row_number()" added and thus the queries fail. To refactor the whole project is out of the question. Also I don't want to dockerize the whole thing with older version of mariadb. Is there a way to use new mariadb but also use older syntax/function set so there is no collision?

Thanks.

In Linux (or similar), a single command can add the backtics across all your files.

Caution: Do not use this without thorough testing first:

sed  -i  's/row_number/\`row_number\`/ig  *

Notes:

-i  -- update file in-place
\   -- need to avoid the backtics being acted on by the shell
i   -- ignore case
g   -- handle multiple occurrences in a line
*   -- change to specify the files that might need editing

One way of working around these sort of problems is to use the regular expression filter in MariaDB MaxScale. If you know the application uses the ROW_NUMBER name in some specific way, you can define a regular expression that matches it and replaces it with the quoted version.

The obvious downside of this approach is that regular expressions are somewhat tricky to get right and they are not easy to maintain if the pattern to be matched gets complex. However, this might allow your application to work while you work on updating your application to a newer version.

Here's an example of how the filter would be configured:

[RegexFilter]
type=filter
module=regexfilter
match=/ROW_NUMBER/
replace=/`ROW_NUMBER`/

Also I don't want to dockerize the whole thing with older version of mariadb.

Good. Using old versions indefinitely is more work than most people realize. Security updates will end, you would need to find an alternative like buy support from someone who can backport fixes. Which is a hard to find skill. And then you are stuck on and old version and don't get new features.

Is there a way to use new mariadb but also use older syntax/function set so there is no collision?

No, row_number is a window function that exists in all supported versions of MariaDB. Unfortunate that you picked the name first, but row_number is a convention among SQL implementations.

Quoting is one syntax to resolve the ambiguity, as you said. This would be a relatively minor refactoring project, no behavior change and no need to touch data or schema. If the developers of your site will not do it, what other more complex maintenance is the code not getting?