auto_increment field always gets a ZERO

cobalt60

I have created a field in a MySQL 3.23.25 table using the following statement:
ALTER TABLE MyTable ADD ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

It worked fine as far as numbering my current records, but when I INSERT a new record, it gives the ID field a big fat ZERO, every time. I can add records until I'm sick of clicking and they are all ZEROs. According to the documentation, this seems impossible, even when I explicitly send it 0 or NULL:
To paraphrase:"An auto_increment field, when sent 0 or NULL, will automatically be assigned (current_highest_number_in_the auto_increment_field + 1)."

I tried including ID in my field-values pairs just for grins, sent it 0 (which, of course, resulted in 0), NULL (which results in 0) and then removed it again (you're not supposed to include it in the field-value pairs are you??) which, surprise, still results in 0. I'm at a loss and appreciate any comments or suggestions. TIA

Anon

Can you post the INSERT command you are using? And also possibly a dump schema of the table you're attempting the INSERT into?

cobalt60

I\'m home now, so unfortunately I can\'t give you the schema until tomorrow. I can tell you, though, that I don\'t have any other auto_increment fields, keys or primary keys.
The rest are varchar, char, and int fields. I have indices on the remaining fields and some multi-column indices, but this auto_increment thing was occuring prior to creating any indices.

Here\'s the gist of the INSERT:
INSERT INTO table (feature_name,alpha_from,num_from,alpha_to,num_to,page_from,page_to,city,county,zip,feature_type,product_name) VALUES ($feature_name,$alpha_from,...

These variables are populated via a form on the previous page, they click an \'Add Record\' button and the next page does the insert after making sure it\'s not a duplicate (just a great big SELECT WHERE query comparing each variable they passed to each field).

I\'ll post tomorrow with the schema unless I figger it out between now and then! Thanks

Anon

Are you leaving the auto_increment variable blank on the insert command? Let's say that the field "num_from" is the auto_increment field. Your insert statement should look like this:

INSERT INTO table (feature_name,alpha_from,num_from) VALUES ($feature_name,$alpha_from,'')

cobalt60

I ASSUMED (wrongly) that my ALTER TABLE had the desired effect. When I did a SHOW COLUMNS, I saw the problem -- not only did my ID field not have the auto_increment attribute, it was not defined as PRI under Key.

You would think that ALTER TABLE ADD ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST would have worked. Well, when I did an ALTER TABLE and CHANGED ID (again), and re-ran my SHOW COLUMNS, it showed PRI and AUTO_INCREMENT. All is well. Works like a charm.

Thanks, James, for responding, and I apologize for not checking the obvious.

Anon

Jason Jenks wrote:

Thanks, James, for responding, and I apologize for not checking the obvious.

If I had a nickel for everytime I didn't check the obvious, I would be a rich man sitting on a beach in the Caribbean drinking a pina colada, rather than in my studio responding to messages on phpbuilder. : ) At any rate, I'm glad you fixed the problem.