Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

Error with MySQL copy database

Using phpMyAdmin to copy a MySQL 5.1.32 database with 59 tables, it halts at the 54th table with error '#1062 - Duplicate entry '1' for key 'PRIMARY' ' on the 54th table. This is 'users' with 2 entries. Only one field is primary key. This seems to be a MySQL bug. Is there a simple workaround I can use? Do I just add last 5 tables by hand or what? Will the copied tables work?
0
rgbatson
Asked:
rgbatson
  • 5
  • 4
1 Solution
 
v2MediaCommented:
If there are only 2 entries in the user table and 1 column is the primary key, check the primary key column for a duplicate integer in the dump. If both are "1", then there's a problem with the database.

If this is the case, logon to your drupal admin and check users - delete the second user and try to copy the database via phpMyAdmin again.
0
 
rgbatsonAuthor Commented:
I did not find duplicate entries in the primary key column in the 'users' table of the Drupal installation (which works fine otherwise).
To be sure, I deleted the second entry, leaving only the administrator user.
Still no success copying database. Does create 54 of 59 tables; then quits at that point with above error message.
0
 
v2MediaCommented:
Could you post a dump of table 54? Edit out the password data
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rgbatsonAuthor Commented:
To v2Media,
Thank you very much for your help.
Dump of table 54 - users

CREATE TABLE IF NOT EXISTS `users` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL DEFAULT '',
  `pass` varchar(32) NOT NULL DEFAULT '',
  `mail` varchar(64) DEFAULT '',
  `mode` tinyint(4) NOT NULL DEFAULT '0',
  `sort` tinyint(4) DEFAULT '0',
  `threshold` tinyint(4) DEFAULT '0',
  `theme` varchar(255) NOT NULL DEFAULT '',
  `signature` varchar(255) NOT NULL DEFAULT '',
  `created` int(11) NOT NULL DEFAULT '0',
  `access` int(11) NOT NULL DEFAULT '0',
  `login` int(11) NOT NULL DEFAULT '0',
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `timezone` varchar(8) DEFAULT NULL,
  `language` varchar(12) NOT NULL DEFAULT '',
  `picture` varchar(255) NOT NULL DEFAULT '',
  `init` varchar(64) DEFAULT '',
  `data` longtext,
  PRIMARY KEY (`uid`),
  UNIQUE KEY `name` (`name`),
  KEY `access` (`access`),
  KEY `created` (`created`),
  KEY `mail` (`mail`),
  FULLTEXT KEY `language` (`language`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `mode`, `sort`, `threshold`, `theme`, `signature`, `created`, `access`, `login`, `status`, `timezone`, `language`, `picture`, `init`, `data`) VALUES
