1

I am receiving the following error on a script when it reaches the bind section of this code.

Error:

Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 4294967296 bytes)

Code:

$result = $dbc->prepare('SELECT make, line, model, name, year, image, processor_family, processor_speeds, shipped_ram, max_ram, ram_type, video_card, video_memory, screen_type, screen_size, hard_drives, internal_drives, external_drives, audio_card, speakers, microphone, networking, external_ports, internal_ports, description, driver_link, manufacturer_link FROM laptop_archive where name=?');

$result->bind_param('s', $name);

$result->execute();

$result->bind_result($make, $line, $model, $name, $year, $image, $processor_family, $processor_speeds, $shipped_ram, $max_ram, $ram_type, $video_card, $video_memory, $screen_type, $screen_size, $hard_drive, $internal_drives, $external_drives, $audio_card, $speakers, $microphone, $networking, $external_ports, $internal_ports, $description, $driver_link, $manufacturer_link);

The portion of the database that it is attempting to access has a number of fields to retrieve although none of them contain a large amount of data with the majority being around 20 characters and the description at around 300 characters.

I have had a search which reveals a few answers although none of them have worked for me, the code is running on a VPS with 512MB RAM and has a memory limit of 256MB as set in php.ini.

The amount of memory to be allocated is 4GB which seems extremely excessive for what is in the database, am I missing something stupid here.

halfer
  • 19,824
  • 17
  • 99
  • 186
TheVDM
  • 29
  • 6
  • Did you isolate this section of code and run *only* that, to be convinced these are the lines causing the error? Did you try adding `limit 1` to the end of the query, to make sure only one line is returned from the database, for testing? – grebneke Jan 17 '14 at 22:56
  • 256M is a lot for any PHP web process, nevermind one that is on a VPS of this small size. In general I think 64M is more appropriate and (at a stretch) 128M for CMS systems with lots of plugins. Insert `memory_get_usage` into your code at various points to see where this memory is being gobbled up. – halfer Jan 17 '14 at 22:57
  • It seems more likely that this is part of a loop, and you're hitting the limit because the loop's previous results weren't "cleaned up" so to speak? – Pekka Jan 17 '14 at 22:57
  • Probable dupe: http://stackoverflow.com/questions/11915415/allowed-memory-size-of-67108864-bytes-exhausted – Danack Jan 17 '14 at 22:57
  • Hi, yes, the code is currently running on it's own, I have created a separate php file that calls the database connect file and only runs this section of code. In theory there should only ever be one line that matches and while writing the script there is currently only 3 lines in the table. I have tried your suggestion of adding the limit to the end of the query although this unfortunately makes no difference. – TheVDM Jan 17 '14 at 23:00
  • 1
    Grab [xhprof](http://pecl.php.net/package/xhprof) and run your code through it. It provides surprisingly useful profiling output and should be able to track down all your memory allocations so you can identify the cause of the problem. NB There's also a [gui](http://xhprof.io/) and I highly recommend you install the graphing library it recommends (the name escapes me) as the visualisation can help more than staring at a wall of numbers – Basic Jan 17 '14 at 23:02
  • Next update, I have set the memory limit to 256MB temporarily for this, the memory limit was previously on 64MB where it will be returned. – TheVDM Jan 17 '14 at 23:03
  • (James, btw, if your responses are to specific individuals, address them using their username (e.g. @halfer) otherwise they may not see your reply). – halfer Jan 17 '14 at 23:05
  • @halfer I will do. I decided to remove each section of the query separately to see if any specific column was causing the error, it would appear that the description was causing the problem, checking the database it was stored as longtext, after changing it to blob the error has gone away. I am just going to change the memory limit back to 64MB, hopefully this is resolved. Edit, all is working now with the memory limit set back to 64MB. Thank you all for your input and time. – TheVDM Jan 17 '14 at 23:10
  • 1
    @JamesValentine Please post a few details as an answer, then accept it so others know this has been solved (and those with a similar problem may be able to find the answer without reading all the comments) – Basic Jan 17 '14 at 23:16
  • I think a blob for this would be wrong too - blobs are for "Binary Large OBjectS", and generally not text. How long does the description need to be? Would, say, `VARCHAR(1024)` be sufficient? I seem to recall on one platform (MySQL possibly?) a varchar can be up to 64K, which is fine for most text needs. – halfer Jan 17 '14 at 23:29
  • 1
    @Basic I have now put the answer in place unfortunatly as a new user (can't remember my old login details) I am unable to answer the question for 8 hours and then I am unable to accept my own answer for 24 hours! So I will accept it tomorrow :D – TheVDM Jan 18 '14 at 14:09
  • @halfer Thank you for that information, I have changed the type to varchar with a max of 1024 characters, the description content is on average around 300-400 characters although it's always good to have a little room for extra information. – TheVDM Jan 18 '14 at 14:11
  • @TheVDM There's also an intermediate type for when you want text but don't know how long it will be (known, originally, as the `TEXT` data type) Have a glance at [this article](http://dev.mysql.com/doc/refman/5.0/en/blob.html) for more details. I tend to use this for user-provided articles – Basic Jan 19 '14 at 02:04

3 Answers3

0

normal times it shouldnt take any huge memory because you didnt fetch the data.

$res = $mysqli->query("SELECT * FROM laptop_archive where name=[REPLACE THIS] limit 1")#
$res->fetch_assoc()

please try this one :)

timg
  • 501
  • 2
  • 4
  • 13
  • Not true, mysqli (and PDO too) stores ALL records internally to make them available for the fetchAll() method by default. You can use the `MYSQLI_USE_RESULT ` constant as the last argument of the mysqli_query function to change this behaviour. – Pred Oct 20 '15 at 13:24
0

The issue appears to be caused by the use of LONGTEXT in the database, after changing the type to VARCHAR the issue has gone away.

halfer
  • 19,824
  • 17
  • 99
  • 186
TheVDM
  • 29
  • 6
0

I had this error two days ago. From what I have learned about it the problems lies in the size of the column your selecting from. In my case I was selecting a pdf and I had to column as a long blob which is 4294967296 bytes. The server was allocating that much to grab the file regardless of the size of the file. So I had to change the column to a medium blob and it works fine. So in your case it looks like it would be the image. I would change that to a medium blob and it should work fine. Otherwise you would have to configure your server to allow bigger allocations of grabs.

Alex Beyer
  • 147
  • 1
  • 8