0

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.

tach
  • 131
  • 3
  • I think you need to bite-the-bullet and edit all the files. If you are on Linux, you could write a `sed` script (one-line, I think) to do all the editing in a single command. (I don't know of an equivalent tool on Windows.) – Rick James Apr 18 '22 at 16:22

3 Answers3

1

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
Rick James
  • 2,463
  • 1
  • 6
  • 13
  • adding quotes is more dangerous than renaming the column and the variables, though it is also quite a bit of work. Also, sed is definitely not ideal for the task, I'd recommend vscode and its replace function - if anybody would be interested in such approach. Nevertheless I don't think i wanna go that way - at least for now. If there isn't compatibility layer for mariadb, then I might consider it. – tach Apr 19 '22 at 20:38
1

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`/
markusjm
  • 328
  • 1
  • 6
0

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?

John Mahowald
  • 32,050
  • 2
  • 19
  • 34
  • And if it weren't "row_nnumber", it might be something else. And the next version will take some other name that you might be using. – Rick James Apr 18 '22 at 20:46