Migrating postgresql to mysql problems

[BenCowan]

Nagios XI 5.8.3
RedHat 8.4

I attempted to follow the guide here, but ran into errors during the import of the converted database to mysql:

https://support.nagios.com/kb/article/c ... i-560.html

[root@shire:/tmp/pg2mysql-master]# mysql nagiosxi --max_allowed_packet=512M --force < nagiosxi.mysql
ERROR 1146 (42S02) at line 8: Table 'nagiosxi.public.xi_auditlog' doesn't exist
ERROR 1146 (42S02) at line 1009: Table 'nagiosxi.public.xi_auditlog' doesn't exist
ERROR 1146 (42S02) at line 2010: Table 'nagiosxi.public.xi_auditlog' doesn't exist
ERROR 1146 (42S02) at line 3011: Table 'nagiosxi.public.xi_auditlog' doesn't exist
ERROR 1146 (42S02) at line 3189: Table 'nagiosxi.public.xi_auth_tokens' doesn't exist
ERROR 1146 (42S02) at line 3192: Table 'nagiosxi.public.xi_cmp_favorites' doesn't exist
ERROR 1146 (42S02) at line 3195: Table 'nagiosxi.public.xi_cmp_nagiosbpi_backups' doesn't exist
ERROR 1146 (42S02) at line 3212: Table 'nagiosxi.public.xi_cmp_scheduledreports_log' doesn't exist
ERROR 1146 (42S02) at line 3404: Table 'nagiosxi.public.xi_commands' doesn't exist
ERROR 1146 (42S02) at line 3407: Table 'nagiosxi.public.xi_events' doesn't exist
ERROR 1146 (42S02) at line 3477: Table 'nagiosxi.public.xi_meta' doesn't exist
ERROR 1146 (42S02) at line 3562: Table 'nagiosxi.public.xi_mibs' doesn't exist
ERROR 1146 (42S02) at line 3632: Table 'nagiosxi.public.xi_options' doesn't exist
ERROR 1146 (42S02) at line 3757: Table 'nagiosxi.public.xi_sysstat' doesn't exist
ERROR 1146 (42S02) at line 3778: Table 'nagiosxi.public.xi_usermeta' doesn't exist
ERROR 1146 (42S02) at line 4575: Table 'nagiosxi.public.xi_users' doesn't exist

[gsmith]

Hi,

Those tables should have been created when running this script:

Code: Select all

mysql -uroot -pnagiosxi < nagiosxi_create_db.sql

Image1.jpg

Could you drop the nagiosxi database in mysql and then rerun the above script?

Thanks

[BenCowan]

I've done it about 4 times now, and always get the same results, and my knowledge of mysql is almost non-existent. How can I confirm these tables are being created? The nagiosxi_create_db.sql runs without errors, but apparently is not working?

[gsmith]

Hi,

Sorry you had to run that 4 times.

So to see what the script did lets take a look at what should be a database with empty tables.

Run:

Code: Select all

mysql -u root -p;

<it will prompt you for the password, enter 'nagiosxi' without the quotes>

Code: Select all

show databases;

Should come back with something like:

Code: Select all

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nagiosxi           |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Next:

Code: Select all

use nagiosxi;
Database changed
mysql> show tables;
+--------------------------+
| Tables_in_nagiosxi       |
+--------------------------+
| xi_auditlog              |
| xi_auth_tokens           |
| xi_cmp_ccm_backups       |
| xi_cmp_favorites         |
| xi_cmp_nagiosbpi_backups |
| xi_cmp_trapdata          |
| xi_cmp_trapdata_log      |
| xi_commands              |
| xi_deploy_agents         |
| xi_deploy_jobs           |
| xi_eventqueue            |
| xi_events                |
| xi_incidents             |
| xi_meta                  |
| xi_mibs                  |
| xi_options               |
| xi_sessions              |
| xi_sysstat               |
| xi_usermeta              |
| xi_users                 |
+--------------------------+
20 rows in set (0.00 sec)

Let me know what happens.

Thanks

