Can't migrate to mySQL from SQLite

ℹ️ Support
1

Nextcloud version :18.1
Operating system and version (eg, Ubuntu 17.04): Linux Mint
Apache or nginx version (eg, Apache 2.4.25): 2
PHP version (eg, 7.1):7.2

The issue you are facing:
Hi. Two weeks ago I succesfully installed Nextcloud 18.1. It works really nice. However, now I want to migrate to mySQL. I try to use the nextcloud converter, but during the process of conversion it throws up with an error:

Please note that tables belonging to available but currently not installed apps
can be included by specifying the --all-apps option.
Continue with the conversion (y/n)? [n] y
oc_accounts
5/5 [============================] 100%oc_activity
chunked query, 2 chunks
1439/1439 [============================] 100%oc_activity_mq
0 [>---------------------------]oc_addressbookchanges
42/42 [============================] 100%oc_addressbooks
5/5 [============================] 100%oc_appconfig
235/235 [============================] 100%oc_authtoken
30/30 [============================] 100%oc_bruteforce_attempts
7/7 [============================] 100%oc_calendar_invitations
0 [>---------------------------]oc_calendar_reminders
0 [>---------------------------]oc_calendar_resources
0 [>---------------------------]oc_calendar_resources_md
0 [>---------------------------]oc_calendar_rooms
0 [>---------------------------]oc_calendar_rooms_md
0 [>---------------------------]oc_calendarchanges
203/203 [============================] 100%oc_calendarobjects
64/64 [============================] 100%oc_calendarobjects_props
119/119 [============================] 100%oc_calendars
10/10 [============================] 100%oc_calendarsubscriptions
0 [>---------------------------]oc_cards
5/5 [============================] 100%oc_cards_properties
22/22 [============================] 100%oc_collres_accesscache
0 [>---------------------------]oc_collres_collections
1/1 [============================] 100%oc_collres_resources
4/4 [============================] 100%oc_comments
chunked query, 3 chunks
0/2242 [>---------------------------] 0%
In AbstractMySQLDriver.php line 106:
An exception occurred while executing ‘INSERT INTO oc_comments (id, ch ildren_count, actor_type, actor_id, message, verb, creation_times tamp, latest_child_timestamp, object_type, object_id, parent_id, topmost_parent_id) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)’ with params
[“70”, “0”, “users”, “qbunjo”, “No to si\u0119 bardzo ciesz\u0119.
Jest te\u017c aplikacja na telefon do rozm\u00f3w (w\u0142a\u015bnie z nie
j pisz\u0119) \ud83d\udc4d”, “comment”, “2020-03-16 06:58:15”, null, “chat”
, “4”, “0”, “0”]:
SQLSTATE[HY000]: General error: 1366 Incorrect string value: ‘\xF0\x9F\x91
x8D’ for column ‘message’ at row 1
In PDOStatement.php line 119:
SQLSTATE[HY000]: General error: 1366 Incorrect string value: ‘\xF0\x9F\x91
x8D’ for column ‘message’ at row 1
In PDOStatement.php line 117:
SQLSTATE[HY000]: General error: 1366 Incorrect string value: ‘\xF0\x9F\x91
x8D’ for column ‘message’ at row 1
db:convert-type [–port PORT] [–password PASSWORD] [–clear-schema] [–all-apps] [–chunk-size CHUNK-SIZE] [–]

Is this the first time you’ve seen this error? (Y/N):Yes

What can I do with that?
I suppose it has something wrong with char encoding, but don’t know what to do with that.

According to google, sign “\xF0\x9F\x91\x8D” is a “thumbs up” emoji.

I tried to set encoding as UTF8mb4 and similar collation in newly created mySQL database, but with no result.

2

I’m having the same issue. Running latest 18 version of Nextcloud.

I run the following:
sudo -u abc php occ db:convert-type mysql USERNAME HOST DATABASE --clear-schema

Starts running and gets most of the way through and dies with:

In AbstractMySQLDriver.php line 60:

An exception occurred while executing ‘INSERT INTO oc_groups (gid, displayname) VALUES(?, ?)’ with params [“admin”, “admin”]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘displayname’ in ‘field list’

In PDOStatement.php line 119:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘displayname’ in ‘field list’

In PDOStatement.php line 117:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘displayname’ in ‘field list’

I’d really like to transition to mysql but I really don’t want to start with a vanilla install.

3

It looks like SQLite doesn’t like utf8 special characters and converting tool has some troubles with moving them to MySQL database.
I use this crazy days my nextcloud base a lot, as well as my coworkers, so we had to move to MySQL. I created brand new database in MySQL and asked coworkers to backup their files and move them again. I decided to to this, cause the base is going to grow day by day and it will be more difficult to move.
Although maybe this issue needs some consider in the converting tool code?