Conversion from OTRS 6 MariaDB (not Docker)

[crythias]

Here’s a snipped from my syslog. It seems during migration the creation of new columns is not complete and causes issues.

Dec 7 19:02:50 otrs-dev ?LogPrefix?-72[17529]: [Error][Kernel::System::MigrateFromOTRS::CloneDB::Driver::mysql::AlterTableAddColumn][Line:295]: Could not execute SQL statement: ALTER TABLE article ADD a_subject (65535).
Dec 7 19:02:50 otrs-dev ?LogPrefix?-72[17529]: [Error][Kernel::System::MigrateFromOTRS::CloneDB::Driver::mysql::AlterTableAddColumn][Line:291]: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‚(65535)‘ at line 1, SQL: ‚ALTER TABLE article ADD a_message_id (65535)‘
Dec 7 19:02:50 otrs-dev ?LogPrefix?-72[17529]: [Error][Kernel::System::MigrateFromOTRS::CloneDB::Driver::mysql::AlterTableAddColumn][Line:295]: Could not execute SQL statement: ALTER TABLE article ADD a_message_id (65535).
Dec 7 19:02:50 otrs-dev ?LogPrefix?-72[17529]: [Error][Kernel::System::MigrateFromOTRS::CloneDB::Driver::mysql::AlterTableAddColumn][Line:291]: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‚(65535)‘ at line 1, SQL: ‚ALTER TABLE article ADD a_in_reply_to (65535)‘
Dec 7 19:02:50 otrs-dev ?LogPrefix?-72[17529]: [Error][Kernel::System::MigrateFromOTRS::CloneDB::Driver::mysql::AlterTableAddColumn][Line:295]: Could not execute SQL statement: ALTER TABLE article ADD a_in_reply_to (65535).
Dec 7 19:02:50 otrs-dev ?LogPrefix?-72[17529]: [Error][Kernel::System::MigrateFromOTRS::CloneDB::Driver::mysql::AlterTableAddColumn][Line:291]: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‚(65535)‘ at line 1, SQL: ‚ALTER TABLE article ADD a_references (65535)‘
Dec 7 19:02:50 otrs-dev ?LogPrefix?-72[17529]: [Error][Kernel::System::MigrateFromOTRS::CloneDB::Driver::mysql::AlterTableAddColumn][Line:295]: Could not execute SQL statement: ALTER TABLE article ADD a_references (65535).
Dec 7 19:02:50 otrs-dev ?LogPrefix?-72[17529]: [Error][Kernel::System::MigrateFromOTRS::CloneDB::Driver::mysql::AlterTableAddColumn][Line:291]: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‚(16777215)‘ at line 1, SQL: ‚ALTER TABLE article ADD a_body MEDIUMTEXT (16777215)‘
Dec 7 19:02:50 otrs-dev ?LogPrefix?-72[17529]: [Error][Kernel::System::MigrateFromOTRS::CloneDB::Driver::mysql::AlterTableAddColumn][Line:295]: Could not execute SQL statement: ALTER TABLE article ADD a_body MEDIUMTEXT (16777215).
Dec 7 19:02:50 otrs-dev ?LogPrefix?-72[17529]: [Error][Kernel::System::MigrateFromOTRS::CloneDB::Driver::Base::DataTransfer][Line:797]: Unknown column ‚a_from‘ in ‚field list‘, SQL: ‚INSERT INTO otobo.article (id, ticket_id, article_type_id, article_sender_type_id, a_from, a_reply_to, a_to, a_cc, a_subject, a_message_id, a_in_reply_to, a_references, a_content_type, a_body, incoming_time, content_path, valid_id, create_time, create_by, change_time, change_by, a_message_id_md5)#012 SELECT id, ticket_id, article_type_id, article_sender_type_id, a_from, a_reply_to, a_to, a_cc, a_subject, a_message_id, a_in_reply_to, a_references, a_content_type, a_body, incoming_time, content_path, valid_id, create_time, create_by, change_time, change_by, a_message_id_md5#012 FROM otrs.article#012‘

 

mysql –version
mysql Ver 15.1 Distrib 10.3.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

But I see the problem (I think)

