"Out of range value for column 'notification_number' at row 1" error after an upgrade or an update

  • 16 January 2023
  • 8 replies
  • 1543 views
Sébastien
Rank
Userlevel 4
Badge +6

Symptoms
 

  • After an update or an upgrade (to 22.04 or 22.10), poller warns as follows:
  • And the broker logs returns errors as follows: 
    [2022-08-22T15:30:40.917+02:00] [sql] [error] mysql_connection: could not store service:  Out of range value for column 'notification_number' at row 1
    [2022-08-22T15:30:40.917+02:00] [core] [error] failover: global error: could not store service:  Out of range value for column 'notification_number' at row 1
    ​​​​​​

Problem

 

When a host or service has a non-ok status for a while, centreon-broker keeps incrementing the notification_number until it reaches 32767, which is a maximum value.

 

Solution

 

You need to change the type of the column to allow centreon-broker to insert larger values.

  1. Log into your terminal and connect to MySQL:
    mysql -udatabase_user -p
  1. Run these queries: 
    UPDATE centreon_storage.hosts SET notification_number=0 WHERE notification_number < 0;
    UPDATE centreon_storage.services SET notification_number=0 WHERE notification_number < 0;

    ALTER TABLE centreon_storage.hosts MODIFY notification_number bigint(20) unsigned DEFAULT NULL;
    ALTER TABLE centreon_storage.services MODIFY notification_number bigint(20) unsigned DEFAULT NULL;
  1. After 20 minutes, check the last update in the Centreon interface in the Configuration > Pollers menu.

 

If it is still warning

  • Check the /var/log/centreon-broker/central-broker-master.log file.

 

If the error still appears

  • Restart cbd on the central:
    systemctl restart cbd

 

See also

Sébastien 1 year ago

FYI :

If the error persists, you can try with this type :

ALTER TABLE centreon_storage.services MODIFY notification_number bigint(20) DEFAULT NULL;
ALTER TABLE centreon_storage.hosts MODIFY notification_number bigint(20) DEFAULT NULL;

The unsigned type could not be recognized by mariadb. 

View original

8 replies

W
Userlevel 1
Badge +4

Hello,

After following the solution, I still have the same problem. I just updated centreon from version 22.04.0 to 22.04.10

However, the changes have been made on the database. I also restarted the cbd service

can anyone help me to dig deeper into the problem ?

Sébastien
Rank
Userlevel 4
Badge +6

Hello @Wisonic 

Can you run this query in centreon_storage ?

select host_id,name,alias,notification_number from hosts order by notification_number DESC limit 5\G

W
Userlevel 1
Badge +4

Hello @Sébastien

Thank you for your help !

MariaDB [centreon_storage]> select host_id,name,alias,notification_number from hosts order by notification_number DESC limit 5\G
*************************** 1. row ***************************
            host_id: 971
               name: X1
              alias: X1
notification_number: 32595
*************************** 2. row ***************************
            host_id: 84
               name: X2
              alias: X2
notification_number: 32205
*************************** 3. row ***************************
            host_id: 1147
               name: X3
              alias: X3
notification_number: 31507
*************************** 4. row ***************************
            host_id: 557
               name: X4
              alias: X4
notification_number: 31460
*************************** 5. row ***************************
            host_id: 810
               name: X5
              alias: X5
notification_number: 31296
Sébastien
Rank
Userlevel 4
Badge +6

Hello @Wisonic 

It should be fixed for :

  • centreon-broker-22.10.1
  • centreon-broker-22.04.2

https://docs.centreon.com/docs/22.04/releases/centreon-core/#22042-1

Improved Broker's behaviour when trying to insert negative values in unsigned columns of the storage database.

Sébastien
Rank
Userlevel 4
Badge +6

FYI :

If the error persists, you can try with this type :

ALTER TABLE centreon_storage.services MODIFY notification_number bigint(20) DEFAULT NULL;
ALTER TABLE centreon_storage.hosts MODIFY notification_number bigint(20) DEFAULT NULL;

The unsigned type could not be recognized by mariadb. 

G
Badge +2

Hi,

I’m using Centreon version 21.10.14, the OS is Centos 7 and the database MariaDB. I keep seeing these error messages in our logs despite both columns have being resized to bigint(20) unsigned. 

Is there anything else we can do to get rid of them?

Regards,

LM

Sébastien
Rank
Userlevel 4
Badge +6

Hi,

I’m using Centreon version 21.10.14, the OS is Centos 7 and the database MariaDB. I keep seeing these error messages in our logs despite both columns have being resized to bigint(20) unsigned. 

Is there anything else we can do to get rid of them?

Regards,

LM

Hello @gianola ,

 

Old mysql versions doesn’t support “unsigned”, have you tried the 2nd solution pinned?

G
Badge +2

Hello @gianola ,

 

Old mysql versions doesn’t support “unsigned”, have you tried the 2nd solution pinned?


Hi,

I haven’t noticed the 2nd solution, we are now testing it.

 

Is it possible that this issue prevented our Centreon to show information in the web from time to time as explained in the thread below? I was focusing on PHP configuration and it seems quite strange to me that the issue comes from the database.

 



Thank you!
LM.
 

Reply


Terms & ConditionsCookie settings