tags:

views:

17

answers:

1

I have two databases that need to have their auto increment ids on various tables synced. Right now, I have a PHP function that checks the current auto increment id for both tables and then sets the lowest to the highest. If there is a better way to do this, I'm all ears.

I really don't want to give the web user alter permissions, as a SQL injection could clear all passwords (or something else silly). Is there any way to restrict a MySQL user to changing auto increment without opening it up to alter statements?

A: 

In MySQL, you can set the value for an autoincrement column. MySQL will automatically make the next number one higher. Let's say I have two records so far, so the highest value in the autoincrement id field is 2. I insert a new record, specifying the value.

INSERT INTO User (id, name) values(5, 'john')

The next id value will be 6:

INSERT INTO User (name) values('dave')

There is no need to alter the table.

In MSSQL, you need to flip a switch to do the same thing.

SET IDENTITY_INSERT ON
Marcus Adams
I think that only works for MSSQL. I wasn't able to find any information for this on MySQL. If you have a link that I'm missing, that would be be very helpful.
psayre23
You're right. In MySQL, you can actually just set the value for an identity column. MySQL will automatically use the next higher for the next one. I'll update my answer.
Marcus Adams
I'm actually talking about keeping two identical tables on different databases synced. The problem is when I move data from database to the other, all my links are broken. Rather then keep a table of the connections and redoing them, I'm going to make sure none of the keys are going to conflict. I found it might work with triggers and store procedures, but I'm not sure it that is the solution.
psayre23
I'm actually talking about keeping two identical tables on different databases synced. -> and you can't use replication? My other thought is to get last_id and use that when inserting on the second db to make sure the record is identical.
bigredbob