[SOLVED] Super-admin big problem with loading software list

WebGreg · 2022-10-07 13:13:54

Hi

I have had a software problem since upgrading from 9.x to 10.x. But only on the super admin profile. Other profiles can load the list quickly. I have to wait for many minutes. The worst situation is when I switch the "Toggle browse" option. With debuge mode:

I have tried to remove all the software but that doesn't fix the problem either.

Last edited by WebGreg (2023-06-20 09:17:41)

WebGreg · 2022-10-10 09:49:22

Only I have this problem?

Maybe someone at least knows how to do this "range optimization"?

WebGreg · 2022-10-18 12:50:42

[2022-10-18 12:21:49] glpisqllog.WARNING: DBmysql::query() in /var/www/html/glpi/src/DBmysql.php line 404
*** MySQL query warnings:
SQL: SELECT * FROM `glpi_softwares` WHERE `glpi_softwares`.`id` = '---2119' LIMIT 1
Warnings:
1292: Truncated incorrect DOUBLE value: '---2119'

How to fix this? 13101 - this is the lowest id I have in the database.

Last edited by WebGreg (2022-10-18 13:00:46)

WebGreg · 2022-10-21 09:20:47

Can anyone help me fix these errors?

WebGreg · 2022-11-03 14:24:35

I have huge logs with the error messages given above

WebGreg · 2022-11-09 10:55:49

Any hints?

cconard96 · 2022-11-09 13:58:55

Does it work well if you decrease the number of rows shown on each page of the results using the dropdown?

Besides that, the limits being exceeded all seem to be on the DB server side.

WebGreg · 2022-11-09 14:39:28

Hi cconard96

With 15 lines, it's a bit better, but I will not say that it works without problems.

However, when I switch the "Toggle browse" option - the white screen for a few minutes until it finally crashes the errors as above.

As I mentioned, it worked great in 9.x. After going to 10 - drama.

Edit - I can't see errors in sql-errors when I go to the software tab. If I remember, before version 10.0.4, they already appeared. Now I only have errors after switch the "Toggle browse"

[2022-11-09 14:14:25] glpisqllog.WARNING: DBmysql::query() in /var/www/html/glpi/src/DBmysql.php line 404
*** MySQL query warnings:
SQL: SELECT DISTINCT `glpi_softwares`.`id` AS id, 'user' AS currentuser,
`glpi_softwares`.`entities_id`, `glpi_softwares`.`is_recursive`, `glpi_softwares`.`name` AS `ITEM_Software_1`,
`glpi_softwares`.`id` AS `ITEM_Software_1_id`,
`glpi_manufacturers`.`name` AS `ITEM_Software_23`, GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_softwareversions`.`name`, '__NULL__'),
'$#$',`glpi_softwareversions`.`id`) ORDER BY `glpi_softwareversions`.`id` SEPARATOR '$$##$$')
AS `ITEM_Software_5`,

GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_operatingsystems_0a35c270152be19b5c8a485502badcd7`.`name`, '__NULL__'),
'$#$',`glpi_operatingsystems_0a35c270152be19b5c8a485502badcd7`.`id`) ORDER BY `glpi_operatingsystems_0a35c270152be19b5c8a485502badcd7`.`id` SEPARATOR '$$##$$')
AS `ITEM_Software_4`,

COUNT(DISTINCT `glpi_items_softwareversions_37010ce8f4633da91ded3e0a4c256dc9`.`id`) AS `ITEM_Software_72`,
FLOOR(SUM(`glpi_softwarelicenses_6427cab6934982da77f819e079502198`.`number`)
* COUNT(DISTINCT `glpi_softwarelicenses_6427cab6934982da77f819e079502198`.`id`)
/ COUNT(`glpi_softwarelicenses_6427cab6934982da77f819e079502198`.`id`)) AS `ITEM_Software_163`,
........

