MySQL query error after upgrade to 9.4.3,

acibademcityclinic · 2019-10-16 11:16:28

After upgrade from 9.1.3 to 9.4.3, the "sql-error.log" file is insane. For 12 hours the file has reached 125 MB.
The all errors is the almost the same like this:

[2019-10-16 12:02:07] glpisqllog.ERROR: DBmysql::query() in C:\inetpub\wwwroot\glpi\inc\dbmysql.class.php line 188
*** MySQL query error:
SQL: SELECT `glpi_locations`.`completename`, `glpi_locations`.`comment`, '' AS `transname`, '' AS `transcomment`, `glpi_locations`.`address`, `glpi_locations`.`town`, `glpi_locations`.`country` FROM `glpi_locations` WHERE `glpi_locations`.`id` = '0'
Error: Unknown column 'glpi_locations.address' in 'field list'
Backtrace :
inc\dbmysqliterator.class.php:95
inc\dbmysql.class.php:580 DBmysqlIterator->execute()
inc\dbutils.class.php:1078 DBmysql->request()
inc\db.function.php:274 DbUtils->getTreeValueCompleteName()
inc\dropdown.class.php:320 getTreeValueCompleteName()
inc\commondbtm.class.php:3322 Dropdown::getDropdownName()
inc\commondbtm.class.php:3503 CommonDBTM->getComments()
inc\commondbtm.class.php:3583 CommonDBTM->getName()
inc\commondbtm.class.php:1227 CommonDBTM->getNameID()
inc\search.class.php:5767 CommonDBTM->getLink()
inc\search.class.php:1445 Search::giveItem()
inc\search.class.php:98 Search::constructData()
inc\search.class.php:80 Search::showList()
front\ticket.php:45 Search::show()
{"user":"311@BGSFWS01","mem_usage":"0.004\", 8.94Mio)"}

Can any one give me advice what should I do to fix this?

Last edited by acibademcityclinic (2019-10-16 19:38:34)

Kaya84 · 2019-10-17 09:14:40

I think there where someting wrong with the upgrade process.
Btw seems the query can't find address column. Try to add:
ALTER TABLE glpi_locations ADD COLUMN address TEXT NULL AFTER sons_cache;

I dind't test the query, take a backup before.

acibademcityclinic · 2019-10-17 21:07:39

Thanks for the help Kaya84, really this works. But unfortunately we have more than one missing column on this and on another tables.
I have an idea, but I don’t know if it will work. If I upgrade this production server to last stable version(9.4.4) and make one dump on the DB, and build another (clean) GLPI (9.4.4) and restore the dump file. After that make my new GLPI in production. Can this move solve all my MySQL query problems? And the most important question is, can this move migrate all data from my old GLPI to the new one?

Last edited by acibademcityclinic (2019-10-18 00:18:05)

Kaya84 · 2019-10-18 10:39:14

Go inside glpi installation folder and run:
php bin/console glpi:database:check

acibademcityclinic · 2019-10-18 14:42:07

Here is the output from this checker, but the sql-error.log is still growing with the errors:

