mysql: setting auto_increment=@variable

PhantomBeaker

Hi all,

I'm having a wee problem with a mysql query.

First a little background: there's a program that uses a table, let's call it BLAH. Now, blah's primary key wasn't set to be auto_increment from the beginning. Now I need to write a flat mysql query to make it so - I need it to be in a pretty flat sql file as we have to distribute this to other users of this program (traditionally any other updates have been in this flat form - that's why I'm reluctant to move away from this).

After much banging my head against a wall, I've figured out the basic query to do it.

alter table BLAH auto_increment=<$safe number>

Where $safe_number is one that doesn't conflict with any entries already in BLAH

After that, I do

alter table change BLAHID BLAHID ...details... auto_increment

And then auto_increment starts from $safe_number. Now, because this is going out to users that will have differently populated BLAH tables, I'd like to select the next free number.

Problem is, even though we're using mysql 5.0 I can't do anything like

set @safe_number=(select max(BLAHID) + 1 from BLAH) ;; This works
alter table BLAH auto_increment=@safe_number ;; this doesn't

Does anyone know of any workarounds to make it work like I intended without resorting to glue code?

Cheers,
Aoife

forbairt

do you have access to this via phpmyadmin ?

PhantomBeaker

I do, at least for our setup, but ideally, I'd like this to be automatable, as it's going to be part of our upgrade process for other users (i.e. when the version bumps, it'll sort out this table for you but without dropping existing data).