Foreman DB - log id as an integer

Support
1

Just an FYI -

This is in Foreman 1.12. Not sure if it's fixed in later versions.

If you see this in your postgresql log:
2017-07-24 07:03:49 PDT
[sess:5974bf14.47da,pid:18394,vitd:9/4891447,tid:0,db:myforemandb]ERROR:
integer out of range
2017-07-24 07:03:49 PDT
[sess:5974bf14.47da,pid:18394,vitd:9/4891447,tid:0,db:myforemandb]STATEMENT:
INSERT INTO "logs" ("message_id", "source_id", "report_id", "level_id",
"created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id"

You might want to check the log id #:
myforemandb=> select * from logs order by created_at desc limit 2;
id | source_id | message_id | report_id | level_id |
created_at | updated_at

··· ------------+-----------+------------+-----------+----------+----------------------------+---------------------------- 2147483647 | 321 | 1807107 | 292344055 | 2 | 2017-07-24 12:08:01.668757 | 2017-07-24 12:08:01.668757 2147483646 | 23451033 | 18001958 | 292344087 | 2 | 2017-07-24 12:08:01.663546 | 2017-07-24 12:08:01.663546 (2 rows)

That first id listed is 2^31-1. It turns out that “id” is a signed int,
meaning it’s 32-bit. I cannot write more logs to the logs table now :slight_smile:

(And before someone brings up my other question - locking issues are
unrelated to hitting this limit, they were happening before this happened.)

2

I believe this was fixed on 1.15 -
https://github.com/theforeman/foreman/pull/4169/files -
Bug #13810: Reports fail to upload, Mysql2::Error: Out of range value for column 'id' (logs table) - Foreman

Are you able to update to try it out? If not, possibly you can cherry-pick
the changes from the pull request and it would fix that problem.

··· On Wednesday, July 26, 2017 at 6:38:42 PM UTC+2, Chris Baldwin wrote: > > Just an FYI - > > This is in Foreman 1.12. Not sure if it's fixed in later versions. > > If you see this in your postgresql log: > 2017-07-24 07:03:49 PDT > [sess:5974bf14.47da,pid:18394,vitd:9/4891447,tid:0,db:myforemandb]ERROR: > integer out of range > 2017-07-24 07:03:49 PDT > [sess:5974bf14.47da,pid:18394,vitd:9/4891447,tid:0,db:myforemandb]STATEMENT: > INSERT INTO "logs" ("message_id", "source_id", "report_id", "level_id", > "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id" > > You might want to check the log id #: > myforemandb=> select * from logs order by created_at desc limit 2; > id | source_id | message_id | report_id | level_id | > created_at | updated_at > > ------------+-----------+------------+-----------+----------+----------------------------+---------------------------- > 2147483647 | 321 | 1807107 | 292344055 | 2 | 2017-07-24 > 12:08:01.668757 | 2017-07-24 12:08:01.668757 > 2147483646 | 23451033 | 18001958 | 292344087 | 2 | 2017-07-24 > 12:08:01.663546 | 2017-07-24 12:08:01.663546 > (2 rows) > > That first id listed is 2^31-1. It turns out that "id" is a signed int, > meaning it's 32-bit. I cannot write more logs to the logs table now :) > > (And before someone brings up my other question - locking issues are > unrelated to hitting this limit, they were happening before this happened.) >
3

Thanks - I'm sanity checking the new code works for me in my dev instances.
So far it looks promising - add the file, run foreman-rake db:migrate.

