Forum GLPI-Project

kandresen

Member

Registered: 2017-09-18

Posts: 8

Database tables are not closed in safe manner leading to corruption

Using GLPI version 9.1.6 with Nginx.
I ensured the database is clean using the commands:
service nginx stop
myisamchk -w -s --check /var/lib/mysql/*/*.MYI
myisamchk --safe-recover /var/lib/mysql/*/*.MYI
myisamchk -w -s --check /var/lib/mysql/*/*.MYI

With no errors mentioned I simply start Nginx using:
service nginx start
login to GLPI
in the search box, type "cron" and search
service nginx stop
myisamchk -w -s --check /var/lib/mysql/*/*.MYI
Result:
myisamchk: MyISAM file /var/lib/mysql/test1glpi/glpi_crontasklogs.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Size of datafile is: 740232            Should be: 743028
myisamchk: error: Found key at page 785408 that points to record outside datafile
myisamchk: error: Record-count is not ok; is 15415        Should be: 15473
myisamchk: warning: Found      15415 key parts. Should be: 15473
MyISAM-table '/var/lib/mysql/test1glpi/glpi_crontasklogs.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/test1glpi/glpi_crontasks.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/test1glpi/glpi_crontasks.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/test1glpi/glpi_events.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Size of datafile is: 6140              Should be: 6204
myisamchk: error: Found key at page 4096 that points to record outside datafile
myisamchk: error: Record-count is not ok; is 90           Should be: 91
myisamchk: warning: Found         90 key parts. Should be: 91
MyISAM-table '/var/lib/mysql/test1glpi/glpi_events.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/test1glpi/glpi_logs.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: warning: Size of indexfile is: 234496        Should be: 230400
myisamchk: error: Size of datafile is: 368140            Should be: 368668
myisamchk: error: Found 101 keys of 5356
myisamchk: error: Record-count is not ok; is 5355         Should be: 5356
myisamchk: warning: Found       5355 key parts. Should be: 5362
MyISAM-table '/var/lib/mysql/test1glpi/glpi_logs.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/test1glpi/glpi_users.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Size of datafile is: 852               Should be: 864
myisamchk: error: Key 12 doesn't point at same records that key 1
myisamchk: error: Keypointers and record positions doesn't match
MyISAM-table '/var/lib/mysql/test1glpi/glpi_users.MYI' is corrupted
Fix it using switch "-r" or "-o"

Last edited by kandresen (2017-09-20 02:07:20)

kandresen

Member

Registered: 2017-09-18

Posts: 8

Re: Database tables are not closed in safe manner leading to corruption

