Solved

Import MySql WorPress data base creates error

Posted on 2013-12-15
17
455 Views
Last Modified: 2013-12-29
I usually build Wordpress sites on a server with a different domain than the final destination.
I then export the db in phpMyAdmin, replace the site url, and import into the phpMyAdmin of the final server and domain.
Usually no problem, done it xx times.

Now suddenly I am running into errors either of
"Access denied for user 'xxxxxx'@'localhost'
(that is on the destination server)
or
on the original server where I want to duplicate the db
#1007 - Can't create database 'xxxxx_yyyyy'; database exists

Regarding the original server with the #1007 error:
I see in the sql dump in line 19 and below:

--
-- Database: `xxxxx_yyyyy`
--
CREATE DATABASE `xxxxx_yyyyy` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `xxxxx_yyyyy`;

I tried replacing that with the name of the empty database in which I want to import, but get the same error.

Hope this makes sense to someone out there.

Please consider in your suggestions that I do not use command-line tools.

Thanks!
0
Comment
Question by:Sumukha
  • 7
  • 6
  • 3
17 Comments
 
LVL 38

Expert Comment

by:Tom Beck
ID: 39721283
The way I've done it successfully in the past avoids creating the database through phpMyAdmin.  Instead, I let Wordpress create the database and tables and just import the data. That way the permissions are no longer an issue.

- From the Export tab for the database in question in phpMyAdmin, choose Custom - display all possible options.
- Select the table you want or select all.
- View as text.
- Format: SQL
- I usually uncheck "Display comments...". It clutters up the file unnecessarily.
- Under "Dump Table" choose "data". You only want the data, not the structure.
- Under Data Dump Options leave the defaults.
- Click Go

Copy the resulting SQL statements to your clipboard and paste them into a SQL query window of phpMyAdmin on the destination server and execute.
0
 

Author Comment

by:Sumukha
ID: 39721440
Thanks for posting tommyboy,

How would you duplicate a database then?
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 39721639
It sounds like the destination server does not allow CREATE DATABASE to be executed by your user account.  I see this at shared hosting ISPs a lot, security measure of sorts.

Simply create the database according to the instructions provided by the ISP (usually "do it via CPanel") and remove the line from the SQL dump.  If this is a server fully under your control, you will need to check the permissions for that user account.
0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 39721661
Probably the way you have attempted, by checking the proper boxes in phpMyAdmin to create the database and tables along with the data. But we are talking about Wordpress here. It's been in development for a long time and no doubt has it's own unique requirements for privileges. Any difference in WP versions between the testing server and the live on could spell trouble. Why risk it? Besides, you always want to have a contingency plan for rebuilding a Wordpress site from scratch. Including rebuilding the databases. Wordpress sites can get corrupted by plugins. Back to your situation, even if the table names were different on my destination server, I would only transfer the data and let Wordpress create the database and tables. In that scenario I would instead save the SQL statements generated by the export to a file, open it in a text editor, find and replace the table names, then execute the statements on the destination server's empty tables. I would not attempt to create the entire structure that way. I'm a web developer, not a database expert, so that's my inclination. Maybe a database expert will post with a different idea.
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 39721691
no doubt has it's own unique requirements for privileges

Nope.  If anything, it's the opposite since WP has to be able to run on nearly any deployment.  WordPress has nothing to do with setting database permissions and issues with plugins result in the site getting hacked and defaced, not database corruption.
0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 39721767
Okay Jason, I'll buy that. Do you see anything wrong with my approach though? Have I been doing it wrong all this time? It always works.
0
 

Accepted Solution

by:
Sumukha earned 0 total points
ID: 39722054
Thanks for posting, guys (and hi there Jason),

Now imagine this:
I am now doing the EXACT same steps from another computer and it works.

@ Jason "and remove the line from the SQL dump"

I posted four lines. Which one you are referring to?

Thanx
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 39722100
CREATE DATABASE `xxxxx_yyyyy` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `xxxxx_yyyyy`;

Open in new window


This is all one command (two technically) that doesn't need to be there if the database is already created and selected in phpMyAdmin.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 39722104
Now imagine this:
I am now doing the EXACT same steps from another computer and it works.

Wonderful.  Don't argue with your good fortune?
0
 

Author Comment

by:Sumukha
ID: 39722124
I always want to know WHY things happen.
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 39722134
I have no idea why the same thing works differently on different boxes :/

The only thing I can think of is that you did change something between attempts...that's the only thing that makes sense.
0
 

Author Comment

by:Sumukha
ID: 39722709
Nothing. Njente.

Errors for duplicating the oiginal db (export/ import into empty one), as well as import on the other server into an empty one.
0
 

Author Comment

by:Sumukha
ID: 39738421
Now who gets points?
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 39738424
0
 

Author Comment

by:Sumukha
ID: 39738461
Thanks, Jason. Will do.

Here is something else for you to peek at:
http://www.experts-exchange.com/Web_Development/Miscellaneous/Q_28325248.html
0
 

Author Closing Comment

by:Sumukha
ID: 39744508
It works on a different machine and nobody knows why...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
spacing 5 30
Apostophes in PHP generated form 6 17
increase words that display in widget 6 20
push text up below h2 tag 1 4
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now