Creating and updating service templates fails with SQL errors

[centreon]

---

Author Name: Moritz Bunkus (Moritz Bunkus)
Original Redmine Issue: 5990, https://forge.centreon.com/issues/5990
Original Date: 2014-11-27

---

Centreon 2.5.3
Centreon Broker 2.6.3

Tested on openSUSE 13.1 with MySQL 5.6.12 and MariaDB 5.5.33 as well
as on Ubuntu 14.04 with MySQL 5.5.40

This is a fresh installation of the aforementioned versions mostly
with default settings. The databases have just been set up via the
web-based setup wizard.

If I try to create a new service template the template is not
saved. sql-error.log shows on openSUSE:

2014-11-14 16:47|0|0|DB Error: unknown error QUERY : INSERT INTO service (service_template_model_stm_id, command_command_id, timeperiod_tp_id, command_command_id2, timeperiod_tp_id2, service_description, service_alias, service_is_volatile, service_max_check_attempts, service_normal_check_interval, service_retry_check_interval, service_active_checks_enabled, service_passive_checks_enabled, service_obsess_over_service, service_check_freshness, service_freshness_threshold, service_event_handler_enabled, service_low_flap_threshold, service_high_flap_threshold, service_flap_detection_enabled, service_process_perf_data, service_retain_status_information, service_retain_nonstatus_information, service_notification_interval, service_notification_options, service_notifications_enabled, contact_additive_inheritance, cg_additive_inheritance, service_inherit_contacts_from_host, service_stalking_options, service_first_notification_delay ,service_comment, command_command_id_arg, command_command_id_arg2, service_register, service_activate) VALUES ( NULL, NULL, NULL, NULL, NULL, 'TemplTest', 'TemplTest', '2', NULL, NULL, NULL, '2', '2', '2', '2', NULL, '2', NULL, NULL, '2', '2', '2', '2', NULL, NULL, '2', 0, 0, 'NULL', NULL, NULL, NULL, NULL, NULL, '0', '1')

On Ubuntu:
2014-11-27 13:47|0|0|DB Error: null value violates not-null constraint QUERY : INSERT INTO service (service_template_model_stm_id, command_command_id, timeperiod_tp_id, command_command_id2, timeperiod_tp_id2, service_description, service_alias, service_is_volatile, service_max_check_attempts, service_normal_check_interval, service_retry_check_interval, service_active_checks_enabled, service_passive_checks_enabled, service_obsess_over_service, service_check_freshness, service_freshness_threshold, service_event_handler_enabled, service_low_flap_threshold, service_high_flap_threshold, service_flap_detection_enabled, service_process_perf_data, service_retain_status_information, service_retain_nonstatus_information, service_notification_interval, service_notification_options, service_notifications_enabled, contact_additive_inheritance, cg_additive_inheritance, service_inherit_contacts_from_host, service_stalking_options, service_first_notification_delay ,service_comment, command_command_id_arg, command_command_id_arg2, service_register, service_activate) VALUES ( NULL, NULL, NULL, NULL, NULL, 'testme-service', 'testme-service', '2', NULL, NULL, NULL, '2', '2', '2', '2', NULL, '2', NULL, NULL, '2', '2', '2', '2', NULL, NULL, '2', 0, 0, 'NULL', NULL, NULL, NULL, NULL, NULL, NULL, NULL)

If I connect to the »centreon« database and execute that command
manually then MySQL reports the following error:

ERROR 1265 (01000): Data truncated for column 'service_inherit_contacts_from_host' at row 1

The error comes from the file
…/centreon/www/include/configuration/configObject/service/DB-Func.php. In
line 850 the »else« case of the ternary operator is wrongfully putting
the SQL keyword NULL into single quotation marks.

The same mistake affects updating existing service templates. Again
DB-Func.php is to blame, this time it's in line 1181.

In order to reproduce:

1. Install Centreon Broker 2.6.3 and Centreon 2.5.3 on a fresh
   openSUSE system using mostly defaults for the answers
2. Run the web-based setup procedure and let it create the databases
3. Login, go to »configuration« ? »services« ? »service templates«
4. Click on »add«. Enter a name and a description. Click on
   »save«. Alternatively edit one of the existing services and save
   it.
5. Look into sql-error.log

[centreon]

---

Original Redmine Comment
Author Name: Andrzej Augustynowicz (Andrzej Augustynowicz)
Original Date: 2015-03-13T13:26:31Z

---

Same on my environment with mysql 5.6.23 (centreon 2.5.4). The problem doesn't exist on mysql 5.5.21. I got only warning message about truncated data.

[centreon]

---

Original Redmine Comment
Author Name: Andrzej Augustynowicz (Andrzej Augustynowicz)
Original Date: 2015-03-16T11:43:38Z

---

You have to switch off strict mode in mysql server.
sql_mode=NO_ENGINE_SUBSTITUTION

Since MySQL 5.6 default setting is STRICT_TRANS_TABLES.

[kduret]

solved by commit 1a674de

[Tpo76]

OK