Error in your SQL syntax

[aidhams]

While executing a component of LemonLDAP to purge cache, an error occurs in Apache::Session::Browseable

[root@prdcmcpor ~]# /usr/libexec/lemonldap-ng/bin/purgeCentralCache DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'integer) < 1578912260) AND _session_kind <> 'Persistent'' at line 1 at /usr/share/perl5/vendor_perl/Apache/Session/Browseable/DBI.pm line 117. DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'integer) < 1578912260) AND _session_kind <> 'Persistent'' at line 1 at /usr/share/perl5/vendor_perl/Apache/Session/Browseable/DBI.pm line 117. [root@prdcmcpor ~]#

The database is MySQL 8.16.

[maxbes]

Hi, please provide your LemonLDAP session configuration (Apache::Session module parameters) as well as your LemonLDAP version and the version of Apache::Session::Browsseable you are using

[aidhams]

The LemonLDAP version is 2.0.4 Apache Session Browseable version is 1.3.2

Apache::Session Module parameters Apache Session Module : Apache::Session::Browseable::MySQL Datasource: DBI:mysql:database=lemonldapng;host= Index: _whatToTrace ipAddr _session_kind _utime

[guimard]

Could you also share the table scheme ? (utime field)

[maxbes]

@guimard , @aidhams no need to share the schema, I reproduced the issue.

It seems that on MySQL >=8, cast(_utime as INTEGER) is a syntax error, and we must use cast(_utime as SIGNED INTEGER) instead. I'll send a PR for this, but it will require changing DBI.pm a little bit to make the SQL queries more flexible.

[maxbes]

@guimard @coudot after more extensive testing, it turns out that this issue does NOT ONLY impact MySQL 8 as I first thought, but every MySQL version since 5.5, and probably earlier ones (I haven't tested this far back).

The reason we never noticed this is that MariaDB is not affected, even recent versions. I suppose the

CAST(column AS INTEGER)

syntax has always been a MariaDB extension. We should probably warn our users about that.

[guimard]

Hmm, I think I tested this with MariaDB... Thanks @maxbes !

[coudot]

@guimard we should maybe release a new version?

[coudot]

@guimard I updated all versions in source code, could you check all is ok for a release?

[guimard]

Hi, OK for me, I updated meta.*

[coudot]

Did you push your commit? I see nothing on the repo

[guimard]

Done :wink: