Trying to link locations and status to an asset generates "The results could not be loaded" error

[mo-wcma]

Code of Conduct

• I agree to follow this project's Code of Conduct

Is there an existing issue for this?

• I have searched the existing issues

Version

v11.0.1 and v11.0.2

Bug description

As per forum post here: https://forum.glpi-project.org/viewtopic.php?pid=520785#p520785 and title of this report.

Trying to load these relations in the dropdown from an asset generates this error. Debuging reveals the error bellow.

I am using a DigitalOcean managed database server. By default, ONLY_FULL_GROUP_BY is enabled. Thankfully I can disable it and doing so fixes the issue, but other managed database engines may not allow this?

Relevant log output

[2025-11-06 21:55:57] glpi.CRITICAL:   *** Uncaught PHP Exception RuntimeException: "MySQL query error: Expressio
n #1 of ORDER BY clause is not in SELECT list, references column 'glpi.glpi_entities.completename' which is not i
n SELECT list; this is incompatible with DISTINCT (3065) in SQL query "SELECT DISTINCT `glpi_states`.* FROM `glpi
_states` LEFT JOIN `glpi_dropdownvisibilities` ON (`glpi_dropdownvisibilities`.`items_id` = `glpi_states`.`id` AN
D `glpi_dropdownvisibilities`.`itemtype` = 'State') LEFT JOIN `glpi_entities` ON (`glpi_entities`.`id` = `glpi_st
ates`.`entities_id`) WHERE `glpi_dropdownvisibilities`.`itemtype` = 'State' AND `glpi_dropdownvisibilities`.`visi
ble_itemtype` = 'Computer' AND `glpi_dropdownvisibilities`.`is_visible` = '1' AND ((`glpi_states`.`entities_id` =
 '0' OR (`glpi_states`.`is_recursive` = '1' AND `glpi_states`.`entities_id` IN ('0')))) ORDER BY `glpi_entities`.
`completename`, `glpi_states`.`completename` LIMIT 100"." at DBmysql.php line 371
  Backtrace :
  ./src/DBmysql.php:371                              
  ./src/DBmysqlIterator.php:123                      DBmysql->doQuery()
  ./src/DBmysql.php:1040                             DBmysqlIterator->execute()
  ./src/Dropdown.php:3158                            DBmysql->request()
  ./ajax/getDropdownValue.php:48                     Dropdown::getDropdownValue()
  ...Glpi/Controller/LegacyFileLoadController.php:64 require()
  ./vendor/symfony/http-kernel/HttpKernel.php:181    Glpi\Controller\LegacyFileLoadController->__invoke()
  ./vendor/symfony/http-kernel/HttpKernel.php:76     Symfony\Component\HttpKernel\HttpKernel->handleRaw()
  ./vendor/symfony/http-kernel/Kernel.php:197        Symfony\Component\HttpKernel\HttpKernel->handle()
  ./public/index.php:70                              Symfony\Component\HttpKernel\Kernel->handle()

[2025-11-06 21:55:58] glpi.CRITICAL:   *** Uncaught PHP Exception RuntimeException: "MySQL query error: Expression #1 of ORDER BY clause is not in SELECT list, references column 'glpi.glpi_entities.completename' which is not in SELECT list; this is incompatible with DISTINCT (3065) in SQL query "SELECT DISTINCT `glpi_states`.* FROM `glpi_states` LEFT JOIN `glpi_dropdownvisibilities` ON (`glpi_dropdownvisibilities`.`items_id` = `glpi_states`.`id` AND `glpi_dropdownvisibilities`.`itemtype` = 'State') LEFT JOIN `glpi_entities` ON (`glpi_entities`.`id` = `glpi_states`.`entities_id`) WHERE `glpi_dropdownvisibilities`.`itemtype` = 'State' AND `glpi_dropdownvisibilities`.`visible_itemtype` = 'Computer' AND `glpi_dropdownvisibilities`.`is_visible` = '1' AND ((`glpi_states`.`entities_id` = '0' OR (`glpi_states`.`is_recursive` = '1' AND `glpi_states`.`entities_id` IN ('0')))) ORDER BY `glpi_entities`.`completename`, `glpi_states`.`completename` LIMIT 100"." at DBmysql.php line 371
  Backtrace :
  ./src/DBmysql.php:371                              
  ./src/DBmysqlIterator.php:123                      DBmysql->doQuery()
  ./src/DBmysql.php:1040                             DBmysqlIterator->execute()
  ./src/Dropdown.php:3158                            DBmysql->request()
  ./ajax/getDropdownValue.php:48                     Dropdown::getDropdownValue()
  ...Glpi/Controller/LegacyFileLoadController.php:64 require()
  ./vendor/symfony/http-kernel/HttpKernel.php:181    Glpi\Controller\LegacyFileLoadController->__invoke()
  ./vendor/symfony/http-kernel/HttpKernel.php:76     Symfony\Component\HttpKernel\HttpKernel->handleRaw()

Page URL

https://glpiinstance.domain/front/computer.form.php

Steps To reproduce

Open GLPI super admin screen
Navigate to assets > computers (or any other asset)
Add a new asset
Select location or status dropdown
Error loaded below field

Your GLPI setup information

v11.0.1 or v11.0.2
VM with 1 vcpu 2GB RAM
Ubuntu 24.04
Managed MYSQL DB server (MySQL 8, no other info)

Anything else?

No response

[mo-wcma]

To clarify, disabling ONLY_FULL_GROUP_BY on the MySQL server fixes the issue.

[trasher]

By default, this mode should be disabled from GLPI by following query:

SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '')

This is already done like this in GLPI v10. Did you try removing all plugins?

[cconard96]

I wonder if this could fail based on SQL user permissions or something else. It is a query direct on the mysqli connection so we don't log errors for it and there is no error check afterwards.

Can you try adding this after the SET SESSION sql_mode line in src/DBmysql.php?

if ($this->dbh->errno) {
    trigger_error(sprintf('MySQL set sql_mode warning: %s (%d).', $this->dbh->error, $this->dbh->errno), E_USER_WARNING);
}

Then, try to link locations or statuses again and check the files/_log/php-errors.log file to see if the warning is logged.

[mo-wcma]

By default, this mode should be disabled from GLPI by following query:

SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '')

This is already done like this in GLPI v10. Did you try removing all plugins?

I am running a vanilla install, so no plugins that I am aware of are installed.

[mo-wcma]

I wonder if this could fail based on SQL user permissions or something else. It is a query direct on the mysqli connection so we don't log errors for it and there is no error check afterwards.

Can you try adding this after the SET SESSION sql_mode line in src/DBmysql.php?

if ($this->dbh->errno) {
trigger_error(sprintf('MySQL set sql_mode warning: %s (%d).', $this->dbh->error, $this->dbh->errno), E_USER_WARNING);
}

Then, try to link locations or statuses again and check the files/_log/php-errors.log file to see if the warning is logged.

So I added this and no additional logging information is being added to php-errors.log.