Anti-truncate method questions

[WillemDH]

Hello,

I saw in another thread http://support.nagios.com/forum/viewtop ... 16&t=27883 a way to make Nagios not truncate the output of service status information:

Code: Select all

echo "use nagios;alter table nagios_servicestatus modify output varchar(65535) not null;alter table nagios_servicestatus modify long_output varchar(65535) not null;alter table nagios_servicestatus modify perfdata varchar(65535) not null;" | mysql -pnagiosxi

    echo "use nagios;alter table nagios_hoststatus modify output varchar(65535) not null;alter table nagios_servicestatus modify long_output varchar(65535) not null;alter table nagios_servicestatus modify perfdata varchar(65535) not null;" | mysql -pnagiosxi

    echo "use nagios;alter table nagios_servicechecks modify output varchar(65535) not null;alter table nagios_servicestatus modify long_output varchar(65535) not null;alter table nagios_servicestatus modify perfdata varchar(65535) not null;" | mysql -pnagiosxi

    echo "use nagios;alter table nagios_hostchecks modify output varchar(65535) not null;alter table nagios_servicestatus modify long_output varchar(65535) not null;alter table nagios_servicestatus modify perfdata varchar(65535) not null;" | mysql -pnagiosxi

Is this something permanent? Is this something that would need to be redone after an upgrade? Does it has other implications? So is 65535 the number of allowed characters? What is the default number of allowed characters?

Grtz

Willem

[tmcdonald]

I can ask a developer to weigh in on this, but from my understanding there is no reason to overwrite the databases on an upgrade so it should be permanent. If you roll back to a backup from before this was done however it will need to be redone as the database *will* be overwritten on a backup restore.

As for implications, it might make your database grow larger, and some places might not expect the 65,535 characters so could either cut them off or possibly segfault if it is a binary. Considering it was not a developer who suggested that fix, we cannot know fully what the implications may be until a dev does look at it.

[scottwilkerson]

This would not need to be changed between upgrades, it would stick.

Other implications could be a slight db performance decrease when searching in these fields. somewhat negligible as you would never find the info searched for if it was truncated...

Yes, 65535 would become the limit for each of these fields.

The default was 255.


I will say, however, there is a bug in the SQL that was posted, and it should read:

Code: Select all

    echo "use nagios;alter table nagios_servicestatus modify output varchar(65535) not null;alter table nagios_servicestatus modify long_output varchar(65535) not null;alter table nagios_servicestatus modify perfdata varchar(65535) not null;" | mysql -pnagiosxi

        echo "use nagios;alter table nagios_hoststatus modify output varchar(65535) not null;alter table nagios_hoststatus modify long_output varchar(65535) not null;alter table nagios_hoststatus modify perfdata varchar(65535) not null;" | mysql -pnagiosxi

        echo "use nagios;alter table nagios_servicechecks modify output varchar(65535) not null;alter table nagios_servicechecks modify long_output varchar(65535) not null;alter table nagios_servicechecks modify perfdata varchar(65535) not null;" | mysql -pnagiosxi

        echo "use nagios;alter table nagios_hostchecks modify output varchar(65535) not null;alter table nagios_hostchecks modify long_output varchar(65535) not null;alter table nagios_hostchecks modify perfdata varchar(65535) not null;" | mysql -pnagiosxi

Or slightly cleaner

Code: Select all

echo "
alter table nagios_servicestatus modify output varchar(65535) not null,modify long_output varchar(65535) not null,modify perfdata varchar(65535) not null;
alter table nagios_hoststatus modify output varchar(65535) not null, modify long_output varchar(65535) not null,modify perfdata varchar(65535) not null;
alter table nagios_servicechecks modify output varchar(65535) not null,modify long_output varchar(65535) not null,modify perfdata varchar(65535) not null;
alter table nagios_hostchecks modify output varchar(65535) not null,modify long_output varchar(65535) not null,modify perfdata varchar(65535) not null;
" | mysql -pnagiosxi nagios

[WillemDH]

So, if I would want to go back to the old situation I would just need to do

Code: Select all

echo "
alter table nagios_servicestatus modify output varchar(255) not null,modify long_output varchar(65535) not null,modify perfdata varchar(255) not null;
alter table nagios_hoststatus modify output varchar(255) not null, modify long_output varchar(65535) not null,modify perfdata varchar(255) not null;
alter table nagios_servicechecks modify output varchar(255) not null,modify long_output varchar(65535) not null,modify perfdata varchar(255) not null;
alter table nagios_hostchecks modify output varchar(255) not null,modify long_output varchar(65535) not null,modify perfdata varchar(255) not null;
" | mysql -pnagiosxi nagios

Correct? Or is there no way back?

I'm asking this as I have several checks which output is truncated and this would be a nice solution, but I'm a total mysql noob, so I'm kind of reluctant to edit this.

Grtz

Willem

[tmcdonald]

StackOverflow to the rescue:

http://stackoverflow.com/questions/6920 ... -when-ille
http://stackoverflow.com/questions/1279 ... ysql-table
http://stackoverflow.com/questions/1018 ... se-without

Short answer: Any data over 255 chars should be truncated safely.

[WillemDH]

This works fine. Thread can be closed.