Hello.
For quick readers:
The following report contains a solution to “repair” a corrupted SQL table using an empty temporary Nextcloud installation of the same version and a new temporary database with utf8mb4_unicode_ci set (before the initial temporary installation of NC).
This is for MySQL rookies or for situations with limited permissions/access on Shared Hosting.
Export the corrupted table as .csv with utf8 set, import to the temporary database (which has to have correct collation upfront), export the same table from the temporary database as .sql file. Delete the original corrupted table in the productive database (mind to backup first!) and import the same table’s sql file which you exported from the temporary database.
Now you check your productive instance and then delete the temporary installation including temporary database
I just post this as I did not find a working solution or workaround in the Nextcloud database, but I found several unsolved but closed issues with the same or similar problem I had.
Threads that have been marked as “solved” did not help me for example cause on my host I do not have sudo root access or for other reasons.
Here is a workaround that seems to work (at least it did for me) on shared hosting.
Our hoster/provider (all-inkl.com) offers a ssh command line with limited (non-root) access for the most important everyday jobs. Hence we have no access to sudo commands or to directories on the server outside our account.
case history:
I was upgrading from NC10 to NC17 with a number of issues at every step from one to the next major version upgrade. I went the path NC10 → NC11 → NC12 → NC13 → NC14 → NC15. (I stopped with NC15, as I needed a running instance for production, so cannot report about NC16 or NC17 upgrade problems at this time).
One major issue during the upgrade process was the “Themeing App”, which I had to deactivate.
Another major issue was that I had downloaded and enabled the “Everyone Group App”, but then could not anymore display a table of all users. The MySQL database semmed to have been corrupted.
At some point during the upgrade(s) I got a recommendation to switch the collation from utf8_unicode_ci to utf8mb4_general_ci or utf8mb4_unicode_ci , which I did and which might have been part of the cause of the problem.
Anyway in NC15 I realised, that the users have not been displayed in the group “Everyone”, which is bad for a productive instance even though the users have been displayed in the other group. There was no option to display all users (including a testwise newly created user).
Digging into the problem and trying to repair the database with php occ maintenance:repair I got the following error message in the command line.
In AbstractMySQLDriver.php line 115:
An exception occurred while executing ‘ALTER TABLE
oc_activityCONVERT TO
CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;’:SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large.
The maximum row size for the used table type, not counting BLOBs, is 65535
. This includes storage overhead, check the manual. You have to change some
columns to TEXT or BLOBsIn PDOStatement.php line 107:
SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large.
The maximum row size for the used table type, not counting BLOBs, is 65535
. This includes storage overhead, check the manual. You have to change some
columns to TEXT or BLOBsIn PDOStatement.php line 105:
SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large.
The maximum row size for the used table type, not counting BLOBs, is 65535
. This includes storage overhead, check the manual. You have to change some
columns to TEXT or BLOBs
After hours and days of unsuccessful research I assumed, that a new Nextcloud MySQL database of a fresh installation into a database that has the collation set right before the NC installation process starts should have the tables etcetera setup as NC expects and as occ requires.
Further more I assumed (correctly) that an export of the table as CSV file would not contain collation information etcetera as it would be just a simple textfile with only basic utf8 character-set.
solution/workaround:
In short what I did to successfully solve the problem:
- setup a new empty NC15 installation (let’s call it NC15_temp)
- create new virgin database and set collation to utf8mb4_unicode_ci
- export (backup!) the full database of the productive newly upgraded NC15 (let’s call it NC15_prod) … preferrably do that from the command line if any possible
Now optionally switch to PHPmyAdmin. - export the corrupted table oc_activity as .csv file with utf8 set including DROP argument (-> oc_activity.csv)
- import oc_activity.csv into the database of NC15_temp
- export the now good table oc_activity from the database of NC_temp as sql file (oc_activity.sql)
- import oc_activity.sql into the database of NC_prod
Go back to the ssh command line (if you have access) - run php occ maintenance:repair
Check if you have to set
‘maintenance’ => false,
in your NC15_prod/config/config.php
Your users should be visible again.
Potentially you might face similar problems with other tables. Thus you can try to solve the other table’s problem in the same way.
This might seem odd and very time consuming compared to a professional procedure of a skilled Linux/MySQL administrator with full root access to his (or his companie’s / customer’s) server.
But in case someone is a MySQL rookie or someone has only limited permissions or access on a shared hosting server, this procedure can be a live saver anyway. For me this is kind of a last stand for a productive website with corrupted tables, which I only have to fall back to every several years.
I hope my report and the workaround will help somebody.
Regards,
Torsten