Convert from sqlite to mariadb/mysql

I decided I would try migrating from SQLlite to mariadb and my experience did not match the documentation. I will leave this here in case anyone needs it.

sudo -u www php ./occ db:convert-type mysql nextcloud [hostname] nextcloud
the above, as supplied by the occ prompt, never worked. I replaced hostname w localhost, the IP address (it’s running on the same machine so not hard to access). None of that worked.

The error was consistently
Failed to connect to the database: An exception occurred in the driver: SQLSTATE[HY000] [2002 ] Connection refused
Nothing helpful there. The database does/did exist and I logged into it through MySQL on the command line to verify.

What eventually worked was
sudo -u www php ./occ db:convert-type mysql nextcloud localhost:3306 nextcloud
The hostname with the port did not work,

The proper order of things would also be to create the database/database user first before attempting this as it will surely fail if the username doesn’t exist.

I also had to add this line to config.php:
"dbtableprefix" => "oc_",

Not this was 100% successful. The server completely b0rked now, kicking back Internal Server Error

The log says::
sudo -u www php ./occ An unhandled exception has been thrown: Doctrine\DBAL\Exception: Failed to connect to the database: An exception occurred in the driver: SQLSTATE[HY000] [2002] No such file or directory in /usr/local/www/nextcloud/lib/private/DB/Connection.php:139
But…
mysql -u nextcloud -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g.

and use database nextcloud/show tables does what one would expect.

So the database exists and can be logged into.

Good morning @paulbeard:

Was that a typo above or am I misunderstanding your statement about not using the port?

There is a separate parameter for specifying a non-standard port[1] if needed (--port). Since 3306 is MySQL’s default port, specifying it shouldn’t be necessary, but either way.

  • Based on some of the behavior you encountered, are you by chance running MariaDB through a UNIX socket / with skip-networking=1 maybe?
  • Can you share the db* parameter set now in your Nextcloud config?

I did a quick test (twice just in case). And I didn’t encounter the same behavior (full capture below).

In addition, checked via Administration settings->Logging that Nextcloud automatically switched over to the new DB within 60s (the default opcache revalidate frequency). Instance didn’t even experience any downtime.

www-data@YYY:/$ mysql -h db -u ncuser -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.6.17-MariaDB-1:10.6.17+maria~ubu2004-log mariadb.org binary distribution

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)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ncdb               |
+--------------------+
2 rows in set (0.001 sec)

MariaDB [(none)]> quit
Bye
www-data@YYY:/$ exit
exit
X@Y:~/src/infrastructure/test/test-nc28-convertdb$ docker compose exec -u33 app bash
www-data@XXX:~/html$ grep db config/config.php
  'dbtype' => 'sqlite3',
  'dbname' => 'ncdblite',
