1 Topic by stefan (edited by stefan 2022-02-24 08:43:20)

  • Registered: 2018-03-25
  • Posts: 58

Topic: SQL Error when restoring database

Hi,

I get this Error in sql.log when trying to restore backuped database.

SQL query error: SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect DOUBLE value: ''
Query string: UPDATE `mycal_settingsX` SET `value` = '' WHERE (`name` = 'spMiniCal' OR `name` = 'spImages' OR `name` = 'spInfoArea') AND `value` = 0

Tried with new installation with only one event in the events table.

Quote Post 1

  • Roel
  • Roel
  • Administrator
  • Offline
  • Registered: 2018-02-26
  • Posts: 565

Re: SQL Error when restoring database

Hi Stefan,
This problem has been reported twice before and so far I could not reproduce it and solve it.
For the other users it appeared that the problem occurred when they migrated to PHP 8.
And when they went back to PHP 7 the problem disappeared.

On the web I saw that many other people with different applications are having this same problem.

Are you using PHP 8? and if so, could you go back to PHP 7.4 and try the Restore again? and let us know your findings.
Roel

Quote Post 2

3 Reply by stefan (edited by stefan 2022-02-26 09:43:12)

  • Registered: 2018-03-25
  • Posts: 58

Re: SQL Error when restoring database

Hi Roel,

in my case php 7.4 ist running.
I found some hints in the web in relation to different mysql versions and if they are running in strict mode or not. But because my test server ist a synology NAS with mariaDB i was not able to get more information about that.
Next hint I found was, that the error can accour, when a string is compared with a number or something like that.

So I looked in the sql.log file and then found the accordingly line in the common/toolboxx.php.
Its on line 877 and 878
At the end og theese lines I sorrounded the 0 and the 1 with single qotation marks. So the result is:

  } else { //spMiniCal existing (0 -> '', 1-> '2,4,6')
    dbQuery("UPDATE `settingsX` SET `value` = '' WHERE (`name` = 'spMiniCal' OR `name` = 'spImages' OR `name` = 'spInfoArea') AND `value` = '0'");
    dbQuery("UPDATE `settingsX` SET `value` = '2,4,6' WHERE (`name` = 'spMiniCal' OR `name` = 'spImages' OR `name` = 'spInfoArea') AND `value` = '1'");
  }

This solves the error!
To me it makes sense, because the "value" field is of type "varchar".
In other server envirements it may only be a warning instead of an error.

Quote Post 3

  • Roel
  • Roel
  • Administrator
  • Offline
  • Registered: 2018-02-26
  • Posts: 565

Re: SQL Error when restoring database

Hi Stefan,
Thank you very much for digging into this problem. I've changed this in the toolboxx.php file.
I will also add the solution to the "Known Issues" section of the forum.

(The SQL error message "Invalid datetime format" is very misleading, since there is no datetime involved.)
Roel

Quote Post 4