postgresql migration to mysql... 99% working

[mon-team]

Dear all,
I migrated postgresql to mysql, following the procedure.
Nagios XI (5.5) is working well, i have only a little problem trying to do an 'Apply Configuration'. The error message is:

SQL Error [nagiosql] : Unknown column 'l.id' in 'field list'

Into the 'nagiosql' mysql databases, the table tbl_session_locks missed. I created it by myself but i don't know the structure, so i put only one field (the primary key 'id'). The error infact is related to the fact that nagios find the table but the expected fields are still missing.
If i comment out these lines in the /usr/local/nagiosxi/html/includes/components/nagioscorecfg/applyconfig.php it works:

#$user_id = intval($_SESSION['user_id']);
#$sql = "SELECT *, l.id AS id FROM `tbl_session_locks` AS l LEFT JOIN `tbl_session` AS s ON l.sid = s.id WHERE s.user_id != ".$user_id.";";
#$res = exec_sql_query(DB_NAGIOSQL, $sql);
#$locks = $res->getArray();
$locks;

but i don't think it to be a valid idea (it should be the code which verifies is other users are trying to apply the configuration).
How can i fix it?
could you give me the sql code to create the table? (By the way also table nagiosql.tbl_session was missing)

Regards
Francesco

[tgriep]

The error might be misleading. When an Apply Config is ran, it logs it in to the nagiosxi database which it what you converted from Postgres to MYSQL.
The issue might be with writing the data to the nagiosxi database and not reading the data from the nagiosql database.

First, run the following as root to repair all of the MYSQL databases on the system just to make sure they are repaired..

Code: Select all

mysqlcheck -f -r -u root -pnagiosxi --all-databases --use-frm

To add the missing tables, you can re-run the manual XI upgrade procedure. Instructions are as follows.
https://assets.nagios.com/downloads/nag ... ctions.pdf

If that doesn't update the tables, leave the XI upgrade files on the serevr and the following commands will update the nagiosql database with all of the updates.

Code: Select all

mysql -u root -pnagiosxi nagiosql </tmp/nagiosxi/subcomponents/nagiosql/mods/schema_01.sql
mysql -u root -pnagiosxi nagiosql </tmp/nagiosxi/subcomponents/nagiosql/mods/schema_02.sql
mysql -u root -pnagiosxi nagiosql </tmp/nagiosxi/subcomponents/nagiosql/mods/schema_03.sql
mysql -u root -pnagiosxi nagiosql </tmp/nagiosxi/subcomponents/nagiosql/mods/schema_04.sql

Last, if you still receive errors, it might be the magiosxi database. Run the following as root and post the /tmp/nagiosxi.txt file so we can check those tables.

Code: Select all

mysqldump --no-data nagiosxi -u root -pnagiosxi >/tmp/nagiosxi.txt

[mon-team]

Thanks,
i solved the issue by re-importing the table definition from:

/tmp/nagiosxi/subcomponents/ccm/db/schema.sql
/tmp/nagiosxi/subcomponents/ccm/db/schema_01.sql
/tmp/nagiosxi/subcomponents/ccm/db/schema_02.sql
/tmp/nagiosxi/subcomponents/ccm/db/schema_03.sql


during the upgrade tables nagiosql.tbl_session and nagiosql.tbl_session_locks were not created.
Now are present and the aplly configuration is working as expected.
Thanks!
Francesco

[tgriep]

Your welcome, glad it is working now. Let me know if you have any further questions or that it is OK to lock and close the post.