Data too long for column 'check_command'

[sethmay]

We were working through problems with StatusEngine not picking up a number of our Nagios services and ran into a number of log entries like the following:

Data too long for column 'check_command' at row X

Seen in both syslog and when running "/opt/statusengine/worker/bin/StatusengineWorker.php" directly.

To fix this, we increased the size of the "check_command" columns in both "statusengine_servicestatus" and "statusengine_hoststatus" from VARCHAR(255) to VARCHAR(512). Once we made the change, the errors disappeared and the missing services appeared.

This may be a change that should be made to to database SQL file.

[nook24]

Thanks for reporting this.

I think as long as this field is using a VARCHAR, it’s hard to define a default length that fits for all users.
Unfortunately I hadn’t time to benchmark TEXT vs VARCHAR but if I remember right a TEXT column will force MySQL to create tmp tables on disk because the memory storage engine don’t support type TEXT.

I also want to update the MySQL schema to adding better indices and a primary key to some columns. But that has a low priority for me right now so I don’t know when I find the time for all the required bench marking.

I guess for now it’s the best if you adjust the length to fit your needs.

[sethmay]

Got it. And you're right: VARCHAR is stored on-row and TEXT is stored off-row (and may need a disk read to fetch), so potentially a factor on performance. Often you'll just pick a VARCHAR length that should reasonably never be exceeded, in these cases that seems to be a pretty trial and error process. At least with this issue report, anyone else should be able to find our fix.