Delay between consecutive SQL statements when using Alembic on Jenkins

Question

I have a problem with delays between consecutive SQL statements when Alembic executes a migration script's upgrade() function on Jenkins:

def upgrade():
    op.execute("DELETE FROM employee WHERE name='John';") #John also has its primary key ID field equal to 1
    op.execute("INSERT INTO employee (id,name,occupation) VALUES (1, 'Michael', 'Bartender');")

Basically the second statement can't run to completion because I'm getting an error that ID column would contain duplicate values. Apparently, there is some sort of delay between the first and second op.execute() statement and when the second one is executed, the DB still contains the old entry for John whose ID is also 1.

I use SQLAlchemy's create_engine() to initiate a DB connection to MySQL in Alembic's run_migration_online() function in its env.py configuration file. I don't experience any such delays or errors on my local machine where I use the exact same codebase and MySQL as well. Also, the problem on Jenkins is intermittent, sometimes the build succeeds when I just hit rebuild.

Do you know what might be the cause of those weird delays on Jenkins?

EDIT: The exact error I'm getting:

sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062, "Duplicate entry '1' for key 'PRIMARY'") [SQL: "INSERT INTO employee (id,name,occupation) VALUES (1, 'Michael', 'Bartender');"] (Background on this error at: http://sqlalche.me/e/gkpj)

Could you have parallel processes attempting to insert the same value? — Ilja Everilä, Aug 17 '20 at 05:49
Related (though no proper answer): https://stackoverflow.com/questions/6777672/mysql-insert-after-delete-fails-because-of-duplicate-entry. This one hints that there could indeed be an internal race condition in MySQL: https://stackoverflow.com/questions/49499654/delete-then-insert-occasionally-fails-with-duplicate-key! That would be quite something. — Ilja Everilä, Aug 17 '20 at 06:25
Please [edit] the question and add the **exact error messages produced**. — Antti Haapala -- Слава Україні, Aug 17 '20 at 07:25

Delay between consecutive SQL statements when using Alembic on Jenkins

0 Answers0