Re: auto_increment field behavior
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
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
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
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/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
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/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
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
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
Re: auto_increment field behavior
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
RE: How MyISAM handle auto_increment
-Original Message- From: Lucio Chiappetti [mailto:lu...@lambrate.inaf.it] Sent: Thursday, October 06, 2011 3:18 AM To: Jerry Schwartz Cc: Mysql List Subject: RE: How MyISAM handle auto_increment On Wed, 5 Oct 2011, Jerry Schwartz wrote: Can't you use CREATE TABLE LIKE and then reset the auto-increment value? Thanks. Since when does create table like exist? I was unaware of it, but I see it exists in mysql 5.1. The tricks I described worked since 3.x or thereabouts. [JS] I don't know when it was introduced. I never used anything before 4.0, and I don't remember when I first used it that command. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.giiresearch.com -- Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy) Citizens entrusted of public functions have the duty to accomplish them with discipline and honour [Art. 54 Constitution of the Italian Republic] For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: How MyISAM handle auto_increment
On Wed, 5 Oct 2011, Jerry Schwartz wrote: Can't you use CREATE TABLE LIKE and then reset the auto-increment value? Thanks. Since when does create table like exist? I was unaware of it, but I see it exists in mysql 5.1. The tricks I described worked since 3.x or thereabouts. -- Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy) Citizens entrusted of public functions have the duty to accomplish them with discipline and honour [Art. 54 Constitution of the Italian Republic] For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How MyISAM handle auto_increment
On Mon, 3 Oct 2011, Reindl Harald wrote: I have questions regarding how MyISAM handles auto_increment clolumn? it is a table-property and you hould NOT touch it without godd reasons because it is named AUTO I guess there are quite often good reasons to change it, which can be done e.g. as alter table north33b auto_increment=21; I often generate an empty table with the same structure as another table with a statement like : create table select * from limit 0; (at this point I have to recreate also all indices etc. doing a show create table and show create table and alter table for anything which is missing) The new table will have auto increment starting where ended. This is good (assuming will not grow any more) to preserve an UNIQUE sequence for all tables different for each table. In case one wants to start at a round number one can just issue an alter table and reset auto_increment. The same if one tests some procedure to populate a new table , then deletes everything, and wants that production population restarts from 1. -- Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy) Citizens entrusted of public functions have the duty to accomplish them with discipline and honour [Art. 54 Constitution of the Italian Republic] For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How MyISAM handle auto_increment
Hi, Folks: I have questions regarding how MyISAM handles auto_increment clolumn? 1. is there a auto_increment counter for MyISAM to assign a new value to auto_increment columns? 2. if MyISAM has the counter, is the counter stored in memory or disk? Thnaks
Re: How MyISAM handle auto_increment
At 04:46 PM 10/3/2011, you wrote: Hi, Folks: I have questions regarding how MyISAM handles auto_increment clolumn? 1. is there a auto_increment counter for MyISAM to assign a new value to auto_increment columns? Yes 2. if MyISAM has the counter, is the counter stored in memory or disk? Thnaks It is stored with the table definition. It is only reset to 0 when the table is (re)created. You can get the last AutoInc for the record that was just added by Select Last_Insert_Id(). See http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How MyISAM handle auto_increment
Thanks, what about if mysqld restart, does auto_increment gets reset ? I saw this happened to Innodb, if table is empty and server restart, auto_incremnet gets reset to 0 From: mos mo...@fastmail.fm To: mysql@lists.mysql.com Sent: Monday, October 3, 2011 3:01 PM Subject: Re: How MyISAM handle auto_increment At 04:46 PM 10/3/2011, you wrote: Hi, Folks: I have questions regarding how MyISAM handles auto_increment clolumn? 1. is there a auto_increment counter for MyISAM to assign a new value to auto_increment columns? Yes 2. if MyISAM has the counter, is the counter stored in memory or disk? Thnaks It is stored with the table definition. It is only reset to 0 when the table is (re)created. You can get the last AutoInc for the record that was just added by Select Last_Insert_Id(). See http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yyll2...@yahoo.com
Re: How MyISAM handle auto_increment
Am 03.10.2011 23:46, schrieb Angela liu: Hi, Folks: I have questions regarding how MyISAM handles auto_increment clolumn? 1. is there a auto_increment counter for MyISAM to assign a new value to auto_increment columns? 2. if MyISAM has the counter, is the counter stored in memory or disk? it is a table-property and you hould NOT touch it without godd reasons because it is named AUTO signature.asc Description: OpenPGP digital signature
Re: How MyISAM handle auto_increment
At 06:21 PM 10/3/2011, Angela liu wrote: Thanks, what about if mysqld restart, does auto_increment gets reset ? No. The next auto increment value stays with the table. As another person already stated, you should never manually change the auto increment value on a table that already has rows in it. MySQL always handles the incrementing of an AutoInc field. I saw this happened to Innodb, if table is empty and server restart, auto_incremnet gets reset to 0 This shouldn't happen unless MySQL crashes. Perhaps you are confusing the next Auto Increment value with the Last_Insert_Id() (stored in server memory) which has a value only AFTER the user has inserted a row. There can be a hundred connections (each with their own session) adding rows to the same table at the same time. Last_Insert_Id() will retrieve the autoinc value of the record that was just inserted for YOUR session. You won't get someone else's AutoInc value, only the autoinc value for the record that YOU just inserted. So if you insert a record, wait 5 minutes, then execute a Select Last_Insert_Id(), you will get the correct autoinc value that was used when YOUR record was inserted, even though another hundred records were added while you waited to execute the Select statement. You can never really reliably know what the autoinc value will be for the record you are about to insert until AFTER the record has been inserted and you execute Last_Insert_Id() to retrieve this autoinc value. I hope this clears it up. Mike From: mos mo...@fastmail.fm To: mysql@lists.mysql.com Sent: Monday, October 3, 2011 3:01 PM Subject: Re: How MyISAM handle auto_increment At 04:46 PM 10/3/2011, you wrote: Hi, Folks: I have questions regarding how MyISAM handles auto_increment clolumn? 1. is there a auto_increment counter for MyISAM to assign a new value to auto_increment columns? Yes 2. if MyISAM has the counter, is the counter stored in memory or disk? Thnaks It is stored with the table definition. It is only reset to 0 when the table is (re)created. You can get the last AutoInc for the record that was just added by Select Last_Insert_Id(). See http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=yyll2...@yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment value increased from 20 to 32 when I inserted a new row.
- Original Message - From: crocket crockabisc...@gmail.com I had 19 rows in series table. And when I tried inserting the 20th row, the auto_increment value suddenly increased from 20 to 32, and the new row has 20 as series_id. The first thing that comes to mind, is transactions that insert, but then rollback - the autoincrement counter gets increased immediately so that other transactions don't risk doubles, but it never gets decreased because that would be relatively complicated and the overhead is mostly useless anyway. Don't assume autoincrements never have gaps; don't even assume they're always assigned in sequence - especially if you're using transactions. Optimize table will also reset the autoincrement counter and reuse the gaps, iirc. Autoincrements are really just a convenience for automatic primary key generation, nothing more is implied or guaranteed. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
auto_increment value increased from 20 to 32 when I inserted a new row.
Below is the definition of the table with the problem. CREATE TABLE `series` ( `series_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) NOT NULL, PRIMARY KEY (`series_id`) ) ENGINE=InnoDB I had 19 rows in series table. And when I tried inserting the 20th row, the auto_increment value suddenly increased from 20 to 32, and the new row has 20 as series_id. This leap seems to happen on every table. What is the cause of the leap in auto_increment? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
auto_increment by more than 1
Is there a way to set the auto-increment for a particular table to increase by some number more than one, like maybe 10? Thanks in advance, Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment by more than 1
Den 23-02-2011 18:41, Jim McNeely skrev: Is there a way to set the auto-increment for a particular table to increase by some number more than one, like maybe 10? Thanks in advance, Jim McNeely CREATE TABLE t ( ... ) AUTO_INCREMENT=10; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment by more than 1
On 2/23/2011 12:41, Jim McNeely wrote: Is there a way to set the auto-increment for a particular table to increase by some number more than one, like maybe 10? Thanks in advance, Jim McNeely The manual is your friend. Don't be afraid of it :) http://dev.mysql.com/doc/refman/5.5/en/replication-options-master.html#sysvar_auto_increment_increment -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment by more than 1
I have read the manual, and you're right, the auto-increment_increment is a system wide setting. I only want this on one table. I am in this instance creating ID's for a separate system via HL7 for a Filemaker system, and FileMaker is too lame and slow to actually spit out an ID in time for the foreign system to function correctly within its workflow requirements for the end users. So, I am going to offset the ID's so that MySQL issues ID's on the 10's, and FM issues ID's on the 5's. That way, it works similar to the way some people set up replication, but I only need it on this one table, I want the other tables to continue to increment normally. I don't want to do this in another instance of MySQL or another DB because I am otherwise trying to keep it simple. Here is the solution I came up with: CREATE DEFINER=`user`@`%` TRIGGER ``.`p_number_zzk` BEFORE INSERT ON ``.`p_number` FOR EACH ROW BEGIN DECLARE maxy INT; SET maxy = (SELECT ROUND(MAX(zzk),-1) from p_number); IF ! NEW.zzk THEN SET NEW.zzk = (maxy + 10); END IF; SET NEW.IdPatient = CONCAT(P, NEW.zzk); END It's probably ugly, but it works. Any objections to this? The zzk and IdPatient fields have unique validations on them. Thanks, Jim McNeely On Feb 23, 2011, at 12:48 PM, Singer X.J. Wang wrote: Its theoretically possible, but its a hackish solution.. can you explain why you want this? On Wed, Feb 23, 2011 at 15:46, Singer X.J. Wang w...@singerwang.com wrote: Right.. and that's not his question.. On Wed, Feb 23, 2011 at 15:34, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 2/23/2011 12:41, Jim McNeely wrote: Is there a way to set the auto-increment for a particular table to increase by some number more than one, like maybe 10? Thanks in advance, Jim McNeely The manual is your friend. Don't be afraid of it :) http://dev.mysql.com/doc/refman/5.5/en/replication-options-master.html#sysvar_auto_increment_increment -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=w...@singerwang.com -- The best compliment you could give Pythian for our service is a referral.
Re: auto_increment by more than 1
This doesn't work, it just sets the starting number, but it will still increment by one unless you set the auto_increment_increment system variable, but this affects all the tables in the DB and not just the particular table. Thanks, Jim McNeely On Feb 23, 2011, at 10:26 AM, Carsten Pedersen wrote: Den 23-02-2011 18:41, Jim McNeely skrev: Is there a way to set the auto-increment for a particular table to increase by some number more than one, like maybe 10? Thanks in advance, Jim McNeely CREATE TABLE t ( ... ) AUTO_INCREMENT=10; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=j...@newcenturydata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment by more than 1
Am 23.02.2011 22:29, schrieb Jim McNeely: I have read the manual, and you're right, the auto-increment_increment is a system wide setting No, scope session means set VAR=value Command-Line Format --auto_increment_increment[=#] Option-File Format auto_increment_increment Option Sets VariableYes, auto_increment_increment Variable Name auto_increment_increment Variable Scope Global, Session signature.asc Description: OpenPGP digital signature
Re: auto_increment by more than 1
Am 23.02.2011 22:55, schrieb Singer X.J. Wang: Yes, you can set it up so that it increases it by X only for that statement.. eg. [other stuff] set auto_increment_increment = X; insert into that table you want set auto_increment_increment = 1; [other stuff] Now you have to remmeber doing that everywhere.. and everytime.. this was not the question, but hopefully your app does not inline-query in every second line, if so you should think about the apllication design it is generally senseless to change this value if you are not using master/master-replications where you do this globally On Wed, Feb 23, 2011 at 16:42, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: Am 23.02.2011 22:29, schrieb Jim McNeely: I have read the manual, and you're right, the auto-increment_increment is a system wide setting No, scope session means set VAR=value Command-Line Format --auto_increment_increment[=#] Option-File Format auto_increment_increment Option Sets VariableYes, auto_increment_increment Variable Name auto_increment_increment Variable Scope Global, Session -- The best compliment you could give Pythian for our service is a referral. -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ signature.asc Description: OpenPGP digital signature
Re: Discontinued AUTO_INCREMENT problem....
Hi. You can show us your show create table statement as well. 杨涛 我博客1:http://yueliangdao0608.cublog.cn My 我博客2:http://yueliangdao0608.blog.51cto.com 2010/12/20 Xavier Correyeur x.correy...@free.fr Hi everybody ! A have a discontinued AUTO_INCREMENT sequence when i insert data in a table with a 100 (or more) items SELECT request. The problem (or situation) is reproductible, you can see an example below. Anybody could explain this to me ? Cheers XC My MySQL version : Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 == Example = -- CREATE test table mysql create table test(`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` VARCHAR(255), `test` int(10), KEY `keyid`(`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) -- INSERT DATA FROM ANOTHER TABLE mysql insert into test(name) select `name`from user limit 100; Query OK, 100 rows affected (0.01 sec) Records: 100 Duplicates: 0 Warnings: 0 -- AUTO_INCREMENT ID CHECK = OK mysql select max(`id`) from test; +---+ | max(`id`) | +---+ | 100 | +---+ 1 row in set (0.00 sec) --INSERT DATA WITH CHECKED SELECTREQUEST 2 = DATA INSERT OK mysql insert into test(name) select `name` from userlimit 100; Query OK, 100 rows affected (0.01 sec) Records: 100 Duplicates: 0 Warnings: 0 -- AUTO_INCREMENT ID CHECK = should be 100 + 100 = 200 -- = 27 IDs are unset, first ID of 2nd insert is 128 instead of 101 -- No field between 100 and 128 mysql select max(`id`) from test; +---+ | max(`id`) | +---+ | 227 | +---+ 1 row in set (0.00 sec) == End Example = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
Re: Discontinued AUTO_INCREMENT problem....
Too curious...could you share a SHOW CREATE TABLE from this table as requested before? Best regards. -- Wagner Bianchi 2010/12/21 杨涛涛 david.y...@actionsky.com Hi. You can show us your show create table statement as well. 杨涛 我博客1:http://yueliangdao0608.cublog.cn My 我博客2:http://yueliangdao0608.blog.51cto.com 2010/12/20 Xavier Correyeur x.correy...@free.fr Hi everybody ! A have a discontinued AUTO_INCREMENT sequence when i insert data in a table with a 100 (or more) items SELECT request. The problem (or situation) is reproductible, you can see an example below. Anybody could explain this to me ? Cheers XC My MySQL version : Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 == Example = -- CREATE test table mysql create table test(`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` VARCHAR(255), `test` int(10), KEY `keyid`(`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) -- INSERT DATA FROM ANOTHER TABLE mysql insert into test(name) select `name`from user limit 100; Query OK, 100 rows affected (0.01 sec) Records: 100 Duplicates: 0 Warnings: 0 -- AUTO_INCREMENT ID CHECK = OK mysql select max(`id`) from test; +---+ | max(`id`) | +---+ | 100 | +---+ 1 row in set (0.00 sec) --INSERT DATA WITH CHECKED SELECTREQUEST 2 = DATA INSERT OK mysql insert into test(name) select `name` from userlimit 100; Query OK, 100 rows affected (0.01 sec) Records: 100 Duplicates: 0 Warnings: 0 -- AUTO_INCREMENT ID CHECK = should be 100 + 100 = 200 -- = 27 IDs are unset, first ID of 2nd insert is 128 instead of 101 -- No field between 100 and 128 mysql select max(`id`) from test; +---+ | max(`id`) | +---+ | 227 | +---+ 1 row in set (0.00 sec) == End Example = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
Re: Discontinued AUTO_INCREMENT problem....
Hi, There is one variable called innodb_autoinc_lock_mode. If the value is 0, this issue wont come. You might set it to 1 or 2. -Partha www.mafiree.com - Original Message From: Wagner Bianchi wagnerbianch...@gmail.com To: 杨涛涛 david.y...@actionsky.com Cc: Xavier Correyeur x.correy...@free.fr; mysql@lists.mysql.com Sent: Tue, 21 December, 2010 3:28:00 PM Subject: Re: Discontinued AUTO_INCREMENT problem Too curious...could you share a SHOW CREATE TABLE from this table as requested before? Best regards. -- Wagner Bianchi 2010/12/21 杨涛涛 david.y...@actionsky.com Hi. You can show us your show create table statement as well. 杨涛 我博客1:http://yueliangdao0608.cublog.cn My 我博客2:http://yueliangdao0608.blog.51cto.com 2010/12/20 Xavier Correyeur x.correy...@free.fr Hi everybody ! A have a discontinued AUTO_INCREMENT sequence when i insert data in a table with a 100 (or more) items SELECT request. The problem (or situation) is reproductible, you can see an example below. Anybody could explain this to me ? Cheers XC My MySQL version : Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 == Example = -- CREATE test table mysql create table test(`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` VARCHAR(255), `test` int(10), KEY `keyid`(`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) -- INSERT DATA FROM ANOTHER TABLE mysql insert into test(name) select `name`from user limit 100; Query OK, 100 rows affected (0.01 sec) Records: 100 Duplicates: 0 Warnings: 0 -- AUTO_INCREMENT ID CHECK = OK mysql select max(`id`) from test; +---+ | max(`id`) | +---+ | 100 | +---+ 1 row in set (0.00 sec) --INSERT DATA WITH CHECKED SELECTREQUEST 2 = DATA INSERT OK mysql insert into test(name) select `name` from userlimit 100; Query OK, 100 rows affected (0.01 sec) Records: 100 Duplicates: 0 Warnings: 0 -- AUTO_INCREMENT ID CHECK = should be 100 + 100 = 200 -- = 27 IDs are unset, first ID of 2nd insert is 128 instead of 101 -- No field between 100 and 128 mysql select max(`id`) from test; +---+ | max(`id`) | +---+ | 227 | +---+ 1 row in set (0.00 sec) == End Example = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Discontinued AUTO_INCREMENT problem....
Hi. This is a good point for this issue. David Yeung, In China, Beijing. My First Blog:http://yueliangdao0608.cublog.cn My Second Blog:http://yueliangdao0608.blog.51cto.com My Msn: yueliangdao0...@gmail.com 2010/12/21 partha sarathy par...@mafiree.com Hi, There is one variable called innodb_autoinc_lock_mode. If the value is 0, this issue wont come. You might set it to 1 or 2. -Partha www.mafiree.com - Original Message From: Wagner Bianchi wagnerbianch...@gmail.com To: 杨涛涛 david.y...@actionsky.com Cc: Xavier Correyeur x.correy...@free.fr; mysql@lists.mysql.com Sent: Tue, 21 December, 2010 3:28:00 PM Subject: Re: Discontinued AUTO_INCREMENT problem Too curious...could you share a SHOW CREATE TABLE from this table as requested before? Best regards. -- Wagner Bianchi 2010/12/21 杨涛涛 david.y...@actionsky.com Hi. You can show us your show create table statement as well. 杨涛 我博客1:http://yueliangdao0608.cublog.cn My 我博客2:http://yueliangdao0608.blog.51cto.com 2010/12/20 Xavier Correyeur x.correy...@free.fr Hi everybody ! A have a discontinued AUTO_INCREMENT sequence when i insert data in a table with a 100 (or more) items SELECT request. The problem (or situation) is reproductible, you can see an example below. Anybody could explain this to me ? Cheers XC My MySQL version : Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 == Example = -- CREATE test table mysql create table test(`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` VARCHAR(255), `test` int(10), KEY `keyid`(`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) -- INSERT DATA FROM ANOTHER TABLE mysql insert into test(name) select `name`from user limit 100; Query OK, 100 rows affected (0.01 sec) Records: 100 Duplicates: 0 Warnings: 0 -- AUTO_INCREMENT ID CHECK = OK mysql select max(`id`) from test; +---+ | max(`id`) | +---+ | 100 | +---+ 1 row in set (0.00 sec) --INSERT DATA WITH CHECKED SELECTREQUEST 2 = DATA INSERT OK mysql insert into test(name) select `name` from userlimit 100; Query OK, 100 rows affected (0.01 sec) Records: 100 Duplicates: 0 Warnings: 0 -- AUTO_INCREMENT ID CHECK = should be 100 + 100 = 200 -- = 27 IDs are unset, first ID of 2nd insert is 128 instead of 101 -- No field between 100 and 128 mysql select max(`id`) from test; +---+ | max(`id`) | +---+ | 227 | +---+ 1 row in set (0.00 sec) == End Example = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
Discontinued AUTO_INCREMENT problem....
Hi everybody ! A have a discontinued AUTO_INCREMENT sequence when i insert data in a table with a 100 (or more) items SELECT request. The problem (or situation) is reproductible, you can see an example below. Anybody could explain this to me ? Cheers XC My MySQL version : Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 == Example = -- CREATE test table mysql create table test(`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` VARCHAR(255), `test` int(10), KEY `keyid`(`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) -- INSERT DATA FROM ANOTHER TABLE mysql insert into test(name) select `name`from user limit 100; Query OK, 100 rows affected (0.01 sec) Records: 100 Duplicates: 0 Warnings: 0 -- AUTO_INCREMENT ID CHECK = OK mysql select max(`id`) from test; +---+ | max(`id`) | +---+ | 100 | +---+ 1 row in set (0.00 sec) --INSERT DATA WITH CHECKED SELECTREQUEST 2 = DATA INSERT OK mysql insert into test(name) select `name` from userlimit 100; Query OK, 100 rows affected (0.01 sec) Records: 100 Duplicates: 0 Warnings: 0 -- AUTO_INCREMENT ID CHECK = should be 100 + 100 = 200 -- = 27 IDs are unset, first ID of 2nd insert is 128 instead of 101 -- No field between 100 and 128 mysql select max(`id`) from test; +---+ | max(`id`) | +---+ | 227 | +---+ 1 row in set (0.00 sec) == End Example = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: about auto_increment id
ALTER TABLE sometable AUTO_INCREMENT = 1000; On 10/17/2010 07:03 AM, short cutter wrote: Hi, Is it possible to change this directive's value without modifition to my.cnf and restart mysqld? I remember there is a set @@variable syntax, but not sure. Thanks. 2010/10/17 mosmo...@fastmail.fm: At 08:55 PM 10/16/2010, you wrote: After executing the SQL statement, the next Id inserted will be 1000. Oops. I meant : After executing the SQL statement, the next Id inserted will be 1001. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
about auto_increment id
Hello, I have a table which has the ID key with auto_increment and which is a primary key. If I insert the table with the id value which is generated by the program, for example, insert table (id, user_name, age) values (1000, 'kenn', 30); the value 1000 is inserted forcely, not generated by database automatically. Will this cause problem? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: about auto_increment id
At 08:05 PM 10/16/2010, you wrote: Hello, I have a table which has the ID key with auto_increment and which is a primary key. If I insert the table with the id value which is generated by the program, for example, insert table (id, user_name, age) values (1000, 'kenn', 30); the value 1000 is inserted forcely, not generated by database automatically. Will this cause problem? Thanks. It is not going to cause a problem but it defeats the purpose of having an auto-inc column. After executing the SQL statement, the next Id inserted will be 1000. It is much better to let the table determine the next auto-inc value by using: insert table (id, user_name, age) values (NULL, 'kenn', 30); If you want to get the value of the Id that was used, simply reference the variable Last_Insert_Id as in: select Last_Insert_Id; See: http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: about auto_increment id
At 08:55 PM 10/16/2010, you wrote: After executing the SQL statement, the next Id inserted will be 1000. Oops. I meant : After executing the SQL statement, the next Id inserted will be 1001. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: about auto_increment id
Hi, Is it possible to change this directive's value without modifition to my.cnf and restart mysqld? I remember there is a set @@variable syntax, but not sure. Thanks. 2010/10/17 mos mo...@fastmail.fm: At 08:55 PM 10/16/2010, you wrote: After executing the SQL statement, the next Id inserted will be 1000. Oops. I meant : After executing the SQL statement, the next Id inserted will be 1001. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
setting auto_increment value with a local variable
Hi, Is there any way to set the auto_increment value with the variable like below. mysql set @id=10; mysql alter table suresh_copy auto_increme...@id; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@id' at line 1 It is working and below but need to work ab above. mysql alter table suresh_copy auto_increment=1000; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 From the MySQL documentation : -- To change the value of the AUTO_INCREMENT counter to be used for new rows, do this: ALTER TABLE t2 AUTO_INCREMENT = value; You cannot reset the counter to a value less than or equal to any that have already been used. Thanks Suresh Kuna
Pb: auto_increment - insert zero value
Hi, I have problem in the insertion of 0 in auto_increment. I have set in my.ini file as follows, sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO Even now the auto increment filed is not allows to insert a zero; If i insert zero, 1 is getting inserted. I have attached my.ini file for your reference. Please help me. Thank you VIKRAM Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment weirdness
what is the value u see when you execute select max(b) from y; On Thu, Feb 18, 2010 at 1:33 PM, Gavin Towey gto...@ffn.com wrote: Reproduced in 5.1.43. Could not reproduce it in 5.0.66 -Original Message- From: Yang Zhang [mailto:yanghates...@gmail.com] Sent: Wednesday, February 17, 2010 6:05 PM To: mysql@lists.mysql.com Subject: auto_increment weirdness Hi, for some reason, I have an auto_increment field that's magically bumped up to the next biggest power of 2 after a big INSERT...SELECT that inserts a bunch of tuples (into an empty table). Is this expected behavior? I couldn't find any mention of this from the docs (using the MySQL 5.4.3 beta). Small reproducible test case: First, generate some data: from bash, run seq 3 /tmp/seq Next, run this in mysql: create table x (a int auto_increment primary key, b int); create table y (b int); load data infile '/tmp/seq' into table y; insert into x (b) select b from y; show create table x; This will show auto_increment = 32768 instead of 3. Is this a bug introduced in the beta? Has it been fixed in newer releases? Couldn't find a mention in the bug database. Thanks in advance. -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
RE: auto_increment weirdness
Reproduced in 5.1.43. Could not reproduce it in 5.0.66 -Original Message- From: Yang Zhang [mailto:yanghates...@gmail.com] Sent: Wednesday, February 17, 2010 6:05 PM To: mysql@lists.mysql.com Subject: auto_increment weirdness Hi, for some reason, I have an auto_increment field that's magically bumped up to the next biggest power of 2 after a big INSERT...SELECT that inserts a bunch of tuples (into an empty table). Is this expected behavior? I couldn't find any mention of this from the docs (using the MySQL 5.4.3 beta). Small reproducible test case: First, generate some data: from bash, run seq 3 /tmp/seq Next, run this in mysql: create table x (a int auto_increment primary key, b int); create table y (b int); load data infile '/tmp/seq' into table y; insert into x (b) select b from y; show create table x; This will show auto_increment = 32768 instead of 3. Is this a bug introduced in the beta? Has it been fixed in newer releases? Couldn't find a mention in the bug database. Thanks in advance. -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
auto_increment weirdness
Hi, for some reason, I have an auto_increment field that's magically bumped up to the next biggest power of 2 after a big INSERT...SELECT that inserts a bunch of tuples (into an empty table). Is this expected behavior? I couldn't find any mention of this from the docs (using the MySQL 5.4.3 beta). Small reproducible test case: First, generate some data: from bash, run seq 3 /tmp/seq Next, run this in mysql: create table x (a int auto_increment primary key, b int); create table y (b int); load data infile '/tmp/seq' into table y; insert into x (b) select b from y; show create table x; This will show auto_increment = 32768 instead of 3. Is this a bug introduced in the beta? Has it been fixed in newer releases? Couldn't find a mention in the bug database. Thanks in advance. -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment without primary key in innodb?
On Mon, Jan 25, 2010 at 10:08 PM, Yong Lee yong@gogoants.com wrote: yah, mysql only allows one auto increment field n that's used as the primary key in tables. I don't think it has to be the primary key as long as it is a unique key i think that's okay. so u should be able to do : create table (myid int unsigned not null auto_increment., unique key (myid)); but this is effectively a primary key Only mostly true :-) It *is* the same for MyISAM, but for InnoDB the primary key is special, as that is the one that stores the data inline (clustered index). Additional unique keys will only contain a reference to the primary key value for the record. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Fwd: auto_increment without primary key in innodb?
Yeah, Paul... This is so clear...the auto_increment column may be indexed like: - KEY(); - UNIQUE(); - PRIMARY KEY() ...when you create or alter a table. -- Wagner Bianchi 2010/1/25 Paul DuBois paul.dub...@sun.com The requirement is that it be indexed. The index need not be a primary key. mysql create table t (i int not null auto_increment, index(i)) engine innodb; Query OK, 0 rows affected (0.45 sec) On Jan 25, 2010, at 9:39 AM, Yang Zhang wrote: Right, I saw the docs. I'm fine with creating an index on it, but the only way I've successfully created a table with auto_increment is by making it a primary key. And I still don't understand why this requirement is there in the first place. On Mon, Jan 25, 2010 at 10:32 AM, Tom Worster f...@thefsb.org wrote: it's not an innodb thing: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Note There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0. -Original Message- From: Yang Zhang yanghates...@gmail.com Sent: Monday, January 25, 2010 10:21am To: mysql@lists.mysql.com Subject: auto_increment without primary key in innodb? In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@thefsb.org -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=paul.dub...@sun.com -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@gmail.com Lattes: http://lattes.cnpq.br/2041067758113940 Twitter: http://twitter.com/wagnerbianchi Skype: infodbacet
auto_increment without primary key in innodb?
In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: auto_increment without primary key in innodb ?
it's not an innodb thing: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Note There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0. -Original Message- From: Yang Zhang yanghates...@gmail.com Sent: Monday, January 25, 2010 10:21am To: mysql@lists.mysql.com Subject: auto_increment without primary key in innodb? In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@thefsb.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment without primary key in innodb?
Right, I saw the docs. I'm fine with creating an index on it, but the only way I've successfully created a table with auto_increment is by making it a primary key. And I still don't understand why this requirement is there in the first place. On Mon, Jan 25, 2010 at 10:32 AM, Tom Worster f...@thefsb.org wrote: it's not an innodb thing: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Note There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0. -Original Message- From: Yang Zhang yanghates...@gmail.com Sent: Monday, January 25, 2010 10:21am To: mysql@lists.mysql.com Subject: auto_increment without primary key in innodb? In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=...@thefsb.org -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment without primary key in innodb?
2010/1/25 Yang Zhang yanghates...@gmail.com: Right, I saw the docs. I'm fine with creating an index on it, but the only way I've successfully created a table with auto_increment is by making it a primary key. And I still don't understand why this requirement is there in the first place. Non-primary key works for me, as documented: --8 mysql create table test_ai (i int PRIMARY KEY, c int auto_increment, index(c)); Query OK, 0 rows affected (0,07 sec) mysql desc test_ai; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | i | int(11) | NO | PRI | NULL|| | c | int(11) | NO | MUL | NULL| auto_increment | +---+-+--+-+-++ 2 rows in set (0,00 sec) mysql insert into test_ai (i) values (100), (200); Query OK, 2 rows affected (0,00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql select * from test_ai; +-+---+ | i | c | +-+---+ | 100 | 1 | | 200 | 2 | +-+---+ 2 rows in set (0,00 sec) --8 Regards, -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment without primary key in innodb?
The requirement is that it be indexed. The index need not be a primary key. mysql create table t (i int not null auto_increment, index(i)) engine innodb; Query OK, 0 rows affected (0.45 sec) On Jan 25, 2010, at 9:39 AM, Yang Zhang wrote: Right, I saw the docs. I'm fine with creating an index on it, but the only way I've successfully created a table with auto_increment is by making it a primary key. And I still don't understand why this requirement is there in the first place. On Mon, Jan 25, 2010 at 10:32 AM, Tom Worster f...@thefsb.org wrote: it's not an innodb thing: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Note There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0. -Original Message- From: Yang Zhang yanghates...@gmail.com Sent: Monday, January 25, 2010 10:21am To: mysql@lists.mysql.com Subject: auto_increment without primary key in innodb? In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@thefsb.org -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=paul.dub...@sun.com -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment without primary key in innodb?
yah, mysql only allows one auto increment field n that's used as the primary key in tables. I don't think it has to be the primary key as long as it is a unique key i think that's okay. so u should be able to do : create table (myid int unsigned not null auto_increment., unique key (myid)); but this is effectively a primary key if u want some auto incrementing behavior but have it do so only on certain scenarios and possibly hold null values, you can write an insert trigger that would update the field on every insert. Yong. On Mon, 2010-01-25 at 10:21 -0500, Yang Zhang wrote: In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- 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
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
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
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
auto_increment Issue
Hi All I have created a table.Now I need to make a field Auto_increment...Help me with this issue..An example will do.. Regards Jnani -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: auto_increment Issue
Date: Fri, 10 Apr 2009 15:15:28 +0530 From: jnaneshwar.banta...@kavach.net To: mysql@lists.mysql.com Subject: auto_increment Issue Hi All I have created a table.Now I need to make a field Auto_increment...Help me with this issue..An example will do.. Regards Jnani Hi Jnani, Assuming your table name is tbl and the field for the auto_increment is id, just write: ALTER TABLE tbl CHANGE id id INT UNSIGNED NOT NULL AUTO_INCREMENT; I hope that helps. Cheers. Alugo Abdulazeez. _ Show them the way! Add maps and directions to your party invites. http://www.microsoft.com/windows/windowslive/products/events.aspx
Re: auto_increment Issue
Hi While trying for the same,I am getting the following error Incorrect table definition; there can be only one auto column and it must be defined as a key Jnani Octavian Rasnita wrote: Or more simple: ALTER TABLE tbl modify id INT UNSIGNED NOT NULL AUTO_INCREMENT; -- Octavian - Original Message - From: abdulazeez alugo defati...@hotmail.com To: jnaneshwar.banta...@kavach.net; mysql@lists.mysql.com Sent: Friday, April 10, 2009 1:07 PM Subject: RE: auto_increment Issue Date: Fri, 10 Apr 2009 15:15:28 +0530 From: jnaneshwar.banta...@kavach.net To: mysql@lists.mysql.com Subject: auto_increment Issue Hi All I have created a table.Now I need to make a field Auto_increment...Help me with this issue..An example will do.. Regards Jnani Hi Jnani, Assuming your table name is tbl and the field for the auto_increment is id, just write: ALTER TABLE tbl CHANGE id id INT UNSIGNED NOT NULL AUTO_INCREMENT; I hope that helps. Cheers. Alugo Abdulazeez. _ Show them the way! Add maps and directions to your party invites. http://www.microsoft.com/windows/windowslive/products/events.aspx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: auto_increment Issue
Date: Fri, 10 Apr 2009 15:55:33 +0530 From: jnaneshwar.banta...@kavach.net To: orasn...@gmail.com CC: defati...@hotmail.com; mysql@lists.mysql.com Subject: Re: auto_increment Issue Hi While trying for the same,I am getting the following error Incorrect table definition; there can be only one auto column and it must be defined as a key Jnani Hi Jnani, Assuming your table name is tbl and the field for the auto_increment is id, just write: ALTER TABLE tbl CHANGE id id INT UNSIGNED NOT NULL AUTO_INCREMENT; I hope that helps. Cheers. Alugo Abdulazeez. Oh sorry! it should be ALTER TABLE tbl CHANGE id id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT; I hope this helps unless you have a column already defined as auto_increment in the same table. Cheers. _ News, entertainment and everything you care about at Live.com. Get it now! http://www.live.com/getstarted.aspx
Re: auto_increment Issue
Hi , Thanks,Its working now Jnani abdulazeez alugo wrote: Date: Fri, 10 Apr 2009 15:55:33 +0530 From: jnaneshwar.banta...@kavach.net To: orasn...@gmail.com CC: defati...@hotmail.com; mysql@lists.mysql.com Subject: Re: auto_increment Issue Hi While trying for the same,I am getting the following error Incorrect table definition; there can be only one auto column and it must be defined as a key Jnani Hi Jnani, Assuming your table name is tbl and the field for the auto_increment is id, just write: ALTER TABLE tbl CHANGE id id INT UNSIGNED NOT NULL AUTO_INCREMENT; I hope that helps. Cheers. Alugo Abdulazeez. Oh sorry! it should be ALTER TABLE tbl CHANGE id id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT; I hope this helps unless you have a column already defined as auto_increment in the same table. Cheers. _ News, entertainment and everything you care about at Live.com. Get it now! http://www.live.com/getstarted.aspx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment problem
alter table tablename modify id int not null auto_increment primary key; On Sat, Oct 25, 2008 at 2:48 AM, Paul [EMAIL PROTECTED] wrote: Anybody know if there's a way to change a primary key field that is not auto-incremented, turning on auto-increment but preserving the values that are currently in it? TIA, Paul W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
auto_increment problem
Anybody know if there's a way to change a primary key field that is not auto-incremented, turning on auto-increment but preserving the values that are currently in it? TIA, Paul W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to build sub-sequence ... AUTO_INCREMENT on a sub-key starting with a specific offset?
You could create an extra table in order to record the max number of widget,the the ID should alway be 1. On Thu, Oct 16, 2008 at 2:04 AM, Rob Wultsch [EMAIL PROTECTED] wrote: I would do a muli key PK with a after insert trigger to that would change widget_number 1 to 1000. Just my HO... I would use this combo as the primary key, but I hate doing joins with multiple primary keys, so I'll also keep the widget_id for the purpose of making joins easier. Why? Both of these fields are ints, so the key length would rather small. I don't think the 'MAX' is optimized, though and maybe there is a better, more robust way to do this which is already built into MySQL that I don't know about. MAX should be fast, assuming the field is indexed. -- Rob Wultsch [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
How to build sub-sequence ... AUTO_INCREMENT on a sub-key starting with a specific offset?
All, I am developing a service in MySQL that models a service I've already built in PostgreSQL. I'm trying to port over some of my ideas from that platform to MySQL. Here's the setup: Let's say I have 2 tables: 'account' and 'widget'. Each of these tables have a primary key but the widget table references the account table with a foreign key on account_id: -- CREATE TABLE `account` ( `account_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT, `label` VARCHAR(64) COLLATE latin1_swedish_ci NOT NULL DEFAULT '', PRIMARY KEY (`account_id`), UNIQUE KEY `account_id` (`account_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; CREATE TABLE `widget` ( `widget_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT, `label` VARCHAR(64) COLLATE latin1_swedish_ci NOT NULL DEFAULT '', `account_id` INTEGER(11) UNSIGNED NOT NULL, `widget_number` INTEGER(11) UNSIGNED NOT NULL, PRIMARY KEY (`widget_id`), UNIQUE KEY `widget_id` (`widget_id`), UNIQUE KEY `widget_number` (`account_id`, `widget_number`), KEY `account_id` (`account_id`), CONSTRAINT `widget_fk_account_id` FOREIGN KEY (`account_id`) REFERENCES `account` (`account_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; -- The problem is that I don't want customers to see the 'widget_id' column on the url like this: http://.../app/widget/edit.php?widget_id=123456 Because if they see this, they can use this information to deduce how many widgets exist in my database. Instead, I want to add another column to the widget table called 'widget_number' that contains an AUTO_INCREMENT column which is based on the 'account_id' in that table. What this means is that every account contains a widget number 1000. And if you add another widget, you get widget number 1001, etc. This way, the url will look like this: http://.../app/widget/edit.php?widget_number=1000 And if I combine widget number 1000 with the account_id, I can uniquely identify an entry in the database. In other words, I have a new primary key candidate of (account_id, widget_number). I would use this combo as the primary key, but I hate doing joins with multiple primary keys, so I'll also keep the widget_id for the purpose of making joins easier. My question is how can I get MySQL to automatically assign the widget_number values? I want the first value for every account ID to be 1000 and increment from there. I almost need a trigger that does something like this: SELECT COALESCE(MAX(widget_number) + 1, 1000) INTO NEW.widget_number FROM widget WHERE account_id = NEW.account_id; I don't think the 'MAX' is optimized, though and maybe there is a better, more robust way to do this which is already built into MySQL that I don't know about. I am using InnoDB, so the solution needs to be transaction safe. Help would be appreciated. -- Dante -- D. Dante Lorenso [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to build sub-sequence ... AUTO_INCREMENT on a sub-key starting with a specific offset?
I would do a muli key PK with a after insert trigger to that would change widget_number 1 to 1000. Just my HO... I would use this combo as the primary key, but I hate doing joins with multiple primary keys, so I'll also keep the widget_id for the purpose of making joins easier. Why? Both of these fields are ints, so the key length would rather small. I don't think the 'MAX' is optimized, though and maybe there is a better, more robust way to do this which is already built into MySQL that I don't know about. MAX should be fast, assuming the field is indexed. -- Rob Wultsch [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and auto_increment
Thanks. That sounds pretty labor intensive, since I'd have to do the copy for each table. That makes the process much harder to automate. And I'm not sure the end result would be what I wanted anyway. I would like to be able to do this without copying data around. Does anybody have another suggestion? On Mon, May 19, 2008 at 11:00 PM, Moon's Father [EMAIL PROTECTED] wrote: You can import your data into a test database,then export the data using statement select ... into ... then You can complare the two. On Tue, May 20, 2008 at 9:14 AM, Dan Lipsitt [EMAIL PROTECTED] wrote: Is there a set of flags for mysqldump that will include the auto_increment specifier for columns, but leave out the AUTO_INCREMENT=x saved values? I want to compare the schema of two versions of a database, without considering the data. In my opinion, the saved auto increment counter is part of the data, not part of the schema, but the --no-data flag doesn't seem to share my opinion. If I run mysqldump a -d a.sql mysqldump b -d b.sql diff a.sql b.sql I want to see no output if the schemas are the same, regardless of how many rows of data each database has. Is there a way to do this with just mysqldump or mysql? Thanks, Dan -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
mysqldump and auto_increment
Is there a set of flags for mysqldump that will include the auto_increment specifier for columns, but leave out the AUTO_INCREMENT=x saved values? I want to compare the schema of two versions of a database, without considering the data. In my opinion, the saved auto increment counter is part of the data, not part of the schema, but the --no-data flag doesn't seem to share my opinion. If I run mysqldump a -d a.sql mysqldump b -d b.sql diff a.sql b.sql I want to see no output if the schemas are the same, regardless of how many rows of data each database has. Is there a way to do this with just mysqldump or mysql? Thanks, Dan
Re: mysqldump and auto_increment
You can import your data into a test database,then export the data using statement select ... into ... then You can complare the two. On Tue, May 20, 2008 at 9:14 AM, Dan Lipsitt [EMAIL PROTECTED] wrote: Is there a set of flags for mysqldump that will include the auto_increment specifier for columns, but leave out the AUTO_INCREMENT=x saved values? I want to compare the schema of two versions of a database, without considering the data. In my opinion, the saved auto increment counter is part of the data, not part of the schema, but the --no-data flag doesn't seem to share my opinion. If I run mysqldump a -d a.sql mysqldump b -d b.sql diff a.sql b.sql I want to see no output if the schemas are the same, regardless of how many rows of data each database has. Is there a way to do this with just mysqldump or mysql? Thanks, Dan -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: auto_increment
Hiep Nguyen schrieb:
hi list,
reading manual on mysql regarding auto_increment with multiple-column
index:
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
+++-+
| grp| id | name|
+++-+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+++-+
my question is what id would be if i:
UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2' AND
`name`='ostrich' LIMIT 1;
you do not need `name`='ostrich' in WHERE, cause grp,id are already UNIQUE
your key is grp,id (bird,2)
but your query will fail, because there is already grp,id (mammal,2) and
therre can not be two identical UNIQUE (PRIMARY) keys
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment
Sebastian Mendel schrieb:
Hiep Nguyen schrieb:
hi list,
reading manual on mysql regarding auto_increment with multiple-column
index:
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
+++-+
| grp| id | name|
+++-+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+++-+
my question is what id would be if i:
UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2'
AND `name`='ostrich' LIMIT 1;
you do not need `name`='ostrich' in WHERE, cause grp,id are already UNIQUE
your key is grp,id (bird,2)
but your query will fail, because there is already grp,id (mammal,2) and
therre can not be two identical UNIQUE (PRIMARY) keys
auto_increment comes only in effect when inserting NULL (or 0 in some SQL
mode) or nothing (with default NULL, 0 what should be always the case for
auto_increment fields)
your query should look like this:
UPDATE `animals`
SET `grp` = 'mammal',
`id` = NULL
WHERE `grp` = 'bird'
AND `id` = '2'
LIMIT 1;
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment
Are you sure, I just get:
CREATE TABLE ...
ERROR 1075 (42000): Incorrect table definition; there can be only one
auto column and it must be defined as a key
On version 5.0.41. What version are you using?
Hiep Nguyen wrote:
hi list,
reading manual on mysql regarding auto_increment with multiple-column
index:
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
+++-+
| grp| id | name|
+++-+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+++-+
my question is what id would be if i:
UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2' AND
`name`='ostrich' LIMIT 1;
i'm confused on auto_increment now.
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment
Ben Clewett schrieb: Are you sure, I just get: CREATE TABLE ... ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key the mentioned CREATE TABLE is fine and works On version 5.0.41. What version are you using? this works on all versions, and the example is from the MySQL manual http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment
You are right, I've tried 5.0.18 and 5.0.45 which work. There must have been a bug in 5.0.41 with which I used test the question... I belive the question has been answered by now anyway :) Ben Sebastian Mendel wrote: Ben Clewett schrieb: Are you sure, I just get: CREATE TABLE ... ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key the mentioned CREATE TABLE is fine and works On version 5.0.41. What version are you using? this works on all versions, and the example is from the MySQL manual http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment
On Tue, 22 Apr 2008, Sebastian Mendel wrote:
Sebastian Mendel schrieb:
Hiep Nguyen schrieb:
hi list,
reading manual on mysql regarding auto_increment with multiple-column
index:
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
+++-+
| grp| id | name|
+++-+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+++-+
my question is what id would be if i:
UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2' AND
`name`='ostrich' LIMIT 1;
you do not need `name`='ostrich' in WHERE, cause grp,id are already UNIQUE
your key is grp,id (bird,2)
but your query will fail, because there is already grp,id (mammal,2) and
therre can not be two identical UNIQUE (PRIMARY) keys
auto_increment comes only in effect when inserting NULL (or 0 in some SQL
mode) or nothing (with default NULL, 0 what should be always the case for
auto_increment fields)
your query should look like this:
UPDATE `animals`
SET `grp` = 'mammal',
`id` = NULL
WHERE `grp` = 'bird'
AND `id` = '2'
LIMIT 1;
--
Sebastian Mendel
thanks, i got it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto_increment
hi list,
reading manual on mysql regarding auto_increment with multiple-column
index:
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
+++-+
| grp| id | name|
+++-+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+++-+
my question is what id would be if i:
UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2' AND
`name`='ostrich' LIMIT 1;
i'm confused on auto_increment now.
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
x schrieb: thanks may you point out which chapter says? From manual I get the following answer agaist to my result(my server version 5.0.45), For |InnoDB| before version 5.0.3, |TRUNCATE TABLE| is mapped to |DELETE|, so there is no difference. Starting with MySQL 5.0.3, fast |TRUNCATE TABLE| is available. However, the operation is still mapped to |DELETE| if there are foreign key constraints that reference the table. (When fast truncate is used, it resets any |AUTO_INCREMENT| counter. From MySQL 5.0.13 on, the |AUTO_INCREMENT| counter is reset by |TRUNCATE TABLE|, regardless of whether there is a foreign key constraint.) The table handler does not remember the last used |AUTO_INCREMENT| value, but starts counting from the beginning. This is true even for |MyISAM| and |InnoDB|, which normally do not reuse sequence values. See: http://dev.mysql.com/doc/refman/5.0/en/truncate.html http://dev.mysql.com/doc/refman/5.1/en/truncate.html oh sorry, yes, i was confused, somehow i mixed some old behavior and InnoDB behavior ... Martijn Tonies, sorry too -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
过客 schrieb: [...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency. -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
[...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency. lol, but TRUNCATE empties the table... What good are your references? :-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
Martijn Tonies schrieb: [...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency. lol, but TRUNCATE empties the table... What good are your references? :-) LOL ??? very funny, really why not read the manual before posting? could help me save a lot of time ... only one table is truncated, not the one referencing to this one it is better to have references leading to 'nothing' than to a wrong datarow ... i think this is very easy to understand - better save than sorry! -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
[...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency. lol, but TRUNCATE empties the table... What good are your references? :-) LOL ??? very funny, really why not read the manual before posting? could help me save a lot of time ... You don't have to answer if you want to save time :-) only one table is truncated, not the one referencing to this one I know that. it is better to have references leading to 'nothing' than to a wrong datarow ... i think this is very easy to understand - better save than sorry! Why is a row with an invalid reference better? It's invalid data and you just corrupted your database. The point I'm trying to make is that this part of the documentation is a bit strange, to say at least. The only benefit I could thing of, is being able to re-fill the table with the original data, but then your auto-inc should be turned OFF in between the mass INSERT and normal operations. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
Martijn Tonies schrieb: [...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency. lol, but TRUNCATE empties the table... What good are your references? it is better to have references leading to 'nothing' than to a wrong datarow ... i think this is very easy to understand - better save than sorry! Why is a row with an invalid reference better? It's invalid data and you just corrupted your database. please define 'invalid' - i think invalid is it in booth cases, so an empty invalid is better than a wrong invalid, or not? better have an unpayed bill leading to no costumer than to a wrong customer -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
[...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency. lol, but TRUNCATE empties the table... What good are your references? it is better to have references leading to 'nothing' than to a wrong datarow ... i think this is very easy to understand - better save than sorry! Why is a row with an invalid reference better? It's invalid data and you just corrupted your database. please define 'invalid' - i think invalid is it in booth cases, so an empty invalid is better than a wrong invalid, or not? IMO, you're f***ed in both cases :-) better have an unpayed bill leading to no costumer than to a wrong customer Why is that better? If you TRUNCATEd the table, you know you're doing something wrong/your data is messed up. As I said, what I wanted to point out is that this piece in the documentation is a bit strange. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
Martijn Tonies schrieb: [...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency. lol, but TRUNCATE empties the table... What good are your references? it is better to have references leading to 'nothing' than to a wrong datarow ... i think this is very easy to understand - better save than sorry! Why is a row with an invalid reference better? It's invalid data and you just corrupted your database. please define 'invalid' - i think invalid is it in booth cases, so an empty invalid is better than a wrong invalid, or not? IMO, you're f***ed in both cases :-) better have an unpayed bill leading to no costumer than to a wrong customer Why is that better? If you TRUNCATEd the table, you know you're doing something wrong/your data is messed up. yes, but this was not the point of the discussion the point was why is auto_increment not reset - and the above is the reason for this - i was not discussing if this is good or bad, or if it is good to delete table content, or if the table content was deleted by accident As I said, what I wanted to point out is that this piece in the documentation is a bit strange. yes, i have read it ... ;-) -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
thanks may you point out which chapter says? From manual I get the following answer agaist to my result(my server version 5.0.45), For |InnoDB| before version 5.0.3, |TRUNCATE TABLE| is mapped to |DELETE|, so there is no difference. Starting with MySQL 5.0.3, fast |TRUNCATE TABLE| is available. However, the operation is still mapped to |DELETE| if there are foreign key constraints that reference the table. (When fast truncate is used, it resets any |AUTO_INCREMENT| counter. From MySQL 5.0.13 on, the |AUTO_INCREMENT| counter is reset by |TRUNCATE TABLE|, regardless of whether there is a foreign key constraint.) The table handler does not remember the last used |AUTO_INCREMENT| value, but starts counting from the beginning. This is true even for |MyISAM| and |InnoDB|, which normally do not reuse sequence values. See: http://dev.mysql.com/doc/refman/5.0/en/truncate.html http://dev.mysql.com/doc/refman/5.1/en/truncate.html Sebastian Mendel 写道: 过客 schrieb: [...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency.
why in procedure truncate table do not reset auto_increment?
*hi everyone: * I've some puzzle with the following test: CREATE TABLE `demo` ( `id` int(11) NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; INSERT INTO demo VALUES(100); delimiter // create procedure test() DETERMINISTIC begin TRUNCATE table demo; show table status like 'demo'; END// delimiter ; when call test() I got auto_increment=101, why in procedure TRUNCATE table demo do not reset auto_increment? any help much appreciate !
Re: Replication and AUTO_INCREMENT; is it safe?
On 10/24/07, Eric Frazier [EMAIL PROTECTED] wrote: js wrote: Hi list, Reading How AUTO_INCREMENT Handling Works in InnoDB[1] makes me wonder how is it possible to replicate AUTO_INCREMENTed value to slaves. According to the doc, If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk. Let's say there are two server, A and B. A replicates its data to B, the slave. A and B has a table that looks like(column 'id' is auto_increment field) id value 1 a 2 b 3 c 4 d If After delete from table where id = 4 and restart mysqld on server B, insert into table (value) values(e) is executed on server A. In this case, because A's internal counter is 4, table on A would be 1 a 2 b 3 c 5 e But B's would be different because restarting mysqld flushed InnoDB's internal counter. 1 a 2 b 3 c 4 e Is this correct? or MySQL is smart enough to handle this problem? Thanks. [1]http://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html http://dev.mysql.com/doc/refman/5.0/en/faqs-general.html See 28.1.5 But there are more reasons to avoid auto-increment in mysql. I haven't run into the problem above, but I have had such problems when restoring backups. Make your data make sense, a mindless counting number just to make a table unique doesn't every make any sense. Session ids, timestamps, combinations of fields all make much better primary keys and it is safer overall to implement a counter function in your app than to trust mysql's js wrote: Thank you for your reply. But I couldn't under stand how --auto-increment-increment and --auto-increment-offset helps me avoid my problem. Could you please explain? Restarting the server doesn't reset autoinc.. But that can happen when you restore a backup, I don't remember what to avoid of the top of my head, but look into mysqldump and do some tests. Best way to understand But, you can avoid any problem with autoinc by just not using it. If you must use it for replication it is quite safe to use it if you are only replicating to a slave write only, so the slave is not also another master(you are not doing inserts/updates on the slave as well), or if you need to replicate in a circle use auto-increment-increment etc. I think it is not a bad idea to use these even if your slave is just a slave. Bottom line, if you are designing a DB, for max safety avoid autoinc entirely. It will save you headaches for a little extra work to start. This is one area where MySQL still deserves some jeering because Postgress had this figured out a long time ago with proper sequences that are a lot easier to mange. With all of the features and cool stuff MySQL has added in the last few years, I don't get why they haven't fixed autoinc or added a true sequence type. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and AUTO_INCREMENT; is it safe?
Hello, On Oct 23, 2007, at 11:23 AM, js wrote: Hi list, Reading How AUTO_INCREMENT Handling Works in InnoDB[1] makes me wonder how is it possible to replicate AUTO_INCREMENTed value to slaves. According to the doc, If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk. Let's say there are two server, A and B. A replicates its data to B, the slave. A and B has a table that looks like(column 'id' is auto_increment field) cut Is this correct? or MySQL is smart enough to handle this problem? The binary logs in MySQL store the generated auto_increment id and use that instead of generating a new value on the slave. If you run mysqlbinlog on a binary log, you will see an output similar to: # at 728 #071024 10:53:54 server id 1 end_log_pos 28Intvar SET INSERT_ID=3/*!*/; # at 756 #071024 10:53:54 server id 1 end_log_pos 124 Query thread_id=3 exec_timSET TIMESTAMP=1193237634/*!*/; insert into ib_test values (NULL)/*!*/; The SET INSERT_ID functionality will cause the next INSERT to use that value for the auto_increment regardless of what it would have generated. Regards, Harrison -- Harrison C. Fisk, Principal Support Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication and AUTO_INCREMENT; is it safe?
Hi list, Reading How AUTO_INCREMENT Handling Works in InnoDB[1] makes me wonder how is it possible to replicate AUTO_INCREMENTed value to slaves. According to the doc, If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk. Let's say there are two server, A and B. A replicates its data to B, the slave. A and B has a table that looks like(column 'id' is auto_increment field) id value 1 a 2 b 3 c 4 d If After delete from table where id = 4 and restart mysqld on server B, insert into table (value) values(e) is executed on server A. In this case, because A's internal counter is 4, table on A would be 1 a 2 b 3 c 5 e But B's would be different because restarting mysqld flushed InnoDB's internal counter. 1 a 2 b 3 c 4 e Is this correct? or MySQL is smart enough to handle this problem? Thanks. [1]http://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and AUTO_INCREMENT; is it safe?
Reading How AUTO_INCREMENT Handling Works in InnoDB[1] makes me wonder how is it possible to replicate AUTO_INCREMENTed value to slaves. According to the doc, If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk. Let's say there are two server, A and B. A replicates its data to B, the slave. A and B has a table that looks like(column 'id' is auto_increment field) id value 1 a 2 b 3 c 4 d If After delete from table where id = 4 and restart mysqld on server B, insert into table (value) values(e) is executed on server A. Why would you delete data from the slave? In this case, because A's internal counter is 4, table on A would be 1 a 2 b 3 c 5 e But B's would be different because restarting mysqld flushed InnoDB's internal counter. 1 a 2 b 3 c 4 e Is this correct? or MySQL is smart enough to handle this problem? Thanks. [1]http://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and AUTO_INCREMENT; is it safe?
If After delete from table where id = 4 and restart mysqld on server B, insert into table (value) values(e) is executed on server A. Why would you delete data from the slave? The delete statement is for Master, not slave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and AUTO_INCREMENT; is it safe?
Thank you for your reply. But I couldn't under stand how --auto-increment-increment and --auto-increment-offset helps me avoid my problem. Could you please explain? On 10/24/07, Eric Frazier [EMAIL PROTECTED] wrote: js wrote: Hi list, Reading How AUTO_INCREMENT Handling Works in InnoDB[1] makes me wonder how is it possible to replicate AUTO_INCREMENTed value to slaves. According to the doc, If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk. Let's say there are two server, A and B. A replicates its data to B, the slave. A and B has a table that looks like(column 'id' is auto_increment field) id value 1 a 2 b 3 c 4 d If After delete from table where id = 4 and restart mysqld on server B, insert into table (value) values(e) is executed on server A. In this case, because A's internal counter is 4, table on A would be 1 a 2 b 3 c 5 e But B's would be different because restarting mysqld flushed InnoDB's internal counter. 1 a 2 b 3 c 4 e Is this correct? or MySQL is smart enough to handle this problem? Thanks. [1]http://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html http://dev.mysql.com/doc/refman/5.0/en/faqs-general.html See 28.1.5 But there are more reasons to avoid auto-increment in mysql. I haven't run into the problem above, but I have had such problems when restoring backups. Make your data make sense, a mindless counting number just to make a table unique doesn't every make any sense. Session ids, timestamps, combinations of fields all make much better primary keys and it is safer overall to implement a counter function in your app than to trust mysql's -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sequential numbering in Auto_Increment Field across two tables
I have two tables. TableA is current data. TableB (created with 'Create Table A like Table B' ) is an archive where deleted data is inserted from Table A before being deleted from table B with INSERT INTO TableA Select * , Null, NOW (), 'ACTION', 'USER' from TableA. The null column in TableB is a primary, Not Null AUTO_INCREMENT field. The issue is that the first auto increment number in the primary key of TableA is the next highest value of the AUTO_INCREMENT field of tableA instead of what I would have suspected was 1. Is this normal. Does it matter. And will this create conflict with other quires using LAST_INSERT_ID() Function? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Behaviour of auto_increment
I have created a new table, with an auto_increment value. I would like the first auto_increment value to be 1001. So I - 1) inserted a fake record with an id of 1000 2) alter table tblname auto_increment=1000 (with and without a fake record) 3) alter table tblname auto_increment=1001 (with and without a fake record) Mysql version is 5.0.38 using MyIsam tables. Can someone explain auto_increment, more than what is in the manual? I have read it, and apparently I am missing something or don't understand it. Thanks! -- Andy Carlson --- Gamecube:$150,PSO:$50,Broadband Adapter: $35, Hunters License: $8.95/month, The feeling of seeing the red box with the item you want in it:Priceless.
Re: Behaviour of auto_increment
At 5:34 PM -0500 7/15/07, Andrew Carlson wrote: I have created a new table, with an auto_increment value. I would like the first auto_increment value to be 1001. So I - 1) inserted a fake record with an id of 1000 2) alter table tblname auto_increment=1000 (with and without a fake record) 3) alter table tblname auto_increment=1001 (with and without a fake record) Mysql version is 5.0.38 using MyIsam tables. Can someone explain auto_increment, more than what is in the manual? I have read it, and apparently I am missing something or don't understand it. Thanks! We could probably explain it better if you said just what it is you're wondering about. For example, did the things you try not work and you're wondering why? You didn't say what were the results of your actions, nor whether they were what you expected. Please clarify. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Behaviour of auto_increment
Andrew Carlson wrote:
I have created a new table, with an auto_increment value. I would
like the
first auto_increment value to be 1001. So I -
1) inserted a fake record with an id of 1000
2) alter table tblname auto_increment=1000 (with and without a fake
record)
3) alter table tblname auto_increment=1001 (with and without a fake
record)
Mysql version is 5.0.38 using MyIsam tables. Can someone explain
auto_increment, more than what is in the manual? I have read it, and
apparently I am missing something or don't understand it. Thanks!
mysql CREATE TEMPORARY TABLE autotest (
- id INT UNSIGNED NOT NULL AUTO_INCREMENT,
- name VARCHAR(10) NOT NULL,
- PRIMARY KEY (id))
- AUTO_INCREMENT = 1001;
mysql INSERT INTO autotest (name) VALUES ('Gary'), ('Andrew'), ('Paul');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql SELECT * FROM autotest;
+--++
| id | name |
+--++
| 1001 | Gary |
| 1002 | Andrew |
| 1003 | Paul |
+--++
3 rows in set (0.01 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert into multiple tables using auto_increment reference
Hi,
Is it possible to insert values into two tables simultaneously and
have the value of one of the columns in the second table be the
auto_increment value from inserting into the first?
E.g. if table1 has an auto_increment column c1, the logic I'm looking
for would be something like:
INSERT INTO table1(c2, c3, c4), table2(c5, c6, c7) VALUES ('v1', 'v2',
'v3', table1.c1, 'v4', 'v5');
where table2.c5 is a reference to table1.c1.
Thanks,
Hamish
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert into multiple tables using auto_increment reference
Hi, Hamish Allan wrote: Hi, Is it possible to insert values into two tables simultaneously and have the value of one of the columns in the second table be the auto_increment value from inserting into the first? No, because you can only insert into one table at a time. But you can write a stored procedure to do this. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert into multiple tables using auto_increment reference
On 6/11/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Hamish Allan wrote: Hi, Is it possible to insert values into two tables simultaneously and have the value of one of the columns in the second table be the auto_increment value from inserting into the first? No, because you can only insert into one table at a time. But you can write a stored procedure to do this. Thanks! H -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sequences and auto_increment
Hi all,
I'm new to MySQL coming from PostgreSQL backgroud. I'd like to know how to
obtain the same effect of a sequence + concat as default value of a table in
mysql. For example, consider the following table definition:
CREATE TABLE competenza
(
id_competenza character varying(30) NOT NULL DEFAULT ('comp-06-'::text ||
(nextval('sequenza_competenza'::regclass))::text),
descrizione character varying(100),
CONSTRAINT competenza_pkey PRIMARY KEY (id_competenza)
)
there, id_competenza is compound by a string comp-06 and the next value of a
sequence (similar to auto_increment). In MySQL there're no sequences, or
better, there's only an auto_increment action on an int field. How can I
obtain the same effect of the concatenation of a sequence and a string?
Thanks,
Luca
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequences and auto_increment
Hi,
I'm new to MySQL coming from PostgreSQL backgroud. I'd like to know how to
obtain the same effect of a sequence + concat as default value of a table
in
mysql. For example, consider the following table definition:
CREATE TABLE competenza
(
id_competenza character varying(30) NOT NULL DEFAULT ('comp-06-'::text
||
(nextval('sequenza_competenza'::regclass))::text),
descrizione character varying(100),
CONSTRAINT competenza_pkey PRIMARY KEY (id_competenza)
)
there, id_competenza is compound by a string comp-06 and the next value
of a
sequence (similar to auto_increment). In MySQL there're no sequences, or
better, there's only an auto_increment action on an int field. How can I
obtain the same effect of the concatenation of a sequence and a string?
Hmm, well, I think: not at all.
Unless you implement something like sequences yourself.
MySQL doesn't allow functions to be used in the DEFAULT clause either.
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequences and auto_increment
I don't think MySQL has exactly what you are looking for, but you may be able
to get the behavior you want.
The auto_increment value is actually based on an index and doesn't have to be unique. So you could create a compound index that has
one or more fields plus the auto_increment field. The effect would be having multiple sequence numbers.
CREATE TABLE competenza (
competenza varchar(30) NOT NULL default 'comp-06-',
id_competenza int unsigned not null auto_increment,
descrizione varchar(100),
PRIMARY KEY (competenza, id_competenza)
)
Since your PRIMARY KEY is a combination of 2 fields (competenza + id_competenza ), each competenza value will have it's own auto
increment (id_competenza ) sequence. So id_competenza won't be unique, but the combination of competenza + id_competenza will be.
- Original Message -
From: Luca Ferrari [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, January 02, 2007 8:54 AM
Subject: sequences and auto_increment
Hi all,
I'm new to MySQL coming from PostgreSQL backgroud. I'd like to know how to
obtain the same effect of a sequence + concat as default value of a table in
mysql. For example, consider the following table definition:
CREATE TABLE competenza
(
id_competenza character varying(30) NOT NULL DEFAULT ('comp-06-'::text ||
(nextval('sequenza_competenza'::regclass))::text),
descrizione character varying(100),
CONSTRAINT competenza_pkey PRIMARY KEY (id_competenza)
)
there, id_competenza is compound by a string comp-06 and the next value of a
sequence (similar to auto_increment). In MySQL there're no sequences, or
better, there's only an auto_increment action on an int field. How can I
obtain the same effect of the concatenation of a sequence and a string?
Thanks,
Luca
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequences and auto_increment
On Tuesday 02 January 2007 16:51 Brent Baisley's cat, walking on the keyboard, wrote: CREATE TABLE competenza ( competenza varchar(30) NOT NULL default 'comp-06-', id_competenza int unsigned not null auto_increment, descrizione varchar(100), PRIMARY KEY (competenza, id_competenza) ) Since your PRIMARY KEY is a combination of 2 fields (competenza + id_competenza ), each competenza value will have it's own auto increment (id_competenza ) sequence. So id_competenza won't be unique, but the combination of competenza + id_competenza will be. Thanks for your suggestion, but this would make my queries more complex, since to get the id of a skill (italian is competenza) will require querying two fields. It's true that I can simulate sequences with a table with auto_increment, but this means I need to insert a record before in such table to get the new id and then insert in my competenza table. This would require a transaction and will result even more complex than the above, I guess. Any other idea? Thanks, Luca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
