Showing computer software tab causes MySQL crash

Bug Reporter · 2008-11-04 15:19:13

I have OCS-NG integrated with GLPI and am using OCS's software dictionary. I imported 2 machines into GLPI and the computers and software went into the database seemingly without issue. When I look at computer A, everything shows up properly. When I look at computer B, I can see everything except the software tab. When I click on the software tab, CPU for the MySQL process goes up significantly and the process consumes all the available memory and swap on my server and then MySQL crashes. I only have 2 machines in GLPI. computer B is a Linux box with 1000 packages installed, but all of the packages are part of an OCS-NG software dictionary call open source packages. I'm running the following:

OS: RHEL 4
OCS-NG: 1.01 / 4100
GLPI: 0.71.2
MySQL: 4.1.22

The MySQL error log captures the failed thread, but the SQL is so long, it doesn't all fit in the buffer. Here is what makes it into the log as the failed SQL:

( SELECT 1 as TYPE, glpi_dropdown_software_category.name as category, glpi_software.category as category_id, glpi_software.name as softname, glpi_inst_software.license as license, glpi_inst_software.ID as ID,glpi_licenses.expire,glpi_software.deleted, glpi_licenses.sID, GROUP_CONCAT( DISTINCT CONCAT(CONCAT_WS(' - ',glpi_licenses.version, glpi_licenses.serial),'$$',glpi_inst_software.ID) SEPARATOR '$$$$') AS version, glpi_licenses.serial, glpi_licenses.version AS orig_version, glpi_licenses.oem, glpi_licenses.oem_computer, glpi_licenses.buy
                FROM glpi_inst_software
                LEFT JOIN glpi_licenses ON ( glpi_inst_software.license = glpi_licenses.ID )
                LEFT JOIN glpi_software ON (glpi_licenses.sID = glpi_software.ID)
                LEFT JOIN glpi_dropdown_software_category ON (glpi_dropdown_software_category.ID = glpi_software.category) WHERE glpi_inst_software.cID = '1' AND glpi_software.category > 0
                                GROUP BY glpi_licenses.sID ) UNION (SELECT 2 as TYPE, glpi_dropdown_software_category.name as category, glpi_software.cate

Any help is appreciated.

Last edited by Bug Reporter (2008-11-07 15:56:35)

homebunny · 2008-11-05 16:38:26

This should be the complete query:

( SELECT 1 as TYPE, glpi_dropdown_software_category.name as category, glpi_software.category as category_id, glpi_software.name as softname, glpi_inst_software.license as license, glpi_inst_software.ID as ID,glpi_licenses.expire,glpi_software.deleted, glpi_licenses.sID, GROUP_CONCAT( DISTINCT CONCAT(CONCAT_WS(' - ',glpi_licenses.version, glpi_licenses.serial),'$$',glpi_inst_software.ID) SEPARATOR '$$$$') AS version, glpi_licenses.serial, glpi_licenses.version AS orig_version, glpi_licenses.oem, glpi_licenses.oem_computer, glpi_licenses.buy    
        FROM glpi_inst_software 
        LEFT JOIN glpi_licenses ON ( glpi_inst_software.license = glpi_licenses.ID )
        LEFT JOIN glpi_software ON (glpi_licenses.sID = glpi_software.ID) 
        LEFT JOIN glpi_dropdown_software_category ON (glpi_dropdown_software_category.ID = glpi_software.category)    
         WHERE glpi_inst_software.cID = 1 AND glpi_software.category > 0 
                GROUP BY glpi_licenses.sID ) UNION (SELECT 2 as TYPE, glpi_dropdown_software_category.name as category, glpi_software.category as category_id, glpi_software.name as softname, glpi_inst_software.license as license, glpi_inst_software.ID as ID,glpi_licenses.expire,glpi_software.deleted, glpi_licenses.sID, GROUP_CONCAT( DISTINCT CONCAT(CONCAT_WS(' - ',glpi_licenses.version, glpi_licenses.serial),'$$',glpi_inst_software.ID) SEPARATOR '$$$$') AS version, glpi_licenses.serial, glpi_licenses.version AS orig_version, glpi_licenses.oem, glpi_licenses.oem_computer, glpi_licenses.buy  
            FROM glpi_inst_software 
        LEFT JOIN glpi_licenses ON ( glpi_inst_software.license = glpi_licenses.ID ) 
            LEFT JOIN glpi_software ON (glpi_licenses.sID = glpi_software.ID)  
            LEFT JOIN glpi_dropdown_software_category ON (glpi_dropdown_software_category.ID = glpi_software.category)
 WHERE glpi_inst_software.cID = 1 AND (glpi_software.category <= 0 OR glpi_software.category IS NULL ) 
            GROUP BY glpi_licenses.sID ) ORDER BY TYPE, category, softname, version

Try to execute this in phpmyadmin ...

(In de code is the result qty is limited by 9999999, so your 1000 packages should be ok)

Rgds,

Bug Reporter · 2008-11-05 23:28:40

I executed the query directly against the database and it finished it 0.3 seconds with no issues. Why would this crash the database when executed through the front-end, but not when executed directly?

Obviously, I can't see if another section of the query is flawed since it gets truncated. Where did you get that code? Thanks.

Last edited by Bug Reporter (2008-11-06 00:12:05)

JMD · 2008-11-06 00:33:08

You could put GLPI in debug mod

http://glpi-project.org/wiki/doku.php?i … nfig:debug

Bug Reporter · 2008-11-07 15:51:06

Here are the errors I recieved from the debug. Any ideas?

On the webpage, I received these errors:

PHP ERROR: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/html/glpi/inc/dbmysql.class.php at line 164
PHP ERROR: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/html/glpi/inc/dbmysql.class.php at line 172

In php-errors, I had these errors:

2008-11-07 08:30:19
Warning(2): mysql_fetch_array(): supplied argument is not a valid MySQL result resource
Backtrace :
/var/www/html/glpi/inc/dbmysql.class.php:172            mysql_fetch_array()
/var/www/html/glpi/inc/db.function.php:56               db->fetch_array()
/var/www/html/glpi/inc/db.function.php:81               countelementsintable()
/var/www/html/glpi/inc/dropdown.function.php:1302               countelementsintableforentity()
/var/www/html/glpi/inc/software.function.php:817                dropdownsoftwaretoinstall()
/var/www/html/glpi/front/computer.form.php:259          showsoftwareinstalled()

2008-11-07 08:30:19
Warning(2): mysql_num_rows(): supplied argument is not a valid MySQL result resource
Backtrace :
/var/www/html/glpi/inc/dbmysql.class.php:164            mysql_num_rows()
/var/www/html/glpi/ajax/dropdownSelectSoftware.php:71           db->numrows()
/var/www/html/glpi/inc/ajax.function.php:62             include()
/var/www/html/glpi/inc/dropdown.function.php:1319               ajaxdropdown()
/var/www/html/glpi/inc/software.function.php:817                dropdownsoftwaretoinstall()
/var/www/html/glpi/front/computer.form.php:259          showsoftwareinstalled()

2008-11-07 08:30:19
Warning(2): mysql_num_rows(): supplied argument is not a valid MySQL result resource
Backtrace :
/var/www/html/glpi/inc/dbmysql.class.php:164            mysql_num_rows()
/var/www/html/glpi/inc/software.function.php:828                db->numrows()
/var/www/html/glpi/front/computer.form.php:259          showsoftwareinstalled()

In sql-errors.log I recieved the following errors:

2008-11-07 08:30:19
*** MySQL query error : 
***
SQL: ( SELECT 1 as TYPE, glpi_dropdown_software_category.name as category, glpi_software.category as category_id, glpi_software.name as softname, glpi_inst_software.license as license, glpi_inst_software.ID as ID,glpi_licenses.expire,glpi_software.deleted, glpi_licenses.sID, GROUP_CONCAT( DISTINCT CONCAT(CONCAT_WS(\' - \',glpi_licenses.version, glpi_licenses.serial),\'$$\',glpi_inst_software.ID) SEPARATOR \'$$$$\') AS version, glpi_licenses.serial, glpi_licenses.version AS orig_version, glpi_licenses.oem, glpi_licenses.oem_computer, glpi_licenses.buy    
        FROM glpi_inst_software 
        LEFT JOIN glpi_licenses ON ( glpi_inst_software.license = glpi_licenses.ID )
        LEFT JOIN glpi_software ON (glpi_licenses.sID = glpi_software.ID) 
        LEFT JOIN glpi_dropdown_software_category ON (glpi_dropdown_software_category.ID = glpi_software.category) WHERE glpi_inst_software.cID = \'1\' AND glpi_software.category > 0 
                GROUP BY glpi_licenses.sID ) UNION (SELECT 2 as TYPE, glpi_dropdown_software_category.name as category, glpi_software.category as category_id, glpi_software.name as softname, glpi_inst_software.license as license, glpi_inst_software.ID as ID,glpi_licenses.expire,glpi_software.deleted, glpi_licenses.sID, GROUP_CONCAT( DISTINCT CONCAT(CONCAT_WS(\' - \',glpi_licenses.version, glpi_licenses.serial),\'$$\',glpi_inst_software.ID) SEPARATOR \'$$$$\') AS version, glpi_licenses.serial, glpi_licenses.version AS orig_version, glpi_licenses.oem, glpi_licenses.oem_computer, glpi_licenses.buy  
            FROM glpi_inst_software 
        LEFT JOIN glpi_licenses ON ( glpi_inst_software.license = glpi_licenses.ID ) 
            LEFT JOIN glpi_software ON (glpi_licenses.sID = glpi_software.ID)  
            LEFT JOIN glpi_dropdown_software_category ON (glpi_dropdown_software_category.ID = glpi_software.category) WHERE glpi_inst_software.cID = \'1\' AND (glpi_software.category <= 0 OR glpi_software.category IS NULL ) 
            GROUP BY glpi_licenses.sID) ORDER BY TYPE, category, softname, version
Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
Backtrace :
/var/www/html/glpi/inc/software.function.php:806        db->query()
/var/www/html/glpi/front/computer.form.php:259        showsoftwareinstalled()
/var/www/html/glpi/front/computer.form.php
2008-11-07 08:30:19
*** MySQL query error : 
***
SQL: SELECT count(*) AS cpt 
        FROM glpi_software WHERE  ( glpi_software.FK_entities = \'0\'  )  
Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
Backtrace :
/var/www/html/glpi/inc/db.function.php:55        db->query()
/var/www/html/glpi/inc/db.function.php:81        countelementsintable()
/var/www/html/glpi/inc/dropdown.function.php:1302        countelementsintableforentity()
/var/www/html/glpi/inc/software.function.php:817        dropdownsoftwaretoinstall()
/var/www/html/glpi/front/computer.form.php:259        showsoftwareinstalled()
/var/www/html/glpi/front/computer.form.php
2008-11-07 08:30:19
*** MySQL query error : 
***
SQL: SELECT DISTINCT glpi_software.ID, glpi_software.name FROM glpi_software  WHERE glpi_software.deleted=0 AND glpi_software.is_template=0  AND FK_entities=\'0\'  ORDER BY glpi_software.name
Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
Backtrace :
/var/www/html/glpi/ajax/dropdownSelectSoftware.php:67        db->query()
/var/www/html/glpi/inc/ajax.function.php:62        include()
/var/www/html/glpi/inc/dropdown.function.php:1319        ajaxdropdown()
/var/www/html/glpi/inc/software.function.php:817        dropdownsoftwaretoinstall()
/var/www/html/glpi/front/computer.form.php:259        showsoftwareinstalled()
/var/www/html/glpi/front/computer.form.php

I think the can't connect errors were because this page caused MySQL to crash and restart, so it was unavailable for a short time.

Last edited by Bug Reporter (2008-11-07 15:56:05)

JMD · 2008-11-07 16:45:17

I think your mysql server have a problem.

It's absolutly not normal that this querry crashed your server.

Bug Reporter · 2008-11-07 18:00:36

Are there any incompatibilities with my versions of PHP, MySQL, and GLPI?

OS: RHEL 4
PHP: php-4.3.9-3.22.12
MySQL: mysql-server-4.1.22-2.el4
PHP-MySQL: php-mysql-4.3.9-3.22.12

I was unable to reproduce the error on a RHEL 5 box running MySQL 5 and PHP 5.

Last edited by Bug Reporter (2008-11-07 18:29:16)

Bug Reporter · 2008-11-10 22:20:09

I deleted everything from the database, stopped using the OCS software dictionary, and re-imported my 2 computers. Now the software tab displays properly. Now I'm getting other MySQL errors when running the Mass Import Plugin. I think there is an issue with one of the software vrsions on my machine. Has anyone had any MySQL issues with the versions I listed in this post? Thanks.

** Update **
The software tab started to crash MySQL again. It worked for a few hours and then started having the same symptoms as before. When I click on the software tab, MySQL starts to use all the available memory until MySQL crashes and restarts. I only have 2 computers in the database and only around 1200 different software packages. If I execute the query directly against MySQL, it completes in less than one second.

Last edited by Bug Reporter (2008-11-11 00:09:10)

Bug Reporter · 2008-11-11 15:24:37

Looks like a possible issue with my version of MySQL. It appears that version 4.1.22 is effected by this bug: http://bugs.mysql.com/bug.php?id=38296. They aren't patching MySQL v4.1 anymore, so it looks like it's time to upgrade to MySQL 5.x. This would explain why I can't reproduce the error on my RHEL 5 box.

Anyone else running MySQL 4.1.22 with GLPI and not getting crashes?

JMD · 2008-11-11 15:40:17

Sorry not me.

You need to know also that the next version would be only compatible with PHP 5 perhas it would be better to update both

Forum GLPI-Project

Announcement

#1 2008-11-04 15:19:13

Showing computer software tab causes MySQL crash

#2 2008-11-05 16:38:26

Re: Showing computer software tab causes MySQL crash

#3 2008-11-05 23:28:40

Re: Showing computer software tab causes MySQL crash

#4 2008-11-06 00:33:08

Re: Showing computer software tab causes MySQL crash

#5 2008-11-07 15:51:06

Re: Showing computer software tab causes MySQL crash

#6 2008-11-07 16:45:17

Re: Showing computer software tab causes MySQL crash

#7 2008-11-07 18:00:36

Re: Showing computer software tab causes MySQL crash

#8 2008-11-10 22:20:09

Re: Showing computer software tab causes MySQL crash

#9 2008-11-11 15:24:37

Re: Showing computer software tab causes MySQL crash

#10 2008-11-11 15:40:17

Re: Showing computer software tab causes MySQL crash

Board footer