Can't resize logentry_data

[dxb74]

I am trying to increase the size of the logentry_data column in nagios_logentries.

Our Nagios installation is a manual install on Linux 7.6 servers. Nagios version is 5.6.14.

When I do an alter table nagios_logentries modify column logentry_data varchar(4000) I get: ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

I have tried smaller sizes but get the same error on anything bigger than about 330.

[dchurch]

There is an index on that field (nagios_logentries.logentry_data). The reason as to why eludes me; database indexes are trade-offs and due to the sheer number of records that tend to be in that table, I'm not sure why there's an index on what appears to be a free-form text column.

Before I advise you to drop that index, I'd like to know how many records are in your table. Please run the following command:

Code: Select all

mysql -undoutils -pn@gweb nagios <<< 'select count(*) from nagios_logentries;'

[dxb74]

looks like that returns a count of 1,4311,221

[dchurch]

Dev informs me the reason there's an index on that column is for performance on a log event search page. If you're not going to use that page, it's safe to drop that index:

Code: Select all

alter table nagios_logentries drop index logentry_data;

[dxb74]

Not much of a database guy here. Does that mean I have to choose between having that field indexed and being able to resize it past it's current character limit? Or can I drop the index, resize it, and then turn indexing back on? I can see potentially wanting to search the log event page at some point in the future for troubleshooting purposes...

[vtrac]

Hi,
How are you doing?

When I do an alter table nagios_logentries modify column logentry_data varchar(4000) I get: ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

I have tried smaller sizes but get the same error on anything bigger than about 330.

I'm not a DBA, but look like the max you go alter is "333"
Here's how ... "nagios_logentries.logentry_data" has the size of (333 * 3) = 999, which falls in the max key length (range) of 1,000 bytes limits as MySQL stores utf8 encoded chars as 3 bytes.

Hope this helps!!

Regards,
Vinh

[dxb74]

Thanks all. I'm just gong to drop the index from that column. Appreciate the guidance.

[vtrac]

Great!! ..... locking/closing post ...