The Puppet Labs Issue Tracker has Moved: https://tickets.puppetlabs.com

Bug #9225

Stored configs db hits integer limit

Added by Jason Rojas over 2 years ago. Updated 4 months ago.

Status:AcceptedStart date:08/26/2011
Priority:HighDue date:
Assignee:-% Done:

0%

Category:stored configuration
Target version:-
Affected Puppet version:2.6.9 Branch:
Keywords:bigint database schema

We've Moved!

Ticket tracking is now hosted in JIRA: https://tickets.puppetlabs.com

This ticket is now tracked at: https://tickets.puppetlabs.com/browse/PUP-1173


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.


Related issues

Related to Puppet Dashboard - Bug #14376: `resource_statuses`.`id` should be BIGINT Closed 05/09/2012

History

#1 Updated by Jason Rojas over 2 years ago

Damn formatting:

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

#2 Updated by James Turnbull over 2 years 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 over 2 years 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 over 2 years 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 about 2 years 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 2 years 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 2 years 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!

#8 Updated by Jacob McCann almost 2 years ago

Just hit this again today. So for us it looks like we’ll hit it roughly every 3 months. :( Probably it’ll speed up too as we keep getting more systems.

Thanks!

#9 Updated by Ken Barber almost 2 years ago

Just to be very clear here – this ticket is in relation to id’s wrapping in stored configurations ONLY – not the console. If you are having this problem in the console, the redmine ID is #14376.

#10 Updated by Jacob McCann over 1 year ago

Happened again today. :( So every 2 months about …

#11 Updated by Jason Rojas over 1 year ago

Jacob McCann wrote:

Happened again today. :( So every 2 months about …

I don’t think this was ever fixed, I dumped my database schema and converted all INT columns to BIGINT before reloading the schema. I have been doing this for each puppet version that has any database changes really a PITA, luckily the DB schema for stored configs doesn’t seem to change much.

It looks like stored configs are being deprecated in newer versions of puppet in favor of puppetdb (postgresql doesnt have this issue afaik) and since this ticket isn’t assigned to anyone I’m not sure if it’s being considered relevant since 3.0 is the current go-to for most.

#12 Updated by Joe McDonagh over 1 year ago

just hit this on the param_values table… to be clear, the process here is:

  1. Stop puppet on all nodes
  2. Modify schema.rb so all :integer is :biginteger (not sure if this is necessary?)
  3. dump db, change all int() to bigint()
  4. restore
  5. start puppet back up

can someone confirm?

#13 Updated by Andrew Parker over 1 year ago

  • Target version deleted (2.7.x)

#14 Updated by Anonymous 4 months ago

Redmine Issue #9225 has been migrated to JIRA:

https://tickets.puppetlabs.com/browse/PUP-1173

Also available in: Atom PDF