Re: auto_increment field behavior

2013-03-12 Thread spameden 
Furthermore I've tested on 133K records and AUTO_INCREMENT field in
the end had the value of 234076.

mysql select count(*) from billing.phone_codes;
+--+
| count(*) |
+--+
|   12 |
+--+
1 row in set (0.02 sec)

AUTO_INCREMENT=234076

So it basically means If I have large enough table I'd need to enlarge
PRIMARY KEY storage type, because it's almost double size of the
actual records.

I didn't delete records in this test too, I've inserted them all via LOAD DATA.

2013/3/13 spameden spame...@gmail.com:
 2013/3/13 Reindl Harald h.rei...@thelounge.net:


 Am 12.03.2013 22:34, schrieb spameden:
 NOTE: AUTO_INCREMENT is 32768 instead of 17923 ! So next inserted row
 would have pc_id=32768.

 Please suggest if it's normal behavior or not

 what do you expect if a PRIMARY KEY record get's removed?
 re-use the same primary key?
 this is not the way a database is allowed to work

 No, I do not want the same key, I just want the next key after I
 insert 17922 records it should be 17923 ?

 I didn't delete or modificate any records.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: auto_increment field behavior

2013-03-12 Thread spameden 
After setting innodb_autoinc_lock_mode=0 it seems to start working as
expected for me:

mysql show variables like 'innodb_autoinc_lock_mode';
+--+---+
| Variable_name| Value |
+--+---+
| innodb_autoinc_lock_mode | 0 |
+--+---+
1 row in set (0.00 sec)

mysql truncate test;
Query OK, 0 rows affected (0.01 sec)

mysql load data infile '/tmp/ABC3x' into table test fields terminated
by ';' enclosed by '#' lines terminated by '\r\n'
(@var1,@var2,@var3,@var4,@var5,@var6,@var7) SET pc_type='ABC';
Query OK, 17922 rows affected (0.21 sec)
Records: 17922  Deleted: 0  Skipped: 0  Warnings: 0

mysql show create table test;
+---+-+
| Table | Create Table

