asked on

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

Rob_Jeffrey

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.

V0LUME

ASKER

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?

Rob_Jeffrey

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.

V0LUME

ASKER

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

Rob_Jeffrey

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.

V0LUME

ASKER

I seem to be able to insert and delete stuff into the tables.

I'm going to modify the script now.

V0LUME

ASKER

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

V0LUME

ASKER

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

Rob_Jeffrey

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

Rob_Jeffrey

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.

V0LUME

ASKER

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

V0LUME

ASKER

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.

Rob_Jeffrey

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.

V0LUME

ASKER

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.

V0LUME

ASKER

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?

ASKER CERTIFIED SOLUTION

V0LUME

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob_Jeffrey

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.

V0LUME

ASKER

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.

V0LUME

ASKER

We solved it ourselves