You are not logged in.
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)
Offline
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.
Offline
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)
Offline
Go inside glpi installation folder and run:
php bin/console glpi:database:check
Offline
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?
Offline
The check command do a simple chek.
Try with
php bin/console db:update
Backup it before do anything.
Offline
Here is the output from the GLPI:
C:\inetpub\wwwroot\glpi\bin>php console db:update
+-----------------------+-----------+--------+
| | Current | Target |
+-----------------------+-----------+--------+
| Database host | localhost | |
| Database name | Helpdesk | |
| Database user | glpi | |
| GLPI version | 9.1.3 | 9.4.3 |
| GLPI database version | 9.4.3 | 9.4.3 |
+-----------------------+-----------+--------+
No migration needed.
Why the GLPI current version is a different from the target version?
I confirm that in home page the version is 9.4.3.
Offline
did you attempt several time to upgrade to 9.4.3 ?
did you restore database ?
if yes did you restore in a empty database ?
Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1 Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6
préprod : glpi10.0.15
Offline
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.
Offline
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)
Offline
I restore the dump file directly on the existing datebase in mySQL.
bad idea. you should restore in an empty database.
Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1 Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6
préprod : glpi10.0.15
Offline
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)
Offline
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?
Offline
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. )
Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1 Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6
préprod : glpi10.0.15
Offline
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 )
Offline
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.)?
Offline
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.
Offline
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?
Offline
Do it manually.
Check the table that are different from schema and update it with create or replace table command
Offline
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)?
Offline
see here for support :
https://services.glpi-network.com/
Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1 Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6
préprod : glpi10.0.15
Offline
Have a look here :
https://glpi-project.org/subscriptions/
Offline