Postgresql to MySQL migration problems

[Fred Kroeger]

I tried to migrate the Postgres files across to MySQL following the Nagios Document. https://support.nagios.com/kb/article.php?id=560
It appears that something doesn't work in the conversion process however. The mysql file that gets generated has all the table information and appears to create them OK, but there is no data to populate the tables. Specifically the user information. It's all in the pgsql file however so the dump part of the process worked OK.
Any ideas on what to try next ? I'm running NagiosXI 5.4.2

[tgriep]

In this file

Code: Select all

nagiosxi.mysql

Only has the table structures but no data?

When you ran the following command to convert the data from the postgres dump to the mysql format, it is generate any errors?

Code: Select all

php pg2mysql_cli.php nagiosxi.psql nagiosxi.mysql myisam

Can you post or PM the nagiosxi.mysql file so we can view it?

[SteveBeauchemin]

This sounds familiar... Feels familiar... Oh yeah, I lived through it...

Check my previous post on this. It may help.
The one thing I learned from the PG to MySQL migration is that there is nothing special in PostgreSQL that needs to touched to move to MySQL.

You just need it to happen.

Look at the post for my story.
https://support.nagios.com/forum/viewto ... 16&t=41447

I hope that helps. This is a one time ever thing. Once it is done, you can move on and forget it ever happened.

Steve B

[Fred Kroeger]

Thanks Steve - I did read your post beforehand and my eyes started to glaze over. That was the reason for my post - surely it shouldn't be so hard !

I will PM the mysql file over. There is no Data - just the table info. The pgdump file definitely contains the data.
I did run the convert process twice to see if I had missed anything - still a very small file and No errors displayed during the process .

[tgriep]

Thanks for the files. I didn't see anything in them that gave a clue on why the converted file didn't have any data.
I took a look at the web site for the conversion tool and the suggested way of dumping the Postgres database is to use the following command.

Code: Select all

pg_dump -U nagiosxi nagiosxi --inserts --format p -f nagiosxi.psql

Try doing that to dump the data and see if the conversion tool works and copies the data as well as the table structures.

[Fred Kroeger]

Well that made some difference!
When I convert the psql file now , the mysql file is almost the same size and looks like all the data is now included.

When I ran the import, apart from all the errors - because the tables had already been created previously, I got a few errors

Code: Select all

ERROR 1170 (42000) at line 6076: BLOB/TEXT column 'ip_address' used in key specification without a key length
ERROR 1170 (42000) at line 6078: BLOB/TEXT column 'source' used in key specification without a key length
ERROR 1064 (42000) at line 6080: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"user" )' at line 1

Line 6080 reads :

Code: Select all

ALTER TABLE `xi_auditlog` ADD INDEX ( "user" ) ;

so I got rid of the quotes around user and reran the import again and then got

Code: Select all

ERROR 1170 (42000) at line 6076: BLOB/TEXT column 'ip_address' used in key specification without a key length
ERROR 1170 (42000) at line 6078: BLOB/TEXT column 'source' used in key specification without a key length
ERROR 1170 (42000) at line 6080: BLOB/TEXT column 'user' used in key specification without a key length

I then followed the rest of the instructions - Stoppped postgresql & updated config.inc.php and managed to login! woohoo!
I still have a lot more testing to do but the initial thing I found was that the License details didn't come across. Went to the License page and it showed the following

Code: Select all

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

So I'm guessing that I should really start again and delete that nagiosxi table that was created as per the doco ?
What SQL command should I run to achieve this ?

[tgriep]

To delete the database so you can re-import it, you would run the following

Code: Select all

mysql -u root -pnagiosxi -e "drop DATABASE nagiosxi;"

Then you should be able to continue on after this section in the KB Article to re-install the MYSQL database.

Setup the New MySQL Database

[Fred Kroeger]

OK - So I've started from scratch again. While it appears to work - the process still isn't 100%

The mysql file appears to be created OK with all the data included. No errors are displayed when I run the dump.

