Trying to run backup via mysqldump return an error

[Kristian]

Hello guys,
I'm writing this post as follow up on the previous one: https://sourceforge.net/p/itop/tickets/1955/

So what I'm trying to do is to simply create backup of my iTOP database via mysqldump.
Why I'm not using the integrated backup solution - because I decided to use external database (the database is not on the same VM as iTOP application)

Can you advice me how I can solve this issue?
Thanks.

[Syscrusher]

With all due respect to the iTop developers, I disagree with the assertion in the ticketing system that this is "not a bug". It is, very much, a bug.

I have just replicated precisely your error, on a brand-new, clean installation of iTop 2.7.3, using the integrated backup feature. I've tried all manner of options on a manual invocation of mysqldump to try to find a combination that will work. The error is due to something in the schema, perhaps a column name, because the error occurs even with "--no-data" specified to back up just the schema, and it occurs with --quote-names.

This was during an attempt to create an initial, baseline backup minutes after finishing the iTop installation wizard, before any data had been populated into the instance.

Installation details:
iTop 2.7.3
MariaDB Server 10.3.27
Debian Linux 10.8
PHP 7.3

The eventual goal, once backups are viable, is to migrate the database to our production MariaDB cluster, but for now it is running on the same machine as Apache and iTop.

This is a newly-provisioned, dedicated virtual server that will run nothing but iTop, so the environment is extremely clean other than the prerequisites for iTop and IPAM-for-iTop.

Here is the error:

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/data/backups/manual/itop-2021-03-26_22_19.tar.gz' backup: creating tmp dir '/var/www/html/data/tmp-backup-1312536948' backup: adding resource '/var/www/html/conf/production/config-itop.php' Starting backup of localhost/itop(suffix:'') backup: generate data file with command: mysqldump --defaults-extra-file="/tmp/itop-mysqldump-5mxqph" --opt --skip-lock-tables --default-character-set=utf8mb4 --add-drop-database --single-transaction --host='localhost' --port='3306' --user=xxxxx --result-file='/var/www/html/data/tmp-backup-1312536948/itop-dump.sql' 'itop' 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-5mxqph" --opt --skip-lock-tables --default-character-set=utf8mb4 --add-drop-database --single-transaction --host='localhost' --port='3306' --user=xxxxx --result-file='/var/www/html/data/tmp-backup-1312536948/itop-dump.sql' 'itop' . 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)

[Pierre Goiffon]

Hello,

With all due respect to the iTop developers, I disagree with the assertion in the ticketing system that this is "not a bug". It is, very much, a bug.

No one ever said that ! I asked to create a discussion so that we can qualify this, in other words understand hwo to reproduce, if a workaround exists, etc.

It's in everyone's best interest to fix bugs in iTop. Having feedbacks from the community is very valuable to us !
And the application history demonstrate that lots of very good things happens thanks to the community : stability, improvements, feedbacks, security, ... (...)

But it is far more easier for us that problems encounters by an iTop user be discussed in the forum first, so that most feedback are obtained, and all the needed information are gathered. Everyone can participate in the forum, almost nobody does in the ticket system.
When we have a clear mind of the problem and know what to change thanks to the discussions in the forum, then we can create a ticket.

[Pierre Goiffon]

With all due respect to the iTop developers, I disagree with the assertion in the ticketing system that this is "not a bug". It is, very much, a bug.

No one ever said that ! I

Oh ok when opening the ticket, I understand why you understood this :

Status: not-a-bug

Indeed, my mistake ! I've just changed to not-reproduced

At least I hope our way to deal with forums and tickets is clearer now O:)

[Syscrusher]

That was also my source of misunderstanding, so thank you for clarifying your intent and the reason for referring the issue to the forum. I apologize if my tone was unduly harsh.

[Pierre Goiffon]

Thanks, you're welcome !

[Pierre Goiffon]

About the problem now :)

Installation details:
iTop 2.7.3
MariaDB Server 10.3.27
Debian Linux 10.8
PHP 7.3

