DB Migration MySQL -> PostgreSQL fails

SonarQube
1

Hi there,

we are currently trying to migrate our SQ DB from MySQL to PostgreSQL using the migration tool.

At first we did not encounter any problem, but using a current snapshot of our production DB we ran into the following problem:

Error while copying rows of table 'issues': Batch entry 1,661 insert into issues (id, kee, rule_id, severity, manual_severity, message, line, gap, status, resolution, checksum, reporter, assignee, author_login, action_plan_key, issue_attributes, effort, created_at, updated_at, issue_creation_date, issue_update_date, issue_close_date, tags, component_uuid, project_uuid, locations, issue_type) values (37472494,'AWMt7ChGBu1u6tAnQGPj',5983,'CRITICAL','FALSE','Parse error at line 2 column 1:

 1: ��
 2: Parse error at line 2 column 1:

 1: ��
 2:
    ^
 3: #ident1   {font-size:13px;font-family:Arial,Helvetica,sans-serif}
 4:
 5: #ident2   {font-size:13px;font-family:Arial,Helvetica,sans-serif}
 6:
 7: #ident3   {font-size:13px;font-family:Arial,Helvetica,sans-serif}
 8:
 9: #ident4   {font-size:13px;font-family:Arial,Helvetica,sans-serif}
10:
11:
12:',2,NULL,'OPEN',NULL,'5abb695295ae1290a38324b2549fbd47',NULL,NULL,NULL,NULL,'',30,1525483876309,1525483876309,1525483175000,1525483175000,NULL,NULL,'AVtnDsqNG0D0S1obSiFe','AVtnDryCU2uuEJWZs5xx',?,2) was aborted: ERROR: invalid byte sequence for encoding "UTF8": 0x00  Call getNextException to see other errors in the batch.

The MySQL DB was created with

CREATE DATABASE sonar CHARACTER SET utf8 COLLATE utf8_general_ci;

What can be done to overcome this problem?

TIA & KR,
Oliver

3

Hi Oliver,

Did you configure the targeted PostgreSQL database to use UTF-8 charset ?

4

Hi Xavier,

Yes, I did. Otherwise the DB would complain differently. But I will confirm it tomorrow when I am back in the office.

Edit: Yes, UTF-8 is used a encoding.

Thanks for looking into this & KR,

Oliver

5

Hi @revilo,

Do you still encounter this issue ?

Thanks.
Regards,
Julien Lancelot

7

Hi @julienlancelot,

as we hit this issue on one of our instances, we did postpone migrating this instance and did the migration on the other instance first. So we did not try it in the meantime.
But as there I am not aware of any new migraotor release, I would expect the problem to persist.

KR,
Oliver

9

The most relevant part of the error message is this:

What’s happening is that the message column in the issues table in MySQL contains some NULL (\0x00) characters. This is not supported in fields of varchar type in Postgresql, that’s why the import fails.

To fix this, I don’t see a better way than to replace NULL characters in the source database, for example with this SQL:

update issues set message = replace(message, '\0', '_') where message like '%\0%';
11

Thanks for the answer, Janos. As only one row is affected, that workaround should be feasible.

This leads to the question whether SQ ensures, no \0 characters get written into the PostgreSQL DB.

KR,
Oliver