Delay between consecutive SQL statements when using Alembic on Jenkins

[Michał Guzek]

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?

Previously I put the DELETE statement in the downgrade() function and run it first by using alembic downgrade -1 and subsequently alembic upgrade head. But I had the same issue, it's as if alembic downgrade updated alembic_version table correctly on time but wasn't able to update my employee table on time, before upgrade() has started... And it also happened only on Jenkins.

[Mike Bayer]

On Sun, Aug 16, 2020, at 11:10 PM, Michał Guzek wrote:

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.

there's no inherent "delay" between statements.   this would sound like you have something going on in the background, such as special triggers and/or replication that has some kind of lag.   What else can you learn from looking at the (not shown here) error message you're getting?  is the table name what you expect?  does the stack trace make sense?  

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.

What else can you say about the MySQL database in use?   what's the exact version in use ?  mariadb or mysql?   is it galera?  is there a proxy server or scaling server in the middle?  what storage engine is used for these tables ?  (innodb?)  does the issue happen only when there's lots of load on the machine?   was there some configurational change that precipated this failure beginning to happen?

is this jenkins running on a VM or container, does it get built out to arbitrary servers? is it running anywhere else without a problem? 

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

Previously I put the DELETE statement in the downgrade() function and run it first by using alembic downgrade -1 and subsequently alembic upgrade head. But I had the same issue, it's as if alembic downgrade updated alembic_version table correctly on time but wasn't able to update my employee table on time, before upgrade() has started... And it also happened only on Jenkins.

--

You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/2dd0ce93-b4c9-4664-855a-ba81cacd519dn%40googlegroups.com.