··· On Thursday, July 27, 2017 at 5:21:43 AM UTC-4, Daniel Lobato wrote: > > I believe this was fixed on 1.15 - > https://github.com/theforeman/foreman/pull/4169/files - > http://projects.theforeman.org/issues/13810 > > Are you able to update to try it out? If not, possibly you can cherry-pick > the changes from the pull request and it would fix that problem. > > On Wednesday, July 26, 2017 at 6:38:42 PM UTC+2, Chris Baldwin wrote: >> >> Just an FYI - >> >> This is in Foreman 1.12. Not sure if it's fixed in later versions. >> >> If you see this in your postgresql log: >> 2017-07-24 07:03:49 PDT >> [sess:5974bf14.47da,pid:18394,vitd:9/4891447,tid:0,db:myforemandb]ERROR: >> integer out of range >> 2017-07-24 07:03:49 PDT >> [sess:5974bf14.47da,pid:18394,vitd:9/4891447,tid:0,db:myforemandb]STATEMENT: >> INSERT INTO "logs" ("message_id", "source_id", "report_id", "level_id", >> "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id" >> >> You might want to check the log id #: >> myforemandb=> select * from logs order by created_at desc limit 2; >> id | source_id | message_id | report_id | level_id | >> created_at | updated_at >> >> ------------+-----------+------------+-----------+----------+----------------------------+---------------------------- >> 2147483647 | 321 | 1807107 | 292344055 | 2 | 2017-07-24 >> 12:08:01.668757 | 2017-07-24 12:08:01.668757 >> 2147483646 | 23451033 | 18001958 | 292344087 | 2 | 2017-07-24 >> 12:08:01.663546 | 2017-07-24 12:08:01.663546 >> (2 rows) >> >> That first id listed is 2^31-1. It turns out that "id" is a signed int, >> meaning it's 32-bit. I cannot write more logs to the logs table now :) >> >> (And before someone brings up my other question - locking issues are >> unrelated to hitting this limit, they were happening before this happened.) >> >
4

Thanks again - adding the new migration script and then migrating fixes the
problem in 1.12.4. Warning to anyone else running in to this: the migration
script will lock the logs, reports, and sessions tables while it performs
these changes. If you have a sizable DB (77 million logs, 8.1 million
reports, 7.4 million messages in our case) this can take forever. It took
7.3 hours for me, but it worked.

··· On Thursday, July 27, 2017 at 1:34:06 PM UTC-4, Chris Baldwin wrote: > > Thanks - I'm sanity checking the new code works for me in my dev > instances. So far it looks promising - add the file, run foreman-rake > db:migrate. > > On Thursday, July 27, 2017 at 5:21:43 AM UTC-4, Daniel Lobato wrote: >> >> I believe this was fixed on 1.15 - >> https://github.com/theforeman/foreman/pull/4169/files - >> http://projects.theforeman.org/issues/13810 >> >> Are you able to update to try it out? If not, possibly you can >> cherry-pick the changes from the pull request and it would fix that problem. >> >> On Wednesday, July 26, 2017 at 6:38:42 PM UTC+2, Chris Baldwin wrote: >>> >>> Just an FYI - >>> >>> This is in Foreman 1.12. Not sure if it's fixed in later versions. >>> >>> If you see this in your postgresql log: >>> 2017-07-24 07:03:49 PDT >>> [sess:5974bf14.47da,pid:18394,vitd:9/4891447,tid:0,db:myforemandb]ERROR: >>> integer out of range >>> 2017-07-24 07:03:49 PDT >>> [sess:5974bf14.47da,pid:18394,vitd:9/4891447,tid:0,db:myforemandb]STATEMENT: >>> INSERT INTO "logs" ("message_id", "source_id", "report_id", "level_id", >>> "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id" >>> >>> You might want to check the log id #: >>> myforemandb=> select * from logs order by created_at desc limit 2; >>> id | source_id | message_id | report_id | level_id | >>> created_at | updated_at >>> >>> ------------+-----------+------------+-----------+----------+----------------------------+---------------------------- >>> 2147483647 | 321 | 1807107 | 292344055 | 2 | 2017-07-24 >>> 12:08:01.668757 | 2017-07-24 12:08:01.668757 >>> 2147483646 | 23451033 | 18001958 | 292344087 | 2 | 2017-07-24 >>> 12:08:01.663546 | 2017-07-24 12:08:01.663546 >>> (2 rows) >>> >>> That first id listed is 2^31-1. It turns out that "id" is a signed int, >>> meaning it's 32-bit. I cannot write more logs to the logs table now :) >>> >>> (And before someone brings up my other question - locking issues are >>> unrelated to hitting this limit, they were happening before this happened.) >>> >>