|
+---+-+
| test  | CREATE TABLE `test` (
  `pc_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `pc_type` enum('ABC','DEF') DEFAULT NULL,
  PRIMARY KEY (`pc_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17923 DEFAULT CHARSET=utf8 |
+---+-+
1 row in set (0.00 sec)

Shame it's a read-only variable and need to restart whole MySQL server.


2013/3/13 spameden spame...@gmail.com:
 Nevermind, I've found the bug:

 http://bugs.mysql.com/bug.php?id=57643

 I'm gonna subscribe for it and see if it's gonna be resolved.

 Many thanks guys for all your assistance!

 2013/3/13 spameden spame...@gmail.com:
 2013/3/13 Rick James rja...@yahoo-inc.com:
 AUTO_INCREMENT guarantees that it will not assign the same number twice.  
 That's about all it is willing to guarantee.

 With InnoDB, if a transaction starts, uses an auto_inc value, then rolls 
 back, that id is lost.

 True, but if you do not specify START TRANSACTION I believe it's done
 automatically? Nothing rolled back for me for that table and noone has
 been using it except me.


 When you have multiple threads loading data into the same table, diff 
 values of innodb_autoinc_lock_mode give you diff tradeoff between speed and 
 predictability.  If replication is involved, you want predictability.

 No, I do not have multiple threads, only 1.


 InnoDB and MyISAM act differently, especially after recovering from a crash.

 I understand the difference between InnoDB and MyISAM. InnoDB is a
 transactional DB engine with single row-level locking.


 If you DELETE the _highest_ id, then restart the server, that id will be 
 reused.  (This is irritating to some people.)  Otherwise, a deleted id will 
 not be reused.

 I didn't DELETE anything! The only actions I did:

 1. Created the TABLE
 2. used LOAD FILE only via command line (1 thread)

 So is it normal or should I fill a bug?

 There may be more.  Most of those are covered here:
 http://mysql.rjweb.org/doc.php/ricksrots




 -Original Message-
 From: spameden [mailto:spame...@gmail.com]
 Sent: Tuesday, March 12, 2013 2:46 PM
 To: Rick James
 Cc: mysql@lists.mysql.com
 Subject: Re: auto_increment field behavior

 2013/3/13 Rick James rja...@yahoo-inc.com:
  What settings?  (innodb_autoinc_lock_mode comes to mind, but there
 may
  be others.)
 Hi, Rick.

 Many thanks for the quick answer here is my settings:

 mysql show variables like '%inc%';
 +-+---+
 | Variable_name   | Value |
 +-+---+
 | auto_increment_increment| 1 |
 | auto_increment_offset   | 1 |
 | div_precision_increment | 4 |
 | innodb_autoextend_increment | 8 |
 | innodb_autoinc_lock_mode| 1 |
 +-+---+
 5 rows in set (0.00 sec)


 
  It is acceptable, by the definition of AUTO_INCREMENT, for it to burn
 the missing 15K ids.

 I don't get this explanation, could you please explain bit more? So
 it's completely normal for AUTO_INCREMENT field to act like this?


 
  -Original Message-
  From: spameden [mailto:spame...@gmail.com]
  Sent: Tuesday, March 12, 2013 2:34 PM
  To: mysql@lists.mysql.com
  Subject: auto_increment field behavior
 
  Hi, I'm running MySQL-5.5 on Ubuntu
 
  ~ $ mysqld -V
  mysqld  Ver 5.5.29-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64
  ((Ubuntu))
 
  Would like to know if it's normal behavior with auto_increment field
  (tried both signed and unsigned now):
 
  mysql show create table phone_codes;
  +-+-
 -

Re: auto_increment field behavior

2013-03-12 Thread spameden 
Nevermind, I've found the bug:

http://bugs.mysql.com/bug.php?id=57643

I'm gonna subscribe for it and see if it's gonna be resolved.

Many thanks guys for all your assistance!

2013/3/13 spameden spame...@gmail.com:
 2013/3/13 Rick James rja...@yahoo-inc.com:
 AUTO_INCREMENT guarantees that it will not assign the same number twice.  
 That's about all it is willing to guarantee.

 With InnoDB, if a transaction starts, uses an auto_inc value, then rolls 
 back, that id is lost.

 True, but if you do not specify START TRANSACTION I believe it's done
 automatically? Nothing rolled back for me for that table and noone has
 been using it except me.


 When you have multiple threads loading data into the same table, diff values 
 of innodb_autoinc_lock_mode give you diff tradeoff between speed and 
 predictability.  If replication is involved, you want predictability.

 No, I do not have multiple threads, only 1.


 InnoDB and MyISAM act differently, especially after recovering from a crash.

 I understand the difference between InnoDB and MyISAM. InnoDB is a
 transactional DB engine with single row-level locking.


 If you DELETE the _highest_ id, then restart the server, that id will be 
 reused.  (This is irritating to some people.)  Otherwise, a deleted id will 
 not be reused.

 I didn't DELETE anything! The only actions I did:

 1. Created the TABLE
 2. used LOAD FILE only via command line (1 thread)

 So is it normal or should I fill a bug?

 There may be more.  Most of those are covered here:
 http://mysql.rjweb.org/doc.php/ricksrots




 -Original Message-
 From: spameden [mailto:spame...@gmail.com]
 Sent: Tuesday, March 12, 2013 2:46 PM
 To: Rick James
 Cc: mysql@lists.mysql.com
 Subject: Re: auto_increment field behavior

 2013/3/13 Rick James rja...@yahoo-inc.com:
  What settings?  (innodb_autoinc_lock_mode comes to mind, but there
 may
  be others.)
 Hi, Rick.

 Many thanks for the quick answer here is my settings:

 mysql show variables like '%inc%';
 +-+---+
 | Variable_name   | Value |
 +-+---+
 | auto_increment_increment| 1 |
 | auto_increment_offset   | 1 |
 | div_precision_increment | 4 |
 | innodb_autoextend_increment | 8 |
 | innodb_autoinc_lock_mode| 1 |
 +-+---+
 5 rows in set (0.00 sec)


 
  It is acceptable, by the definition of AUTO_INCREMENT, for it to burn
 the missing 15K ids.

 I don't get this explanation, could you please explain bit more? So
 it's completely normal for AUTO_INCREMENT field to act like this?


 
  -Original Message-
  From: spameden [mailto:spame...@gmail.com]
  Sent: Tuesday, March 12, 2013 2:34 PM
  To: mysql@lists.mysql.com
  Subject: auto_increment field behavior
 
  Hi, I'm running MySQL-5.5 on Ubuntu
 
  ~ $ mysqld -V
  mysqld  Ver 5.5.29-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64
  ((Ubuntu))
 
  Would like to know if it's normal behavior with auto_increment field
  (tried both signed and unsigned now):
 
  mysql show create table phone_codes;
  +-+-
 -
  +-+--
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  --+
  | Table   | Create Table
 
 
 
 
 
 
 
 
|
  +-+-
 -
  +-+--
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  --+
  | phone_codes | CREATE TABLE `phone_codes` (
`pc_id` int(11) NOT NULL AUTO_INCREMENT,
`pc_type` enum('ABC','DEF') DEFAULT NULL,
`pc_code` decimal(3,0) NOT NULL,
`pc_from` decimal(7,0) NOT NULL,
`pc_to` decimal(7,0) NOT NULL,
`pc_capacity` decimal(8,0) NOT NULL,
`pc_operator` varchar(255) DEFAULT NULL,
`pc_city` varchar(255)

Re: auto_increment field behavior

2013-03-12 Thread spameden 
2013/3/13 Rick James rja...@yahoo-inc.com:
 AUTO_INCREMENT guarantees that it will not assign the same number twice.  
 That's about all it is willing to guarantee.

 With InnoDB, if a transaction starts, uses an auto_inc value, then rolls 
 back, that id is lost.

True, but if you do not specify START TRANSACTION I believe it's done
automatically? Nothing rolled back for me for that table and noone has
been using it except me.


 When you have multiple threads loading data into the same table, diff values 
 of innodb_autoinc_lock_mode give you diff tradeoff between speed and 
 predictability.  If replication is involved, you want predictability.

No, I do not have multiple threads, only 1.


 InnoDB and MyISAM act differently, especially after recovering from a crash.

I understand the difference between InnoDB and MyISAM. InnoDB is a
transactional DB engine with single row-level locking.


 If you DELETE the _highest_ id, then restart the server, that id will be 
 reused.  (This is irritating to some people.)  Otherwise, a deleted id will 
 not be reused.

I didn't DELETE anything! The only actions I did:

1. Created the TABLE
2. used LOAD FILE only via command line (1 thread)

So is it normal or should I fill a bug?

 There may be more.  Most of those are covered here:
 http://mysql.rjweb.org/doc.php/ricksrots




 -Original Message-
 From: spameden [mailto:spame...@gmail.com]
 Sent: Tuesday, March 12, 2013 2:46 PM
 To: Rick James
 Cc: mysql@lists.mysql.com
 Subject: Re: auto_increment field behavior

 2013/3/13 Rick James rja...@yahoo-inc.com:
  What settings?  (innodb_autoinc_lock_mode comes to mind, but there
 may
  be others.)
 Hi, Rick.

 Many thanks for the quick answer here is my settings:

 mysql show variables like '%inc%';
 +-+---+
 | Variable_name   | Value |
 +-+---+
 | auto_increment_increment| 1 |
 | auto_increment_offset   | 1 |
 | div_precision_increment | 4 |
 | innodb_autoextend_increment | 8 |
 | innodb_autoinc_lock_mode| 1 |
 +-+---+
 5 rows in set (0.00 sec)


 
  It is acceptable, by the definition of AUTO_INCREMENT, for it to burn
 the missing 15K ids.

 I don't get this explanation, could you please explain bit more? So
 it's completely normal for AUTO_INCREMENT field to act like this?


 
  -Original Message-
  From: spameden [mailto:spame...@gmail.com]
  Sent: Tuesday, March 12, 2013 2:34 PM
  To: mysql@lists.mysql.com
  Subject: auto_increment field behavior
 
  Hi, I'm running MySQL-5.5 on Ubuntu
 
  ~ $ mysqld -V
  mysqld  Ver 5.5.29-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64
  ((Ubuntu))
 
  Would like to know if it's normal behavior with auto_increment field
  (tried both signed and unsigned now):
 
  mysql show create table phone_codes;
  +-+-
 -
  +-+--
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  --+
  | Table   | Create Table
 
 
 
 
 
 
 
 
|
  +-+-
 -
  +-+--
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  --+
  | phone_codes | CREATE TABLE `phone_codes` (
`pc_id` int(11) NOT NULL AUTO_INCREMENT,
`pc_type` enum('ABC','DEF') DEFAULT NULL,
`pc_code` decimal(3,0) NOT NULL,
`pc_from` decimal(7,0) NOT NULL,
`pc_to` decimal(7,0) NOT NULL,
`pc_capacity` decimal(8,0) NOT NULL,
`pc_operator` varchar(255) DEFAULT NULL,
`pc_city` varchar(255) DEFAULT NULL,
`pc_region` varchar(255) DEFAULT NULL,
PRIMARY KEY (`pc_id`),
KEY `pc_code` (`pc_code`),
KEY `pc_code_from_to` (`pc_code`,`pc_from`,`pc_to`),
KEY `pc_operator` (`pc_operator`),
KEY `pc_city`

RE: auto_increment field behavior

2013-03-12 Thread Rick James 
What settings?  (innodb_autoinc_lock_mode comes to mind, but there may be 
others.)

It is acceptable, by the definition of AUTO_INCREMENT, for it to burn the 
missing 15K ids.

 -Original Message-
 From: spameden [mailto:spame...@gmail.com]
 Sent: Tuesday, March 12, 2013 2:34 PM
 To: mysql@lists.mysql.com
 Subject: auto_increment field behavior
 
 Hi, I'm running MySQL-5.5 on Ubuntu
 
 ~ $ mysqld -V
 mysqld  Ver 5.5.29-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64
 ((Ubuntu))
 
 Would like to know if it's normal behavior with auto_increment field
 (tried both signed and unsigned now):
 
 mysql show create table phone_codes;
 +-+
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 --+
 | Table   | Create Table
 
 
 
 
 
 
 
 
   |
 +-+
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 --+
 | phone_codes | CREATE TABLE `phone_codes` (
   `pc_id` int(11) NOT NULL AUTO_INCREMENT,
   `pc_type` enum('ABC','DEF') DEFAULT NULL,
   `pc_code` decimal(3,0) NOT NULL,
   `pc_from` decimal(7,0) NOT NULL,
   `pc_to` decimal(7,0) NOT NULL,
   `pc_capacity` decimal(8,0) NOT NULL,
   `pc_operator` varchar(255) DEFAULT NULL,
   `pc_city` varchar(255) DEFAULT NULL,
   `pc_region` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`pc_id`),
   KEY `pc_code` (`pc_code`),
   KEY `pc_code_from_to` (`pc_code`,`pc_from`,`pc_to`),
   KEY `pc_operator` (`pc_operator`),
   KEY `pc_city` (`pc_city`),
   KEY `pc_region` (`pc_region`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
 +-+
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 --+
 1 row in set (0.00 sec)
 
 mysql load data infile '/tmp/ABC3x' into table phone_codes fields
 terminated by ';' enclosed by '#' lines terminated by '\r\n'
 (pc_code,pc_from,pc_to,pc_capacity,pc_operator,pc_city,pc_region) SET
 pc_type='ABC'; Query OK, 17922 rows affected (4.44 sec)
 Records: 17922  Deleted: 0  Skipped: 0  Warnings: 0
 
 mysql show create table phone_codes;
 +-+
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---+
 | Table   | Create Table
 
 
 
 
 
 
 
 
|
 +-+
 ---
 ---
 ---
 ---
 ---
 ---

Re: auto_increment field behavior

2013-03-12 Thread spameden 
2013/3/13 Rick James rja...@yahoo-inc.com:
 What settings?  (innodb_autoinc_lock_mode comes to mind, but there may be 
 others.)
Hi, Rick.

Many thanks for the quick answer here is my settings:

mysql show variables like '%inc%';
+-+---+
| Variable_name   | Value |
+-+---+
| auto_increment_increment| 1 |
| auto_increment_offset   | 1 |
| div_precision_increment | 4 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode| 1 |
+-+---+
5 rows in set (0.00 sec)



 It is acceptable, by the definition of AUTO_INCREMENT, for it to burn the 
 missing 15K ids.

I don't get this explanation, could you please explain bit more? So
it's completely normal for AUTO_INCREMENT field to act like this?



 -Original Message-
 From: spameden [mailto:spame...@gmail.com]
 Sent: Tuesday, March 12, 2013 2:34 PM
 To: mysql@lists.mysql.com
 Subject: auto_increment field behavior

 Hi, I'm running MySQL-5.5 on Ubuntu

 ~ $ mysqld -V
 mysqld  Ver 5.5.29-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64
 ((Ubuntu))

 Would like to know if it's normal behavior with auto_increment field
 (tried both signed and unsigned now):

 mysql show create table phone_codes;
 +-+
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 --+
 | Table   | Create Table








   |
 +-+
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 --+
 | phone_codes | CREATE TABLE `phone_codes` (
   `pc_id` int(11) NOT NULL AUTO_INCREMENT,
   `pc_type` enum('ABC','DEF') DEFAULT NULL,
   `pc_code` decimal(3,0) NOT NULL,
   `pc_from` decimal(7,0) NOT NULL,
   `pc_to` decimal(7,0) NOT NULL,
   `pc_capacity` decimal(8,0) NOT NULL,
   `pc_operator` varchar(255) DEFAULT NULL,
   `pc_city` varchar(255) DEFAULT NULL,
   `pc_region` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`pc_id`),
   KEY `pc_code` (`pc_code`),
   KEY `pc_code_from_to` (`pc_code`,`pc_from`,`pc_to`),
   KEY `pc_operator` (`pc_operator`),
   KEY `pc_city` (`pc_city`),
   KEY `pc_region` (`pc_region`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
 +-+
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 --+
 1 row in set (0.00 sec)

 mysql load data infile '/tmp/ABC3x' into table phone_codes fields
 terminated by ';' enclosed by '#' lines terminated by '\r\n'
 (pc_code,pc_from,pc_to,pc_capacity,pc_operator,pc_city,pc_region) SET
 pc_type='ABC'; Query OK, 17922 rows affected (4.44 sec)
 Records: 17922  Deleted: 0  Skipped: 0  Warnings: 0

 mysql show create table phone_codes;
 +-+
 ---
 ---
 ---
 ---
 ---
 ---
 ---

RE: auto_increment field behavior

2013-03-12 Thread Rick James 
AUTO_INCREMENT guarantees that it will not assign the same number twice.  
That's about all it is willing to guarantee.

With InnoDB, if a transaction starts, uses an auto_inc value, then rolls back, 
that id is lost.

When you have multiple threads loading data into the same table, diff values of 
innodb_autoinc_lock_mode give you diff tradeoff between speed and 
predictability.  If replication is involved, you want predictability.

InnoDB and MyISAM act differently, especially after recovering from a crash.

If you DELETE the _highest_ id, then restart the server, that id will be 
reused.  (This is irritating to some people.)  Otherwise, a deleted id will not 
be reused.

There may be more.  Most of those are covered here:
http://mysql.rjweb.org/doc.php/ricksrots




 -Original Message-
 From: spameden [mailto:spame...@gmail.com]
 Sent: Tuesday, March 12, 2013 2:46 PM
 To: Rick James
 Cc: mysql@lists.mysql.com
 Subject: Re: auto_increment field behavior
 
 2013/3/13 Rick James rja...@yahoo-inc.com:
  What settings?  (innodb_autoinc_lock_mode comes to mind, but there
 may
  be others.)
 Hi, Rick.
 
 Many thanks for the quick answer here is my settings:
 
 mysql show variables like '%inc%';
 +-+---+
 | Variable_name   | Value |
 +-+---+
 | auto_increment_increment| 1 |
 | auto_increment_offset   | 1 |
 | div_precision_increment | 4 |
 | innodb_autoextend_increment | 8 |
 | innodb_autoinc_lock_mode| 1 |
 +-+---+
 5 rows in set (0.00 sec)
 
 
 
  It is acceptable, by the definition of AUTO_INCREMENT, for it to burn
 the missing 15K ids.
 
 I don't get this explanation, could you please explain bit more? So
 it's completely normal for AUTO_INCREMENT field to act like this?
 
 
 
  -Original Message-
  From: spameden [mailto:spame...@gmail.com]
  Sent: Tuesday, March 12, 2013 2:34 PM
  To: mysql@lists.mysql.com
  Subject: auto_increment field behavior
 
  Hi, I'm running MySQL-5.5 on Ubuntu
 
  ~ $ mysqld -V
  mysqld  Ver 5.5.29-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64
  ((Ubuntu))
 
  Would like to know if it's normal behavior with auto_increment field
  (tried both signed and unsigned now):
 
  mysql show create table phone_codes;
  +-+-
 -
  +-+--
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  --+
  | Table   | Create Table
 
 
 
 
 
 
 
 
|
  +-+-
 -
  +-+--
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --
  --+
  | phone_codes | CREATE TABLE `phone_codes` (
`pc_id` int(11) NOT NULL AUTO_INCREMENT,
`pc_type` enum('ABC','DEF') DEFAULT NULL,
`pc_code` decimal(3,0) NOT NULL,
`pc_from` decimal(7,0) NOT NULL,
`pc_to` decimal(7,0) NOT NULL,
`pc_capacity` decimal(8,0) NOT NULL,
`pc_operator` varchar(255) DEFAULT NULL,
`pc_city` varchar(255) DEFAULT NULL,
`pc_region` varchar(255) DEFAULT NULL,
PRIMARY KEY (`pc_id`),
KEY `pc_code` (`pc_code`),
KEY `pc_code_from_to` (`pc_code`,`pc_from`,`pc_to`),
KEY `pc_operator` (`pc_operator`),
KEY `pc_city` (`pc_city`),
KEY `pc_region` (`pc_region`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  +-+-
 -
  +-+--
  
 -
  --
  
 -
  --
  
 -
  --
  
 -
  --

Re: auto_increment field behavior

2013-03-12 Thread spameden 
Also, forget to quote from the docs
(http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html)

With innodb_autoinc_lock_mode set to 0 (“traditional”) or 1
(“consecutive”), the auto-increment values generated by any given
statement will be consecutive, without gaps, because the table-level
AUTO-INC lock is held until the end of the statement, and only one
such statement can execute at a time. 

So I believe this is a bug in MySQL because there were no parallel
INSERTs at all.

Sorry for the spam :)

2013/3/13 spameden spame...@gmail.com:
 After setting innodb_autoinc_lock_mode=0 it seems to start working as
 expected for me:

 mysql show variables like 'innodb_autoinc_lock_mode';
 +--+---+
 | Variable_name| Value |
 +--+---+
 | innodb_autoinc_lock_mode | 0 |
 +--+---+
 1 row in set (0.00 sec)

 mysql truncate test;
 Query OK, 0 rows affected (0.01 sec)

 mysql load data infile '/tmp/ABC3x' into table test fields terminated
 by ';' enclosed by '#' lines terminated by '\r\n'
 (@var1,@var2,@var3,@var4,@var5,@var6,@var7) SET pc_type='ABC';
 Query OK, 17922 rows affected (0.21 sec)
 Records: 17922  Deleted: 0  Skipped: 0  Warnings: 0

 mysql show create table test;
 +---+-+
 | Table | Create Table

 |
 +---+-+
 | test  | CREATE TABLE `test` (
   `pc_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
   `pc_type` enum('ABC','DEF') DEFAULT NULL,
   PRIMARY KEY (`pc_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=17923 DEFAULT CHARSET=utf8 |
 +---+-+
 1 row in set (0.00 sec)

 Shame it's a read-only variable and need to restart whole MySQL server.


 2013/3/13 spameden spame...@gmail.com:
 Nevermind, I've found the bug:

 http://bugs.mysql.com/bug.php?id=57643

 I'm gonna subscribe for it and see if it's gonna be resolved.

 Many thanks guys for all your assistance!

 2013/3/13 spameden spame...@gmail.com:
 2013/3/13 Rick James rja...@yahoo-inc.com:
 AUTO_INCREMENT guarantees that it will not assign the same number twice.  
 That's about all it is willing to guarantee.

 With InnoDB, if a transaction starts, uses an auto_inc value, then rolls 
 back, that id is lost.

 True, but if you do not specify START TRANSACTION I believe it's done
 automatically? Nothing rolled back for me for that table and noone has
 been using it except me.


 When you have multiple threads loading data into the same table, diff 
 values of innodb_autoinc_lock_mode give you diff tradeoff between speed 
 and predictability.  If replication is involved, you want predictability.

 No, I do not have multiple threads, only 1.


 InnoDB and MyISAM act differently, especially after recovering from a 
 crash.

 I understand the difference between InnoDB and MyISAM. InnoDB is a
 transactional DB engine with single row-level locking.


 If you DELETE the _highest_ id, then restart the server, that id will be 
 reused.  (This is irritating to some people.)  Otherwise, a deleted id 
 will not be reused.

 I didn't DELETE anything! The only actions I did:

 1. Created the TABLE
 2. used LOAD FILE only via command line (1 thread)

 So is it normal or should I fill a bug?

 There may be more.  Most of those are covered here:
 http://mysql.rjweb.org/doc.php/ricksrots




 -Original Message-
 From: spameden [mailto:spame...@gmail.com]
 Sent: Tuesday, March 12, 2013 2:46 PM
 To: Rick James
 Cc: mysql@lists.mysql.com
 Subject: Re: auto_increment field behavior

 2013/3/13 Rick James rja...@yahoo-inc.com:
  What settings?  (innodb_autoinc_lock_mode comes to mind, but there
 may
  be others.)
 Hi, Rick.

 Many thanks for the quick answer here is my settings:

 mysql show variables like '%inc%';
 +-+---+
 | Variable_name   | Value |
 +-+---+
 | auto_increment_increment| 1 |
 | auto_increment_offset   | 1 |
 | div_precision_increment | 4 |
 | innodb_autoextend_increment | 8 |
 | innodb_autoinc_lock_mode| 1 |
 +-+---+
 5 rows in set (0.00 sec)


 
  It is acceptable, by the definition of AUTO_INCREMENT, for it to burn
 the missing 15K ids.

 I don't get this explanation, could you please explain bit more? So
 it's completely normal for

Re: auto_increment field behavior

2013-03-12 Thread Reindl Harald 


Am 12.03.2013 22:34, schrieb spameden:
 NOTE: AUTO_INCREMENT is 32768 instead of 17923 ! So next inserted row
 would have pc_id=32768.
 
 Please suggest if it's normal behavior or not

what do you expect if a PRIMARY KEY record get's removed?
re-use the same primary key?
this is not the way a database is allowed to work



signature.asc
Description: OpenPGP digital signature