Nagios MySQL collation.

[jmsanesteban.sgre]

Hi all!

I have a MSSQL database and I have to push some information from the Nagios' MySQL database to the MS one. So I have configured a linked server in MS SQL and I can insert information from Nagios to the MSSQL. The problem appears when I have to update or insert some of the columns and it is related with the collation, so I checked the collation and charset of the database.

I'm using utf8 as the default database charset and utf8_general_ci as the default collation nevertheless, some of the columns have a different value as you can see in the query below

Code: Select all

SELECT TABLE_SCHEMA , TABLE_NAME , COLUMN_NAME , COLLATION_NAME , CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS where collation_name is not null and collation_name not like 'utf8%' order by collation_name ;

# TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME, CHARACTER_SET_NAME
'nagios2', 'nagios_logentries', 'logentry_data', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_scheduleddowntime', 'author_name', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_runtimevariables', 'varvalue', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_runtimevariables', 'varname', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_programstatus', 'global_service_event_handler', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_programstatus', 'global_host_event_handler', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_processevents', 'program_date', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_processevents', 'program_version', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_processevents', 'program_name', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_objects', 'name2', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_objects', 'name1', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_notifications', 'output', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_scheduleddowntime', 'comment_data', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_instances', 'instance_description', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_instances', 'instance_name', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hoststatus', 'check_command', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hoststatus', 'event_handler', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hoststatus', 'perfdata', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hoststatus', 'output', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hosts', 'statusmap_image', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hosts', 'vrml_image', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hosts', 'icon_image_alt', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hosts', 'icon_image', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hosts', 'action_url', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_services', 'notes_url', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_timeperiods', 'alias', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_systemcommands', 'output', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_systemcommands', 'command_line', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_statehistory', 'output', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_servicestatus', 'check_command', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_servicestatus', 'event_handler', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_servicestatus', 'perfdata', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_servicestatus', 'output', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_services', 'icon_image_alt', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_services', 'icon_image', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_services', 'action_url', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hosts', 'notes_url', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_services', 'notes', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_services', 'failure_prediction_options', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_services', 'eventhandler_command_args', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_services', 'check_command_args', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_services', 'display_name', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_servicegroups', 'alias', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_servicechecks', 'perfdata', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_servicechecks', 'output', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_servicechecks', 'command_line', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_servicechecks', 'command_args', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_conninfo', 'disposition', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_customvariables', 'varvalue', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_customvariables', 'varname', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_contacts', 'pager_address', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_contacts', 'email_address', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_contacts', 'alias', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_contactnotificationmethods', 'command_args', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_contactgroups', 'alias', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_contact_notificationcommands', 'command_args', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_contact_addresses', 'address', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_conninfo', 'connect_type', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_conninfo', 'connect_source', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_customvariablestatus', 'varname', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_conninfo', 'agent_version', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_conninfo', 'agent_name', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_configfilevariables', 'varvalue', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_configfilevariables', 'varname', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_configfiles', 'configfile_path', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_comments', 'comment_data', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_comments', 'author_name', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_commenthistory', 'comment_data', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_commenthistory', 'author_name', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_commands', 'command_line', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_acknowledgements', 'comment_data', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hostchecks', 'command_args', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hosts', 'notes', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hosts', 'failure_prediction_options', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hosts', 'eventhandler_command_args', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hosts', 'check_command_args', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hosts', 'address', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hosts', 'display_name', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hosts', 'alias', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hostgroups', 'alias', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hostchecks', 'perfdata', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hostchecks', 'output', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_hostchecks', 'command_line', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_acknowledgements', 'author_name', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_externalcommands', 'command_args', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_externalcommands', 'command_name', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_eventhandlers', 'output', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_eventhandlers', 'command_line', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_eventhandlers', 'command_args', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_downtimehistory', 'comment_data', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_downtimehistory', 'author_name', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_dbversion', 'version', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_dbversion', 'name', 'latin1_swedish_ci', 'latin1'
'nagios2', 'nagios_customvariablestatus', 'varvalue', 'latin1_swedish_ci', 'latin1'