select column_name,data_type from information_schema.columns where table_name='article';
+----------------------------+------------+
| column_name | data_type |
+----------------------------+------------+
| id | bigint |
| ticket_id | bigint |
| article_type_id | smallint |
| article_sender_type_id | smallint |
| a_from | text |
| a_reply_to | text |
| a_to | text |
| a_cc | text |
| a_subject | text |
| a_message_id | text |
| a_in_reply_to | text |
| a_references | text |
| a_content_type | varchar |
| a_body | mediumtext |
| incoming_time | int |
| content_path | varchar |
| valid_id | smallint |
| create_time | datetime |
| create_by | int |
| change_time | datetime |
| change_by | int |
| a_message_id_md5 | varchar |
| id | bigint |
| ticket_id | bigint |
| article_sender_type_id | smallint |
| communication_channel_id | bigint |
| is_visible_for_customer | smallint |
| search_index_needs_rebuild | smallint |
| insert_fingerprint | varchar |
| create_time | datetime |
| create_by | int |
| change_time | datetime |
| change_by | int |
| article_type_id | smallint |
| a_content_type | varchar |
| incoming_time | int |
| content_path | varchar |
| valid_id | smallint |
| a_message_id_md5 | varchar |
+----------------------------+------------+

 

The migration tool doesn’t know what to do with ‚text‘ (or ‚bigint‘)

„Kernel/System/MigrateFromOTRS/CloneDB/Driver/mysql.pm“

if ( $Param{DBType} =~ /mysql/ ) {
$Result{varchar} = ‚VARCHAR‘;
$Result{int} = ‚INTEGER‘;
$Result{datetime} = ‚DATETIME‘;
$Result{smallint} = ‚SMALLINT‘;
$Result{longblob} = ‚LONGBLOB‘;
$Result{mediumtext} = ‚MEDIUMTEXT‘;

[crythias]

in MariaDB, mediumtext doesn’t take a value, but TEXT does.

Next:
[Kernel::System::MigrateFromOTRS::CloneDB::Driver::Base::DataTransfer][Line:797]: Field ‚communication_channel_id‘ doesn’t have a default value,

[Stefan Rother]

Hello Crythias,

I hope you are well!

Please excuse the late reply, we are currently a bit stretched with answering OTOBO related queries on all channels.

You are right about the first error, but this query should only be made if an additional column has been added to the table „Article“ in OTRS and emigrates between different databases. However, we also found a bug that was fixed in 10.0.7.

In general, we have fixed a few more bugs in 10.0.7. This version will be released at the beginning of next week and hopefully your second bug will be fixed then. If this is not the case, please contact us briefly at hello@otobo.de so that we can analyse and fix the error together in a short remote session.

Thank you for your support!

Stefan Rother
Team OTOBO

[crythias]

Is there’s a path to run migrate.pl by command line? I am over VPN, it takes hours, then disconnection fails and I apparently have to restart the hours …

 

[Sven Oesterling]

Hi Crythias,

yes there is, kind of, if you are using OTOBO with mysql that is. (I saw that we really have to update the migration tutorial. We are working on this quite a bit, still, and the documentation isn’t really keeping up, atm…)

Basically instead of migrating the database via migration.pl, this step can be done by hand, and then be skipped in the web interface. To do this, you have to run

scripts/backup.pl -t migratefromotrs --db-name otrs --db-host 127.0.0.1 --db-user otrs --db-password "secret_otrs_password"

which will create a directory with a couple of sql-files, which have to be imported into the OTOBO-database in the following order: 1. _pre; 2. _schema_for _otobo; 3. _data; 4. _post; For docker this would be done via:

docker exec -i otobo_db_1 mysql -u root -p otobo < otobo_pre.sql

You should do this, right before running migration.pl (after setting secure mode=0!), because the system won’t be usable from here on, until after the migration. Within the migration, at the step where you normally set up and test the OTRS db, you can then check the checkbox „skip db migration“. The migration will for the most part play out the same, and currently there is no way around it, but the time intensive step of copying the db will be skipped.

(If you run into errors in the steps after the db migration, and the script stops, sometimes they can be resolved by hand (with the current version we are aware of a potential problem with the web services, which will be fixed in OTOBO 10.0.8, which will be released this week) and more often than not, you can then just restart the migration from the point it failed before. Even if not, though, having the sql files should speed up doing it again, if something doesn’t work out.)

If you need further help, feel free to contact us directly,

Sven

• This reply was modified 2 years, 10 months ago by Sven Oesterling.

[Author]

Posts