Hi, I'm running into problems running MySQL comman...
# prefect-community
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?
upvote 1
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.
The sql that runs fine is in the other tasks such as loading data to mysql
Thank you
Here is the error after 2 hours of running usind the mysqlexecute task from prefect. This also happens when just using pymysql. The oracle connector just keeps running.
I know the stored procedure finishes before this error because I track through the show processlist in mysql while it is running.
Interesting. The fact that it's failing when using vanilla
without Prefect suggests this is an issue at the
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
any ideas on how to diagnose it?
Taking a look at the MySQL task and
more generally, it looks like you need to specify that you want to
in order for your changes to be saved. It looks like that defaults to
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
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()
works without commit
in jupyter notebook
I can try to put commit on though
👍 1
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.
I don't know if something happens when it is a long running command in prefect
Hmm. I wouldn't anticipate that behavior. Do you mind opening a Github issue for this?