Recreate my nextcloud database again

Hi,
as I described in a further post (Server unavailable: "oc_filecache is corrupted"), a mariadb table of the nextcloud database was corrupted and I wasn’t able to repair it with the mysqlcheck tool. I dumped and dropped the whole database to recover it afterwards. Unfortunately, my sql dump file was incomplete ( it stopped at the first corrupted table).
I understood I totally messed it up :frowning: .
Now I want to recreate the whole database from scratch and rescan my files. I dropped the database again and started with the commands

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE DATABASE IF NOT EXISTS nextcloud CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
GRANT ALL PRIVILEGES on nextcloud.* to 'username'@'localhost';
FLUSH privileges;

as described in https://docs.nextcloud.com/server/18/admin_manual/configuration_database/linux_database_configuration.html .
What would be the next steps to create all necessary tables, so I’ll be able to run the occ files:scan command and make nextcloud running again? Can I find an according sql script somewhere?

Not sure if that will help, but since you already lost your db, start from scratch and let Nextcloud create the db again. Run the installation routine as described in the manual installation documentation in step 3:

$ cd /var/www/nextcloud/
$ sudo -u www-data php occ  maintenance:install --database \
"mysql" --database-name "nextcloud"  --database-user "root" --database-pass \
"password" --admin-user "admin" --admin-pass "password"

You need to provide the mysql root user password so that Nextcloud has privileges to create the new database. Also set the new admin password which you can then use to login to your instance as admin.

Thanks! But unfortunately this results in the following error message:

PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'nextcloud.oc_appconfig' doesn't exist in /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:88

I don’t understand, shouldn’t the occ maintenance:install command actually be responsible for creating this and other tables?
Maybe it thinks the tables already exist because in my earlier installation, some kind of flag has been set somewhere ( i am just speculating )…

You said that you had dropped the database. Are you sure you did that?

Yes, but I recreated it using the upper sql commands. Shall I drop it again and then only run the shell commands you provided?

You created an empty database that is no use. Yes drop it and try the command I suggested.

I tried droping the database with

docker exec ${D}_db_1 mysql  -p$MYSQL_ROOT_PASSWORD -e "drop database nextcloud"

But without recreating the database, I got an even earlier error:

Failed to connect to the database: An exception occurred in the driver: SQLSTATE[HY000] [1049] Unknown database 'nextcloud' in /var/www/html/lib/private/DB/Connection.php:139

so I recreated it with

docker exec ${D}_db_1 mysql -p$MYSQL_ROOT_PASSWORD -e "create database nextcloud;" 

and then

docker exec --user www-data ${D}_app_1 php occ maintenance:install --database "mysql" --database-name "nextcloud"  --database-user "root" --database-pass "$MYSQL_ROOT_PASSWORD" --admin-user "admin" --admin-pass "$MYSQL_PASSWORD"

but I get the same error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'nextcloud.oc_appconfig' doesn't exist in /var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php:82

You have to grant priviledges again with

GRANT ALL PRIVILEGES ON *.* TO 'nextcloud'@'localhost' IDENTIFIED BY '$MYSQL_PASSWORD';

And then you have to set the installed flag in your config file to false:

grep installed config/config.php

and create a file CAN_INSTALL in your config folder

Then you can reinstall in the web-interface

Note that if you use docker, the database hostname is db not localhost