+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: $this->dbutil->backup() produces Error Number: 2008 MySQL client ran out of memory (/showthread.php?tid=34981)
$this->dbutil->backup() produces Error Number: 2008 MySQL client ran out of memory - El Forum - 10-15-2010
[eluser]Peter Bowen[/eluser]
Greetings and thanks in advance for your attention to my problem.
I'm backing up selected tables from my database and saving them to individual files (there is a good reason behind this).
Code:
$tables = array('a_table','another_table_etc');
foreach($tables as $table){
//set the preferences and choose the table
$prefs = array(
'tables' => array($table),
'format' => 'txt',
'add_drop' => TRUE,
'add_insert' => TRUE,
'newline' => "\n"
);
$backup = $this->dbutil->backup($prefs);
//write to file
write_file("$directory$table.sql", $backup);
}I get this error: Error Number: 2008 MySQL client ran out of memory.
I've tried the following without any luck:
1. increasing the memory limit for the script;
2. $this->db->save_queries = false;
3. turning caching on and off;
I'd appreciate your thoughts on the matter.
Thanks
Pete
$this->dbutil->backup() produces Error Number: 2008 MySQL client ran out of memory - El Forum - 10-15-2010
[eluser]WanWizard[/eluser]
When you run a query (any query), the MySQL driver keeps the entire resultset in memory.
In case of a backup, I can imagine that you run out of memory if you do a "SELECT *" on a large table.
There is an option for the server daemon that makes that the result set is stored server side, but it has a performance impact on both the server and the applications connecting to it, so I advice against using it.
Either make the backup server side using the tools provided, or rewrite the backup() method so it works incremental (for example, 1000 rows at the time).
$this->dbutil->backup() produces Error Number: 2008 MySQL client ran out of memory - El Forum - 10-19-2010
[eluser]Peter Bowen[/eluser]
Thanks for the reply.
It looks like I could try to free the memory in the _backup function deep down in the heart of the code, but I'm a little reluctant to do that. Maybe I'll end up having to write a backup function from scratch that lets me free the memory after each query.
Cheers
Pete
$this->dbutil->backup() produces Error Number: 2008 MySQL client ran out of memory - El Forum - 10-19-2010
[eluser]WanWizard[/eluser]
You could copy the method from the db_util library to your own library, and then modify it so that it processes for example 1000 rows in a loop, to avoid this problem.
$this->dbutil->backup() produces Error Number: 2008 MySQL client ran out of memory - El Forum - 10-19-2010
[eluser]Peter Bowen[/eluser]
Yip,
I looked at it and it makes sense to do it that way. But who wants to write a db utility when there are far more fun things to code
$this->dbutil->backup() produces Error Number: 2008 MySQL client ran out of memory - El Forum - 10-19-2010
[eluser]WanWizard[/eluser]
If your app needs it, it's functionality like all others...
$this->dbutil->backup() produces Error Number: 2008 MySQL client ran out of memory - El Forum - 10-19-2010
[eluser]Peter Bowen[/eluser]
It's a MySql server, server version: 5.1.48-community-log.
The backup works fine through phpMyAdmin or the command line. The challenge is trying to do it through a script.