Page 2 of 3
Re: Postgresql to MySQL migration problems
Posted: Thu Mar 30, 2017 10:57 am
by 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
Re: Postgresql to MySQL migration problems
Posted: Thu Mar 30, 2017 2:51 pm
by tgriep
Well, maybe the OP has a restriction on installing applications on his desktop so the other procedure was available to him to use.
Re: Postgresql to MySQL migration problems
Posted: Thu Mar 30, 2017 7:23 pm
by 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
Re: Postgresql to MySQL migration problems
Posted: Fri Mar 31, 2017 8:45 am
by 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.
Re: Postgresql to MySQL migration problems
Posted: Sun Apr 02, 2017 8:34 pm
by 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
Re: Postgresql to MySQL migration problems
Posted: Mon Apr 03, 2017 9:18 am
by 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.
Re: Postgresql to MySQL migration problems
Posted: Mon Apr 03, 2017 9:16 pm
by 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?
Re: Postgresql to MySQL migration problems
Posted: Tue Apr 04, 2017 8:42 am
by 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
Re: Postgresql to MySQL migration problems
Posted: Mon Apr 10, 2017 7:24 pm
by 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.
Re: Postgresql to MySQL migration problems
Posted: Tue Apr 11, 2017 9:12 am
by 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.