(0, '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, NULL, '', '', '', NULL),
(1, 'admin', '21232f297a57a5a743894a0e4a801fc3', 'rgbatson@hotmail.com', 0, 0, 0, '', '', 1239339414, 1240448948, 1240448762, 1, '-18000', '', '', 'rgbatson@hotmail.com', 'a:1:{s:13:"form_build_id";s:37:"form-97510bb464b935604e74aceacf9e2998";}');

CREATE TABLE IF NOT EXISTS `users` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL DEFAULT '',
  `pass` varchar(32) NOT NULL DEFAULT '',
  `mail` varchar(64) DEFAULT '',
  `mode` tinyint(4) NOT NULL DEFAULT '0',
  `sort` tinyint(4) DEFAULT '0',
  `threshold` tinyint(4) DEFAULT '0',
  `theme` varchar(255) NOT NULL DEFAULT '',
  `signature` varchar(255) NOT NULL DEFAULT '',
  `created` int(11) NOT NULL DEFAULT '0',
  `access` int(11) NOT NULL DEFAULT '0',
  `login` int(11) NOT NULL DEFAULT '0',
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `timezone` varchar(8) DEFAULT NULL,
  `language` varchar(12) NOT NULL DEFAULT '',
  `picture` varchar(255) NOT NULL DEFAULT '',
  `init` varchar(64) DEFAULT '',
  `data` longtext,
  PRIMARY KEY (`uid`),
  UNIQUE KEY `name` (`name`),
  KEY `access` (`access`),
  KEY `created` (`created`),
  KEY `mail` (`mail`),
  FULLTEXT KEY `language` (`language`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 
--
-- Dumping data for table `users`
--
 
INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `mode`, `sort`, `threshold`, `theme`, `signature`, `created`, `access`, `login`, `status`, `timezone`, `language`, `picture`, `init`, `data`) VALUES
(0, '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, NULL, '', '', '', NULL),
(1, 'admin', '21232f297a57a5a743894a0e4a801fc3', 'rgbatson@hotmail.com', 0, 0, 0, '', '', 1239339414, 1240448948, 1240448762, 1, '-18000', '', '', 'rgbatson@hotmail.com', 'a:1:{s:13:"form_build_id";s:37:"form-97510bb464b935604e74aceacf9e2998";}');

Open in new window

0
 
rgbatsonAuthor Commented:
Ran this sql against a new blank database. Same error.
Errord shows up at point of 'insert' and 'values. Not create table.
0
 
rgbatsonAuthor Commented:
Error

SQL query:

--
-- Dumping data for table `users`
--
INSERT INTO `users` ( `uid` , `name` , `pass` , `mail` , `mode` , `sort` , `threshold` , `theme` , `signature` , `created` , `access` , `login` , `status` , `timezone` , `language` , `picture` , `init` , `data` )
VALUES ( 0, '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, NULL , '', '', '', NULL ) , ( 1, 'admin', '21232f297a57a5a743894a0e4a801fc3', 'rgbatson@hotmail.com', 0, 0, 0, '', '', 1239339414, 1240448948, 1240448762, 1, '-18000', '', '', 'rgbatson@hotmail.com', 'a:1:{s:13:"form_build_id";s:37:"form-97510bb464b935604e74aceacf9e2998";}' ) ;

MySQL said: Documentation
#1062 - Duplicate entry '1' for key 'PRIMARY'
0
 
v2MediaCommented:
How you got that data dump I have no idea, but the insert query is stuffed. Use the one below, works fine. This portion, "( 0, '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, NULL , '', '', '', NULL ) ," simply doesn't belong.
CREATE TABLE IF NOT EXISTS `users` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL DEFAULT '',
  `pass` varchar(32) NOT NULL DEFAULT '',
  `mail` varchar(64) DEFAULT '',
  `mode` tinyint(4) NOT NULL DEFAULT '0',
  `sort` tinyint(4) DEFAULT '0',
  `threshold` tinyint(4) DEFAULT '0',
  `theme` varchar(255) NOT NULL DEFAULT '',
  `signature` varchar(255) NOT NULL DEFAULT '',
  `created` int(11) NOT NULL DEFAULT '0',
  `access` int(11) NOT NULL DEFAULT '0',
  `login` int(11) NOT NULL DEFAULT '0',
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `timezone` varchar(8) DEFAULT NULL,
  `language` varchar(12) NOT NULL DEFAULT '',
  `picture` varchar(255) NOT NULL DEFAULT '',
  `init` varchar(64) DEFAULT '',
  `data` longtext,
  PRIMARY KEY (`uid`),
  UNIQUE KEY `name` (`name`),
  KEY `access` (`access`),
  KEY `created` (`created`),
  KEY `mail` (`mail`),
  FULLTEXT KEY `language` (`language`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 
--
-- Dumping data for table `users`
--
 
INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `mode`, `sort`, `threshold`, `theme`, `signature`, `created`, `access`, `login`, `status`, `timezone`, `language`, `picture`, `init`, `data`) VALUES (1, 'admin', '21232f297a57a5a743894a0e4a801fc3', 'rgbatson@hotmail.com', 0, 0, 0, '', '', 1239339414, 1240448948, 1240448762, 1, '-18000', '', '', 'rgbatson@hotmail.com', 'a:1:{s:13:"form_build_id";s:37:"form-97510bb464b935604e74aceacf9e2998";}');

Open in new window

0
 
rgbatsonAuthor Commented:
Thank you much. Works fine. Never thought to count data items. Don't know how you matched the correct ones to fields. Copied the remaining 5 tables ok one at a time.
The SQL was produced by phpMyAdmin both as a dump and as a table export.
This is all in aid of following drupal_cookbook_10_25_2008.pdf, the section on setting up a local wamp-based environment with drupal handling multiple sites.
Also had to set max_execution_time to 0 in several php ini files to get this far. Old Dell machine with max 512 Mg memory.
0
 
v2MediaCommented:
Do exports/imports as zip or gzip files. Valuable time/resources are wasted pushing uncompressed sql files around.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now