subject:"Problems with auto_increment updating when \(i think\) it shouldn't"

Problems with auto_increment updating when (i think) it shouldn't

2009-08-07 Thread Proemial

Hey folks.  I'm getting some weird behaviour out of Auto_increment.
If I enter a attempt to INSERT a row into a table with a UNIQUE index,
where the insert would violate uniqueness of existing data, I'm seeing
the auto_increment increase even though the insert fails.

The server in question is 5.1.34 running as master.  Slave is also 5.1.34.

First noticed through a script operating over ODBC, but replicated by
hand through the query browser.

I couldn't see anything in the ref manual stating this as standard
behaviour -- but I easily could have missed something there.  Can
someone point me in the right direction?

Thank you!
Martin

Using Mysql 5.1.34
TEST CASE:

CREATE TABLE  `test`.`test_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL, PRIMARY KEY (`id`),
UNIQUE KEY `index_2` (`name`)
)
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

insert some values


1, 'test'
2, 'test2'
3, 'test3'


SHOW TABLE STATUS
Nametest_table
Engine  InnoDB
Version 10
Row_format  Compact
Rows3
Avg_row_length  5461
Data_length 16384
Max_data_length 0
Index_length16384
Data_free   0
Auto_increment  4
Create_time 2009-08-07 09:33:04
Update_time 
Check_time  
Collation   latin1_swedish_ci
Checksum
Create_options  
Comment 

---
INSERT INTO test.test_table (name) VALUES ('test')

SHOW TABLE STATUS
Nametest_table
...
Auto_increment  5

---
INSERT IGNORE test.test_table (name) VALUES ('test')

SHOW TABLE STATUS
Nametest_table
...
Auto_increment  6



-- 
---
This is a signature.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Problems with auto_increment updating when (i think) it shouldn't

2009-08-07 Thread Johnny Withers

It will also update the auto_increment column when you ROLLBACK a failed
insert:

mysql USE test;
Database changed
mysql SELECT * FROM t1\G
Empty set (0.00 sec)
mysql DROP TABLE t1;
Query OK, 0 rows affected (0.06 sec)
mysql
mysql CREATE TABLE t1(
- id INT UNSIGNED NOT NULL AUTO_INCREMENT,
- c1 VARCHAR(255),
- PRIMARY KEY(id)
- ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.13 sec)
mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO t1(c1) VALUES('TEST1');
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO t1(c1) VALUES('TEST2');
Query OK, 1 row affected (0.00 sec)
mysql ROLLBACK;
Query OK, 0 rows affected (0.02 sec)
mysql SHOW CREATE TABLE t1\G
*** 1. row ***
   Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB *AUTO_INCREMENT=3* DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql SELECT * FROM t1\G
Empty set (0.00 sec)
mysql INSERT INTO t1(c1) VALUES('TEST3');
Query OK, 1 row affected (0.03 sec)
mysql SELECT * FROM t1\G
*** 1. row ***
id: 3
c1: TEST3
1 row in set (0.00 sec)
mysql

I believe this is how it has to work. In the event that I start a
transaction, then another transaction starts, mine fails, the other
completes and commit's, it has to get ID #3 and not ID #1. At the time the
transaction was taking place, ID #1 and #2 were in use.

Essentially, your SQL statement is a single transaction with AUTO_COMMIT set
to '1'.



On Fri, Aug 7, 2009 at 8:55 AM, Proemial proem...@gmail.com wrote:

 Hey folks.  I'm getting some weird behaviour out of Auto_increment.
 If I enter a attempt to INSERT a row into a table with a UNIQUE index,
 where the insert would violate uniqueness of existing data, I'm seeing
 the auto_increment increase even though the insert fails.

 The server in question is 5.1.34 running as master.  Slave is also 5.1.34.

 First noticed through a script operating over ODBC, but replicated by
 hand through the query browser.

 I couldn't see anything in the ref manual stating this as standard
 behaviour -- but I easily could have missed something there.  Can
 someone point me in the right direction?

 Thank you!
 Martin

 Using Mysql 5.1.34
 TEST CASE:

 CREATE TABLE  `test`.`test_table` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(45) NOT NULL, PRIMARY KEY (`id`),
 UNIQUE KEY `index_2` (`name`)
 )
 ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

 insert some values

 
 1, 'test'
 2, 'test2'
 3, 'test3'
 

 SHOW TABLE STATUS
 Nametest_table
 Engine  InnoDB
 Version 10
 Row_format  Compact
 Rows3
 Avg_row_length  5461
 Data_length 16384
 Max_data_length 0
 Index_length16384
 Data_free   0
 Auto_increment  4
 Create_time 2009-08-07 09:33:04
 Update_time
 Check_time
 Collation   latin1_swedish_ci
 Checksum
 Create_options
 Comment

 ---
 INSERT INTO test.test_table (name) VALUES ('test')

 SHOW TABLE STATUS
 Nametest_table
 ...
 Auto_increment  5

 ---
 INSERT IGNORE test.test_table (name) VALUES ('test')

 SHOW TABLE STATUS
 Nametest_table
 ...
 Auto_increment  6



 --
 ---
 This is a signature.

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net




-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

Re: Problems with auto_increment updating when (i think) it shouldn't

2009-08-07 Thread Proemial

Hmm, that makes sense.  I should have thought of that.  Thanks!

On Fri, Aug 7, 2009 at 12:32 PM, Johnny Withersjoh...@pixelated.net wrote:
 It will also update the auto_increment column when you ROLLBACK a failed
 insert:

 mysql USE test;
 Database changed
 mysql SELECT * FROM t1\G
 Empty set (0.00 sec)
 mysql DROP TABLE t1;
 Query OK, 0 rows affected (0.06 sec)
 mysql
 mysql CREATE TABLE t1(
     - id INT UNSIGNED NOT NULL AUTO_INCREMENT,
     - c1 VARCHAR(255),
     - PRIMARY KEY(id)
     - ) ENGINE=InnoDB;
 Query OK, 0 rows affected (0.13 sec)
 mysql START TRANSACTION;
 Query OK, 0 rows affected (0.00 sec)
 mysql INSERT INTO t1(c1) VALUES('TEST1');
 Query OK, 1 row affected (0.00 sec)
 mysql INSERT INTO t1(c1) VALUES('TEST2');
 Query OK, 1 row affected (0.00 sec)
 mysql ROLLBACK;
 Query OK, 0 rows affected (0.02 sec)
 mysql SHOW CREATE TABLE t1\G
 *** 1. row ***
    Table: t1
 Create Table: CREATE TABLE `t1` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `c1` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
 1 row in set (0.00 sec)
 mysql SELECT * FROM t1\G
 Empty set (0.00 sec)
 mysql INSERT INTO t1(c1) VALUES('TEST3');
 Query OK, 1 row affected (0.03 sec)
 mysql SELECT * FROM t1\G
 *** 1. row ***
 id: 3
 c1: TEST3
 1 row in set (0.00 sec)
 mysql

 I believe this is how it has to work. In the event that I start a
 transaction, then another transaction starts, mine fails, the other
 completes and commit's, it has to get ID #3 and not ID #1. At the time the
 transaction was taking place, ID #1 and #2 were in use.

 Essentially, your SQL statement is a single transaction with AUTO_COMMIT set
 to '1'.



 On Fri, Aug 7, 2009 at 8:55 AM, Proemial proem...@gmail.com wrote:

 Hey folks.  I'm getting some weird behaviour out of Auto_increment.
 If I enter a attempt to INSERT a row into a table with a UNIQUE index,
 where the insert would violate uniqueness of existing data, I'm seeing
 the auto_increment increase even though the insert fails.

 The server in question is 5.1.34 running as master.  Slave is also 5.1.34.

 First noticed through a script operating over ODBC, but replicated by
 hand through the query browser.

 I couldn't see anything in the ref manual stating this as standard
 behaviour -- but I easily could have missed something there.  Can
 someone point me in the right direction?

 Thank you!
 Martin

 Using Mysql 5.1.34
 TEST CASE:

 CREATE TABLE  `test`.`test_table` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(45) NOT NULL, PRIMARY KEY (`id`),
 UNIQUE KEY `index_2` (`name`)
 )
 ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

 insert some values

 
 1, 'test'
 2, 'test2'
 3, 'test3'
 

 SHOW TABLE STATUS
 Name    test_table
 Engine  InnoDB
 Version 10
 Row_format      Compact
 Rows    3
 Avg_row_length  5461
 Data_length     16384
 Max_data_length 0
 Index_length    16384
 Data_free       0
 Auto_increment  4
 Create_time     2009-08-07 09:33:04
 Update_time
 Check_time
 Collation       latin1_swedish_ci
 Checksum
 Create_options
 Comment

 ---
 INSERT INTO test.test_table (name) VALUES ('test')

 SHOW TABLE STATUS
 Name    test_table
 ...
 Auto_increment  5

 ---
 INSERT IGNORE test.test_table (name) VALUES ('test')

 SHOW TABLE STATUS
 Name    test_table
 ...
 Auto_increment  6



 --
 ---
 This is a signature.

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=joh...@pixelated.net




 --
 -
 Johnny Withers
 601.209.4985
 joh...@pixelated.net




-- 
---
This is a signature.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Problems with auto_increment updating when (i think) it shouldn't

Re: Problems with auto_increment updating when (i think) it shouldn't

Re: Problems with auto_increment updating when (i think) it shouldn't

3 matches

Site Navigation

Mail list logo

Footer information