I have now installed a completely new debian 9 server based on the netinstall image.
0.0: setup new virtual guest system: Debian 9, all additional packages except openssh server deactivated.
0.1: download glpi using:  wget --no-check-certificate http://github.com/glpi-project/glpi/rel … -9.1.6.tgz
0.2: unzip glpi: tar -xzvf glpi-9.1.6.tgz
0.3: follow install instructions in glpi/README.md
0.4: apt get install apache2 mariadb-server php-json php-mbstring php-mysqli php-dg php-curl libapache2-mod-php7.0 php-xml php-xmlrpc
0.5: mysql -u root -p 
create database glpi;
create user 'glpi'@'localhost' identified by 'glpitest';
grant all on glpi.* to 'glpi'@'localhost';
0.6: mv /root/glpi /var/www/glpi
0.7: chown www-data: -R /var/www/glpi
0.8: edit /etc/apache2/sites-enabled/000-default.conf
DocumentRoot = /var/www/glpi
0.9: service apache2 restart
0.10: crontab -e
* * * * * www-data /usr/bin/php7.0 /var/www/glpi/front/cron.php &>/dev/null
1.0: open browser to point at server, in my case: http://192.168.20.37
1.1: click install glpi
1.2: ignore the last few missing modules and the final warning about the "files" folder, and click to install using the username/password given above.
1.3: login using username glpi, password glpi
2: switch to a linux shell and run command:
myisamchk -w -s --check /var/lib/mysql/*/*.MYI
Result:
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_apiclients.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_apiclients.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_blacklists.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_blacklists.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_calendarsegments.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_calendarsegments.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_calendars.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_calendars.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_configs.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_configs.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_crontasklogs.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_crontasklogs.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_crontasks.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_crontasks.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_devicememorytypes.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_devicememorytypes.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_displaypreferences.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_displaypreferences.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_documenttypes.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_documenttypes.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_entities.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_entities.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_events.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_events.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_filesystems.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_filesystems.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_interfacetypes.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_interfacetypes.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_logs.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_logs.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_notifications.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_notifications.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_notificationtargets.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_notificationtargets.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_notificationtemplates.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_notificationtemplates.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_notificationtemplatetranslations.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_notificationtemplatetranslations.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_profilerights.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_profilerights.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_profiles.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_profiles.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_profiles_users.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_profiles_users.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_projectstates.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_projectstates.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_requesttypes.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_requesttypes.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_ruleactions.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_ruleactions.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_rulecriterias.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_rulecriterias.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_rulerightparameters.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_rulerightparameters.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_rules.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_rules.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_softwarecategories.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_softwarecategories.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_softwarelicensetypes.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_softwarelicensetypes.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_ssovariables.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_ssovariables.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_tickets.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_tickets.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_tickets_users.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_tickets_users.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_tickettemplatemandatoryfields.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_tickettemplatemandatoryfields.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_tickettemplates.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_tickettemplates.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_transfers.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_transfers.MYI' is usable but should be fixed
myisamchk: MyISAM file /var/lib/mysql/glpi/glpi_users.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/glpi/glpi_users.MYI' is usable but should be fixed

Ok, though it is not good practice not to close tables after use, there are no corrupted tables yet.
Also tried creating a test ticket, with same result.
It might be corruption only happen after adding the fusioninventory plugin?

kandresen

Member

Registered: 2017-09-18

Posts: 8

Re: Database tables are not closed in safe manner leading to corruption

I installed a vanilla version of GLPI version 0.85.5, and got essentially the same result as above. I did not try adding data to the tables, just login the user glpi and check.

kandresen

Member

Registered: 2017-09-18

Posts: 8

Re: Database tables are not closed in safe manner leading to corruption

It is not only plugins. I attempted a login with the vanilla GLPI 9.1.6 database again, and got following result:
myisamchk -w -s --check /var/lib/mysql/*/*.MYI
myisamchk: MyISAM file /var/lib/mysql/test1glpi/glpi_crontasklogs.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Size of datafile is: 740232            Should be: 745908
myisamchk: error: Found key at page 785408 that points to record outside datafile
myisamchk: error: Record-count is not ok; is 15415        Should be: 15533
myisamchk: warning: Found      15415 key parts. Should be: 15533
MyISAM-table '/var/lib/mysql/test1glpi/glpi_crontasklogs.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/test1glpi/glpi_crontasks.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/test1glpi/glpi_crontasks.MYI' is usable but should be fixed
root@webdeb9:~#  myisamchk -w -s --check /var/lib/mysql/*/*.MYI
myisamchk: MyISAM file /var/lib/mysql/test1glpi/glpi_crontasklogs.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: error: Size of datafile is: 740232            Should be: 758452
myisamchk: error: Found key at page 785408 that points to record outside datafile
myisamchk: error: Record-count is not ok; is 15415        Should be: 15795
myisamchk: warning: Found      15415 key parts. Should be: 15795
MyISAM-table '/var/lib/mysql/test1glpi/glpi_crontasklogs.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: MyISAM file /var/lib/mysql/test1glpi/glpi_crontasks.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table '/var/lib/mysql/test1glpi/glpi_crontasks.MYI' is usable but should be fixed

stoatwblr

Member

Registered: 2014-04-13

Posts: 118

Re: Database tables are not closed in safe manner leading to corruption

If you manually change each table's engine to InnoDB, do you still get the problem?

kandresen

Member

Registered: 2017-09-18

Posts: 8

Re: Database tables are not closed in safe manner leading to corruption

Changing the default database engine to InnoDB does indeed seem to resolve the issue! For reference to anyone wanting to try:
1: Ensure the tables are without error: myisamchk -w -s --check /var/lib/mysql/*/*.MYI
2: If no errors encountered, to to next step, else repeat step 1 after running: myisamchk --safe-recover /var/lib/mysql/*/*.MYI   
3: When no errors encountered do backup of database: mysqldump --databases <database> > <database>_backup.sql
4: run the command to ALTER the engine of your glpi database:
mysql -u root -p <database> -e "show table status where Engine='MyISAM';" | awk 'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}' | mysql -u root -p <database>

Note: this command might take several minutes!

Since the tables no longer is MyISAM, we will from now need to use InnoDB commands to check the table:
mysqlcheck -u root -p --databases <database>  | grep -v OK$

In my case this resulted in the following output:
test1glpi.glpi_plugin_fusioninventory_dblockinventories
note     : The storage engine for the table doesn't support check
test1glpi.glpi_plugin_fusioninventory_dblockinventorynames
note     : The storage engine for the table doesn't support check
test1glpi.glpi_plugin_fusioninventory_dblocksoftwares
note     : The storage engine for the table doesn't support check
test1glpi.glpi_plugin_fusioninventory_dblocksoftwareversions
note     : The storage engine for the table doesn't support check

No tables for GLPI only returned error messages, only warnings are related with the plugin fusioninventory in this case.

kandresen

Member

Registered: 2017-09-18

Posts: 8

Re: Database tables are not closed in safe manner leading to corruption

Never mind the errors mentioned in post #6, result the tables are of type "MEMORY", not InnoDB or MyISAM.

Last edited by kandresen (2017-10-02 11:15:39)

kandresen

Member

Registered: 2017-09-18

Posts: 8

Re: Database tables are not closed in safe manner leading to corruption

InnoDB is apparently much slower than MyISAM on my virtual test server. I am currently trying to tweak the mariadb server parameters, but hope this does not mean GLPI require many times more RAM now. I wonder: 
1) would it not be better to fix the issues with the default MyISAM engine?
2) would maybe changing engine require many query changes to work effectively?
3) if moving away from MyISAM, would it be better to use PostgreSQL instead?