Cannot move to mysql

ℹ️ Support
,
1

Nextcloud version (eg, 12.0.2): 16.0
Operating system and version (eg, Ubuntu 17.04): openSUSE Tumbleweed
Apache or nginx version (eg, Apache 2.4.25): Apache 2.4.3.8
PHP version (eg, 7.1): PHP 7.3.4

The issue you are facing:
I cannot move to my Mysql database:

feuerstern:/srv/www/nextcloud # sudo -u wwwrun php occ db:convert-type --all-apps mysql cloud localhost karatekcloud --password="mypassword"
The current PHP memory limit is below the recommended value of 512MB.
Creating schema in new database
The following tables will not be converted:
oc_dashboard_events
oc_dashboard_settings
oc_ocsms_config
oc_ocsms_conversation_read_states
oc_ocsms_sendmessage_queue
oc_ocsms_smsdatas
oc_ocsms_user_datas
oc_registration
oc_richdocuments_assets
oc_richdocuments_direct
oc_richdocuments_member
oc_richdocuments_wopi
oc_social_cache_actors
oc_social_cache_documents
oc_social_request_queue
oc_social_server_actors
oc_social_server_follows
oc_social_server_notes
oc_talk_guests
oc_talk_participants
oc_talk_rooms
oc_talk_signaling
oc_twofactor_yubikey
oc_weather_city
oc_weather_config
oc_xray_log
oc_xray_requests
Continue with the conversion (y/n)? [n] y
oc_accounts
 4/4 [============================] 100%oc_activity
chunked query, 5 chunks
 2000/4033 [=============>--------------]  49%
