MySQL Auto Increment – Inserting Missing Day of Data into Table with Incremented Primary Key

auto-incrementinsertMySQL

So I recently switched servers, but there is a day inbetween the switch of servers where the data didn't get carried over. I have exported the missing data in a CSV.

I know there is 617 rows to be inserted into the "primary" database, problem is there is entries which have already taken the "primary key" (in this case, it is called "id"). This ID auto increments by 1.

What I would like to do, is the following:

starting at id = 552611 ADD $ID + 618 & repeat till end of table.

This would allow me to insert the 617 keys starting at:

id = 552611

Ending at:

id = 553228

My SQL-Foo is not that strong! Can anybody point me in the right direction?

Best Answer

Since you're changing the identity of the row, can we assume that it's not used as a foreign key of another table? If it's only purpose is to uniquely identify the row of the table it's on, then don't bother attempting to assign it a new identity because it's already auto incremented by one, upload your CSV without specifying the id column and it will take care of the id.

You'd have to worry about key collision and identity_insert etc, it's just a mess, but if your heart is set on it, there are ways it could be done.

Related Solutions

Mysql – Getting duplicate primary key for auto increment column

I reached the max possible value of an int column. Problem resolved by altering table and setting big int than int.

The signed range of MIDINT is –8388608 to 8388607. The unsigned range is 0 to 16777215

Reference: http://help.scibit.com/mascon/masconMySQL_Field_Types.html

MySQL Auto Increment Primary ID – Inserting Random Values Issue

I'm having a very difficult time thinking of any possible misbehaving mechanism inside MySQL or InnoDB that could cause this behavior as the result of a bug, without throwing errors or causing a crash. It seems much more likely that the table is being updated by the application. Instead of writing new rows, something is finding and updating old ones.

If that's not supposed to be happening, I would put a trigger on this table to block those updates.

mysql> CREATE TRIGGER txn_bu BEFORE UPDATE ON txn FOR EACH ROW 
       SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'this table does not support updates';
Query OK, 0 rows affected (0.14 sec)

mysql> UPDATE txn SET `timestamp` = NOW() WHERE id = 1;
ERROR 1644 (45000): this table does not support updates

mysql>

This makes directly updating rows impossible. Note that you don't need the usual DELIMITER change and BEGIN/END blocks because the trigger is comprised of a single simple statement.

If the table is never updated then the trigger would never fire, so there's no significant overhead associated with having it defined. If it sees an update query then it's going to throw an exception back to the application which should generate exception reports or support tickets. Arguably, if your application has a defect that's stomping data, you want it to fail hard, instead of soft.

Best Answer

Related Solutions

Mysql – Getting duplicate primary key for auto increment column

MySQL Auto Increment Primary ID – Inserting Random Values Issue

Related Question