Re: auto_increment counter changes even if query fails
Maciek,
Hi,
Is this correct for MySQL to increment the auto_increment counter if the
INSERT query fails? For example:
mysql CREATE TABLE test ( i INT NOT NULL AUTO_INCREMENT, c CHAR(16) NOT
NULL, PRIMARY KEY(i), UNIQUE(c)) type=InnoDB;
Query OK, 0 rows affected (0.25 sec)
mysql INSERT INTO test VALUES('', 'test1');
Query OK, 1 row affected (0.05 sec)
mysql INSERT INTO test VALUES('', 'test2');
Query OK, 1 row affected (0.24 sec)
mysql INSERT INTO test VALUES('', 'test2');
ERROR 1062: Duplicate entry 'test2' for key 2
mysql INSERT INTO test VALUES('', 'test3');
Query OK, 1 row affected (0.02 sec)
mysql SELECT * FROM test;
+---+---+
| i | c |
+---+---+
| 1 | test1 |
| 2 | test2 |
| 4 | test3 |
+---+---+
3 rows in set (0.00 sec)
auto-inc values are assigned past the transactional mechanism, for
efficiency, and to avoid deadlocks. Therefore a rollback following a failing
insert statement does not roll back the counter.
If you want a strictly contiguous sequence of values, use the standard
technique of a counter row in another table, from which you assign the
values to your insert statements.
CREATE TABLE test_counter(a INT) TYPE = INNODB;
SELECT a FROM testcounter FOR UPDATE;
UPDATE testcounter SET a = a + 1;
INSERT INTO test (...);
Regards,
Heikki
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto_increment counter changes even if query fails
Maciek,
Hi,
Is this correct for MySQL to increment the auto_increment counter if the
INSERT query fails? For example:
mysql CREATE TABLE test ( i INT NOT NULL AUTO_INCREMENT, c CHAR(16) NOT
NULL, PRIMARY KEY(i), UNIQUE(c)) type=InnoDB;
Query OK, 0 rows affected (0.25 sec)
mysql INSERT INTO test VALUES('', 'test1');
Query OK, 1 row affected (0.05 sec)
mysql INSERT INTO test VALUES('', 'test2');
Query OK, 1 row affected (0.24 sec)
mysql INSERT INTO test VALUES('', 'test2');
ERROR 1062: Duplicate entry 'test2' for key 2
mysql INSERT INTO test VALUES('', 'test3');
Query OK, 1 row affected (0.02 sec)
mysql SELECT * FROM test;
+---+---+
| i | c |
+---+---+
| 1 | test1 |
| 2 | test2 |
| 4 | test3 |
+---+---+
3 rows in set (0.00 sec)
auto-inc values are assigned past the transactional mechanism, for
efficiency, and to avoid deadlocks. Therefore a rollback following a failing
insert statement does not roll back the counter.
If you want a strictly contiguous sequence of values, use the standard
technique of a counter row in another table, from which you assign the
values to your insert statements.
CREATE TABLE test_counter(a INT) TYPE = INNODB;
SELECT a FROM testcounter FOR UPDATE;
UPDATE testcounter SET a = a + 1;
INSERT INTO test (...);
Regards,
Heikki
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto_increment counter changes even if query fails
Maciek,
Hi,
Is this correct for MySQL to increment the auto_increment counter if the
INSERT query fails? For example:
mysql CREATE TABLE test ( i INT NOT NULL AUTO_INCREMENT, c CHAR(16) NOT
NULL, PRIMARY KEY(i), UNIQUE(c)) type=InnoDB;
Query OK, 0 rows affected (0.25 sec)
mysql INSERT INTO test VALUES('', 'test1');
Query OK, 1 row affected (0.05 sec)
mysql INSERT INTO test VALUES('', 'test2');
Query OK, 1 row affected (0.24 sec)
mysql INSERT INTO test VALUES('', 'test2');
ERROR 1062: Duplicate entry 'test2' for key 2
mysql INSERT INTO test VALUES('', 'test3');
Query OK, 1 row affected (0.02 sec)
mysql SELECT * FROM test;
+---+---+
| i | c |
+---+---+
| 1 | test1 |
| 2 | test2 |
| 4 | test3 |
+---+---+
3 rows in set (0.00 sec)
auto-inc values are assigned past the transactional mechanism, for
efficiency, and to avoid deadlocks. Therefore a rollback following a failing
insert statement does not roll back the counter.
If you want a strictly contiguous sequence of values, use the standard
technique of a counter row in another table, from which you assign the
values to your insert statements.
CREATE TABLE test_counter(a INT) TYPE = INNODB;
SELECT a FROM testcounter FOR UPDATE;
UPDATE testcounter SET a = a + 1;
INSERT INTO test (...);
Regards,
Heikki
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto_increment counter changes even if query fails
Maciek,
Hi,
Is this correct for MySQL to increment the auto_increment counter if the
INSERT query fails? For example:
mysql CREATE TABLE test ( i INT NOT NULL AUTO_INCREMENT, c CHAR(16) NOT
NULL, PRIMARY KEY(i), UNIQUE(c)) type=InnoDB;
Query OK, 0 rows affected (0.25 sec)
mysql INSERT INTO test VALUES('', 'test1');
Query OK, 1 row affected (0.05 sec)
mysql INSERT INTO test VALUES('', 'test2');
Query OK, 1 row affected (0.24 sec)
mysql INSERT INTO test VALUES('', 'test2');
ERROR 1062: Duplicate entry 'test2' for key 2
mysql INSERT INTO test VALUES('', 'test3');
Query OK, 1 row affected (0.02 sec)
mysql SELECT * FROM test;
+---+---+
| i | c |
+---+---+
| 1 | test1 |
| 2 | test2 |
| 4 | test3 |
+---+---+
3 rows in set (0.00 sec)
auto-inc values are assigned past the transactional mechanism, for
efficiency, and to avoid deadlocks. Therefore a rollback following a failing
insert statement does not roll back the counter.
If you want a strictly contiguous sequence of values, use the standard
technique of a counter row in another table, from which you assign the
values to your insert statements.
CREATE TABLE test_counter(a INT) TYPE = INNODB;
SELECT a FROM testcounter FOR UPDATE;
UPDATE testcounter SET a = a + 1;
INSERT INTO test (...);
Regards,
Heikki
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto_increment counter changes even if query fails
Maciek,
Hi,
Is this correct for MySQL to increment the auto_increment counter if the
INSERT query fails? For example:
mysql CREATE TABLE test ( i INT NOT NULL AUTO_INCREMENT, c CHAR(16) NOT
NULL, PRIMARY KEY(i), UNIQUE(c)) type=InnoDB;
Query OK, 0 rows affected (0.25 sec)
mysql INSERT INTO test VALUES('', 'test1');
Query OK, 1 row affected (0.05 sec)
mysql INSERT INTO test VALUES('', 'test2');
Query OK, 1 row affected (0.24 sec)
mysql INSERT INTO test VALUES('', 'test2');
ERROR 1062: Duplicate entry 'test2' for key 2
mysql INSERT INTO test VALUES('', 'test3');
Query OK, 1 row affected (0.02 sec)
mysql SELECT * FROM test;
+---+---+
| i | c |
+---+---+
| 1 | test1 |
| 2 | test2 |
| 4 | test3 |
+---+---+
3 rows in set (0.00 sec)
auto-inc values are assigned past the transactional mechanism, for
efficiency, and to avoid deadlocks. Therefore a rollback following a failing
insert statement does not roll back the counter.
If you want a strictly contiguous sequence of values, use the standard
technique of a counter row in another table, from which you assign the
values to your insert statements.
CREATE TABLE test_counter(a INT) TYPE = INNODB;
SELECT a FROM testcounter FOR UPDATE;
UPDATE testcounter SET a = a + 1;
INSERT INTO test (...);
Regards,
Heikki
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto_increment counter changes even if query fails
Maciek,
Hi,
Is this correct for MySQL to increment the auto_increment counter if the
INSERT query fails? For example:
mysql CREATE TABLE test ( i INT NOT NULL AUTO_INCREMENT, c CHAR(16) NOT
NULL, PRIMARY KEY(i), UNIQUE(c)) type=InnoDB;
Query OK, 0 rows affected (0.25 sec)
mysql INSERT INTO test VALUES('', 'test1');
Query OK, 1 row affected (0.05 sec)
mysql INSERT INTO test VALUES('', 'test2');
Query OK, 1 row affected (0.24 sec)
mysql INSERT INTO test VALUES('', 'test2');
ERROR 1062: Duplicate entry 'test2' for key 2
mysql INSERT INTO test VALUES('', 'test3');
Query OK, 1 row affected (0.02 sec)
mysql SELECT * FROM test;
+---+---+
| i | c |
+---+---+
| 1 | test1 |
| 2 | test2 |
| 4 | test3 |
+---+---+
3 rows in set (0.00 sec)
auto-inc values are assigned past the transactional mechanism, for
efficiency, and to avoid deadlocks. Therefore a rollback following a failing
insert statement does not roll back the counter.
If you want a strictly contiguous sequence of values, use the standard
technique of a counter row in another table, from which you assign the
values to your insert statements.
CREATE TABLE test_counter(a INT) TYPE = INNODB;
SELECT a FROM testcounter FOR UPDATE;
UPDATE testcounter SET a = a + 1;
INSERT INTO test (...);
Regards,
Heikki
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
