MySql id reSequence procedure for innodb

| | Comments (0) | TrackBacks (0)

Let me begin with this warning. The procedure below will alter all the rows of your tables. If you don’t feel comfortable doing this please contact us here at box201.com and we can talk about a maintenance or rescue operation. Here is the scenario which led down the resequence path. While building a website, we realized newly registered users would not have all the information  and the “not-null” columns needed something other than NULL but not the default value.

So how do I correct a table which has an order I want to keep but allow us to insert a new entry for id=1. How about a procedure? Here is a way to pass the table name and last record id to start from and reorder the table.

Couple notes here notice the ‘_Set’. You must have a space before the Set or the table name looks like `sometableSet` due to the concat. Also setting the Delimiter is the only way to create the stored procedure with “;” after each line.

DELIMITER $$

DROP PROCEDURE IF EXISTS reSequence $$
CREATE PROCEDURE reSequence (tableName varchar(30), maxid int)
BEGIN

set @v1 = maxid;
set @stmt = concat('UPDATE ', tableName, ' SET id = @v1 + 1
 WHERE id = @v1');
PREPARE stmt2 FROM @stmt;

  WHILE @v1 > 0 DO
    EXECUTE stmt2;
    SET @v1 = @v1 - 1;
  END WHILE;
 
DEALLOCATE PREPARE stmt2;

END $$

DELIMITER ;

Call reSequence like this….

mysql>CALL reSequence('cars', 1415);
mysql> ALTER TABLE colors AUTO_INCREMENT = 1416;
Query OK, 1415 rows affected (0.05 sec)
Records: 1415  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Where `cars` is the table name and 1415 is the last populated id in the table. As long as no foreign keys exist the procedure will take id=1415 and update the id to id=1416 and work it’s way down to 1 setting the starting record at “2” so you can update row id=1416 to row id=1. The problem is the auto_increment gets set forward one to 1417 so you will need to reset auto_increment back to 1416;

Example - my db is innodb and the colors table looks like this:

mysql> describe colors;

+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from colors;
+----+---------------+
| id | name |
+----+---------------+
| 1 | red |
| 2 | green |
| 3 | blue |
+----+---------------+
3 rows in set (0.00 sec)

mysql> CALL reSequence('colors',3);
ERROR 1451 (23000): Cannot delete or update a parent row: a
foreign key constraint fails (`my_testdb`.`bicycles`,
CONSTRAINT `bicycles_ibfk_2` FOREIGN KEY (`color_id`)
REFERENCES `colors` (`id`))
mysql> select * from colors;
+----+---------------+
| id | name |
+----+---------------+
| 1 | red |
| 2 | green |
| 4 | blue |
+----+---------------+
3 rows in set (0.00 sec)

mysql> select * from bycycles;
+----+---------------+----------+
| id | name | color_id |
+----+---------------+----------+
| 1 | schwinn | NULL |
| 2 | high_flyier | NULL |
| 3 | blue_flame | NULL |
| 4 | hot_mongoose | 3 |
+----+---------------+----------+

Here we need to adjust the parent table foreign key to the new id then continue. color_id=3 is the color blue. Select first the records which are offending. Since the query stopped at id=3 and didn’t touch id=2 so we can assume id=4 color_id=3 is the offending row.

mysql> select id from bicylcles where color_id = 3;
+----+
| id |
+----+
| 4 |
+----+
1 row in set (0.00 sec)

mysql> select id from bicycles where color_id = 4;
Empty set (0.00 sec)

#### NOTE HERE. We are safe here and only need to fix this one row to complete the resequence. More complex data may require this process a few times. ####


mysql> update bicycles set color_id = 4 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Remember we do not want to change id=4 anymore. The blue bike is still blue with id=4, we just restart where we left off.

mysql> call reSequence('colors',2);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from colors;
+----+---------------+
| id | name |
+----+---------------+
| 2 | red |
| 3 | green |
| 4 | blue |
+----+---------------+
3 rows in set (0.00 sec)

mysql> select id from bycycles where color_id = 3;
Empty set (0.00 sec)

We are clear so now I can update colors table with id=1;

mysql> insert into colors (id, name) values (1, 'white');
Query OK, 1 row affected (0.43 sec)

mysql> select * from colors;
+----+---------------+
| id | color |
+----+---------------+
| 1 | white |
| 2 | red |
| 3 | green |
| 4 | blue |
+----+---------------+
4 rows in set (0.00 sec)

There is a problem here as auto_increment is now set at “6”;


mysql> SELECT Auto_increment FROM information_schema.tables
    -> WHERE table_name='colors';
+----------------+
| Auto_increment |
+----------------+
| 6 |
+----------------+
1 row in set (0.00 sec)

mysql> alter table colors auto_increment = 5;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into colors (`name`) values ( 'black');
Query OK, 1 row affected (0.00 sec)

mysql> select * from colors;
+----+---------------+
| id | name |
+----+---------------+
| 1 | white |
| 2 | red |
| 3 | green |
| 4 | blue |
| 5 | black |
+----+---------------+
5 rows in set (0.00 sec)

mysql>

Thats how to re-sequence a table without manually updating each row. It works great on smaller tables but be very cautious the changes are very fast.

0 TrackBacks

Listed below are links to blogs that reference this entry: MySql id reSequence procedure for innodb.

TrackBack URL for this entry: http://www.box201.com/cgi-bin/mt/mt-tb.cgi/5

Leave a comment

About this Entry

This page contains a single entry by Agrapha published on May 5, 2011 12:19 AM.

freelance php development was the previous entry in this blog.

Find recent content on the main index or look in the archives to find all content.