Home

Fix for importing users into a database with auto_increment still enabled with PRIMARY KEY and all

July 19, 2010 - 8:27pm — Anonymous
  1.     /* Where the total number of members in the database is 279, when importing the db, do the red stuff at the bottom:*/
  2.  
  3. DROP TABLE IF EXISTS `users`;
  4. CREATE TABLE IF NOT EXISTS `users` (
  5.   `uid` int(10) unsigned NOT NULL auto_increment,
  6.   `name` varchar(60) NOT NULL default '',
  7.   `pass` varchar(32) NOT NULL default '',
  8.   `mail` varchar(64) default '',
  9.   `mode` tinyint(4) NOT NULL default '0',
  10.   `sort` tinyint(4) default '0',
  11.   `threshold` tinyint(4) default '0',
  12.   `theme` varchar(255) NOT NULL default '',
  13.   `signature` varchar(255) NOT NULL default '',
  14.   `created` int(11) NOT NULL default '0',
  15.   `access` int(11) NOT NULL default '0',
  16.   `login` int(11) NOT NULL default '0',
  17.   `status` tinyint(4) NOT NULL default '0',
  18.   `timezone` varchar(8) default NULL,
  19.   `language` varchar(12) NOT NULL default '',
  20.   `picture` varchar(255) NOT NULL default '',
  21.   `init` varchar(64) default '',
  22.   `data` longtext,
  23.   `signature_format` smallint(6) NOT NULL default '0',
  24.   PRIMARY KEY  (`uid`),
  25.   UNIQUE KEY `name` (`name`),
  26.   KEY `access` (`access`),
  27.   KEY `created` (`created`),
  28.   KEY `mail` (`mail`)
  29. )  ENGINE=InnoDB AUTO_INCREMENT=432 DEFAULT CHARSET=utf8 AUTO_INCREMENT=280;  // <---IMPORTANT. Note the number where to start the auto_imcrement!!
  30.  
  31. INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `mode`, `sort`, `threshold`, `theme`, `signature`, `created`, `access`, `login`, `status`, `timezone`, `language`, `picture`, `init`, `data`, `signature_format`) VALUES
  32. (0, '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, NULL, '', '', '', NULL, 0);
  33.  
  34. Edit Comment:
  35.  
  36.     * Heading 1
  37.     * Heading 2
  38.     * Heading 3
  39.     * ---------------
  40.     * Bold
  41.     * Italic
  42.     * ---------------
  43.     * Bulleted List
  44.     * Numeric List
  45.     * ---------------
  46.     * Link
  47.     * Picture
  48.     * ---------------
  49.     * Quotes
  50.     * Code
  51.     * ---------------
  52.     * Ticket
  53.     * Repository Revision
  54.     * Repository Source File
  55.     * Notebook Page
  56.     * ---------------
  57.     * Preview
  58.     * Help
  59.  
  60. Using Drupal UID is fine for membership site. Best way to migrate to other servers is to use the http://drupal.org/project/backup_migrate module (already installed). If importing from a raw mysql database *sql file, heed this: The users table auto_increments the uid field in the users table. If you export the users table with auto_increment enabled, it will re-flow the uids. This is not good because then the uid on the web site will not match the uid on the membership card. So, use backup/migrate under the admin/content section of Drupal 6. Here is why you should use this module: It automatically compensates for the uid auto_complete, so IF YOU ARE USING myPhpMyAdmin web or mysqldump, look at the way to rewrite the sql commands in the backup file before imprting into a new database: /* Where the total number of members in the database is 279, when importing the db, do the red stuff at the bottom:*/ DROP TABLE IF EXISTS `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, `signature_format` smallint(6) NOT NULL default '0', PRIMARY KEY (`uid`), UNIQUE KEY `name` (`name`), KEY `access` (`access`), KEY `created` (`created`), KEY `mail` (`mail`) ) ENGINE=InnoDB AUTO_INCREMENT=432 DEFAULT CHARSET=utf8 AUTO_INCREMENT=280; // <---IMPORTANT. Note the number where to start the auto_imcrement!! INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `mode`, `sort`, `threshold`, `theme`, `signature`, `created`, `access`, `login`, `status`, `timezone`, `language`, `picture`, `init`, `data`, `signature_format`) VALUES (0, '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, NULL, '', '', '', NULL, 0);
  61. /projects/7/preview_text
  62. /docs/topics/
  63. or Cancel

Submit Fix

Any tags you'd like to associate with your code, delimitered by commas (example: Views, CCK, Module, etc).
Select the syntax highlighting mode to use.