1

Hello I am using Python connector 8.0.13 under Python 3.6 and MySQL 5.5.62. On Win 7

I want to write large Blob Data into my Tables. The problem is that under Python the query crashes the server with the Error:

C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysqld.exe: Out of memory (Needed 240225944 bytes)

And

Error: Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

My Ram neither Swap are Full when this occurs. The strange thing is. If I use MySQL Workbench, the Data gets inserted.

What I did to this point:

  • Raise innodb_buffer_pool_size to 256M
  • Max_allowed_packet to 2G
  • Set all timeout settings to at least 600 (also in mysql.connector.connect)

The insertion via Python looks sort like this:

command = 'INSERT into table (col1,Blob, col3,... ) Values(val1, %s, val3) on duplicate key update col1=val1, Blob = %s, col3=val3'
cursor.execute(command, [Blobdata, Blobdata])

The the above code works for data about 15-20 MB flawless but with for example 200 MB (like in the Error above) the Server shuts down.

I would be thankful if some of you got an idea how to solve this.

  • Thank you CalderiaG for correcting my Format. I could not quite figure out how to format the text on my mobile. – Sepp May 20 at 14:13
  • You were on the mobile? Oh, that can be a pain doing formatting XD but you're welcome :D – CaldeiraG May 21 at 6:48
0

OK, after fiddling some Time I found a working solution.

The keyword is: LOAD_FILE()

This Funkction is able to load Data from any kind of filetype. However the file to upload must be present on the Host Server. (be aware of secure_file_priv).

The Syntax looks sort of like this:

INSERT INTO table (Blobcolumn) VALUES(load_file('full path' ))

Beware, at least with windows, the slashes need to be forward (/). Also load_file() seems not to raise an error if the file was not inserted.

I tested so far with a 400 MB File without trouble. Also, there is no problem to select and fetch the data the usual way.

This feels more like a workaround. I'm still open for better solutions. I also would like to know if this is a bug that was only present in 5.5 or if this problem still exists in up-to-date versions.

  • I apologize for not formatting it correct again... But I didn't find tools other than upload a picture in the editor. (android 9 Chrome) – Sepp May 24 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.