$this->dbutil->backup() produces Error Number: 2008 MySQL client ran out of memory

[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

[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).

[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

[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.

[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

[eluser]WanWizard[/eluser]
If your app needs it, it's functionality like all others...

[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.