Bug #9225

Stored configs db hits integer limit

Added by Jason Rojas 9 months ago. Updated 6 days ago.

Status:Accepted Start date:08/26/2011
Priority:High Due date:
Assignee:- % Done:

0%
Category:stored configuration
Target version:2.7.x
Affected Puppet version:2.6.9 Branch:
Keywords:bigint database schema
Votes: 4

Description

It looks like the default schema for puppet’s stored configs (lib/puppet/rails/database/schema.rb) sets id columns as int(11) vs using bigint. In my largest puppet environment, I noticed this:

Thu Aug 25 15:32:40 -0400 2011 Puppet (err): Mysql::Error: Duplicate entry ‘2147483647’ for key 1: INSERT INTO fact_values (created_at, updated_at, value, fact_name_id, host_id) VALUES(‘2011-08-25 15:32:40’, ‘2011-08-25 15:32:40’, ‘10.6.0.111’, 67, 1716)

2147483647=231 which is the 32bit integer limit.

Which resulted in these logs on all of my clients in that environment:

Aug 26 13:47:26 kickstart-001 puppet-agent[27506]: Could not retrieve catalog from remote server: Error 400 on SERVER: Mysql::Error: Duplicate entry ‘2147483647’ for key 1: INSERT INTO fact_values (created_at, updated_at, value, fact_name_id, host_id) VALUES(‘2011-08-26 13:47:26’, ‘2011-08-26 13:47:26’, ‘10.6.0.104’, 67, 1183)

Updating the database schema for stored configs to use bigint for the id fields vs int, will help auto increment avoid the issue.

I checked the code for 2.7 (since I am running 2.6.9) and it seems to be the same there as well, this could potentially affect the dashboard as well.

My immediate work around was to truncate fact_values and set AUTO_INCREMENT=1; But this only will band-aid it until it reaches that point again.

History

#1
Updated by Jason Rojas 9 months ago

Damn formatting:

2147483647=2 ^ 31 which is the 32bit integer limit.

#2
Updated by James Turnbull 9 months ago

  • Status changed from Unreviewed to Needs Decision
  • Assignee set to Nigel Kersten

Jason – any chance of a patch BTW?

#3
Updated by Nigel Kersten 9 months ago

  • Status changed from Needs Decision to Accepted
  • Assignee deleted (Nigel Kersten)
  • Priority changed from Normal to High
  • Target version set to 2.7.x

#4
Updated by Jason Rojas 9 months ago

James, I do not have a patch, from my readings, the closest I can find to patches are from google:

http://moeffju.net/blog/using-bigint-columns-in-rails-migrations

It looks like one other option would be to create a migration instead of modifying the schema.rb.

#5
Updated by Steven Seed 3 months ago

I am having this problem currently in dashboard. It started a couple of days ago and now all of my client reports are failing with errors similar to this:

Importing report report-3524-4646.yaml at 2012-02-13 00:40 PST

Mysql::Error: Duplicate entry '2147483647' for key 'PRIMARY': INSERT INTO `resource_statuses` (`failed`, `resource_type`, `title`, `line`, `change_count`, `time`, `out_of_sync_count`, `evaluation_time`, `tags`, `report_id`, `file`, `skipped`, `status`) VALUES(0, 'Exec', 'line_absent_remove_orig_prelink_disable_exec-sheild', NULL, 0, '2012-02-13 08:39:58', 0, 0.066588, '--- \n- exec\n- line_absent_remove_orig_prelink_disable_exec-sheild\n- line\n- remove_orig_prelink_disable_exec-sheild\n- class\n- basebuild::config\n- basebuild\n- config\n- baseclass\n- node\n- default\n- nopopups\n', 14163833, NULL, 0, 'unchanged')

I tried to run the optimize on my database, but this doesn’t seem to have helped.

#6
Updated by Jacob McCann about 1 month ago

This started occurring for me this morning. :(

Apr  5 08:24:28 puppetmaster puppet-master[30094]: Mysql::Error: Duplicate entry '2147483647' for key 1: INSERT INTO `fact_values` (`value`, `fact_name_id`, `host_id`, `updated_at`, `created_at`) VALUES ('Linux', 24, 219, '2012-04-05 08:24:28', '2012-04-05 08:24:28')
Apr  5 08:24:35 puppetmaster puppet-master[30094]: Mysql::Error: Duplicate entry '2147483647' for key 1: INSERT INTO `fact_values` (`value`, `fact_name_id`, `host_id`, `updated_at`, `created_at`) VALUES ('Linux', 24, 239, '2012-04-05 08:24:35', '2012-04-05 08:24:35')

#7
Updated by Jacob McCann about 1 month ago

So I followed the advice of the bug creator to band-aid this. Here is what I did for others who are not mysql literate (not that I am):

use puppet;
truncate table fact_values;
alter table fact_values AUTO_INCREMENT=1;

The alter query to modify the AUTO_INCREMENT timed out for me though, however it seems the table did start back at 1. So basically not sure if the alter query is needed or not.

Thanks!

Also available in: Atom PDF