error in sql-errors-log

[jorge-madrigal]

Hello @tomolimo

I have detected this error in the file: sql-errors.log of glpi:

SQL: SELECT glpi_changetasks.* FROM glpi_changetasks INNER JOIN glpi_changes ON (glpi_changes.id = glpi_changetasks.changes_id$ Warnings: 1292: Incorrect datetime value: '2018-11-27T00:00:00Z' for column 'end' at row 1 1292: Incorrect datetime value: '2018-11-27T00:00:00Z' for column 'end' at row 1 1292: Incorrect datetime value: '2018-11-27T00:00:00Z' for column 'end' at row 1 Backtrace : src/DBmysqlIterator.php:112 src/DBmysql.php:1078 DBmysqlIterator->execute() src/CommonITILTask.php:1208 DBmysql->request() plugins/processmaker/inc/task.class.php:152 CommonITILTask::genericPopulatePlanning() src/Planning.php:2236 PluginProcessmakerTask::populatePlanning() src/Planning.php:2006 Planning::constructEventsArraySingleLine() ajax/planning.php:50 Planning::constructEventsArray() public/index.php:82 require() {"user":"1246@ddb5de5d4f70"} [2023-11-27 08:06:10] glpisqllog.WARNING: DBmysql::query() in /var/www/html/glpi/src/DBmysql.php line 419 ** MySQL query warnings: SQL: SELECT glpi_problemtasks. FROM glpi_problemtasks INNER JOIN glpi_problems ON (glpi_problems.id = glpi_problemtasks.`proble$ Warnings: 1292: Incorrect datetime value: '2018-11-27T00:00:00Z' for column 'end' at row 1 1292: Incorrect datetime value: '2018-11-27T00:00:00Z' for column 'end' at row 1 1292: Incorrect datetime value: '2018-11-27T00:00:00Z' for column 'end' at row 1

---

Thank you Regards, Jorge Madrigal

[tomolimo]

Hello @jorge-madrigal Could you give the steps in order to reproduce this error? Thank you, Regards, Tomolimo

[jorge-madrigal]

Hello @tomolimo:

I have to investigate it, because I think it is an automatic action. At least, I don't do anything special.

Thank you Jorge

[jorge-madrigal]

Hello @tomolimo

The error occurs when you click on the assistance->planning menu.

I have modified the task.class.php file (function populatePlanning), with this code, so that the error does not occur.

 static function populatePlanning($params = []) :array {

      $events = [];

      if (isset($params['start'])) {
         $params['begin'] = $params['start']; //'2000-01-01 00:00:00';
         if ($params['type'] == 'group') {
            $params['who_group'] = $params['who'];
            $params['whogroup'] = $params['who'];
            $params['who'] = 0;
         }

         $objects = ['TicketTask', 'ChangeTask', 'ProblemTask'];
         foreach ($_SESSION['glpi_plannings']['filters'] as $tasktype => $iteminfo) {
            if (!$iteminfo['display'] || !in_array($tasktype, $objects)) {
               continue;
            }

        \\ New lines
        $params['end']=date("Y-m-d H:i:s",strtotime($params['end']));
        $params['begin']=date("Y-m-d H:i:s",strtotime($params['begin']));
        $params['start']=date("Y-m-d H:i:s",strtotime($params['start']));

        $ret = CommonITILTask::genericPopulatePlanning($tasktype, $params);

etc.....

I know it is not the best solution in programming, but work.

Thank you Jorge

[tomolimo]

Hello @jorge-madrigal,

One question: did you transform the DATETIME fields into TIMESTAMP? See: https://glpi-install.readthedocs.io/en/latest/command-line.html#various-tools there is a command line to do that: php bin/console glpi:migration:timestamps

Thank you Regards, Tomolimo

[jorge-madrigal]

Hola @tomolimo:

Yes, we made that command. Anyway, I've been through it again.

The result:

root@ddb5de5d4f70:/var/www/html/glpi# php bin/console glpi:migration:timestamps Se encontraron 0 tabla(s) que requieren migración. No se necesita migración. root@ddb5de5d4f70:/var/www/html/glpi#

Translation: 0 table(s) were found that require migration. No migration needed.

Thank you Jorge

[tomolimo]

Hello @jorge-madrigal Could you copy/paste here the complete populatePlanning() function before modifications and after? Thank you, Regards, Tomlimo

[jorge-madrigal]

Hello @tomolimo:

Before:

static function populatePlanning($params = []) :array {

      $events = [];

      if (isset($params['start'])) {
         $params['begin'] = $params['start']; //'2000-01-01 00:00:00';
         if ($params['type'] == 'group') {
            $params['who_group'] = $params['who'];
            $params['whogroup'] = $params['who'];
            $params['who'] = 0;
         }

         $objects = ['TicketTask', 'ChangeTask', 'ProblemTask'];
         foreach ($_SESSION['glpi_plannings']['filters'] as $tasktype => $iteminfo) {
            if (!$iteminfo['display'] || !in_array($tasktype, $objects)) {
               continue;
            }

        $ret = CommonITILTask::genericPopulatePlanning($tasktype, $params);

            foreach ($ret as $key => $event) {
               // if todo or done but need to show them (=planning)
 if ($event['state'] == Planning::TODO || $event['state'] == Planning::INFO || ($params['display_done_events'] == 1 && $event['state'] == Planning::DONE)) {
                  // check if task is one within a case
                  $pmTask = new PluginProcessmakerTask($tasktype);
                  if ($pmTask->getFromDB($event[strtolower($tasktype).'s_id'])) {
                     $event['editable'] = false;
                     $tmpCase = new PluginProcessmakerCase;
                     $tmpCase->getFromDB($pmTask->fields['plugin_processmaker_cases_id']);
                     $event['url'] = $tmpCase->getLinkURL().'&forcetab=PluginProcessmakerTask$'.$pmTask->fields['items_id'];

                     $taskCat = new TaskCategory;
                     $taskCat->getFromDB( $pmTask->fields['taskcategories_id'] );
                     $taskComment = isset($taskCat->fields['comment']) ? $taskCat->fields['comment'] : '';
                     if (Session::haveTranslations('TaskCategory', 'comment')) {
                        $taskComment = DropdownTranslation::getTranslatedValue( $taskCat->getID(), 'TaskCategory', 'comment', $_SESSION['glpilanguage'], $taskComment );
                     }

                     $event['content'] = str_replace( '##processmaker.taskcomment##', $taskComment, $event['content'] );
                     $event['content'] = str_replace( ['\n##processmakercase.url##', '##processmakercase.url##'], "", $event['content'] );
                     $events[$key]     = $event;
                  }
               }
            }
         }
      }
      return $events;
}

After:

static function populatePlanning($params = []) :array {

      $events = [];

      if (isset($params['start'])) {
         $params['begin'] = $params['start']; //'2000-01-01 00:00:00';
         if ($params['type'] == 'group') {
            $params['who_group'] = $params['who'];
            $params['whogroup'] = $params['who'];
            $params['who'] = 0;
         }

         $objects = ['TicketTask', 'ChangeTask', 'ProblemTask'];
         foreach ($_SESSION['glpi_plannings']['filters'] as $tasktype => $iteminfo) {
            if (!$iteminfo['display'] || !in_array($tasktype, $objects)) {
               continue;
            }
        $params['end']=date("Y-m-d H:i:s",strtotime($params['end']));
        $params['begin']=date("Y-m-d H:i:s",strtotime($params['begin']));
        $params['start']=date("Y-m-d H:i:s",strtotime($params['start']));

        $ret = CommonITILTask::genericPopulatePlanning($tasktype, $params);

            foreach ($ret as $key => $event) {
               // if todo or done but need to show them (=planning)
 if ($event['state'] == Planning::TODO || $event['state'] == Planning::INFO || ($params['display_done_events'] == 1 && $event['state'] == Planning::DONE)) {
                  // check if task is one within a case
                  $pmTask = new PluginProcessmakerTask($tasktype);
                  if ($pmTask->getFromDB($event[strtolower($tasktype).'s_id'])) {
                     $event['editable'] = false;
                     $tmpCase = new PluginProcessmakerCase;
                     $tmpCase->getFromDB($pmTask->fields['plugin_processmaker_cases_id']);
                     $event['url'] = $tmpCase->getLinkURL().'&forcetab=PluginProcessmakerTask$'.$pmTask->fields['items_id'];

                     $taskCat = new TaskCategory;
                     $taskCat->getFromDB( $pmTask->fields['taskcategories_id'] );
                     $taskComment = isset($taskCat->fields['comment']) ? $taskCat->fields['comment'] : '';
                     if (Session::haveTranslations('TaskCategory', 'comment')) {
                        $taskComment = DropdownTranslation::getTranslatedValue( $taskCat->getID(), 'TaskCategory', 'comment', $_SESSION['glpilanguage'], $taskComment );
                     }

                     $event['content'] = str_replace( '##processmaker.taskcomment##', $taskComment, $event['content'] );
                     $event['content'] = str_replace( ['\n##processmakercase.url##', '##processmakercase.url##'], "", $event['content'] );
                     $events[$key]     = $event;
                  }
               }
            }
         }
      }
      return $events;
}

I hope you find it useful.

Thank you. Jorge Madrigal

[tomolimo]

Hello @jorge-madrigal the correct fix is to comment the line that assigns a value to $params['begin']

      if (isset($params['start'])) {
         //$params['begin'] = $params['start']; //'2000-01-01 00:00:00';
         if ($params['type'] == 'group') {
            $params['who_group'] = $params['who'];
            $params['whogroup'] = $params['who'];
            $params['who'] = 0;
         }

Could you try that?

Thank you, Regards, Tomolimo

[jorge-madrigal]

Hello @tomolimo

I have tested and it works correctly

Thank you Jorge