Are PreProcessSQL() and PreProcessBindSQL() still needed?

[bschmalhofer]

The methods are only defined only for Kernel::System::DB::mysql. The explicitly encode the strings sent via the MySQL database connection. My hunch is that this might no longer be necessary. Needs to be investigated.

[bschmalhofer]

This is fairly confusing. See perl5-dbi/DBD-mysql#106. According to https://metacpan.org/changes/distribution/DBD-mysql the old buggy version of DBD::mysql was restored in DBD::mysql 4.0.43. This means that the comment in Kernel/System/DB/mysql,pm is obsolete:

da5f5593310 (Martin Gruner    2017-03-13 11:32:41 +0100 106)         # DBD::mysql 4.042+ requires data to be octets, so we encode the data on our own.
da5f5593310 (Martin Gruner    2017-03-13 11:32:41 +0100 107)         #   The mysql_enable_utf8 flag seems to be unusable because it treats ALL data as UTF8 unless
da5f5593310 (Martin Gruner    2017-03-13 11:32:41 +0100 108)         #   it has a custom bind data type like SQL_BLOB.
da5f5593310 (Martin Gruner    2017-03-13 11:32:41 +0100 109)         #
ec15de810d8 (Martin Gruner    2017-03-10 11:41:57 +0100 110)         #   See also https://bugs.otrs.org/show_bug.cgi?id=12677.
ec15de810d8 (Martin Gruner    2017-03-10 11:41:57 +0100 111)         $EncodeObject->EncodeOutput( \$BindRef->[$I] );

My preferred solution is to switch to DBD::MariaDB. See https://metacpan.org/pod/distribution/DBD-MariaDB/lib/DBD/MariaDB.pod#UNICODE-SUPPORT.

One thing that should be done, is to require DBD::mysql 4.0.43+ for OTOBO. This would remove some failure modes.
@svenoe , @StefanRother-OTOBO : Are you OK with requiring minimum version DBD::mysql 4.0.43 ?

[svenoe]

If DBD::mysql 4.0.43 is available for all supported Distros, this can be removed, and 4.0.43+ can be required. (DBD::mariadb should probably be used longterm.)

[bschmalhofer]

DBD::mysql 4.043 was released more than five years ago. I suppose that this version is now available in the usual LTS Linux distributions.

[daxim]

https://pkgs.org/search/?q=dbd-mysql https://repology.org/project/perl:dbd-mysql

ok/nok	distro that is still somehow supported	rel. year	DBD-mysql version
✘	Debian 9 oldoldstable	2017	4.041
✔	Debian 10 oldstable	2019	4.050
✘	CentOS 7	2014	4.023
✔	CentOS 8	2019	4.046
✔	OpenSUSE 15.3	2021	4.046
✘	Ubuntu 14	2014	4.025
✘	Ubuntu 16	2016	4.033
✔	Ubuntu 18	2018	4.046

[bschmalhofer]

Thanks to @daxim for looking that up. So instead of requiring DBD::mysql 4.043 or higher, I'd rather put DBD::mysql 4.042 on the blacklist.

[bschmalhofer]

DBD::mysql 4.042 is now on the blacklist. The SQL preprocessing has been removed. The test suite looks fine, and a quick sanity test looked fine too. Closing this issue.