Column name offset is a reserved word in mysql

[deatomium]

This is my first reported issue, sorry if something important is missing.

My script drops an error:
MSG: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'offset int(10) not null, sequence longblob, primary key(id,offset)

In Bio/DB/SeqFeature/Store/DBI/mysql.pm, the sequence table to be created has a column named "offset". In later MySQL versions, OFFSET is a reserved word. MariaDB.pm might be also affected (since 10.6).
As a workaround, placing all occurences of "offset" between backticks, my script works.

(Debian trixie, libbio-db-seqfeature-perl 1.7.5-1, MariaDB 11.8.3)

[moschlar]

I've made this into a bug for the Debian package, too: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=1120035

I can confirm that as a workaround, replacing offset with `offset` in all SQL statements in MariaDB.pm did the trick for us.

I am however reluctant to just offer this as a fix in a PR since it seems like a stylistic mistake to me and I wonder if the authors would rather like to quote all plain names or change the column name offset to something else then.

[carandraug]

Hi. I do not think it's a good idea to change the column name now. Who knows where else it is being used and who's relying on that column name. I'm guessing that an alternative to quote the column name is to reference together with the table name, like so:

SELECT s.offset FROM sequence AS s

However, I do not currently have the means to test a fix for this. Could you please provide a patch?

[carandraug]

This now fixed, thank you @moschlar for the patch. Note that despite the original message, offset is not a reserved a keyword in mysql. This should have only been an issue on MariaDB 10.6 or later.