Postgresql to MySQL migration problems

[SteveBeauchemin]

Too bad my process made your eyes glaze over.

Input is PGsql -> Middleman = MySQL WorkBench -> Output is Mysql.
The tool has a native built-in ability to do this.

But carry on, I enjoy reading the posts...

Steve B

[tgriep]

Well, maybe the OP has a restriction on installing applications on his desktop so the other procedure was available to him to use.

[Fred Kroeger]

Thanks for the followup Steve. Yes, unfortunately at some of the sites I look after , I can only VPN in to a Citrix desktop and no apps are allowed to be installed.
So I need a solution that I can use across all sites.

Fred

[tgriep]

The mysql log entries from your PM is showing an optimize problem with the nagios database and not the database that was converted.
Run the following 2 commands to repair the nagios database and also optimize it.

Code: Select all

mysqlcheck -f -r -u root -pnagiosxi --use_frm --databases nagios
mysqlcheck -f -o -u root -pnagiosxi  --databases nagios

Depending on the size of the database's tables, this could take a while.

[Fred Kroeger]

Ran the optimise as suggested on both databases. As per your investigation, the converted database appeared tohave no problems.
I have attached the output of the 1st Optimise run.
I then ran the optimise again on the original database expecting to see no error/warning messages - however there were even more the second time. Is this what you would have expected ?
The 2nd optimise run is also attached.

Fred

[tgriep]

The extra warning and info messages were generated because you ran the repair twice using the --use_frm option.
The USE_FRM option is available for use if the .MYI index file is missing or if its header is corrupted.
This option tells MySQL not to trust the information in the .MYI file header and to re-create it using information from the .frm file.
After running this the first time, the database may fix and remove the errors at a later time so when you ran it the second time, the database wasn't finished with the repair and the optimization and the second run caused the extra warnings.

As long as you are not getting anymore errors, you should be all right.

[Fred Kroeger]

Thanks for the update, when the command exited back to the prompt, I thought that the process had completed. Is this something that needs to be done on a regular basis ?
BTW - What errors should I be looking for?
So now that we have repaired & optimised - we're back to the last question I had about why I get the message "SQL Error [nagiosxi] : Duplicate entry '0' for key 'PRIMARY' " at the top of the screen when I display the license information page?

[tgriep]

I posted this last week.
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

[Fred Kroeger]

I'll PM you the file as it has some identifiable details in it. If anyoune is following this thread, the previous SQL commands have a typo - the single "single quote" should be removed from the command before running it.

Also noticed that the SQL error message was also displayed when I ran the Config Wizard.
When I went to run the commands, discovered that the MySQL database had died again - so something is not stable in this config - even after running all the repairs and the optimise.

[tgriep]

There are a few missing indexes in your xi_autidlog table, run the following as root to add them.

Code: Select all

echo 'alter table xi_auditlog ADD INDEX (`user`) using btree;' |mysql -u root -pnagiosxi nagiosxi
echo 'alter table xi_auditlog ADD INDEX (`ip_address`) using btree;' |mysql -u root -pnagiosxi nagiosxi

Then, run this to delete a table entry that is causing the duplicate entry '0'. If doesn't need to be there.

Code: Select all

echo 'DELETE FROM xi_auditlog WHERE auditlog_id = 0;' | mysql -u root -pnagiosxi nagiosxi

Try that out and let us know if it works.