In the code there are comments like http://bugs.otobo.org/show_bug.cgi?id=2230. These links are broken as bugs.otobo.org is empty. IMHO these links could be reverted back to e.g. http://bugs.otrs.org/show_bug.cgi?id=2230 .

The .gitignore files still mention OTRS.

I typed in bin/otrs.Console.pl Dev::Package::Build ./ and expected to get the name of the .sopm file via bash completion. However it looks like bash completion does not know about the filename argument of the command.

In the Docker-world it is not obvious what the use as the user.host of the DB-User 'otobo'.
A static IP-address is problematic as IP-addresses often change.
A hostname is problematic because then we would have to unset skip-name-resolve.
Currently '%' is used. But is this secure?

The query cache has been removed in MySQL 8. Especially the variable query_cache_size which is mentioned in the installation instructions

For development it would be nice to have a little viewer of the database, e.g. Mojolicious::Plugin::DBViewer. This could be added to otobo.psgi or run in a separate container.

See https://stefanorodighiero.net/writings/perl/composing-multiple-mojolicious-apps-with-plack.html and https://metacpan.org/pod/Mojolicious::Plugin::DBViewer

If I use the script Otobo.CheckModules.pl -list under ubuntu 18.04, I get the following output:

apt-get install -y DateTime::TimeZone libdatetime-perl libdbi-perl libnet-dns-perl libtemplate-perl libtemplate-perl libxml-libxml-perl libyaml-libyaml-perl

• Fix "DateTime::TimeZone" it´s not possible here.

• And if anyone changes it, please adjust -help setting. The description includes all commands with a - (example: -list), but --list also works. I would just change the description to be standard compatible.

• My suggestion is, that the command otobo.CheckModules.pl opens the help page.

• A categorization of the modules during output would be helpful to see what I really need.

Thanks!

Stefan

It would be nice if the AuthSession modules could be reused from non-CGI applications easily. This is already possible, but only if the environment variables REMOTE_AGENT and HTTP_USER_AGENT are set. It would be nicer it these variables were retrieved from the ParamObject.

See bin/mojo/dbviewer.pl for an example where the workaround is needed.

In ./Kernel/System/Console/Command/Dev/Tools/ImportFakeEmails.pm and in ./bin/otobo.SetPermissions.pl there are still variables containing 'Otrs'.

$ENV{SCRIPT_NAME} is used in several places in the code base. My impression is that these usages are not standard compliant, as SCRIPT_NAME can depend on the webserver setup. In any case it would be nice if $ENV{SCRIPT_NAME} would be inspected in a single central location, e.g. in Kernel::System::Web::Request

otobo.CheckModules.pl --cpanfile generates a simple cpanfile with the required modules. The module list is kept in the skript itself. It would be nice to support features in the cpanfile so that users could install modules based on their feature preferences. E.g. cpanm --installdeps --with-feature postgresql --with-feature PSGI .

Why can't I simply call prove scripts/tests for running the tests?

The application bin/cgi-bin/app.psgi is working when Plack is installed. It can be started with plackup bin/cgi-bin/app.psgi.

However there are some issued remaining:

• some static files are not served

• Database connection caching, replacement for Apache::DBI

• Logfiles are not set up

• TODO: check the apache config for missing settings

• Using CGI::Emulate::PSGI is kind of ugly

In MySQL 8, the default authentication plugin has changed from 'mysql_native_password' to 'caching_sha2_password'. The goal is to stay with 'mysql_native_password'.
Also creating users with GRANT is no longer supported.

TODO:

• Check the install docs and the code for database user creation
• Switch to 'CREATE USER ... IDENTIFIED WITH mysql_native_password
• Test with MySQL 8
• Test with MySQL 5.7
• Test with MySQL 5.6
• Maybe test with older MySQL version if they are still relevant.

PerlEx is some Perl-Webserver-Thingy from ActiveState for Windows. Windows is not supported. So get rid of it.

I had a strange setup with additional fields in CustomerCompany. In the config these were marked as readonly. Consequently the additional fields could not be changed. Because of a direkt update in the table customer_company different values were shown in the GUI as were in the table. When updating in the GUI the values from the GUI were inserted in the table.

My guess is that the readonly fields were still POST input fields. And all it takes to make these fields writable is to use a Browser extension that make ro input fields writable. IMHO this is a security bug.

Kernel::System::Environment claims that the package Moo is a bundled module . However there is no bundled Kernel/cpan-lib/Moo.pm. The same applies to namespace::cleanand possibly other modules.

There are two possible fixes:

1. Add Moo and namespace::clean to Kernel/cpan-lib
2. Remove Moo and namespace::clean from Kernel::System::Environmentand add them to bin/otobo.CheckModules.pl as needed modules