I still had to remove the quotes around "user" as before. This is the only entry that I had to manually update.

Code: Select all

ALTER TABLE `xi_auditlog` ADD INDEX ( user ) ;

I still get some errors when I import

Code: Select all

# mysql -u nagiosxi -pn@gweb nagiosxi --force < nagiosxi.mysql
ERROR 1068 (42000) at line 5785: Multiple primary key defined
ERROR 1068 (42000) at line 5787: Multiple primary key defined
ERROR 1068 (42000) at line 5789: Multiple primary key defined
ERROR 1068 (42000) at line 5791: Multiple primary key defined
ERROR 1068 (42000) at line 5793: Multiple primary key defined
ERROR 1068 (42000) at line 5795: Multiple primary key defined
ERROR 1068 (42000) at line 5797: Multiple primary key defined
ERROR 1170 (42000) at line 5800: BLOB/TEXT column 'ip_address' used in key specification without a key length
ERROR 1170 (42000) at line 5802: BLOB/TEXT column 'source' used in key specification without a key length
ERROR 1170 (42000) at line 5804: BLOB/TEXT column 'user' used in key specification without a key length

Lines 5785 to 5804 are below

Code: Select all

ALTER TABLE xi_auditlog
    ADD CONSTRAINT xi_auditlog_pkey PRIMARY KEY (auditlog_id);
ALTER TABLE xi_commands
    ADD CONSTRAINT xi_commands_pkey PRIMARY KEY (command_id);
ALTER TABLE xi_events
    ADD CONSTRAINT xi_events_pkey PRIMARY KEY (event_id);
ALTER TABLE xi_meta
    ADD CONSTRAINT xi_meta_pkey PRIMARY KEY (meta_id);
ALTER TABLE xi_options
    ADD CONSTRAINT xi_options_pkey PRIMARY KEY (option_id);
ALTER TABLE xi_sysstat
    ADD CONSTRAINT xi_sysstat_pkey PRIMARY KEY (sysstat_id);
ALTER TABLE xi_usermeta
    ADD CONSTRAINT xi_usermeta_pkey PRIMARY KEY (usermeta_id);
ALTER TABLE xi_users
    ADD CONSTRAINT xi_users_pkey PRIMARY KEY (user_id);
ALTER TABLE `xi_events` ADD INDEX ( event_source ) ;
ALTER TABLE `xi_auditlog` ADD INDEX ( ip_address ) ;
ALTER TABLE `xi_auditlog` ADD INDEX ( log_time ) ;
ALTER TABLE `xi_auditlog` ADD INDEX ( source ) ;
ALTER TABLE `xi_auditlog` ADD INDEX ( type ) ;
ALTER TABLE `xi_auditlog` ADD INDEX ( user ) ;

I can login OK but I have picked up a problem when I try to display License Information. The following message appears at the top of the screen but the page does load successfully

Code: Select all

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

I've looked at a few other screens but without knowing what is affected by migrating the postgresql tables , I'm not sure what needs to be checked?

[tgriep]

Can you run the two commands below and PM me the /tmp/sql.txt file? We should be able to figure out the Duplicate entry in the data.

Code: Select all

echo "desc xi_options;" | mysql -uroot -pnagiosxi nagiosxi -t >/tmp/sql.txt
echo "select * from xi_options;" | mysql -uroot -pnagiosxi nagiosxi -t >>/tmp/sql.txt

[tgriep]

All of the settings for the xi_options table that you sent in look good so the message could be coming from another MYSQL table and it could be the Audit Log.
There were some errors when you imported the data so can you run the following and post the /tmp/sql.txt file?

Code: Select all

echo "desc `xi_auditlog;" | mysql -uroot -pnagiosxi nagiosxi -t >/tmp/sql.txt
echo "select * from `xi_auditlog;" | mysql -uroot -pnagiosxi nagiosxi -t >>/tmp/sql.txt
echo "show create table `xi_auditlog;" | mysql -uroot -pnagiosxi nagiosxi -t >>/tmp/sql.txt