mysqldump generates 0kb file

[coolT]

I'm trying to use mysqldump in combination with php system command. (Let's say myphpadmin and shell command is not an option).

Upon executing the command, the file mybackup.sql is created in the folder where I run the script from, however the backup file contains nothing. (No, my database is not empty.)

The directory is moded to r+w+e (otherwise no file would be created).

I'm using:
mysqldump -u " . $dbUsername . " -p " . $dbPassword . " " . $dbDatabase . " > " .$dbFilename;

return value of executing this command is 2 - but I cannot find any place on the web where it explains to me what the two means.

Anybody have any suggestion?

Thanks

[nemonoman]

This is confusing.

Are you absolutely sure the file is empty?

A successful dump should contain SOMETHING. For example, here's the mysqldump of a database with no tables, no nothing:

-- MySQL dump 9.10

-- Host: localhost Database: notables

---

-- Server version 4.0.18-standard

So even for an empty database the file has something.

Suppose your parameters were wrong:

For example, if by chance $dbDatabase were empty, the content of the file should be something like

Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

So by nothing do you mean 0 bytes? No content?

[coolT]

Yup, Zip, zero, nada written to the file.

I desided to run the mysqldump through unix command window. My unix account did not have any permission on the mysql database I try to dump so I got error code 2 - permission denied (so the error code 2 mystery is solved).

However, using php, I connect to the database using the proper username and password, so that user should be allowed to execute the dump. But, I guess it is the apache user that executes the command/execute statement and it is possible therefore I get error code 2?! Does that make sense? If yes, any suggestion as to how I work around this without bugging the system administrator too much?

Copy and paste some other routine that queries the database and write out a textfile with create database and insert statement?

Any suggestions?

Thanks.

[bubblenut]

Just because a user has access to the database does not mean they have access to do everything within the database. Here is a desc on the mysql user table for MySQL 4.0.18

+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | varchar(60) binary                |      | PRI |         |       |
| User                  | varchar(16) binary                |      | PRI |         |       |
| Password              | varchar(16) binary                |      |     |         |       |
| Select_priv           | enum('N','Y')                     |      |     | N       |       |
| Insert_priv           | enum('N','Y')                     |      |     | N       |       |
| Update_priv           | enum('N','Y')                     |      |     | N       |       |
| Delete_priv           | enum('N','Y')                     |      |     | N       |       |
| Create_priv           | enum('N','Y')                     |      |     | N       |       |
| Drop_priv             | enum('N','Y')                     |      |     | N       |       |
| Reload_priv           | enum('N','Y')                     |      |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     |      |     | N       |       |
| Process_priv          | enum('N','Y')                     |      |     | N       |       |
| File_priv             | enum('N','Y')                     |      |     | N       |       |
| Grant_priv            | enum('N','Y')                     |      |     | N       |       |
| References_priv       | enum('N','Y')                     |      |     | N       |       |
| Index_priv            | enum('N','Y')                     |      |     | N       |       |
| Alter_priv            | enum('N','Y')                     |      |     | N       |       |
| Show_db_priv          | enum('N','Y')                     |      |     | N       |       |
| Super_priv            | enum('N','Y')                     |      |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     |      |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     |      |     | N       |       |
| Execute_priv          | enum('N','Y')                     |      |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     |      |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     |      |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') |      |     |         |       |
| ssl_cipher            | blob                              |      |     |         |       |
| x509_issuer           | blob                              |      |     |         |       |
| x509_subject          | blob                              |      |     |         |       |
| max_questions         | int(11) unsigned                  |      |     | 0       |       |
| max_updates           | int(11) unsigned                  |      |     | 0       |       |
| max_connections       | int(11) unsigned                  |      |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+

You may well have a lot of the privileges (enough to do reads, writes, creates etc) but you most certainly will not have full permissions. Contact your service provider and find out exactly what permissions your db user has. I believe you need Process_priv and File_priv but don't quote me on that :rolleyes:
HTH
Bubble

[Drakla]

Just in case it ends up you're having the same problem I did doing it, try this first:

Make sure you use the path to mysqldump, just use phpinfo to find it, then have NO space between the -u and username, same with the password. You could capture the output and echo it to make sure the command works, then try shoving it in a file, or otherwise write the captured output to a file, which is what I ended up having to do. This was on a me2uweb server with Linux hosting

$pathToMySQL=$_SERVER['HTTP_HOST']!="localhost" && $_SERVER['HTTP_HOST']!='127.0.0.1' ? '/usr/local/mysql/bin/' : 'd:/mysql/bin/';

$dumpCommand=$pathToMySQL.'mysqldump';
	$dumpCommand.=" -e -f -u$user -p$password";	// -f to force dump even if errors
	$dumpCommand.=" $database $tables";

$results=`$dumpCommand`;

// echo or write it out

[coolT]

Thank you everyone for helping me out - it worked out in the end.

What did the trick?

The comment about making sure there is no space between -u and usename, well the same goes for -p and password.

Cheers