What are the Slack Archives?

It’s a history of our time together in the Slack Community! There’s a ton of knowledge in here, so feel free to search through the archives for a possible answer to your question.

Because this space is not active, you won’t be able to create a new post or comment here. If you have a question or want to start a discussion about something, head over to our categories and pick one to post in! You can always refer back to a post from Slack Archives if needed; just copy the link to use it as a reference..

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

U01EQSHPHTP
U01EQSHPHTP Posts: 5 πŸ§‘πŸ»β€πŸš€ - Cadet

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

Comments

  • Thomas Lehner
    Thomas Lehner Support Engineer @ Spryker Posts: 289 πŸ› - Council (mod)
  • U01EQSHPHTP
    U01EQSHPHTP Posts: 5 πŸ§‘πŸ»β€πŸš€ - Cadet

    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
    Jeremy Fourna Lead Product Manager Sprykee Posts: 130 πŸ§‘πŸ»β€πŸš€ - Cadet

    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
    profuel SSA Sprykee Posts: 35 ✨ - Novice

    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
    profuel SSA Sprykee Posts: 35 ✨ - Novice

    @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
    U01EQSHPHTP Posts: 5 πŸ§‘πŸ»β€πŸš€ - Cadet

    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
    U01EQSHPHTP Posts: 5 πŸ§‘πŸ»β€πŸš€ - Cadet

    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
    U01EQSHPHTP Posts: 5 πŸ§‘πŸ»β€πŸš€ - Cadet

    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
    profuel SSA Sprykee Posts: 35 ✨ - Novice

    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.