[BenCowan]

Okay, I think I made it work..., here is the log of what I did. I had to edit the "nagiosxi.mysql" file and remove all instances of 'public.', and then it loaded. See below...

[root@shire:/tmp/pg2mysql-master]# mysql < nagiosxi_create_db.sql
[root@shire:/tmp/pg2mysql-master]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23380
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| nagios |
| nagiosql |
| nagiosxi |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)

mysql> use nagiosxi;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------------+
| Tables_in_nagiosxi |
+--------------------------+
| xi_auditlog |
| xi_auth_tokens |
| xi_cmp_ccm_backups |
| xi_cmp_favorites |
| xi_cmp_nagiosbpi_backups |
| xi_cmp_trapdata |
| xi_cmp_trapdata_log |
| xi_commands |
| xi_deploy_agents |
| xi_deploy_jobs |
| xi_eventqueue |
| xi_events |
| xi_incidents |
| xi_meta |
| xi_mibs |
| xi_options |
| xi_sessions |
| xi_sysstat |
| xi_usermeta |
| xi_users |
+--------------------------+
20 rows in set (0.00 sec)

mysql> quit
Bye

[root@shire:/tmp/pg2mysql-master]# systemctl restart mysqld.service
[root@shire:/tmp/pg2mysql-master]# mysql nagiosxi --max_allowed_packet=512M --force < nagiosxi.mysql
ERROR 1146 (42S02) at line 8: Table 'nagiosxi.public.xi_auditlog' doesn't exist
ERROR 1146 (42S02) at line 1009: Table 'nagiosxi.public.xi_auditlog' doesn't exist
ERROR 1146 (42S02) at line 2010: Table 'nagiosxi.public.xi_auditlog' doesn't exist
ERROR 1146 (42S02) at line 3011: Table 'nagiosxi.public.xi_auditlog' doesn't exist
ERROR 1146 (42S02) at line 3102: Table 'nagiosxi.public.xi_cmp_favorites' doesn't exist
ERROR 1146 (42S02) at line 3105: Table 'nagiosxi.public.xi_cmp_nagiosbpi_backups' doesn't exist
ERROR 1146 (42S02) at line 3122: Table 'nagiosxi.public.xi_cmp_scheduledreports_log' doesn't exist
ERROR 1146 (42S02) at line 3314: Table 'nagiosxi.public.xi_meta' doesn't exist
ERROR 1146 (42S02) at line 3331: Table 'nagiosxi.public.xi_mibs' doesn't exist
ERROR 1146 (42S02) at line 3401: Table 'nagiosxi.public.xi_options' doesn't exist
ERROR 1146 (42S02) at line 3526: Table 'nagiosxi.public.xi_sysstat' doesn't exist
ERROR 1146 (42S02) at line 3547: Table 'nagiosxi.public.xi_usermeta' doesn't exist
ERROR 1146 (42S02) at line 4344: Table 'nagiosxi.public.xi_users' doesn't exist
[root@shire:/tmp/pg2mysql-master]# vi nagiosxi.mysql
[root@shire:/tmp/pg2mysql-master]# mysql nagiosxi --max_allowed_packet=512M --force < nagiosxi.mysql
ERROR 1146 (42S02) at line 3122: Table 'nagiosxi.xi_cmp_scheduledreports_log' doesn't exist
[root@shire:/tmp/pg2mysql-master]# mysql -e "CREATE USER 'nagiosxi'@'localhost' IDENTIFIED BY 'n@gweb';"
ERROR 1396 (HY000) at line 1: Operation CREATE USER failed for 'nagiosxi'@'localhost'
[root@shire:/tmp/pg2mysql-master]# mysql -e "GRANT ALL ON nagiosxi.* TO 'nagiosxi'@'localhost'; FLUSH PRIVILEGES;"
[root@shire:/tmp/pg2mysql-master]# vi /usr/local/nagiosxi/html/config.inc.php
[root@shire:/tmp/pg2mysql-master]# vi /usr/local/nagiosxi/var/xi-sys.cfg
[root@shire:/tmp/pg2mysql-master]# systemctl stop postgresql.service

