table schema's differs

[pzbdkp]

Code of Conduct

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

Is there an existing issue for this?

• [X] I have searched the existing issues

Version

10.0.7

Bug description

After the update to the latest GLPI 10.0.7 I get the following when running php bin/console db:check

Table schema differs for table "glpi_budgets".
--- Expected database schema
+++ Current database schema
@@ @@
   KEY `budgettypes_id` (`budgettypes_id`),
   KEY `date_creation` (`date_creation`),
   KEY `date_mod` (`date_mod`),
-  KEY `end_date` (`end_date`),
   KEY `entities_id` (`entities_id`),
   KEY `is_deleted` (`is_deleted`),
   KEY `is_recursive` (`is_recursive`),
Table schema differs for table "glpi_knowbaseitems".
--- Expected database schema
+++ Current database schema
@@ @@
   FULLTEXT KEY `answer` (`answer`),
   FULLTEXT KEY `fulltext` (`name`,`answer`),
   FULLTEXT KEY `name` (`name`),
-  KEY `begin_date` (`begin_date`),
   KEY `date_creation` (`date_creation`),
   KEY `date_mod` (`date_mod`),
-  KEY `end_date` (`end_date`),
   KEY `is_faq` (`is_faq`),
   KEY `users_id` (`users_id`)
 )
Table schema differs for table "glpi_users".
--- Expected database schema
+++ Current database schema
@@ @@
   UNIQUE KEY `unicityloginauth` (`name`,`authtype`,`auths_id`),
   KEY `authitem` (`authtype`,`auths_id`),
   KEY `auths_id` (`auths_id`),
-  KEY `begin_date` (`begin_date`),
   KEY `date_creation` (`date_creation`),
   KEY `date_mod` (`date_mod`),
   KEY `default_requesttypes_id` (`default_requesttypes_id`),
-  KEY `end_date` (`end_date`),
   KEY `entities_id` (`entities_id`),
   KEY `firstname` (`firstname`),
   KEY `groups_id` (`groups_id`),

However, running the command php bin/console db:update gives:

+-----------------------+-----------+--------+
|                       | Current   | Doelen |
+-----------------------+-----------+--------+
| Database host         | localhost |        |
| Database name         | glpi      |        |
| Database user         | glpi      |        |
| GLPI version          | 10.0.7    | 10.0.7 |
| GLPI database version | 10.0.7    | 10.0.7 |
+-----------------------+-----------+--------+
No migration needed.

Relevant log output

No response

Page URL

No response

Steps To reproduce

run command php bin/console db:check

Your GLPI setup information

GLPI 10.0.7 ( => /var/www/glpi) Installation mode: TARBALL Current language:en_GB

Anything else?

No response

[pzbdkp]

Also, I've noticed that users with simplified interface don't have access to all their own tickets anymore. With simplified interface they only see the tickets created after the update. With standard interface they see all their own tickets.

[cedric-anne]

Table schema differs for table "glpi_budgets".
--- Expected database schema
+++ Current database schema
@@ @@
   KEY `budgettypes_id` (`budgettypes_id`),
   KEY `date_creation` (`date_creation`),
   KEY `date_mod` (`date_mod`),
-  KEY `end_date` (`end_date`),
   KEY `entities_id` (`entities_id`),
   KEY `is_deleted` (`is_deleted`),
   KEY `is_recursive` (`is_recursive`),

This key was already existing in GLPI 0.80. Maybe its creation was not correctly defined in corresponding migration.

Table schema differs for table "glpi_knowbaseitems". --- Expected database schema +++ Current database schema @@ @@ FULLTEXT KEY answer (answer), FULLTEXT KEY fulltext (name,answer), FULLTEXT KEY name (name),

• KEY begin_date (begin_date), KEY date_creation (date_creation), KEY date_mod (date_mod),
• KEY end_date (end_date), KEY is_faq (is_faq), KEY users_id (users_id) ) Table schema differs for table "glpi_users". --- Expected database schema +++ Current database schema @@ @@ UNIQUE KEY unicityloginauth (name,authtype,auths_id), KEY authitem (authtype,auths_id), KEY auths_id (auths_id),
• KEY begin_date (begin_date), KEY date_creation (date_creation), KEY date_mod (date_mod), KEY default_requesttypes_id (default_requesttypes_id),
• KEY end_date (end_date), KEY entities_id (entities_id), KEY firstname (firstname), KEY groups_id (groups_id),

These key are supposed to be added by migration to GLPI 0.85.5 (see https://github.com/glpi-project/glpi/blob/d3afa05a67e00fa7b889c664c3387a6d7d9da08b/install/migrations/update_0.85.3_to_0.85.5.php#L74-L78). I suppose something wrongs happens when this migration was executed.

However, running the command php bin/console db:update gives:

Schema diff are fixed automatically. Maybe we could add an automatic logic for some diffs (e.g. missing keys), but most of them requires a manual operation as they are the result of a previously failed migration or a manual database schema change and it would be almost impossible in such case to be 100% sure of how it should be fixed without risking any data loss.

In conclusion, you have to fix it manually.

[github-actions[bot]]

This issue has been closed as we only track bugs here.

You can get community support on forums or you can consider taking a subscription to get professional support. You can also contact GLPI editor team directly.

[pzbdkp]

I did found the following topic on the forum, describing the exact same errors on the database by another user.

When looking at the information, it looks like a bug from an earlier version. it looks like that key had been set incorrectly in 0.78 and then later removed for a few versions, then re-added. It is likely that a migration was either bugged or had somehow failed and the keys weren't re-added properly.

Probably a lot of long-time users have the same issue (without knowing it)...

[pzbdkp]

For those looking for a solution to the same problem; I've used the following queries on my MySQL-database (which is named glpi):

CREATE INDEX end_date ON glpi.glpi_budgets (end_date);
CREATE INDEX begin_date ON glpi.glpi_knowbaseitems (begin_date);
CREATE INDEX end_date ON glpi.glpi_knowbaseitems (end_date);
CREATE INDEX begin_date ON glpi.glpi_users (begin_date);
CREATE INDEX end_date ON glpi.glpi_users (end_date);