Guys im at a loss here, i uploaded my clients site through ftp, changed config file settings to include all thats needed. assigned users with user permissions etc...but im getting this msg every time when uploading / importing this database.

Staff Member

If you have database prefixing enabled, then the username for accessing the database should not be ash7723 but ashley_ash7723 instead. In cPanel under database prefixing, the cPanel username prepends the database username in cPanelusername_dbusername fashion.

Staff Member

Actually, if you wish to restore your databases via the MySQL client (`mysql`) and you are in control of the cPanel account (not just a virtual database user), then I'd login into MySQL as the "dbowner". The dbowner is fairly recent moniker that stems from the DB Mapping feature set (available in 11.28+), however the concept of the dbowner has been present for awhile, albeit not in any obvious sense. The dbowner is the set of credentials that cPanel uses on your behalf when performing MySQL actions. Typically, these credentials are the same as your cPanel credentials. So, if you cPanel username is "dave" and your password was "secretpass!" then you could:

However, it's important to remember that when you are restoring a backups in this manner, you are limited to the privileges set in MySQL for your dbowner. You will only be able to restore databases that 1) you already have explicit permission to or 2) that you have a "prefix" wildcard for (there's a lil' caveat below, but let's disregard it for simplicity). This prefix grant plays an important role. On cPanel systems before 11.28, all cPanel accounts would have a special wildcard grant. So, if my cPanel username was "dave", then in the mysql.db table, I'd have a grant for "dave_%". This prefix grant ensures that my account will always have access to any database name beginning with "dave_". Only my account will have this particular grant; other cPanel accounts will have their own particular grant (based on their account username). With this grant, if your dbowner were to try to create a database on the command line (whether via a restoration or manually) as long as the database's name begins with "dbownername_" then MySQL will perform the requested action.

It is likely that your system has DB Prefixing "on" (the default value; valid for everything I mentioned so far). It is also likely that as long as the databases you are trying to restore are named in accordance to you prefix, then the operation will succeed if you are logged in with your dbowner/cPanel credentials. So, from your example, I have to ask, what is your cPanel account name? Is it "ash7723" or is it "ashley". If you cPanel name is not "ashley", you will need to either rename you databases or get with your hosting provider to make an exception for you. If you go the latter route, you're likely to have some difficulty convincing them that A) it's possible to do so "out-of-the-box" with cPanel 11.28+ B) it's not time consuming or significantly complicated C) worth the exception. This last item is important. If the host is not accustomed to making paradigm changes for individual customers, they aren't likely to start making exceptions now. (just my 2 cents; also, if it were my business, I'd be hesitant too). WHM does allow the root user you arbitrarily map existing databases to any cPanel account, regardless of DB Prefixing or the name of the database (...it simple, but a different topic all together).

So...you may need to rename your databases within your .sql file to make things easy...kind of depends on who you're authenticated as and how that may or may not jive with the prefix on the database names (itemized in the .sql file).

All that said, I have a suggestion. Depending on how many databases you have, I'd probably create them all first in the cPanel interface, and then preform the restore. My reasoning for this has to do with two things:
1) explicit grants for all databases

Unlike in older cPanel installs, in 11.28 all databases created in our UI and API receive an explicit grant. In this way, we remove the dependency on the wildcard grant. That grant is not compatible with the more sophisticated feature sets of DB Mapping. CPanel is moving away from that simplistic paradigm.​

2) the db mapping file

This file is considered the authoritative source itemizing you database resources (database and virtual database users). If you have a MySQL database, proper privileges and all, but that database is not itemized in your db map file, then you will not "see" it itemized in the cPanel UI.

This file will not be updated if you restore your backup from the command line. This file is only governed by the cPanel & WHM UIs and APIs. MySQL is not aware of it. So, if you did perform the backup on the command line successfully and if your current cPanel account never had those databases before, then the db map file will not have the information and thus you would not "see" those databases itemized in the cPanel UI.
​

If you simply create the database before hand, you'll update the db map file and ensure that you have the the right grants in place for MySQL. Then when you restore the .sql file, it will simply populate the databases (if necessary it can drop the "empty" databases and recreate them because you have an explicit grant for that database.)

Regards,
-DavidN

PS. for those interested in more detail about the wildcard grant. If you are on 11.28 or higher, and you disable DB Prefixing, the wildcard grant will be removed for all users. This is done for security purposes.

Like I said earlier, in 11.28, we make explicit grants for all databases when they are created with our UI or API and do not depend on the wildcard grant internally. It only remains as a legacy.

i built the site on my local host using WAMP. I uploaded the files through ftp, then exported my db "ashley_db" through WAMP phpMYadmin, to have a db on my pc named ashley_db.sql (385kb in size)

i then logged into cpanel with the clients user name and password, went to phpmyadmin and imported the above sql file...where its giving me this error all the time.

before exporting the db i made sure all privileges were set to full, even created a db on msql on cpanel, assigned a user to the database and made all privileges max. Still no luck

this client uploaded a word press template / db before this and worked fine. iv developed a new site using joomla but just cant get around this. i'v tried what you said dave, renamed the db but no luck to. the cpanel account name is ash7723, when i started developing the site i created the db named ashley_db?

apologies but im green green when it comes to this and coding, so do ask for your patience

would it help if i supply someone with the cpanel info? ftp info to try see if they can see where im going wrong?

ive asked the client to ask the person who set this hosting up, account on cpanel but apparently they have moved and cant get in touch with them?

Staff Member

Thanks for the info, it helps clarify the situation and hopefully I can help guide you through what you need to do. If I understand you correctly, you've probably already tried this (after reading my previous post), but I'd like to review all the steps to make sure we're clear about what is necessary for you situation.

1) In your cPanel account ("ash7723") go to MySQL Databases page.

2) Create a new database
- The Create Database forum will most likely be an input box, with the text "ash7723_" right before it
- fill in the box with "db" and submit the form
- this should create the database and show you the "full name" of the database : "ash7723_db", not just the unique part ("db").

3) In you development environment, make a copy (or rename) you database to conform with the name itemized in step 2 (ie, "ash7723_db")

4) export the database

5) In the client's cPanel interface, go to phpMyAdmin.

6) Here, you should see the (empty) database you created in step 2 (in the left sidebar)
- probably with the prefix on one line and the unqiue name indented on the line below
- in may just be the "full name" in a drop-down, it kinda depends. only thing that matters is that you see it

7) Attempt to import the the database using the phpMyAdmin import.

The above steps should get your database into a usable state for the cPanel account. At this point, you will probably want to do the following:

1) Create a "database user" within the cPanel interface specifically for use by the Joomla application
2) Assign privileges for that user to the imported database
3) update your Joomla config to use the new user and database

If the database won't import that way, you may consider opening a support ticket with cPanel. If you do, please reference this forum post and PM the ticket number so that I can assist our technicians if necessary.