Closed jcervantes-sipecom closed 3 years ago
cedric-anne
commented
4 years ago Hi,
Dates are pretty weird in your SQL logs. Can you check on glpi_tickets database table if there is a *date field that contains a value far in the past or far in the future ?
Regards
jcervantes-sipecom
commented
4 years ago Hi, yes I did this query (Sorry if the query is not optimal or perfect, I am not a DBA, but I got the data I wanted.):
select date,closedate,solvedate,date_mod,date_creation from g9495.glpi_tickets where year(cast(date as datetime))<>'2020' and year(cast(date as datetime))<>'2019' or year(cast(closedate as datetime))<>'2020' and year(cast(closedate as datetime))<>'2019' or year(cast(solvedate as datetime))<>'2020' and year(cast(solvedate as datetime))<>'2019' or year(cast(date_mod as datetime))<>'2020' and year(cast(date_mod as datetime))<>'2019' or year(cast(date_creation as datetime))<>'2020' and year(cast(date_creation as datetime))<>'2019' \G;I got some values pretty weird like (attach txt): glpi_tickets.txt
closedate: 2038-01-18 22:14:07 solvedate: 2038-01-18 22:14:07 closedate: NULL date: 0000-00-00 00:00:00
I don't know how these values are there, but is there any way to fix it?
trasher
commented
4 years ago I dunno for the "0" date; for 2038, see https://github.com/glpi-project/glpi/issues/7953
cedric-anne
commented
4 years ago Hi,
SLA/OLA computations may use the date field as begin date, and, for each iteration, check if target date is in working days. Setting date with date_creation value when date == '0000-00-00 00:00:00' should fix your issue.
Regards
jcervantes-sipecom
commented
4 years ago Hi, thanks for your answers. For:
I dunno for the "0" date; for 2038, see #7953
I don't think this is the problem, but thanks anyway.
For:
Hi,
SLA/OLA computations may use the
datefield as begin date, and, for each iteration, check if target date is in working days. Settingdatewithdate_creationvalue whendate == '0000-00-00 00:00:00'should fix your issue.Regards
Sorry I don't know how to setting date with date_creation value when date == '0000-00-00 00:00:00' as I said I delete SLA/OLA if this setting is done there.
cedric-anne
commented
4 years ago Sorry I don't know how to setting
date with date_creation value when date == '0000-00-00 00:00:00'as I said I delete SLA/OLA if this setting is done there.
Not sure that deleting SLA/OLA will remove computed SLA/OLA date from tickets table, so, you should try this query: UPDATE `glpi_tickets` SET `date` = `date_creation` WHERE `date` = '0000-00-00 00:00:00';.
jcervantes-sipecom
commented
4 years ago Hi, I wrote earlier that this was the solution:
You should try this query:
UPDATE `glpi_tickets` SET `date` = `date_creation` WHERE `date` = '0000-00-00 00:00:00';.
And partly it was, now I can view the tickets page and it loads. However, I had to delete it because the slowness continues and after a few minutes the following window appears:
Before this happens I can see in the button "Display debug information", which takes time to open when clicked, the following:
9944 Queries took 0.451s
And from line 440 the following command is executed:
SELECT COUNT() AS cpt FROM
glpi_calendars_holidaysINNER JOINglpi_holidaysON (glpi_calendars_holidays.holidays_id=glpi_holidays.id) WHEREglpi_calendars_holidays.calendars_id= '1' AND (((glpi_holidays.end_date>= '2020-10-23' ANDglpi_holidays.begin_date<= '2020-10-23')) OR ((glpi_holidays.is_perpetual= '1' AND MONTH(end_date)100 + DAY(end_date) >= 1023 AND MONTH(begin_date)*100 + DAY(begin_date) <= 1023)))
Up to line 6736, it runs:
SELECT COUNT() AS cptFROM
glpi_calendars_holidaysINNER JOINglpi_holidaysON (glpi_calendars_holidays.holidays_id=glpi_holidays.id)WHEREglpi_calendars_holidays.calendars_id= '1' AND (((glpi_holidays.end_date>= '2038-01-18' ANDglpi_holidays.begin_date<= '2038-01-18')) OR ((glpi_holidays.is_perpetual= '1' AND MONTH(end_date)100 + DAY(end_date) >= 118 AND MONTH(begin_date)*100 + DAY(begin_date) <= 118)))
Then stop, and from line 6854 execute the code again:
SELECT COUNT() AS cpt FROM
glpi_calendars_holidaysINNER JOINglpi_holidaysON (glpi_calendars_holidays.holidays_id=glpi_holidays.id) WHEREglpi_calendars_holidays.calendars_id= '1' AND (((glpi_holidays.end_date>= '2020-03-12' ANDglpi_holidays.begin_date<= '2020-03-12')) OR ((glpi_holidays.is_perpetual= '1' AND MONTH(end_date)100 + DAY(end_date) >= 312 AND MONTH(begin_date)*100 + DAY(begin_date) <= 312)))
Up to line 7057, it runs:
SELECT COUNT() AS cpt FROM
glpi_calendars_holidaysINNER JOINglpi_holidaysON (glpi_calendars_holidays.holidays_id=glpi_holidays.id) WHEREglpi_calendars_holidays.calendars_id= '1' AND (((glpi_holidays.end_date>= '2020-10-01' ANDglpi_holidays.begin_date<= '2020-10-01')) OR ((glpi_holidays.is_perpetual= '1' AND MONTH(end_date)100 + DAY(end_date) >= 1001 AND MONTH(begin_date)*100 + DAY(begin_date) <= 1001)))
It seems that this repetitive query causes the loading of the tickets page to be delayed.
Also in "Display (number of items)" it is 100.
cedric-anne
commented
4 years ago Checks on holidays can lead to a huge amount of DB queries depending on SLA/OLA durations and on calendars configuration. I do not know if this is the cause of your issue. Can you try with #7990 ?
jcervantes-sipecom
commented
4 years ago Hi, thanks for your answer. Sorry I didn't know how to apply this patch, so I did my research. I did:
curl -L https://github.com/glpi-project/glpi/pull/7990.patch > /tmp/7990.patch git apply /tmp/7990.patch
I got a problem with a folder that I didn't have "tests". I check the files and folder on this page, in CODE, and I found out that there is a folder "tests" that isn't in Releases. So I download this folder, and did again:
git apply /tmp/7990.patch service apache2 restart
And then I got this:
And again the tickets are not displayed.
cedric-anne
commented
4 years ago Hi,
Maybe you have invalid values in time_to_resolve, time_to_own, begin_waiting_date, ola_ttr_begin_date, internal_time_to_resolve, internal_time_to_own.
I added a commit to #7990 (e40b89a5989b694e663edae7ec6077a3a5ac05ce) that add a workaround to limit checks on holidays if distance between 2 dates seems illegitimate (I set this threshold to 2 years), but the real solution requires lots of work, and I am not sure to be able to implement it in a short delay.
Regards
jcervantes-sipecom
commented
4 years ago Hello, I made the change manually because when trying to apply the patch I got this error:
error: patch failed: inc/calendar.class.php:241 error: inc/calendar.class.php: patch does not apply error: patch failed: inc/calendar_holiday.class.php:206 error: inc/calendar_holiday.class.php: patch does not apply error: patch failed: tests/functionnal/Calendar.php:186 error: tests/functionnal/Calendar.php: patch does not apply error: patch failed: tests/functionnal/Calendar.php:269 error: tests/functionnal/Calendar.php: patch does not apply
Anyway, once the inc/calendar.class.php file was modified I was able to open the Tickets page and the "Display debug information" button no longer showed that repetitive iteration, it was faster to load the page: 3304 Queries took 0.275s
cedric-anne
commented
4 years ago Hi,
I keep this issue opened as I would like to find better than just a workaround.
You can take a look at your logs, you should see messages like Distance between "%s" and "%s" is too important, time computation will ignore holidays parameters., where %s are dates. It may help you to identify which value seems incorrect in your database, and maybe fix it.
Regards
cedric-anne
commented
3 years ago Hi @jcervantes-sipecom ,
I found another solution to speed-up SLA/OLA computation. Can you check if #8413 fixes your performances issues ?
Regards
jcervantes-sipecom
commented
3 years ago Hi @jcervantes-sipecom ,
I found another solution to speed-up SLA/OLA computation. Can you check if #8413 fixes your performances issues ?
Regards
Hi @cedric-anne, sorry for the delay I was really bussy. I updated to 9.5.3, do I the before patch still apply? or does the last Glpi versión already has it?
trasher
commented
3 years ago @jcervantes-sipecom referenced pull request has not been merged; so no, this is not currently part of GLPI.
jcervantes-sipecom
commented
3 years ago ok I will try it right now.
jcervantes-sipecom
commented
3 years ago Hello, sorry I did not send the error I got, I did these steps:
curl -L https://patch-diff.githubusercontent.com/raw/glpi-project/glpi/pull/8413.patch > /tmp/8413.patch
cd /var/www/html/glpi/
git apply /tmp/8413.patchand got the following:
warning: inc/calendar.class.php has type 100755, expected 100644
warning: inc/calendar_holiday.class.php has type 100755, expected 100644
warning: inc/holiday.class.php has type 100755, expected 100644
error: tests/functionnal/Calendar.php: No such file or directoryHi,
If the error did not blocked the patching process, the important part of the patch should have been applied correctly. Indeed, files mode is not a problem, and test file will not be usefull in your case.
If files were not patched, you can try with patch -p1 < /tmp/8413.patch.
Regards
jcervantes-sipecom
commented
3 years ago Ok I did what you suggested:
patch -p1 < /tmp/8413.patch
patching file inc/calendar.class.php
patching file inc/calendar_holiday.class.php
Hunk #1 succeeded at 237 (offset 31 lines).
patching file inc/holiday.class.php
patching file tests/functionnal/Calendar_Holiday.php
can't find file to patch at input line 528
Perhaps you used the wrong -p or --strip option?
The text leading up to this was:
--------------------------
|
|From 873b18e0cc8a07bf52275d505e6ada1aac5c48a6 Mon Sep 17 00:00:00 2001
|From: =?UTF-8?q?C=C3=A9dric=20Anne?= <cedric.anne@gmail.com>
|Date: Wed, 23 Sep 2020 09:50:25 +0200
|Subject: [PATCH 2/2] Revert tests update as cache should be invalidate on
| holiday addition
|
|---
| tests/functionnal/Calendar.php | 11 ++++-------
| 1 file changed, 4 insertions(+), 7 deletions(-)
|
|diff --git a/tests/functionnal/Calendar.php b/tests/functionnal/Calendar.php
|index 282f78cf64a..fee14e5e802 100644
|--- a/tests/functionnal/Calendar.php
|+++ b/tests/functionnal/Calendar.php
--------------------------
File to patch:
Skip this patch? [y]
Skipping patch.
2 out of 2 hunks ignoredWhat else do we need to try?
cedric-anne
commented
3 years ago Do you still have performances issues after applying this patch ?
cedric-anne
commented
3 years ago We made some improvement on master branch. Performances should be better in next major version.
Describe the bug When trying to access the main page of tickets (front/ticket.php), it does not finish loading, leaving completely blank, and the loading never completes. I have to restart Apache2 and Mysql to be able to enter the main page.
Page(s) URL
front/ticket.php
To reproduce
Steps to reproduce the behavior:
Expected behavior
Show the ticket page and all tickets.
Logs sql-errors.log
I had to reduce the log file to be able to upload, but originally this was 150mb. A partial portion of the sql-errors.log:
Screenshots
After some time later I got this error on browser:
Your GLPI setup (you can find it in Setup > General menu, System tab)
Additional context
Checking the logs, I found there is a loop with the portion of SQL before. I also find I'm not the only one with this problem:
I delete the SLA from Setup > Service Levels. The problem persist.
Same error with me.
Same error with no answer
This problem start when I was on 9.4.6 version, and I update to 9.5.2 hoping this was the solution but nothing. All the plugins are deactivate, so it's not a plugin error.