www-data@XXX:~/html$ ./occ db:convert-type --password="ncpass" --all-apps mysql ncuser db ncdb
Creating schema in new database
 - related_resources
 - files_versions
 - theming
 - support
 - weather_status
 - lookup_server_connector
 - systemtags
 - user_ldap
 - workflowengine
 - photos
 - files_sharing
 - suspicious_login
 - sharebymail
 - comments
 - federation
 - provisioning_api
 - twofactor_totp
 - user_status
 - password_policy
 - federatedfilesharing
 - files_trashbin
 - admin_audit
 - text
 - dashboard
 - activity
 - survey_client
 - files
 - serverinfo
 - recommendations
 - encryption
 - firstrunwizard
 - files_reminders
 - dav
 - settings
 - contactsinteraction
 - twofactor_backupcodes
 - files_pdfviewer
 - notifications
 - viewer
 - updatenotification
 - files_external
 - privacy
 - logreader
 - bruteforcesettings
 - nextcloud_announcements
 - oauth2
 - cloud_federation_api
 - circles
 - oc_accounts
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_accounts_data
 12/12 [============================] 100% < 1 sec/< 1 sec
 - oc_activity
 91/91 [============================] 100% < 1 sec/< 1 sec
 - oc_activity_mq
    0 [>---------------------------] < 1 sec
 - oc_addressbookchanges
    0 [>---------------------------] < 1 sec
 - oc_addressbooks
    0 [>---------------------------] < 1 sec
 - oc_appconfig
 146/146 [============================] 100% < 1 sec/< 1 sec
 - oc_authorized_groups
    0 [>---------------------------] < 1 sec
 - oc_authtoken
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_bruteforce_attempts
    0 [>---------------------------] < 1 sec
 - oc_calendarchanges
    0 [>---------------------------] < 1 sec
 - oc_calendarobjects
    0 [>---------------------------] < 1 sec
 - oc_calendarobjects_props
    0 [>---------------------------] < 1 sec
 - oc_calendars
    0 [>---------------------------] < 1 sec
 - oc_calendarsubscriptions
    0 [>---------------------------] < 1 sec
 - oc_calendar_invitations
    0 [>---------------------------] < 1 sec
 - oc_calendar_reminders
    0 [>---------------------------] < 1 sec
 - oc_calendar_resources
    0 [>---------------------------] < 1 sec
 - oc_calendar_resources_md
    0 [>---------------------------] < 1 sec
 - oc_calendar_rooms
    0 [>---------------------------] < 1 sec
 - oc_calendar_rooms_md
    0 [>---------------------------] < 1 sec
 - oc_cards
    0 [>---------------------------] < 1 sec
 - oc_cards_properties
    0 [>---------------------------] < 1 sec
 - oc_circles_circle
 2/2 [============================] 100% < 1 sec/< 1 sec
 - oc_circles_event
    0 [>---------------------------] < 1 sec
 - oc_circles_member
 2/2 [============================] 100% < 1 sec/< 1 sec
 - oc_circles_membership
 2/2 [============================] 100% < 1 sec/< 1 sec
 - oc_circles_mount
    0 [>---------------------------] < 1 sec
 - oc_circles_mountpoint
    0 [>---------------------------] < 1 sec
 - oc_circles_remote
    0 [>---------------------------] < 1 sec
 - oc_circles_share_lock
    0 [>---------------------------] < 1 sec
 - oc_circles_token
    0 [>---------------------------] < 1 sec
 - oc_collres_accesscache
    0 [>---------------------------] < 1 sec
 - oc_collres_collections
    0 [>---------------------------] < 1 sec
 - oc_collres_resources
    0 [>---------------------------] < 1 sec
 - oc_comments
    0 [>---------------------------] < 1 sec
 - oc_comments_read_markers
    0 [>---------------------------] < 1 sec
 - oc_dav_absence
    0 [>---------------------------] < 1 sec
 - oc_dav_cal_proxy
    0 [>---------------------------] < 1 sec
 - oc_dav_shares
    0 [>---------------------------] < 1 sec
 - oc_directlink
    0 [>---------------------------] < 1 sec
 - oc_direct_edit
    0 [>---------------------------] < 1 sec
 - oc_federated_reshares
    0 [>---------------------------] < 1 sec
 - oc_filecache
 178/178 [============================] 100% < 1 sec/< 1 sec
 - oc_filecache_extended
    0 [>---------------------------] < 1 sec
 - oc_files_metadata
 10/10 [============================] 100% < 1 sec/< 1 sec
 - oc_files_metadata_index
 10/10 [============================] 100% < 1 sec/< 1 sec
 - oc_files_reminders
    0 [>---------------------------] < 1 sec
 - oc_files_trash
    0 [>---------------------------] < 1 sec
 - oc_files_versions
 45/45 [============================] 100% < 1 sec/< 1 sec
 - oc_file_locks
    0 [>---------------------------] < 1 sec
 - oc_flow_checks
    0 [>---------------------------] < 1 sec
 - oc_flow_operations
    0 [>---------------------------] < 1 sec
 - oc_flow_operations_scope
    0 [>---------------------------] < 1 sec
 - oc_groups
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_group_admin
    0 [>---------------------------] < 1 sec
 - oc_group_user
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_jobs
 39/39 [============================] 100% < 1 sec/< 1 sec
 - oc_known_users
    0 [>---------------------------] < 1 sec
 - oc_login_flow_v2
    0 [>---------------------------] < 1 sec
 - oc_migrations
