auto_increment weird behavior

[QatQat]

Hi there,

I have developed an application, where the main table, Jobmain, has got JobID, key, auto_increment.

Now an ASP page allows users to create new jobsheets online and shows them the jobID on the page.

To get the JobID before it is inserted I used

select 1+max(jobid) from jobmain and it usually works, but if you delete a record then it does not show the correct ID anymore.

I then tried to avoid this using

select 1+jobid from jobmain order by jobid desc limit 1

but surprisingly I still get the same error.

MySQL seems to ignore the deleted records but does not re-assign the same ID to the next one.

What else can I try?

Thanks


Qatqat



Life is what happens when you are making other plans.

 

[QatQat]

I found out also that

select last_insert_id()

always shows either 0 or 277 when my jobid count is over 6000.

If I use

select last_insert_id() from jobmain
it shows 0 or 277 a number of times which is exactly equal to the number of records; which lead me to


select count(last_insert_id()) from jobmain

which is working correctly but I refuse to use it.
There must be a better ways of getting the current ID

Bye


Qatqat

Bye

Qatqat

Life is what happens when you are making other plans.

 

[sleipnir214]

One a value has been used in an auto_increment column, it will not be reused. This is a feature, not a bug. It prevents collisions within related tables if those subordinate tables are not cleaned up properly.

But you're going to continually run into problems by explicitly manipulating an auto_increment column. In your specific case, last_insert_id() returns the last auto_increment value generated by MySQL. If you are explicitly setting the value of the auto_increment column, then MySQL did not generate a value.

Basically, you're not letting MySQL do what it's supposed to do. When inserting records into a table with an auto_increment column, just don't reference the column at all -- MySQL will generate a value, and last_insert_id() will word properly.

Also, in the query &quot;SELECT last_insert_id() from <tablename>&quot;, the &quot;from <tablename>&quot; clause is meaningless and ignored. The last_insert_id() is maintained on a per-connection basis, and only one value is remembered -- the last auto_increment value MySQL generated for your connection, regardless of which table you used in your connection.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[QatQat]

Hi Sleipnir, thanks for your time but perhaps I did not explain my problem clearly

I am letting MySQL self generate the value. The only thing is that I would like to know it in advance as people booking the unit in have to put a sticker on the unit itself with the jobsheet number.

I know that auto_increment does not let you reuse a number and I am fine with it.
If you try this for yourself you will understand what is happening

1)do an insert in any table you have got with auto_increment

2)now run

select 1+id from table order by id desc limit 1

You shoul get a number that will be the next available ID
This should return a correct value

3)Now run

delete from table where id = 'the id you just inserted'

4) try re-run number 2

you will get a wrong value as it will still give you the same result but that ID is no longer available.


So my problem:

Is there a reliable way of indicating what will be the next available ID before inserting?

Thanks in advance


Qatqat

Life is what happens when you are making other plans.

 

[liuwt]

Hi Qatqat,

maybe you could do this, although it causes a little overhead:

1.lock the table
2.insert something
3.get last_insert_id() and store it in a variable, lets say $id
4.delete the thing you inserted (if it's useless)
5.you get the next available ID incrementing $id everytime.
...do what you want...
x.unlock the table

Greetings

Smash your head on keyboard to continue...

 

[sleipnir214]

You're working in a multi-user environment. There is no guaranty that any guess at the next auto_increment number will be accurate, because the activities of other users can invalidated the number you have calculated.

If you need to produce an auto_increment ID before you have all the data for a record, simply insert a blank record then fetch the ID created. After that, update your empty record when you get the rest of your data.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[QatQat]

Thanks Sleipnir,

I think that will work. It is a bit of a long way from the programming point of view but it makes sense.

Thanks a lot everyone for your time.

Bye

Qatqat

Life is what happens when you are making other plans.