SQL error on backup after converting postgresql to mysql

[perric]

Hi, I'm in the process of converting my development 5.4.0 system which was upgraded from 2014 from postgresql to mysql, so I can do an export and restore onto a new 5.4.0 VI, in preparation for repeating the experience on my production environment.
(I started the process when 5.4.0 came out so that is why I'm testing on that version; I will retest on current version before doing it to the live system).

I followed the postgresql to mysql conversion steps at https://support.nagios.com/kb/article.php?id=560 and all went well - there were some duplicate settings at the bottom of the script trying to reapply primary key and the like that it complained about, but no errors on data import.

I was able to log in after shutting down postgresql, and then immediately went to take a backup through the web interface and got this error at the top of the page:

SQL Error [nagiosxi] : Duplicate entry '0' for key 'PRIMARY'

The backup still happened, so not sure if this is a harmless error maybe caused by the auto increment PKs being off?

What else does the old nagiosxi pgsql database control that I should retest on mysql - looking through the import script, looks like users, options, events, commands, audit log. Anything else I should try after the conversion to make sure I don't have issues?

Thanks

[perric]

I get a similar error if I remove a user - user still gets removed but I get these

SQL Error [nagiosxi] : Duplicate entry '0' for key 'PRIMARY'
SQL Error [nagiosxi] : Duplicate entry '0' for key 'PRIMARY'
SQL Error [nagiosxi] : Duplicate entry '0' for key 'PRIMARY'
SQL Error [nagiosxi] : Duplicate entry '0' for key 'PRIMARY'

[perric]

I'm pretty sure this is an issue with the conversion script not picking up that the auditlog_id is supposed to be auto increment

From the conversion script:
CREATE TABLE xi_auditlog (
auditlog_id int(11) NOT NULL,
log_time timestamp,
source text,
`user` text,
`type` int(11),
message text,
ip_address text,
details text
) ENGINE=myisam;

From the base nagios install script:

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_auditlog` (
`auditlog_id` int auto_increment,
`log_time` timestamp,
`source` text,
`user` varchar(200),
`type` int,
`message` text,
`ip_address` varchar(45),
`details` text,
primary key (`auditlog_id`),
index using btree (`log_time`),
index using btree (`user`),
index using btree (`type`),
index using btree (`ip_address`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

I'm going to retry the import using the create statement from the nagios install and the insert statements from the upgrade script. Only thing I'm not sure about is whether I am going to have a mismatch on the last used IDs? For example, I only have 20-25 records in xi_auditlog, but the highest value is 3487

[tgriep]

AUTO_INCREMENT should be enabled on all of the xxxxxxxx_id field for the tables in the database. Without that, it could be causing the error.
You can either change the fields on your own or you can run the following and post the /tmp/nagiosxi.txt file which will dump out the table settings and then we can see which ones have to be changed.

Code: Select all

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

[perric]

From the import script created by the utility, it looks like it's fine on all of the tables except for xi_auditlog.

I am trying to use the create script in 5.4.0 install to create the table definitions and then import the data, but I'm running into a problem:

The create script in 5.4.0 is missing the api_key info that exists in the pgsql-exported data. Not sure if this is intended or if it moved elsewhere? I can always modify the table to add it.

5.4.0 xi_users Create script:

Code: Select all

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_users` (
    `user_id` int auto_increment,
    `username` varchar(64) not null,
    `password` varchar(128) not null,
    `name` varchar(100),
    `email` varchar(128) not null,
    `backend_ticket` varchar(128),
    `enabled` smallint default 1,
    primary key (`user_id`),
    unique(`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Conversion script:

Code: Select all

CREATE TABLE xi_users (
    user_id int(11) auto_increment NOT NULL,
    username varchar(64) NOT NULL,
    password varchar(64) NOT NULL,
    name varchar(64),
    email varchar(128) NOT NULL,
    backend_ticket varchar(128),
    enabled smallint DEFAULT 1 ,
    api_key varchar(128),
    api_enabled smallint DEFAULT 0 ,
    login_attempts smallint DEFAULT 0 ,
    last_attempt int(11) DEFAULT 0 NOT NULL,
    last_password_change int(11) DEFAULT 0 NOT NULL,
    last_login int(11) DEFAULT 0 NOT NULL,
    last_edited int(11) DEFAULT 0 NOT NULL,
    last_edited_by int(11) DEFAULT 0 NOT NULL,
    created_by int(11) DEFAULT 0 NOT NULL,
    created_time int(11) DEFAULT 0 NOT NULL
, PRIMARY KEY(`user_id`)
) ENGINE=myisam;

edit: removed the 2nd issue regarding autoincrement mismatch, not sure what the problem was but on a reimport everything looks good and the autoincrement is properly set to the highest value

[tgriep]

The additions to the nagiosxi sql database can be found in this folder if you extracted the Nagios XI install in the /tmp folder.

Code: Select all

/tmp/nagiosxi/nagiosxi/nagiosxi-db/mods/mysql/

Add those missing entries to the database and see if it works for you.

[ssax]

Later in the install it runs these as well, this is where the api_key field comes from in psql as well (different script, same functionality):

You will need to do it in a different order though, create the table and then run these:

Code: Select all

USE `nagiosxi`;

ALTER TABLE xi_usermeta MODIFY keyvalue LONGTEXT;

ALTER TABLE xi_users ADD COLUMN api_key varchar(128) NULL;
ALTER TABLE xi_users ADD COLUMN api_enabled smallint DEFAULT 0 NOT NULL;

UPDATE xi_users SET api_enabled = 1, api_key = backend_ticket;

# Account security features
ALTER TABLE xi_users ADD COLUMN login_attempts smallint(6) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_attempt int(10) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_password_change int(10) DEFAULT 0 NOT NULL;

# Security information
ALTER TABLE xi_users ADD COLUMN last_login int(11) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_edited int(11) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_edited_by int(10) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN created_by int(10) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN created_time int(11) DEFAULT 0 NOT NULL;

# Event Queue table for more efficient Global Event Handlers
CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_eventqueue` (
    `eventqueue_id` int auto_increment,
    `event_time` int,
    `event_source` smallint,
    `event_type` smallint default 0 not null,
    `event_meta` text,
    primary key (`eventqueue_id`),
    unique(`eventqueue_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Then import your data.

[perric]

Thanks - I am sorted. From my perspective, there is either a bug or a caveat in the migration process at https://support.nagios.com/kb/article.php?id=560 that should be documented. Happy to provide the script output or the source pgsql database or whatever else can help troubleshoot.

Thread can be closed.

Thanks again!

[mcapra]

I'll see about getting the article updated with the findings in this thread.

Did you have additional questions regarding this, or can we close this thread?

[perric]

OK to close
Thanks