Solved

how to set auto increment = 1

Posted on 2011-09-21
2
459 Views
Last Modified: 2012-05-12
Experts,

if i run the following scripts i still get three records with id = 10, 20, and 30. What am I missing here?

i'm completely new to mySQL, but this is odd...

drop table admin_users;

CREATE TABLE `admin_users` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `admin_logon` varchar(32) NOT NULL,
  `admin_psswd` varchar(32) NOT NULL,
  `update_psswd` int(11) NOT NULL DEFAULT '0',
  `dept_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

alter table admin_users auto_increment = 1;

INSERT INTO `admin_users`
(`admin_logon`,
`admin_psswd`,
`update_psswd`,
`dept_id`,
`role_id`)
VALUES
('dduck','test',0,4,2);

INSERT INTO `admin_users`
(`admin_logon`,
`admin_psswd`,
`update_psswd`,
`dept_id`,
`role_id`)
VALUES
('mmouse','test',0,4,2);

INSERT INTO `admin_users`
(`admin_logon`,
`admin_psswd`,
`update_psswd`,
`dept_id`,
`role_id`)
VALUES
('pluto','test',0,4,2);

select * from admin_users;

Result:

ID

10      2011-09-21 04:09:39      dduck      test      0      4      2
20      2011-09-21 04:09:39      mmouse      test      0      4      2
30      2011-09-21 04:09:40      pluto      test      0      4      2

TIA!
0
Comment
Question by:crafuse
2 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 36572420
Check to see the seed value of the autoincrement isn't set to 10.

You can check by:

SELECT Auto_increment FROM information_schema.tables WHERE table_name='the_table_you_want';

 As noted elsewhere you can change by using the system variable @@set_auto_increment_increment

SET @@auto_increment_increment=1;

refer
http://stackoverflow.com/questions/206751/mysql-autoincrement-column-jumps-by-10-why
0
 

Author Closing Comment

by:crafuse
ID: 36572461
thanks, ya i found that too...
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question