In AbstractMySQLDriver.php line 125:
                                                                                                                                                                                                                                 
  An exception occurred while executing 'INSERT INTO `oc_activity` (`activity_id`, `timestamp`, `priority`, `type`, `user`, `affecteduser`, `app`, `subject`, `subjectparams`, `message`, `messageparams`, `file`, `link`, `obj  
  ect_type`, `object_id`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["2016", "1554904303", "30", "file_created", "jens", "jens", "files", "created_self", "[{\"29036\":\"\\\/Videos\\\/Spieler kontrolli  
  eren mit Metadaten! \\ud83d\\ude06\\ud83d\\ude30 _ _commandspy Plugin programmieren! & SourceCode-E7Y88bYfx6A.mp4\"}]", "", "[]", "\/Videos\/Spieler kontrollieren mit Metadaten! \ud83d\ude06\ud83d\ude30 _ _commandspy Plug  
  in programmieren! & SourceCode-E7Y88bYfx6A.mp4", "http:\/\/feuercloud.scandic.priv\/index.php\/apps\/files\/?dir=\/Videos", "files", "29036"]:                                                                                 
                                                                                                                                                                                                                                 
  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x98\x86\xF0\x9F...' for column `karatekcloud`.`oc_activity`.`file` at row 1                                                                   
                                                                                                                                                                                                                                 

In PDOStatement.php line 143:
                                                                                                                                                                
  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x98\x86\xF0\x9F...' for column `karatekcloud`.`oc_activity`.`file` at row 1  
                                                                                                                                                                

In PDOStatement.php line 141:
                                                                                                                                                                
  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x98\x86\xF0\x9F...' for column `karatekcloud`.`oc_activity`.`file` at row 1  
                                                                                                                                                                

db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>

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

The output of your Nextcloud log in Admin > Logging:

It is too big, I can not paste it.

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

<?php
$CONFIG = array (
  'instanceid' => '...',
  'passwordsalt' => '...',
  'secret' => '...',
  'trusted_domains' => 
  array (
    0 => 'feuercloud.scandic.priv',
  ),
  'datadirectory' => '/srv/www/nextcloud/data',
  'dbtype' => 'sqlite3',
  'version' => '16.0.0.9',
  'overwrite.cli.url' => 'http://feuercloud.scandic.priv',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost:54321',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'installed' => true,
  'proxy' => '172.16.0.80:3128',
  'mail_domain' => 'expamle.com',
  'mail_from_address' => 'me',
  'mail_smtpmode' => 'smtp',
  'mail_smtpsecure' => 'ssl',
  'mail_sendmailmode' => 'smtp',
  'mail_smtphost' => 'smtp.strato.de',
  'mail_smtpport' => '465',
  'maintenance' => false,
  'theme' => '',
  'loglevel' => 0,
  'updater.secret' => '$2y$10$iKimF5ihhQ9ms3z1FoH7tOCk06MeQEPZTrBQQbJuYKTjRAU3xEjDC',
);

The output of your Apache/nginx/system log in /var/log/____:

2

are your database and tables using utf8mb4? that error message looks like you are only using utf8.

3

I don’t know. How to find it out? I am not verry familar with sqlite.

4

i am refering to mysql (destination) not sqlite (source)

login to mysql server via CLI,

then type:
use
then:
SELECT @@character_set_database, @@collation_database;

you want to see that like this:

mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_general_ci   |
+--------------------------+----------------------+
1 row in set (0.00 sec)

and:

mysql> show create table oc_accounts;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| oc_accounts | CREATE TABLE `oc_accounts` (
  `uid` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `data` longtext COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
5

Done, output:

feuerstern:/home/jens # mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 10.2.21-MariaDB openSUSE package

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use karatekcloud
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [karatekcloud]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_general_ci   |
+--------------------------+----------------------+
1 row in set (0.00 sec)

MariaDB [karatekcloud]> show create table oc_accounts;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                    |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| oc_accounts | CREATE TABLE `oc_accounts` (
`uid` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`data` longtext COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [karatekcloud]>

Does this mean that I am using utf8?
CHARSET=utf8 COLLATE=utf8_bin

6

Hello,
your config.php settings still refer to your sqlite database.
Also recheck dbname, dbhost, dbport, if these settings differ from your previous sqlite database.
As I see from your provided output, your database name is now ‘karatekcloud’ instead of ‘nextcloud’.
The occ command uses the settings from config.php to connect to your database.

7

So I have to change the config.php before running
occ db:convert-type --all-apps mysql cloud localhost karatekcloud --password="mypassword" ?

8

Ah, sorry i didn’t fully read the entered command.
Only checked your config settings related to database.

9

you need to alter the collates on the table(s) from utf8_bin to utf8mb4_bin and charset from utf8 > utf8mb4

since people are throwing around with utf8-emojis, you will have a bad time sooner or later, in your case sooner.

10

How can I do that?

12

Has anyone at Nextcloud actually followed the documentation to convert from SQLite to MySQL? Not clear how NC can be seeing tables in an empty database and failing to write its schema as a result.

[root@www /usr/local/www/nextcloud]# sudo -u www php ./occ db:convert-type --password <> -- mysql nc localhost nextcloud 
  An exception occurred while executing 'CREATE TABLE oc_migrations (app VARCHAR(255) NOT NULL, version VARCHAR(255) NOT   
  NULL, PRIMARY KEY(app, version)) DEFAULT CHARACTER SET UTF8 COLLATE utf8_bin ENGINE = InnoDB':                           
                                                                                                                           
  SQLSTATE[42S01]: Base table or view already exists: 1050 Table '`nextcloud`.`oc_migrations`' already exists              
 mysql -u nc -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1045
Server version: 5.6.44 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use nextcloud;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> 

sudo -u www php ./occ db:convert-type --all-apps mysql nc 127.0.0.1 nextcloud  
The current PHP memory limit is below the recommended value of 512MB.
What is the database password?
Creating schema in new database

In AbstractMySQLDriver.php line 125:
                                                                                                                           
  An exception occurred while executing 'CREATE TABLE oc_migrations (app VARCHAR(255) NOT NULL, version VARCHAR(255) NOT   
  NULL, PRIMARY KEY(app, version)) DEFAULT CHARACTER SET UTF8 COLLATE utf8_bin ENGINE = InnoDB':                           
                                                                                                                           
  SQLSTATE[HY000]: General error: 1813 Tablespace for table '`nextcloud`.`oc_migrations`' exists. Please DISCARD the tabl  
  espace before IMPORT.                                                                                                    
                                                                                                                           

In PDOConnection.php line 109:
                                                                                                                           
  SQLSTATE[HY000]: General error: 1813 Tablespace for table '`nextcloud`.`oc_migrations`' exists. Please DISCARD the tabl  
  espace before IMPORT.                                                                                                    
                                                                                                                           

In PDOConnection.php line 107:
                                                                                                                           
  SQLSTATE[HY000]: General error: 1813 Tablespace for table '`nextcloud`.`oc_migrations`' exists. Please DISCARD the tabl  
  espace before IMPORT.                                                                                                    
                                                                                                                           

db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>

It turns out there was a file — oc_migrations.ibd — in the nextcloud database directory that mysql couldn’t see (it wasn’t a tablespace) that was blocking the installation/conversion. So anyone doing this through occ is not going to find that and may not have the permissions to delete an errant file like that. Not ideal at all.

13

There is the option --clear-schema to clear the database schema before starting the conversion:
https://docs.nextcloud.com/server/16/admin_manual/configuration_database/db_conversion.html

14

Yeah. Tried that. Didn’t work. Took shell access with root’s powers to resolve this.

15

This has recurred with an upgrade to 16.0.2.

At first, the upgrade was balking on this:

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'CREATE TABLE oc_migrations (app VARCHAR(255) NOT NULL, version VARCHAR(255) NOT NULL, PRIMARY KEY(app, version)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ENGINE = InnoDB ROW_FORMAT = compressed':

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

Update failed

I was able to fix the problem (removing the oc_migrations.idb file repeatedly didn’t work, nor did copying an frm file to allow the table to be dropped. But the commands had to be run consecutively.

Now we are here:

Doctrine\DBAL\Schema\SchemaException: The column 'uid_lower' on table 'oc_users' already exists.

Update failed

No idea how to fix that. I suppose deleting the column and re-running the upgrade might work.