Hello, We are experiencing a failure of the `check_oms_timeouts` Jenkins job, because of the follow

U01EQSHPHTP

Hello,
We are experiencing a failure of the check_oms_timeouts Jenkins job, because of the following exception:

Zed.ERROR: CLI command "oms:check-timeout" exception, message "State machine trigger is locked. Propel exception: Unable to execute INSERT statement [INSERT INTO "spy_oms_state_machine_lock" ("id_oms_state_machine_lock", "identifier", "expires", "details", "created_at", "updated_at") VALUES (:p0, :p1, :p2, :p3, :p4, :p5)]" {"exception":"[object] (Spryker\\Zed\\Oms\\Business\\Exception\\LockException(code: 0): State machine trigger is locked. Propel exception: Unable to execute INSERT statement [INSERT INTO \"spy_oms_state_machine_lock\" (\"id_oms_state_machine_lock\", \"identifier\", \"expires\", \"details\", \"created_at\", \"updated_at\") VALUES (:p0, :p1, :p2, :p3, :p4, :p5)] at /data/shop/vendor/spryker/oms/src/Spryker/Zed/Oms/Business/Lock/TriggerLocker.php:64, Propel\\Runtime\\Exception\\PropelException(code: 0): Unable to execute INSERT statement [INSERT INTO \"spy_oms_state_machine_lock\" (\"id_oms_state_machine_lock\", \"identifier\", \"expires\", \"details\", \"created_at\", \"updated_at\") VALUES (:p0, :p1, :p2, :p3, :p4, :p5)] at /data/shop/src/Orm/Zed/Oms/Persistence/Base/SpyOmsStateMachineLock.php:886, PDOException(code: 22003): SQLSTATE[22003]: Numeric value out of range: 7 ERROR:  value \"2147484436\" is out of range for type integer at /data/shop/vendor/propel/propel/src/Propel/Runtime/Connection/StatementWrapper.php:194)

It looks like the id_oms_state_machine_lock autoincrement sequence ran out of numbers in the integer range.

My first solution would be to reset the sequence to 0, but I’m not sure if that could have dangerous side effects. Another solution could be to switch to BIGINT as datatype, but that could impact performance.

Did anyone already experience that problem? Which option is the better one?
Best regards

Thomas Lehner

@jeremy.fourna maybe?

U01EQSHPHTP

We fixed it now with ALTER SEQUENCE spy_oms_state_machine_lock_pk_seq RESTART WITH 1; and currently see no problems. There seems to be no foreign key on that column so I hope we are safe. But an expert opinion would still be nice 🙂

Jeremy Fourna

interesting, will discuss this internally with the architects to see what we can do here, because
• changing the DB schema is a major
• if you need to manually fix the sequence every time this must not a long term solution

profuel

There're no limitations to this Lock table, and it's usually empty, so technically you may safely reset the sequence to 1 every night.

profuel

@ULC9V5Y2G do you have a daily delta sequence increase? I want to use it as a reference that we actually have to consider improvement in this place from Spryker's side. My estimation of 10000 locks per day , means overflow in 588 years, which is not that bad 🙂

U01EQSHPHTP

We have way more locks than 10.000 per day. Since I reset the sequence at around 9:00 until now we already have increased the sequence to 1.3 Mio. I’m not sure why this happens though. I need to check with my collegues.

U01EQSHPHTP

A clean / easy fix for this problem would be to create the sequence as INT (not BIGINT as it currently is) and to set the cycle property to one. At least for this table this should be fine. We are currently reviewing our tables to check if the problem could occur on other tables as well.

U01EQSHPHTP

The reason why we have so many locks is the complexity of our OMS process. We have dozens of steps in the oms graph along with thousands of orders daily.

profuel

Thanks for your update, would be awesome to hear about your result. Once we have answer from our side, I'll share it with you.