auto_increment not really auto incrementing

Does anyone know what would be the reason for my row ID's going from 7, 0, to 288?

The rows are being created when you login to the website and create a user account, my SQL I used for it is:

CREATE TABLE `critter` (
  `id` bigint(20) NOT NULL auto_increment,
  `username` text NOT NULL,
  `password` varchar(32) NOT NULL default '',
  `name` text NOT NULL,
  `email` text NOT NULL,
  `address` text NOT NULL,
  `address1` varchar(70) NOT NULL default '',
  `city` varchar(70) NOT NULL default '',
  `state` varchar(2) NOT NULL default '',
  `phone` text NOT NULL,
  `businfo` text NOT NULL,
  `website` text NOT NULL,
  `date` text NOT NULL,
  `ip` text NOT NULL,
  `actkey` varchar(40) NOT NULL default '',
  `activated` int(1) NOT NULL default '0',
  `zip` varchar(5) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;


If you need any other information, let me know. I can paste up my code.

Thanks,

Ryan
LVL 1
catonthecouchproductionsAsked:
Who is Participating?
 
Richard QuadlingSenior Software DeveloperCommented:
If the id is used in other tables, then be careful.

If not, then ...

1 - export the data to a form you can easily access - a SQL script would be ideal.
2 - delete the table.
3 - recreate the table.
4 - edit the SQL script to make sure that you have supplied the IDs you want.
5 - make sure the table doesn't use autoinc.
6 - import the data
7 - set the autoinc

That way you've recreated the data with the autoincs corrected.

But.

Having said all of that, in normal circumstances, I cannot see why you are having the problem you are, sorry.
0
 
glcumminsCommented:
Based on the information you provided, there is no reason for this behavior. However, we will need to see the code that actually inserts the records into the table to determine a cause.
0
 
Richard QuadlingSenior Software DeveloperCommented:
A few things.

1 - Can you control the autoinc mechanism to not necessarily be linear, maybe a formula or a trigger.
2 - Does data already exist and you are looking at the inserted IDs?

Some bits ....

To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;




Adding an AUTO_INCREMENT column to a table with ALTER TABLE might not produce the same ordering of the rows on the slave and the master. This occurs because the order in which the rows are numbered depends on the specific storage engine used for the table and the order in which the rows were inserted. If it is important to have the same order on the master and slave, the rows must be ordered before assigning an AUTO_INCREMENT  number. Assuming that you want to add an AUTO_INCREMENT column to the table t1, the following statements produce a new table t2 identical to t1  but with an AUTO_INCREMENT column:





NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number. (Added in MySQL 4.1.1)

This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.) For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. As of MySQL 4.1.1, mysqldump automatically includes a statement in the dump output that enables NO_AUTO_VALUE_ON_ZERO to avoid this problem.


0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
catonthecouchproductionsAuthor Commented:
Hey. here is my PHP for the register.php page which handles that part of the script.

<?php
if(isset($_SESSION['s_username']))
{
      echo 'You are already logged in, you can not register again.';
      exit;
}
if(isset($_POST['submit']))
{

$first = addslashes(trim($_POST['firstname']));
$surname = addslashes(trim($_POST['surname']));
$username = addslashes(trim($_POST['username']));
$email = addslashes(trim($_POST['email']));
$pass = addslashes(trim($_POST['password']));
$conf = addslashes(trim($_POST['confirm']));
$website = addslashes(trim($_POST['website']));

$ip = $_SERVER['REMOTE_ADDR'];
$date = date("d, m y");

if ( $_POST['password'] == $_POST['confirm'] )
{}else{

echo '<script>alert("Your passwords were not the same, please enter the same password in each field.");</script>';
echo '<script>history.back(1);</script>';
exit;

}

$password = md5($pass);

if ((((( empty($first) ) || ( empty($surname) ) || ( empty($username) ) || ( empty($email) ) || ( empty($password) )))))
{

echo '<script>alert("One or more fields was left empty, please try again.");</script>';
echo '<script>history.back(1);</script>';
exit;

}

if((!strstr($email , "@")) || (!strstr($email , ".")))
{

echo '<script>alert("You entered an invalid email address. Please try again.");</script>';
echo '<script>history.back(1);</script>';
exit;

}

$q = mysql_query("SELECT * FROM critter WHERE Username = '$username'") or die(mysql_error());
if(mysql_num_rows($q) > 0)
{

echo '<script>alert("The username you entered is already in use, please try again.");</script>';
echo '<script>history.back(1);</script>';
exit;

}

$name = $first . ' ' . $surname;
$actkey = mt_rand(1, 500).'f78dj899dd';
$act = sha1($actkey);

$query = mysql_query(
"INSERT INTO critter (username, password, name, email, date, actkey, address, address1, city, state, zip, ip, phone, businfo, website)
 VALUES ('$username','$password','$name','$email','$date','$act','$address','$address1','$city','$state','$zip','$ip','$phone','$businfo','$website')"
) or die(mysql_error());
$send = mail($email , "Registration Confirmation" , "Thank you for registering with Critter Classifieds.\n\nYour username and password is below, along with details on how to activate your account.\n\nUser: ".$username."\nPass: ".$pass."\n\nClick the link below to activate your account:\nhttp://www.critterclassifieds.net?a=activate&id=".$act."\n\nPlease do not reply, this is an automated mailer.\n\nThanks", "FROM: auto@mailer.com");

if(($query)&&($send))
{

echo '
<p>Thank you for registering, you will receive an email soon with your login details and your activation link so that you can activate your account.</p>
<p><a href="?a=login">Click here</a> to login once you have activated.</p>
';

} else {

echo '
<p>We are sorry, there appears to be a problem with our script at the moment.</p>
<p>Your data was not lost. Username: '.$username.' | password: '.$pass.' | Email: '.$email.' | Full Name: '.$name.'</p>
<p>Please try again later.</p>
';

}

} else {

?>

Thanks,

Ryan

0
 
Richard QuadlingSenior Software DeveloperCommented:
Even with what I've said, under normal conditions, the DB should use sequential numbers.

Can you delete the table and recreate it?
0
 
catonthecouchproductionsAuthor Commented:
Yeah, that is going to be my next step. Try having them sign up again and see if it starts normal, by that I mean, 1,2,3, etc.

When I go to run my SQL code to create the table, what can I use at the auto_increment line to start it at 1?

I found what you typed above very helpful, I didnt know any of that. Thanks for that! Would you recomend doing what you said?

Ryan
0
 
catonthecouchproductionsAuthor Commented:
Alrighty, thank you so much, I am going to give this a shot!

Thanks man.

Ryan
0
 
Richard QuadlingSenior Software DeveloperCommented:
NP.
0
 
catonthecouchproductionsAuthor Commented:
4 - edit the SQL script to make sure that you have supplied the IDs you want.
5 - make sure the table doesn't use autoinc.

When you say that do you mean:

Say if there 5, 1246, 4

Then

Change them to say 1,2,3 correct?

And when I go to use the import code, make sure the table doesnt have autoinc set and do that manually correct?

Thanks,

Ryan
0
 
Richard QuadlingSenior Software DeveloperCommented:
I would only change the codes if there is no other table linking to the table in question and linking on the ID.

If you are linking based on the username (for example), then that's OK.

0
 
catonthecouchproductionsAuthor Commented:
Okay, thank you!

I'll let you know how I make out with that

Ryan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.