6.0.30 + Oracle, Initial Problems

[Jholm]

Hi,

I have installed up a fresh new CentOS7 server and installed OTRS CE 6.30 on it.
When I try to install it from installer.pl I get a timeout after a while and I can see that is has created just under 100 tables, nearly 700 constraints but no data in the tables.

If I take the three sql-scripts from the server ..\scripts\database and run them in this order;
1. otrs-schema.oracle.sql
2. otrs-initial_insert.oracle.sql
3. otrs-schema-post.oracle.sql

I get all the tables, the default data and all the contraints, no errors.

But when I try to do the first login with the user root@locahost I get the following error in the log;

[Wed Dec 16 08:16:22.875529 2020] [cgi:error] [pid 3791] [client 172.19.6.186:16271] AH01215: [Wed Dec 16 08:16:22 2020] index.pl: DBD::Oracle::db prepare failed: ORA-00936: uttryck saknas (DBD ERROR: error possibly near <*> indicator at char 57 in 'SELECT pw, id, login FROM users WHERE valid_id IN ( <*>) AND login = 'root@localhost'') [for Statement "SELECT pw, id, login FROM users WHERE valid_id IN ( ) AND login = 'root@localhost'"] at /opt/otrs/bin/cgi-bin/../../Kernel/System/DB.pm line 685

I have correct data in the table valid.
select * from users;

Code: Select all

1	root@localhost	***********		Admin	OTRS	1	2020/12/18 11:36:40	1	2020/12/18 11:36:40	1

select * from valid;

Code: Select all

1	valid	2020/12/18 11:36:40	1	2020/12/18 11:36:40	1
2	invalid	2020/12/18 11:36:40	1	2020/12/18 11:36:40	1
3	invalid-temporarily	2020/12/18 11:36:40	1	2020/12/18 11:36:40	1

If I try to set the password for root@localhost user I get a similar error:

./otrs.Console.pl Admin::User::SetPassword root@localhost
ERROR: OTRS-otrs.Console.pl-Admin::User::SetPassword-10 Perl: 5.16.3 OS: linux Time: Wed Dec 16 08:42:55 2020



Message: No Valid for valid found!



Traceback (4024):
Module: Kernel::System::Valid::ValidLookup Line: 139
Module: Kernel::System::Valid::ValidIDsGet Line: 160
Module: Kernel::System::User::UserSearch Line: 735
Module: Kernel::System::Console::Command::Admin::User::SetPassword::Run Line: 47
Module: (eval) Line: 460
Module: Kernel::System::Console::BaseCommand::Execute Line: 454
Module: Kernel::System::Console::InterfaceConsole::Run Line: 80
Module: ./otrs.Console.pl Line: 36



