Ask Your Question
1

puppet-dashboard - Mysql::Error: Duplicate entry '2147483647'

asked 2014-02-20 14:06:43 -0500

suppldp2001 gravatar image

Hi,

I am receiving above message when trying to process puppet reports in the dashboard. The id field in the resource_statuses tables hit the integer limit. Which the best approach to deal with this?

Is it possible to truncate the tables (resourcestatuses and resourceevents)? Or a better approach is to change the id field to bigint?

Any advice will be welcome.

Thanks.-

edit retag flag offensive close merge delete

3 Answers

Sort by ยป oldest newest most voted
2

answered 2014-02-26 08:33:10 -0500

suppldp2001 gravatar image

updated 2014-02-26 11:14:11 -0500

ramindk gravatar image

Thank you @ramindk and @jonn. Here is the script I run in the MySQL database:

use `puppet_dashboard`;
rename table resource_events to resource_events_old;
rename table resource_statuses to resource_statuses_old;

CREATE TABLE `resource_statuses` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `report_id` int(11) NOT NULL,
  `resource_type` varchar(255) DEFAULT NULL,
  `title` text,
  `evaluation_time` decimal(12,6) DEFAULT NULL,
  `file` text,
  `line` int(11) DEFAULT NULL,
  `tags` text,
  `time` datetime DEFAULT NULL,
  `change_count` int(11) DEFAULT NULL,
  `out_of_sync_count` int(11) DEFAULT NULL,
  `skipped` tinyint(1) DEFAULT NULL,
  `failed` tinyint(1) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_resource_statuses_on_report_id_2` (`report_id`),
  CONSTRAINT `fk_resource_statuses_report_id_2 ...
(more)
edit flag offensive delete link more

Comments

Nice and thanks for posting what you used.

ramindk gravatar imageramindk ( 2014-02-26 11:14:33 -0500 )edit
1

answered 2014-02-24 02:03:46 -0500

jonn gravatar image

You'll want to add a cron job to prune the reporting tables. This link explains how.

Also, @ramindk posted an answer with a good overview of how to deal with a report set that has already got too big.

edit flag offensive delete link more
0

answered 2014-02-24 02:37:34 -0500

ramindk gravatar image

I don't believe the solution @jonn proposes will work in this case. Even if you delete old records, and I imagine you are or your DB would be truly huge, you'll still have an id problem. Below is what the table looks like.

show create table resource_statuses;

| resource_statuses | CREATE TABLE `resource_statuses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `report_id` int(11) NOT NULL,
  `resource_type` varchar(255) DEFAULT NULL,
  `title` text,
  `evaluation_time` decimal(12,6) DEFAULT NULL,
  `file` text,
  `line` int(11) DEFAULT NULL,
  `tags` text,
  `time` datetime DEFAULT NULL,
  `change_count` int(11) DEFAULT NULL,
  `out_of_sync_count` int(11) DEFAULT NULL ...
(more)
edit flag offensive delete link more

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account.

Add Answer

Question Tools

1 follower

Stats

Asked: 2014-02-20 14:06:43 -0500

Seen: 698 times

Last updated: Feb 26 '14