Mysql/phpmyadmin privileges


#1

I’m a newbie to hosting, most of the jargon around here, etc, and am having trouble with mysql/phpmyadmin, specifically with importing data for gallery.

I’ve created various mysql dbs in the control panel, and a user with all permissions for all my dbs. Problem: When I log on to phpmyadmin, I’m told I have no privileges to create a new db. When I try to import I get “#1044 - Access denied for user ‘dale’@‘208.97.128.0/255.255.192.0’ to database ‘gallery2’”. Note, gallery2 is the sql export I’m trying to import.

I’m sure I’m doing a few things wrong, but I can’t sort them out if I don’t have access to the db. Any suggestions? Maybe related to the host(s) I’ve allowed my user to access the db from, in the control panel?

Thanks in advance for any help you can provide.


#2

i’m not sure. but maybe DH does not allow mysql user to create db. You can create db only in the control panel.

Save [color=#CC0000]$97[/color] (max discount) on dreamhost plans by using promo code: [color=#CC0000]97CRAZY[/color].


#3

Sounds like the backup of the database (the dump) you did has a create database line at the top. Remove that it will work fine.

Save [color=#CC0000]$97[/color] on Dreamhost plans by using promo code: [color=#CC0000]SRVR97[/color]


#4

Thanks for the suggestion. I tried this, but it made no difference…


#5

As previous posters have pointed out, a machine user on DH (that’s you) doesn’t have “create” authority for MySQL databases. There are also a few other operations that you cannot complete with even “full” permission on DH’s MySQL. These limitations are true whether you attempt to access/manipulate the database via PhpMyAdmin, mysql commands from the shell command line, or via program code.

That said, the first step in trouble shooting a MySQL problem is to make sure you user can connect to the database and complete the operations except those indicated above. You can do this by browsing to the “host” name for the database, and entering your credentials.

If this works, and you can access the PhpMyAdmin on your host, then your user has at least some permissions to access the database. You can confirm the extent of these permissions by clicking the “edit” link next to the database user name in the Control Panel–>Goodies–>Manage MySQL databases screen. If you somehow created a user with insufficient authority to manipulate the database as desired, you can correct that on this screen.

The next step is to inspect the contenets of the SQL backup/dump/etc file you are trying to move into the database, in conjunction with the error messages you receive when you try to import the data, to determine what “task” is causing the problem, and remove that from the “import” file. You can generally replace that step with a different operation, or by using a different method (for instance, the “create database” command previously mentioned.)

As a last resort, you could post here in a message the first few lines of the SQL file you are trying to import, and maybe we can help tell you where the problem lies. :wink: Good Luck!

–rlparker


#6

rlparker: First off, thanks for a very helpful reply. More specifics below.

This is useful information. If I tick “create” in the DH control panel for the user in question, it’s not clear to me what I can and can’t create in phpmyadmin (e.g., dbs? tables?). Apparently creating dbs is not an option.

This was the ticket. I think before I just found the error message cryptic. The solution was (1) remove the first command (CREATE DATABASE XX…) from the dump file, (2) global-replace my friend’s db name with my own db file, (3) delete all the tables in my existing gallery db (all of which were empty anyway), and (4) run the import.

Now I see the whole gallery “structure” on my gallery… but with no images. So I have to figure out how to tell gallery where to find my image files. But that’s a question for another forum, I think. :slight_smile:

Thanks again to all who replied.


#7

You’re certainly welcome, and I’m glad you were able to make some progress. :slight_smile:

That is correct, and that is not readily apparent. The only way, on Dreamhost, to create a database is via the Control Panel–>Goodies–>Manage MySQL screens.

Unfortunately, I’m not all that familiar with Gallery, but IIRC that should be configurable from within the Gallery admin back-end. As you pointed out, getting help for that issue will probably more easily accomplished with a new post appropriately titled so others can see it (or from the Gallery forums :wink: ). At any rate, good luck with it; I’m sure that is going to prove to be a fairly easy fix.

–rlparker