Inserting large strings into a MySQL DB

[sirdavidoff]

Hi guys,

 

I have a mysql database, in which I want to store some files as longblobs. I'm using PEAR to connect to the DB. So far so good... until the files get over 16MB; the server takes ages to do the query and finally breaks without actually performing it.

 

I'm reading in the file to a variable in memory and outputting that variable in the SQL statement, e.g.

"INSERT INTO table VALUES('$fileContents');"

 

What's weird (and what makes me think it's not a max_allowed_packet problem) is that if I use MySQL's LOAD_FILE() function it works fine:

"INSERT INTO table VALUES(LOAD_FILE('$pathToFile'));"

 

Any ideas?

 

David

[bobleny]

You could try using at the top of the page that is timing out:

set_time_limit(0);

 

The server will still take it's good old time, but it shouldn't stop working on you.

[sirdavidoff]

Hmm... I don't think it's a timeout issue, it seems more to be that the PHP-mysql interface can't handle the data. I'm testing this on my local machine, and a 15MB file takes less than a second, whereas a 16.5MB file hangs the server for a minute or so 

[veridicus]

Is there any reason you're not storing the large text in a plain file on the server?  Storing it in the DB may be somewhat convenient, but you won't want to do any searching on it anyway.

[sirdavidoff]

Unfortunately this is part of a complex application, and it's not really feasible to change the way that the files are stored now. I have found other posts on the internet from people who have had similar problems and ended up giving up though... someone must know the answer!

[shaunrigby]

try changing the field type to text

[per1os]

Chances are either PHP or MYSQL have a memory limit of 16mbs change that to be something higher and you should be good.

 

http://us2.php.net/manual/en/ini.core.php

 

Resource Limits

 

Table H.5. Resource Limits

Name Default Changeable Changelog

memory_limit "128M" PHP_INI_ALL "8M" before PHP 5.2.0, "16M" in PHP 5.2.0

 

 

[sirdavidoff]

Hi frost110 - you're right, it sounds like a PHP memory limit problem and indeed the default memory_limit for my version of PHP (4.4.4) is 16MB.

 

However, I have tried increasing this and it doesn't seem to make any difference. It is interesting to note that the memory_limit variable does not appear in phpInfo() - does this mean that there is no limit set by PHP?

 

Thanks for your help 

[sirdavidoff]

Come to think of it, the problem doesn't occur when I read the file into memory, it happens later when I execute the INSERT statement using $dbConnection->query($sql).

 

Doesn't that suggest that it's not a PHP memory problem?

[per1os]

I would locate your MySQL my.cnf file and check that for the key_buffer size or something similar and make sure it is set to something larger than 16M

 

That is all I could gather from mysql.

[rcorlew]

Check the php.ini for the max post size. Most of the time it is between 8-16 mb. you can change that at the top of your script by using ini_set like this:

 

ini_set('max_upload_size', '300000');

 

ini_set('max_post_size', '300000');

 

You can try different size limits to get different results.

 

They should come right after you opening tag before anyting is sent to the page.