Solved Nextcloud upgrade v17 - v18 sql error

cbrace

cbrace

Hi all,

I have Nextcloud running on my FreeBSD vps. I would like to upgrade it to v18. I upgrade the port and run the upgrade command:
Code: 
$ sudo -u www php occ upgrade

Doctrine\DBAL\Exception\TableNotFoundException: An exception occurred while executing 'ALTER TABLE oc_groups ADD displayname VARCHAR(255) DEFAULT '' NOT NULL':`

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'nextcloud.oc_groups' doesn't exist
    Update failed
    Maintenance mode is kept active
    Reset log level
I posted a query on the Nextcloud forum but not a single response: Occ upgrade error: Table ‘nextcloud.oc_groups’ doesn’t exist.

Since I didn't get any replies there, I am trying here. Anyone have any ideas for me?

Nextcloud v17.0.7
FreeBSD 12.1-RELEASE-p5
apache v2.4.43
PHP v7.3.17
mySQL v5.6.48
 
cbrace said:
I upgrade the port and run the upgrade command:
Click to expand...
That's strange. Would you want to downgrade back to the working version, check that it's working, backup and try upgrade again. The DB error is saying the table should have been in the previous installation but not found. Try run the command on the working v17 if it will work.
 
Thanks for the suggestion. My nextcloud installation is again at v17.0.7.

This is what I see when I run the command again:
Code: 
$ sudo -u www php occ upgrade
Nextcloud is already latest version

Any thoughts?
 
cbrace said:
Thanks for the suggestion. My nextcloud installation is again at v17.0.7.

This is what I see when I run the command again:
Code: 
$ sudo -u www php occ upgrade
Nextcloud is already latest version

Any thoughts?
Click to expand...
Are you running this command in the Nextcloud17 install?
 
That makes sense, then. You're at the latest version as far as Nextcloud 17 is concerned. You've gotta run the occ upgrade command in the Nextcloud 18 install folder. Sorry if I'm being vague, I haven't migrated Nextcloud to Freebsd yet.
 
Maybe I'm not being clear here. Let me try again.

I have a www/nextcloud installation.

It is located in the default location: /usr/local/www/nextcloud

I have been running v17. I would like to upgrade it to v18.

Using pkg, I upgraded to v18.

Pkg info : nextcloud-php73-18.0.4_2

Running occ to upgrade the system didn't work (see above).

Because I had made ZFS snapshots of /usr/local/www/nextcloud and /var/db/mysql/nextcloud I could roll back to the earlier version so I would again have a running nextcloud installation.

I still need to upgrade to v18, as at some point v17 will be EOL'ed, but I'm stuck with the sql error.

Hope my situation is clear now.
 
cbrace said:
Because I had made ZFS snapshots of /usr/local/www/nextcloud and /var/db/mysql/nextcloud I could roll back to the earlier version so I would again have a running nextcloud installation.
Click to expand...
You could do so, remove nextcloudv17 and install v18 via ports.

cbrace said:
Thanks for the suggestion. My nextcloud installation is again at v17.0.7.

This is what I see when I run the command again:
Code: 
$ sudo -u www php occ upgrade
Nextcloud is already latest version

Any thoughts?
Click to expand...
But the error is now gone. If it is still v17, then you need do "pkg upgrade" as Jose advised before running the occ upgrade. You may be better off removing it and installing via ports in order to fix possible problems. Your DB and data should be fine.
 
Lamia I tried this several times already: Run pkg to upgrade to v18, then run occ. Each time I see the sql error.
 
First: I don't use Nextcloud.

The error message says that the table "oc_groups" doesn't exist in your database. That I would check first: You have a MySQL database for Nextcloud with a username and password configured, so log into MySQL by command line:

mysql -u username -ppassword database

Next list the tables of this database:

SHOW TABLES;

Is there a table "oc_groups"? If yes, then check if you can run the crashing command manually:

ALTER TABLE oc_groups ADD displayname VARCHAR(255) DEFAULT '' NOT NULL;

If that all works then I assume that your upgrade command didn't log into MySQL correctly. But then I would expect an earlier error message… And if your Nextcloud database tables are listed, but "oc_groups" isn't in it, then I would write a bug report to Nextcloud. If the table exists, but the ALTER command fails, then there is a problem belonging to the rights management of your database user - it might be the case that you're user isn't allowed to manipulate the database structure.
 
cbrace said:
Lamia I tried this several times already: Run pkg to upgrade to v18, then run occ. Each time I see the sql error.
Click to expand...
An Upgrade is different from a fresh installation (with existing DB and files); so is a port installation from a pkg install/upgrade.

There were only suggestions.
 
I've never updated my nextcloud instance to a new major version, and I use PostgreSQL instead of MySQL.
But here's something to consider:

When I installed my instance I went with the official documentation, so nothing FreeBSD specific.
But I remember that installing went something like this:
  1. create a db
  2. create a db user with FULL ACCESS to said db
  3. let nextcloud setup (occ) do it's thing and create tables and whatnot
  4. remove FULL ACCESS and set it a bit more restrictive
  5. let the web interface do the rest
Maybe that step 4 is something you did as well, and now that is preventing occ to update.
 
I've done many Nextcloud upgrades on Gentoo Linux, but also with Postgresql. I also did a migration Owncloud -> Nextcloud. I have encountered SQL problems sometimes, but not this one. IIRC oc_groups is one of the very basic tables that has been around since the earliest Owncloud days. Are you sure your database is up and your nextcloud user has full rights to it?
 
Thanks everyone for the inputs.

jmos I log into mysql as the "nextcloud" user. The output of the SHOW TABLES; command indicates there seems to be an oc_groups table:

Screenshot from 2020-06-22 21-07-34.png


However, when I run the command, it fails:
Code: 
mysql> ALTER TABLE oc_groups ADD displayname VARCHAR(255) DEFAULT '' NOT NULL;
ERROR 1146 (42S02): Table 'nextcloud.oc_groups' doesn't exist
mysql>

FWIW, when I open phpmyadmin and go to the nextcloud database, I don't see the oc_groups table:

Screenshot from 2020-06-22 21-12-32.png

Any thoughts about what might be going on here?
 
Also, since the question of privileges has been mentioned, this is the current setting for the "nextcloud" user as displayed in phpmyadmin:

Screenshot from 2020-06-22 21-20-29.png
 
Code: 
mysql>  SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| nextcloud  |
+------------+
1 row in set (0.01 sec)
 
Does SELECT * FROM oc_groups; also fail with table not found? Considering you restored this database from a snapshot, is it possible you're running into one of these situations?
stackoverflow.com

MySQL > Table doesn't exist. But it does (or it should)

I changed the datadir of a MySQL installation and all the bases moved correctly except for one. I can connect and USE the database. SHOW TABLES also returns me all the tables correctly, and the fi...
stackoverflow.com stackoverflow.com
 
Yes, same error:
Code: 
mysql>  SELECT * FROM oc_groups;
ERROR 1146 (42S02): Table 'nextcloud.oc_groups' doesn't exist
Thanks for the link to that Stack Overflow page. Sounds like maybe the zfs snapshot may indeed have interfered in some way.

I see now that the oc_groups table is in /var/db/mysql/nextcloud:
Code: 
# ls -l /var/db/mysql/nextcloud/oc_groups*
-rw-rw----  1 mysql  mysql   8558 May  3  2017 /var/db/mysql/nextcloud/oc_groups.frm
-rw-rw----  1 mysql  mysql  98304 May  3  2017 /var/db/mysql/nextcloud/oc_groups.ibd
Any idea about how to make this table visible again?
 
OK, thanks anyway. One thing I've learned is that I should probably shut down mysql-server before creating a zfs snapshot. I will look through that Stack Overflow page now.
 
I've now imported the single oc_groups table from another installation.

To be able to import the table, I had to both drop it from within mysql as well as delete the .ibd file manually in terminal, as this error message was displayed:
Code: 
Tablespace for table '`nextcloud`.`oc_groups`' exists.

I will try upgrade to v18 tomorrow. Thanks to everyone for the inputs!
 
You must log in or register to reply here.
Back
Top