Import of database failing with Access Denied in MySQL Workbench

I am trying to import a databases from a Dev server to ITG. I click on Data Import/Restore and then Import from a self contained file, select the Default Target Schema then "Start Import". I am getting Access denied each time:

Running: mysql.exe --defaults-file="c:\users\james~1.whi\appdata\local\temp\tmpfja94q.cnf"  --host=subdomain.domain.com --user=EMEA --port=1531 --default-character-set=utf8 --comments --database=mktit < "C:\\MySQLDumps\\Volume_DEV_4ITG_Dump20150403.sql"
ERROR 1044 (42000) at line 1: Access denied for user to database 'wordpress'

Operation failed with exitcode 1

I am able to export the database using the same user. How can I overwrite the existing database?

Thanks
V0LUMEAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Rob_JeffreyIT/ProgrammingCommented:
What is line 1 of the file "C:\\MySQLDumps\\Volume_DEV_4ITG_Dump20150403.sql"?  Perhaps it is drop if exists command?  Perhaps the user doesn't have permission to drop the database?  Or perhaps it is creating the database?  Removing the line may solve the problem if the user has the right permissions to load the data and make tables in the select database.
0
V0LUMEAuthor Commented:
when I exported the DB from Dev I edited the DB name in the SQL file with a text editor as the name is different on the destination. I have also tried a search and replace of CREATE with USE. The server is managed by a third party which we don't have access to. From what I know we have successfully imported the database a few times.

The first line is this:

CREATE DATABASE  IF NOT EXISTS `wordpress` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci */;

Should I delete that?
0
Rob_JeffreyIT/ProgrammingCommented:
Yes, if the database name has changed, then that will try to create a new one called 'wordpress'.  There should be another command shortly after that one in the file saying 'use wordpress' that should be changed to the new database name as well.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

V0LUMEAuthor Commented:
I removed the first line and modified the db name. Drop is now failing:

14:14:59 Restoring C:\MySQLDumps\Volume_HP_DEV_4ITG3_Dump20150403.sql
Running: mysql.exe --defaults-file="c:\users\james~1.whi\appdata\local\temp\tmp6coh4h.cnf"  --host=subdomain.domain.com --user=user--port=1531 --default-character-set=utf8 --comments --database=mktitmerlin < "C:\\MySQLDumps\\Volume_HP_DEV_4ITG3_Dump20150403.sql"
ERROR 1142 (42000) at line 23: DROP command denied to user 'user'@'x.x.x.x' for table 'sprout_10_commentmeta'

Operation failed with exitcode 1
14:15:03 Import of C:\MySQLDumps\Volume_HP_DEV_4ITG3_Dump20150403.sql has finished with 1 errors
0
Rob_JeffreyIT/ProgrammingCommented:
Sounds like the user you are using has very limited permissions on this database.

If you are not able to elevate the permissions for this user, or use another user, you will need to modify the import file to avoid all commands that the user isn't permitted to use.  The 'drop' looks like the first to go.  You may have to change the drop to truncate - if they have that ability.  You will then need to delete or comment out the 'create table' lines as well.  
Perhaps you should test to make sure this user has the ability to insert and delete on the tables within that database first - if the user only has read then there isn't much else to do with that user account.
0
V0LUMEAuthor Commented:
I seem to be able to insert and delete stuff into the tables.

I'm going to modify the script now.
0
V0LUMEAuthor Commented:
It doesn't seem to recognize Truncate:

15:04:05 Restoring C:\MySQLDumps\truncate.sql
Running: mysql.exe --defaults-file="c:\users\james~1.whi\appdata\local\temp\tmpkizmit.cnf"  --host=hostname.com --user=mktitmerlinweb --port=1531 --default-character-set=utf8 --comments --database=mktitmerlin < "C:\\MySQLDumps\\truncate.sql"
ERROR 1064 (42000) at line 23: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IF EXISTS `sprout_10_commentmeta`' at line 1

Operation failed with exitcode 1
15:04:09 Import of C:\MySQLDumps\truncate.sql has finished with 1 errors
0
V0LUMEAuthor Commented:
Commenting out Drop throws an error about Create being denied:

15:01:19 Restoring C:\MySQLDumps\Comment Out Drop.sql
Running: mysql.exe --defaults-file="c:\users\james~1.whi\appdata\local\temp\tmpgccqm7.cnf"  --host=hostname.com --user=mktitmerlinweb --port=1531 --default-character-set=utf8 --comments --database=mktitmerlin < "C:\\MySQLDumps\\Comment Out Drop.sql"
ERROR 1142 (42000) at line 26: CREATE command denied to user 'mktitmerlinweb'@'IP' for table 'sprout_10_commentmeta'

Operation failed with exitcode 1
15:01:23 Import of C:\MySQLDumps\Comment Out Drop.sql has finished with 1 errors
0
Rob_JeffreyIT/ProgrammingCommented:
Instead of replacing the
DROP TABLE IF EXISTS `{table}`;

with
TRUNCATE TABLE IF EXISTS `{table}`;

You should have simply
TRUNCATE {TABLE};

The truncate doesn't understand 'if exists'.
0
Rob_JeffreyIT/ProgrammingCommented:
If you can't drop the table you won't be able to create it either.  Switching to truncate only empties the table, so as long as the structure is the same, you will be able to load data into the empty table(s).

If you do need to change the table structure, then you will need to look into getting higher permissions.
0
V0LUMEAuthor Commented:
Ok to clarify I did a search and replace of "DROP TABLE IF EXISTS" with "TRUNCATE"

ERROR 1050 (42S01) at line 26: Table 'sprout_10_commentmeta' already exists
0
V0LUMEAuthor Commented:
can I upload the file for you to have a look? Its not ideal from a security perspective, but I have a deadline to meet.
0
Rob_JeffreyIT/ProgrammingCommented:
At this point I would reach out to the hosting company to resolve your permissions issues.  Normally a shared hosting plan provides full access to a database - so you should have the ability to drop, create and update tables within that database.

If they are not willing to increase your permissions, they should be able to apply the update you are trying to upload.

I believe that would be faster than me attempting to hobble something that may work for you.  Especilly if you are under a deadline.
0
V0LUMEAuthor Commented:
Its bit of a funny situation. We normally host these sites on our own infrastructure, but the client wanted it hosted on theirs. We can only gain access by VPN and a key fob. The client has high security policies (big household name company). The person that normally does the import is on holiday till Tuesday and we have to deliver the app tomorrow! He gave me the credentials which he used last time, but didn't explain what he did to the file to complete the import.
0
V0LUMEAuthor Commented:
I just reached out to another support company I know and they asked me to run this command to find out what permissions I have:

SHOW GRANTS FOR CURRENT_USER;
'GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `mktitmerlin`.* TO \'mktitmerlinadm\'@\'%\''

Does this not indicate I have full permissions?
0
V0LUMEAuthor Commented:
We managed to do the import! It was the locked tables statement. After we removed that it worked.
0

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
Rob_JeffreyIT/ProgrammingCommented:
Glad to hear you were able to solve it!

Kind of a facepalm moment though.  ;)

Sorry I didn't pick up on that right off.
0
V0LUMEAuthor Commented:
Yeah I know. That is what happens when they get a Windows Sys Admin to manage MySQL Databases with no training. I have limited knowledge!

Thanks for your help.
0
V0LUMEAuthor Commented:
We solved it ourselves
0
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.