Nagios NNA Migration from 2024R2.1 > 2026R1.0.1 Migration SQL Credential Issue

[smithjor]

I am trying to migrate my NNA 2024R2.1 environment to 2026R1.0.1 following https://assets.nagios.com/downloads/nag ... o-2026.pdf. My source environment is Debian 12 and my target environment is Debian 13. I have installed a fresh NNA 2026 on the Debian 13 environment and configured email as instructed.

I generated a backup and sql dump on the source environment as instructed and transferred it to the target environment. When I run the migration it fails to import the SQL db stating there is a credential issue. I have tested the credentials of the root user and the nagiosna user and am able to connect. I also checked the reference file /var/www/html/nagiosna/.env and the credentials and username are properly detailed here.

Is there another place I need to look or modify for the script to specify the credentials? Find below console output of a restore attempt:

root@NAGIOSNETWORKANALYZER:/usr/local/nagiosna/scripts# ./migrate_na.sh /store/backups/nagiosna/1771962587.tar.gz \/store/backups/nagiosna/nagiosna.sql
TS=1772042243
Extracting backup to /store/backups/nagiosna/1772042243-restore...
In /store/backups/nagiosna/1772042243-restore/1771962587...
Backup files look okay. Preparing to restore...
Shutting down services...
Restoring flow files to /usr/local/nagiosna/var/...
Restoring MySQL databases...
Using SQL dump file: /store/backups/nagiosna/nagiosna.sql
Processing SQL dump file for migration...
Renaming table: nagiosna_Checks -> checks
Removing table: nagiosna_ChecksCmdLinker
Removing table: nagiosna_ChecksHSALinker
Removing table: nagiosna_ChecksTrapsLinker
Removing table: nagiosna_ChecksUsersLinker
Renaming table: nagiosna_Commands -> commands
Renaming table: nagiosna_HostServiceAssociations -> service_hostnames
Renaming table: nagiosna_NagiosServers -> nagios_servers
Removing table: nagiosna_Queries
Removing table: nagiosna_Reports
Renaming table: nagiosna_SGLinker -> source_group_linker
Renaming table: nagiosna_SourceGroups -> source_groups
Renaming table: nagiosna_Sources -> sources
Removing table: nagiosna_SourcesViewsLinker
Renaming table: nagiosna_TrapReceivers -> snmp_receivers
Renaming table: nagiosna_Views -> views
Renaming table: nagiosna_auth_servers -> auth_servers
Renaming table: nagiosna_cf_options -> config_options
Removing table: nagiosna_cmdsubsys
Removing table: nagiosna_groups
Removing table: nagiosna_hostname_cache
Removing table: nagiosna_login_attempts
Removing table: nagiosna_migrations
Removing table: nagiosna_path_nodes
Removing table: nagiosna_path_status
Removing table: nagiosna_sessions
Removing table: nagiosna_usermeta
Renaming table: nagiosna_users -> users
Removing table: nagiosna_users_groups

Filtering complete. Output written to: /store/backups/nagiosna/1772042243-restore/nagiosna_filtered.sql
Truncating selected tables in nagiosna before import...
Importing filtered database...
ERROR 1054 (42S22) at line 121: Unknown column 'disable_abnormal' in 'field list'
Error restoring MySQL database 'nagiosna' - check database credentials!
Filtered SQL dump is available at: /store/backups/nagiosna/1772042243-restore/nagiosna_filtered.sql

[sgardil]

On your backup its saying the column disable_abnormal doesnt exist on the sources table. Could you check your old NNA instances database to see if that is true?

[smithjor]

It does exist on the source.

root@LANNAGIOSNETANALYZER:~# mysql
MariaDB [(none)]> SELECT TABLE_NAME, COLUMN_NAME
-> FROM INFORMATION_SCHEMA.COLUMNS
-> WHERE TABLE_SCHEMA = 'nagiosna'
-> AND COLUMN_NAME = 'disable_abnormal';
+------------------+------------------+
| TABLE_NAME | COLUMN_NAME |
+------------------+------------------+
| nagiosna_Sources | disable_abnormal |
+------------------+------------------+
1 row in set (0.001 sec)


It does not exist on the target:

root@NAGIOSNETWORKANALYZER:~# mysql -u root -p
Enter password:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| nagiosna |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT TABLE_NAME, COLUMN_NAME
-> FROM INFORMATION_SCHEMA.COLUMNS
-> WHERE TABLE_SCHEMA = 'nagiosna'
-> AND COLUMN_NAME = 'disable_abnormal';
Empty set (0.00 sec)

[sgardil]

It does exist on the source.

root@LANNAGIOSNETANALYZER:~# mysql
MariaDB [(none)]> SELECT TABLE_NAME, COLUMN_NAME
-> FROM INFORMATION_SCHEMA.COLUMNS
-> WHERE TABLE_SCHEMA = 'nagiosna'
-> AND COLUMN_NAME = 'disable_abnormal';
+------------------+------------------+
| TABLE_NAME | COLUMN_NAME |
+------------------+------------------+
| nagiosna_Sources | disable_abnormal |
+------------------+------------------+
1 row in set (0.001 sec)


It does not exist on the target:

root@NAGIOSNETWORKANALYZER:~# mysql -u root -p
Enter password:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| nagiosna |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT TABLE_NAME, COLUMN_NAME
-> FROM INFORMATION_SCHEMA.COLUMNS
-> WHERE TABLE_SCHEMA = 'nagiosna'
-> AND COLUMN_NAME = 'disable_abnormal';
Empty set (0.00 sec)

Hm interesting, the scripts should be handling it but it seems to be failing for some reason that I haven't seen when testing it. Would you mind modifying the script to test a change since I wasnt able to replicate. In

