Glpi: Error when Upgrading to current Version

[wagneral]

Hello,

there seems to be an error in the database upgrade process from Version _GLPI 9.1-106-g368d55c_ to the current version. After pulling the current sources and running composer install --no-dev in the console, the webinterface requests a database upgrade. Connection ist successful but after starting the update it stops with the following error:

9.1.1 multiple alter in glpi_tickets - Fehler in der Datenbank-Abfrage: ALTER TABLE glpi_tickets ADD INDEX slts_ttr_id (slts_ttr_id) , DROP INDEX slalevels_id , ADD INDEX ttr_slalevels_id (ttr_slalevels_id) - Fehler ist Key column 'ttr_slalevels_id' doesn't exist in table

Anyone a clue?

Thanks in advance

[edgardmessias]

If you have backup, try this:

Before upgrade, change this line:
https://github.com/glpi-project/glpi/blob/6855d4cb1ec43e39d6693143d01d48bea30ed7ac/install/update_0905_91.php#L766

Change glpi_slalevels to glpi_tickets

If table glpi_slts exists, drop before upgrade.

[wagneral]

Thanks. I tried as described above, but the error message ist still the same. As you can see in the error message the glpi_tickets table is already selected for being altered.

Is it possible to create the needed column manually? Or better rename an existing one?

If i do something like ALTER TABLE glpi_tickets CHANGE slalevels_id ttr_slalevels_id INT; after applying your changes, the upgrade will run smoothly and complete without errors.

Does this have any bad effects?

Should be the same action as in this line, https://github.com/glpi-project/glpi/blob/6855d4cb1ec43e39d6693143d01d48bea30ed7ac/install/update_0905_91.php#L774
but seems to work incorrect here.

[Armin83]

I have the same problem.

Edit:
I use the really official release 9.1.1.

[Exactly in the same german language ;) - joke]

[Armin83]

edit: http://forum.glpi-project.org/viewtopic.php?id=155990

2016-11-16 17:56:15 [@testsystem-glpi]
  *** MySQL query error:
  SQL: ALTER TABLE `glpi_tickets` ADD INDEX `slts_ttr_id` (`slts_ttr_id`) ,
DROP INDEX `slalevels_id` ,
ADD INDEX `ttr_slalevels_id` (`ttr_slalevels_id`)
  Error: Key column 'ttr_slalevels_id' doesn't exist in table
  Backtrace :
  inc/dbmysql.class.php:208
  inc/migration.class.php:579                        DBmysql->queryOrDie()
  install/update_91_911.php:90                       Migration->migrationOneTable()
  install/update.php:800                             update91to911()
  install/update.php:958                             updateDbUpTo031()
2016-11-16 18:02:05 [@testsystem-glpi]
  *** MySQL query error:
  SQL: ALTER TABLE `glpi_tickets` ADD INDEX `slts_ttr_id` (`slts_ttr_id`) ,
DROP INDEX `slalevels_id` ,
ADD INDEX `ttr_slalevels_id` (`ttr_slalevels_id`)
  Error: Key column 'ttr_slalevels_id' doesn't exist in table
  Backtrace :
  inc/dbmysql.class.php:208
  inc/migration.class.php:579                        DBmysql->queryOrDie()
  install/update_91_911.php:90                       Migration->migrationOneTable()
  install/update.php:800                             update91to911()
  install/update.php:958                             updateDbUpTo031()
2016-11-16 18:02:14 [@testsystem-glpi]
  *** MySQL query error:
  SQL: ALTER TABLE `glpi_tickets` ADD INDEX `slts_ttr_id` (`slts_ttr_id`) ,
DROP INDEX `slalevels_id` ,
ADD INDEX `ttr_slalevels_id` (`ttr_slalevels_id`)
  Error: Key column 'ttr_slalevels_id' doesn't exist in table
  Backtrace :
  inc/dbmysql.class.php:208
  inc/migration.class.php:579                        DBmysql->queryOrDie()
  install/update_91_911.php:90                       Migration->migrationOneTable()
  install/update.php:800                             update91to911()
  install/update.php:958                             updateDbUpTo031()
2016-11-16 18:24:16 [@testsystem-glpi]
  *** MySQL query error:
  SQL: ALTER TABLE `glpi_tickets` ADD INDEX `slts_ttr_id` (`slts_ttr_id`) ,
DROP INDEX `slalevels_id` ,
ADD INDEX `ttr_slalevels_id` (`ttr_slalevels_id`)
  Error: Key column 'ttr_slalevels_id' doesn't exist in table
  Backtrace :
  inc/dbmysql.class.php:208
  inc/migration.class.php:579                        DBmysql->queryOrDie()
  install/update_91_911.php:90                       Migration->migrationOneTable()
  install/update.php:800                             update91to911()
  install/update.php:958                             updateDbUpTo031()
root@testsystem-glpi:/var/www/glpi/files/_log#

[wagneral]

interesting blog entry. didn't get in touch with that before, thanks. so renaming columns seems to be a useful workaround ...

[Armin83]

@orthagh
Hi,
can you review this update bug from 9.1 to 9.1.1

[yllen]

Due to https://github.com/orthagh/glpi/commit/f1adb87774b78da1d5de145bd9f1e8da381d701f

[yllen]

another post for this problem http://forum.glpi-project.org/viewtopic.php?id=155990

[Armin83]

yllen solution from her post in glpi forum threat works for me also if i upgrade from 0.90.3 to 9.1.1 the other sql didn't work in this update jump.

