[Solved] OTRS database update problem

[kkgigiioo]

Dear Members!

I have a problem with you that I would like to upgrade OTRS from 4.0 to 5.0.2. When I try to update the database, I get the following error message:

Code: Select all

Step 3 of 16: Migrate Database Column Types...[Fri Oct 18 12:21:22 2019] DBUpdate-to-5.pl: DBD::Pg::db do failed: ERROR:  cannot alter type of a column used by a view or rule
[Fri Oct 18 12:21:22 2019] DBUpdate-to-5.pl: DETAIL:  rule _RETURN on view jegyek depends on column "id" at /opt/otrs/Kernel/System/DB.pm line 449.
ERROR: OTRS-DBUpdate-to-5.pl-10 Perl: 5.14.2 OS: linux Time: Fri Oct 18 12:21:22 2019

 Message: ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view jegyek depends on column "id", SQL: 'ALTER TABLE ticket ALTER id TYPE BIGINT'

 Traceback (30134):
   Module: main::_MigrateDatabaseColumnTypes Line: 330
   Module: scripts/DBUpdate-to-5.pl Line: 158

ERROR: OTRS-DBUpdate-to-5.pl-10 Perl: 5.14.2 OS: linux Time: Fri Oct 18 12:21:22 2019

 Message: Error during execution of 'ALTER TABLE ticket ALTER id TYPE BIGINT'!

 Traceback (30134):
   Module: main::_MigrateDatabaseColumnTypes Line: 332
   Module: scripts/DBUpdate-to-5.pl Line: 158

error.

[Fri Oct 18 12:21:22 2019] DBUpdate-to-5.pl: Died at scripts/DBUpdate-to-5.pl line 163.

As I see it, it might be a syntactic error, because I need to know the COLUMN expression after the ALTER when changing the attribute type.
I looked at the given line in the /Kernel/System/DB.pl file, but it contains the structure I think.

Please help what's the problem.

Thanks in advance!

Have a nice day!

[jojo]

please use latest version for updates (5.0.38)

also you have to delete your database table view first

[kkgigiioo]

Thanks for the tip!
I downloaded and installed version 5.0.38. My view tables are not in the database so this should not be a problem. However, the problem remains. What has changed is that it now refers to line 462.

I also attach the current error message:

Code: Select all

Migration started...

Step 1 of 19: Refresh configuration cache...
If you see warnings about 'Subroutine Load redefined', that's fine, no need to worry!
done.

Step 2 of 19: Check framework version...[Fri Oct 18 13:04:44 2019] DBUpdate-to-5.pl: Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAuto.pm line 7.
done.

Step 3 of 19: Migrate Database Column Types...[Fri Oct 18 13:04:47 2019] DBUpdate-to-5.pl: DBD::Pg::db do failed: ERROR:  cannot alter type of a column used by a view or rule
[Fri Oct 18 13:04:47 2019] DBUpdate-to-5.pl: DETAIL:  rule _RETURN on view jegyek depends on column "id" at /opt/otrs/Kernel/System/DB.pm line 462.
ERROR: OTRS-DBUpdate-to-5.pl-10 Perl: 5.14.2 OS: linux Time: Fri Oct 18 13:04:47 2019

 Message: ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view jegyek depends on column "id", SQL: 'ALTER TABLE ticket ALTER id TYPE BIGINT'

 Traceback (30549):
   Module: main::_MigrateDatabaseColumnTypes Line: 340
   Module: scripts/DBUpdate-to-5.pl Line: 168

ERROR: OTRS-DBUpdate-to-5.pl-10 Perl: 5.14.2 OS: linux Time: Fri Oct 18 13:04:47 2019

 Message: Error during execution of 'ALTER TABLE ticket ALTER id TYPE BIGINT'!

 Traceback (30549):
   Module: main::_MigrateDatabaseColumnTypes Line: 342
   Module: scripts/DBUpdate-to-5.pl Line: 168

error.

[Fri Oct 18 13:04:47 2019] DBUpdate-to-5.pl: Died at scripts/DBUpdate-to-5.pl line 173.

[jojo]

delete the view as it blocks the upgrade: "rule _RETURN on view jegyek depends on column "id",...."

[kkgigiioo]

Thanks for the help, I found the view boards. After I deleted them, the problem was really solved.