Stored procedure on OVHCloud MySQL server fails with Unknown column '' in 'field list'



  • 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.



  • 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)));
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2