[orthagh]

For resume :

• addKey & dropKey functions cannot fail normally, they test existence (for add) or absence (for drop) of the key before registering the changes
• Theses changes are applied by migrationOneTable
• The mentionned commit (f1adb87) fix migration for 9.1 users who didn't have theses indexes.
• So it's a doublon but with the behavior of add/drop keys function, it's normally not a problem.

So the commit f1adb87 doesn't trigger this issue.

Actually, in the provided logs, we could see that theses users didn't have glpi_tickets.ttr_slalevels_id column and the addkey fails to add an index on an inexistent column (i think we shouldn't change that) .

I just tested this morning

• update empty 9.1 to 9.1.1
• update empty 0.90.5 to 9.1.1
• update empty 0.90.3 to 9.1.1
• update a copy of my prod 0.90.5 to 9.1.1

Each tested versions was archives released on github.
I didn't encounter this issue (nor any errors)

My guess is the users who mention this issue are using dev branches.

Anyway, for thoses who miss glpi_tickets.ttr_slalevels_id field and already have a 9.1 glpi, you could execute this query manually before update to 9.1.1:

ALTER TABLE `glpi_tickets` CHANGE COLUMN `slalevels_id` `ttr_slalevels_id` int(11) NOT NULL DEFAULT '0';

That should fix your instances.

[orthagh]

If the dev version usage is confirmed, i think we should add a (BIG) warning when using a non released version (like fusion inventory)

[edgardmessias]

In my tests, the problem happened when the 'glpi_slts' table exists on 0.90.5 version.

In update_0905_91.php#L703, lines between 703 and 794 not executed during migration.

Probably the 'glpi_slts' table was a vestige of an old test.

[Armin83]

Oh, you are right.
I restore a backup from produktion system which was 0.90.3 on my official 9.1.1 on testsystem but before there was a git dev version on it. -> i got the error

I updated my productive system without trouble or problems to the great 9.1.1 version. (sometimes there is a moment of slowness

[SylvainGuibert]

@orthagh thx for the fix

[wagneral]

thx @everyone being involved.

[superbfahad]

I did but upgrading from glpi-0.90 to 9.1still giving this error.... multiple alter in glpi_slalevels - Error during the database query: ALTER TABLE glpi_slalevels ADD INDEX slts_id (slts_id) - Error is Table 'glpi-new.glpi_slalevels' doesn't exist. anyone can help?

[trasher]

@yllen you told me you was able to reproduce such issues with glpi > 9.1.1. Is it still the case? Could you give me steps to reproduce?

I've tried several ways, upgrading from a 0.90 and from a 0.84.4 to current 9.1/bugfixes branch... I can't reproduce this issue.

[trasher]

ping?

[yllen]

Migration tested fo 9.2 from 0.85.x.
I has this message 7 times because i do the migration several time. At 8th, migration is OK.
Why??? mystery....

[mkaphera]

Hello

I was upgrading version of a database and am stuck on the page that has the details below;
Can anyone help?

GLPI SETUP

Upgrade

Database connection successful

Work in progress...

Update to 0.85.3

Task completed. (0 seconds)

Update to 0.85.5

Task completed. (0 seconds)

Update to 0.90

Task completed. (0 seconds)

Update to 0.90.1

Task completed. (0 seconds)

Update to 0.90.5

Task completed. (0 seconds)

Update to 9.1

Change of the database layout - glpi_tickets (7 seconds)

9.1 multiple alter in glpi_tickets - Error during the database query: ALTER TABLE glpi_tickets ADD slts_tto_id INT(11) NOT NULL DEFAULT '0' AFTER slts_ttr_id , ADD INDEX slts_tto_id (slts_tto_id) - Error is Unknown column 'slts_ttr_id' in 'glpi_tickets'

Thanks

[superbfahad]

I was able to migrate old database successfully from 0.90.5 to 9.1
You have to perform following tasks:
1- Login your phpmyadmin
2 - rename the table glpi_slalevels to glpi_tickets
3- drop slalevels_id
4- add ttr_slalevels_id INT(11) NOT NULL DEFAULT '0'
5 - refresh your page GLPI page.
6 - Start upgrade process again..

You are Done.

[mkaphera]

Hello,

Thanks for your response. I have tried the method above, however it did not work for me.
What I have done though;

1. I Dropped the original database which is created when installing GLPI
2. I Created another database of same name "glpi"
3. I imported a backup of old database.
4. I restarted the upgrading process and it finished. I did this on two separate machines and they were all successful.

I hope this will help someone.

Thanks.

[rebrec]

Hi
We are experiencing the same behaviour (migrating from 0.90.5 to 9.2.1).
We have to manually rename the field glpi_tickets.slas_ttr_id to glpi_tickets.slts_ttr_id.

Everything seems to be working fine.
But i wonder if this is normal to use some manual modification into the database schema in a "stable" version :

• Does it mean i may have a problem in the database that i may discover later (in a few month maybe ?)
• Why is such problem still there in a production version (just curious).

After the upgrade process my field name seems to have been renamed back to glpi_tickets.slas_ttr_id, does that mean that i am now "safe" from any side effect of doing manual db modification ?

Regards

[jul666]

Hi,
We have the same issue when we upgrade from 0.85.2 to 9.2.1.
We use the solution provided by rebrec and the update finish properly.

But I've the same question as rebrec.

Best

And thanks to rebrec for the solution