DB error

[pnewlon]

We are getting thousands of these errors in our messages log - 800MB since last night. I searched the forums found where it was recommended to run the 'repairmysql' script. Still getting the same errors. Not disk space issues though I have to keep clearing the messages log to make sure that we don't run into one.

Not sure what to do at this point.....



Aug 2 11:47:18 lpnagv01 ndo2db: mysql_error: 'Duplicate entry '2147483647' for key 1'
Aug 2 11:47:18 lpnagv01 ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_timedeventqueue SET instance_id='1', event_type='0', queued_time=FROM_UNIXTIME(1375458363), queued_time_usec='400903', scheduled_time=FROM_UNIXTIME(1375458615), recurring_event='0', object_id='917''

http://support.nagios.com/forum/viewtop ... IME#p36753

-rw------- 1 root root 816963234 Aug 2 11:59 messages

Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
8.6G 6.0G 2.3G 73% /
/dev/sda1 99M 18M 76M 20% /boot
tmpfs 1.5G 0 1.5G 0% /dev/shm
/dev/mapper/VolGroup00-LV02
9.9G 7.4G 2.1G 79% /usr/local
/dev/mapper/VolGroup00-LV03
9.9G 7.7G 1.7G 82% /store
tmpfs 50M 19M 32M 38% /usr/local/ramdisk

[scottwilkerson]

This can happen if the auto_increment for timedeventqueue_id in nagios_timedeventqueue has hit the limit for int

Run the following to resolve this issue

Code: Select all

echo "ALTER TABLE nagios_timedeventqueue MODIFY timedeventqueue_id SERIAL;"|mysql -pnagiosxi nagios

[pnewlon]

I did that, got same result. Reran repairmysql, still same result. Interesting that the error is always the same number - 2,324,513 since last night around 4pm EDT.

Aug 1 17:14:24 lpnagv01 ndo2db: mysql_error: 'Duplicate entry '2147483647' for key 1'
Aug 1 17:14:24 lpnagv01 ndo2db: mysql_error: 'Duplicate entry '2147483647' for key 1'

[pnewlon]

Does this look right to you? `timedeventqueue_id` bigint(20) unsigned NOT NULL auto_increment



[root@lpnagv01 log]# echo "ALTER TABLE nagios_timedeventqueue MODIFY timedeventqueue_id SERIAL;"|mysql -pnagiosxi nagios


[root@lpnagv01 log]# echo "SHOW CREATE TABLE nagios_timedeventqueue;"|mysql -pnagiosxi nagios
Table Create Table
nagios_timedeventqueue CREATE TABLE `nagios_timedeventqueue` (\n `timedeventqueue_id` bigint(20) unsigned NOT NULL auto_increment,\n `instance_id` smallint(6) NOT NULL default '0',\n `event_type` smallint(6) NOT NULL default '0',\n `queued_time` datetime NOT NULL default '0000-00-00 00:00:00',\n `queued_time_usec` int(11) NOT NULL default '0',\n `scheduled_time` datetime NOT NULL default '0000-00-00 00:00:00',\n `recurring_event` smallint(6) NOT NULL default '0',\n `object_id` int(11) NOT NULL default '0',\n PRIMARY KEY (`timedeventqueue_id`),\n UNIQUE KEY `timedeventqueue_id` (`timedeventqueue_id`),\n UNIQUE KEY `timedeventqueue_id_2` (`timedeventqueue_id`),\n KEY `instance_id` (`instance_id`),\n KEY `event_type` (`event_type`),\n KEY `scheduled_time` (`scheduled_time`),\n KEY `object_id` (`object_id`),\n KEY `queued_time` (`queued_time`)\n) ENGINE=MyISAM AUTO_INCREMENT=452274466 DEFAULT CHARSET=utf8 COMMENT='Current Nagios event queue'

[pnewlon]

Nevermind - went to Google University and learned "SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE."

[lmiltchev]

It's hard to imagine a world without "Google University"...

[pnewlon]

It's hard to imagine a world without "Google University"...

Isn't that the truth?! I wonder if there's an app for that.....

[scottwilkerson]

Unlocked by user request.

Did this not resolve the problem? What errors are you receiving now that it is changed to bigint?

[pnewlon]

Post right after your 'alter table....' post

I did that, got same result. Reran repairmysql, still same result. Interesting that the error is always the same number - 2,324,513 since last night around 4pm EDT.

Aug 1 17:14:24 lpnagv01 ndo2db: mysql_error: 'Duplicate entry '2147483647' for key 1'
Aug 1 17:14:24 lpnagv01 ndo2db: mysql_error: 'Duplicate entry '2147483647' for key 1'

[scottwilkerson]

So the error is still in nagios_timedeventqueue..

Can you run

Code: Select all

echo "DESCRIBE nagios_timedeventqueue;"|mysql -pnagiosxi nagios

the reason it is the same number is because that is the max size of the INT field you would have had before making the change