[2022-11-09 14:14:25] glpisqllog.WARNING: DBmysql::query() in /var/www/html/glpi/src/DBmysql.php line 404
*** MySQL query warnings:
SQL: SELECT `glpi_softwarecategories`.`id`, `glpi_softwarecategories`.`name`, `glpi_softwarecategories`.`softwarecategories_id`, (SELECT COUNT(DISTINCT(`glpi_softwares`.`id`)) AS `cpt` FROM `glpi_softwares` WHERE `glpi_softwares`.`softwarecategories_id` = `glpi_softwarecategories`.`id` AND `glpi_softwares`.`id` IN ('0', '12622', '12623', '12624', '12625', '12626', '12627', '12628', '12629', ..........>
Warnings:
3170: Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.
Backtrace :
src/DBmysqlIterator.php:112
src/DBmysql.php:1056 DBmysqlIterator->execute()
src/Features/TreeBrowse.php:222 DBmysql->request()
src/Features/TreeBrowse.php:75 Software::getTreeCategoryList()
src/Search.php:174 Software::showBrowseView()
front/software.php:42 Search::show()
{"user":"25@glpi","mem_usage":"0.056\", 11.62Mio)"}

[2022-11-09 14:14:25] glpisqllog.WARNING: DBmysql::query() in /var/www/html/glpi/src/DBmysql.php line 404
*** MySQL query warnings:
SQL: SELECT COUNT(DISTINCT(`glpi_softwares`.`id`)) AS `cpt` FROM `glpi_softwares` WHERE `glpi_softwares`.`softwarecategories_id` = '0' AND `glpi_softwares`.`id` IN ('0', '12622', '12623', '12624', '12625', ...........>
Warnings:
3170: Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.
Backtrace :
src/DBmysqlIterator.php:112
src/DBmysql.php:1056 DBmysqlIterator->execute()
src/Features/TreeBrowse.php:247 DBmysql->request()
src/Features/TreeBrowse.php:75 Software::getTreeCategoryList()
src/Search.php:174 Software::showBrowseView()
front/software.php:42 Search::show()

Last edited by WebGreg (2022-11-09 15:23:22)

cconard96 · 2022-11-09 20:12:41

I don't know if it is related to the memory issue, but that query definitely looks like it needs to be optimized. On large installs, it doesn't seem feasible to have every single software ID in a list in the query like that.
I'll need to look into it more to understand the purpose of that part and look for other ways to try and simplify the query.

WebGreg · 2022-11-09 23:30:57

I'm so happy that someone finally confirmed it. So far I felt lonely with it

cconard96 · 2022-11-09 23:49:08

How many Software items do you have in your GLPI? I'm trying to bulk add a bunch to mine so I can try to accurately compare any before and after performance changes.

WebGreg · 2022-11-10 00:38:07

Showing 1 to 15 of 5578 rows

Once (I don't remember when and what I changed in the meantime) I recreated the machine from the backup to the test. And now the test one is running smoothly. 2800 rows. Both machines have the same amount of memory and processors. The test one does not communicate with agents, so it does not download new data, it is not spammed with history entries.

WebGreg · 2022-11-10 10:54:43

cconard96 wrote:

I don't know if it is related to the memory issue

Let me just add that there is no doubt - in version 9.x I had no problems with 10k lines. Now I'm having trouble halfway through it and after adding more memory.

cconard96 wrote:

I'll need to look into it more to understand the purpose of that part and look for other ways to try and simplify the query.

Or maybe bite it from the other side? A single Windows computer can register 500 software items, e.g.

Dell Command | Update 1.10.7.17134    neutral Yes system Yes
Dell Command | Update 10.1807.9.0    x64    Yes system Yes
Dell Command | Update 10.1807.9.0    x86    Yes system Yes
Dell Command | Update 3.0.122.0       x64    Yes application Yes
Dell Command | Update 4.6.0       i586    Yes application Yes

But after logging in to that computer, I only see 30 items in the software list. I am still not convinced that the amount of software distributed by the glpi agent is actually justified.

cconard96 · 2022-11-10 11:01:07

I posted a comment on the GitHub issue you have open with a link to a potential fix that I cannot fully test as MariaDB doesn't seem to have this limit.
Can you check if it fixes the SQL warning or at least improves performance?
https://github.com/glpi-project/glpi/issues/12779

WebGreg · 2023-06-20 09:17:30

After deleting the account causing the problem and adding it again - everything works fine.

Forum GLPI-Project

Announcement

#1 2022-10-07 13:13:54

[SOLVED] Super-admin big problem with loading software list

#2 2022-10-10 09:49:22

Re: [SOLVED] Super-admin big problem with loading software list

#3 2022-10-18 12:50:42

Re: [SOLVED] Super-admin big problem with loading software list

#4 2022-10-21 09:20:47

Re: [SOLVED] Super-admin big problem with loading software list

#5 2022-11-03 14:24:35

Re: [SOLVED] Super-admin big problem with loading software list

#6 2022-11-09 10:55:49

Re: [SOLVED] Super-admin big problem with loading software list

#7 2022-11-09 13:58:55

Re: [SOLVED] Super-admin big problem with loading software list

#8 2022-11-09 14:39:28

Re: [SOLVED] Super-admin big problem with loading software list

#9 2022-11-09 20:12:41

Re: [SOLVED] Super-admin big problem with loading software list

#10 2022-11-09 23:30:57

Re: [SOLVED] Super-admin big problem with loading software list

#11 2022-11-09 23:49:08

Re: [SOLVED] Super-admin big problem with loading software list

#12 2022-11-10 00:38:07

Re: [SOLVED] Super-admin big problem with loading software list

#13 2022-11-10 10:54:43

Re: [SOLVED] Super-admin big problem with loading software list

#14 2022-11-10 11:01:07

Re: [SOLVED] Super-admin big problem with loading software list

#15 2023-06-20 09:17:30

Re: [SOLVED] Super-admin big problem with loading software list

Board footer