You are not logged in.
Hello,
I updated glpi to version 10, since I have this message on the interface :
2 tables are using the deprecated utf8mb3 storage charset. Run the "php bin/console glpi:migration:utf8mb4" command to migrate them.
23 primary or foreign keys columns are using signed integers. Run the "php bin/console glpi:migration:unsigned_keys" command to migrate them
I did these commands, it worked for the other tables but not for this one
C:\wamp64\bin\php\php7.4.9>php c:\wamp64\www\glpi\bin\console glpi:migration:utf8mb4
2 tables nécessitent une migration vers "utf8mb4".
Voulez-vous continuer ? [Yes/no]Yes
0/2 [>---------------------------] 0%SQL Error "1292": Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181 in query "ALTER TABLE `glpi_items_devicebatteries` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
Erreur de migration de la table "glpi_items_devicebatteries".
0/2 [>---------------------------] 0%SQL Error "1292": Incorrect date value: '0000-00-00' for column 'date_install' at row 589 in query "ALTER TABLE `glpi_items_softwareversions` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
Erreur de migration de la table "glpi_items_softwareversions".
2/2 [============================] 100%
Des erreurs se sont produites pendant la migration.
C:\wamp64\bin\php\php7.4.9>php c:\wamp64\www\glpi\bin\console glpi:migration:unsigned_keys
Trouvé 23 colonne(s) de clé principale ou étrangère utilisant des entiers signés
Voulez-vous continuer ? [Yes/no]Yes
La migration de la colonne "glpi_cartridgeitems.locations_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_cartridgeitems.manufacturers_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_computers.locations_id" ne peut pas être faite car elle contient des valeurs négatives.La migration de la colonne "glpi_computers.computermodels_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_consumableitems.manufacturers_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_domains.entities_id" ne peut pas être faite car elle contient des valeurs négatives.
5/23 [======>---------------------] 21%SQL Error "1292": Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181 in query "ALTER TABLE `glpi_items_devicebatteries` MODIFY COLUMN `id` int unsigned NOT NULL auto_increment"
La migration de la colonne "glpi_items_devicebatteries.id" a échouée avec le message "(1292) Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181".
6/23 [=======>--------------------] 26%SQL Error "1292": Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181 in query "ALTER TABLE `glpi_items_devicebatteries` MODIFY COLUMN `items_id` int unsigned NOT NULL DEFAULT '0' "
La migration de la colonne "glpi_items_devicebatteries.items_id" a échouée avec le message "(1292) Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181".
7/23 [========>-------------------] 30%SQL Error "1292": Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181 in query "ALTER TABLE `glpi_items_devicebatteries` MODIFY COLUMN `devicebatteries_id` int unsigned NOT NULL DEFAULT '0' "
La migration de la colonne "glpi_items_devicebatteries.devicebatteries_id" a échouée avec le message "(1292) Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181".
8/23 [=========>------------------] 34%SQL Error "1292": Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181 in query "ALTER TABLE `glpi_items_devicebatteries` MODIFY COLUMN `entities_id` int unsigned NOT NULL DEFAULT '0' "
La migration de la colonne "glpi_items_devicebatteries.entities_id" a échouée avec le message "(1292) Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181".
9/23 [==========>-----------------] 39%SQL Error "1292": Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181 in query "ALTER TABLE `glpi_items_devicebatteries` MODIFY COLUMN `locations_id` int unsigned NOT NULL DEFAULT '0' "
La migration de la colonne "glpi_items_devicebatteries.locations_id" a échouée avec le message "(1292) Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181".
10/23 [============>---------------] 43%SQL Error "1292": Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181 in query "ALTER TABLE `glpi_items_devicebatteries` MODIFY COLUMN `states_id` int unsigned NOT NULL DEFAULT '0' "
La migration de la colonne "glpi_items_devicebatteries.states_id" a échouée avec le message "(1292) Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181".
11/23 [=============>--------------] 47%SQL Error "1292": Incorrect date value: '0000-00-00' for column 'date_install' at row 589 in query "ALTER TABLE `glpi_items_softwareversions` MODIFY COLUMN `id` int unsigned NOT NULL auto_increment"
La migration de la colonne "glpi_items_softwareversions.id" a échouée avec le message "(1292) Incorrect date value: '0000-00-00' for column 'date_install' at row 589".
12/23 [==============>-------------] 52%SQL Error "1292": Incorrect date value: '0000-00-00' for column 'date_install' at row 589 in query "ALTER TABLE `glpi_items_softwareversions` MODIFY COLUMN `items_id` int unsigned NOT NULL DEFAULT '0' "
La migration de la colonne "glpi_items_softwareversions.items_id" a échouée avec le message "(1292) Incorrect date value: '0000-00-00' for column 'date_install' at row 589".
13/23 [===============>------------] 56%SQL Error "1292": Incorrect date value: '0000-00-00' for column 'date_install' at row 589 in query "ALTER TABLE `glpi_items_softwareversions` MODIFY COLUMN `softwareversions_id` int unsigned NOT NULL DEFAULT '0' "
La migration de la colonne "glpi_items_softwareversions.softwareversions_id" a échouée avec le message "(1292) Incorrect date value: '0000-00-00' for column 'date_install' at row 589".
14/23 [=================>----------] 60%SQL Error "1292": Incorrect date value: '0000-00-00' for column 'date_install' at row 589 in query "ALTER TABLE `glpi_items_softwareversions` MODIFY COLUMN `entities_id` int unsigned NOT NULL DEFAULT '0' "
La migration de la colonne "glpi_items_softwareversions.entities_id" a échouée avec le message "(1292) Incorrect date value: '0000-00-00' for column 'date_install' at row 589".
La migration de la colonne "glpi_monitors.locations_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_monitors.manufacturers_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_peripherals.locations_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_peripherals.manufacturers_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_printers.locations_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_softwares.softwares_id" ne peut pas être faite car elle contient des valeurs négatives.La migration de la colonne "glpi_users.locations_id" ne peut pas être faite car elle contient des valeurs négatives.
23/23 [============================] 100%
Des erreurs se sont produites pendant la migration.
I uninstalled the plugins but that didn't fix it.
Offline
0/2 [>---------------------------] 0%SQL Error "1292": Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181 in query "ALTER TABLE `glpi_items_devicebatteries` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
Erreur de migration de la table "glpi_items_devicebatteries".
0/2 [>---------------------------] 0%SQL Error "1292": Incorrect date value: '0000-00-00' for column 'date_install' at row 589 in query "ALTER TABLE `glpi_items_softwareversions` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
Hi
Backup
UPDATE glpi_items_devicebatteries SET date_install = NULL WHERE CAST(manufacturing_date AS CHAR(20)) = '1980-00-00'
UPDATE glpi_items_softwareversions SET date_install = NULL WHERE CAST(date_install AS CHAR(20)) = '0000-00-00'
Last edited by WebGreg (2022-05-12 12:25:40)
--
GLPI 10.0.17
GLPI-Inventory 1.4.0
Ubuntu Server 20.04 LTS
Offline
Hi
thank you for your reply
I ran these 2 command in MySQL console :
mysql> UPDATE glpi_items_devicebatteries SET date_install = NULL WHERE CAST(manufacturing_date AS CHAR(20)) = '1980-00-00';
ERROR 1054 (42S22): Champ 'date_install' inconnu dans field list
mysql> UPDATE glpi_items_softwareversions SET date_install = NULL WHERE CAST(date_install AS CHAR(20)) = '0000-00-00';
Query OK, 333 rows affected (0.18 sec)
Enregistrements correspondants: 333 Modifi├®s: 333 Warnings: 0
I then ran the commands using the administrator command prompt :
C:\wamp64\bin\php\php7.4.9>php C:\Wamp64\www\glpi\bin\console glpi:migration:utf8mb4
2 tables nécessitent une migration vers "utf8mb4".
Voulez-vous continuer ? [Yes/no]Yes
0/2 [>---------------------------] 0%SQL Error "1292": Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181 in query "ALTER TABLE `glpi_items_devicebatteries` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
Erreur de migration de la table "glpi_items_devicebatteries".
2/2 [============================] 100%
Des erreurs se sont produites pendant la migration.
C:\wamp64\bin\php\php7.4.9>php C:\Wamp64\www\glpi\bin\console glpi:migration:unsigned_keys
Trouvé 23 colonne(s) de clé principale ou étrangère utilisant des entiers signés
Voulez-vous continuer ? [Yes/no]Yes
La migration de la colonne "glpi_cartridgeitems.locations_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_cartridgeitems.manufacturers_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_computers.locations_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_computers.computermodels_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_consumableitems.manufacturers_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_domains.entities_id" ne peut pas être faite car elle contient des valeurs négatives.
5/23 [======>---------------------] 21%SQL Error "1292": Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181 in query "ALTER TABLE `glpi_items_devicebatteries` MODIFY COLUMN `id` int unsigned NOT NULL auto_increment"
La migration de la colonne "glpi_items_devicebatteries.id" a échouée avec le message "(1292) Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181".
6/23 [=======>--------------------] 26%SQL Error "1292": Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181 in query "ALTER TABLE `glpi_items_devicebatteries` MODIFY COLUMN `items_id` int unsigned NOT NULL DEFAULT '0' "
La migration de la colonne "glpi_items_devicebatteries.items_id" a échouée avec le message "(1292) Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181".
7/23 [========>-------------------] 30%SQL Error "1292": Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181 in query "ALTER TABLE `glpi_items_devicebatteries` MODIFY COLUMN `devicebatteries_id` int unsigned NOT NULL DEFAULT '0' "
La migration de la colonne "glpi_items_devicebatteries.devicebatteries_id" a échouée avec le message "(1292) Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181".
8/23 [=========>------------------] 34%SQL Error "1292": Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181 in query "ALTER TABLE `glpi_items_devicebatteries` MODIFY COLUMN `entities_id` int unsigned NOT NULL DEFAULT '0' "
La migration de la colonne "glpi_items_devicebatteries.entities_id" a échouée avec le message "(1292) Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181".
9/23 [==========>-----------------] 39%SQL Error "1292": Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181 in query "ALTER TABLE `glpi_items_devicebatteries` MODIFY COLUMN `locations_id` int unsigned NOT NULL DEFAULT '0' "
La migration de la colonne "glpi_items_devicebatteries.locations_id" a échouée avec le message "(1292) Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181".
10/23 [============>---------------] 43%SQL Error "1292": Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181 in query "ALTER TABLE `glpi_items_devicebatteries` MODIFY COLUMN `states_id` int unsigned NOT NULL DEFAULT '0' "
La migration de la colonne "glpi_items_devicebatteries.states_id" a échouée avec le message "(1292) Incorrect date value: '1980-00-00' for column 'manufacturing_date' at row 181".
La migration de la colonne "glpi_monitors.locations_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_monitors.manufacturers_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_peripherals.locations_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_peripherals.manufacturers_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_printers.locations_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_softwares.softwares_id" ne peut pas être faite car elle contient des valeurs négatives.
La migration de la colonne "glpi_users.locations_id" ne peut pas être faite car elle contient des valeurs négatives.
23/23 [============================] 100%
Des erreurs se sont produites pendant la migration.
Offline
I use phpmyadmin. But it doesn't make much difference. Just check the row and collumn mentioned in the error message and modify the sql query to correct this.
--
GLPI 10.0.17
GLPI-Inventory 1.4.0
Ubuntu Server 20.04 LTS
Offline
thank you
I did it with phpmyadmin and with this command : UPDATE glpi_monitors SET manufacturers_id = 0 WHERE manufacturers_id = -1;
Offline
>I came across this exact problem when I upgraded my DEV GLPI from 9.5.2 to 10.0.2 and just want to share my solution for those who have trouble.
>I used Ubuntu server with docker for GLPI and MYSQL.
I got 4 errors at page and the first 2 were simple and I was able to fix but the last two were not, and they are:
#For the both of the error after running "php bin/console...." the number of tables and primary key went down but it complained about column
1. 391 tables are using the deprecated utf8mb3 storage charset. Run the "php bin/console glpi:migration:utf8mb4" command to migrate them
2. 1305 primary or foreign keys columns are using signed integers. Run the "php bin/console glpi:migration:unsigned_keys" command to migrate them
#First go into my GLPI container bash
>docker container exec -it ID bash
>cd /var/www/html/glpi/
>php bin/console glpi:migration:utf8mb4
#got error:
Error “SQL Error "1292": Incorrect date value: '1980-00-10' for column 'manufacturing_date' at row 127 in query "ALTER TABLE `glpi_items_devicebatteries` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
Error migrating table "glpi_items_devicebatteries".”
>Open Mysql Workbench
select the schema
#disable safe update
>SET SQL_SAFE_UPDATES = 0;
>UPDATE glpi_items_devicebatteries SET manufacturing_date = NULL WHERE CAST(manufacturing_date AS CHAR(20)) = '1980-00-10'
#Enable safe update
>SET SQL_SAFE_UPDATES = 1;
>reboot server. Make sure you stop both docker
>login and start both database and GLPI docker container
#ran the suggest command again and the error went away
>php bin/console glpi:migration:utf8mb4
###I did the same for 2nd error. Make sure to have a backup and read the error and just fill in the correct column and table name. Alternatively you can just delete the column in the database if you don't care
Offline
Hi. Did you check the table to see if the value was changed?
BTW - your post is chaotic, hard to read because you mix your words, except with some quotes and commands.
For quotes use the tag: [ quote=userNick] ............. [ / quote]
For commands: [ code] ................. [/ code]
Last edited by WebGreg (2022-08-27 10:07:01)
--
GLPI 10.0.17
GLPI-Inventory 1.4.0
Ubuntu Server 20.04 LTS
Offline
@WebGreg sorry!
For future users reading this post. Please ignore my previous post. I had the chance to run another upgrade and found the solution that worked for me.
I'm using Ubuntu Linux server with docker container for GLPI and MySQL
1.Connect to database by using Mysql WorkBench
2.Run query. "SET SQL_SAFE_UPDATES = 0" this will turn off safe update
3.Go back to your Linux box, cd into GLPI bash (because i'm using docker container), initiate the recommended command that GLPI listed on the web portal (php bin/console glpi:migration:timestamps; php bin/console glpi:migration:utf8mb4)
4.Run query. "SET SQL_SAFE_UPDATES = 1" this will turn on safe update
Last edited by whosdatboi (2023-01-20 23:24:41)
Offline
Hello,
Since 9.2 Glpi include commande line. To solve this trouble easily (done last week wioth same error) :
- Locate php.exe in wamp folder
- Open CMD
- put "php directory"\php.exe "glpi directory\bin\console glpi:migration:utf8mb4
Do the same for unsigned_keys and its all
You can do another things with console, abuse it :-)
Offline