Error: entero fuera de rango

markersocial · 16 Agosto, 2021 16:35

Getting this error a lot in Sidekiq (Retries and Dead lists):

Jobs::HandledExceptionWrapper: Wrapped ActiveRecord::RangeError: PG::NumericValueOutOfRange: ERROR: integer out of range

The jobs I’ve noticed this identical error for are:
Jobs::PostAlert
Jobs::ProcessPost
Jobs::NotifyCategoryChange

This was discussed a bit in the past here: Feedback on the new Review Queue - #250 by markersocial

Falco · 16 Agosto, 2021 17:20

Can you please run:

cd /var/discourse/
./launcher enter app
su postgres
psql
\x
\connect discourse 
SELECT id FROM notifications ORDER BY 1 DESC LIMIT 1;
\q
exit

markersocial · 16 Agosto, 2021 17:25

Thanks @Falco, I just ran it now. Here is the result:

-[ RECORD 1 ]--
id | 2147483496

Falco · 16 Agosto, 2021 17:33

Okay, so it’s the famous integer max problem. We need to move to bigint to fix this. I will take a look into this.

sam · 17 Agosto, 2021 00:50

For now your workaround is to run:

cd /var/discourse/
./launcher enter app
su postgres
psql
\x
\connect discourse 
ALTER TABLE notifications ALTER COLUMN id SET DATA TYPE bigint
\q
exit

This is the default for new installs but old installs have the wrong data type.

Running the workaround may be hard cause it will block the table, you many need to shed your web load first.

markersocial · 17 Agosto, 2021 06:48

Thanks @Falco & @sam - appreciate it

Regarding the workaround, this should be relatively safe to do? Not worried about down time, just breaking something.

It’s using the standard app.yml single container. For shedding the web load, do you think using read-only mode and running ./launcher stop app, ./launcher start app prior to doing the workaround probably suffice?

sam · 17 Agosto, 2021 06:54

It will not break anything, worst case is that it will just be “stuck” for a very long time.

markersocial · 17 Agosto, 2021 08:14

Thanks Sam, I did the workaround. Though, did not get any feedback upon inputting:

ALTER TABLE notifications ALTER COLUMN id SET DATA TYPE bigint

Not sure if it’s still processing perhaps. Currently getting the same error (in the /sidekiq dead list, for last retry ‘just now’ jobs) for:

Jobs::PostAlert
Jobs::ProcessPost
Jobs::NotifyCategoryChange

sam · 17 Agosto, 2021 08:29

Looks like you need to run in with post_id column as well

markersocial · 17 Agosto, 2021 08:40

Thanks!

Just to be sure, does this look correct?

ALTER TABLE notifications ALTER COLUMN post_id SET DATA TYPE bigint

sam · 17 Agosto, 2021 08:43

Yes that should be safe, once we add an official migration it will allow for it

markersocial · 17 Agosto, 2021 09:09

Perfect, thanks

I ran that (no confirmation/feedback received, like the previous one). Forum didn’t slow down, so not sure if it worked. Getting the same errors currently.

Maybe it would be best if I wait for the official migration.

sam · 17 Agosto, 2021 09:57

Yeah looks like this is in the post_alerts table, we need to sweep through many tables

sam · 7 Septiembre, 2021 05:32

Curious where you are on this issue now? Did the errors stop?

Originally we were thinking of making some official migration here, but the risk far outweighs the benefit. We find it exceedingly rare to come across DBs with more than 2,147,483,647 posts. 2.1 billion is a real big number.

The downside of increasing the sized everywhere are that storage requirements go up.

Where we are now is that we are considering adding a rake task that “makes space” if you are in an outlier case where you have tables in Discourse that containe 2 billion rows (or had 2 billion rows of churn)

markersocial · 7 Septiembre, 2021 09:56

Thanks for the follow up @sam

I just upgraded to 2.8.0.beta6 and still getting the integer out of range errors.