I'm not sure what the preferred approach would be. One hunch is that adding the bundled modules to otobo.CheckModules is sensible in any case.

The files COPYING and LICENSE in the OTOBO root are identical. I'm sure whether both have a purpose or one of the files is redundant.

I installed OTOBO under Ubuntu 19.10. One of the suggested commands in the installation guide is a2enmod version This command failed with the message:

ERROR: Module version does not exist!

https://stackoverflow.com/questions/38568364/module-version-in-apache and https://forum.otterhub.org/viewtopic.php?p=161271 told me that the Apache2 module is already compiled in. Therefore I propose to simply remove this instruction.

List::MoreUtils has many useful subroutines. It would be nice to be able these subs in framework and in extension code.

otobo.psgi currently does not provide support for HTTPS. The recommendation for supporting HTTPS access is to run otobo.psgi behind a proxy providing HTTPS. Therefore otobo.psgi should support a reverse proxy setup. Maybe also provide an optional nginx container that is configured with HTTPS support.

Ubuntu 19.10 ships with MySQL 8.0.20. MySQL 8.0 is the successor of MySQL 5.7. Looks like they dropped the 5 in order to confuse people. There are some changes in MySQL 8.0 that are relevant for OTOBO. See also https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html.

The individual topics will be handled in dependent issues:

• #4: Creating users with GRANT no longer works. The default authentication plugin has changed from 'mysql_native_password' to 'caching_sha2_password'
• #6: "groups" is now a reserved word and can no longer be used as an unquoted table name
• #9: The query cache has been removed. Especially the variable query_cache_size which is mentioned in the installation instructions
• The default character_set_server has changed from latin1 to utf8mb4. No action required here as the encoding utf8 is explicitly stated in the CREATE TABLE statements
• The character set utf8mb3, which is aliased by utf8, is now deprecated, no action required for this issue. Switching to utf8mb4 is not in the scope of this issue

When creating the otobo database user it is advisable to explicitly set the authentication plugin mysql_native_password . This avoid surprises when the default authentication plugin changes. E.g. when MySQL 5.7 was upgraded to MySQL 8.

For MySQL 5.7 and MySQL 8 the following statement seems to work.

CREATE USER test1@localhost IDENTIFIED WITH mysql_native_password BY 'xxxx';

However this fails for MariaDB 10.5.3, There the statement:

CREATE USER test1@localhost IDENTIFIED WITH mysql_native_password AS PASSWORD( 'xxxx' );

works.
TODO: Look at $dbh->{'mysql_serverinfo'};and use the appropriat SQL statement.

See also #37.

https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci suggest that utf8mb4_unicode_ci might be a better collation than utf8_general_ci.

In Installer.pm there are SQL-statements first collected and then executed. However the return status of the execution is never inspecter. Thus the intstaller GUI might report everything as successful, even when some tables were not created.

TODO: inspect the return code and stop the installationj when there are errors

Create a Dockerfile that builds an image with OTOBO running with PSGI under Starman using MySQL as backend.
For now it is still expected that 'otobo/installation.pl' will be run by the user.

I had an use case where I wanted to run MySQL in Docker. On the host there was another MySQL running on port 3306. In order to avoid the conflicting port I mapped the port 3306 in the container to the port 5003 on the host. However in installer.pl I could not specify the port.

IMHO it would be a nice feature it the MySQL port could be specified. If the GUI should be kept as simple as possible, then it would be nice if the port could be specified in Config.pm.

ManiaDB 10.5 does not spport the CREATE USER ... IDENTIFIED WITH .. BY ... syntax. An alternative might be to use IDENTIFIED WITH .. AS PASSWORD(...) . This should also work on MySQL 5.7 and MySQL 8.

Some folks like to use cpanm --installdeps . . In order to support this add an option --gen-cpanfile to otobo.CheckModules.pl that prints a cpanfile with all required modules to STDOUT.

I tried to improve the documentation of TicketSearch() because I was confused.

In the mod_perl version for OTOBO the Perl module Apache::DBI. This module enables connection caching. However in otobo.psgi there is no connection caching and there seems to be a reconnect for each request.

In might be worthwhile to use DBIx::Connector in the OTOBO_RUNS_UNDER_PSGI case. Apache::DBImight do the job as well, but that is IMHO confusing.

The sysconfig setting ViewableTicketsPage seems to be no longer used. It looks like the number of displayed tickets is now taken from the user preferences.
I also removed manually the now obsolete translation. There is propably a tool for that, but I'm clueless about it.

https://doc.otobo.org/manual/installation/stable/en/content/requirements.html states that OTOBO requires Perl 5.16.0 . This version of Perl is from 2012 and supporting old Perl versions brings no benefit to OTOBO. Can we bump up the minimum version to Perl 5.24. 5.24 is because I'm a fan of posfix dereferencing.