Code: Select all

/usr/local/nagiosna/scripts/filter_sql_dump.py

could you add on line 123-124 a line that has this:

Code: Select all

line = line.strip()

It should be before the line that starts with

Code: Select all

insert_match

[smithjor]

Thanks I made the change and attempted it but it failed with the same. It is ok to use the old backup or should I be generating a new backup each time?

root@NAGIOSNETWORKANALYZER:~# vim /usr/local/nagiosna/scripts/filter_sql_dump.py
# Match INSERT INTO table
line = line.strip()
insert_match = re.match(rf'^INSERT INTO `{re.escape(table_name)}` \(([^)]+)\) VALUES (.+);$', line, re.DOTALL)
if not insert_match:
return None, None


root@NAGIOSNETWORKANALYZER:/usr/local/nagiosna/scripts# ./migrate_na.sh /store/backups/nagiosna/1771962587.tar.gz \/store/backups/nagiosna/nagiosna.sql
TS=1772632289
Extracting backup to /store/backups/nagiosna/1772632289-restore...
In /store/backups/nagiosna/1772632289-restore/1771962587...
Backup files look okay. Preparing to restore...
Shutting down services...
Restoring flow files to /usr/local/nagiosna/var/...
Restoring MySQL databases...
Using SQL dump file: /store/backups/nagiosna/nagiosna.sql
Processing SQL dump file for migration...
Renaming table: nagiosna_Checks -> checks
Removing table: nagiosna_ChecksCmdLinker
Removing table: nagiosna_ChecksHSALinker
Removing table: nagiosna_ChecksTrapsLinker
Removing table: nagiosna_ChecksUsersLinker
Renaming table: nagiosna_Commands -> commands
Renaming table: nagiosna_HostServiceAssociations -> service_hostnames
Renaming table: nagiosna_NagiosServers -> nagios_servers
Removing table: nagiosna_Queries
Removing table: nagiosna_Reports
Renaming table: nagiosna_SGLinker -> source_group_linker
Renaming table: nagiosna_SourceGroups -> source_groups
Renaming table: nagiosna_Sources -> sources
Removing table: nagiosna_SourcesViewsLinker
Renaming table: nagiosna_TrapReceivers -> snmp_receivers
Renaming table: nagiosna_Views -> views
Renaming table: nagiosna_auth_servers -> auth_servers
Renaming table: nagiosna_cf_options -> config_options
Removing table: nagiosna_cmdsubsys
Removing table: nagiosna_groups
Removing table: nagiosna_hostname_cache
Removing table: nagiosna_login_attempts
Removing table: nagiosna_migrations
Removing table: nagiosna_path_nodes
Removing table: nagiosna_path_status
Removing table: nagiosna_sessions
Removing table: nagiosna_usermeta
Renaming table: nagiosna_users -> users
Removing table: nagiosna_users_groups

Filtering complete. Output written to: /store/backups/nagiosna/1772632289-restore/nagiosna_filtered.sql
Truncating selected tables in nagiosna before import...
Importing filtered database...
ERROR 1054 (42S22) at line 121: Unknown column 'disable_abnormal' in 'field list'
Error restoring MySQL database 'nagiosna' - check database credentials!
Filtered SQL dump is available at: /store/backups/nagiosna/1772632289-restore/nagiosna_filtered.sql

[sgardil]

Thanks I made the change and attempted it but it failed with the same. It is ok to use the old backup or should I be generating a new backup each time?

It's fine to use the old backup when you run it, just know that if you have any new data coming in on your 2024 NNA between the time of the old backup and when you run the script than that data would not be present in the data if you use an older back up.

Just to be safe you are getting the backup from a 2024 NNA and the mysql dump from running this

Code: Select all

mysqldump --no-create-info --complete-insert nagiosna > ~/nagiosna.sql

from an 2024 version of NNA and then are moving them onto your NNA with version 2026R1.0.1 and then trying the migration?

If you are then I am a little loss as I havnt seen this on any of my testing, especially since your old system has the column in the table.

[sgardil]

I just tested again and I was still unable to reproduce the error sadly. If it's not an issue of using a backup from a 2026 version then we may need to open a support ticket to dive deeper into your system to see what's going on.

[smithjor]

Yes the backup was created and transferred using these exact commands. I do have a support case open I am awaiting further help from them.

/usr/local/nagiosna/scripts/backup_na.sh
mkdir /home/temp
mv /store/backups/nagiosna/1771962587.tar.gz /home/temp
mysqldump --no-create-info --complete-insert nagiosna > /tmp/nagiosna.sql
cp /tmp/nagiosna.sql /home/temp/
rsync -avHP /home/temp/ root@xxxx:/home/temp/

I appreciate your assistance thus far. I will update this thread with what support determines.

[sgardil]

For anyone else that may run into this issue, it looks like the issue was due to some formatting of the sql dump. The python script for filtering and modifying table insertions for the migration was excepting the insert statements to all be on one line, but it seems like under certain stipulations that running the sql dump command can expand the inserts to many lines. I believe this can happen when sql sizes reach a certain size that it will wrap the inserts into new lines rather than trying to put so much information onto a single line.

For the fix, we updated the python filter script to be able to handle this situation. If you run into this issue in the mean time, I attached the updated script below. Take that script and replace the script with the same name in

Code: Select all

/usr/local/nagiosna/scripts/

. Make sure to give it the correct permissions

Code: Select all

sudo chmod 751 /usr/local/nagiosna/scripts/filter_sql_dump.py

and that it is owned by root root. After that run the migration as normal and things should function properly.

[sgardil]

Note, this fix will be in our next release, 2026R1.2, and the upgrade process will work to migrate to 1.2.