Loading a dumped MySQL database

See attached php. This is an attempt to load a dumped database into a new MySQL 5 database. The new database has NO tables as of now.

The file db2621629712013-06-02.sql was just produced using dbdump

When run, it produces the attached text file.

What's wrong?
Richard KortsBusiness Owner / Chief DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard KortsBusiness Owner / Chief DeveloperAuthor Commented:
This might help. I was told that the host was "localhost/tmp/mysql5.sock".

I've never seen anything like that; maybe that's an error?

Is there a way to get an error message or number?
The TXT file contains the help text of the MySQL command line interface.

Typically, a command-line utility will output it's help text when the parameters it receives are invalid or syntactically incorrect.

One thing I can see that is wrong with your system() call is the "-p" parameter. There isn't supposed to be a space inbetween the "-p" and the actual password.

Also, check that $backupfile actually exists. It isn't an absolute path so you have to make sure your .sql file is in teh same directory as the executing php script.

So try it like this:
system("mysql -h $dbhost -u $dbuser -p$dbpass $dbname < $backupfile");

If that doesn't work, the first diagnostic step is to look at the actual command that is being executing and make sure it is valid. Do something like:

echo "EXECUTING:  " . "mysql -h $dbhost -u $dbuser -p$dbpass $dbname < $backupfile";

And look at what the results are. Maybe there's a typo in one of your variables or something else silly is going on that will become clear when you see what the actual command that was executed was.
Richard KortsBusiness Owner / Chief DeveloperAuthor Commented:
To Frosty555,

I did all that. When I got the parameters right, the page of text went away. But it still does nothing.

So I tried the echo "Executing" etc.

Everything looks right.

It's amazing that something so simple is SOOOOOO hard.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

The dump file if it does not include use/create database directives,your first tasks to create the database
Create database databasename
You should have create table tablename directives.

Once you do tht using mysql on the command line

You can run
Mysql -u username -ppassword databasename <mysqldumpfiel.sql

Get phpmyadmin.

What or how did the dump of the database was made?
Richard KortsBusiness Owner / Chief DeveloperAuthor Commented:
To arnold

The dump file was made using mysqldump. If was run on a MySQL version 4 database.

I should have mentioned this earlier,

The whole project is to convert from MySQL 4 to MySQL 5 as the web host requires that by like tomorrow.

I created the MySQL 5 database earlier; it exists but no tables.

I looked in the dump file; it creates each table before it loads it.

Is there a compatibility issue with dump files between MySQL 4 & MySQL 5?
Richard KortsBusiness Owner / Chief DeveloperAuthor Commented:
To all.

I have phpmyadmin,. I have used it for years

I have been trying most of the day to do export / import. That doesn't work either (the dump file is like 280MB).
Does the mysqldump used for a single database backup or multiple?

You need to create the database.
Then use mysql -u username [-ppassword] databasename < dumpfile.

grep -i 'create table' dumpfile

You have to make sure your system as enough space and has the engines you used in the mysql 4.

Do you still have access to the old databse server?

It should take a while depending n whether you have binary logging (transaction) enabled.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Maybe it's hitting a size or time limit. Try with one table instead of the whole database at once.
Richard KortsBusiness Owner / Chief DeveloperAuthor Commented:
We have a test database that is MUCH smaller. I will try that today.

But I don't think so; the MySQL command finishes in one second.
Richard KortsBusiness Owner / Chief DeveloperAuthor Commented:
To arnold,

It's the same server for BOTH databases; same host.

It's seems clear to me that something simple is being overlooked.
If it is on the same server, make sure you do not have a "use databasename;"
in the dump file if you are not restoring.

if you already have a mysql 4 database loaded, you need to run the mysql_upgrade to convert the mysql 4 database files to mysql5 files.
Richard KortsBusiness Owner / Chief DeveloperAuthor Commented:
Where is the documentation on mysql_upgrade? That would likely solve ALL problems.

Man mysql_upgrade
What is the issue that you are trying to resolve with the dump.
Richard KortsBusiness Owner / Chief DeveloperAuthor Commented:
I ran the upgrade. I have to do it from php.

So I did this: system("mysql_upgrade --verbose -h $dbhost -u $dbuser -p$dbpass");

It produces this. I have NO CLUE what this means:

Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck' with connection arguments: '--host=localhost' Running 'mysqlcheck' with connection arguments: '--host=localhost'

There are several databases. How can it know which one I'm trying to upgrade?
You need to run this on the command line in the shell. not sure why think you have to run it from PHP? going through PHP would mean you are bound by the webserver/etc. and may be using more resources than needed.
if you do not specify, it will check all and and repair/fix those that need to be fixed/repaired.
Richard KortsBusiness Owner / Chief DeveloperAuthor Commented:
To All,

Just to eliminate the possibility of size issues, I dumped our "dev" (developer) database; dump file is 23 MB (as opposed to 275 MB fr the "real" database.

Same problem; the load DOES NOT work.  It does NOTHING.
There are different ways to get this.  The issue that you are trying to do it via a web browser/php interface may suggest that your limits on the web browser could adversely interact with the issue.

usually when using PHP, you have to upload the file first into a location that php/web/apache has access to and which will be used as input to the command.
If you place a file in some directory in the assumption that running the PHP will get to the file and be able to load the data you are mistaken.  You likely do not have a check to see whether your PHP script can actually open the file for viewing. I.e. when you run the PHP code, the message from the file system is "Access denied" and the script falls through to the next command seemingly completing the task.
Richard KortsBusiness Owner / Chief DeveloperAuthor Commented:
To arnold:

Result of running the upgrade from the command line:

Note, I dummied out the password.

(uiserver):u51003339:~ > mysql_upgrade --verbose -h localhost -u dbo274353673 -pabc123 Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--host=localhost'
Running 'mysqlcheck' with connection arguments: '--host=localhost'
mysqlcheck: Got error: 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOR UPGRADE' at line 1 when executing 'CHECK TABLE ...  FOR UPGRADE'
FATAL ERROR: Upgrade failed
(uiserver):u51003339:~ >
You need to run mysql_upgrade with a higher level user
I.e. root.
The error you get deals with the user not having rights to the mysql database (emissions, configuration)
Richard KortsBusiness Owner / Chief DeveloperAuthor Commented:
To arnold,

We ran the db load from the command line.

Got this:  

OK - running . . .

 mysql --verbose -h localhost  -u dbo473251127  -pabcdef db473251127 < /homepages/46/d261861815/htdocs/dev/db2743536732013-06-03.sql

Gives me . . .

ERROR 1045 (HY000): Access denied for user: 'dbo473251127@localhost' (Using password: YES)

We had earlier tried it with the host just like the hosting company specified; localhost/tmp/mysql5.sock. It said invalid host.

I think it's time to give this up, the path we are going down is NOT productive & is wasting WAAAAAY to much time.
Richard KortsBusiness Owner / Chief DeveloperAuthor Commented:
There is no "best solution". The problem is NOT solved.
What are the contents of the dump file?
On the command line you are not specifying the database which is being "restrored"
If there is no use directive in the dump file ... not sure whether the error you received is a consequence of a missing use directive.

The user you are using might not have rights to create a database, is you use the command
mysql -u username -ppassword databasename
are you able to login?
if so, you need to add databasename to the mysql command above while making sure that the dump  file you use does not have a "use thisdatabasename" directive.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.