Solved

how to set auto increment = 1

Posted on 2011-09-21
2
460 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

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 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