[Wed Dec 16 09:42:55 2020] otrs.Console.pl: DBD::Oracle::db prepare failed: ORA-00936: uttryck saknas (DBD ERROR: error possibly near <*> indicator at char 103 in 'SELECT id, login
[Wed Dec 16 09:42:55 2020] otrs.Console.pl: FROM users WHERE LOWER(login) LIKE :p1 ESCAPE '\'AND valid_id IN (<*>)') [for Statement "SELECT id, login
[Wed Dec 16 09:42:55 2020] otrs.Console.pl: FROM users WHERE LOWER(login) LIKE ? ESCAPE '\'AND valid_id IN ()"] at /opt/otrs/Kernel/System/DB.pm line 685.
ERROR: OTRS-otrs.Console.pl-Admin::User::SetPassword-10 Perl: 5.16.3 OS: linux Time: Wed Dec 16 08:42:55 2020



Message: ORA-00936: uttryck saknas (DBD ERROR: error possibly near <*> indicator at char 103 in 'SELECT id, login
FROM users WHERE LOWER(login) LIKE :p1 ESCAPE '\'AND valid_id IN (<*>)'), SQL: 'SELECT id, login
FROM users WHERE LOWER(login) LIKE ? ESCAPE '\'AND valid_id IN ()'



Traceback (4024):
Module: Kernel::System::User::UserSearch Line: 740
Module: Kernel::System::Console::Command::Admin::User::SetPassword::Run Line: 47
Module: (eval) Line: 460
Module: Kernel::System::Console::BaseCommand::Execute Line: 454
Module: Kernel::System::Console::InterfaceConsole::Run Line: 80
Module: ./otrs.Console.pl Line: 36



Error: No user found with login 'root@localhost'!

Does anyone know what causes this? Or any suggestions how to solve this?

[jojo]

I strongly advise not to use Oracle as it is the slowest databse of the available ones.

Did you check if your version of the database is supported by OTRS ommunity Edion?

[Jholm]

Hi,

I have currently 6.0.24 CE and 5.0.10 CE running fine against Oracle 19c.
And support for Oracle will end with version 9, right now support for Oracle is 10g or higher.

su -c "./otrs.Console.pl Maint::Database::Check" -s /bin/bash otrs
Trying to connect to database 'DBI:Oracle://<DB-Address>:1521/*******' with user 'otrs'...
Connection successful.

su -c "./otrs.Console.pl Admin::Config::ListInvalid" -s /bin/bash otrs
All settings are valid.

[root]

Hi Johan,

Did you run otrs.Console.pl Maint::Config::Rebuild after the manual installation?

- Roy

[Jholm]

Hi Roy,

No I haven't, tried that now with the following result:

su -c "./otrs.Console.pl Maint::Config::Rebuild" -s /bin/bash otrs
Rebuilding the system configuration...
ERROR: OTRS-otrs.Console.pl-Maint::Config::Rebuild-10 Perl: 5.16.3 OS: linux Time: Fri Dec 18 13:58:58 2020

Message: No Valid for valid found!

Traceback (7297):
Module: Kernel::System::Valid::ValidLookup Line: 139
Module: Kernel::System::Valid::ValidIDsGet Line: 160
Module: Kernel::System::Priority::PriorityList Line: 94
Module: Kernel::System::SysConfig::ValueType::Entity::Priority::EntityValueList Line: 52
Module: Kernel::System::SysConfig::ValueType::Entity::EntityValueList Line: 184
Module: Kernel::System::SysConfig::ValueType::Entity::SettingEffectiveValueCheck Line: 124
Module: Kernel::System::SysConfig::SettingEffectiveValueCheck Line: 1550
Module: Kernel::System::SysConfig::ConfigurationDeploy Line: 3443
Module: Kernel::System::Console::Command::Maint::Config::Rebuild::Run Line: 137
Module: (eval) Line: 460
Module: Kernel::System::Console::BaseCommand::Execute Line: 454
Module: Kernel::System::Console::InterfaceConsole::Run Line: 80
Module: ./otrs.Console.pl Line: 36

[Fri Dec 18 14:58:58 2020] otrs.Console.pl: DBD::Oracle::db prepare failed: ORA-00936: uttryck saknas (DBD ERROR: error possibly near <*> indicator at char 58 in 'SELECT id, name FROM ticket_priority WHERE valid_id IN ( <*>)') [for Statement "SELECT id, name FROM ticket_priority WHERE valid_id IN ( )"] at /opt/otrs/Kernel/System/DB.pm line 685.
ERROR: OTRS-otrs.Console.pl-Maint::Config::Rebuild-10 Perl: 5.16.3 OS: linux Time: Fri Dec 18 13:58:58 2020

Message: ORA-00936: uttryck saknas (DBD ERROR: error possibly near <*> indicator at char 58 in 'SELECT id, name FROM ticket_priority WHERE valid_id IN ( <*>)'), SQL: 'SELECT id, name FROM ticket_priority WHERE valid_id IN ( )'

Traceback (7297):
Module: Kernel::System::Priority::PriorityList Line: 98
Module: Kernel::System::SysConfig::ValueType::Entity::Priority::EntityValueList Line: 52
Module: Kernel::System::SysConfig::ValueType::Entity::EntityValueList Line: 184
Module: Kernel::System::SysConfig::ValueType::Entity::SettingEffectiveValueCheck Line: 124
Module: Kernel::System::SysConfig::SettingEffectiveValueCheck Line: 1550
Module: Kernel::System::SysConfig::ConfigurationDeploy Line: 3443
Module: Kernel::System::Console::Command::Maint::Config::Rebuild::Run Line: 137
Module: (eval) Line: 460
Module: Kernel::System::Console::BaseCommand::Execute Line: 454
Module: Kernel::System::Console::InterfaceConsole::Run Line: 80
Module: ./otrs.Console.pl Line: 36

ERROR: OTRS-otrs.Console.pl-Maint::Config::Rebuild-10 Perl: 5.16.3 OS: linux Time: Fri Dec 18 13:58:58 2020

Message: Setting PostmasterDefaultPriority Effective value is not correct: Entity value is invalid(3 normal)!

Traceback (7297):
Module: Kernel::System::SysConfig::ConfigurationDeploy Line: 3485
Module: Kernel::System::Console::Command::Maint::Config::Rebuild::Run Line: 137
Module: (eval) Line: 460
Module: Kernel::System::Console::BaseCommand::Execute Line: 454
Module: Kernel::System::Console::InterfaceConsole::Run Line: 80
Module: ./otrs.Console.pl Line: 36

Error: There was a problem writing ZZZAAuto.pm.

[Jholm]

su otrs -s /bin/bash -c "/opt/otrs/bin/otrs.Console.pl Maint::Cache::Delete";
su otrs -s /bin/bash -c "/opt/otrs/bin/otrs.Console.pl Maint::Config::Rebuild";

And I got an succeful Rebuild of the Config.

Now everything works! Thanks for the help!