Hi, I'm running into problems running MySQL commands from prefect. I've tried pymysql, mysqlconnector, and sql alchemy. I can't figure out if this is a mysql problem or a seeting I'm missing or a Prefect problem. The code runs and ends fine in a jupyter notebook. However, when prefect runs it it never gets the signal that the command ends. I even tried packaging it up in a stored procedure and everything ran perfectly fine. Prefect just never got the response that that it completed and I can't figure out why. Any, help would be appreciated. I also have other sql commands in other tasks that run just fine. I've attached the code.

Could you post the code for your Flow as well as an example of SQL in other tasks that run fine? When you say it never gets the signal that the command ends, you mean your code doesn’t hear back from MySQL that the commands execute? Do you know which of the commands this happens during?

Here is the flow. I tried the mysql task same result. I know for sure it fails on the sql attached above in the text file the create table command. It also continuously runs and fails on the tasks called create_foreign_key_invoice_iline and ct_iline_pk. One is creating a primary key and the other a foreign key. I packaged up the create table command with some other commands as a stored procedure. These commands are in the text file attached above. Same result. I appreciate your help. Let me know f you have any questions.

Interesting. The fact that it's failing when using vanilla

pymysql

without Prefect suggests this is an issue at the

pymysql

layer or in the DB itself. You mentioned something about an Oracle connector-- how does that factor into your setup?

It runs fine in a jupyter notebook. I can see when it finishes. I've tried as many connectors as I could find to see if that fixed the issue. It is only in prefect that is doesn't seem to get a response

Taking a look at the MySQL task and

mypysql

more generally, it looks like you need to specify that you want to

commit

in order for your changes to be saved. It looks like that defaults to

False

in the task library-- is there any chance that you're not getting back a response because the transaction isn't being committed?

I thought about commiting

try:

with connection.cursor() as cursor: cursor.execute("DROP TABLE pymysql_test") sql = "CREATE TABLE pymysql_test AS SELECT DISTINCT Location as Location from ct_qty_on_hand" cursor.execute(sql) cursor.execute("ALTER TABLE pymysql_test ADD Id BIGINT PRIMARY KEY AUTO_INCREMENT") cursor.execute("CREATE INDEX location ON pymysql_test(Location(4))") finally: connection.close()

Sounds good. Let's give that a shot, and we can open an issue if that doesn't do the trick.

Well even with commit on it failed. A much shorter stored procedure was successful though. I figured out a way around this issue by putting the code in a jupyter notebook and running the the jupyter notebook from a task using nbconvert.

Hmm. I wouldn't anticipate that behavior. Do you mind opening a Github issue for this?

sure