Skipping migrations table because it was already filled by running the migrations
 - oc_mimetypes
 21/21 [============================] 100% < 1 sec/< 1 sec
 - oc_mounts
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_notifications
 6/6 [============================] 100% < 1 sec/< 1 sec
 - oc_notifications_pushhash
    0 [>---------------------------] < 1 sec
 - oc_notifications_settings
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_oauth2_access_tokens
    0 [>---------------------------] < 1 sec
 - oc_oauth2_clients
    0 [>---------------------------] < 1 sec
 - oc_open_local_editor
    0 [>---------------------------] < 1 sec
 - oc_photos_albums
    0 [>---------------------------] < 1 sec
 - oc_photos_albums_collabs
    0 [>---------------------------] < 1 sec
 - oc_photos_albums_files
    0 [>---------------------------] < 1 sec
 - oc_preferences
 11/11 [============================] 100% < 1 sec/< 1 sec
 - oc_privacy_admins
    0 [>---------------------------] < 1 sec
 - oc_profile_config
    0 [>---------------------------] < 1 sec
 - oc_properties
    0 [>---------------------------] < 1 sec
 - oc_ratelimit_entries
    0 [>---------------------------] < 1 sec
 - oc_reactions
    0 [>---------------------------] < 1 sec
 - oc_recent_contact
    0 [>---------------------------] < 1 sec
 - oc_schedulingobjects
    0 [>---------------------------] < 1 sec
 - oc_share
    0 [>---------------------------] < 1 sec
 - oc_share_external
    0 [>---------------------------] < 1 sec
 - oc_storages
 2/2 [============================] 100% < 1 sec/< 1 sec
 - oc_storages_credentials
    0 [>---------------------------] < 1 sec
 - oc_systemtag
    0 [>---------------------------] < 1 sec
 - oc_systemtag_group
    0 [>---------------------------] < 1 sec
 - oc_systemtag_object_mapping
    0 [>---------------------------] < 1 sec
 - oc_text2image_tasks
    0 [>---------------------------] < 1 sec
 - oc_textprocessing_tasks
    0 [>---------------------------] < 1 sec
 - oc_text_documents
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_text_sessions
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_text_steps
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_trusted_servers
    0 [>---------------------------] < 1 sec
 - oc_twofactor_backupcodes
    0 [>---------------------------] < 1 sec
 - oc_twofactor_providers
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_users
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_user_status
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_user_transfer_owner
    0 [>---------------------------] < 1 sec
 - oc_vcategory
    0 [>---------------------------] < 1 sec
 - oc_vcategory_to_object
    0 [>---------------------------] < 1 sec
 - oc_webauthn
    0 [>---------------------------] < 1 sec
 - oc_whats_new
    0 [>---------------------------] < 1 sec
www-data@YYY:~/html$ grep db config/config.php
  'dbtype' => 'mysql',
  'dbname' => 'ncdb',
  'dbhost' => 'db',
  'dbuser' => 'ncuser',
  'dbpassword' => 'ncpass',

Snippet of attempt specifying the --port="3306" parameter (successful too) on another clean sqlite3 installation:

www-data@XXX:~/html$ ./occ db:convert-type --password="ncpass" --port="3306" --all-apps mysql ncuser db ncdb
Creating schema in new database
[...]
www-data@XXX:~/html$ grep db config/config.php
  'dbtype' => 'mysql',
  'dbname' => 'ncdb',
  'dbhost' => 'db:3306',
  'dbuser' => 'ncuser',
  'dbpassword' => 'ncpass',
www-data@XXX:~/html$ 

[1] Converting database type — Nextcloud latest Administration Manual latest documentation

1 Like

[client-server] port = 3306 socket = /tmp/mysql.sock
[mysqld] #skip-networking=0

I don’t use docker and maybe that’s a prerequisite I am not aware of. I have no idea what docker is (I assume it’s some kind virtualization/emulation). It’s not clear how adding a layer of virtualization or whatever would make it easier to connect to a running database that other processes can access.

Once again, this is where — and many others on here — seem to be stuck.
sudo -u www php ./occ An unhandled exception has been thrown: Doctrine\DBAL\Exception: Failed to connect to the database: An exception occurred in the driver: SQLSTATE[HY000] [2002] No such file or directory in /usr/local/www/nextcloud/lib/private/DB/Connection.php:139
No clue why it can’t connect, what driver it is using or requires. As for elapsed time…

