#1921 Problem when backing up itop 2.7.0

[Georg Smejkal]

i've got a Problem when backing up itop

Following error is displayed when creating a backup from the cmdb admin UI

Error: Failed to execute mysqldump: mysqldump: Couldn't execute 'show create table `view_DBServer`': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= 0 and `SoftwareLicence_softwarelicence_id_Licence`.`end_date` < date_format...' at line 1 (1064)

Creating backup: '/var/www/html/cmdb/data/backups/manual/cmdb-2020-11-19_12_15.tar.gz' backup: creating tmp dir '/var/www/html/cmdb/data/tmp-backup-421710889' backup: adding resource '/var/www/html/cmdb/conf/production/config-itop.php' Starting backup of localhost/cmdb(suffix:'') backup: generate data file with command: mysqldump --defaults-extra-file="/tmp/itop-mysqldump-vKGlkh" --opt --skip-lock-tables --default-character-set=utf8mb4 --add-drop-database --single-transaction --host='localhost' --port='3306' --user=xxxxx --result-file='/var/www/html/cmdb/data/tmp-backup-421710889/itop-dump.sql' 'cmdb' mysqldump said: mysqldump: Couldn't execute 'show create table `view_DBServer`': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= 0 and `SoftwareLicence_softwarelicence_id_Licence`.`end_date` < date_format...' at line 1 (1064) Error: Failed to execute: mysqldump --defaults-extra-file="/tmp/itop-mysqldump-vKGlkh" --opt --skip-lock-tables --default-character-set=utf8mb4 --add-drop-database --single-transaction --host='localhost' --port='3306' --user=xxxxx --result-file='/var/www/html/cmdb/data/tmp-backup-421710889/itop-dump.sql' 'cmdb' . The command returned:2 Error: mysqldump said: mysqldump: Couldn't execute 'show create table `view_DBServer`': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= 0 and `SoftwareLicence_softwarelicence_id_Licence`.`end_date` < date_format...' at line 1 (1064)

The Error when trying to do a select on the sql stays the same

MariaDB [sbacmdb]> select * from view_DBServer;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= 0 and `SoftwareLicence_softwarelicence_id_Licence`.`end_date` < date_format...' at line 1

As far as uage, itop works, but i can't create a backup in any way without errors.

is there something i can do, so the views won't lead to errors when backing up?
Just for fun i've made a snapshot of the machine and tried to delete the view, after which the backup continues, but i also can't seem to recreate them (same error as when trying to select from it)

What am I missing?
Do you have any hints for what i could do?

Thank you very much

[Plesiosaure]

I just had this same problem with 2.7.2-1 but not with 2.7.1.
You say it occurred with 2.7.0 ?
So it means that it is maybe not due to an issue with the version ? or it is a regression ?

[Georg Smejkal]

I've tried with 2.7.2-1 now as well (upgrade was possible without error)- same error.

Somehow the views have a syntax errors, when trying to backup as well as when trying to

select * from view_DBServer;

Crappy Workaround:
Delete Views
backup
run setup again to recreate (broken) views

[Georg Smejkal]

Following views have syntax errors
view_DBServer
view_Licence
view_Middleware
view_OSLicence
view_OtherSoftware
view_PCSoftware
view_Server
view_SoftwareInstance
view_SoftwareLicence
view_VirtualMachine
view_WebServer
view_lnkDocumentToLicence

i've tried downgrading to 2.7.1 but sadly this didn't work.

I found an old ticket which had a similar problem
https://sourceforge.net/p/itop/tickets/1336/

But deleting views isn't a fix, nor is it supported

[Paty]

I just had this same problem with 2.7.2-1 too.

[glorang]

Same issue just popped up here as well. Issue is caused by MariaDB upgrade (on Debian) from "1:10.3.25-0+deb10u1" to "1:10.3.27-0+deb10u1".

I've used following command to downgrade MariaDB

# apt-get install mariadb-common=1:10.3.25-0+deb10u1 mariadb-client-10.3=1:10.3.25-0+deb10u1 mariadb-client-core-10.3=1:10.3.25-0+deb10u1 mariadb-server-10.3=1:10.3.25-0+deb10u1 mariadb-server-core-10.3=1:10.3.25-0+deb10u1 mariadb-server=1:10.3.25-0+deb10u1

After that backup / views seems to work ok again.

Disclaimer: we've only run this on our test instance so use at your own risk ;-)

[glorang]

If any of the iTop devs is reading this, somewhere in the view you have written (or have generated) :

`SoftwareLicence_softwarelicence_id_Licence`.`end_date` is null = 0

This works fine under MariaDB 10.3.25:

# mysql -V
mysql  Ver 15.1 Distrib 10.3.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

MariaDB [(none)]> select (null is null = 0);
+--------------------+
| (null is null = 0) |
+--------------------+
|                  0 |
+--------------------+

But not under 10.3.27:

# mysql -V
mysql  Ver 15.1 Distrib 10.3.27-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

MariaDB [(none)]> select (null is null = 0);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= 0)' at line 1

HTH;

[Rorcha07]

Hello,

It could be related to this bug:
https://jira.mariadb.org/browse/MDEV-24194

[Georg Smejkal]

At the moment i'm using 10.3.27-MariaDB, so it might actually be that bug.
So i'll be waiting for the 10.3.28 update to might fix this

[Vincent @ Combodo]

MariaDB impacted versions
10.5.7 10.5.8
10.4.16 10.4.17
10.3.26 10.3.27
10.2.35 10.2.36
Workaround:

• Drop Views before doing Backup, Views are not required by iTop itself.
• Cautious a Setup will recreate the Views, so drop them again after that.

(Views will be totally removed in 3.0, an optional extension will be proposed for those using them).

[Georg Smejkal]

Thank you very much for the information!