I think that it’s just the notifications that has got to a massive number, which is a bit more realistic to reach the limit compared to number of posts. A lot of large topics with many replies, likes etc. from different users can result in quite a lot of notifications.

A rake task sounds fantastic

bjlarouche · 14 Febrero, 2023 22:11

Sé que este es un hilo antiguo –

Acabamos de encontrar este problema en nuestra configuración también (devforum.roblox.com). Estamos ejecutando la v2.8.9, pero pronto actualizaremos a la 3.0.1.

Notamos que algo andaba mal cuando los usuarios comenzaron a ver 403/500 al intentar dar “me gusta” o “no me gusta” a las publicaciones.

Luego me encontré con este hilo y revisé nuestra tabla de notificaciones:

=> SELECT id FROM notifications ORDER BY 1 DESC LIMIT 1;
     id     
------------
 2147483647
(1 row)

@sam ¿Sigue siendo la solución alternativa anterior la mejor sugerencia, o se consideró más un rake task desde septiembre de 2021?

bjlarouche · 14 Febrero, 2023 23:34

Más información –

Después de modificar la columna notifications.id, estoy viendo un problema separado con el trabajo Jobs::PostAlert

Job exception: 2147498514 está fuera de rango para ActiveModel::Type::Integer con límite de 4 bytes

¿Quizás hay otra tabla/columna que me estoy perdiendo? ¿O algún lugar en ruby que todavía esté esperando el tipo de dato entero?

backtrace

activemodel-6.1.6.1/lib/active_model/type/integer.rb:49:in `ensure_in_range'

activemodel-6.1.6.1/lib/active_model/type/integer.rb:28:in `serialize'

activemodel-6.1.6.1/lib/active_model/attribute.rb:56:in `value_for_database'

activemodel-6.1.6.1/lib/active_model/attribute.rb:68:in `forgetting_assignment'

activemodel-6.1.6.1/lib/active_model/attribute_set.rb:90:in `transform_values'

activemodel-6.1.6.1/lib/active_model/attribute_set.rb:90:in `map'

activemodel-6.1.6.1/lib/active_model/dirty.rb:262:in `forget_attribute_assignments'

activemodel-6.1.6.1/lib/active_model/dirty.rb:154:in `changes_applied'

activerecord-6.1.6.1/lib/active_record/attribute_methods/dirty.rb:202:in `_create_record'

activerecord-6.1.6.1/lib/active_record/callbacks.rb:461:in `block in _create_record'

activesupport-6.1.6.1/lib/active_support/callbacks.rb:106:in `run_callbacks'

activesupport-6.1.6.1/lib/active_support/callbacks.rb:824:in `_run_create_callbacks'

activerecord-6.1.6.1/lib/active_record/callbacks.rb:461:in `_create_record'

activerecord-6.1.6.1/lib/active_record/timestamp.rb:108:in `_create_record'

activerecord-6.1.6.1/lib/active_record/persistence.rb:900:in `create_or_update'

activerecord-6.1.6.1/lib/active_record/callbacks.rb:457:in `block in create_or_update'

activesupport-6.1.6.1/lib/active_support/callbacks.rb:106:in `run_callbacks'

activesupport-6.1.6.1/lib/active_support/callbacks.rb:824:in `_run_save_callbacks'

activerecord-6.1.6.1/lib/active_record/callbacks.rb:457:in `create_or_update'

activerecord-6.1.6.1/lib/active_record/timestamp.rb:126:in `create_or_update'

activerecord-6.1.6.1/lib/active_record/persistence.rb:507:in `save!'

activerecord-6.1.6.1/lib/active_record/validations.rb:53:in `save!'

activerecord-6.1.6.1/lib/active_record/transactions.rb:302:in `block in save!'

activerecord-6.1.6.1/lib/active_record/transactions.rb:354:in `block in with_transaction_returning_status'

