[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

auto_increment not really auto incrementing

Posted on 2007-07-31
11
Medium Priority
?
307 Views
Last Modified: 2008-02-01
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
0
Comment
  • 5
  • 5
11 Comments
 
LVL 24

Expert Comment

by:glcummins
ID: 19600245
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
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 19600312
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
 
LVL 1

Author Comment

by:catonthecouchproductions
ID: 19600458
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
Independent Software Vendors: 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!

 
LVL 40

Expert Comment

by:Richard Quadling
ID: 19600760
Even with what I've said, under normal conditions, the DB should use sequential numbers.

Can you delete the table and recreate it?
0
 
LVL 1

Author Comment

by:catonthecouchproductions
ID: 19602001
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
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 2000 total points
ID: 19606949
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
 
LVL 1

Author Comment

by:catonthecouchproductions
ID: 19609367
Alrighty, thank you so much, I am going to give this a shot!

Thanks man.

Ryan
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 19609388
NP.
0
 
LVL 1

Author Comment

by:catonthecouchproductions
ID: 19609439
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
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 19609495
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
 
LVL 1

Author Comment

by:catonthecouchproductions
ID: 19621265
Okay, thank you!

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

Ryan
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses
Course of the Month17 days, 21 hours left to enroll

829 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