MariaDB, pypyodbc, "Unknown prepared statement handler" executing "SELECT" query on table loaded with "LOAD DATA LOCAL INFILE."

Question

Python 3.4.3, MariaDB 10.0.21, MariaDB ODBC Connector 1.0.0, pypyodbc 1.3.3, all 64-bit on 64-bit Windows 7.

I've got a python script that's supposed to create a table, populate it with data from a fixed-width file, and then run a SELECT statement against it. All simple stuff. My script looks something like this:

import pypyodbc

def do_stuff(name, password, filepath):
    db = pypyodbc.connect(driver = "{MariaDB ODBC 1.0 Driver}",
                    server = "localhost", uid = name,
                    pwd = password, autocommit = True)

    cursor = db.cursor()

    cursor.execute("CREATE TABLE `foo`.`bar` (`col1` INT);")
    cursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE `foo`.`bar` (@row) SET col1 = SUBSTR(@row,1,1)" % filepath.replace("\\", "\\\\"))
    for row in cursor.execute("SELECT * FROM `foo`.`bar`"):
        print(row)
    db.close()

do_stuff("root", "password", r"C:\\Users\\laj\\Desktop\\test.txt")

It grabs the first character from each line in the the text file and sticks it in the sole column in the table. When the "SELECT" statement comes around, however, I get hit with the following error:

Traceback (most recent call last):
  File "test.py", line 25, in <module>
    do_stuff("root", "oag123", r"C:\\Users\\laj\\Desktop\\test.txt")
  File "test.py", line 21, in do_stuff
    for row in cursor.execute("SELECT * FROM `foo`.`bar`"):
  File "C:\Python34\lib\site-packages\pypyodbc-1.3.3-py3.4.egg\pypyodbc.py", line 1605, in execute
  File "C:\Python34\lib\site-packages\pypyodbc-1.3.3-py3.4.egg\pypyodbc.py", line 1631, in execdirect
  File "C:\Python34\lib\site-packages\pypyodbc-1.3.3-py3.4.egg\pypyodbc.py", line 986, in check_success
  File "C:\Python34\lib\site-packages\pypyodbc-1.3.3-py3.4.egg\pypyodbc.py", line 964, in ctrl_err
pypyodbc.Error: ('HY000', '[HY000] Unknown prepared statement handler (5) given to mysqld_stmt_reset')

What really gets me, though is that I can get rid of the error simply by closing and reopening the database connection in between populating the table and executing the "SELECT," like so:

import pypyodbc

def do_stuff(name, password, filepath):
    db = pypyodbc.connect(driver = "{MariaDB ODBC 1.0 Driver}",
                    server = "localhost", uid = name,
                    pwd = password, autocommit = True)

    cursor = db.cursor()

    cursor.execute("CREATE TABLE `foo`.`bar` (`col1` INT);")
    cursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE `foo`.`bar` (@row) SET col1 = SUBSTR(@row,1,1)" % filepath.replace("\\", "\\\\"))

    db.close()
    db = pypyodbc.connect(driver = "{MariaDB ODBC 1.0 Driver}",
                    server = "localhost", uid = name,
                    pwd = password, autocommit = True)

    cursor = db.cursor()


    for row in cursor.execute("SELECT * FROM `foo`.`bar`"):
        print(row)
    db.close()

do_stuff("root", "password", r"C:\\Users\\laj\\Desktop\\test.txt")

Unfortunately, this isn't actually a valid solution to my problem. Not only is it something I shouldn't have to do, but it also doesn't help when it comes to temporary tables because they just get dropped during the disconnect phase of that "fix." Any insight would be great, this is driving me up a wall.

Found that I'm able to complete these actions no problem with perl's DBI. So at this point I'm looking at pypyodbc as the culprit. — Logan Jones, Sep 10 '15 at 16:01

score 0 · Answer 1 · answered Sep 09 '15 at 02:02

0

execute does not return what you think:

cursor.execute("SELECT ...");
rows = cur.fetchall();
for row in rows ...

answered Sep 09 '15 at 02:02

Rick James

135,179
13
127
222

cursor.execute("SELECT ..."); It dies here. It's the execution of the select statement that kills it. Even if I don't try to iterate it, which I can do anyway as long as I close and reopen the connection first. The way I'm treating the cursor is not the problem. – Logan Jones Sep 09 '15 at 17:54

score 0 · Accepted Answer · answered Sep 10 '15 at 16:17

0

Turned out to be a pypyodbc problem. Installed pyodbc, imported it as pypyodbc, and everything worked as it should.

answered Sep 10 '15 at 16:17

Logan Jones

324
1
3
10

MariaDB, pypyodbc, "Unknown prepared statement handler" executing "SELECT" query on table loaded with "LOAD DATA LOCAL INFILE."

2 Answers2