Oh, both iTop 2.7 and MariaDB... Isn't this bug you are dealing with : Backup & MariaDB

They seem very similar to me ?
If this is the same thing, seems like 10.3.28 is out now, can you give it a try ?

[Syscrusher]

I am not able to see the linked wiki page because I do not have an account on the private wiki.

I will assume you are correct, as seems likely, and I will download the new version and install it. Our iTop installation is brand new and not yet production, so I can apply upgrades without disruption to our operations.

I will return here and report the outcome.

[Pierre Goiffon]

Ouch, I linked to the wrong site my apologies. Here is the correct link to the public wiki : Backup & MariaDB

[Syscrusher]

I have good news. The MariaDB upgrade fixes the problem here.

MariaDB 10.3.28 is not yet available in Debian stable, but I was able to obtain MariaDB 10.5.9 is provided by Debian testing branch. I installed that, plus its dependencies, on my test system, and the backups now work via my external shell script, or via the integrated web UI backup tool in iTop 2.7.3.

As far as I am concerned, though I do not speak for @Kristian, this problem is resolved.

For reference, our external backup script uses the following options on the mysqldump command:

mysqldump --add-drop-database --complete-insert \ --create-options --quote-names --events \ --no-data --databases itop

The extra options are needed because some of our other applications use triggers and stored procedures.

Our backup script runs this command to back up the schema only (which is useful in case we need it for reference or documentation purposes), then it runs the same command again without the "--no-data" option. Obviously the two iterations are sent to different output files. We gzip the data dump but leave the schema-only dump unzipped for convenience, since these are very small.

[Kristian]

Hey @Syscrusher thanks for clarifying that. I have tested only with mysqldump, because I'm using SQL database which is running on a different host, other than where the iTOP instance is running.

Because of that, I cannot use integrated backup tool. I simply don't have mysqldump locally installed.

The only way to do a backup of my existing database is by using phpmyadmin and do export of the database.

[Pierre Goiffon]

Hi Kristian, ok so you're using a distant DB server.
Can you give same info as Syscrusher did ? iTop version, DB name & version, OS name & version, PHP version ?

[Kristian]

Sure, here is the required info:

iTOP side:

root@itop:~# cat /etc/debian_version
10.8
root@itop:~# dpkg -l |grep php
ii  libapache2-mod-php7.3            7.3.27-1~deb10u1                      amd64        server-side, HTML-embedded scripting language (Apache 2 module)
ii  php-apcu                         5.1.17+4.0.11-1                       amd64        APC User Cache for PHP
ii  php-apcu-bc                      1.0.4-4                               amd64        APCu Backwards Compatibility Module
ii  php-common                       2:69                                  all          Common files for PHP packages
ii  php-pear                         1:1.10.6+submodules+notgz-1.1+deb10u1 all          PEAR Base System
ii  php-sqlite3                      2:7.3+69                              all          SQLite3 module for PHP [default]
ii  php7.3-cli                       7.3.27-1~deb10u1                      amd64        command-line interpreter for the PHP scripting language
ii  php7.3-common                    7.3.27-1~deb10u1                      amd64        documentation, examples and common module for PHP
ii  php7.3-curl                      7.3.27-1~deb10u1                      amd64        CURL module for PHP
ii  php7.3-dev                       7.3.27-1~deb10u1                      amd64        Files for PHP7.3 module development
ii  php7.3-gd                        7.3.27-1~deb10u1                      amd64        GD module for PHP
ii  php7.3-imap                      7.3.27-1~deb10u1                      amd64        IMAP module for PHP
ii  php7.3-json                      7.3.27-1~deb10u1                      amd64        JSON module for PHP
ii  php7.3-ldap                      7.3.27-1~deb10u1                      amd64        LDAP module for PHP
ii  php7.3-mbstring                  7.3.27-1~deb10u1                      amd64        MBSTRING module for PHP
ii  php7.3-mysql                     7.3.27-1~deb10u1                      amd64        MySQL module for PHP
ii  php7.3-opcache                   7.3.27-1~deb10u1                      amd64        Zend OpCache module for PHP
ii  php7.3-readline                  7.3.27-1~deb10u1                      amd64        readline module for PHP
ii  php7.3-soap                      7.3.27-1~deb10u1                      amd64        SOAP module for PHP
ii  php7.3-sqlite3                   7.3.27-1~deb10u1                      amd64        SQLite3 module for PHP
ii  php7.3-xml                       7.3.27-1~deb10u1                      amd64        DOM, SimpleXML, WDDX, XML, and XSL module for PHP
ii  php7.3-zip                       7.3.27-1~deb10u1                      amd64        Zip module for PHP
ii  pkg-php-tools                    1.37                                  all          various packaging tools and scripts for PHP packages
root@itop:~#