In Framework.xml there is a setting for SecureMode.

   <Setting Name="SecureMode" Required="1" Valid="1" ConfigLevel="200">
        <Description Translatable="1">Disables the web installer (http://yourhost.example.com/otobo/installer.pl), to prevent the system from being hijacked. If not enabled, the system can be reinstalled and the current basic configuration will be used to pre-populate the questions within the installer script. If enabled, it also disables the GenericAgent, PackageManager and SQL Box.</Description>
        <Navigation>Core</Navigation>
        <Value>
            <Item ValueType="Checkbox">0</Item>
        </Value>
    </Setting>

There it is stated that the SQL Box is disabled when SecureMode is enabled. However I have been able to run a SELECT in the SQL Box after installer.pl has run. I think that the documentation in Framework.xml is outdated.

We incorporated some packages which rely on Kernel::System::Time (or its former duplicate Kernel::System::ZnunyTime). In OTRS Kernel::System::Time is handled as legacy. In principle we are not bound to deprecate it, too, but with Kernel::System::DateTime being present, too, the current situation messy.

In my opinion, at some point we should probably add the missing functionality of Kernel::System::Time to Kernel::System::DateTime, and get rid of the former.

When running installer.pl for a new installation the database host is not yet known. Yet the intro page of the Installer calls $Output .= $LayoutObject->Footer(); Within that call there is an attempt to get the list of states from the otobo database. However the Database connect fails when the MySQL database does not run on the local host.

For some reason both XML::LibXML and XML::Parser are used in OTOBO. I propose to standardise on XML::LibXML.

https://lists.otrs.org/pipermail/otrs/2013-July/040302.html states that rpc.pl is deprecated. IMHO this means that it can be removed in OTOBO.
rpc.pl is already not supported in FastCGI and in PSGI.

The current bin/docker/entrypoint.sh runs under der user 'otobo' which can't, and shouldn't, start the Cron Daemon. This means that currently no cronjobs are executed in the docker container.

Therefore I propose to forego cronjobs and watch the Daemon.pl with a tool like supervisor. The downside it that no othere cronjobs can be supported. However my understanding is that all periodic actions should be handled by the Daemon anyways.

In the current docker setup there is no rsyslog running on otobo_web_1. The logging method should therefore be logging to a file. The Docker philosophy is to log to STDOUT. Thus the default logfile should be /proc/self/fd/1 (STDOUT) or /proc/self/fd/2 (STDERR). But before changing the defaults, this approach needs to be tested first.

The scripts assume that the system Perl in in /usr/bin/perl. However the official Perl docker image has perl in /usr/local/bin. Or some users might want to use perlbrew.

I suggest to change the Shebang to `#!/usr/bin/env perl'

When looking at the code for CustomerCompanySearchDetail() I found that CustomerCompany::EnhancedSearchFields are mentioned in the POD. This seems to be a leftover from a previous implementation, as the code indicates that the search fields are taken from the CustomerCompanyMaps.

Same applies to CustomerUser.

Currently /otobo/dbviewer redirects to /otobo/index.pl when authentication fails. It would be nice to distinguish the two cases:

1. Access denied because the user is not logged in
2. Access denied because the user is logged in but not a admin.

For case 1. it would be nice to have a redirect to a login page that redirects back to the wanted page. Currently this is not possible as the GET parameter RequestedURL is expected to be a query string only. Thus '../dbviewer' can't be used.

My Understanding is that SecureMode = 0 indicates that OTOBO is being configured. In this situation running the Daemon might be problematic. How about adding a check for SecureMode that exits the Daemon when SecureMode = 0?
Alternatively Monitor Kernel/Config.pm and Reload it when it is changed. This could be done with Module::Refresh, or Module::Reload, or with Linux::Inotify2.

Kernel::System::CustomerUser handles registered event, but Kernel::System::User doesn't provide support for events. It might be useful to add that capability.

A version check of the database would be nice to have.

There are some checks for MicroSoft webservers. These checks can be removed as Windows is not supported.

Sending and receiving mails will most likely not work yet. Propably the ports for SMTP need to be opened and maybe we need a container running postfix.

'groups' is a reserved word in MySQL 8. The solution is to rename the table 'groups' to 'groups_table'.

TODO:

• adapt the table creation in the table creation step during installation
• rename 'groups' to 'groups_table' in the OTOBO Perl modules
• rename 'groups' to 'groups_table' in the OTOBO test scripts
• migrate 'groups' to 'groups_table' during the migration to OTOBO 10

The purpose of the SQL scripts in the dir scripts/database is not obvious. For the installation, the .xml files in scripts/database are used. I did not see where the .sql files are used. I also did not see a generation script, so they might have been updated manually.

• remove the .sql files in case they are not needed

• find are write a generation script in case that they are needed