Page 1 of 1
Can't resize logentry_data
Posted: Mon Apr 19, 2021 9:50 am
by 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.
Re: Can't resize logentry_data
Posted: Mon Apr 19, 2021 12:06 pm
by 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;'
Re: Can't resize logentry_data
Posted: Tue Apr 27, 2021 12:07 pm
by dxb74
looks like that returns a count of 1,4311,221
Re: Can't resize logentry_data
Posted: Tue Apr 27, 2021 4:08 pm
by 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;
Re: Can't resize logentry_data
Posted: Wed May 05, 2021 4:11 pm
by 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...
Re: Can't resize logentry_data
Posted: Thu May 06, 2021 1:35 pm
by 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
Re: Can't resize logentry_data
Posted: Tue May 11, 2021 11:43 am
by dxb74
Thanks all. I'm just gong to drop the index from that column. Appreciate the guidance.
Re: Can't resize logentry_data
Posted: Tue May 11, 2021 12:08 pm
by vtrac
Great!! ..... locking/closing post ...
