Errors with autoresponses + FAQ + UI

[Naashkyr]

Hello,

We're in a bit of a bind. We were running Znuny 6.4.3, yesterday, the DB server that the OTRS DB was on was updated from MySQL 8.0.32 to 8.0.34. Apparently, Znuny didn't like that and though the autoresponses were still in the DB tables, we couldn't see them listed at all on the interface nor could they be used and would simply return blank info complaining about an SQL syntax error in the logs.

We tried to update to 6.5 (no change), we tried to update from 6.5 to the latest 7.0.13. Now, it's even worse, the interface is completely broken as if there was no CSS, we get errors about the FAQ module not being found, the config files didn't seem to carry over so we had to manually update them.... it is a big mess and we're still getting SQL syntax errors left and right, most of them look like this:

ERROR: Znuny-CGI-10 Perl: 5.16.3 OS: linux Time: Tue Mar 26 17:45:49 2024

Message: Can't locate Kernel/System/Stats/Static/FAQAccess.pm in @INC (@INC contains: /opt/znuny/Custom /opt/znuny/Kernel/cpan-lib /opt/znuny/ /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 . /etc/httpd) at /opt/znuny//Kernel/System/Main.pm line 86.


RemoteAddress: <censored>
RequestURI: /znuny/index.pl

Traceback (4744):
Module: Kernel::System::Stats::GetObjectBehaviours Line: 1130
Module: Kernel::System::Stats::StatsGet Line: 268
Module: Kernel::System::Stats::StatsListGet Line: 745
Module: Kernel::Modules::AgentDashboardCommon::Run Line: 66
Module: Kernel::System::Web::InterfaceAgent::Run Line: 1091
Module: ModPerl::ROOT::ModPerl::Registry::opt_znuny_bin_cgi_2dbin_index_2epl::handler Line: 39
Module: (eval) (v1.99) Line: 207
Module: ModPerl::RegistryCooker::run (v1.99) Line: 207
Module: ModPerl::RegistryCooker::default_handler (v1.99) Line: 173
Module: ModPerl::Registry::handler (v1.99) Line: 32

ERROR: Znuny-CGI-10 Perl: 5.16.3 OS: linux Time: Tue Mar 26 17:46:17 2024

Message: Can't locate Kernel/System/Stats/Static/FAQAccess.pm in @INC (@INC contains: /opt/znuny/Custom /opt/znuny/Kernel/cpan-lib /opt/znuny/ /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 . /etc/httpd) at /opt/znuny//Kernel/System/Main.pm line 86.


RemoteAddress: <censored>
RequestURI: /znuny/index.pl

Traceback (5270):
Module: Kernel::System::Stats::GetObjectBehaviours Line: 1130
Module: Kernel::System::Stats::StatsGet Line: 268
Module: Kernel::System::Stats::StatsListGet Line: 745
Module: Kernel::Modules::AgentDashboardCommon::Run Line: 66
Module: Kernel::System::Web::InterfaceAgent::Run Line: 1091
Module: ModPerl::ROOT::ModPerl::Registry::opt_znuny_bin_cgi_2dbin_index_2epl::handler Line: 39
Module: (eval) (v1.99) Line: 207
Module: ModPerl::RegistryCooker::run (v1.99) Line: 207
Module: ModPerl::RegistryCooker::default_handler (v1.99) Line: 173
Module: ModPerl::Registry::handler (v1.99) Line: 32


We looked around alot but haven't found anything remotely close to what we are experiencing. Any ideas? Thank you for your time.

Edit: I attached an image of what the home page looks like right now.

[Naashkyr]

Trying to install the FAQ module from package manager gives us:

Backend ERROR: Znuny-CGI-10 Perl: 5.16.3 OS: linux Time: Tue Mar 26 17:59:47 2024

Message: 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 'content_type, content, install_status, create_time, create_by, change_time, cha' at line 1, SQL: 'INSERT INTO package_repository (name, version, vendor, filename, content_type, content, install_status, create_time, create_by, change_time, change_by) VALUES (?, ?, ?, ?, 'text/xml', ?, 'not installed', '2024-03-26 17:59:47', 1, '2024-03-26 17:59:47', 1)'

RemoteAddress: <censored>
RequestURI: /znuny/index.pl

Traceback (5514):
Module: Kernel::System::Package::RepositoryAdd Line: 385
Module: Kernel::System::Package::PackageInstall Line: 576
Module: Kernel::Modules::AdminPackageManager::_InstallHandling Line: 1993
Module: Kernel::Modules::AdminPackageManager::Run Line: 848
Module: Kernel::System::Web::InterfaceAgent::Run Line: 1091
Module: ModPerl::ROOT::ModPerl::Registry::opt_znuny_bin_cgi_2dbin_index_2epl::handler Line: 39
Module: (eval) (v1.99) Line: 207
Module: ModPerl::RegistryCooker::run (v1.99) Line: 207
Module: ModPerl::RegistryCooker::default_handler (v1.99) Line: 173
Module: ModPerl::Registry::handler (v1.99) Line: 32

[Naashkyr]

Ended up fixing the interface using the Admin::Config::FixMissingFrontendFiles console command but our autoresponses are still gone...

Even trying to manually add an autoresponse gives us a similar SQL syntax error as before:

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 'content_type, create_time, create_by, change_time, change_by) VALUES' at line 3, SQL: ' INSERT INTO auto_response (name, valid_id, comments, text0, text1, type_id, system_address_id, content_type, create_time, create_by, change_time, change_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, '2024-03-26 18:20:11', ?, '2024-03-26 18:20:11', ?)'

Edit: Furthermore, it seems like we can't create new tickets either...

Seems like everything is related to that broken FAQ module. Is there a way to forcefully uninstall/remove it from the CLI? We don't even use it.

[Naashkyr]

Ok so we managed to remove the FAQ module, but the problem with the SQL syntax everywhere remains and our autoresponses are still broken.

Is there a known incompatibility issue with MySQL 8.0.34 and Znuny?

[root]

Hi,

You probably know it already, but this seems related to issue $548.
We will look into it to see what are the options.

- Roy