Reading and writing BLOBs to MySQL works OK, but I cannot get it to READ anything bigger than somewhere near 10k. I have configured client and server ends to use 32MB max_allowed_packet, and from mysql.exe this works just fine. With MySQL-Python 1.20 (version_info reports (1, 2, 0, 'final', 1)) the following program fails, it does not return any data in the SELECT even though the data is there.
I'm running Python 2.4.3 and MySQL 5.0.16-nt.
Example code:
import sys, os, time
import MySQLdb
DATA_OK = 1000 * 'x'
DATA_FAIL = 10000 * 'x'
def connect(host='localhost', dbname='mysql', user='root', password=None):
if password is None:
result = MySQLdb.connect(host, user, db=dbname, read_default_group='client')
else:
result = MySQLdb.connect(host, user, password, db=dbname)
c = result.cursor()
c.close()
return result
def dropdb():
c = connect().cursor()
c.execute('DROP DATABASE pyblob')
c.close()
try:
dropdb()
except:
pass
c = connect().cursor()
c.execute('CREATE DATABASE pyblob')
c.execute('USE pyblob')
c.execute("""
CREATE TABLE image (
imageID INTEGER NOT NULL,
imageData LONGBLOB NOT NULL,
PRIMARY KEY(imageID)
)
""")
c.execute("INSERT INTO image (imageID, imageData) VALUES (%s, %s)", (1, DATA_OK))
c.execute("INSERT INTO image (imageID, imageData) VALUES (%s, %s)", (2, DATA_FAIL))
c.execute("SELECT imageData FROM image WHERE imageID=%s", (1,))
d = c.fetchall()
assert d # OK on my system
c.execute("SELECT imageData FROM image WHERE imageID=%s", (2,))
d = c.fetchall()
assert d # FAILS !
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Not at the moment. The Prepared Statement API (MySQL-4.1 and newer) seems to be able to do this, but it on the list of features to implement in MySQLdb-1.3.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Try 1.2.1_p2 and see if that's any different. 1.2.0 does not really support MySQL-5.0.
Oh, and if you are using InnoDB, please do call .commit() on your database connection (which you currently throw away, and only have long enough to make a cursor).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I spent a few hours, but failed to get the 1.2.1 package to build with mingw. Could not find a binary either. So if anyone is willing to provide one, I'd be happy to try it.
So instead I installed MySQL 4.1, and that gives exactly the same result. I only get a BLOB back when it's quite small (5k works, 10k does not), even though Mysql says it's there and happily returns it to any other client than Mysql-Python.
It's probably some buffer size setting or something silly like that.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Here's some more strangeness. I connect to the local DB, and run some commands. Note the weird result when SELECTing the data, and subsequently, it seems to have lost the connection and somehow reset to the mysql database.
>>> c.execute("use pyblob")
0L
>>> c.execute("select imageID from image")
2L
>>> c.fetchall()
((1L,), (2L,))
>>> c.execute("select imageID from image")
2L
>>> c.fetchall()
((1L,), (2L,))
>>> c.execute("select imageData from image")
18446744073709551615L
>>> c.execute("select imageData from image")
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "C:\Python24\Lib\site-packages\MySQLdb\cursors.py", line 137, in execute
self.errorhandler(self, exc, value)
File "C:\Python24\Lib\site-packages\MySQLdb\connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1146, "Table 'mysql.image' doesn't exist")
>>>
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Also, you should not be doing USE statements. You should specify the database with the db parameter to connect. If you need to change databases for some reason, use connection.select_db(dbname).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
See code: It fails with a BLOB size of 10000 bytes.
The code snippet is not production code. It's just something I stuffed together for others to be able to reproduce it.
I tried on another machine and the problem does not occur there (same versions of Python, MySQL and MySQL-Python). So it seems something configuration specific, though I cannot figure out what triggers it.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Reading and writing BLOBs to MySQL works OK, but I cannot get it to READ anything bigger than somewhere near 10k. I have configured client and server ends to use 32MB max_allowed_packet, and from mysql.exe this works just fine. With MySQL-Python 1.20 (version_info reports (1, 2, 0, 'final', 1)) the following program fails, it does not return any data in the SELECT even though the data is there.
I'm running Python 2.4.3 and MySQL 5.0.16-nt.
Example code:
import sys, os, time
import MySQLdb
DATA_OK = 1000 * 'x'
DATA_FAIL = 10000 * 'x'
def connect(host='localhost', dbname='mysql', user='root', password=None):
if password is None:
result = MySQLdb.connect(host, user, db=dbname, read_default_group='client')
else:
result = MySQLdb.connect(host, user, password, db=dbname)
c = result.cursor()
c.close()
return result
def dropdb():
c = connect().cursor()
c.execute('DROP DATABASE pyblob')
c.close()
try:
dropdb()
except:
pass
c = connect().cursor()
c.execute('CREATE DATABASE pyblob')
c.execute('USE pyblob')
c.execute("""
CREATE TABLE image (
imageID INTEGER NOT NULL,
imageData LONGBLOB NOT NULL,
PRIMARY KEY(imageID)
)
""")
c.execute("INSERT INTO image (imageID, imageData) VALUES (%s, %s)", (1, DATA_OK))
c.execute("INSERT INTO image (imageID, imageData) VALUES (%s, %s)", (2, DATA_FAIL))
c.execute("SELECT imageData FROM image WHERE imageID=%s", (1,))
d = c.fetchall()
assert d # OK on my system
c.execute("SELECT imageData FROM image WHERE imageID=%s", (2,))
d = c.fetchall()
assert d # FAILS !
Having found a machine without the problem, I arrived at the cause quickly...
Removing the following line from the [client] section of my.ini:
max_allowed_packet=32M
This fixed the problem, can now read and write 10MB blobs without problems with the code above.
BTW, is there a more memory-efficient way of reading/writing BLOBs? (e.g. streaming)
Not at the moment. The Prepared Statement API (MySQL-4.1 and newer) seems to be able to do this, but it on the list of features to implement in MySQLdb-1.3.
Try 1.2.1_p2 and see if that's any different. 1.2.0 does not really support MySQL-5.0.
Oh, and if you are using InnoDB, please do call .commit() on your database connection (which you currently throw away, and only have long enough to make a cursor).
I spent a few hours, but failed to get the 1.2.1 package to build with mingw. Could not find a binary either. So if anyone is willing to provide one, I'd be happy to try it.
So instead I installed MySQL 4.1, and that gives exactly the same result. I only get a BLOB back when it's quite small (5k works, 10k does not), even though Mysql says it's there and happily returns it to any other client than Mysql-Python.
It's probably some buffer size setting or something silly like that.
Here's some more strangeness. I connect to the local DB, and run some commands. Note the weird result when SELECTing the data, and subsequently, it seems to have lost the connection and somehow reset to the mysql database.
>>> c.execute("use pyblob")
0L
>>> c.execute("select imageID from image")
2L
>>> c.fetchall()
((1L,), (2L,))
>>> c.execute("select imageID from image")
2L
>>> c.fetchall()
((1L,), (2L,))
>>> c.execute("select imageData from image")
18446744073709551615L
>>> c.execute("select imageData from image")
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "C:\Python24\Lib\site-packages\MySQLdb\cursors.py", line 137, in execute
self.errorhandler(self, exc, value)
File "C:\Python24\Lib\site-packages\MySQLdb\connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1146, "Table 'mysql.image' doesn't exist")
>>>
How big are your blobs now?
Also, you should not be doing USE statements. You should specify the database with the db parameter to connect. If you need to change databases for some reason, use connection.select_db(dbname).
See code: It fails with a BLOB size of 10000 bytes.
The code snippet is not production code. It's just something I stuffed together for others to be able to reproduce it.
I tried on another machine and the problem does not occur there (same versions of Python, MySQL and MySQL-Python). So it seems something configuration specific, though I cannot figure out what triggers it.