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: 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
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: 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
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
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
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
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]
Re: auto_increment field start value
David - there's some info in the online docs here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Specifically: To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this: mysql ALTER TABLE tbl AUTO_INCREMENT = 100; HTH, Dan On 9/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I seem to recall that when creating a table, you could designate an auto_increment field to begin counting at zero(0) instead of one (1), but I can't find an example in the documents. I'm using 4.0.16 and table type=myisam. David -- 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: auto_increment field start value
Thanks, Dan, but I can't get it to work. Defining a column like this: a int not null auto_increment=0 primary key throws an error, and while the alter table statement seems to work ok, whether the table is empty or not, it has no effect on subsequent inserts. I'm wondering if 4.0.16 has not implemented this feature. David - there's some info in the online docs here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Specifically: To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this: mysql ALTER TABLE tbl AUTO_INCREMENT = 100; HTH, Dan On 9/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I seem to recall that when creating a table, you could designate an auto_increment field to begin counting at zero(0) instead of one (1), but I can't find an example in the documents. I'm using 4.0.16 and table type=myisam. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment field start value
OK. If you assign to auto_increment any number higher than what currently exists in the column, it changes the value and the incremented sequence from that point. But apparently you can't assign the value zero to the column, even if the table is empty. Thanks, Dan, but I can't get it to work. Defining a column like this: a int not null auto_increment=0 primary key throws an error, and while the alter table statement seems to work ok, whether the table is empty or not, it has no effect on subsequent inserts. I'm wondering if 4.0.16 has not implemented this feature. David - there's some info in the online docs here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Specifically: To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this: mysql ALTER TABLE tbl AUTO_INCREMENT = 100; HTH, Dan On 9/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I seem to recall that when creating a table, you could designate an auto_increment field to begin counting at zero(0) instead of one (1),but I can't find an example in the documents. I'm using 4.0.16 and table type=myisam. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: auto_increment field start value
I just tried it in 5.0.21, and found that it fails silently with zero (0). Works with 100. I did specify int, not unsigned int, in my test table. See http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html for some discussion about how you could get a zero in there; look for NO_AUTO_VALUE_ON_ZERO Dan On 9/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Thanks, Dan, but I can't get it to work. Defining a column like this: a int not null auto_increment=0 primary key throws an error, and while the alter table statement seems to work ok, whether the table is empty or not, it has no effect on subsequent inserts. I'm wondering if 4.0.16 has not implemented this feature. David - there's some info in the online docs here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Specifically: To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this: mysql ALTER TABLE tbl AUTO_INCREMENT = 100; HTH, Dan On 9/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I seem to recall that when creating a table, you could designate an auto_increment field to begin counting at zero(0) instead of one (1), but I can't find an example in the documents. I'm using 4.0.16 and table type=myisam. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment field start value
At 14:16 -0500 9/22/06, [EMAIL PROTECTED] wrote: I seem to recall that when creating a table, you could designate an auto_increment field to begin counting at zero(0) instead of one (1), but I can't find an example in the documents. Don't store 0 in an AUTO_INCREMENT column. -- 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: auto_increment field start value
At 14:16 -0500 9/22/06, [EMAIL PROTECTED] wrote: I seem to recall that when creating a table, you could designate an auto_increment field to begin counting at zero(0) instead of one (1), but I can't find an example in the documents. Don't store 0 in an AUTO_INCREMENT column. Thanks Dan and Paul, Since I can't store 0 in the column, I won't ;) For clarification, I'm writing a DICOM application and will store a complicated query result in table before further processing of the data. I guess I'll deal with the starts at 1, not 0 issue in the application. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment Question
Hi Mark
People_id is the column with auto increment? You can verify that it
really does have auto_increment by using the describe command. For
example:
mysql describe checks;
+-+--+--+-+-
++
| Field | Type | Null | Key | Default |
Extra |
+-+--+--+-+-
++
| id | int(14) unsigned | NO | PRI | |
auto_increment |
| original_check | int(14) unsigned | YES | |
||
| hotel_pos | int(7) | YES | |
||
| number | int(5) unsigned | YES | |
||
| revenuecenter | int(9) unsigned | YES | MUL |
||
| open_time | datetime | YES | MUL |
||
| close_time | datetime | YES | |
||
| employee| int(8) unsigned | YES | MUL |
||
| cashier | int(8) unsigned | YES | |
||
| restauranttable | int(3) unsigned | YES | |
||
| covers | int(4) | YES | |
||
+-+--+--+-+-
++
11 rows in set (0.15 sec)
If that's set up correctly, you should be able to leave that column
out of the list, e.g.
$people = INSERT INTO people (people_full_name, people_isactor,
people_isdirector) VALUES ( 'Jim Carey', 1, 0), (2, 'Tom Shadyac',
0, 1), ( 'Lawrence Kasdan', 0, 1), ( 'Kevin Kline', 1, 0), ( 'Ron
Livingston', 1, 0), ('Mike Judge', 0, 1);
$results = mysql_query($people) or die(mysql_error());
Thus I could insert into the table I describe above as:
mysql insert into checks (original_check, hotel_pos, number) values
(123, 1, 456), (124, 1, 456), (125, 1, 443);
Query OK, 3 rows affected (0.11 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select count(*) from checks;
+--+
| count(*) |
+--+
| 5542 |
+--+
1 row in set (0.04 sec)
mysql select * from checks limit 5539, 3;
+--++---++---
+---++--+-+-
++
| id | original_check | hotel_pos | number | revenuecenter |
open_time | close_time | employee | cashier | restauranttable | covers |
+--++---++---
+---++--+-+-
++
| 5540 | 123| 1 | 456| NULL
| || NULL | NULL| NULL|
NULL |
| 5541 | 124| 1 | 456| NULL
| || NULL | NULL| NULL|
NULL |
| 5542 | 125| 1 | 443| NULL
| || NULL | NULL| NULL|
NULL |
+--++---++---
+---++--+-+-
++
3 rows in set (0.01 sec)
Douglas Sims
[EMAIL PROTECTED]
On Jun 1, 2006, at 4:12 AM, Mark Sargent wrote:
Hi All,
if a table has an auto_incremented primary key why does the below
code require the people_id to be manually inserted? I got this from
Beginning PHP, Apache, MySQL Web Development book from Wrox.
Curious, as it seems to defeat the purpose of auto_increment, no?
Cheers.
$people = INSERT INTO people (people_id, people_full_name,
people_isactor, people_isdirector) VALUES (1, 'Jim Carey', 1, 0),
(2, 'Tom Shadyac', 0, 1), (3, 'Lawrence Kasdan', 0, 1), (4, 'Kevin
Kline', 1, 0), (5, 'Ron Livingston', 1, 0), (6, 'Mike Judge', 0, 1);
$results = mysql_query($people) or die(mysql_error());
Mark Sargent
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment Question
At 19:12 +1000 6/1/06, Mark Sargent wrote: Hi All, if a table has an auto_incremented primary key why does the below code require the people_id to be manually inserted? I got this from Beginning PHP, Apache, MySQL Web Development book from Wrox. Curious, as it seems to defeat the purpose of auto_increment, no? Cheers. $people = INSERT INTO people (people_id, people_full_name, people_isactor, people_isdirector) VALUES (1, 'Jim Carey', 1, 0), (2, 'Tom Shadyac', 0, 1), (3, 'Lawrence Kasdan', 0, 1), (4, 'Kevin Kline', 1, 0), (5, 'Ron Livingston', 1, 0), (6, 'Mike Judge', 0, 1); $results = mysql_query($people) or die(mysql_error()); If people_id is the AUTO_INCREMENT primary key, and the table doesn't have any records with 1 though 6 in the table already, the code should work. You can insert values into the column if they aren't already there. But you could also insert NULL (or omit people_id from the statement entirely), and then MySQL will generate sequence numbers automatically. -- 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: auto_increment and the value 0
Stanton, Brian wrote: I'm migrating a database from 4.0.12 on Solaris to 4.0.18-0 on Red Hat Linux. A few of the tables have a 0 (zero) in the auto_increment primary key column. However, when importing, the 0 in the insert is translated to the next available auto_increment value thus causing a duplicate key situation on the next value in the import. I've tried removing the 0 row from the export and adding it in manually afterwards, but that also translates the 0 to the next available auto_increment value. I've also tried creating the table with the table option AUTO_INCREMENT=0 and inserting the 0 row first. That also translated it to a value of 1 and caused duplicate keys. You can either: - create the table without the auto_increment field, load the data, and add the auto_increment field, or - change all your zero values *now* ( and related fields in other tables ), back things up, and then move the data I would take the 2nd option. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment and the value 0
Are you saying just change the row with the 0 value as the PK, and change the FK's in the related tables to point to the new value instaed of 0? If so, would this move the row logically to the end of the table, if the 0 PK was replaced with the next auto_increment value? I suppose that would be alot easier than trying to bump the PK and related FK values of the whole table by 1, just to give the first row in the table the auto_increment value of 1? What about before migrating the database, just adding a new row to the end of the table, that would duplicate the data in the first row, then deleting the first row from the table? Would that work? Keith In theory, theory and practice are the same; in practice they are not. On Thu, 30 Mar 2006, Daniel Kasak wrote: To: Stanton, Brian [EMAIL PROTECTED], 'mysql@lists.mysql.com' mysql@lists.mysql.com From: Daniel Kasak [EMAIL PROTECTED] Subject: Re: auto_increment and the value 0 Stanton, Brian wrote: I'm migrating a database from 4.0.12 on Solaris to 4.0.18-0 on Red Hat Linux. A few of the tables have a 0 (zero) in the auto_increment primary key column. However, when importing, the 0 in the insert is translated to the next available auto_increment value thus causing a duplicate key situation on the next value in the import. I've tried removing the 0 row from the export and adding it in manually afterwards, but that also translates the 0 to the next available auto_increment value. I've also tried creating the table with the table option AUTO_INCREMENT=0 and inserting the 0 row first. That also translated it to a value of 1 and caused duplicate keys. You can either: - create the table without the auto_increment field, load the data, and add the auto_increment field, or - change all your zero values *now* ( and related fields in other tables ), back things up, and then move the data I would take the 2nd option. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au k -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment and the value 0
[EMAIL PROTECTED] wrote: Are you saying just change the row with the 0 value as the PK, and change the FK's in the related tables to point to the new value instaed of 0? Yes. If so, would this move the row logically to the end of the table, if the 0 PK was replaced with the next auto_increment value? Not really. The primary key isn't ( or at least shouldn't be ) used to determine 'position' in the record. If you are actually using the primary key to determine 'position', ie select * from table order by primary_key ... then yes, the position will change. But the record is still in there, and still linked to related records ( as long as you update their foreign key values to the new value as well ). So as long as you don't depend on the primary key in something like the above ( ie you're not using the primary key to determine a record's age ), then it doesn't really matter what primary key it has. If you are depending on this value in an 'order by' clause, then find some work-around - order by something else - before you change it. Also keep in mind that when you do a select without an order by clause, you aren't guaranteed of getting the records in any particular order anyway - the DB server is allowed to return records in any order if you don't specify an 'order by' clause. I suppose that would be alot easier than trying to bump the PK and related FK values of the whole table by 1, just to give the first row in the table the auto_increment value of 1? Yes. That sounds messy. What about before migrating the database, just adding a new row to the end of the table, that would duplicate the data in the first row, then deleting the first row from the table? Would that work? Yes but it would be no different to just updating the primary key of the existing record. Either way, you will have issues with restoring from backups if you keep an auto_increment column with a zero value around ( as you've discovered ), so what ever you do, you need to get rid of those zero values. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment and the value 0
On 30/03/2006 12:31 p.m., Daniel Kasak wrote: [EMAIL PROTECTED] wrote: I suppose that would be alot easier than trying to bump the PK and related FK values of the whole table by 1, just to give the first row in the table the auto_increment value of 1? Yes. That sounds messy. What about before migrating the database, just adding a new row to the end of the table, that would duplicate the data in the first row, then deleting the first row from the table? Would that work? Yes but it would be no different to just updating the primary key of the existing record. Either way, you will have issues with restoring from backups if you keep an auto_increment column with a zero value around ( as you've discovered ), so what ever you do, you need to get rid of those zero values. Another option would be to reassign the zero row to be -1 (if the column is not UNSIGNED). Assuming the current zero row has some kind of special significance, this may make more sense than just giving it the next unused auto_increment value. This would also keep it in the same place with an ORDER BY. -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment and the value 0
You can override MySQL behaviour of generating a new value if you insert a 0 into an auton_increment field. Quoting from the manual: NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number. This mode can be useful if 0 has been stored in a table's AUTO_INCREMENTcolumn. (Storing 0 is not a recommended practice, by the way.) For example, if you dump the table with *mysqldump* and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. *mysqldump* now automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem. So you must alter the SQL-mode: Issue : mysqlSET @@session.sql_mode = NO_AUTO_VALUE_ON_ZERO; For altering the sessions sql_mode... now you will have to insert the dump with: mysqlSOURCE /path/to/dump.sql You can change it globally mysqlSET @@global.sql_mode = NO_AUTO_VALUE_ON_ZERO; But it's not recommend ... because until you change it back... MySQL will not generate auto increment values for your inserts if you use 0... only if you use NULL !!! The advantage is that you can import the dump from the command line. Another way is to add the statement: SET @@session.sql_mode = NO_AUTO_VALUE_ON_ZERO; into the dump itself... but... i think it's not that small... I believe this will do for you... but keep in mind that a value of 0 in an auto_increment column is not a good thing ... as everybody said before ! Good luck ! -- Gabriel PREDA Senior Web Developer
Re: auto_increment syntax
On 24/03/2006 11:06 a.m., Eric Beversluis wrote: Can someone illustrate the correct syntax for using auto_increment in making a table? I've studied the manual and I'm not seeing how it comes out. EG: CREATE TABLE Books ( bookID INT(5) PRIMARY KEY AUTO_INCREMENT... THEN WHAT? Thanks. EB Should be: bookID INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY Primary keys have to be NOT NULL, and AUTO_INCREMENT must appear before PRIMARY KEY AUTO_INCREMENT fields should normally be INT UNSIGNED as well since you generally won't want to store a negative ID number, and this gives you an extra byte. -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment
Hello. Please, could add more details of your actions. Are you inserting in the table which already has data? Please, provide exact error message. I'm not a telepathist, but in case of duplicate key errors a brute solution is to perform a dump with --insert-ignore option. Scott Johnson wrote: Hi All, I'm trying to restore a database where the first column is set to auto_increment. When I apply the inserts created from the mysqldump I get errors about the auto_increment column. Is there a may to turn it off while I run the inserts or do I have to modify the table? Thanks, Scott Johnson [EMAIL PROTECTED] Tel.: (514) 917-4922 Fax: (514) 673-0011 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment
You can change the table definition to not have the auto_increment column, and then ALTER TABLE MODIFY COLUMN after that. However, if you post your errors here, perhaps you'll get the answer that actually solves the problem, instead of working around it to possibly leave bad data for you to discover later on? -Sheeri On 2/3/06, Scott Johnson [EMAIL PROTECTED] wrote: Hi All, I'm trying to restore a database where the first column is set to auto_increment. When I apply the inserts created from the mysqldump I get errors about the auto_increment column. Is there a may to turn it off while I run the inserts or do I have to modify the table? Thanks, Scott Johnson [EMAIL PROTECTED] Tel.: (514) 917-4922 Fax: (514) 673-0011 -- 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: Auto_Increment value
http://dev.mysql.com/doc/refman/4.1/en/odbc-and-last-insert-id.html | | Cal Evans | http://www.calevans.com | Danesh Daroui wrote: Hi all, I have a simple table with an Auto_Increment column. I insert NULL to this column each time I insert a row to have an automatic unique value. The problem is that I want to have new automatically generated value back to insert it to another table. How can I have new Auto_Increment value back at the same time I send NULL to it and it is gerenated ? Regards, Danesh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment trouble (not the usual check || alter table solution, though)
On 5/16/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, with a similar structure, you can have : mysql select * from users where uid =262140; ++--+ | uid| nickname | ++--+ | 262140 | text | | 262141 | text | | 262142 | text | | 262143 | text | | 262144 | text | ++--+ 5 rows in set (0.00 sec) it's surprising that you can insert NULL in a primary key auto_increment. second, with only 1 values, if the auto_increment reached 2147483647 this means that you have an intensive delete, or the auto_increment had been altered. you can create table toto like users, and insert data again into toto. This will reincremente between 1 and 10xxx. Then rename toto to users. Mathias, Unfortunately, this didn't work out. I created, by hand, a table with the exact same definition although with a different name (newusers). Then, using a dump from the users table, I restored the data into the new table. Right after restoring the data, a show table status like 'newusers' shows that the auto_increment value is 2147483647. This could lead to three paths: the problem would be somewhere in the data, in the restore procedure or in the mysql engine itself. I've analyzed the data by hand (all the ~10900 lines), and I am sure that there isn't a single uid above 10900. Does this make any sense? Thanks in advance for any leads, Ricardo Oliveira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: auto_increment trouble (not the usual check || alter table solution, though)
This may not be a very elegant solution, but you can do this just to get the inserts going again: ALTER TABLE users MODIFY COLUMN uid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT; If you don't have any negative uids, then you will be able to store 2 billion more uids. Just out of curiosity, have you DROPPED re-created the table? -- Partha Dutta, Senior Consultant MySQL Inc, NY, USA, www.mysql.com Are you MySQL certified? www.mysql.com/certification -Original Message- From: Ricardo Oliveira [mailto:[EMAIL PROTECTED] Sent: Monday, May 16, 2005 5:00 PM To: mysql@lists.mysql.com Subject: auto_increment trouble (not the usual check || alter table solution, though) Hi everyone, First time on the list, although I've been (quietly) a (not-so)long-time quiet observer. This isn't a FAQ and I can assure you I've tried every procedure out there (both on and off-list) to solve it. A couple weeks ago, I started having problems with a MyISAM table which holds records from user profiles. It's a very simple table, with one auto_increment field and a bunch of other field - nothing out of the ordinary. To those of you familiar with slashcode, it's an old version of the users table: CREATE TABLE users ( uid int(11) NOT NULL auto_increment, nickname varchar(20) NOT NULL default '', realemail varchar(50) NOT NULL default '', fakeemail varchar(50) default NULL, homepage varchar(100) default NULL, passwd varchar(12) NOT NULL default '', sig varchar(160) default NULL, seclev int(11) NOT NULL default '0', matchname varchar(20) default NULL, banned int(11) NOT NULL default '0', permmod int(11) NOT NULL default '0', PRIMARY KEY (uid), KEY login (uid,passwd,nickname), KEY chk4user (nickname,realemail), KEY chk4email (realemail) ) TYPE=MyISAM PACK_KEYS=1; We have about 10k lines (more precisely, 10885 lines). Our code inserts data using a insert into users values (NULL, .) statement which hasn't changed over the last few years. Recently, we started seeing the last record with a _way_ high uid value - more precisely, 2147483647 (which keeps new inserts from happening). Obviously this is a problem with the auto_increment mechanism, and we have followed all the (documented) and traditional approaches, which follow: * search the row with the wrong uid and correct it: - upon inserting a new record, its uid will be 2147483647; * correct the uid and modify the auto_increment value from the table: mysql alter table users AUTO_INCREMENT=10900; Query OK, 10885 rows affected (0.33 sec) Records: 10885 Duplicates: 0 Warnings: 0 mysql show table status like 'users'; +---+++---++-+ -+--+---++ -+-+-++--- --+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++---++-+ -+--+---++ -+-+-++--- --+ | users | MyISAM | Dynamic| 10885 | 68 | 748604 | 4294967295 | 1051648 | 0 | --2147483647-- | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | pack_keys=1| | +---+++---++-+ -+--+---++ -+-+-++--- --+ * dump the table, make sure it doesn't have any uid above =~10k, delete the table and restore the table (and data) from disk: - data is okay, select * from users where uid11k shows ZERO records - insert into users values (NULL,.) results in a new row with uid=2147483647 * several combinations of CHECK TABLE, myisamchk (-r|-o|), dump remore_wrong_record restore, drop the entire database: - data is OKAY, select results in no records above 11k, next insert will have uid=2147483647 The table fits in about 1MB on disk, and its indexes fit in about 700kB. Does anyone have a clue? Any help is highly appreciated. Best of regards, Ricardo Oliveira PS: Sorry for such a long message. -- 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: auto_increment trouble (not the usual check || alter table solution, though)
Partha, On 5/16/05, Partha Dutta [EMAIL PROTECTED] wrote: This may not be a very elegant solution, but you can do this just to get the inserts going again: ALTER TABLE users MODIFY COLUMN uid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT; If you don't have any negative uids, then you will be able to store 2 billion more uids. Although I haven't tried this, I guess it'd possibly work - but I'm trying to have a linear uid column, as it has relations with some other tables (where the linearity of this field is crucial). Nevertheless, I'll try it ASAP and I'll get back to you. Thank you, Ricardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment trouble (not the usual check || alter table solution, though)
Hi, with a similar structure, you can have : mysql select * from users where uid =262140; ++--+ | uid| nickname | ++--+ | 262140 | text | | 262141 | text | | 262142 | text | | 262143 | text | | 262144 | text | ++--+ 5 rows in set (0.00 sec) it's surprising that you can insert NULL in a primary key auto_increment. second, with only 1 values, if the auto_increment reached 2147483647 this means that you have an intensive delete, or the auto_increment had been altered. you can create table toto like users, and insert data again into toto. This will reincremente between 1 and 10xxx. Then rename toto to users. Mathias Selon Ricardo Oliveira [EMAIL PROTECTED]: Hi everyone, First time on the list, although I've been (quietly) a (not-so)long-time quiet observer. This isn't a FAQ and I can assure you I've tried every procedure out there (both on and off-list) to solve it. A couple weeks ago, I started having problems with a MyISAM table which holds records from user profiles. It's a very simple table, with one auto_increment field and a bunch of other field - nothing out of the ordinary. To those of you familiar with slashcode, it's an old version of the users table: CREATE TABLE users ( uid int(11) NOT NULL auto_increment, nickname varchar(20) NOT NULL default '', realemail varchar(50) NOT NULL default '', fakeemail varchar(50) default NULL, homepage varchar(100) default NULL, passwd varchar(12) NOT NULL default '', sig varchar(160) default NULL, seclev int(11) NOT NULL default '0', matchname varchar(20) default NULL, banned int(11) NOT NULL default '0', permmod int(11) NOT NULL default '0', PRIMARY KEY (uid), KEY login (uid,passwd,nickname), KEY chk4user (nickname,realemail), KEY chk4email (realemail) ) TYPE=MyISAM PACK_KEYS=1; We have about 10k lines (more precisely, 10885 lines). Our code inserts data using a insert into users values (NULL, .) statement which hasn't changed over the last few years. Recently, we started seeing the last record with a _way_ high uid value - more precisely, 2147483647 (which keeps new inserts from happening). Obviously this is a problem with the auto_increment mechanism, and we have followed all the (documented) and traditional approaches, which follow: * search the row with the wrong uid and correct it: - upon inserting a new record, its uid will be 2147483647; * correct the uid and modify the auto_increment value from the table: mysql alter table users AUTO_INCREMENT=10900; Query OK, 10885 rows affected (0.33 sec) Records: 10885 Duplicates: 0 Warnings: 0 mysql show table status like 'users'; +---+++---++-+-+--+---++-+-+-++-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++---++-+-+--+---++-+-+-++-+ | users | MyISAM | Dynamic| 10885 | 68 | 748604 | 4294967295 | 1051648 | 0 | --2147483647-- | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | pack_keys=1| | +---+++---++-+-+--+---++-+-+-++-+ * dump the table, make sure it doesn't have any uid above =~10k, delete the table and restore the table (and data) from disk: - data is okay, select * from users where uid11k shows ZERO records - insert into users values (NULL,.) results in a new row with uid=2147483647 * several combinations of CHECK TABLE, myisamchk (-r|-o|), dump remore_wrong_record restore, drop the entire database: - data is OKAY, select results in no records above 11k, next insert will have uid=2147483647 The table fits in about 1MB on disk, and its indexes fit in about 700kB. Does anyone have a clue? Any help is highly appreciated. Best of regards, Ricardo Oliveira PS: Sorry for such a long message. -- 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: auto_increment trouble (not the usual check || alter table solution, though)
Partha, Partha Dutta wrote: This may not be a very elegant solution, but you can do this just to get the inserts going again: ALTER TABLE users MODIFY COLUMN uid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT; If you don't have any negative uids, then you will be able to store 2 billion more uids. Just out of curiosity, have you DROPPED re-created the table? Sorry about the other message (from my lurker, always-silent identity). I have tried DROPping both the table and the whole database to no avail. As I said in the other reply, I'll try your suggestion, but I really need a solution to this problem other than having a huge leap in the uid values. Thanks, Ricardo Oliveira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment insert-delete-insert
Jim McAtee wrote: Say a row is inserted into a table with an auto_increment column and then deleted before another record is inserted. When a new row is inserted, will the value of the auto_increment column be the same as the deleted record's, or will it be one greater? Greater. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment insert-delete-insert
Jim McAtee [EMAIL PROTECTED] wrote on 02/24/2005 04:50:11 PM: Say a row is inserted into a table with an auto_increment column and then deleted before another record is inserted. When a new row is inserted, will the value of the auto_increment column be the same as the deleted record's, or will it be one greater? One greater. Once an auto_increment value is used, it's used. Gaps in these numbers are no big deal as they aren't intended for end-user consumption. They are intended to aid the DBA in uniquely identify each and every row in a table, even if those rows contain identical data in all of their other columns. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: auto_increment not working?
I am not really experienced on this, but i have noticed that simply
truncating an innodb table doesnt reset the autoinc key, u have to redump
the table.
I cant see what you are trying to do here
ALTER TABLE users auto_increment = 590;
set it to start @ 590 ?
Hello,
I am trying to get auto_increment to begin at a certain integer.
CREATE TABLE USERS (
user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username varchar(50),
firstname varchar(50),
middlename varchar(50),
lastname varchar(50),
email varchar(100),
business_name varchar(250),
visible varchar(1) NOT NULL DEFAULT 'T'
) TYPE=InnoDB;
ALTER TABLE users auto_increment = 590;
After executing this above code, and do a insert,
insert into users (username) values ('scott');
I get a user_id of 1 and not 590.
What possibly could I be doing wrong. I have read the fine manual, and
it looks like I am doing the correct thing?
mysql \s
--
mysql Ver 12.21 Distrib 4.0.15, for Win95/Win98 (i32)
Thanks,
Scott K Purcell | Developer | VERTIS |
555 Washington Ave. 4th Floor | St. Louis, MO 63101 |
314.588.0720 Ext:1320 | [EMAIL PROTECTED] |
http://www.vertisinc.com http://www.vertisinc.com/
Vertis is the premier provider of targeted advertising, media, and
marketing services that drive consumers to marketers more effectively.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment not working?
it doesn't work with innodb table.
http://dev.mysql.com/doc/mysql/en/innodb-restrictions.html
InnoDB does not support the AUTO_INCREMENT table option for setting the
initial sequence value in a CREATE TABLE or ALTER TABLE statement. To set
the value with InnoDB, insert a dummy row with a value one less and delete
that dummy row, or insert the first row with an explicit value specified.
HTH!
Hello,
I am trying to get auto_increment to begin at a certain integer.
CREATE TABLE USERS (
user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username varchar(50),
firstname varchar(50),
middlename varchar(50),
lastname varchar(50),
email varchar(100),
business_name varchar(250),
visible varchar(1) NOT NULL DEFAULT 'T'
) TYPE=InnoDB;
ALTER TABLE users auto_increment = 590;
After executing this above code, and do a insert,
insert into users (username) values ('scott');
I get a user_id of 1 and not 590.
What possibly could I be doing wrong. I have read the fine manual, and it
looks like I am doing the correct thing?
mysql \s
--
mysql Ver 12.21 Distrib 4.0.15, for Win95/Win98 (i32)
Thanks,
Scott K Purcell | Developer | VERTIS |
555 Washington Ave. 4th Floor | St. Louis, MO 63101 |
314.588.0720 Ext:1320 | [EMAIL PROTECTED] | http://www.vertisinc.com
http://www.vertisinc.com/
Vertis is the premier provider of targeted advertising, media, and
marketing services that drive consumers to marketers more effectively.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment=0
Hello. Use the NO_AUTO_VALUE_ON_ZERO sql mode. See: http://dev.mysql.com/doc/mysql/en/server-sql-mode.html Philippe Rousselot [EMAIL PROTECTED] wrote: hi, I am migrating a DB having a table with a UID not_null autoincrement the original table starts at UID=0 I cannot migrate this table autmaticaly as the line for UID=0 is automatically transformed into UID=1 and therefore I get an error message for the next line (UID=1) as being already into the table. I thought to be smart first by creating the table with no autoincrement and second altering the table to have UID unique and autoincremet but MySQL does not seem to like that I cannot modify the table to have it starting this UID=1 what can I do ? Thanks in advance Philippe -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT working
Hello. There were several bugs in older versions of MySQL related to 'duplicate entry' errors. Do you use latest release? In documentation it is said that AUTO_INCREMENT works correctly with replication. Please also take a look in the documentation at these links: http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html http://dev.mysql.com/doc/mysql/en/InnoDB_auto-increment_column.html http://dev.mysql.com/doc/mysql/en/Replication_Problems.html Can anybody tell me how AUTO_INCREMENT works in MySQL especially when we are using DB Replications. At times I get duplicate key error though taken care that the insertions and updations are done in the master db. Mitul Bhammar [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT
According to http://dev.mysql.com/doc/mysql/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. It did not specify that the AUTO_INCREMENT column couldn't have a regular index on it. If you have this table: CREATE TABLE users ( ID int auto_increment not null, LOGIN varchar(8) not null, other fields ... Primary Key (Id) ) And you wanted to make LOGIN your PK, just do this: ALTER TABLE users Drop primary key, add key(ID), add primary key(LOGIN); To create an auto_increment column on a new table that isn't a PK, just index it. CREATE TABLE secondsample ( id2 int auto_increment not null, PKField1 varchar(6) not null, PKField2 varchar(24) not null, PKField3 datetime not null, ... other fields ... PRIMARY KEY(PKField1, PKField2, PKField3), KEY(id2) ) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Seena Blace [EMAIL PROTECTED] wrote on 09/16/2004 12:30:29 AM: Hi, Is there any way AUTO_INCREMENT column cann't me Primary key ? One table has one column auto_increment which I want to convert into non primary key but I also want to maintain the column.How to do that? thanks - Do you Yahoo!? vote.yahoo.com - Register online to vote today!
Re: Auto_increment
I hate to dampen your spirits but I think you have made a poor design choice. BEGIN GLOOM and DOOM The word key is a reserved word in MySQL. In order to assign to a database object a name that is either a reserved word or a name that uses a special character, you must surround that with a pair of backticks, ``. Those are not to be confused with a pair of single quotes, ''. Read here for details: http://dev.mysql.com/doc/mysql/en/Reserved_words.html http://dev.mysql.com/doc/mysql/en/Legal_names.html BE VERY WARNED: once you create this table you will ALWAYS need to refer to that column by surrounding its name with backticks. May I suggest you use an alternate spelling (like key_) or an alternate name (like id) for your column to avoid needing backticks? Anyway, if you _must_ keep your current design choice: CREATE TABLE backticktest `key` int auto_increment, data varchar(100) null, PRIMARY KEY(`key`) ) Just to add to my warning, here is a sample select: SELECT `key`,data from backticktest where `key`=15; If you forget those backticks, your query WILL FAIL. END GLOOM and DOOM :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Pahlevanzadeh Mohsen [EMAIL PROTECTED] wrote on 09/10/2004 09:49:00 AM: Dears, I need to define a column within my table.I want to name it key.Also i need to it is auto_increment not null.Also i want to it is primary key. Please tell its statement. Yours,Mohsen = -DIGITAL SIGNATURE--- ///Mohsen Pahlevanzadeh Network administrator programmer My home phone is: +98213810146 My email address is m_pahlevanzadeh at yahoo dot com My website is: http://webnegar.net __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Auto_increment and existing table
If you do an ALTER TABLE table_name MODIFY id INTEGER AUTO_INCREMENT, ADD PRIMARY KEY(id); ... The next record entered should be properly auto_incremented. -Original Message- From: Scott Hamm To: 'Mysql ' (E-mail) Sent: 8/11/04 1:32 PM Subject: Auto_increment and existing table I've imported Access DB into MySQL, and AutoID was lost. The number in ID column exists and I wonder if I update the column from int(10) to auto_increment, will it replace existing number with MySQL's auto_increment number? Or is there a proper way to update from int(10) to auto_increment without replacing existing data that are already there? -- 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: Auto_increment and existing table
Thanks! -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 11, 2004 3:40 PM To: 'Scott Hamm '; ''Mysql ' (E-mail) ' Subject: RE: Auto_increment and existing table If you do an ALTER TABLE table_name MODIFY id INTEGER AUTO_INCREMENT, ADD PRIMARY KEY(id); ... The next record entered should be properly auto_incremented. -Original Message- From: Scott Hamm To: 'Mysql ' (E-mail) Sent: 8/11/04 1:32 PM Subject: Auto_increment and existing table I've imported Access DB into MySQL, and AutoID was lost. The number in ID column exists and I wonder if I update the column from int(10) to auto_increment, will it replace existing number with MySQL's auto_increment number? Or is there a proper way to update from int(10) to auto_increment without replacing existing data that are already there? -- 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: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
So, if I understand you correctly, somewhere in the middle of a 20,000 row insert, a row gets inserted with auto_increment id = 87,123,456, say, then the next row tries to insert with the value 87,123,457 but fails. You fix this by skipping the next value with ALTER TABLE yourtable AUTO_INCREMENT=87123458 After that, the auto_increment id column resumes working as expected, until the next time. Is that right? I can't imagine why that would happen. I have some suggestions: (You may have done some of these already.) First, run a CHECK TABLE on your table. Assuming that indicated no problems, the next time this happens, start by running CHECK TABLE again. Then, do a SHOW TABLE STATUS LIKE 'yourtable' to verify that the next auto_increment value (87,123,457 in my example) is the one that produced the error, then run a SELECT * FROM yourtable WHERE id=87123457 (use the value that produced the error) to verify that there is no row with that id. Then try manually inserting a row to verify you get the same error. Assuming you do, try manually inserting a row with the id explicitly set to 87123457 to see if the problem is with the value as opposed to the auto_increment. Have you tried resetting the auto_increment id by manually inserting a larger number? INSERT INTO yourtable (id) values (87123458); Under normal circumstances, this would cause the auto_increment counter to change to the next value. It would also be a lot quicker than an ALTER TABLE statement. One more thing. When you do a multiple row INSERT statement, LAST_INSERT_ID() returns the auto_increment id of the first row inserted. (See http://dev.mysql.com/doc/mysql/en/Information_functions.html) So, if you've just done a multiple row insert, LAST_INSERT_ID()+1 should already exist as an ID in the table. Michael Kevin Brock wrote: On Jun 19, 2004, at 10:37 AM, Michael Stassen wrote: Something about your description doesn't quite fit, however. You say that you are nowhere near the limit, but you say that resetting the auto_increment starting point fixes the problem. Those seem contradictory to me. To me as well, that's why I posted. What kind of table is it (MyISAM, InnoDB,...)? MyISAM. Have you tried SHOW TABLE STATUS LIKE 'yourtable' Nope. I'm sure we'll be able to reproduce the problem shortly though, and I'll try it then. when this happens? What is the next auto_increment value (in the Auto_increment column) according to the output? Well, since I didn't try it I don't know for sure :-) When I check LAST_INSERT_ID, it's correct. I.e., the ID that fails would be the next ID after LAST_INSERT_ID. Have you looked at the data in the table? Are the values in the auto_increment column consecutive, as expected? What's the max value in that column? The max value is one less than the value that failed, the number of rows is correct, and the values in the column are consecutive. Are you inserting 87 million rows in an empty or existing table? If the latter, how many rows are already there? In either case, is this a large bulk insert that fails before it's done, or are you saying that in normal operations it fails every 87 million or so inserts? The table has been accumulating data for about a month, starting with an empty table. Each insert is on the order of 10-20,000, inserting using INSERT INTO doing multiple rows at a time. How are you resetting the AUTO_INCREMENT starting point, exactly? How do you choose the starting value? What is the value you choose? Do you verify it worked with SHOW TABLE STATUS? Note that if you try to set the next AUTO_INCREMENT to a value less than the largest value currently in the column, the ALTER silently fails. I did an ALTER TABLE to set AUTO_INCREMENT to one greater than the value that failed. We were able to insert more data after that, and the data inserted had the expected values for the ID column... Normally, auto_increment columns do not reuse values, so if you insert then delete a row, that number is still gone. Are you doing just inserts, or are there deletions mixed in? If, for example, you insert 80 million rows a day and delete rows that were inserted yesterday, you'll run out of INT keys in under a month, even though there are only 80 million rows in the table. We hadn't done any deletes on the table. The first ID value is one. Finally, just to cover all the bases, that really is 87 million inserts, not 8.7 million, right? I only ask because a MEDIUMINT column runs out a little past 8.3 million. Right. 87 million rows in the table. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
On Jun 23, 2004, at 8:15 AM, Michael Stassen wrote: So, if I understand you correctly, somewhere in the middle of a 20,000 row insert, a row gets inserted with auto_increment id = 87,123,456, say, then the next row tries to insert with the value 87,123,457 but fails. You fix this by skipping the next value with ALTER TABLE yourtable AUTO_INCREMENT=87123458 After that, the auto_increment id column resumes working as expected, until the next time. Is that right? Yes. I've looked at the table status, and it appears to be OK, and the AUTO_INCREMENT values look good (except as below). Have you tried resetting the auto_increment id by manually inserting a larger number? INSERT INTO yourtable (id) values (87123458); No... I'll give that a try next time round. multiple row INSERT statement, LAST_INSERT_ID() returns the auto_increment id of the first row inserted. (See http://dev.mysql.com/doc/mysql/en/Information_functions.html) So, if you've just done a multiple row insert, LAST_INSERT_ID()+1 should already exist as an ID in the table. I'll have to take a look at that--hadn't thought to look at the effect of the multiple row insert. Thanks. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
On Jun 18, 2004, at 5:31 PM, Scott Haneda wrote: While I do not know why, I would suggest you simply drop the PK and recreate it, this should be a whole lot faster than the alter. This took the same amount of time as the alter table (a little longer actually). The documentation says that in later versions of MySQL the drop index is mapped to alter table, so I'd pretty much expect it to work that way. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
At 13:37 -0400 on 06/19/2004, Michael Stassen wrote about Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a: Finally, just to cover all the bases, that really is 87 million inserts, not 8.7 million, right? I only ask because a MEDIUMINT column runs out a little past 8.3 million. It goes to 16+ Million if you say UNSIGNED. Why lose half the range by allowing the negative numbers that will never be used? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
Robert A. Rosenberg wrote: At 13:37 -0400 on 06/19/2004, Michael Stassen wrote about Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a: Finally, just to cover all the bases, that really is 87 million inserts, not 8.7 million, right? I only ask because a MEDIUMINT column runs out a little past 8.3 million. It goes to 16+ Million if you say UNSIGNED. Why lose half the range by allowing the negative numbers that will never be used? You missed my point. I was not recommending using MEDIUMINT as an auto_increment id, I was attempting to help Kevin figure out why his AUTO_INCREMENT column was giving a duplicate key error. If he was doing an 8.7 million row bulk insert into a table with a MEDIUMINT AUTO_INCREMENT id, then he would get a duplicate key error near the end. I did not think this was the likely explanation, but I wanted to rule it out. Kevin has already confirmed that that is not the problem. In fact, it seems clear that he's getting a duplicate key error long before hitting max int. So while I agree that doubling the range by making the auto_increment column UNSIGNED is a good idea, I think it's clearly not relevant to the problem at hand. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
Just a suggestion, Kevin, but how about changing from INT to BIGINT? Terry --Original Message- We have a table with a primary index which is INT NOT NULL AUTO_INCREMENT. After inserting ~87,000,000 entries, we started seeing error 1062, ER_DUP_ENTRY. We can get going again after doing an ALTER TABLE to reset the auto_increment starting point, but this takes about an hour... I've seen a couple of places where how to get around this problem was discussed, but nobody seems to discuss *why* this occurs in the first place. Does anyone know why MySQL would start failing to increment an auto_increment index properly when it's nowhere near the upper limit? Does anyone know a way to get things functioning again without a couple of hours downtime? Hoping there's an answer out there somewhere... Kevin Brock [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
On Jun 18, 2004, at 5:31 PM, Scott Haneda wrote: on 06/18/2004 05:16 PM, Kevin Brock at [EMAIL PROTECTED] wrote: We have a table with a primary index which is INT NOT NULL AUTO_INCREMENT. After inserting ~87,000,000 entries, we started seeing error 1062, ER_DUP_ENTRY. While I do not know why, I would suggest you simply drop the PK and recreate it, this should be a whole lot faster than the alter. Thanks, I'll give it a try next time the problem shows up. My main worry is that the AUTO_INCREMENT problem is merely a symptom of a more serious problem with the table. I've got a couple of potential band-aids, but I want to find out how it got hurt so we can avoid it in the future... Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
On Jun 19, 2004, at 6:03 AM, Terry Riley wrote: Just a suggestion, Kevin, but how about changing from INT to BIGINT? I thought of trying that, but since we're nowhere near the limit even for an INT I think changing to BIGINT is premature. I want to find out a bit more about what's happening first. The fact that it stops at such a low number makes me think it's not related to the size of the field. I saw a reference in a post (to a different mailing llist) to a tables auto_increment limit, as though that was something different from the max value of an INT, but I haven't run across it in any official documentation. Kevin --Original Message- We have a table with a primary index which is INT NOT NULL AUTO_INCREMENT. After inserting ~87,000,000 entries, we started seeing error 1062, ER_DUP_ENTRY. We can get going again after doing an ALTER TABLE to reset the auto_increment starting point, but this takes about an hour... I've seen a couple of places where how to get around this problem was discussed, but nobody seems to discuss *why* this occurs in the first place. Does anyone know why MySQL would start failing to increment an auto_increment index properly when it's nowhere near the upper limit? Does anyone know a way to get things functioning again without a couple of hours downtime? Hoping there's an answer out there somewhere... Kevin Brock [EMAIL PROTECTED] -- 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: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
To the best of my knowledge, AUTO_INCREMENT columns are limited only by the size of the int, so an INT NOT NULL AUTO_INCREMENT should go to 2,147,483,647. Something about your description doesn't quite fit, however. You say that you are nowhere near the limit, but you say that resetting the auto_increment starting point fixes the problem. Those seem contradictory to me. Perhaps if you explained further, someone might be able to figure out what is happening. To that end, here are some questions: What kind of table is it (MyISAM, InnoDB,...)? Have you tried SHOW TABLE STATUS LIKE 'yourtable' when this happens? What is the next auto_increment value (in the Auto_increment column) according to the output? Have you looked at the data in the table? Are the values in the auto_increment column consecutive, as expected? What's the max value in that column? Are you inserting 87 million rows in an empty or existing table? If the latter, how many rows are already there? In either case, is this a large bulk insert that fails before it's done, or are you saying that in normal operations it fails every 87 million or so inserts? How are you resetting the AUTO_INCREMENT starting point, exactly? How do you choose the starting value? What is the value you choose? Do you verify it worked with SHOW TABLE STATUS? Note that if you try to set the next AUTO_INCREMENT to a value less than the largest value currently in the column, the ALTER silently fails. Normally, auto_increment columns do not reuse values, so if you insert then delete a row, that number is still gone. Are you doing just inserts, or are there deletions mixed in? If, for example, you insert 80 million rows a day and delete rows that were inserted yesterday, you'll run out of INT keys in under a month, even though there are only 80 million rows in the table. Finally, just to cover all the bases, that really is 87 million inserts, not 8.7 million, right? I only ask because a MEDIUMINT column runs out a little past 8.3 million. Michael Kevin Brock wrote: On Jun 19, 2004, at 6:03 AM, Terry Riley wrote: Just a suggestion, Kevin, but how about changing from INT to BIGINT? I thought of trying that, but since we're nowhere near the limit even for an INT I think changing to BIGINT is premature. I want to find out a bit more about what's happening first. The fact that it stops at such a low number makes me think it's not related to the size of the field. I saw a reference in a post (to a different mailing list) to a tables auto_increment limit, as though that was something different from the max value of an INT, but I haven't run across it in any official documentation. Kevin --Original Message- We have a table with a primary index which is INT NOT NULL AUTO_INCREMENT. After inserting ~87,000,000 entries, we started seeing error 1062, ER_DUP_ENTRY. We can get going again after doing an ALTER TABLE to reset the auto_increment starting point, but this takes about an hour... I've seen a couple of places where how to get around this problem was discussed, but nobody seems to discuss *why* this occurs in the first place. Does anyone know why MySQL would start failing to increment an auto_increment index properly when it's nowhere near the upper limit? Does anyone know a way to get things functioning again without a couple of hours downtime? Hoping there's an answer out there somewhere... Kevin Brock [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
On Jun 19, 2004, at 10:37 AM, Michael Stassen wrote: Something about your description doesn't quite fit, however. You say that you are nowhere near the limit, but you say that resetting the auto_increment starting point fixes the problem. Those seem contradictory to me. To me as well, that's why I posted. What kind of table is it (MyISAM, InnoDB,...)? MyISAM. Have you tried SHOW TABLE STATUS LIKE 'yourtable' Nope. I'm sure we'll be able to reproduce the problem shortly though, and I'll try it then. when this happens? What is the next auto_increment value (in the Auto_increment column) according to the output? Well, since I didn't try it I don't know for sure :-) When I check LAST_INSERT_ID, it's correct. I.e., the ID that fails would be the next ID after LAST_INSERT_ID. Have you looked at the data in the table? Are the values in the auto_increment column consecutive, as expected? What's the max value in that column? The max value is one less than the value that failed, the number of rows is correct, and the values in the column are consecutive. Are you inserting 87 million rows in an empty or existing table? If the latter, how many rows are already there? In either case, is this a large bulk insert that fails before it's done, or are you saying that in normal operations it fails every 87 million or so inserts? The table has been accumulating data for about a month, starting with an empty table. Each insert is on the order of 10-20,000, inserting using INSERT INTO doing multiple rows at a time. How are you resetting the AUTO_INCREMENT starting point, exactly? How do you choose the starting value? What is the value you choose? Do you verify it worked with SHOW TABLE STATUS? Note that if you try to set the next AUTO_INCREMENT to a value less than the largest value currently in the column, the ALTER silently fails. I did an ALTER TABLE to set AUTO_INCREMENT to one greater than the value that failed. We were able to insert more data after that, and the data inserted had the expected values for the ID column... Normally, auto_increment columns do not reuse values, so if you insert then delete a row, that number is still gone. Are you doing just inserts, or are there deletions mixed in? If, for example, you insert 80 million rows a day and delete rows that were inserted yesterday, you'll run out of INT keys in under a month, even though there are only 80 million rows in the table. We hadn't done any deletes on the table. The first ID value is one. Finally, just to cover all the bases, that really is 87 million inserts, not 8.7 million, right? I only ask because a MEDIUMINT column runs out a little past 8.3 million. Right. 87 million rows in the table. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
At 17:16 -0700 on 06/18/2004, Kevin Brock wrote about AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one: We have a table with a primary index which is INT NOT NULL AUTO_INCREMENT. After inserting ~87,000,000 entries, we started seeing error 1062, ER_DUP_ENTRY. You are wasting half your range by not saying UNSIGNED. Not your problem (I think) but still an error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
on 06/18/2004 05:16 PM, Kevin Brock at [EMAIL PROTECTED] wrote: We have a table with a primary index which is INT NOT NULL AUTO_INCREMENT. After inserting ~87,000,000 entries, we started seeing error 1062, ER_DUP_ENTRY. We can get going again after doing an ALTER TABLE to reset the auto_increment starting point, but this takes about an hour... I've seen a couple of places where how to get around this problem was discussed, but nobody seems to discuss *why* this occurs in the first place. Does anyone know why MySQL would start failing to increment an auto_increment index properly when it's nowhere near the upper limit? Does anyone know a way to get things functioning again without a couple of hours downtime? Hoping there's an answer out there somewhere... While I do not know why, I would suggest you simply drop the PK and recreate it, this should be a whole lot faster than the alter. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment question
I found the answer to my question by reading the online manual with user comments. Sorry!. Mark - Original Message - From: Mark Fuller [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, May 09, 2004 12:25 AM Subject: auto_increment question I would like to use MEDIUMINT UNSIGNED AUTO_INCREMENT. Over time various rows will be deleted. What happens when auto_increment reaches the maximum value? Will it roll over to 1? And, will it begin filling those unused values from past deletes? Thanks, Mark -- 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: auto_increment id
Stefan Schuster [EMAIL PROTECTED] wrote: Stefan Schuster [EMAIL PROTECTED] wrote: Hi, I have a question about auto_increment: I have 2 tables, on of them holds my online transactions, the other one the offline transactions. Every transaction is created in the first table (call it t1) and then moved to t2. The id is generated using auto_increment. My problem is that the id's should be unique across both tables. If I dont restart the server this works fine, but if I restart it, mySQL assigns the lowest number for the new row in t1, but this id exits already in t2 which results in an error if I try to move from t1 to t2. I hope I explained the problem in a appropriate way. How can I tell mySQL to hold the auto_increment counter for Table t1 persistent ? Do you use InnoDB tables? If so, it's a known behavior. If you restart MySQL server InnoDB may reuse old value for AUTO_INCREMENT column. I need transaction support, as I understand it InnoDB is the only tabletype that supports this. Is there any way to change the behaviour, maybe a table type that has the desired (persistent incrementcounter) behaviour AND supports transactions ? BDB tables are also support transaction, but they are reuse deleted auto_increment values not only if server restarts. For InnoDB tables you can initialize the auto-increment counter by yourself. You should insert into t1 dummy row with max id value from t2 and then remove this row. Something like: INSERT IGNORE INTO db_name.t1(id) SELECT greatest(max(t1.id), max(t2.id)) as id from db_name.t1, db_name.t2; So, with SELECT statement you find greatest value and insert this value into t1 table. If this value is already exists, INSERT statement is ignored. Then you should remove this dummy row: DELETE db_name.t1 FROM db_name.t1, db_name.t2 WHERE db_name.t1.id=db_name.t2.id; Put INSERT and DELETE commands to the file and specify file name with --init-file option of mysqld. MySQL server will read this file at startup: http://www.mysql.com/doc/en/Server_options.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment id
Mrs Reznichenko, About the ID problem, I´d create a single table just for ID´s (and ´d keep it as thin as possible). This would avoid the repeating id numbers. Sincerely Leandro da Rocar. [EMAIL PROTECTED] - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 12, 2004 9:35 AM Subject: Re: auto_increment id Stefan Schuster [EMAIL PROTECTED] wrote: Stefan Schuster [EMAIL PROTECTED] wrote: Hi, I have a question about auto_increment: I have 2 tables, on of them holds my online transactions, the other one the offline transactions. Every transaction is created in the first table (call it t1) and then moved to t2. The id is generated using auto_increment. My problem is that the id's should be unique across both tables. If I dont restart the server this works fine, but if I restart it, mySQL assigns the lowest number for the new row in t1, but this id exits already in t2 which results in an error if I try to move from t1 to t2. I hope I explained the problem in a appropriate way. How can I tell mySQL to hold the auto_increment counter for Table t1 persistent ? Do you use InnoDB tables? If so, it's a known behavior. If you restart MySQL server InnoDB may reuse old value for AUTO_INCREMENT column. I need transaction support, as I understand it InnoDB is the only tabletype that supports this. Is there any way to change the behaviour, maybe a table type that has the desired (persistent incrementcounter) behaviour AND supports transactions ? BDB tables are also support transaction, but they are reuse deleted auto_increment values not only if server restarts. For InnoDB tables you can initialize the auto-increment counter by yourself. You should insert into t1 dummy row with max id value from t2 and then remove this row. Something like: INSERT IGNORE INTO db_name.t1(id) SELECT greatest(max(t1.id), max(t2.id)) as id from db_name.t1, db_name.t2; So, with SELECT statement you find greatest value and insert this value into t1 table. If this value is already exists, INSERT statement is ignored. Then you should remove this dummy row: DELETE db_name.t1 FROM db_name.t1, db_name.t2 WHERE db_name.t1.id=db_name.t2.id; Put INSERT and DELETE commands to the file and specify file name with --init-file option of mysqld. MySQL server will read this file at startup: http://www.mysql.com/doc/en/Server_options.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- 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: auto_increment id
Stefan Schuster [EMAIL PROTECTED] wrote: Hi, I have a question about auto_increment: I have 2 tables, on of them holds my online transactions, the other one the offline transactions. Every transaction is created in the first table (call it t1) and then moved to t2. The id is generated using auto_increment. My problem is that the id's should be unique across both tables. If I dont restart the server this works fine, but if I restart it, mySQL assigns the lowest number for the new row in t1, but this id exits already in t2 which results in an error if I try to move from t1 to t2. I hope I explained the problem in a appropriate way. How can I tell mySQL to hold the auto_increment counter for Table t1 persistent ? Do you use InnoDB tables? If so, it's a known behavior. If you restart MySQL server InnoDB may reuse old value for AUTO_INCREMENT column. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment id
Stefan Schuster [EMAIL PROTECTED] wrote: Hi, I have a question about auto_increment: I have 2 tables, on of them holds my online transactions, the other one the offline transactions. Every transaction is created in the first table (call it t1) and then moved to t2. The id is generated using auto_increment. My problem is that the id's should be unique across both tables. If I dont restart the server this works fine, but if I restart it, mySQL assigns the lowest number for the new row in t1, but this id exits already in t2 which results in an error if I try to move from t1 to t2. I hope I explained the problem in a appropriate way. How can I tell mySQL to hold the auto_increment counter for Table t1 persistent ? Do you use InnoDB tables? If so, it's a known behavior. If you restart MySQL server InnoDB may reuse old value for AUTO_INCREMENT column. I need transaction support, as I understand it InnoDB is the only tabletype that supports this. Is there any way to change the behaviour, maybe a table type that has the desired (persistent incrementcounter) behaviour AND supports transactions ? Thanks, -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- 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: auto_increment pseudo sequence?
When you create the table I think you just set it.. ie- create table blah AUTO_INCREMENT= P -Scott Purcell [EMAIL PROTECTED] wrote: - To: [EMAIL PROTECTED] From: Scott Purcell [EMAIL PROTECTED] Date: 02/09/2004 12:21PM Subject: auto_increment pseudo sequence? Hello, I have an application in which I am using auto_increment as a kind of sequence replacement. The only problem I have is trying to get the auto_increment to start at a larger number than 0. Is auto_increment the replacement for sequences? and if so, is there a way to set the digit to a larger number when creating? Thanks, Scott -- 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: auto_increment pseudo sequence?
alter table AUTO_INCREMENT=x Scott Purcell wrote: Hello, I have an application in which I am using auto_increment as a kind of sequence replacement. The only problem I have is trying to get the auto_increment to start at a larger number than 0. Is auto_increment the replacement for sequences? and if so, is there a way to set the digit to a larger number when creating? Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT in InnoDB
Hassan, - Original Message - From: Hassan Shaikh [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, February 01, 2004 5:01 PM Subject: AUTO_INCREMENT in InnoDB Hi, How do I reset the AUTO_INCREMENT column to some arbitrary number? My table type is InnoDB. drop the table, recreate it, insert a dummy row with the auto-inc column set to the desired value minus 1, and delete that dummy row. Thanks. Hassan Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto_increment question
* Chris W I have two tables with a one to many relationship: boys and their toys. I want to know if there are any advantages or disadvantages for each of the following two ways to create the tables. I am mainly interested in performance. Either way the insert and select queries aren't any more difficult than the other way. CREATE TABLE boy ( BoyID INT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (BoyID) ); CREATE TABLE toy ( BoyID INT NOT NULL, ToyID INT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (BoyID,ToyID) ); This design is without a key for 'toys', the table could have been named 'boystoys' or similar to make that more clear. But that may be okay for you if you are sure this design covers your needs. My immediate instinct says I would need three tables, one for boys, one for toys and one for the ownerships (boystoys). That way multiple boys could own the same toy (sharing a toy, or if that is not relevant, sharing a class of toys, like 'bicycle'), or toys could be sold/bought/traded, such that the owner of the toy changes over time. But this will of course depend on the application. The ToyID will start from 1 for each boy, is this something you need? If yes, this is the way to do it. If no, I would go for the second design. OR #no change here CREATE TABLE boy ( BoyID INT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (BoyID) ); #slight change here CREATE TABLE toy ( ToyID INT NOT NULL AUTO_INCREMENT, BoyID INT NOT NULL, name CHAR(30) NOT NULL, PRIMARY KEY (ToyID) ); This design has a real toy table, the design is easy to extend, for instance with a 'toytype' or 'boystoys' table, but it lacks an index on the BoyID column. This will be slow when you select toys for a specific boy. It can be fixed simply by adding an index on BoyID. And because MySQL can optimize queries where all relevant info is in the index, one can often benefit from combining keys in a single index: ALTER TABLE toy ADD UNIQUE (BoyID,ToyID) If you query like this: SELECT boy.name FROM boy, toy WHERE toy.BoyID = boy.BoyID AND toy.ToyID 100 This would give you the name of boys who own the 100 'oldest' toys (unless you have deleted some toys...), and it would be able to use the combined index, which means it does not need to physically read the toy table at all. In contrast, this statment must also read the physical toy row to check the name of the toy: SELECT boy.name FROM boy, toy WHERE toy.BoyID = boy.BoyID AND toy.name = bicycle This query would have benefited from an index on toy.name: ALTER TABLE toy ADD INDEX (name) or ALTER TABLE toy ADD UNIQUE (BoyID,name) This final index could be used instead of the (BoyID,ToyID) index, if the ToyID 100 type of criteria is not needed, but the boy.name column is. Note that you would probably also need an index with 'name' as the first or the only column, at least if there are many toys and you sometimes need to search for a toy based on the name of the toy. The 'name' column is a CHAR(30) (why not a VARCHAR?), you can index on a substring, for instance: ALTER TABLE toy ADD INDEX (name(10)) This would make an index on only the first ten characters of the toy name. This can be faster in some cases, but I don't know which... You should probably also have an index on boy.name. If you need more specific information, please include some details about your application: what kind of queries, how many simultaneous users, how much data, what kind of hardware, what OS... HTH, -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT Starting over
Dan Muey [EMAIL PROTECTED] wrote: I have a table that I create with=20 CREATE TABLE SuperTest ( ID int(11) NOT NULL AUTO_INCREMENT, Name varchar(64) NOT NULL, Domain varchar(64) NOT NULL, PRIMARY KEY(ID) ); I use that same thing to create a the same table on two servers. On one server (mysql Ver 11.16 Distrib 3.23.49, for portbld-freebsd4.6 = (i386)) If I add, say five records I get ID to be 1,2,3,4,5. Then I delete ID 5 and insert a new record and it's id is 6. So now I = have 1,2,3,4,6. That's what I want it to do. However on the other server (mysql version ???) if I have 1,2,3,4,5, = delete ID 5=20 and insert a new one it makes the new one ID 5. I suppose because it's = the next number. I'm not really even sure what to call it besides 'non reusable = auto_increment'. I can't seem to find anything on mysql.com about what settings, version, = etc cause it to work either way. It depends on table types. AUTO_INCREMENT values are reused for BDB and ISAM tables and not reused for MyISAM and InnoDB tables. Any insight would be greatly appreciated as in some situations I really = need it to be one way or the other consistently across servers. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: AUTO_INCREMENT Starting over
Dan Muey [EMAIL PROTECTED] wrote: I have a table that I create with=20 CREATE TABLE SuperTest ( ID int(11) NOT NULL AUTO_INCREMENT, Name varchar(64) NOT NULL, Domain varchar(64) NOT NULL, PRIMARY KEY(ID) ); I use that same thing to create a the same table on two servers. On one server (mysql Ver 11.16 Distrib 3.23.49, for portbld-freebsd4.6 = (i386)) If I add, say five records I get ID to be 1,2,3,4,5. Then I delete ID 5 and insert a new record and it's id is 6. So now I = have 1,2,3,4,6. That's what I want it to do. However on the other server (mysql version ???) if I have 1,2,3,4,5, = delete ID 5=20 and insert a new one it makes the new one ID 5. I suppose because it's = the next number. I'm not really even sure what to call it besides 'non reusable = auto_increment'. I can't seem to find anything on mysql.com about what settings, version, = etc cause it to work either way. It depends on table types. AUTO_INCREMENT values are reused for BDB and ISAM tables and not reused for MyISAM and InnoDB tables. Oh cool! So if I want to reuse them and know 100% they will be reused I need to specify the type as BDB or ISAM. And if I want to not reuse them and know 100% for sure they won't be reused I need to dpescify MyISAM or InnoDB. That's were my problem lay I believe, I never specified the table type and just let it happen as it felt like. Lesson learned! Thanks a million! DMuey Any insight would be greatly appreciated as in some situations I really = need it to be one way or the other consistently across servers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT Starting over
Dan Muey [EMAIL PROTECTED] wrote: Dan Muey [EMAIL PROTECTED] wrote: I have a table that I create with=3D20 CREATE TABLE SuperTest ( ID int(11) NOT NULL AUTO_INCREMENT, Name varchar(64) NOT NULL, Domain varchar(64) NOT NULL, PRIMARY KEY(ID) ); I use that same thing to create a the same table on two servers. On one server (mysql Ver 11.16 Distrib 3.23.49, for=20 portbld-freebsd4.6 =3D (i386)) If I add, say five records I get ID to be 1,2,3,4,5. Then I delete ID 5 and insert a new record and it's id is=20 6. So now I =3D have 1,2,3,4,6. That's what I want it to do. However on the other server (mysql version ???) if I have=20 1,2,3,4,5, =3D delete ID 5=3D20 and insert a new one it makes the new one ID 5. I suppose=20 because it's =3D the next number. =20 I'm not really even sure what to call it besides 'non reusable =3D=20 auto_increment'. =20 I can't seem to find anything on mysql.com about what settings,=20 version, =3D etc cause it to work either way. =20 It depends on table types. AUTO_INCREMENT values are reused=20 for BDB and ISAM tables and not reused for MyISAM and InnoDB tables. Oh cool! So if I want to reuse them and know 100% they will be reused I=20 need to specify the type as BDB or ISAM. And if I want to not reuse them and know 100% for sure they won't=20 be reused I need to dpescify MyISAM or InnoDB. Each of table type has it's own advantages and restrictions. They are different not only in usage of AUTO_INCREMENT value. Read more about each table type before choosing table type: http://www.mysql.com/doc/en/Table_types.html That's were my problem lay I believe, I never specified the table=20 type and just let it happen as it felt like. Lesson learned! Thanks a million! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: AUTO_INCREMENT Starting over
Dan Muey [EMAIL PROTECTED] wrote: Dan Muey [EMAIL PROTECTED] wrote: I have a table that I create with=3D20 CREATE TABLE SuperTest ( ID int(11) NOT NULL AUTO_INCREMENT, Name varchar(64) NOT NULL, Domain varchar(64) NOT NULL, PRIMARY KEY(ID) ); I use that same thing to create a the same table on two servers. On one server (mysql Ver 11.16 Distrib 3.23.49, for=20 portbld-freebsd4.6 =3D (i386)) If I add, say five records I get ID to be 1,2,3,4,5. Then I delete ID 5 and insert a new record and it's id is=20 6. So now I =3D have 1,2,3,4,6. That's what I want it to do. However on the other server (mysql version ???) if I have=20 1,2,3,4,5, =3D delete ID 5=3D20 and insert a new one it makes the new one ID 5. I suppose=20 because it's =3D the next number. =20 I'm not really even sure what to call it besides 'non reusable =3D=20 auto_increment'. =20 I can't seem to find anything on mysql.com about what settings,=20 version, =3D etc cause it to work either way. =20 It depends on table types. AUTO_INCREMENT values are reused=20 for BDB and ISAM tables and not reused for MyISAM and InnoDB tables. Oh cool! So if I want to reuse them and know 100% they will be reused I=20 need to specify the type as BDB or ISAM. And if I want to not reuse them and know 100% for sure they won't=20 be reused I need to dpescify MyISAM or InnoDB. Each of table type has it's own advantages and restrictions. They are different not only in usage of AUTO_INCREMENT value. Read more about each table type before choosing table type: http://www.mysql.com/doc/en/Table_types.html Thanks, I sure will. I appreciate your time! Dan That's were my problem lay I believe, I never specified the table=20 type and just let it happen as it felt like. Lesson learned! Thanks a million! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment with FOREIGN KEY UPDATE CASCADE courses Lost connection to MySQL server
vinita vigine MURUGIAH [EMAIL PROTECTED] wrote: Hello, I'm using ver 4.0.12, checked for bugs in ver 4.0.12(http://bugs.mysql.com/search.php) but couldn't find this one. Thanks for report, but I wasn't able to repeat Lost connection error on v4.0.16. Many bugs were fixed since that time. Upgrade MySQL server to the 4.0.16. [skip] CREATE TABLE software ( softwareID CHAR(20) NOT NULL, softwareName CHAR(100), softwareVers CHAR(20), installedDate DATE, softwareSource BLOB, softwareNote BLOB, localMods BLOB, PRIMARY KEY (softwareID) ) TYPE=INNODB; CREATE TABLE software_machineOSs ( softwareID CHAR(20) NOT NULL, id INT(2) unsigned zerofill NOT NULL auto_increment, osName CHAR(20), osRevision CHAR(20), INDEX (softwareID), FOREIGN KEY (softwareID) REFERENCES software (softwareID) ON DELETE CASCADE ON UPDATE CASCADE, KEY(id), PRIMARY KEY (softwareID, id) ) TYPE=INNODB; insert into software (softwareID,softwareName,softwareVers,installedDate,softwareSource) values (vim-1.1, vim, 1.1, NOW(), www.test.com.au); insert into software_machineOSs (softwareID,osName,osRevision) values (vim-1.1, sun, 8); mysql select * from software; ysql select * from software; ++--+--+---+-+--+---+ | softwareID | softwareName | softwareVers | installedDate | softwareSource | softwareNote | localMods | ++--+--+---+-+--+---+ | vim-1.1| vim | 1.1 | 2003-10-28| www.test.com.au | NULL | NULL | ++--+--+---+-+--+---+ 1 row in set (0.00 sec) mysql select * from software_machineOSs; +++++ | softwareID | id | osName | osRevision | +++++ | vim-1.1| 01 | sun| 8 | +++++ 1 row in set (0.00 sec) mysql update software set softwareID=vi-3 where softwareID=vim-1.1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from software; ++--+--+---+-+--+---+ | softwareID | softwareName | softwareVers | installedDate | softwareSource | softwareNote | localMods | ++--+--+---+-+--+---+ | vi-3 | vim | 1.1 | 2003-10-28| www.test.com.au | NULL | NULL | ++--+--+---+-+--+---+ 1 row in set (0.00 sec) mysql select * from software_machineOSs; ERROR 2013: Lost connection to MySQL server during query mysql select * from software_machineOSs; ERROR 2006: MySQL server has gone away No connection. Trying to reconnect... Connection id:1 Current database: dept_db ERROR 2013: Lost connection to MySQL server during query -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment with FOREIGN KEY UPDATE CASCADE courses Lost connection to MySQL server
Vinita, it is most probably this bug fixed in 4.1.14: Fixed a bug: if in a FOREIGN KEY with an UPDATE CASCADE clause the parent column was of a different internal storage length than the child column, then a cascaded update would make the column length wrong in the child table and corrupt the child table. Because of MySQL's 'silent column specification changes' a fixed-length CHAR column can change internally to a VARCHAR and cause this error. Below a test run with the latest 4.0 tree. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables [EMAIL PROTECTED]:~/mysql-4.0/client mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.17-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE TABLE software ( - softwareID CHAR(20) NOT NULL, - softwareName CHAR(100), - softwareVers CHAR(20), - installedDate DATE, - softwareSource BLOB, - softwareNote BLOB, - localMods BLOB, - PRIMARY KEY (softwareID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.04 sec) mysql mysql CREATE TABLE software_machineOSs ( - softwareID CHAR(20) NOT NULL, - id INT(2) unsigned zerofill NOT NULL auto_increment, - osName CHAR(20), - osRevision CHAR(20), - INDEX (softwareID), - FOREIGN KEY (softwareID) REFERENCES software (softwareID) ON - DELETE CASCADE ON UPDATE CASCADE, - KEY(id), - PRIMARY KEY (softwareID, id) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql select * from software; Empty set (0.03 sec) mysql insert into software - (softwareID,softwareName,softwareVers,installedDate,softwareSource) - values (vim-1.1, vim, 1.1, NOW(), - www.test.com.au); Query OK, 1 row affected (0.01 sec) mysql mysql insert into software_machineOSs (softwareID,osName,osRevision) values - (vim-1.1, sun, 8); Query OK, 1 row affected (0.00 sec) mysql mysql select * from software; ++--+--+---+ -+-- +---+ | softwareID | softwareName | softwareVers | installedDate | softwareSource | s oftwareNote | localMods | ++--+--+---+ -+-- +---+ | vim-1.1| vim | 1.1 | 2003-10-28| www.test.com.au | N ULL | NULL | ++--+--+---+ -+-- +---+ 1 row in set (0.00 sec) mysql select * from software_machineOSs; +++++ | softwareID | id | osName | osRevision | +++++ | vim-1.1| 01 | sun| 8 | +++++ 1 row in set (0.00 sec) mysql update software set softwareID=vi-3 where - softwareID=vim-1.1; Query OK, 1 row affected (1 min 14.30 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from software; ++--+--+---+ -+-- +---+ | softwareID | softwareName | softwareVers | installedDate | softwareSource | s oftwareNote | localMods | ++--+--+---+ -+-- +---+ | vi-3 | vim | 1.1 | 2003-10-28| www.test.com.au | N ULL | NULL | ++--+--+---+ -+-- +---+ 1 row in set (0.00 sec) mysql select * from software_machineOSs; +++++ | softwareID | id | osName | osRevision | +++++ | vi-3 | 01 | sun| 8 | +++++ 1 row in set (0.00 sec) mysql check table software; +---+---+--+--+ | Table | Op| Msg_type | Msg_text | +---+---+--+--+ | test.software | check | status | OK | +---+---+--+--+ 1 row in set (0.01 sec) mysql check table software_machineOSs; +--+---+--+--+ | Table| Op| Msg_type | Msg_text | +--+---+--+--+ | test.software_machineOSs | check | status | OK | +--+---+--+--+ 1 row in set (0.01 sec) mysql show create table software; +--+
Re: auto_increment fields
On Wed, 27 Aug 2003 09:05:17 -0300 bernardaum [EMAIL PROTECTED] wrote: Hi, I have a table with an auto_increment field. When I delete all the record and insert a new one the auto_increment field is not clean, its follows the sequence. Can I restart this sequence? Start from 0 again? yes, use TRUNCATE TABLE table; to restart sequence. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment fields
On Wednesday 27 August 2003 1:05 pm, bernardaum wrote: Hi, I have a table with an auto_increment field. When I delete all the record and insert a new one the auto_increment field is not clean, its follows the sequence. Can I restart this sequence? Start from 0 again? When I need to do this, I just drop and re-create the table. simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment fields
On Wed, 27 Aug 2003 13:27:25 +0100 Simon [EMAIL PROTECTED] wrote: On Wednesday 27 August 2003 1:05 pm, bernardaum wrote: Hi, I have a table with an auto_increment field. When I delete all the record and insert a new one the auto_increment field is not clean, its follows the sequence. Can I restart this sequence? Start from 0 again? When I need to do this, I just drop and re-create the table. http://www.mysql.com/doc/en/TRUNCATE.html Truncate operations drop and re-create the table, which is much faster than deleting rows one by one. (c) I suppose TRUNCATE is more efficient way to get the same result =) --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment fields
At 9:05 -0300 8/27/03, bernardaum wrote: Hi, I have a table with an auto_increment field. When I delete all the record and insert a new one the auto_increment field is not clean, its follows the sequence. Can I restart this sequence? Start from 0 again? Why bother? MySQL doesn't care if there are gaps in the sequence. And if you're using the ID to relate records in the table to records in another table, you'll destroy the correspondence. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment fields
On Wed, 27 Aug 2003 09:38:16 -0400 Paul DuBois [EMAIL PROTECTED] wrote: At 9:05 -0300 8/27/03, bernardaum wrote: Hi, I have a table with an auto_increment field. When I delete all the record and insert a new one the auto_increment field is not clean, its follows the sequence. Can I restart this sequence? Start from 0 again? Why bother? MySQL doesn't care if there are gaps in the sequence. And if you're using the ID to relate records in the table to records in another table, you'll destroy the correspondence. imho, he's talking about resetting auto_increment to 0, not about gaps in ID: When I -- delete all -- the records ... --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