I suppose that this configuration is in the SQL file that nagios uses to generate its databases and tables during the installation, please correct me if I am wrong . So from that point I have two questions:

Is there a real reason to have those columns with a non default configuration? If the response is yes, what is that reason?

Is there a problem if I set to those columns another character set and collation on a live environment? (stopping databases and the application), I would like to use uft8 as charater set and utf8_general_ci as a collation. In that way I could converte it to modern_spanish_ci_as if I want.

Thanks in advance.

BR,
Juanma.

[dchurch]

I'd have to look at your configuration, but I'm not sure that nagios2 is in-use by Nagios. It's not a default database name that gets created when one installs Nagios XI.

What are you using the nagios2 database for?

The reason some of the columns in nagiosql.tbl_info are latin1 instead of utf8 is because the software makes a key out of some of the columns (key1, key2, version, and language Edit: NOT infotext). MySQL assumes utf8 characters are 3 bytes long, so you run into the max key size (1000 bytes iirc) pretty quickly if they're UTF8. All the rest of the columns in all the nagios* databases should utf8 except for those 5.

The reason your nagios2 database got created evidently with most of the tables / columns as latin1 is due to a MySQL default. MySQL may have taken your terminal encoding to be the database encoding. You can configure the default character set for newly-created tables by editing /etc/mysql/mysql.conf.d/mysqld.cnf with the following:

Code: Select all

[mysqld] # <~ find this section, don't add a new one
character-set-server=utf8
collation-server=utf8_general_ci

More info on MySQL character sets can be found here

You can also issue ALTER TABLE commands to change the character set if you want to modify a table in-place.

You can also add COLLATE modifiers to columns when creating tables. More on that here.

[jmsanesteban.sgre]

Hi all!

First of all, thanks for your answer

Nagios2 database is an export from our real Nagios database, I'm working in that way to avoid problems with the real database, because I need to change character set and collation and it is better to test before the changes.

Regarding the configuration of mysqld, it was alredady configured like this. This was one of the reasons I'm writing this, because I couldn't understand why some columns were not configured using the server''s default value .

I've change the character set and collation in Nagios2 to check if it could solve my problem and it does. So with nagios2 database in UTF8 I can achieve my goals. But after reading your answer, I'm getting worried, because I don't want to reach the max key size limit. I need to insert that data into a MSQ SQL Server with modern_spanish_CI_AS collation, so I need to change from latin1_swedish_ci to utf8_general_ci without problems either now or in the future. Should I find another way to push the data without changing the collation of those columns?
Thanks in advance.

BR,
Juanma.

[dchurch]

Apparently the maximum key length of 1000 applies to MyISAM tables, and it's 3072 for InnoDB tables.

You should be able to use a UTF-8 collation on that table without running into the maximum key size limit if you convert it to InnoDB. More on how to do that.

[jmsanesteban.sgre]

Apparently the maximum key length of 1000 applies to MyISAM tables, and it's 3072 for InnoDB tables.

You should be able to use a UTF-8 collation on that table without running into the maximum key size limit if you convert it to InnoDB. More on how to do that.

Thanks for your response.

Based on your experience and knowledgement how easy is to reach that max key limit? Because we need to make those changes, but we need a solid solution for our environments, so maybe we can find out other solution.

BR,
Juanma.

[dchurch]

I made a mistake earlier; the key in the tbl_info table doesn't include the big text field infotext. Running some queries on it, It seems like nothing in the tbl_info table even comes close, the biggest key size being 56 characters. That's an internal table that doesn't receive updates either. Anyhoo, the key consists of varchar(200) * 2 + varchar(50) * 2, which maxes out at 500 characters. Well within key size limit.

The rest of the places the nagiosql schema creates keys seem to be:

• On a set of two int(11)'s
• On a varchar(30)
• On a set of fields that consists of a text(255) and an int(11) (maximum size ~266).

Seems like the constraints are well-designed to keep key size well within tolerances.

[jmsanesteban.sgre]

Hi all!

Ok, that infomation get me more relaxed.

Thank you very much for your response and info.

BR,
Juanma.