activerecord-6.1.6.1/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `block in transaction'

activerecord-6.1.6.1/lib/active_record/connection_adapters/abstract/transaction.rb:319:in `block in within_new_transaction'

activesupport-6.1.6.1/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'

activesupport-6.1.6.1/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'

activesupport-6.1.6.1/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'

activesupport-6.1.6.1/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'

activesupport-6.1.6.1/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'

activerecord-6.1.6.1/lib/active_record/connection_adapters/abstract/transaction.rb:317:in `within_new_transaction'

activerecord-6.1.6.1/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `transaction'

activerecord-6.1.6.1/lib/active_record/transactions.rb:350:in `with_transaction_returning_status'

activerecord-6.1.6.1/lib/active_record/transactions.rb:302:in `save!'

activerecord-6.1.6.1/lib/active_record/suppressor.rb:48:in `save!'

/app/app/models/notification.rb:40:in `tap'

/app/app/models/notification.rb:40:in `consolidate_or_create!'

activerecord-6.1.6.1/lib/active_record/relation/delegation.rb:67:in `block in consolidate_or_create!'

activerecord-6.1.6.1/lib/active_record/relation.rb:406:in `block in scoping'

activerecord-6.1.6.1/lib/active_record/relation.rb:804:in `_scoping'

activerecord-6.1.6.1/lib/active_record/relation.rb:406:in `scoping'

activerecord-6.1.6.1/lib/active_record/associations/collection_proxy.rb:1109:in `scoping'

activerecord-6.1.6.1/lib/active_record/relation/delegation.rb:67:in `consolidate_or_create!'

/app/app/services/post_alerter.rb:496:in `create_notification'

/app/app/services/post_alerter.rb:825:in `block in notify_post_users'

/app/app/services/post_alerter.rb:838:in `block (2 levels) in each_user_in_batches'

activerecord-6.1.6.1/lib/active_record/relation/delegation.rb:88:in `each'

activerecord-6.1.6.1/lib/active_record/relation/delegation.rb:88:in `each'

/app/app/services/post_alerter.rb:838:in `block in each_user_in_batches'

/app/app/services/post_alerter.rb:837:in `each'

/app/app/services/post_alerter.rb:837:in `each_slice'

/app/app/services/post_alerter.rb:837:in `each_user_in_batches'

/app/app/services/post_alerter.rb:821:in `notify_post_users'

/app/app/services/post_alerter.rb:162:in `after_save_post'

/app/app/jobs/regular/post_alert.rb:11:in `execute'

/app/app/jobs/base.rb:232:in `block (2 levels) in perform'

/app/lib/rails_multisite/connection_management.rb:80:in `with_connection'

/app/app/jobs/base.rb:221:in `block in perform'

/app/app/jobs/base.rb:217:in `each'

/app/app/jobs/base.rb:217:in `perform'

sidekiq-6.3.1/lib/sidekiq/processor.rb:196:in `execute_job'

sidekiq-6.3.1/lib/sidekiq/processor.rb:164:in `block (2 levels) in process'

sidekiq-6.3.1/lib/sidekiq/middleware/chain.rb:138:in `block in invoke'

/app/lib/sidekiq/pausable.rb:138:in `call'

sidekiq-6.3.1/lib/sidekiq/middleware/chain.rb:140:in `block in invoke'

sidekiq-6.3.1/lib/sidekiq/middleware/chain.rb:143:in `invoke'

sidekiq-6.3.1/lib/sidekiq/processor.rb:163:in `block in process'

sidekiq-6.3.1/lib/sidekiq/processor.rb:136:in `block (6 levels) in dispatch'

sidekiq-6.3.1/lib/sidekiq/job_retry.rb:112:in `local'

sidekiq-6.3.1/lib/sidekiq/processor.rb:135:in `block (5 levels) in dispatch'

