Are you planning to share a locally-developed database from your web server? Maybe you’re migrating a database for WordPress or another content management system (CMS) for development purposes. Whatever the case, phpMyAdmin has a simple process for importing a database file after creating the database in cPanel.
In this tutorial, we will show you how to import a MySQL Database using phpMyAdmin in cPanel.
Under Databases, open phpMyAdmin by clicking the phpMyAdmin icon.
On the left, select the database that you will be working with.
Click Import in the top menu.
Under File to Import, click Browse and select the backup file you previously created (it will either be a .sql .zip or .tar.gz file).
Click Go at the bottom right to import the database file.
When the database has been imported successfully, you should see a message at the top of the page similar to: Import has been successfully finished, ## queries executed.
Congratulations, you have just imported a database!
Troubleshooting
#1044 – Access denied
If you get the following error when importing a database via PhpMyAdmin, you will need to edit the SQL file you’re trying to import.
CREATE SCHEMA IF NOT EXISTS `DataBaseName` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; MySQL said: Documentation #1044 – Access denied for user ‘userna5’@’localhost’ to database ‘DatabaseName’
PhpMyAdmin does not allow you to CREATE databases due to security reasons. In order to get your SQL file to load you will need to remove the CREATE SCHEMA line at the beginning of your code. You can use our MySQL – 1044 Access Denied Error Message tutorial and a text editor such as Notepad++ or Atom.io to accomplish this.
50+ MB databases
The process for uploading databases over 50 MB differs depending on your InMotion Hosting plan.
Note: If you need assistance with importing the process, you can email our technical support a verified ticket request with the file and database name.
Hello! If you’d like to download a database, please see our article on exporting your database (this one uses phpMyAdmin), or the ‘retrieve a standard database’ section in our guide to cPanel backups. If you’ve exported the file but still need to download it, you can use cPanel’s file manager, or FTP. Hope that helps!
My database is over 50mb, and the alternative directions are confusing for a novice. (i.e. “simply import the database via SSH” makes no sense, and the “install a phpMyAdmin instance” has a deadlink in step 2). So now I’m stuck.
Thanks for your question regarding the #1071 error. The problem is literally what the error is telling you. You can see a great explanation of the issue here. They also link to the specific MYSQL documentation that refers to prefix limits. Please review this definition and then review your code. If you require further assistance, then I recommend that you contact a seasoned database programmer.
I have my database set up and the table created. When trying to import the database (txt.gz file), I get this error message.
#1064 – 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 ‘2010/12/25’, ‘*’, ‘BJORN – NHSB 2346883’, ‘BJORN’, ‘*’, ‘FAMKE DIEUWKE FAN IT D’ at line 2
Can you help direct me to the solution?
Thank you!
Correcting this error requires the knowledge of MySQL syntax for the version of MySQL that your server is using. Typically, I refer similar requests for assistance to the official MySQL documentation to reference with the syntax of the code indicated in the error message. However, there is not enough information here to make that assessment to know which version you are using. You can however find their documentation here, you will just need to select the correct version that matches yours.
MySQL said: Documentation
#1064 – 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 ‘TYPE=MyISAM AUTO_INCREMENT=3’ at line 5
Here is a helpful link to the official documentation on using AUTO_INCREMENT. It provides additional details and examples of how to format the attribute.
I am trying to import a .ods vile using import in PhP Admin and i get an error no data in column T, yet my data is only in columns A-S. How to solve this error?
I’m sorry to see that you are experiencing difficulty in using phpMyAdmin to import your database from a .odsOpen Document Spreadsheet. According to phpMyAdmin documentation, there are specific formatting conditions that need to be met in order for the import to successfully complete. I suggest reviewing the details at that link, modifying your Spreadsheet accordingly, and then reattempting the import. I hope this helps!
Make sure to clear your browser cache and check the list of databases in the MySQL Databases list in cPanel. If the problem persists, then please contact our live technical support team so that they can check it directly at the server level. They will need for you to verify the account and provide more information about the database that is in question.
You can upload large .sql file to phpmyadmin using two method.
1.First find your main php.ini file.
In php.ini file, edit following thing for upto 2GB file size import or upload. max_execution_time = 1800 // equals to 30 minutes max_input_time = 1800 // equals to 30 minutes memory_limit = 2001M // equals to 2 GB upload_max_filesize = 2000M // equals to 2 GB post_max_size = 2001M // equals to 2 GB After edit save that file.
Hello,
I am trying to take my test site and move it to my live site. I get this error for importing the database sql file for the live.
Error SQL query: --
-- Dumping data for table `wp_blogs`
--
Your error seems to be indicating that the table already contains the data you are importing. I recommend checking your tables to ensure they are empty before importing content in.
Thanks for your response. The problem is that I cancelled my hosting plan and requested for my backup. The website and database is no longer up, so there’s no way to re-export. i only have the sql file with me.
I’m sorry to see that this is the case. If you’re unable to attempt to re-export that database, then the data may not be able to be recovered properly and would need to be rebuilt manually. I would contact your previous host and see if they can provide you a full cPanel backup of your account. Or if they can provide a backup of that database. With either of those, you can attempt to restore the site. Unfortunately, if that is not an option, there’s not much else you can do. I hope this helps!
Hi, I am new to dealing with sql databases, I tried to import one and got message below. Any help would be greatly appreciated
Error SQL query:
OP TABLE IF EXISTS `wp_options`;
MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OP TABLE IF EXISTS `wp_options`' at line 1
Bukola, this error indicates that the permissions for your MySQL user ‘id1616023_bfilmz’@’%’ are not set to allow this import. You will need to either adjust these permissions, or contact your current web host for assistance with doing so.
I am new in PHP my admin as I was working on localhost and created some views in my database but now every time I copy my database file to another system then that views are not working and it was showing error “view name ” does not exist
Is there any wayout to get that views on another machine through that database file…
Abhilash, I would recommend that when you are exporting your database from your localhost you also enable a DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement. This may vary based on the software you are using. Also, please keep in mind that database views may not be supported on our business class / reseller hosting servers.
I am trying to import a joomla database from a backup files stored on my desktop to cpanel using phpmyadmin. Question I have is when you click on the browse where to navigate into which folder is the sql file. I see lots of sql files mostly 1 kp and some max 40 kb
Sorry for the issue with the database import. You will need to determine you Joomla database file in your Joomla installation files. You need to find the config.php file and open it up. It will tell you the exact name of the database file. Generally, the database file will be bigger than 40kb.
If you have any further questions or comments, please let us know.
Hi, I am cloning a wordpress site for dev / sandbox.I manage to export the database with no problemand when I tried to import the db and I got this message:
Error
I think that the error has more to do with the “no database selected” message. When you export a database, make sure it includes all of the tables. You should simply create a BLANK database (no tables) through MySQL in cPanel. Then when you import the tables from your old database, you would select the blank database.
I hope this helps to answer your question, please let us know if you require any further assistance.
Installed an Open Source Shopping Cart on a machine with WAMP :
Setting up Successful !!!
( Made changes to the sample Shopping Site )
Installed wamp on another machine :
Tried to restore the database (to import the data from the additions I did on the other machine)
Database import fails !!!
Split script with data and structure separately:
Structure is successful !
Errors are in the data, and these are with the sample data (not any data that I had added)
Errors that I identified were,
1. Error #1064 reported :
#1064 – check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Cart TTL (days)’, ” at line 53
Cause identified as “#” in line above
2. Replaced “#” with “No” and then error is reported :
Error
SQL query:
INSERT INTO `xc_country_translations` (`label_id`, `id`, `country`, `code`) VALUES
(1, ‘AF’, ‘Afghanistan’, ‘en’),
(2, ‘AX’, ‘Åland Islands’, ‘en’),
(3, ‘AL’, ‘Albania’, ‘en’),
(4, ‘DZ’, ‘Algeria’, ‘en’),
(5, ‘AS’, ‘American Samoa’, ‘en’),
(6, ‘AD’, ‘Andorra’, ‘en’),
(7, ‘AO’, ‘Angola’, ‘en’),
(8, ‘AI’, ‘Anguilla’, ‘en’),
(9, ‘AQ’, ‘Antarctica’, ‘en’),
(10, ‘AG’, ‘Antigua and Barbuda’, ‘en’),
(11, ‘AR’, ‘Argentina’, ‘en’),
(12, ‘AM’, ‘Armenia’, ‘en’),
(13, ‘AW’, ‘Aruba’, ‘en’),
(14, ‘AU’, ‘Australia’, ‘en’),
(15, ‘AT’, ‘Austria’, ‘en’),
(16, ‘AZ’, ‘Azerbaijan’, ‘en’),
(17, ‘BS’, ‘Bahamas’, ‘en’),
(18, ‘BH’, ‘Bahrain’, ‘en’),
(19, ‘BD’, ‘Bangladesh’, ‘en’),
(20, ‘BB’, ‘Barbados’, ‘en’),
(21, ‘BY’, ‘Belarus’, ‘en’),
(22, ‘BE’, ‘Belgium’, ‘en’),
(23, ‘BZ’, ‘Belize’, ‘en’),
(24, ‘BJ’, ‘Benin’, ‘en’),
(25, ‘BM’, ‘Bermuda’, ‘en’),
(26, ‘BT’, ‘Bhutan’, ‘en’),
(27, ‘BO’, ‘Bolivia, Plurinational State of’, ‘en’),
(28, ‘BQ’, ‘Bonaire, Sint Eustatius and Saba’, ‘en’),
(29, ‘BA’, ‘Bosnia and Herze[…]
MySQL said:
]#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘CREATE TABLE `xc_coupons` (
`id` int(10) NOT NULL,
`code` char(16) COLLATE u’ at line 258
Not sure why this is happening since these are errors in the sample database.
Can someone please provide me some solution on how i can get the sample database restored.
Attaching the sql backups (Strucuture and Data separate)
Sorry for the problems with the database import. You will need to see why the data is not importing. It’s possible that the data does not match the old location to the new. There may also be corrupt data causing the import to fail. You will need to look at the import and see if there are errors occurring during the import process. Additionally, is the table “zamaadmi_cfm.wp_post” supposed to exist? By default, the “wp_posts” table is required within the WordPress database. If you are an InMotion Hosting customer, the name of database uses the prefix of based on the user name of the account. If that is not the correct database name (or prefix), then what part of WordPress is requesting the table. If you had caching turned on BEFORE you made the import, then it should have been turned off, or the saved cache could be trying to refer to the database from the previous location.
I hope this helps to answer your question and point you in the right direction. Please let us know if you require any further assistance.
You do not have the correct privileges assigned to your MySQL user. Here’s the command to do so:
GRANT ALL PRIVILEGES ON * . * TO ‘newuser’@’localhost’;
I want to add a created database in localhost to available database on webhost, so that the tables of both(local&web host) are exactly same but their informations are different. Now I want by importing, add the data on local tables to same table on webhost.
I am not quite sure what you mean. Are you wanting to simply import the structure of the database, but no data? You may want to import the database so you get the structure and then delete the content with short SQL queries. Then you will have an empty database in which you can input your new data.
I have a windows hosting for asp.net but I want to upload a website which developed by wordpress. where used mysql database. How to upload and configure it? please reply me as soon as possible.
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;—- Database: `moyie_wrdp59`—- ———————————————————— Table structure for table `wp_commentmeta`– DROP TABLE IF EXISTS `wp_commentmeta` ;
MySQL said: #1046 - No database selected
Kindly direct me on how to fix this.
Regards
Habyb
When you import a database, you should be specifying database you are importing TO. In the steps above, you will have needed to have selected an existing database to do this. You should not be choosing a database with these same tables already created. Check out How to create a MySQL database in cPanel if needed. If you’re importing into a blank database, then it should be no problem. Make sure that your file is no larger than 50 MB as there is a limit to the import size using phpMyAdmin.
If you have any further questions or comments, please let us know.
I’m interested is there any way i can automatically import a database with phpMyAdmin at a given interval? I’d like to have phpMyAdmin import a database from a specific location every 24 hours, or any interval that i like.
Currently, the only way to do that is to create a CRON job that launches a script for the database import. The import would need to be written to be executed via command line using MySQL. Check out how to run a cron job for further details.
If you have any further questions or comments, please let us know.
I am having issues importing a sql database backup from my old host to a blank database here. I get no errors but the database is still blank and I get no confirmation of completion. I have been able to import the backup to my old host to a blank database and it works fine and i get confirmation that it was successful. One thing i noticed between the two hosts is that my old host, i have to login with the sql username, here there is no way to login as the sql user.
i am deploying my project,its done successfully and imported database into online database .the problem i’m getting is there no db update and no connetion estabilesh while connection online server..
Sorry for the problem with the database connection. Can you please provide us some information about your database and your account? We would that information in order to investigate the issue further. Otherwise, you are welcome to submit a ticket to our live technical support team. They can investigate the issue privately so that your connection information is not published in a reply on this page.
Please provide us a little more information, or submit a support ticket to our live tech support team and we can investigate the issue in more depth.
I get this when I try importing SQL file. Pls someone should help me out on how to solve this problem:
Error
There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem
ERROR: Unknown Punctuation String @ 15
STR: ><
SQL: <!DOCTYPE HTML><html lang=’en’ dir=’ltr’ class=’safari safari4′><meta charset=”utf-8″ /><meta name=”robots” content=”noindex,nofollow” /><meta http-equiv=”X-UA-Compatible” content=”IE=Edge”><style>html{display: none;
SQL query:
<!DOCTYPE HTML><html lang=’en’ dir=’ltr’ class=’safari safari4′><meta charset=”utf-8″ /><meta name=”robots” content=”noindex,nofollow” /><meta http-equiv=”X-UA-Compatible” content=”IE=Edge”><style>html{display: none; MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL ser
The reason for the error is that the data it is reading is not SQL, but HTML. PhpMyAdmin cannot import HTML and convert it to a database. It needs to have a regular SQL Query in order to do so.
Apologies for the problem that you’re seeing. However, the issue centers around your code NOT selecting a specific database. You may need to speak with a developer if you are unable to correct that issue. We unfortunately cannot correct the code for you.
I am new to using phpMyadmin.I had a crash on my site, exported a .sql database file. Created a new database inviroment and installed a new version of concrete5 on to de database. When uploading the exported .sql file i get a error#1062 - Duplicate entry '1' for key 'PRIMARY'
Sorry to hear you’re having problems with the database error. I looked in Concrete’s support forum and if you review the posts, they have a possible solution. Please see the Concrete 5 Support forums in regards to this error and they be able to provide a fix for you.
I have a website hosted with InMotion, and I used a WordPress plugin to give me a weekly backup of what I assume is the database. The file it sends me each week is a .zip file.
The instructions for restoring this backup are the same as what is outilined here: go to phpMyAdmin in Cpanel, select the appropriate database, select “Import”, navigate to the backup file and click on the “Go” button. This is supposed to restore the database to the backup version.
My question is whether this actually replaces the old database with the backup. Does this process remove the old database and replace it with the imported backup? It doesn’t really make that clear. I’m worried that I’ll just be duplicating all the content or otherwise making things worse. Do I have to somehow remove the old database first BEFORE I import the backup file or is that done automatically?
Yes, the restore will replace the existing database so no data will be duplicated. You can always remove all the data in the database if you like by selecting all the tables and “DROP”ing them. This would give you an empty database shell to restore to. It is an additional step, but a good one if you are not quite certain.
Sorry to hear that you’re having problems with the database. However, we need more specific information in order to help you with the connection issue. Do you have an account with InMotion? If not, can you please provide the error message you’re seeing as well as the configuration information used for connecting to the database? The connection parameters should be verified so that we can at least rule it out.
That error is when the query attempts to put data from a column in to a table and the database cannot locate that particular column. Be sure to check for spelling errors in either the query or the database itself.
I had a crash on my site, exported a .sql database file. Created a new database inviroment and installed a new version of concrete5 on to de database. When uploading the exported .sql file i get a error #1054 – Unknown column ‘fvHasThumbnail1’ in ‘field list’.
Trying to get it to run again, any help is greatly appriciated. If needed i can share the .sql file.
I believe there is some miscommunication somewhere as the wp-config.php file has the database connection information and is not the database itself. I would suggest contacting your old host and request a backup of the database specifically as you can not back up the database solely from FTP.
Created my db just fine along with a db user just fine. But when I import it says i do not have permission to create a table. Issue is this: My cpanel login is ‘xy’ (not really). All users that I create for the db have to be ‘x_’ something. When I go into phpMyAdmin, it never asks me to login, it just lets me in and says I am ‘xy’@’localhost’. My question is how to I tell phpMyAdmin to be ‘x_dbuser’@’localhost’ (which I created) instead of ‘xy’@’localhost’ (who I cannot create as a db user and so does not have access to the db)? It must be something simple but I cannot find it.
I am new to using phpMyadmin. I followed your procedure and the import was successfull. However after importing, if I click on “My Website” in it, i get error in connection. I already have any existing website in wordpress and would like to know how I should connect it to the phpMyadmin.
Typically, waiting a bit should clear this up. If you continue to receive the error, you will want to contact our Live Support department to have them take a look at your individual situation.
When you import to an alternative database how can you be sure that all the previous data is removed. Is import destructive or only additive. If you want it to be an exact duplicate of the imported DB do you need to drop the previous tables or delete all the rows?
50MB is the size limit for database imports on a shared server. You will need to contact Live Support to have them import anything larger than that. You can go ahead and upload the database file to your account, preferably in the home directory or public_html folder. Be sure to indicate the name and location of the file in your request. As always, be sure to verify your request with either the last 4 digits of the credit card on file or the current AMP password if sending an email.
Get web hosting that grows with your business. Our all-in-one hosting platform gives you everything your website needs to scale - so you can focus on the next big thing for you and your business.
Ä°t is not possible to download or save SQL file of phpmyadmin.
Hello! If you’d like to download a database, please see our article on exporting your database (this one uses phpMyAdmin), or the ‘retrieve a standard database’ section in our guide to cPanel backups. If you’ve exported the file but still need to download it, you can use cPanel’s file manager, or FTP. Hope that helps!
My database is over 50mb, and the alternative directions are confusing for a novice. (i.e. “simply import the database via SSH” makes no sense, and the “install a phpMyAdmin instance” has a deadlink in step 2). So now I’m stuck.
I understand your frustration. The article has been updated. If you have any questions about the process, feel free to contact Live Support directly.
Error
SQL query:
CREATE TABLE IF NOT EXISTS `admins` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `admins_email_unique` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
MySQL said:
#1071 - Specified key was too long; max key length is 767 bytes
Thanks for your question regarding the #1071 error. The problem is literally what the error is telling you. You can see a great explanation of the issue here. They also link to the specific MYSQL documentation that refers to prefix limits. Please review this definition and then review your code. If you require further assistance, then I recommend that you contact a seasoned database programmer.
I have my database set up and the table created. When trying to import the database (txt.gz file), I get this error message.
#1064 – 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 ‘2010/12/25’, ‘*’, ‘BJORN – NHSB 2346883’, ‘BJORN’, ‘*’, ‘FAMKE DIEUWKE FAN IT D’ at line 2
Can you help direct me to the solution?
Thank you!
Hello.
Correcting this error requires the knowledge of MySQL syntax for the version of MySQL that your server is using. Typically, I refer similar requests for assistance to the official MySQL documentation to reference with the syntax of the code indicated in the error message. However, there is not enough information here to make that assessment to know which version you are using. You can however find their documentation here, you will just need to select the correct version that matches yours.
Sincerely,
Carlos D.
Error
SQL query: Copy
CREATE TABLE `card_types` (
`card_type_id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default ”,
PRIMARY KEY (`card_type_id`)
) TYPE=MyISAM AUTO_INCREMENT=3;
MySQL said: Documentation
#1064 – 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 ‘TYPE=MyISAM AUTO_INCREMENT=3’ at line 5
Here is a helpful link to the official documentation on using AUTO_INCREMENT. It provides additional details and examples of how to format the attribute.
You may want to try following this StackOverflow forum.
Please what do i use as my hostname in my php connect file.
Typically, the hostname will be ‘localhost’ or the domain name you are connecting to such as ‘example.com’.
Thank you,
John-Paul
I am trying to import a .ods vile using import in PhP Admin and i get an error no data in column T, yet my data is only in columns A-S. How to solve this error?
Hello.
I’m sorry to see that you are experiencing difficulty in using phpMyAdmin to import your database from a .ods Open Document Spreadsheet. According to phpMyAdmin documentation, there are specific formatting conditions that need to be met in order for the import to successfully complete. I suggest reviewing the details at that link, modifying your Spreadsheet accordingly, and then reattempting the import. I hope this helps!
Sincerely,
Carlos D
Hello,
There was some issue in my website, so i exported the all tables of database and deleted the the databse.
but whn i am creating again the same database it is showing database already exists. I have checked there is no any database with same name.
I am new phpmyadmin user, please help me to resolve the issue.
Make sure to clear your browser cache and check the list of databases in the MySQL Databases list in cPanel. If the problem persists, then please contact our live technical support team so that they can check it directly at the server level. They will need for you to verify the account and provide more information about the database that is in question.
HOw to import large sql file to phpmyadmin.
You can upload large .sql file to phpmyadmin using two method.
1.First find your main php.ini file.
In php.ini file, edit following thing for upto 2GB file size import or upload.
max_execution_time = 1800 // equals to 30 minutes
max_input_time = 1800 // equals to 30 minutes
memory_limit = 2001M // equals to 2 GB
upload_max_filesize = 2000M // equals to 2 GB
post_max_size = 2001M // equals to 2 GB
After edit save that file.
2.Use bigdump.php file.
Find more details here.
https://www.ithemesforests.com/how-to-import-large-files-into-phpmyadmin/
Thank you for sharing!
John-Paul
Hello,
I am trying to take my test site and move it to my live site. I get this error for importing the database sql file for the live.
Error
SQL query:
--
-- Dumping data for table `wp_blogs`
--
INSERT INTO `wp_blogs` (`blog_id`, `site_id`, `domain`, `path`, `registered`, `last_updated`, `public`, `archived`, `mature`, `spam`, `deleted`, `lang_id`) VALUES
(1, 1, 'newvworld.com', '/', '2017-08-02 14:38:18', '2017-08-10 01:16:20', 1, 0, 0, 0, 0, 0);
MySQL said:
#1062 - Duplicate entry '1' for key 'PRIMARY'
Thanks!
Your error seems to be indicating that the table already contains the data you are importing. I recommend checking your tables to ensure they are empty before importing content in.
Thank you,
John-Paul
Thanks for your response. The problem is that I cancelled my hosting plan and requested for my backup. The website and database is no longer up, so there’s no way to re-export. i only have the sql file with me.
Hi, Naomie.
I’m sorry to see that this is the case. If you’re unable to attempt to re-export that database, then the data may not be able to be recovered properly and would need to be rebuilt manually. I would contact your previous host and see if they can provide you a full cPanel backup of your account. Or if they can provide a backup of that database. With either of those, you can attempt to restore the site. Unfortunately, if that is not an option, there’s not much else you can do. I hope this helps!
Sincerely,
Carlos E
Thanks for the response. I don’t know how to go about the repair though. Any pointers would be appreciated.
Hi, Naomie!
We actually have a great article that will guide you through How to Check and Repair Databases in cPanel and also How to Check and Repair a Database in phpMyAdmin. I hope this helps!
Sincerely,
Carlos E
hello. please i’ve been trying to import my database tables for a long while no but i keep getting this error
“Error
SQL query: /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `ecomdtapi_session` ( `user_id` INT( 10 ) UNSIGNED NOT NULL COMMENT ‘User id’, `logdate` TIMESTAMP NOT NULL DEFAULT ‘0000-00-00 00:00:00’ COMMENT ‘Login date’, `sessid` VARCHAR( 40 ) DEFAULT NULL COMMENT ‘Sessioin id’, KEY `IDX_API_SESSION_USER_ID` ( `user_id` ) , KEY `IDX_API_SESSION_SESSID` ( `sessid` ) , CONSTRAINT `FK_API_SESSION_USER_ID_API_USER_USER_ID` FOREIGN KEY ( `user_id` ) REFERENCES `ecomdtapi_user` ( `user_id` ) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = ‘Api Sessions’; MySQL said: Documentation #1215 – Cannot add foreign key constraint “
I need help please.
You may want to try running a repair on the database and re-exporting it. Then re-import that repaired file.
Normal
0
false
false
false
EN-GB
X-NONE
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:””;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-fareast-language:EN-US;}
Hi, I am new to dealing with sql databases, I tried to import one and got message below. Any help would be greatly appreciated
Error
SQL query:
OP TABLE IF EXISTS `wp_options`;
MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OP TABLE IF EXISTS `wp_options`' at line 1
What are you trying to do? Looks like you are missing the “DROP” instead of “OP”
SQL query:
MySQL said:
#1044 - Access denied for user 'id1616023_bfilmz'@'%' to database 'information_schema'
Hi, am having issue importing my database from my Xamp-phpadmin-sql
Bukola, this error indicates that the permissions for your MySQL user ‘id1616023_bfilmz’@’%’ are not set to allow this import. You will need to either adjust these permissions, or contact your current web host for assistance with doing so.
Hello,
I am new in PHP my admin as I was working on localhost and created some views in my database but now every time I copy my database file to another system then that views are not working and it was showing error “view name ” does not exist
Is there any wayout to get that views on another machine through that database file…
THANKS
Abhilash, I would recommend that when you are exporting your database from your localhost you also enable a DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement. This may vary based on the software you are using. Also, please keep in mind that database views may not be supported on our business class / reseller hosting servers.
I am trying to import a joomla database from a backup files stored on my desktop to cpanel using phpmyadmin. Question I have is when you click on the browse where to navigate into which folder is the sql file. I see lots of sql files mostly 1 kp and some max 40 kb
thanks
Hello Maditha,
Sorry for the issue with the database import. You will need to determine you Joomla database file in your Joomla installation files. You need to find the config.php file and open it up. It will tell you the exact name of the database file. Generally, the database file will be bigger than 40kb.
If you have any further questions or comments, please let us know.
Regards,
Arnel C.
Hi, I am cloning a wordpress site for dev / sandbox.I manage to export the database with no problemand when I tried to import the db and I got this message:
Error
SQL query:
--
-- Database: `waso_wrdp5`
--
-- --------------------------------------------------------
--
-- Table structure for table `wp_commentmeta`
--
DROP TABLE IF EXISTS `wp_commentmeta`;
MySQL said:
#1046 - No database selected
I am thinking of re-exporting the database without that section.Or if you have an alternate solution?Cheers
Hello Ben,
I think that the error has more to do with the “no database selected” message. When you export a database, make sure it includes all of the tables. You should simply create a BLANK database (no tables) through MySQL in cPanel. Then when you import the tables from your old database, you would select the blank database.
I hope this helps to answer your question, please let us know if you require any further assistance.
Regards,
Arnel C.
Scenario
Installed an Open Source Shopping Cart on a machine with WAMP :
Setting up Successful !!!
( Made changes to the sample Shopping Site )
Installed wamp on another machine :
Tried to restore the database (to import the data from the additions I did on the other machine)
Database import fails !!!
Split script with data and structure separately:
Structure is successful !
Errors are in the data, and these are with the sample data (not any data that I had added)
Errors that I identified were,
1. Error #1064 reported :
#1064 – check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Cart TTL (days)’, ” at line 53
Cause identified as “#” in line above
2. Replaced “#” with “No” and then error is reported :
Error
SQL query:
INSERT INTO `xc_country_translations` (`label_id`, `id`, `country`, `code`) VALUES
(1, ‘AF’, ‘Afghanistan’, ‘en’),
(2, ‘AX’, ‘Åland Islands’, ‘en’),
(3, ‘AL’, ‘Albania’, ‘en’),
(4, ‘DZ’, ‘Algeria’, ‘en’),
(5, ‘AS’, ‘American Samoa’, ‘en’),
(6, ‘AD’, ‘Andorra’, ‘en’),
(7, ‘AO’, ‘Angola’, ‘en’),
(8, ‘AI’, ‘Anguilla’, ‘en’),
(9, ‘AQ’, ‘Antarctica’, ‘en’),
(10, ‘AG’, ‘Antigua and Barbuda’, ‘en’),
(11, ‘AR’, ‘Argentina’, ‘en’),
(12, ‘AM’, ‘Armenia’, ‘en’),
(13, ‘AW’, ‘Aruba’, ‘en’),
(14, ‘AU’, ‘Australia’, ‘en’),
(15, ‘AT’, ‘Austria’, ‘en’),
(16, ‘AZ’, ‘Azerbaijan’, ‘en’),
(17, ‘BS’, ‘Bahamas’, ‘en’),
(18, ‘BH’, ‘Bahrain’, ‘en’),
(19, ‘BD’, ‘Bangladesh’, ‘en’),
(20, ‘BB’, ‘Barbados’, ‘en’),
(21, ‘BY’, ‘Belarus’, ‘en’),
(22, ‘BE’, ‘Belgium’, ‘en’),
(23, ‘BZ’, ‘Belize’, ‘en’),
(24, ‘BJ’, ‘Benin’, ‘en’),
(25, ‘BM’, ‘Bermuda’, ‘en’),
(26, ‘BT’, ‘Bhutan’, ‘en’),
(27, ‘BO’, ‘Bolivia, Plurinational State of’, ‘en’),
(28, ‘BQ’, ‘Bonaire, Sint Eustatius and Saba’, ‘en’),
(29, ‘BA’, ‘Bosnia and Herze[…]
MySQL said:
]#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘CREATE TABLE `xc_coupons` (
`id` int(10) NOT NULL,
`code` char(16) COLLATE u’ at line 258
Not sure why this is happening since these are errors in the sample database.
Can someone please provide me some solution on how i can get the sample database restored.
Attaching the sql backups (Strucuture and Data separate)
https://drive.google.com/open?id=0B4hQPwRL1XVsRUUwVGFuZk1CZkk
I recommend reviewing your syntax for the INSERT INTO statement. As it does not look like it is formatted correctly.
Thank you,
John-Paul
You need to check your syntax and unfortunately without being able to see the file we can’t offer you advice on how to correct the issue.
I’m getting an error when attempting to import a new DB using phpMyAdmin as follows;
#1115 - Unknown character set: 'utf8mb4'
My research indicates that this is due to an older version of MySQL being used on the server. Is this correct and is there a solution?
Yes, your assertion seems to be correct. It looks like you may need to update your MySQL to a newer version.
hello sir
i am getting this error while importing the database into myphpadmin?
SQL query:
—
— Database: `wordpress`
—
— ——————————————————–
—
— Table structure for table `wp_commentmeta`
—
CREATE TABLE `wp_commentmeta` (
`comment_id` bigint( 20 ) UNSIGNED NOT NULL DEFAULT ‘0’,
`meta_key` varchar( 255 ) COLLATE utf8mb4_unicode_ci DEFAULT NULL ,
`meta_value` longtext COLLATE utf8mb4_unicode_ci
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
MySQL said:
#1046 - No database selected
thanks
You must select the database first and then “Import” tab. If you use the Import option first without selecting a database it will generate this error.
Nice article . I loved the points , Does anyone know if my company might be able to obtain a blank TREC 9-6 example to edit ?
I’m not sure what specifically that is, but when Googling “TREC 9-6”, Google did give some pdf links for different forms.
HI I’m getting an error #1146 – Table ‘zamaadmi_cfm.wp_posts’ doesn’t exist
Hello Zama,
Sorry for the problems with the database import. You will need to see why the data is not importing. It’s possible that the data does not match the old location to the new. There may also be corrupt data causing the import to fail. You will need to look at the import and see if there are errors occurring during the import process. Additionally, is the table “zamaadmi_cfm.wp_post” supposed to exist? By default, the “wp_posts” table is required within the WordPress database. If you are an InMotion Hosting customer, the name of database uses the prefix of based on the user name of the account. If that is not the correct database name (or prefix), then what part of WordPress is requesting the table. If you had caching turned on BEFORE you made the import, then it should have been turned off, or the saved cache could be trying to refer to the database from the previous location.
I hope this helps to answer your question and point you in the right direction. Please let us know if you require any further assistance.
Regards,
Arnel C.
#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
You do not have the correct privileges assigned to your MySQL user. Here’s the command to do so:
GRANT ALL PRIVILEGES ON * . * TO ‘newuser’@’localhost’;
hi,
I want to add a created database in localhost to available database on webhost, so that the tables of both(local&web host) are exactly same but their informations are different. Now I want by importing, add the data on local tables to same table on webhost.
Please help me, if it is possible!
thanks
Hello Stephan,
I am not quite sure what you mean. Are you wanting to simply import the structure of the database, but no data? You may want to import the database so you get the structure and then delete the content with short SQL queries. Then you will have an empty database in which you can input your new data.
Kindest Regards,
Scott M
I GET “PARSE OR FATAL ERROR” WHEN CONNECTING OR SELECTING FROM THE DATABASE
Hello Yobo,
Where are you seeing that message?
Best Regards,
TJ Edens
Hello Sir/ Ma’am,
I have a windows hosting for asp.net but I want to upload a website which developed by wordpress. where used mysql database. How to upload and configure it? please reply me as soon as possible.
Regards
Hafizul Sk
Hello Hafizul,
Thanks for the question. We do not provide support for other types of servers, unfortunately.
Regards,
Arnel C.
Hi,
I get the following error during import:
Error
SQL query:
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;—- Database: `moyie_wrdp59`—- ———————————————————— Table structure for table `wp_commentmeta`– DROP TABLE IF EXISTS `wp_commentmeta` ;
MySQL said:
#1046 - No database selected
Kindly direct me on how to fix this.
Regards
Habyb
Hello Habyb,
When you import a database, you should be specifying database you are importing TO. In the steps above, you will have needed to have selected an existing database to do this. You should not be choosing a database with these same tables already created. Check out How to create a MySQL database in cPanel if needed. If you’re importing into a blank database, then it should be no problem. Make sure that your file is no larger than 50 MB as there is a limit to the import size using phpMyAdmin.
If you have any further questions or comments, please let us know.
Regards,
Arnel C.
Hello guys!
I’m interested is there any way i can automatically import a database with phpMyAdmin at a given interval? I’d like to have phpMyAdmin import a database from a specific location every 24 hours, or any interval that i like.
I know how to creat a MySQL Event, if that helps.
Hello Nick,
Currently, the only way to do that is to create a CRON job that launches a script for the database import. The import would need to be written to be executed via command line using MySQL. Check out how to run a cron job for further details.
If you have any further questions or comments, please let us know.
Regards,
Arnel C.
Hello,
I am having issues importing a sql database backup from my old host to a blank database here. I get no errors but the database is still blank and I get no confirmation of completion. I have been able to import the backup to my old host to a blank database and it works fine and i get confirmation that it was successful. One thing i noticed between the two hosts is that my old host, i have to login with the sql username, here there is no way to login as the sql user.
How large is the import? It may be failing due to size, which, in that case we can help you with the import.
i am deploying my project,its done successfully and imported database into online database .the problem i’m getting is there no db update and no connetion estabilesh while connection online server..
help dude.
Hello Anil,
Sorry for the problem with the database connection. Can you please provide us some information about your database and your account? We would that information in order to investigate the issue further. Otherwise, you are welcome to submit a ticket to our live technical support team. They can investigate the issue privately so that your connection information is not published in a reply on this page.
Please provide us a little more information, or submit a support ticket to our live tech support team and we can investigate the issue in more depth.
Regards,
Arnel C.
I get this when I try importing SQL file. Pls someone should help me out on how to solve this problem:
Error
There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem
ERROR: Unknown Punctuation String @ 15
STR: ><
SQL: <!DOCTYPE HTML><html lang=’en’ dir=’ltr’ class=’safari safari4′><meta charset=”utf-8″ /><meta name=”robots” content=”noindex,nofollow” /><meta http-equiv=”X-UA-Compatible” content=”IE=Edge”><style>html{display: none;
SQL query:
<!DOCTYPE HTML><html lang=’en’ dir=’ltr’ class=’safari safari4′><meta charset=”utf-8″ /><meta name=”robots” content=”noindex,nofollow” /><meta http-equiv=”X-UA-Compatible” content=”IE=Edge”><style>html{display: none;
MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL ser
Hello Nestor,
The reason for the error is that the data it is reading is not SQL, but HTML. PhpMyAdmin cannot import HTML and convert it to a database. It needs to have a regular SQL Query in order to do so.
Kindest Regards,
Scott M
I have different kinds of database files with me I dont know how to import those files (like, OPT, FRM, MYI, etc)
can anyone suggest what to do?
PhpMyAdmin allows for the following import formats: SQL, MediaWiki Table, OpenDocument Spreadsheet, CSV, ESRI Shape File, or XML
Erro
Comando SQL:
—- Database: `joomla`—- ———————————————————— Table structure for table `#__assets`– CREATE TABLE IF NOT EXISTS `#__assets` (
`id` int( 10 ) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘Primary Key’, `parent_id` int( 11 ) NOT NULL DEFAULT 0 COMMENT ‘Nested set parent.’, `lft` int( 11 ) NOT NULL DEFAULT 0 COMMENT ‘Nested set lft.’, `rgt` int( 11 ) NOT NULL DEFAULT 0 COMMENT ‘Nested set rgt.’, `level` int( 10 ) unsigned NOT NULL COMMENT ‘The cached level in the nested tree.’, `name` varchar( 50 ) NOT NULL COMMENT ‘The unique name for the asset.\n’, `title` varchar( 100 ) NOT NULL COMMENT ‘The descriptive title for the asset.’, `rules` varchar( 5120 ) NOT NULL COMMENT ‘JSON encoded access control.’, PRIMARY KEY ( `id` ) , UNIQUE KEY `idx_asset_name` ( `name` ) , KEY `idx_lft_rgt` ( `lft` , `rgt` ) , KEY `idx_parent_id` ( `parent_id` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8;
Mensagens do MySQL :
#1046 - No database selected
Hello Abilio,
Apologies for the problem that you’re seeing. However, the issue centers around your code NOT selecting a specific database. You may need to speak with a developer if you are unable to correct that issue. We unfortunately cannot correct the code for you.
Kindest regards,
Arnel C.
Hi,
I am new to using phpMyadmin.I had a crash on my site, exported a .sql database file. Created a new database inviroment and installed a new version of concrete5 on to de database. When uploading the exported .sql file i get a error
#1062 - Duplicate entry '1' for key 'PRIMARY'
Hello Minkoko,
Sorry to hear you’re having problems with the database error. I looked in Concrete’s support forum and if you review the posts, they have a possible solution. Please see the Concrete 5 Support forums in regards to this error and they be able to provide a fix for you.
Regards,
Arnel C.
I have a website hosted with InMotion, and I used a WordPress plugin to give me a weekly backup of what I assume is the database. The file it sends me each week is a .zip file.
The instructions for restoring this backup are the same as what is outilined here: go to phpMyAdmin in Cpanel, select the appropriate database, select “Import”, navigate to the backup file and click on the “Go” button. This is supposed to restore the database to the backup version.
My question is whether this actually replaces the old database with the backup. Does this process remove the old database and replace it with the imported backup? It doesn’t really make that clear. I’m worried that I’ll just be duplicating all the content or otherwise making things worse. Do I have to somehow remove the old database first BEFORE I import the backup file or is that done automatically?
Thank you very much for your help.
Doug Nienhuis
Hello Doug,
Yes, the restore will replace the existing database so no data will be duplicated. You can always remove all the data in the database if you like by selecting all the tables and “DROP”ing them. This would give you an empty database shell to restore to. It is an additional step, but a good one if you are not quite certain.
Kindest Regards,
Scott M
Hello there,
i had successfully imported the database to the myphpadmin , but still it does not connecting with my website , will u please sort out the problems
Hello Praveen,
Sorry to hear that you’re having problems with the database. However, we need more specific information in order to help you with the connection issue. Do you have an account with InMotion? If not, can you please provide the error message you’re seeing as well as the configuration information used for connecting to the database? The connection parameters should be verified so that we can at least rule it out.
Regards,
Arnel C.
Thanks for the info Tj. =)
Hello Olaf,
That error is when the query attempts to put data from a column in to a table and the database cannot locate that particular column. Be sure to check for spelling errors in either the query or the database itself.
Kindest Regards,
Scott M
I had a crash on my site, exported a .sql database file. Created a new database inviroment and installed a new version of concrete5 on to de database. When uploading the exported .sql file i get a error #1054 – Unknown column ‘fvHasThumbnail1’ in ‘field list’.
Trying to get it to run again, any help is greatly appriciated. If needed i can share the .sql file.
Hi Jeff,
I’d be happy if you can help me.
I backed my files by ftp.
My host opened a new domain name. And created a brand new site.
I updated the wp-config file and tried to import it bu phpmyadmin, but with no success.
I sent all my old files to the new site by ftp.
Is there any missing logic on my actions?
Also is the database only wp-config ?
Thanks
Hello Baris,
I believe there is some miscommunication somewhere as the wp-config.php file has the database connection information and is not the database itself. I would suggest contacting your old host and request a backup of the database specifically as you can not back up the database solely from FTP.
Best Regards,
TJ Edens
Created my db just fine along with a db user just fine. But when I import it says i do not have permission to create a table. Issue is this: My cpanel login is ‘xy’ (not really). All users that I create for the db have to be ‘x_’ something. When I go into phpMyAdmin, it never asks me to login, it just lets me in and says I am ‘xy’@’localhost’. My question is how to I tell phpMyAdmin to be ‘x_dbuser’@’localhost’ (which I created) instead of ‘xy’@’localhost’ (who I cannot create as a db user and so does not have access to the db)? It must be something simple but I cannot find it.
The main cPanel user should have access to all databases. Could you provide the exact error you are getting?
Hi,
I am new to using phpMyadmin. I followed your procedure and the import was successfull. However after importing, if I click on “My Website” in it, i get error in connection. I already have any existing website in wordpress and would like to know how I should connect it to the phpMyadmin.
thanks.
Hello Vishal,
If you imported the installation, then you would have needed to change the settings for the database in your wp-config.php file. Check out this article for further assistance: Error Establishing Database Connection. You may also need to review this tutorial on finding your database name.
I hope this helps to answer your question, please let us know if you require any further assistance.
Regards,
Arnel C.
i get this error when tray to import a file of 33mb size
“The connection was reset”
Hello Atman,
Typically, waiting a bit should clear this up. If you continue to receive the error, you will want to contact our Live Support department to have them take a look at your individual situation.
Kindest Regards,
Scott M
When you import to an alternative database how can you be sure that all the previous data is removed. Is import destructive or only additive. If you want it to be an exact duplicate of the imported DB do you need to drop the previous tables or delete all the rows?
Before importing, you will need to drop all previous tables if you are overwriting existing data.
find this error
You probably tried to upload a file that is too large. Please refer to documentation for a workaround for this limit.
Hello adesh,
50MB is the size limit for database imports on a shared server. You will need to contact Live Support to have them import anything larger than that. You can go ahead and upload the database file to your account, preferably in the home directory or public_html folder. Be sure to indicate the name and location of the file in your request. As always, be sure to verify your request with either the last 4 digits of the credit card on file or the current AMP password if sending an email.
Kindest Regards,
Scott M