Postgresql to MySQL migration problems

[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

[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.

[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

[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.

[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.

[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

[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 ?

[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

[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?

[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.