real 0m0.233s
user 0m0.001s
sys 0m0.006s

Pretty much instantaneous.

I don’t use docker and maybe that’s a prerequisite I am not aware of.

Docker is irrelevant.

Once again, this is where — and many others on here — seem to be stuck.

You didn’t answer my questions:

  1. Was that a typo above or am I misunderstanding your statement about not using the port? (your example has a port on the hostname still). I’d like to find out the actual command that finally worked for you for the conversion part.
  2. Can you share the db* parameter set now in your Nextcloud config.php? The ones like dbtype, etc.

Here, from my history are all the incantations I used to get this to work: the last one was successful.

514 sudo -u www php ./occ db:convert-type mariadb, nextcloud, localhost, nextcloud
515 sudo -u www php ./occ db:convert-type mysql, nextcloud, localhost, nextcloud
516 sudo -u www php ./occ db:convert-type mysqldb, nextcloud, localhost, nextcloud
517 sudo -u www php ./occ db:convert-type mysql nextcloud localhost nextcloud
518 sudo -u www php ./occ db:convert-type mysql nextcloud localhost nextcloud
521 sudo -u www php ./occ db:convert-type mysql nextcloud localhost nextcloud
522 sudo -u www php ./occ db:convert-type mysql nextcloud www nextcloud
523 sudo -u www php ./occ db:convert-type mysql nextcloud www nextcloud
524 sudo -u www php ./occ db:convert-type mysql nextcloud mariadb nextcloud
525 sudo -u www php ./occ db:convert-type mysql nextcloud 192.168.0.26 nextcloud
526 sudo -u www php ./occ db:convert-type mysql nextcloud www:3306 nextcloud
527 sudo -u www php ./occ db:convert-type mysql nextcloud localhost:3306 nextcloud

‘dbtype’ => ‘mysql’,
‘dbname’ => ‘nextcloud’,
‘dbhost’ => ‘localhost:3306’,
‘dbuser’ => ‘nextcloud’,
‘dbpassword’ => ‘[redacted]’,
“dbtableprefix” => “oc_”,

most of that, all but the last line, was added by occ.

Docker may be irrelevant but every working solution seems to reference it: seems like no one does this in a bare metal (emulated or otherwise) setting.

1 Like

Also:
nextcloud@localhost [(none)]> use nextcloud;
Database changed
nextcloud@localhost [nextcloud]> show tables;
±----------------------------+
| Tables_in_nextcloud |
±----------------------------+
| oc_accounts |
| oc_accounts_data |
[…]

102 rows in set (0.019 sec)

Okay I think I know what happened in your case. Your MySQL server has both UNIX socket support and networking (TCP) enabled. That’s totally fine, but there are some built-in assumptions in the mysql drivers in the event that localhost is specified - namely they try to use the UNIX socket mode. This is old MySQL client behavior.

And technically it should have been fine, but there is a bug in db:convert-type right now for UNIX socket support (which I kind of forgot about, but I already created a patch it last summer[1] then got distracted so I never wrapped it up for merging).

When you appended :3306 it overrode that behavior and worked via networking (TCP).

I’m already working on some doc adjustments to clarify that whole section. I’ll take another look at the bug fix for UNIX sockets too. I’ll also update the docs in a second pass, once that bug addressed, to include UNIX socket handling as well during conversions.

Thanks for clarifying your steps.

[1] (occ db:convert-type) Add support for UNIX sockets by joshtrichards · Pull Request #39242 · nextcloud/server · GitHub

1 Like

It didn’t work at all until I added that. Not obvious that it tried networking or that using the socket is preferred.

I just turned on “skip-networking” and changed dbhost to 'dbhost' => 'localhost:/tmp/mysql.sock',

Now it works as expected. And wordpress is agnostic about it all so it works as it did before. Postel’s Law of Robustness comes to mind here. This is a widely-shared issue, lots of search results for it.

This topic was automatically closed 8 days after the last reply. New replies are no longer allowed.