NextCloud database migration sqlite --> mysql troubleshooting

Support intro

Sorry to hear you’re facing problems :slightly_frowning_face:

help.nextcloud.com is for home/non-enterprise users. If you’re running a business, paid support can be accessed via portal.nextcloud.com where we can ensure your business keeps running smoothly.

In order to help you as quickly as possible, before clicking Create Topic please provide as much of the below as you can. Feel free to use a pastebin service for logs, otherwise either indent short log examples with four spaces:

example

Or for longer, use three backticks above and below the code snippet:

longer
example
here

Some or all of the below information will be requested if it isn’t supplied; for fastest response please provide as much as you can :heart:

Nextcloud version: 22.2.3
Operating system and version: Raspbian GNU/Linux 10 (buster)
Apache or nginx version: Apache/2.4.38 (Raspbian)
PHP version: 7.3

The issue you are facing:
I am trying to convert sqlite database to mysql (MariaDB for instance) I have create the new database and tables have been created. After confirming with “y” that I want proceed with the conversion I am getting a duplicate primary key error.

 - oc_accounts
 0/9 [>---------------------------]   0% < 1 sec/< 1 sec
In ExceptionConverter.php line 60:
                                                                                                                                       
  An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Bruna' for ke  
  y 'PRIMARY'                                                                                                                          
                                                                                                                                       

In Exception.php line 26:
                                                                                                   
  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Bruna' for key 'PRIMARY'  
                                                                                                   

In Statement.php line 92:
                                                                                                   
  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Bruna' for key 'PRIMARY'

“Bruna” is a users username.

Is this the first time you’ve seen this error?: Yes

I have been trying to look if there is a duplicate with the oc_accounts table in the sqlite database but I get:
SQL error: no such table: oc_accounts

I suppose there must be an error with account “Bruna” but I am not able to troubleshoot it. Any help would be greatly appreciated. Thanks

After some searching on sqlite syntax I’ve been able to list the content of the oc_accounts tables and there are no duplicates. Suspecting that there was something wrong in the new db on mysql I dropped (deleted) the new db an started over again.

This time I got past the oc_accounts error but got a new error:

An exception occurred while executing a query: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x94\xA5 -...' for column nextcloud.oc_activity.file a t row 1

Searching for this error I found that it was related to emoticons used in talk and that I needed to enable utf-8 support for the mysql database.

Just add ‘mysql.utf8mb4’ => true, to your config.php (source)

After this migration went as expected.