sidekiq-6.3.1/lib/sidekiq/rails.rb:14:in `block in call'

activesupport-6.1.6.1/lib/active_support/execution_wrapper.rb:91:in `wrap'

activesupport-6.1.6.1/lib/active_support/reloader.rb:72:in `block in wrap'

activesupport-6.1.6.1/lib/active_support/execution_wrapper.rb:91:in `wrap'

activesupport-6.1.6.1/lib/active_support/reloader.rb:71:in `wrap'

sidekiq-6.3.1/lib/sidekiq/rails.rb:13:in `call'

sidekiq-6.3.1/lib/sidekiq/processor.rb:131:in `block (4 levels) in dispatch'

sidekiq-6.3.1/lib/sidekiq/processor.rb:257:in `stats'

sidekiq-6.3.1/lib/sidekiq/processor.rb:126:in `block (3 levels) in dispatch'

sidekiq-6.3.1/lib/sidekiq/job_logger.rb:13:in `call'

sidekiq-6.3.1/lib/sidekiq/processor.rb:125:in `block (2 levels) in dispatch'

sidekiq-6.3.1/lib/sidekiq/job_retry.rb:79:in `global'

sidekiq-6.3.1/lib/sidekiq/processor.rb:124:in `block in dispatch'

sidekiq-6.3.1/lib/sidekiq/logger.rb:11:in `with'

sidekiq-6.3.1/lib/sidekiq/job_logger.rb:33:in `prepare'

sidekiq-6.3.1/lib/sidekiq/processor.rb:123:in `dispatch'

sidekiq-6.3.1/lib/sidekiq/processor.rb:162:in `process'

sidekiq-6.3.1/lib/sidekiq/processor.rb:78:in `process_one'

sidekiq-6.3.1/lib/sidekiq/processor.rb:68:in `run'

sidekiq-6.3.1/lib/sidekiq/util.rb:43:in `watchdog'

sidekiq-6.3.1/lib/sidekiq/util.rb:52:in `block in safe_thread'

sam · 15 Febrero, 2023 01:25

Sí, esta sigue siendo la única solución aquí. Me preocupa cambiarlo en el núcleo, pero supongo que esto seguirá sucediendo en foros gigantescos si no lo solucionamos.
La desventaja es el aumento del almacenamiento.

bjlarouche · 15 Febrero, 2023 01:35

¿Sabes si hay algún uso en el servicio post_actions (o en algún lugar del proceso de notificación) que todavía pueda estar esperando enteros después de ejecutar ALTER?

Estamos viendo errores 5xx en las llamadas de “me gusta”/“no me gusta” a /post_actions con la respuesta

{"errors":["No se pudo encontrar la URL o el recurso solicitado."],"error_type":"not_found"}

Además, algunas de las fallas de trabajos en torno a las notificaciones (Jobs::BookmarkReminderNotifications, Jobs::GrantAnniversaryBadges, Jobs::PostAlert).

Agregué el rastreo de pila para PostAlert en mi mensaje anterior; parece que hay un problema que se lanza por un límite de enteros en consolidate_or_create en notification.rb.

Para nuestro uso, el aumento del almacenamiento no es una gran preocupación si podemos restaurar la funcionalidad

sam · 15 Febrero, 2023 01:46

Quizás intenta reiniciar su contenedor, podría haber algo en caché en la memoria.

Tema		Respuestas	Vistas
Very slow Sidekiq issue with large queue due to massive numbers of unread user notifications Installation	69	7326	11 Mayo 2020
Problem migrating polls: `integer out of range` (1000000000000) Support	3	847	4 Diciembre 2018
Notifications are coming again even after reading them [Private Topics plugin] Support notifications	20	1330	1 Diciembre 2023
Error While Trying to load, New and Admin accounts Support	15	3068	20 Julio 2015
Database migration chokes on huge value of a "calendar-details" item in table "post_custom_fields" Bug	13	2011	7 Diciembre 2020

Error: entero fuera de rango

Temas relacionados