C:\inetpub\wwwroot\glpi\bin>php console glpi:database:check
Table schema differs for table "glpi_authldaps".
--- Original
+++ New
@@ @@
create table `glpi_authldaps` (
+ `date_creation` datetime default null
@@ @@
`location_field` varchar(255) default null
- `responsible_field` varchar(255) default null
`pagesize` int(11) not null default '0'
`ldap_maxlimit` int(11) not null default '0'
`can_support_pagesize` tinyint(1) not null default '0'
`picture_field` varchar(255) default null
- `date_creation` datetime default null
- `inventory_domain` varchar(255) default null
primary key (`id`)

Table schema differs for table "glpi_blacklistedmailcontents".
--- Original
+++ New
@@ @@
create table `glpi_blacklistedmailcontents` (
+ `date_creation` datetime default null
+ `date_mod` datetime default null
`id` int(11) not null auto_increment
`name` varchar(255) default null
`content` text
`comment` text
- `date_mod` datetime default null
- `date_creation` datetime default null
primary key (`id`)

Table schema differs for table "glpi_blacklists".
--- Original
+++ New
@@ @@
create table `glpi_blacklists` (
+ `date_creation` datetime default null
+ `date_mod` datetime default null
`id` int(11) not null auto_increment
`type` int(11) not null default '0'
`name` varchar(255) default null
`value` varchar(255) default null
`comment` text
- `date_mod` datetime default null
- `date_creation` datetime default null
primary key (`id`)

Table schema differs for table "glpi_budgets".
--- Original
+++ New
@@ @@
create table `glpi_budgets` (
+ `budgettypes_id` int(11) not null default '0'
+ `locations_id` int(11) not null default '0'
@@ @@
`date_creation` datetime default null
- `locations_id` int(11) not null default '0'
- `budgettypes_id` int(11) not null default '0'
primary key (`id`)

Table schema differs for table "glpi_calendars".
--- Original
+++ New
@@ @@
create table `glpi_calendars` (
+ `date_creation` datetime default null
@@ @@
`cache_duration` text
- `date_creation` datetime default null
primary key (`id`)

Table schema differs for table "glpi_cartridgeitems".
--- Original
+++ New
@@ @@
create table `glpi_cartridgeitems` (
+ `date_creation` datetime default null
+ `date_mod` datetime default null
@@ @@
`alarm_threshold` int(11) not null default '10'
- `date_mod` datetime default null
- `date_creation` datetime default null
primary key (`id`)

Table schema differs for table "glpi_cartridgeitemtypes".
--- Original
+++ New
@@ @@
create table `glpi_cartridgeitemtypes` (
+ `date_creation` datetime default null
+ `date_mod` datetime default null
`id` int(11) not null auto_increment
`name` varchar(255) default null
`comment` text
- `date_mod` datetime default null
- `date_creation` datetime default null
primary key (`id`)

Table schema differs for table "glpi_cartridges".
--- Original
+++ New
@@ @@
create table `glpi_cartridges` (
+ `date_creation` datetime default null
+ `date_mod` datetime default null
@@ @@
`pages` int(11) not null default '0'
- `date_mod` datetime default null
- `date_creation` datetime default null
primary key (`id`)

Table schema differs for table "glpi_changes".
--- Original
+++ New
@@ @@
create table `glpi_changes` (
+ `date_creation` datetime default null
@@ @@
`validation_percent` int(11) not null default '0'
+ `solutiontypes_id` int(11) not null default '0'
+ `solution` longtext
`actiontime` int(11) not null default '0'
`begin_waiting_date` datetime default null
`waiting_duration` int(11) not null default '0'
`close_delay_stat` int(11) not null default '0'
`solve_delay_stat` int(11) not null default '0'
- `date_creation` datetime default null
primary key (`id`)

Table schema differs for table "glpi_changetasks".
--- Original
+++ New
@@ @@
create table `glpi_changetasks` (
+ `date_creation` datetime default null
+ `date_mod` datetime default null
@@ @@
`actiontime` int(11) not null default '0'
- `date_mod` datetime default null
- `date_creation` datetime default null
`tasktemplates_id` int(11) not null default '0'
- `timeline_position` tinyint(1) not null default '0'
- `is_private` tinyint(1) not null default '0'
primary key (`id`)

Table schema differs for table "glpi_changevalidations".
--- Original
+++ New
@@ @@
`validation_date` datetime default null
- `timeline_position` tinyint(1) not null default '0'
primary key (`id`)

DBmysql::query() in C:\inetpub\wwwroot\glpi\inc\dbmysql.class.php line 188
*** MySQL query error:
SQL: SHOW CREATE TABLE `glpi_items_disks`
Error: Table 'helpdesk.glpi_items_disks' doesn't exist
Backtrace :
inc\dbmysql.class.php:1106
inc\console\database\checkcommand.class.php:90 DBmysql->getTableSchema()
vendor\symfony\console\Command\Command.php:255 Glpi\Console\Database\CheckCommand->execute()
vendor\symfony\console\Application.php:953 Symfony\Component\Console\Command\Command->run()
inc\console\application.class.php:196 Symfony\Component\Console\Application->doRunCommand()
vendor\symfony\console\Application.php:248 Glpi\Console\Application->doRunCommand()
vendor\symfony\console\Application.php:148 Symfony\Component\Console\Application->doRun()
bin\console:72 Symfony\Component\Console\Application->run()

C:\inetpub\wwwroot\glpi\bin>

Is there any tools that we can use to full check of the database and repair it accordingly?

Kaya84 · 2019-10-21 10:44:23

The check command do a simple chek.
Try with
php bin/console db:update
Backup it before do anything.

acibademcityclinic · 2019-10-21 13:49:00

Here is the output from the GLPI:

Why the GLPI current version is a different from the target version?
I confirm that in home page the version is 9.4.3.

LaDenrée · 2019-10-21 13:55:54

did you attempt several time to upgrade to 9.4.3 ?
did you restore database ?

if yes did you restore in a empty database ?

Kaya84 · 2019-10-21 14:00:23

Well.... I can suppose something went wrong during upgrade?
Or... are you sure u are in the right installation folder?

Seems that you upgraded database but then running an older (9.1.3) version of GLPI..
Check the content of CHANGELOG.md file.

acibademcityclinic · 2019-10-21 15:06:01

LaDenree:
1 question: yes, maybe 5 or 6 times before I successfully upgraded to 9.4.3.
2- question: yes, after first try to upgrade to 9.4.3 and was unsuccessful. After the error of the unique information, I was restore of the dump file and copy the older files(9.1.3) in production directory.
3- question: I think I answer on this question in previous question but ...
I restore the dump file directly on the existing database in mySQL.

Kaya84,
Yes, And I think so, something went wrong with the update.
Im pretty sure that Im using the right version, because I downloaded the files from the GLPI site before to replace them in production folder. I will check this file later and replay here.

Last edited by acibademcityclinic (2019-10-21 15:09:24)

LaDenrée · 2019-10-21 15:12:56

I restore the dump file directly on the existing datebase in mySQL.

bad idea. you should restore in an empty database.

Kaya84 · 2019-10-21 15:24:41

Oh yes, really a bad bad idea.
Option a) Restore dump of MYSQL AND file
Option b) download glpi 9.4.3 and install it (before, rename actual directory and after copy only configuration file)

acibademcityclinic · 2019-10-21 15:57:03

So, Can we fix the existing DB, because the information is important?
Because what I understand from your answer is that I have two options: to restore dump file which is from two weeks ago or to install fresh copy and start from zero, right?

LaDenrée · 2019-10-21 16:12:46

whatever you do : backup your database before.

I Don't know any other way than restore backup from 2 weeks ago. I'm not sure this will solve all your problems.

(it's also quite difficult to repair database on a forum, be carefull, there is no warranty on advices you can get. )

Kaya84 · 2019-10-21 16:24:08

acibademcityclinic wrote:

So, Can we fix the existing DB, because the information is important?
Because what I understand from your answer is that I have two options: to restore dump file which is from two weeks ago or to install fresh copy and start from zero, right?

Otherwise you can manually fix table per table (there are 12 tables to fix) .
But I don't know what it can mean in the future...
Lost by lost, try this. (BACKUP FIRST!!)

But first of all, check check chek which version you are running (by comparing https://github.com/glpi-project/glpi/releases or check CHANGELOG.MD which says the current version )

acibademcityclinic · 2019-10-22 10:28:44

So here is what I have in CHANGELOG.MD:

## [9.4.3] unreleased

### API changes

#### Deprecated

The following methods have been deprecated:

- `Html::convertTagFromRichTextToImageTag()`

## [9.4.2] 2019-04-11

### API changes

#### Deprecated

The following methods have been deprecated:

- `CommonDBTM::getTablesOf()`
- `CommonDBTM::getForeignKeyFieldsOf()`

What this mean: "[9.4.3] unreleased"? And how can we fix this.
If I upgrade to last version(9,4,4) will this help?
And I have one question, how dump file works in mySQL? I mean when restore the dump file, is restored only the data or is restored the data and the shema(tables, colums ... etc.)?

Kaya84 · 2019-10-22 14:05:43

Well, at this point I think u can try an in place upgrade with 9.4.4 version. Maybe it will fix.

And I have one question, how dump file works in mySQL? Read: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
I mean when restore the dump file, is restored only the data or is restored the data and the shema(tables, colums ... etc.)? Depends on what u will do.

acibademcityclinic · 2019-10-22 16:09:24

So after the upgrade to 9.4.4, there is no changes. The same results like a previous command of: "php console db:update", the sql-error.log is in the same state.
Interesting is that after I replaced the files with the latest version 9.4.4, after running the GLPI URL, there's no installation page, just load normally, and the version of GLPI in the home page was changed to 9.4.4.
In the ChangeLOG.md file information now is:
## [9.4.4] unreleased
## [9.4.3] 2019-06-20
How can we manually upgrade to target version of 9.4.4?

Kaya84 · 2019-10-23 12:01:13

Do it manually.
Check the table that are different from schema and update it with create or replace table command

acibademcityclinic · 2019-10-31 14:19:22

Thank you very much for your help so far.
I have one last question. Do you know how can I contact GLPI official developer to solve my database problem (paid support)?

LaDenrée · 2019-10-31 14:39:50

see here for support :
https://services.glpi-network.com/

Jean-Christophe · 2019-10-31 14:50:29

Have a look here :
https://glpi-project.org/subscriptions/

Forum GLPI-Project

Announcement

#1 2019-10-16 11:16:28

MySQL query error after upgrade to 9.4.3,

#2 2019-10-17 09:14:40

Re: MySQL query error after upgrade to 9.4.3,

#3 2019-10-17 21:07:39

Re: MySQL query error after upgrade to 9.4.3,

#4 2019-10-18 10:39:14

Re: MySQL query error after upgrade to 9.4.3,

#5 2019-10-18 14:42:07

Re: MySQL query error after upgrade to 9.4.3,

#6 2019-10-21 10:44:23

Re: MySQL query error after upgrade to 9.4.3,

#7 2019-10-21 13:49:00

Re: MySQL query error after upgrade to 9.4.3,

#8 2019-10-21 13:55:54

Re: MySQL query error after upgrade to 9.4.3,

#9 2019-10-21 14:00:23

Re: MySQL query error after upgrade to 9.4.3,

#10 2019-10-21 15:06:01

Re: MySQL query error after upgrade to 9.4.3,

#11 2019-10-21 15:12:56

Re: MySQL query error after upgrade to 9.4.3,

#12 2019-10-21 15:24:41

Re: MySQL query error after upgrade to 9.4.3,

#13 2019-10-21 15:57:03

Re: MySQL query error after upgrade to 9.4.3,

#14 2019-10-21 16:12:46

Re: MySQL query error after upgrade to 9.4.3,

#15 2019-10-21 16:24:08

Re: MySQL query error after upgrade to 9.4.3,

#16 2019-10-22 10:28:44

Re: MySQL query error after upgrade to 9.4.3,

#17 2019-10-22 14:05:43

Re: MySQL query error after upgrade to 9.4.3,

#18 2019-10-22 16:09:24

Re: MySQL query error after upgrade to 9.4.3,

#19 2019-10-23 12:01:13

Re: MySQL query error after upgrade to 9.4.3,

#20 2019-10-31 14:19:22

Re: MySQL query error after upgrade to 9.4.3,

#21 2019-10-31 14:39:50

Re: MySQL query error after upgrade to 9.4.3,

#22 2019-10-31 14:50:29

Re: MySQL query error after upgrade to 9.4.3,

Board footer