And DB side:

root@monitoring.home.lan:~# cat /etc/debian_version
10.9
root@monitoring.home.lan:~# dpkg -l |grep maria
ii  libmariadb3:amd64                    1:10.3.27-0+deb10u1                                                      amd64        MariaDB database client library
ii  mariadb-client                       1:10.3.27-0+deb10u1                                                      all          MariaDB database client (metapackage depending on the latest version)
ii  mariadb-client-10.3                  1:10.3.27-0+deb10u1                                                      amd64        MariaDB database client binaries
ii  mariadb-client-core-10.3             1:10.3.27-0+deb10u1                                                      amd64        MariaDB database core client binaries
ii  mariadb-common                       1:10.3.27-0+deb10u1                                                      all          MariaDB common metapackage
ii  mariadb-server                       1:10.3.27-0+deb10u1                                                      all          MariaDB database server (metapackage depending on the latest version)
ii  mariadb-server-10.3                  1:10.3.27-0+deb10u1                                                      amd64        MariaDB database server binaries
ii  mariadb-server-core-10.3             1:10.3.27-0+deb10u1                                                      amd64        MariaDB database core server files
root@monitoring.home.lan:~#

[Pierre Goiffon]

Ok, MariaDB 10.3.27 is the same version as Syscrusher. Can you check the same fix, meaning updating to 10.3.28 ?

[Kristian]

Sure, I will have a look and let you know. By now, there is no available update in the Debian official repositories. So I have to add MariaDB specific repo.

I will write here when I have the time to test it.

[Pierre Goiffon]

Thanks !

[Kristian]

Brief explanation what I've did:

I decided to test with two scenarios, the first one is creating backup of the original database via phpMyAdmin and export functionality, then transfer this backup to the latest version of MySQL server (I've prepared local installation on the iTOP machine) and import the backup to it.

Although the export went fine, the import didn't generated any errors, and on first glance it looks like everything was fine, when I checked the content I was unable to see all the CI's, later on I've got some SQL errors - so I can conclude this scenarios has failed.

The second one was to do in place upgrade of the database. To be sure in case of failure I've created a backup via mariabackup tool locally, then upgraded the database to the latest version, and everything seems to be fine.

The mysqldump command is able to create backups for that database, and doesn't return any errors. I can check the sql file and it looks line as well.

So as conclusion we can say - yeah the MySQL was the culprit.

[Pierre Goiffon]

Warning, you're talking about MySQL whereas you sent screenshots showing MariaDB 10.3.27 !

Many thanks for the feedback anyway !

I'm waiting for Syscrusher's feedback before changing the ticket status.

[Kristian]

Yeah, my fault I meant MariaDB. I continue to live with the feeling that they are same project :-) Sorry about that.

[Pierre Goiffon]

You're welcome :)
Believe me, at Combodo we know they are not the same ;o)

[Syscrusher]

Good day! I apologize, I did not have time to work on this yesterday due to other projects. I am preparing to install the upgraded MariaDB on my test server now, and hopefully I will have test results here later today.

[Pierre Goiffon]

Hello, adding reference so that this discussion will appear directly in the ticket [#1955]

[Syscrusher]

@pgoiffon I wish to extend my thanks to you for your assistance in resolving this problem.