Page 3 of 3
Re: Postgresql to MySQL migration problems
Posted: Tue Apr 11, 2017 6:46 pm
by Fred Kroeger
Not having much joy here.....
Code: Select all
# echo 'alter table xi_auditlog ADD INDEX (`user`) using btree;' |mysql -u root -pnagiosxi nagiosxi
ERROR 1170 (42000) at line 1: BLOB/TEXT column 'user' used in key specification without a key length
# echo 'alter table xi_auditlog ADD INDEX (`ip_address`) using btree;' |mysql -u root -pnagiosxi nagiosxi
ERROR 1170 (42000) at line 1: BLOB/TEXT column 'ip_address' used in key specification without a key length
Re: Postgresql to MySQL migration problems
Posted: Wed Apr 12, 2017 10:25 am
by tgriep
The table type for those entries are setup as test and should be VARCHAR. Run the following to convert them to VARCHAR and then add the indexes.
Code: Select all
echo 'alter table xi_auditlog modify user VARCHAR(200);' |mysql -u root -pnagiosxi nagiosxi
echo 'alter table xi_auditlog modify ip_address VARCHAR(45);' |mysql -u root -pnagiosxi nagiosxi
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
Let us know how it works out.
Re: Postgresql to MySQL migration problems
Posted: Wed Apr 12, 2017 7:22 pm
by Fred Kroeger
OK - that's done now. What next ?
I checked the License Information screen and that SQL error is still appearing at the top.
Code: Select all
SQL Error [nagiosxi] : Duplicate entry '0' for key 'PRIMARY'
I checked the mysql log and it shows that the logentries table has crashed again - so I'm running the database repair script again.
This table is extremely large - Data records: 10,293,130. My Prod server has double this amount - 22,116,808
Is this normal ?
regards... Fred
Re: Postgresql to MySQL migration problems
Posted: Thu Apr 13, 2017 8:34 am
by tgriep
The problem is that one of the tables didn't get converted over correctly and that is causing the SQL error.
I think it is the Auditlog table and it needs to be cleared out as the dump you sent in earlier, has a entry with a primary key set to 0.
So, lets truncate that table and see if that removes all of the entries and clears out the error. Run the following to do that.
Code: Select all
echo 'TRUNCATE TABLE xi_auditlog;' | mysql -u root -pnagiosxi nagiosxi
The logentries table stores all on the output for the checks, a system with a lot of checks, generates a lot of entries.
You can change the amount of entries the system keeps by logging into the XI GUI and going to the Admin > Performance Settings menu, and decrease the Max Log Entries settings to a smaller value.
Re: Postgresql to MySQL migration problems
Posted: Mon Apr 17, 2017 7:10 pm
by Fred Kroeger
SQL error was showing before I ran the truncate.
Message disappeared after running the truncate. I will keep monitoring this.
In relation to the logentries table - it was showing crashed again this morning. I'm not really concerned about the number of records I'm keeping - more so that it does have a large number and the fact that it keeps crashing.
Re: Postgresql to MySQL migration problems
Posted: Tue Apr 18, 2017 9:41 am
by tgriep
Lets take a look at the settings for the logentries table.
Run the following and post the output.
Code: Select all
echo 'desc nagios_logentries;' |mysql -u root -pnagiosxi nagios -t
Also, can you look in the log files when the corruption starts and see what caused it and post the error?
That may give us a clue on why the error is coming back. I suspect that the logentry_data table is not big enough for the info returned from one of your checks.
If you want to increase the table and see if that fixes the issue, run the following command as root.
Code: Select all
echo "alter table nagios_logentries modify logentry_data varchar(65535) not null;" | mysql -pnagiosxi nagios
Re: Postgresql to MySQL migration problems
Posted: Fri Apr 21, 2017 2:14 am
by Fred Kroeger
Hmmm... a watched table never crashes!
So far no problems since the last repair.
Code: Select all
+-------------------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+--------------+------+-----+---------------------+----------------+
| logentry_id | int(11) | NO | PRI | NULL | auto_increment |
| instance_id | int(11) | NO | MUL | 0 | |
| logentry_time | datetime | NO | MUL | 0000-00-00 00:00:00 | |
| entry_time | datetime | NO | | 0000-00-00 00:00:00 | |
| entry_time_usec | int(11) | NO | | 0 | |
| logentry_type | int(11) | NO | | 0 | |
| logentry_data | varchar(255) | NO | MUL | | |
| realtime_data | smallint(6) | NO | | 0 | |
| inferred_data_extracted | smallint(6) | NO | | 0 | |
+-------------------------+--------------+------+-----+---------------------+----------------+
Tried to update the field size but it fails
Code: Select all
# echo "alter table nagios_logentries modify logentry_data varchar(65535) not null;" | mysql -pnagiosxi nagios
ERROR 1170 (42000) at line 1: BLOB/TEXT column 'logentry_data' used in key specification without a key length
I'm presuming that it doesn't pre-allocate all this extra space as the table has almost 23million records ?
Re: Postgresql to MySQL migration problems
Posted: Fri Apr 21, 2017 8:57 am
by tgriep
No, it shouldn't pre-allocate the space. Try running this and see if this increases the size of the table.
Code: Select all
echo "alter table nagios_logentries modify logentry_data varchar(65535) default null;" | mysql -pnagiosxi nagios
Re: Postgresql to MySQL migration problems
Posted: Sun Apr 23, 2017 5:38 pm
by Fred Kroeger
Command still isn't working - same error as before
Code: Select all
echo "alter table nagios_logentries modify logentry_data varchar(65535) default null;" | mysql -pnagiosxi nagios
ERROR 1170 (42000) at line 1: BLOB/TEXT column 'logentry_data' used in key specification without a key length
BTW - I notice that you define the varchar size 1 byte less than the full size ( 64 * 1024 = 65536). It''s just that I increased the size of the service perfdata & output columns some time ago to 2048 as the default 255 meant that records were being truncated. Should I have made them 2047 instead?
Re: Postgresql to MySQL migration problems
Posted: Mon Apr 24, 2017 8:46 am
by tgriep
The reason the size is set one byte smaller is so MYSQL will not convert that entry to the next larger size of the table type.
Since the table is not crashing, lets leave it alone as for some reason it cannot be increased.