The Puppet Labs Issue Tracker has Moved: https://tickets.puppetlabs.com
Stored configs db hits integer limit
|Affected Puppet version:||2.6.9||Branch:|
|Keywords:||bigint database schema|
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
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: Could not retrieve catalog from remote server: Error 400 on SERVER: Mysql::Error: Duplicate entry ‘2147483647’ for key 1: INSERT INTO
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.
#4 Updated by Jason Rojas over 3 years ago
James, I do not have a patch, from my readings, the closest I can find to patches are from google:
It looks like one other option would be to create a migration instead of modifying the schema.rb.
#5 Updated by Steven Seed about 3 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 almost 3 years ago
This started occurring for me this morning. :(
Apr 5 08:24:28 puppetmaster puppet-master: 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: 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 almost 3 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.
#11 Updated by Jason Rojas over 2 years 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 about 2 years ago
just hit this on the param_values table… to be clear, the process here is:
- Stop puppet on all nodes
- Modify schema.rb so all :integer is :biginteger (not sure if this is necessary?)
- dump db, change all int() to bigint()
- start puppet back up
can someone confirm?