Missing db.table "nextcloud.oc_file_locks" at installation

[Johannes]

I’m trying to install the Nextcloud(V16.0.4) plugin on a FreeNAS server.
FreeNAS is version 11.2-U6 and is running on a VM.
The install of the plugin worked fine, without any errors.
Also I mounted a directory in freeNAS for nextcloud.
I connected to Nextcloud with a browser and got a steup screen. I entered all parameters, hit “Finish Setup” and got the error “Error while trying to create admin user: There is no table with name ‘nextcloud.oc_file_locks’ in the schema.”
I checked the tables with the SQL command use nextcloud; show tables; and nextcloud.oc_file_locks isn’t shown.
I tried to reinstall the plugin but that didin’t helped.

[JimmyKater]

try adding the missing table manually

[Johannes]

Thanks for your reply.
I didn’t find the schema of the table online. Do you know were I can find it, or is there an option other than CREATE TABLE to add the table.

[tuxedo-rb]

Hello,

SHOW CREATE TABLE oc_file_locks;

resulted in the following table scheme from my mysql database.

CREATE TABLE `oc_file_locks` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `lock` int(11) NOT NULL DEFAULT '0', `key` varchar(64) COLLATE utf8mb4_bin NOT NULL, `ttl` int(11) NOT NULL DEFAULT '-1', PRIMARY KEY (`id`), UNIQUE KEY `lock_key_index` (`key`), KEY `lock_ttl_index` (`ttl`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED;

I decreased the auto_increment value to the start value of 1.

Good luck

Edit: code formatting

[Johannes]

Hello tuxedo-rb,

I tried the command you posted and got the following error:
ERROR 1913 (HY000): Tablespace for table 'nextcloud.oc_file_locks' exists. Pleas DISCARD the tablespace befor IMPORT.
so I ask google and it seems that the reason for the missing table is the missing file oc_file_locks.frm in the directory /var/db/mysql/nextcloud/.
To solve the Error 1913 I deleted the corresponding file oc_file_locks.ibd, than I tried the CREATE TABLE command again and got the next error: ERROR 1050 (42S01): TABLE 'nextcloud.oc_file_locks' already exists.
So I think mysql has somewhere an entry that the table exist and I must first remove the entry, to get rid of the error.

Do you know something about this?

[tuxedo-rb]

Hello Johannes,

sorry for the late reply.
I never had this kind of issue, but after a bit googling, deleting the corresponding *.idb file seems to be the solution.
See: https://stackoverflow.com/questions/15694168/error-tablespace-for-table-xxx-exists-please-discard-the-tablespace-before-imp

Perhaps you have to run following additional SQL-queries after deletion:

ALTER TABLE oc_file_locks DISCARD TABLESPACE;
DROP TABLE oc_file_locks;


It would be interesting to find out what caused this error.

[Johannes]

Hey tuxedo-rb,

I tried the commands you suggested, but both of them resulted in errors:
ALTER TABLE oc_file_locks DISCARD TABLESPACE; results in:
ERROR 1146 (42S02): Tablespace for table 'nextcloud.oc_file_locks' doesn't exist
DROP TABLE oc_file_locks; results in:
ERROR 1051 (42S02): Unknown table 'nextcloud.oc_file_locks'

Finally I found a solution that work, I’m not sure if it is 100% complete, because I tried lots of thinks, but hopfully it works for others too.

1. I created the table in another database(in my chase “test”) with the following command:
   use test; CREATE TABLEoc_file_locks(idbigint(20) unsigned NOT NULL AUTO_INCREMENT,lockint(11) NOT NULL DEFAULT '0',keyvarchar(64) COLLATE utf8mb4_bin NOT NULL,ttlint(11) NOT NULL DEFAULT '-1', PRIMARY KEY (id), UNIQUE KEYlock_key_index(key), KEYlock_ttl_index(ttl) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED;
2. Then I removed the file oc_file_locks.ibd from the nextcloud database with the following:
   rm /var/db/mysql/nextcloud/oc_file_locks.ibd
3. Next I moved the files oc_file_locks.ibd and oc_file_locks.frm from the test database to the nextcloud database:
   mv /var/db/mysql/test/oc_file_locks.ibd /var/db/mysql/nextcloud
mv /var/db/mysql/test/oc_file_locks.frm /var/db/mysql/nextcloud
4. Then I restarted the System (I think this is not nessesary, but thats what I did)
5. After restart I removed the oc_file_table from the nextcloud database with the SQL commands:
   use nextcloud; ALTER TABLE oc_file_locks DISCARD TABLESPACE;
DROP TABLE oc_file_locks;
6. Then I created the table again with the following:
   use nextcloud; CREATE TABLEoc_file_locks(idbigint(20) unsigned NOT NULL AUTO_INCREMENT,lockint(11) NOT NULL DEFAULT '0',keyvarchar(64) COLLATE utf8mb4_bin NOT NULL,ttlint(11) NOT NULL DEFAULT '-1', PRIMARY KEY (id), UNIQUE KEYlock_key_index(key), KEYlock_ttl_index(ttl) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED;

After all this I connected with the webpage to nextcloud, entered all parameters again and finished the setup. Unfortunately the setup process never finished, so after about 20min I restarted the server and connected again. This time I got a normal login screen and was able to login.

Note: all the paths I noticed, are from the filesystem of the jail. If you use FreeNAS to execute the commands you need to add the path of your Jail.

Thank you for your help.