OTRS 6 (MySQL) migration to OTOBO (PostgreSQL)

[Jeffrey nvt]

Hi,

I’m trying to migrate from OTRS with a MySQL database to OTOBO with a PostgreSQL database. This should be possible following the documentation, only step 4 on migration.pl gives an error.

Copy database.
System was unable to complete data transfer.

Syslog shows two different errors.

permission denied to set parameter „session_replication_role“, SQL: ’set session_replication_role to replica;

Without the superuser role it’s not possible to set parameter „session_replication_role“,  this can be fix with ALTER USER otobo WITH SUPERUSER;. Only even with the superuser role, it still not possible to truncate table ‚article‘.

Mar 31 08:31:55 XXX ?LogPrefix?-40[14257]: [Error][Kernel::System::MigrateFromOTRS::CloneDB::Driver::Base::DataTransfer][Line:636]: ERROR: cannot truncate a table referenced in a foreign key constraint#012DETAIL: Table „ticket_history“ references „article“.#012HINT: Truncate table „ticket_history“ at the same time, or use TRUNCATE … CASCADE., SQL: ‚TRUNCATE TABLE article‘

Mar 31 08:31:55 XXX ?LogPrefix?-40[14257]: [Error][Kernel::System::MigrateFromOTRS::Base::MigrationLog][Line:1172]: Could not truncate target table ‚article‘

Any idea how to fix this?

[Jeffrey nvt]

A workaround for this issue, change line 636 in file „Kernel/System/MigrateFromOTRS/CloneDB/Driver/Base.pm“

Orignal:

my $TrunkateSuccess = $TargetDBObject->Do( SQL => "TRUNCATE TABLE $TargetTable" );

Replace with:

my $TrunkateSuccess = $TargetDBObject->Do( SQL => "TRUNCATE TABLE $TargetTable CASCADE" );

Also be sure that you grant the superuser role to the psql user.

Grant superuser role
ALTER USER otobo WITH SUPERUSER;

Revoke superuser role (after migration):
ALTER USER otobo WITH NOSUPERUSER;

Not sure if I will hit other issues, because the migration process is still running.

[bes]

Also note that this issue should be fixed in OTOBO 10.0.10. See https://github.com/RotherOSS/otobo/issues/818. The approach taken there is to replace "TRUNCATE TABLE $TargetTable" with "DELETE FROM $TargetTable".

The reason for this strange behavior is https://www.postgresql.org/message-id/15657-f94bb6e3ad28e1e2%40postgresql.org.

[Roy Sigurd Karlsbakk]

I can’t reply with SQL commands, but the above doesn’t help

[Roy Sigurd Karlsbakk]

SQL sentences are blocked due to security reasons (probably noone bothered to sanitize their code first. See http://paste.debian.net/1214558/

Oh – and see https://xkcd.com/327/

[Author]

Posts