After removing all instances of 'public.' from the table names, the only error was on the xi_cmp_scheduledreports_log table, and XI is still working with postgresql stopped. This was my Test server, and I still need to do Production and DR. Do you know why that file gets created with the table names prefixed with 'public.'?

[gsmith]

Hi

Not sure why 'public' was in there but that definitely was causing the problem.

It either came from the postgresql dump or the conversion program.

Do you still have the nagiosxi.psql file around? You could look at that.

I don't have a system running postgresql so would be interested in what you find.

Let me know....Thanks

[BenCowan]

I didn't want to paste the whole thing, so I grep-d for public.... Definitely came from the pg_dump. I searched for this issue on and the first hit was:

https://stackoverflow.com/questions/203 ... th-pg-dump

It's a 7 year old post, so not sure if anything has changed...

[root@shire:/tmp/pg2mysql-master]# cat nagiosxi.psql |grep public
-- Data for Name: xi_auditlog; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_auditlog (auditlog_id, log_time, source, "user", type, message, ip_address, details) FROM stdin;
-- Data for Name: xi_auth_tokens; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_auth_tokens (auth_token_id, auth_user_id, auth_session_id, auth_token, auth_valid_until, auth_expires_at, auth_restrictions, auth_used) FROM stdin;
-- Data for Name: xi_cmp_ccm_backups; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_cmp_ccm_backups (config_id, config_creator, config_name, config_date, archived, config_dir, config_hash, config_changes, config_diff) FROM stdin;
-- Data for Name: xi_cmp_favorites; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_cmp_favorites (item_id, user_id, title, partial_href) FROM stdin;
-- Data for Name: xi_cmp_nagiosbpi_backups; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_cmp_nagiosbpi_backups (config_id, config_creator, config_name, config_file, config_hash, config_changes, config_diff, config_date, archived) FROM stdin;
-- Data for Name: xi_cmp_scheduledreports_log; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_cmp_scheduledreports_log (log_id, report_name, report_run, report_user_id, report_status, report_type, report_run_type, report_recipients) FROM stdin;
-- Data for Name: xi_cmp_trapdata; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_cmp_trapdata (trapdata_id, trapdata_updated, trapdata_enabled, trapdata_event_name, trapdata_event_oid, trapdata_category, trapdata_severity, trapdata_exec, trapdata_desc, trapdata_custom_format, trapdata_raw_data, trapdata_wizard_integration_enabled, trapdata_wizard_integration_data, trapdata_parent_mib_name) FROM stdin;
-- Data for Name: xi_cmp_trapdata_log; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_cmp_trapdata_log (trapdata_log_id, trapdata_log_event_name, trapdata_log_event_oid, trapdata_log_numeric_oid, trapdata_log_symbolic_oid, trapdata_log_community, trapdata_log_trap_hostname, trapdata_log_trap_ip, trapdata_log_agent_hostname, trapdata_log_agent_ip, trapdata_log_category, trapdata_log_severity, trapdata_log_uptime, trapdata_log_datetime, trapdata_log_bindings) FROM stdin;
-- Data for Name: xi_commands; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_commands (command_id, group_id, submitter_id, beneficiary_id, command, submission_time, event_time, frequency_type, frequency_units, frequency_interval, processing_time, status_code, result_code, command_data, result) FROM stdin;
-- Data for Name: xi_deploy_agents; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_deploy_agents (deploy_id, creator_id, deployed_date, last_updated_date, last_status_check, available, version, address, hostname, os, metadata) FROM stdin;
-- Data for Name: xi_deploy_jobs; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_deploy_jobs (job_id, job_name, creator_id, version, os, addresses, ncpa_token, username, password, vault_password, sudo, status, pid, metadata) FROM stdin;
-- Data for Name: xi_eventqueue; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_eventqueue (eventqueue_id, event_time, event_source, event_type, event_meta) FROM stdin;
-- Data for Name: xi_events; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_events (event_id, event_time, event_source, event_type, status_code, processing_time) FROM stdin;
-- Data for Name: xi_incidents; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_incidents (id, incident_id, submitted, type, host, service, event_time, status, output) FROM stdin;
-- Data for Name: xi_meta; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_meta (meta_id, metatype_id, metaobj_id, keyname, keyvalue) FROM stdin;
-- Data for Name: xi_mibs; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_mibs (mib_id, mib_name, mib_uploaded, mib_last_processed, mib_type) FROM stdin;
-- Data for Name: xi_options; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_options (option_id, name, value) FROM stdin;
-- Data for Name: xi_sessions; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_sessions (session_id, session_phpid, session_created, session_user_id, session_address, session_page, session_data, session_last_active) FROM stdin;
-- Data for Name: xi_sysstat; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_sysstat (sysstat_id, metric, value, update_time) FROM stdin;
-- Data for Name: xi_usermeta; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_usermeta (usermeta_id, user_id, keyname, keyvalue, autoload) FROM stdin;
-- Data for Name: xi_users; Type: TABLE DATA; Schema: public; Owner: -
COPY public.xi_users (user_id, username, password, name, email, backend_ticket, enabled, api_key, api_enabled, login_attempts, last_attempt, last_password_change, last_login, last_edited, last_edited_by, created_by, created_time) FROM stdin;
-- Name: if_command_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.if_command_id_seq', 20, false);
-- Name: if_meta_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.if_meta_id_seq', 16, false);
-- Name: if_option_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.if_option_id_seq', 25, false);
-- Name: if_sysstat_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.if_sysstat_id_seq', 15, false);
-- Name: if_user_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.if_user_id_seq', 14, false);
-- Name: if_usermeta_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.if_usermeta_id_seq', 142, false);
-- Name: xi_auditlog_auditlog_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_auditlog_auditlog_id_seq', 62339, true);
-- Name: xi_auth_tokens_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_auth_tokens_id_seq', 3222, true);
-- Name: xi_cmp_ccm_backups_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_cmp_ccm_backups_id_seq', 1, false);
-- Name: xi_cmp_favorites_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_cmp_favorites_id_seq', 1, true);
-- Name: xi_cmp_nagiosbpi_backups_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_cmp_nagiosbpi_backups_id_seq', 123, true);
-- Name: xi_cmp_scheduledreports_log_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_cmp_scheduledreports_log_id_seq', 190, true);
-- Name: xi_cmp_trapdata_trapdata_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_cmp_trapdata_trapdata_id_seq', 1, false);
-- Name: xi_cmp_trapdata_trapdata_log_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_cmp_trapdata_trapdata_log_id_seq', 1, false);
-- Name: xi_commands_command_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_commands_command_id_seq', 18408, true);
-- Name: xi_deploy_agents_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_deploy_agents_id_seq', 1, false);
-- Name: xi_deploy_jobs_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_deploy_jobs_id_seq', 1, false);
-- Name: xi_eventqueue_eventqueue_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_eventqueue_eventqueue_id_seq', 827883, true);
-- Name: xi_events_event_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_events_event_id_seq', 1115112, true);
-- Name: xi_incidents_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_incidents_id_seq', 1, false);
-- Name: xi_meta_meta_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_meta_meta_id_seq', 1115127, true);
-- Name: xi_mibs_mib_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_mibs_mib_id_seq', 68, true);
-- Name: xi_options_option_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_options_option_id_seq', 191, true);
-- Name: xi_sessions_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_sessions_id_seq', 23635, true);
-- Name: xi_sysstat_sysstat_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_sysstat_sysstat_id_seq', 101, true);
-- Name: xi_usermeta_usermeta_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_usermeta_usermeta_id_seq', 1803, true);
-- Name: xi_users_user_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
SELECT pg_catalog.setval('public.xi_users_user_id_seq', 63, true);

[gsmith]

Hi

Thanks for doing that. I'll keep in mind for next time.

Sounds like you are good to go so I am going to lock this.

Thanks!