When upgrading, missing data source profile can cause errors to be logged

[nem8]

Describe the bug

Upgrade from 0.8.8 to 1.2.25 (latest) fails on db upgrade somewhere between 0.8.8 to 1.0.5 due to missing data source profile id.

To Reproduce

Steps to reproduce the behavior:
Upgrade from 0.8.8 to >1.0.0 and observe /var/www/html/cacti/log/cacti.log for error messages like:
CMDPHP ERROR: A DB Exec Failed!, Error: 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 'WHERE data_source_profile_id = 0' at line 1

Expected behavior

Expect database upgrade to complete without errors.

Desktop (please complete the following information)

• OS: Ubuntu 22.04.3 LTS

• Browser: Firefox

• Version 118.0.1

Additional context

We have an old cacti 0.8.8 install that we are migrating to a new server. I have set up a new server and installed all required packages. I did an mysqldump of old database and imported to new.
Installation wizard fails on updating database from 1.0.4 to 1.0.5 and after looking at the files in /var/www/html/cacti/install/upgrades i can see that in 1_0_0.php the table is created and in 1_0_5.php installer attempts to fetch the id from data_source_profiles but it fails and returns an empty string as the table is empty.
When grepping for data_source_profiles in all the upgrade php scripts, only the following is found:

1_0_0.php:	db_install_execute("CREATE TABLE IF NOT EXISTS `data_source_profiles` (
1_0_0.php:	db_install_execute("CREATE TABLE IF NOT EXISTS `data_source_profiles_cf` (
1_0_0.php:	db_install_execute("CREATE TABLE IF NOT EXISTS `data_source_profiles_rra` (
1_0_0.php:				$id = sql_save($save, 'data_source_profiles');
1_0_0.php:					db_install_execute("INSERT INTO data_source_profiles_rra
1_0_0.php:					db_install_execute("REPLACE INTO data_source_profiles_cf
1_0_5.php:	$profile_id_results = db_install_fetch_cell('SELECT id FROM data_source_profiles ORDER BY `default` DESC LIMIT 1');

I am not able to really understand why data_source_profiles is empty, but apparently this is a bug considering there is no fallback in 1_0_5.php in case it is..

Please advice if there is anything i can do manually to solve this.

To be noted, i also emptied host_snmp_cache as it failed on duplicate primary keys. I figured i would rerun the poller once upgrade is finished. I dont think this is relevant to the issue at hand.

[bmfmancini]

Wow that's quite the upgrade!! Try running audit_database.php in the. Cli folder with the - - report flag it will check the dB against the current schema and report back There is an option to repair which will add/remove tables or columns as needed I would take a complete backup first though

…

On Wed, Oct 11, 2023, 06:14 nem8 ***@***.***> wrote: Describe the bug Upgrade from 0.8.8 to 1.2.25 (latest) fails on db upgrade somewhere between 0.8.8 to 1.0.5 due to missing data source profile id. To Reproduce Steps to reproduce the behavior: Upgrade from 0.8.8 to >1.0.0 and observe /var/www/html/cacti/log/cacti.log for error messages like: CMDPHP ERROR: A DB Exec Failed!, Error: 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 'WHERE data_source_profile_id = 0' at line 1 Expected behavior Expect database upgrade to complete without errors. Desktop (please complete the following information) - OS: Ubuntu 22.04.3 LTS - Browser: Firefox - Version 118.0.1 Additional context We have an old cacti 0.8.8 install that we are migrating to a new server. I have set up a new server and installed all required packages. I did an mysqldump of old database and imported to new. Installation wizard fails on updating database from 1.0.4 to 1.0.5 and after looking at the files in /var/www/html/cacti/install/upgrades i can see that in 1_0_0.php the table is created and in 1_0_5.php installer attempts to fetch the id from data_source_profiles but it fails and returns an empty string as the table is empty. When grepping for data_source_profiles in all the upgrade php scripts, only the following is found: 1_0_0.php: db_install_execute("CREATE TABLE IF NOT EXISTS `data_source_profiles` ( 1_0_0.php: db_install_execute("CREATE TABLE IF NOT EXISTS `data_source_profiles_cf` ( 1_0_0.php: db_install_execute("CREATE TABLE IF NOT EXISTS `data_source_profiles_rra` ( 1_0_0.php: $id = sql_save($save, 'data_source_profiles'); 1_0_0.php: db_install_execute("INSERT INTO data_source_profiles_rra 1_0_0.php: db_install_execute("REPLACE INTO data_source_profiles_cf 1_0_5.php: $profile_id_results = db_install_fetch_cell('SELECT id FROM data_source_profiles ORDER BY `default` DESC LIMIT 1'); I am not able to really understand why data_source_profiles is empty, but apparently this is a bug considering there is no fallback in 1_0_5.php in case it is.. Please advice if there is anything i can do manually to solve this. To be noted, i also emptied host_snmp_cache as it failed on duplicate primary keys. I figured i would rerun the poller once upgrade is finished. I dont think this is relevant to the issue at hand. — Reply to this email directly, view it on GitHub <#5527>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADGEXTFPOUHIUSNCWOA4D73X6ZWQ7ANCNFSM6AAAAAA53XR6U4> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[nem8]

Yeah its a big leap. Its a long forgotten server, set up by someone who probably left the company years ago..
As mentioned i dumped the db and im working on this upgrade on a new server so it doesnt matter if i have to nuke the db and start over a few more times, we wont lose any data.

When i run php -f audit_database.php -- report it results in:
WARNING: Cacti must be upgraded first. Use the --upgrade option to perform that upgrade
I can see from the code that it expects the installed cacti version to match the one from the version table, however the version table is stuck at 1.0.4 as the upgrade stalled there.

mysql -e "use cacti; select cacti from version"
+-------+
| cacti |
+-------+
| 1.0.4 |
+-------+

I could bypass this check but from first glance it looks like it will do changes to the database even with the report argument (?)

[TheWitness]

Go to the readme and ensure you have all the requisite params, then restart MariaDB, and the run the CLI script upgrade_databse.php --forcever=0.8.8

[nem8]

Hey,

Yeah i restarted the upgrade process by dropping the database and re-importing the 0.8.8 one.
The result is the same as before tho:

 sudo -u www-data php -q cli/upgrade_database.php
NOTE: Repairing Tables for Local Database
Upgrading from v0.8.8h
Upgrading from v0.8.8h (DB 0.8.8h) to v1.0.0
++++++++++++++++++++++++++++++++++++-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-++++++++++++++++++++++++++++++++++++++-++++-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++----++-++++++++++++++++++++++++++++--+++-++++-+++++++++++++++++-+-+-+-++++++-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-Upgrading from v1.0.0 (DB 1.0.0) to v1.0.4
-Upgrading from v1.0.4 (DB 1.0.4) to v1.0.5
x+++++x+++++++++

***removed 1 error for snmp cache as i resolve this by emptying the table as mentioned in previous post***

################################
[Fail]       -   DB Exec Failed!, Error 1064: 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 'WHERE data_source_profile_id = 0' at line 1
+------------------------------+
UPDATE data_template_data SET data_source_profile_id =  WHERE data_source_profile_id = 0
################################

Running it with or without forcever='0.8.8' yields the same result. My original post outlines my findings in the upgrade php scripts, and logically these are used regardless of where the upgrade is triggered from (wizard or cli) so result is the same. data_source_profile_id doesnt exist.

If someone knows what this is maybe i can set it manually, or correct an error in the script?

There is an identical case about this from 2020 where it supposedly was fixed..
#3319

I cant seem to find the corresponding code change tho, so i dont know what was changed in that patch..

[nem8]

I looked at the git history for the upgrade script and there hasn't been any other changes done to it besides the one in 2020.

$profile_id_results = db_install_fetch_cell('SELECT id FROM data_source_profiles ORDER BY `default` DESC LIMIT 1');
$profile_id         = $profile_id_results['data'];
db_install_execute('UPDATE data_template_data SET data_source_profile_id = ' . $profile_id . ' WHERE data_source_profile_id = 0');

data_source_profiles is empty so it returns an empty string (or nothing).
I checked data_source_data and this has no data_source_profile_id's other than 0:

mysql -e 'SELECT COUNT(data_source_profile_id) FROM cacti.data_template_data WHERE data_source_profile_id <> 0;'
+-------------------------------+
| COUNT(data_source_profile_id) |
+-------------------------------+
|                             0 |
+-------------------------------+

[TheWitness]

You did not follow my advice.

[TheWitness]

What is your MariaDB/MySQL version? I was referring to this...

[mysqld]

# required for multiple language support
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Memory tunables - Cacti provides recommendations at upgrade time
max_heap_table_size = XXX
max_allowed_packet = 500M
tmp_table_size = XXX
join_buffer_size = XXX
sort_buffer_size = XXX

# important for compatibility
sql_mode=NO_ENGINE_SUBSTITUTION

# innodb settings - Cacti provides recommendations at upgrade time
innodb_buffer_pool_instances = XXX
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = XXX
innodb_sort_buffer_size = XXX
innodb_doublewrite = ON

# required
innodb_file_per_table = ON
innodb_file_format = Barracuda
innodb_large_prefix = 1

# not all version support
innodb_flush_log_at_timeout = 3

# for SSD's/NVMe
innodb_read_io_threads = 32
innodb_write_io_threads = 16
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_flush_method = O_DIRECT

[nem8]

You did not follow my advice.

I did, I just didn't type it out.
I went through README.MD and I verified all mariadb settings, added a few recommendations that I didn't already have and changed one that the upgrade complained about.
I can verify mariadb version when I'm at the server again, it's the one from Ubuntu repos whichever that is. Do you suspect this is a mariadb issue?

[TheWitness]

If you can send a dump of the database to thewitness@cacti.net I'll take a look at it. Your call.

[netniV]

I feel we should deprecate upgrade_database in favor of the cli installer. However, in this case, your search revealed that there are two parts that should have inserted data for profiles in your 1.0.5 update:

1_0_0.php:				$id = sql_save($save, 'data_source_profiles');
1_0_0.php:					db_install_execute("INSERT INTO data_source_profiles_rra

If you can see why these didn't occur, had any errors during your upgrade, and/or any exclusions in the outer IF statements that may have resulted in them being skipped, that would aid in understanding how to fix them. I'm not looking at the code itself right now so this is purely from reading this issue.

[TheWitness]

Still waiting on that dump @nem8

[nem8]

Hello,
Sorry for the delay in responding. I havent had time to continue working on the issue quite yet.
Im not able to upload a dump of the database due to company policy, else i would.
I will spend more time troubleshooting the code to see if i can get closer to pinpointing why it fails. PHP isnt a language i know very well, but it seems to be readable enough. Just need to look up the odd thing here and there..

I will have more time later this week to work on this issue and ill update here with my findings, or lack thereof..

Thanks

[TheWitness]

Is it a classic install or was it bundled with a vendor tool? Upload a screen shot of the RRA's page.