1

I'm using the jaro winkler stored procedure here: https://gist.github.com/TheHiddenHaku/7229861. This runs fine on my local MySQL server (version 8.0.19) but when trying to use the same function on OVHCloud MySQL server (version 8.0.26) it's failing with the error Unknown column '' in 'field list'.

I'm testing with various statements:

select jws('test','test1')
select jws("test","test1")
select jws(myColumn, 'test1') from myTable;

And all queries result in the same error.

5
  • What code line causes this error?
    – Akina
    Jan 12, 2022 at 10:45
  • PS. I cannot find something which may result in shown error. Check does the function text is entered correctly.
    – Akina
    Jan 12, 2022 at 10:52
  • I've since solved this with the help of some folk on the OVH Discord. They're going to post an answer so as to create a little knowledge base on the subject. Thanks for looking :)
    – webnoob
    Jan 12, 2022 at 11:09
  • Please post self-answer and describe the solution, at least briefly. If you can predict where this "little knowledge base" will be published then give the link. This may help somebody else.
    – Akina
    Jan 12, 2022 at 11:15
  • I was waiting for someone else to post - it's all good. Thanks.
    – webnoob
    Jan 12, 2022 at 11:22

1 Answer 1

2

It seems that your server is using sql_mode=''. OVHCloud MySQL server has sql_mode ANSI,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_ALL_TABLES.

ANSI activates the ANSI_QUOTES mode which interprets double quotes (") as fields name. Your jaro winkler stored procedure uses double quotes to define strings string="value" (l.16, 17 and 38).

Without ANSI_QUOTES, MySQL reads: variable string is equal to value.

With ANSI_QUOTES, MySQL reads: variable string is equal to value of field called "value".

You need to update your stored procedure to use single quotes (') for string definition.

...
set common1='';
set common2='';
...
set in2 = concat(substr(in2,1,curSub-1),concat('0',substr(in2,curSub+1,length(in2)-curSub+1)));
2
  • @Akina I posted the comment in the link once we found the solution to this issue so that no one else has the same pain :)
    – webnoob
    Jan 12, 2022 at 11:21
  • ;) I have not payed attention to the fact that doublequotes are used - this is an axiom for me that " may/must be used only in JSON processing...
    – Akina
    Jan 12, 2022 at 11:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.