Welcome Guest, Not a member yet? Register   Sign In
Backup problem: "Fatal error: Allowed memory..."
#1

[eluser]ministry[/eluser]
Hi.
I'm trying to backup my entire database, but I get this message:
Quote:Fatal error: Allowed memory size of 25165824 bytes exhausted (tried to allocate 10755665 bytes) in /web/htdocs/www.mydomain.it/home/system/libraries/Zip.php on line 160

I did my last working backup yesterday; so it seems the last inserted queries have exceded the allowed database size; at the moment its size is about 2.5mb. How can I increase the limit?

By the way, my code is very simple:
Code:
$this->load->dbutil();
$this->load->helper('download');
$namefile="backup_database.zip";
$prefs = array(
         'tables'      => array(),              // Array of tables to backup.
         'ignore'      => array(),           // List of tables to omit from the backup
         'format'      => 'zip',             // gzip, zip, txt
         'filename'    => $namefile,            // File name - NEEDED ONLY WITH ZIP FILES
         'add_drop'    => TRUE,              // Whether to add DROP TABLE statements to backup file
         'add_insert'  => TRUE,              // Whether to add INSERT data to backup file
         'newline'     => "\n"               // Newline character used in backup file
         );

    $backup = $this->dbutil->backup($prefs);
    force_download($nomefile, $backup);
#2

[eluser]AgentPhoenix[/eluser]
It's not that the database is too large, but that PHP's memory limit is too low in order to execute the script. You can change it using ini_set() or change your PHP's php.ini file to set a higher memory limit.
#3

[eluser]ministry[/eluser]
ini_set() did the trick!
Thank you very much! Smile
#4

[eluser]TheoR74[/eluser]
I am having a problem like this.

I have a script running at the prompt accepting data and writing it to a database. It seems if I comment out the db->insert commands, it doesn't have any memory issues, only on those commands. So, I'm wondering, is there something in that command that isn't freeing up memory? Or a variable that isn't getting cleared out where the multiple insert commands are too much for it? I looked at the db->insert code but kinda got lost.
#5

[eluser]mddd[/eluser]
I have had memory problems with scripts inserting large amounts of data into the database.
I found that the problem was, that CI keeps record of all the queries it does. This is so that you can see them all when you turn on the Profiler.
If you turn on this 'remembering of queries', you can save memory. The more queries you run, the more effect it will have.
Turn it off using:
Code:
$this->db->save_queries = false;

Also, it is a good idea to free up database results when they're no longer needed:
Code:
$query = $this->db->query('SELECT name FROM some_table');
$row = $query->row();
$query->free_result(); // The $query result object will no longer be available
#6

[eluser]TheoR74[/eluser]
I added that save_queries line of code and that did make a HUGE difference!

Thanks alot.
#7

[eluser]mddd[/eluser]
You are welcome. Some more tips that might help you:

Try combining INSERT statements. For instance:
Code:
INSERT INTO table SET col1='value1', col2='value2'
INSERT INRO table SET col1='value3', col2='value4'
can also be written as:
Code:
INSERT INTO table (col1, col2) VALUES ('value1','value2'), ('value3', 'value4')
It will be quicker because you are sending less separate queries to the database.

If you are writing a LOT of information, it can often be quicker to write all the data to a file first, and then load the file with the LOAD DATA INFILE command. But this does require 'file' rights in Mysql. If you are on shared hosting you may not have those rights.
#8

[eluser]Buso[/eluser]
nice tips, thanks for sharing Smile




Theme © iAndrew 2016 - Forum software by © MyBB