By halloffame on

Heya guys. I'm developing locally and when I'm about to upload my database to my web host I got some error. The first time I tried uploading it (using PHPMyAdmin), it returned me '#1007 - Can't create database 'databasename'; database exists' error. So I deleted the existing database on my web host and tried uploading again and this time it returned me another error - '#1044 - Access denied for user 'sitename'@'localhost' to database 'databasename'.

One more thing. When I exported the same database the other time from my web host to my local machine it all went perfectly smooth without a hitch. Was using the same method. I don't know why I keep getting this error now trying to upload the same database to my web host.

I don't know how to deal with this. Please help.

⋅ Categories: Drupal 6.x

Comments

carlmcdade’s picture

carlmcdade commented

Database creation and administration is different on each web host. Dreamhost for instance does not let you run a script to create a database from phpmyadmin. You have to use their control panel.

Hiveminds Magazine | Drupal Developers Network | 5.0 Developers Guide in offline CHM format

avadhutp’s picture

avadhutp commented

You may have to delete and modify the first few lines of your SQL dump. Paste the first few lines here (maybe around 50) and I could advice you what to delete and modify.

halloffame’s picture

halloffame commented

Ok here it goes....

 -- phpMyAdmin SQL Dump
-- version 2.11.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 17, 2008 at 06:38 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `bwdb`
--
CREATE DATABASE `bwdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `bwdb`;
-- --------------------------------------------------------

--
-- Table structure for table `access`
--

CREATE TABLE IF NOT EXISTS `access` (
  `aid` int(11) NOT NULL auto_increment,
  `mask` varchar(255) NOT NULL default '',
  `type` varchar(255) NOT NULL default '',
  `status` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`aid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `access`
--


-- --------------------------------------------------------

--
-- Table structure for table `accesslog`
--

CREATE TABLE IF NOT EXISTS `accesslog` (
  `aid` int(11) NOT NULL auto_increment,
  `sid` varchar(64) NOT NULL default '',
  `title` varchar(255) default NULL,
  `path` varchar(255) default NULL,
  `url` varchar(255) default NULL,
  `hostname` varchar(128) default NULL,
  `uid` int(10) unsigned default '0',
  `timer` int(10) unsigned NOT NULL default '0',
  `timestamp` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`aid`),
  KEY `accesslog_timestamp` (`timestamp`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `accesslog`
--


-- --------------------------------------------------------

--
-- Table structure for table `actions`
--

CREATE TABLE IF NOT EXISTS `actions` (
  `aid` varchar(255) NOT NULL default '0',
  `type` varchar(32) NOT NULL default '',
  `callback` varchar(255) NOT NULL default '',
  `parameters` longtext NOT NULL,
  `description` varchar(255) NOT NULL default '0',
  PRIMARY KEY  (`aid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `actions`
--

INSERT INTO `actions` (`aid`, `type`, `callback`, `parameters`, `description`) VALUES
('comment_unpublish_action', 'comment', 'comment_unpublish_action', '', 'Unpublish comment'),
('node_publish_action', 'node', 'node_publish_action', '', 'Publish post'),
('node_unpublish_action', 'node', 'node_unpublish_action', '', 'Unpublish post'),
('node_make_sticky_action', 'node', 'node_make_sticky_action', '', 'Make post sticky'),
('node_make_unsticky_action', 'node', 'node_make_unsticky_action', '', 'Make post unsticky'),
('node_promote_action', 'node', 'node_promote_action', '', 'Promote post to front page'),
('node_unpromote_action', 'node', 'node_unpromote_action', '', 'Remove post from front page'),
('node_save_action', 'node', 'node_save_action', '', 'Save post'),
('user_block_user_action', 'user', 'user_block_user_action', '', 'Block current user'),
('user_block_ip_action', 'user', 'user_block_ip_action', '', 'Ban IP address of current user');

-- --------------------------------------------------------

--
-- Table structure for table `actions_aid`
--

CREATE TABLE IF NOT EXISTS `actions_aid` (
  `aid` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`aid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `actions_aid`
--
avadhutp’s picture

avadhutp commented

Create the database bwdb manually.

Then, delete this line from your dump before uploading it to the database server:

CREATE DATABASE `bwdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

addendum’s picture

addendum commented

this worked for me i used note pad to remove CREATE DATABASE `name` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

it all works again

thanks

halloffame’s picture

halloffame commented

OK folks what I really want here is to find a way to easily upload my database to and from my local machine anytime I want to. I'm open to whatever ways possible in doing this so please lay out all the options you can think of.

Much appreciated.

halloffame’s picture

halloffame commented

Here's what my host told me.
In order to import a database that already exists, you need to either rename the database in the .sql file using a unix standard text editor like "vi"

Well I went looking for the VI text editor and it seems to be only for Unix. I'm in Windows. And even if I had that text editor I don't know where/what to edit exactly. Besides, why do I need to download another text editor, isn't Notepad enough already to do such editing?

carlmcdade’s picture

carlmcdade commented

Create a database on the host side and then export only the tables and data locally. Then import the tables and data on the host server.

Hiveminds Magazine | Drupal Developers Network | 5.0 Developers Guide in offline CHM format

halloffame’s picture

halloffame commented

I have already asked my host to allow me to create db using phpmyadmin. I don't know yet if they would give me access or even if they will I don't know if this will fix my prob.

I don't know... import/export by tables seem a very tedious process as I have a big database.

carlmcdade’s picture

carlmcdade commented

If you have a large database then you are going have to use something like Big Dump anyway. phpMyAdmin has a upload limit of 2mb or so depending on the server.

The only other way is to upload the *.sql script to the server and run in via the shell, if you have shell access.

Hiveminds Magazine | Drupal Developers Network | 5.0 Developers Guide in offline CHM format

bappa.sarkar’s picture

bappa.sarkar commented

As you already mentioned that you used PHPMyAdmin on your host machine. So follow the steps.

  1. Delete Databse creation command from your .sql file
  2. create a database manually.
  3. use PHPMyAdmin and enter in your database.
  4. Click SQL
  5. paste half of the data from your .sql file(do it carefully so that you are not breaking a full query in middle) and put in the text area and run query
  6. Do the same for the remaining data.

Enjoy!