[Bug]: Missing index for oc_appconfig.lazy

ChristophWurst commented 3 weeks ago

⚠️ This issue respects the following points: ⚠️

[X] This is a bug, not a question or a configuration/webserver/proxy issue.
[X] This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
[X] Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
[X] I agree to follow Nextcloud's Code of Conduct.

Bug description

The query

SELECT
  `appid`,
  `configkey`,
  `configvalue`,
  `type`
FROM
  `oc_appconfig`
WHERE
  `lazy` = 0

does not use an index. It's executed often.

Query id 43744e10e8843e7dff44336c2481d8ad

Steps to reproduce

???

Expected behavior

Fast queries

Installation method

None

Nextcloud Server version

29

Operating system

None

PHP engine version

None

Web server

None

Database engine version

None

Is this bug present after an update or on a fresh install?

None

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

[ ] Default user-backend (database)
[ ] LDAP/ Active Directory
[ ] SSO - SAML
[ ] Other

Configuration report

No response

List of activated Apps

No response

Nextcloud Signing status

No response

Nextcloud Logs

No response

Additional info

No response

Xeladaren commented 1 week ago

I have similar issue post my upgrade to nextcloud 29, when I try to do an occ upgrade :

www-data@:~/html$ php occ upgrade
Nextcloud or one of the apps require upgrade - only a limited number of commands are available
You may use your browser or the occ upgrade command to do the upgrade
Setting log level to debug

In DbalException.php line 71:

  An exception occurred while executing a query: SQLSTATE[42703]: Undefined column: 7 ERROR:  column "lazy" of relation "oc_appconfig" does not exist  
  LINE 1: INSERT INTO "oc_appconfig" ("appid", "lazy", "type", "config...                                                                              
                                               ^                                                                                                       

In ExceptionConverter.php line 68:

  An exception occurred while executing a query: SQLSTATE[42703]: Undefined column: 7 ERROR:  column "lazy" of relation "oc_appconfig" does not exist  
  LINE 1: INSERT INTO "oc_appconfig" ("appid", "lazy", "type", "config...                                                                              
                                               ^                                                                                                       

In Exception.php line 28:

  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "lazy" of relation "oc_appconfig" does not exist  
  LINE 1: INSERT INTO "oc_appconfig" ("appid", "lazy", "type", "config...                               
                                               ^                                                        

In Statement.php line 130:

  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "lazy" of relation "oc_appconfig" does not exist  
  LINE 1: INSERT INTO "oc_appconfig" ("appid", "lazy", "type", "config...                               
                                               ^                                                        

upgrade

And this block my nextcloud server.

ChristophWurst commented 1 week ago

That is unrelated. Look for exact matches of the error or submit a new report.

NikhilMunda commented 1 week ago

I was also facing the same issue. I am using Docker I have used this process

Step 1: Identify Your Database Container First, you need to identify the name of your database container. Run the following command to list all running Docker containers:

docker ps

Look for your Nextcloud database container, typically named something like nextcloud-db or mysql.

Step 2: Access the Database Container Use the docker exec command to access the shell of your database container. For example, if your container is named nextcloud-db:

docker exec -it nextcloud-db /bin/bash

Step 3: Access MySQL Once inside the container, access the MySQL data

mysql -u your_username -p

You will be prompted for the password. Use the credentials you set when you configured the Nextcloud database.

Step 4: Select the Nextcloud Database After logging into MySQL, select your Nextcloud database:

USE your_nextcloud_database;

Replace your_nextcloud_database with the actual name of your database.

i.e it can be found config.php under dbname and password is also found there

Step 5: Add the Missing Column Identify the table where the column is missing and add it. For example, if the missing column is ‘lazy’ in a table named oc_appconfig:

alter table oc_appconfig add lazy tinyint(1) null default 0;

then

alter table oc_appconfig add `type` int(11) not null default 2;

Step 6: Verify the Changes Verify that the column has been added correctly:

DESCRIBE oc_appconfig;

This command will show the structure of the table, including the new column.

![Screenshot 2024-07-04 185710] (https://github.com/nextcloud/server/assets/46515697/f5d1d966-8867-4f62-aa51-e041be31e391)

After that i turned off the maintainance mode and manually started the upgrade proccess on the web page. At the end it showed one error but after reloading/refreshing the page it open to dashboard and thus the nextcloud is updated to 29.0.3.4.

nextcloud / server