Fw: Inserting a value in an autoincrement list?
mysql@lists.mysql.com - Original Message - From: Sebastian Mendel <[EMAIL PROTECTED]> To: Richard <[EMAIL PROTECTED]> Cc: Sent: Wednesday, October 31, 2007 3:11 PM Subject: Re: Inserting a value in an autoincrement list? > Richard schrieb: > > I want to have a list of titles to which I can add new ones either at > > the end of the list or in a chosen position so I can choose what order > > they show up in. > > > > The first solution that I thought of was to add one to all of the > > position values heigher than the position I wish to insert the new one > > to. But this would envolve alot of queries and therefore resources. > > > > So now I'm wandering if it is possible to do this directly with mysql. > > Here is an example of what I want to do > > I will have a table called titles like this : > > > > > > table : title > > --- > >POSTITION| TITLE > >1| title joejjeo > >2| title ejuejej > >3| title ekkke > >4| title eueoueo > >5|title eehiehiehop > > > > > > And I wish to insert : > > > >3|title inserted > > > > So that titles in positions 3, 4 and 5 become titles 4, 5 and 6 > > > just two queries: > > UPDATE `title` > SET `POSTITION` = `POSTITION` + 1 > WHERE `POSTITION` > 2; > > INSERT ...; > > -- > Sebastian > > -- > 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: Inserting a value in an autoincrement list?
Richard schrieb: > I want to have a list of titles to which I can add new ones either at > the end of the list or in a chosen position so I can choose what order > they show up in. > > The first solution that I thought of was to add one to all of the > position values heigher than the position I wish to insert the new one > to. But this would envolve alot of queries and therefore resources. > > So now I'm wandering if it is possible to do this directly with mysql. > Here is an example of what I want to do > I will have a table called titles like this : > > > table : title > --- >POSTITION| TITLE >1| title joejjeo >2| title ejuejej >3| title ekkke >4| title eueoueo >5|title eehiehiehop > > > And I wish to insert : > >3|title inserted > > So that titles in positions 3, 4 and 5 become titles 4, 5 and 6 just two queries: UPDATE `title` SET `POSTITION` = `POSTITION` + 1 WHERE `POSTITION` > 2; INSERT ...; -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inserting a value in an autoincrement list?
Hello, I'm programming with PHP+Mysql a CMS system to easily update my site. I want to have a list of titles to which I can add new ones either at the end of the list or in a chosen position so I can choose what order they show up in. The first solution that I thought of was to add one to all of the position values heigher than the position I wish to insert the new one to. But this would envolve alot of queries and therefore resources. So now I'm wandering if it is possible to do this directly with mysql. Here is an example of what I want to do I will have a table called titles like this : table : title --- POSTITION| TITLE 1| title joejjeo 2| title ejuejej 3| title ekkke 4| title eueoueo 5|title eehiehiehop And I wish to insert : 3|title inserted So that titles in positions 3, 4 and 5 become titles 4, 5 and 6 So I would have : table : title --- POSTITION| TITLE 1| title 1 2| title 2 3|title inserted 4| title 3 5| title 4 6|title 5 Is there an easy way to do this with mysql ? and if not what would be the best way to achieve this? Thanks :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding a new autoincrement field to an existing table
Grant Griffith wrote: I am trying to add an autoincrement field to a table that already exists and I keep receiving errors when trying to do it. Can someone point me in the right direction on how I can do this? I have access via Webadmin and phpMyAdmin, so I can try it however I need to. ALTER TABLE [table] ADD [fieldname] [integer type] not null auto_increment key; or if you want it to be the primary key: ALTER TABLE [table] ADD [fieldname] [integer type] not null auto_increment primary key; Keep in mind that you can have only one auto_increment column and it must be part of a key. regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help trying to add an autoincrement col to an exisiting table
You are better off doing the following DROP TABLE IF EXISTS users_new; CREATE TABLE users_new ( uname varchar(20) NOT NULL default '', passwd varchar(15) NOT NULL default '', fname varchar(25) NOT NULL default '', lname varchar(40) NOT NULL default '', dir varchar(28) NOT NULL default '', pict varchar(50) NOT NULL default '', level int(4) NOT NULL default '0', email varchar(40) NOT NULL default '', rank int(4) NOT NULL default '0', dgroup int(4) NOT NULL default '0', parent varchar(20) NOT NULL default '', seminar int(11) NOT NULL default '0', getnewsletter int(11) default '0', id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), KEY uname_users (uname), KEY dir_users (dir), KEY seminar_users (seminar), KEY user_lvl_idx (level) ); INSERT INTO users_new (uname,passwd,fname,lname,dir,pict,level,email,rank,dgroup,parent,seminar,getnewsletter) SELECT uname,passwd,fname,lname,dir,pict,level,email,rank,dgroup,parent,seminar,getnewsletter FROM users; DROP TABLE users; ALTER TABLE users_new RENAME TO users; That's it. You may want to create the table as mentioned before. However, if you prefer the 'uname' as the primary key, then create the table like this instead: CREATE TABLE users_new ( uname varchar(20) NOT NULL default '', passwd varchar(15) NOT NULL default '', fname varchar(25) NOT NULL default '', lname varchar(40) NOT NULL default '', dir varchar(28) NOT NULL default '', pict varchar(50) NOT NULL default '', level int(4) NOT NULL default '0', email varchar(40) NOT NULL default '', rank int(4) NOT NULL default '0', dgroup int(4) NOT NULL default '0', parent varchar(20) NOT NULL default '', seminar int(11) NOT NULL default '0', getnewsletter int(11) default '0', id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (uname), UNIQUE KEY id (id), KEY dir_users (dir), KEY seminar_users (seminar), KEY user_lvl_idx (level) ); - Original Message - From: Randy Paries <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 9:23:55 PM GMT-0500 US/Eastern Subject: help trying to add an autoincrement col to an exisiting table Hello, currently i have the following table structure CREATE TABLE users ( uname varchar(20) NOT NULL default '', passwd varchar(15) NOT NULL default '', fname varchar(25) NOT NULL default '', lname varchar(40) NOT NULL default '', dir varchar(28) NOT NULL default '', pict varchar(50) NOT NULL default '', level int(4) NOT NULL default '0', email varchar(40) NOT NULL default '', rank int(4) NOT NULL default '0', dgroup int(4) NOT NULL default '0', parent varchar(20) NOT NULL default '', seminar int(11) NOT NULL default '0', getnewsletter int(11) default '0', PRIMARY KEY (uname), KEY uname_users (uname), KEY dir_users (dir), KEY seminar_users (seminar), KEY user_lvl_idx (level) ) TYPE=MyISAM; I want to add an autoincrement field when i first tried i got the error Incorrect table definition; there can be only one auto column and it must be defined as a key so then i tried ALTER TABLE `users` DROP PRIMARY KEY; ALTER TABLE `users` ADD PRIMARY KEY (id); ALTER TABLE `users` CHANGE `id` `keyid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT; and i get the error Duplicate entry '0' for key 1 Can some one please tell me what i am doing wrong Thanks -- 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]
help trying to add an autoincrement col to an exisiting table
Hello, currently i have the following table structure CREATE TABLE users ( uname varchar(20) NOT NULL default '', passwd varchar(15) NOT NULL default '', fname varchar(25) NOT NULL default '', lname varchar(40) NOT NULL default '', dir varchar(28) NOT NULL default '', pict varchar(50) NOT NULL default '', level int(4) NOT NULL default '0', email varchar(40) NOT NULL default '', rank int(4) NOT NULL default '0', dgroup int(4) NOT NULL default '0', parent varchar(20) NOT NULL default '', seminar int(11) NOT NULL default '0', getnewsletter int(11) default '0', PRIMARY KEY (uname), KEY uname_users (uname), KEY dir_users (dir), KEY seminar_users (seminar), KEY user_lvl_idx (level) ) TYPE=MyISAM; I want to add an autoincrement field when i first tried i got the error Incorrect table definition; there can be only one auto column and it must be defined as a key so then i tried ALTER TABLE `users` DROP PRIMARY KEY; ALTER TABLE `users` ADD PRIMARY KEY (id); ALTER TABLE `users` CHANGE `id` `keyid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT; and i get the error Duplicate entry '0' for key 1 Can some one please tell me what i am doing wrong Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Find Most Recent Autoincrement Index Assigned???
- Original Message - From: "David T. Ashley" <[EMAIL PROTECTED]> To: Sent: Saturday, April 22, 2006 12:53 AM Subject: How to Find Most Recent Autoincrement Index Assigned??? I'm using PHP, and I sometimes INSERT new records in a table. MySQL assigns a new autoincrement int field on each INSERT ... nothing surprising there. It goes 1, 2, 3, etc. What query can I use to find out what value this int autoincrement assigned field was? I could of course SELECT based on what was just inserted, but that seems inefficient. Thanks for any help, RTFM? If you search the MySQL manual on "increment", you'll get several hits, one of which is "3.6.9 Using AUTO_INCREMENT". Here is the link: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html If you read that page, you should find your answer -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Find Most Recent Autoincrement Index Assigned???
Hi, Actually i update(increment by 1 ) a value in a table with only one row, ie. update table1 set col = col+1; Can i get the updated value without diong select in the same query with update. Because if i run Select after that than some time is lost and in the mean time any other process updates the table and i do not get the exact value. Thanks, Abhishek Jain On 4/22/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > From 'Section 13.5.3 SET Syntax' of the 5.0.18 ref manual: > > IDENTITY = value > > The variable is a synonym for the LAST_INSERT_ID variable. > It exists for compatibility with other database systems. You > can read its value with SELECT @@IDENTITY, and set it using > SET IDENTITY. > > > INSERT_ID = value > > Set the value to be used by the following INSERT or ALTER > TABLE statement when inserting an AUTO_INCREMENT value. This > is mainly used with the binary log. > > > LAST_INSERT_ID = value > > Set the value to be returned from LAST_INSERT_ID(). This is > stored in the binary log when you use LAST_INSERT_ID() in a > statement that updates a table. Setting this variable does > not update the value returned by the mysql_insert_id() C API > function. > > Eg. > > mysql> select @@last_insert_id; > +--+ > | @@last_insert_id | > +--+ > |0 | > +--+ > 1 row in set (0.04 sec) > > mysql> select @@insert_id; > +-+ > | @@insert_id | > +-+ > | 0 | > +-+ > 1 row in set (0.00 sec) > > mysql> select @@identity; > ++ > | @@identity | > ++ > | 0 | > ++ > 1 row in set (0.00 sec) > > mysql> set @@last_insert_id = 5; > Query OK, 0 rows affected (0.00 sec) > > mysql> select @@last_insert_id; > +--+ > | @@last_insert_id | > +--+ > |5 | > +--+ > 1 row in set (0.00 sec) > > mysql> select @@insert_id; > +-+ > | @@insert_id | > +-+ > | 5 | > +-+ > 1 row in set (0.00 sec) > > mysql> select @@identity; > ++ > | @@identity | > ++ > | 5 | > ++ > 1 row in set (0.00 sec) > > So it appears you can use either of the three variables > above to achieve the same effect. > > Regards > > Keith > > On Sat, 22 Apr 2006, Michael Stassen wrote: > > > To: David T. Ashley <[EMAIL PROTECTED]> > > From: Michael Stassen <[EMAIL PROTECTED] > > > Subject: Re: How to Find Most Recent Autoincrement Index Assigned??? > > > > David T. Ashley wrote: > > > I'm using PHP, and I sometimes INSERT new records in a table. MySQL > > > assigns > > > a new autoincrement int field on each INSERT ... nothing surprising > > > there. > > > It goes 1, 2, 3, etc. > > > > > > What query can I use to find out what value this int autoincrement > > > assigned > > > field was? I could of course SELECT based on what was just inserted, > > > but > > > that seems inefficient. > > > > > > Thanks for any help, > > > Dave. > > > > LAST_INSERT_ID() > > > > <http://dev.mysql.com/doc/refman/4.1/en/information-functions.html> > > > > Michael > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > >
Re: How to Find Most Recent Autoincrement Index Assigned???
>From 'Section 13.5.3 SET Syntax' of the 5.0.18 ref manual: IDENTITY = value The variable is a synonym for the LAST_INSERT_ID variable. It exists for compatibility with other database systems. You can read its value with SELECT @@IDENTITY, and set it using SET IDENTITY. INSERT_ID = value Set the value to be used by the following INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT value. This is mainly used with the binary log. LAST_INSERT_ID = value Set the value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update the value returned by the mysql_insert_id() C API function. Eg. mysql> select @@last_insert_id; +--+ | @@last_insert_id | +--+ |0 | +--+ 1 row in set (0.04 sec) mysql> select @@insert_id; +-+ | @@insert_id | +-+ | 0 | +-+ 1 row in set (0.00 sec) mysql> select @@identity; ++ | @@identity | ++ | 0 | ++ 1 row in set (0.00 sec) mysql> set @@last_insert_id = 5; Query OK, 0 rows affected (0.00 sec) mysql> select @@last_insert_id; +--+ | @@last_insert_id | +--+ |5 | +--+ 1 row in set (0.00 sec) mysql> select @@insert_id; +-+ | @@insert_id | +-+ | 5 | +-+ 1 row in set (0.00 sec) mysql> select @@identity; ++ | @@identity | ++ | 5 | ++ 1 row in set (0.00 sec) So it appears you can use either of the three variables above to achieve the same effect. Regards Keith On Sat, 22 Apr 2006, Michael Stassen wrote: > To: David T. Ashley <[EMAIL PROTECTED]> > From: Michael Stassen <[EMAIL PROTECTED]> > Subject: Re: How to Find Most Recent Autoincrement Index Assigned??? > > David T. Ashley wrote: > > I'm using PHP, and I sometimes INSERT new records in a table. MySQL > > assigns > > a new autoincrement int field on each INSERT ... nothing surprising > > there. > > It goes 1, 2, 3, etc. > > > > What query can I use to find out what value this int autoincrement > > assigned > > field was? I could of course SELECT based on what was just inserted, > > but > > that seems inefficient. > > > > Thanks for any help, > > Dave. > > LAST_INSERT_ID() > > <http://dev.mysql.com/doc/refman/4.1/en/information-functions.html> > > Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Find Most Recent Autoincrement Index Assigned???
David T. Ashley wrote: I'm using PHP, and I sometimes INSERT new records in a table. MySQL assigns a new autoincrement int field on each INSERT ... nothing surprising there. It goes 1, 2, 3, etc. What query can I use to find out what value this int autoincrement assigned field was? I could of course SELECT based on what was just inserted, but that seems inefficient. Thanks for any help, Dave. LAST_INSERT_ID() <http://dev.mysql.com/doc/refman/4.1/en/information-functions.html> Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to Find Most Recent Autoincrement Index Assigned???
I'm using PHP, and I sometimes INSERT new records in a table. MySQL assigns a new autoincrement int field on each INSERT ... nothing surprising there. It goes 1, 2, 3, etc. What query can I use to find out what value this int autoincrement assigned field was? I could of course SELECT based on what was just inserted, but that seems inefficient. Thanks for any help, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about autoincrement ID
On Thu, 23 Mar 2006 [EMAIL PROTECTED] wrote: > > To: saf <[EMAIL PROTECTED]> > From: [EMAIL PROTECTED] > Subject: Re: Question about autoincrement ID > > One important thing to remember: You should not let UI > design requirements dictate your DB design. Most > developers who design the database just to support the > front end up regretting the decision. Those designs are > either impossible to extend or impossible to manage or > both. You should always design for an efficient database > and adjust your retrieval methods to present the data in > the manner requested, not the other way around. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine IMHO I think the database is the central core of a DB driven website. Therefore it should be the first thing designed in a DB driven website. Everything else in a DB driven site should then be built around the expected functionality of the database. So, if one starts out by designing a database (and it's server(s)) with optimum performance and upgradability as design goals, you won't go to far wrong. Just my 2c. Keith Roberts In theory, theory and practice are the same; in practice they are not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about autoincrement ID
[EMAIL PROTECTED] (saf) wrote on 03/23/2006 11:10:04 AM: > On Thu, Mar 23, 2006 at 11:04:55AM -0500, [EMAIL PROTECTED] wrote: > > [EMAIL PROTECTED] (saf) wrote on 03/23/2006 10:50:10 AM: > > > > The short answer is "no". The Record #2 already existed. It's current > > status is "deleted". If you had other tables that linked their data to > > record #2 and you created a new #2 to replace the one you already deleted > > then you could possibly be making a "bad" match between the old data and > > the new data. > > > > For the sake of data consistency and for all of the other good reasons to > > have a relational database, once an auto_increment value has been issued > > it's considered used and no other record should ever have that number. > > Only if you completely reset your table (see the command "TRUNCATE TABLE") > > could it be possibly safe to begin re-issuing the smaller numbers. Again, > > it's only possible if all of the child records that used to point to the > > old data were also deleted. > > > > Do not rely on the auto_increment value for record sequencing. If you need > > your records serialized in some sequential way, you will need to code the > > support for those sequential numbers in your application. > > So I must do a big SELECT and then check my self every time (for each INSERT), > which IDs are free? > Hmm if the table has more than 100 000 entries, this will slow down my system. > Specialitty because the check function would be written in PHP. > > -- > Best regards, > saf > http://www.trashmail.net/ > No, you should quit trying to tell the database how it should implement auto_increment. If you don't want a monotonically increasing integer value to be automatically generated for each new record (or attempted new record) then simply don't use auto_increment. At that point you can make your ID values anything you want because you are going to be completely in charge of creating them. There are dozens of great reasons why the database has an auto_increment function built into it. There are probably as many reasons why doing what you propose to do is normally considered "very bad practice". What's the real reason you don't want to let auto_increment do its automatic numbering? Many of us on the list manage databases with millions or billions of rows in our tables and we DO NOT even attempt to "fill in the gaps" as you propose to do. There is just no good reason to do it, and several good reasons to NOT do it. One important thing to remember: You should not let UI design requirements dictate your DB design. Most developers who design the database just to support the front end up regretting the decision. Those designs are either impossible to extend or impossible to manage or both. You should always design for an efficient database and adjust your retrieval methods to present the data in the manner requested, not the other way around. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Question about autoincrement ID
>So I must do a big SELECT and then check my self every time (for each INSERT), >which IDs are free? No, you just ignore "deleted" IDs. What's the point? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about autoincrement ID
On Thu, Mar 23, 2006 at 04:17:44PM +, [EMAIL PROTECTED] wrote: > Lots of ways round this. Instead of deleting records, add a boolean > "deleted" flag. All selects then need to add "and deleted = 0". But you can > find a (random) deleted row with "select id from table where deleted = 1 > limit 1". If this returns a result, use update to re-populate that record, > clearing the deleted flag. If it returns nothing, use insert to create a > new record. Great idea!! Many thanks!!! -- Best regards, saf http://Trashmail.net/ signature.asc Description: Digital signature
Re: Question about autoincrement ID
[EMAIL PROTECTED] (saf) wrote on 23/03/2006 16:10:04: > On Thu, Mar 23, 2006 at 11:04:55AM -0500, [EMAIL PROTECTED] wrote: > > [EMAIL PROTECTED] (saf) wrote on 03/23/2006 10:50:10 AM: > > > > The short answer is "no". The Record #2 already existed. It's current > > status is "deleted". If you had other tables that linked their data to > > record #2 and you created a new #2 to replace the one you already deleted > > then you could possibly be making a "bad" match between the old data and > > the new data. > > > > For the sake of data consistency and for all of the other good reasons to > > have a relational database, once an auto_increment value has been issued > > it's considered used and no other record should ever have that number. > > Only if you completely reset your table (see the command "TRUNCATE TABLE") > > could it be possibly safe to begin re-issuing the smaller numbers. Again, > > it's only possible if all of the child records that used to point to the > > old data were also deleted. > > > > Do not rely on the auto_increment value for record sequencing. If you need > > your records serialized in some sequential way, you will need to code the > > support for those sequential numbers in your application. > > So I must do a big SELECT and then check my self every time (for each INSERT), > which IDs are free? > Hmm if the table has more than 100 000 entries, this will slow down my system. > Specialitty because the check function would be written in PHP. Lots of ways round this. Instead of deleting records, add a boolean "deleted" flag. All selects then need to add "and deleted = 0". But you can find a (random) deleted row with "select id from table where deleted = 1 limit 1". If this returns a result, use update to re-populate that record, clearing the deleted flag. If it returns nothing, use insert to create a new record. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about autoincrement ID
On Thu, Mar 23, 2006 at 11:04:55AM -0500, [EMAIL PROTECTED] wrote: > [EMAIL PROTECTED] (saf) wrote on 03/23/2006 10:50:10 AM: > > The short answer is "no". The Record #2 already existed. It's current > status is "deleted". If you had other tables that linked their data to > record #2 and you created a new #2 to replace the one you already deleted > then you could possibly be making a "bad" match between the old data and > the new data. > > For the sake of data consistency and for all of the other good reasons to > have a relational database, once an auto_increment value has been issued > it's considered used and no other record should ever have that number. > Only if you completely reset your table (see the command "TRUNCATE TABLE") > could it be possibly safe to begin re-issuing the smaller numbers. Again, > it's only possible if all of the child records that used to point to the > old data were also deleted. > > Do not rely on the auto_increment value for record sequencing. If you need > your records serialized in some sequential way, you will need to code the > support for those sequential numbers in your application. So I must do a big SELECT and then check my self every time (for each INSERT), which IDs are free? Hmm if the table has more than 100 000 entries, this will slow down my system. Specialitty because the check function would be written in PHP. -- Best regards, saf http://www.trashmail.net/ signature.asc Description: Digital signature
Re: Question about autoincrement ID
[EMAIL PROTECTED] (saf) wrote on 03/23/2006 10:50:10 AM:
> Hi,
>
> I have a question about autoincremend id:
> If I have an autoincrement id set on my first column field of my
> table and I have the
> following entries:
> 1
> 3
>
> And then I make a INSERT INTO foobar VALUES(''); , the next field
> would be automatically 4:
> 1
> 3
> 4
>
> Is there a possibility to take a free ID to not use too high IDs for
nothing?
> I would like to take the ID 2 and not 4, because ID 2 is free.
>
> My problem is that my system which uses the ID numbers in
> applications which uses them
> as signed int or unsigned int,
> so I will soon have a problem, because I insert (and delete some
> times) many entries in my SQL database,
> but not more than the highest value of an signed integer.
>
>
> --
> Best regards,
> saf
> http://TrashMail.net/
The short answer is "no". The Record #2 already existed. It's current
status is "deleted". If you had other tables that linked their data to
record #2 and you created a new #2 to replace the one you already deleted
then you could possibly be making a "bad" match between the old data and
the new data.
For the sake of data consistency and for all of the other good reasons to
have a relational database, once an auto_increment value has been issued
it's considered used and no other record should ever have that number.
Only if you completely reset your table (see the command "TRUNCATE TABLE")
could it be possibly safe to begin re-issuing the smaller numbers. Again,
it's only possible if all of the child records that used to point to the
old data were also deleted.
Do not rely on the auto_increment value for record sequencing. If you need
your records serialized in some sequential way, you will need to code the
support for those sequential numbers in your application.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Question about autoincrement ID
Hi,
I have a question about autoincremend id:
If I have an autoincrement id set on my first column field of my table and I
have the
following entries:
1
3
And then I make a INSERT INTO foobar VALUES(''); , the next field would be
automatically 4:
1
3
4
Is there a possibility to take a free ID to not use too high IDs for nothing?
I would like to take the ID 2 and not 4, because ID 2 is free.
My problem is that my system which uses the ID numbers in applications which
uses them
as signed int or unsigned int,
so I will soon have a problem, because I insert (and delete some times) many
entries in my SQL database,
but not more than the highest value of an signed integer.
--
Best regards,
saf
http://TrashMail.net/
signature.asc
Description: Digital signature
Re: AUTOINCREMENT / UNIQUE Behavior [Newbie Question]
In the last episode (Feb 06), David T. Ashley said: > I remember in MySQL that you can define an integer table field as > AUTOINCREMENT and UNIQUE (I might have the specific keywords wrong, > but everyone will know what I mean). > > In the life of a database where there are frequent additions and > deletions, 2^32 isn't that large of a number. > > When the integer field reaches 2^32-1 or whatever the upper limit is, > what happens then? Will it try to reuse available values from > records that have been deleted? Or is it always an error? It will roll over and return a "duplicate key" error on the first insert of a low-numbered value that still exists. If you think you're going to generate more than 2 billion records, use a BIGINT which will never roll over (well, if you inserted 2 billion records per second, it would roll over in ~270 years). -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AUTOINCREMENT / UNIQUE Behavior [Newbie Question]
I remember in MySQL that you can define an integer table field as AUTOINCREMENT and UNIQUE (I might have the specific keywords wrong, but everyone will know what I mean). In the life of a database where there are frequent additions and deletions, 2^32 isn't that large of a number. When the integer field reaches 2^32-1 or whatever the upper limit is, what happens then? Will it try to reuse available values from records that have been deleted? Or is it always an error? Thanks, Dave. --- David T. Ashley ([EMAIL PROTECTED]) Thousand Feet Consulting, LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: autoincrement for year
Yes it could be but YOU DON'T NEED TRIGGERS. All you need is the auto_increment extension for multiple-column primary keys, as described by another post and demonstrated here: http://dev.mysql.com/doc/refman/4.1/en/example-auto-increment.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Gleb Paharenko <[EMAIL PROTECTED]> wrote on 12/28/2005 08:31:18 AM: > Hello. > > > > This should be possible with triggers. See: > > http://dev.mysql.com/doc/refman/5.0/en/triggers.html > > > > > > > > "Salvatore Celsomino" <[EMAIL PROTECTED]> wrote: > > >Hi, > > >it is possible to create a field autoincrement for year. > > >example: > > >1/2005 > > >2/2005 > > >... > > >10500/2005 > > >new year-- > > >1/2006 > > >2/2006 > > > > > > > -- > 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: autoincrement for year
Hello. This should be possible with triggers. See: http://dev.mysql.com/doc/refman/5.0/en/triggers.html "Salvatore Celsomino" <[EMAIL PROTECTED]> wrote: >Hi, >it is possible to create a field autoincrement for year. >example: >1/2005 >2/2005 >... >10500/2005 >new year-- >1/2006 >2/2006 > -- 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: autoincrement for year
2005/12/28, Salvatore Celsomino <[EMAIL PROTECTED]>: > Hi, > it is possible to create a field autoincrement for year. > example: > 1/2005 > 2/2005 > ... > 10500/2005 > new year-- > 1/2006 > 2/2006 > > This could be of interest : http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html So, if the year is generated by your application or with YEAR(), you could do what you want. (For MyISam and BDB tabel types) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
autoincrement for year
Hi, it is possible to create a field autoincrement for year. example: 1/2005 2/2005 ... 10500/2005 new year-- 1/2006 2/2006
Re: innoDB and autoincrement
At 15:17 +0530 9/12/05, [EMAIL PROTECTED] wrote: This is from the docs . "The auto-increment value of a deleted record will only not be reused until the server is restarted, then it will be reused. In other words, if you insert 10 rows into an InnoDB table and then delete them the AUTO_INCREMENT value will stay at 11 until the server is shutdown at which point it will revert to 1!!!"" Now if i have records 1 to 10 and i delete some records in between , say, 5, 7, 8. Now when i restart my server, will these values of 5, 7 and 8 be reused?? No. If there is a value present in the column with a value higher than the unused values, those values won't be reused. Does it mean that the autoincrement values of deleted records will be reused only if ALL records are deleted and then server restarted?? All records with values higher than the deleted records. From docs " Note that you may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have gotten numbers from the counter. " Does InnoDB reuse these gap values at any time ??? If all records with values higher than the deleted records are deleted and you restart. -- 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: innoDB and autoincrement
Hello. > Now if i have records 1 to 10 and i delete some records in between , say, 5, > 7, 8. Now when i restart my server, will these values of 5, 7 and 8 be > reused?? No, they won't be reused until your column has values more than 5,7 or 8. The AUTO_INCREMENT counter initialized to the maximum value in the column, and next inserts increment the counter. In your case it will be equal to 10, the next value will be 11. But! If you delete, say 9,10 - the next value will be 9 after the startup (in MyISAM table it will be 11, because it is stored in the table and not initialized on startup). > Does InnoDB reuse these gap values at any time ??? Again, if you don't add a row with a greater value after the transaction, the values will be reused after the startup. It is easy to make your own experiments. See: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from ia; +---+ | a | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql> insert into ia values(); Query OK, 1 row affected (0.00 sec) mysql> insert into ia values(); Query OK, 1 row affected (0.00 sec) mysql> select * from ia; +---+ | a | +---+ | 1 | | 2 | | 3 | +---+ mysql> delete from ia where 1=1; Query OK, 3 rows affected (0.00 sec) mysql> select * from ia; Empty set (0.00 sec) mysql> insert into ia values(); Query OK, 1 row affected (0.01 sec) mysql> select * from ia; +---+ | a | +---+ | 4 | +---+ We see that AUTO_INCREMENT counter reflects the changes which was made inside the transaction. The values less than 4 won't be reused after startup because we have inserted the record. But if we hadn't done this, the values after startup would have been reused. [EMAIL PROTECTED] wrote: > [-- text/plain, encoding quoted-printable, charset: us-ascii, 25 lines --] > > This is from the docs . "The auto-increment value of a deleted record > will only not be reused until the server is restarted, then it will be > reused. In other words, if you insert 10 rows into an InnoDB table and then > delete them the AUTO_INCREMENT value will stay at 11 until the server is > shutdown at which point it will revert to 1!!!"" > > Now if i have records 1 to 10 and i delete some records in between , say, 5, > 7, 8. Now when i restart my server, will these values of 5, 7 and 8 be > reused?? > > Does it mean that the autoincrement values of deleted records will be reused > only if ALL records are deleted and then server restarted?? > > From docs " Note that you may see gaps in the sequence of values > assigned to the AUTO_INCREMENT column if you roll back transactions that have > gotten numbers from the counter. " > Does InnoDB reuse these gap values at any time ??? > > Thanks > Priya -- 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: innoDB and autoincrement
[EMAIL PROTECTED] wrote: This is from the docs . "The auto-increment value of a deleted record will only not be reused until the server is restarted, then it will be reused. In other words, if you insert 10 rows into an InnoDB table and then delete them the AUTO_INCREMENT value will stay at 11 until the server is shutdown at which point it will revert to 1!!!"" Now if i have records 1 to 10 and i delete some records in between, say, 5, 7, 8. Now when i restart my server, will these values of 5, 7 and 8 be reused?? No. Does it mean that the autoincrement values of deleted records will be reused only if ALL records are deleted and then server restarted?? No. From docs " Note that you may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have gotten numbers from the counter. " Does InnoDB reuse these gap values at any time ??? No. Thanks Priya There is no special handling of gaps. At startup, the next auto_increment value is simply set to MAX(auto_increment_col) + 1. Thus, values at the end which are deleted will be reused, but gaps remain. For example, if you have records 1 to 10, delete records 3, 6, 9, and 10, then restart, the next value will be 9. See the manual for details <http://dev.mysql.com/doc/mysql/en/innodb-auto-increment-column.html>. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innoDB and autoincrement
This is from the docs . "The auto-increment value of a deleted record will only not be reused until the server is restarted, then it will be reused. In other words, if you insert 10 rows into an InnoDB table and then delete them the AUTO_INCREMENT value will stay at 11 until the server is shutdown at which point it will revert to 1!!!"" Now if i have records 1 to 10 and i delete some records in between , say, 5, 7, 8. Now when i restart my server, will these values of 5, 7 and 8 be reused?? Does it mean that the autoincrement values of deleted records will be reused only if ALL records are deleted and then server restarted?? From docs " Note that you may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have gotten numbers from the counter. " Does InnoDB reuse these gap values at any time ??? Thanks Priya
Re: Problem with autoincrement management
Le samedi 20 août 2005 à 13:53 -0700, Devananda a écrit : Hi Devananda, > In short, it sounds like you should be able to simply copy the > auto_increment field data from your old DBMS into MySQL, and continue to > use it as such, without problem. Have you tried this in a test > environment yet? > > Perhaps I have completely misunderstood what you are trying to do. If > that's the case, I apologize for the long winded and useless answer! This was precisely the explanation I was looking for, and it will save me a great deal of headache. I hadn't realized that you could insert non-contiguous values into an auto_increment column and maintain the increment integrity. Thanks a lots for your reply, Alex -- Thurgood Alex <[EMAIL PROTECTED]> Cabinet Michel Richebourg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with autoincrement management
Thurgood Alex wrote: Hi everyone, I'm having a conceptual problem with managing autoincrements and I was hoping that the list could point me in the right direction ;-) I've migrated a FMPro database which turned out to be a nightmare for various reasons, including the use of multivalue fields in FMPro that didn't export well. However, the real problem that I have is that FMPro allows you to use multiple autoincrement fields in the same table. The mysql server I've migrated to is at version 4.0.23. The autoincrement fields were exported as text and are unfortunately not sequential (in other words, there are gaps) due to data entry errors and a certain lack of rigor in the use of the previous FMPro db. The data in this field serves as a foreign key for another table, thus enabling groups of data to be created in the other table that all reference a same, and in theory, unique, id. To help with understanding, I'll give a sample below : Table1 : field1 int unsigned zerofill auto_increment primary key field2 varchar field3 date field4 int unsigned zerofill (references second table) Table2 : field1 int unsigned zerofill auto_increment primary key field2 int unsigned zerofill (corresponds to the values that need to appear in field4 of Table1) I have populated Table2.field2 with the data that was migrated from the original FMPro table. This has given me a list something like the following : +-+--+ | field1 | field2 | +-+--+ | 01 | 07 | | 02 | 09 | | 03 | 10 | | 04 | 12 | | 05 | 13 | | 06 | 14 | | 07 | 16 | | 08 | 17 | | 09 | 19 | | 10 | 22 | | 11 | 24 | | 12 | 26 | | 13 | 28 | | 14 | 30 | | 15 | 31 | +-+--+ As you can see, the values in field2 are not sequential. Unfortunately, I can not regenerate a new listing, since this would require modifying by hand all of the files in the office that have changed. So the first condition is to maintain these values. Now, I need to be able to continue generating values for field2 in a sequential manner i.e. field2 +1, each time a new group reference is created. This is where I am stuck. I have tried using last-insert_id() to help me with adding one to field2 of the last generated record, but this functionality is session and user specific, which means that when the user disconnects from the db and reconnects later, the initial reference count for last_insert_id is reset to zero I also tried changing my autoincrement value in field1 to start from a value that would be one more than the last value of field2 actually present in Table2. This works fine, but I don't know which SQL command to enter to get it to copy the value from the autoinc generation into field2 at the same time as it creates the autoinc value. To top it all, data entry is achieved through a form in OpenOffice.org via MyODBC, so any proposed solutions would have to be ODBC compliant. The main form corresponds to Table1, and links to the value of field2 in Table2 via a drop down list that executes a select SQL statement on the list of values present in field2. That way, the user can choose the value they want. I'm using a button activated macro to try and generate a new reference, but I'm failing with the necessarySQL command. Any help most greatly appreciated, TIA, Alex Thurgood Alex, I'm not sure I understand your dilemma; it sounds like you have made it more complex than it needs to be. If you need to import data that was an auto_increment field (but is no longer contiguous) and then, after the import, you wish for that field to continue to be auto_increment that is simple. An auto_increment field in MySQL does not need to store contiguous integers (ie, you can delete rows previously inserted, or insert rows with higher values than the current max). Here is a demonstration (table "t1" represents your old data, table "t2" demonstrates importing the old data and continuing to insert data into it). mysql> create table t1 (a int auto_increment primary key, b int); Query OK, 0 rows affected (0.01 sec) mysql> create table t2 (a int auto_increment primary key, b int); Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 (b) values (10),(20),(30),(40),(50); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> insert into t1 (a,b) values (10,90); Query OK, 1 row affected (0.01 sec) mysql> delete from t1 where b in (20,30); Query OK, 2 rows affected (0.00 sec) mysql> select * from t1 order by a; ++--+ | a | b| ++--+ | 1 | 10 | | 4 | 40 | | 5 | 50 | | 10 |
Problem with autoincrement management
Hi everyone, I'm having a conceptual problem with managing autoincrements and I was hoping that the list could point me in the right direction ;-) I've migrated a FMPro database which turned out to be a nightmare for various reasons, including the use of multivalue fields in FMPro that didn't export well. However, the real problem that I have is that FMPro allows you to use multiple autoincrement fields in the same table. The mysql server I've migrated to is at version 4.0.23. The autoincrement fields were exported as text and are unfortunately not sequential (in other words, there are gaps) due to data entry errors and a certain lack of rigor in the use of the previous FMPro db. The data in this field serves as a foreign key for another table, thus enabling groups of data to be created in the other table that all reference a same, and in theory, unique, id. To help with understanding, I'll give a sample below : Table1 : field1 int unsigned zerofill auto_increment primary key field2 varchar field3 date field4 int unsigned zerofill (references second table) Table2 : field1 int unsigned zerofill auto_increment primary key field2 int unsigned zerofill (corresponds to the values that need to appear in field4 of Table1) I have populated Table2.field2 with the data that was migrated from the original FMPro table. This has given me a list something like the following : +-+--+ | field1 | field2 | +-+--+ | 01 | 07 | | 02 | 09 | | 03 | 10 | | 04 | 12 | | 05 | 13 | | 06 | 14 | | 07 | 16 | | 08 | 17 | | 09 | 19 | | 10 | 22 | | 11 | 24 | | 12 | 26 | | 13 | 28 | | 14 | 30 | | 15 | 31 | +-+--+ As you can see, the values in field2 are not sequential. Unfortunately, I can not regenerate a new listing, since this would require modifying by hand all of the files in the office that have changed. So the first condition is to maintain these values. Now, I need to be able to continue generating values for field2 in a sequential manner i.e. field2 +1, each time a new group reference is created. This is where I am stuck. I have tried using last-insert_id() to help me with adding one to field2 of the last generated record, but this functionality is session and user specific, which means that when the user disconnects from the db and reconnects later, the initial reference count for last_insert_id is reset to zero I also tried changing my autoincrement value in field1 to start from a value that would be one more than the last value of field2 actually present in Table2. This works fine, but I don't know which SQL command to enter to get it to copy the value from the autoinc generation into field2 at the same time as it creates the autoinc value. To top it all, data entry is achieved through a form in OpenOffice.org via MyODBC, so any proposed solutions would have to be ODBC compliant. The main form corresponds to Table1, and links to the value of field2 in Table2 via a drop down list that executes a select SQL statement on the list of values present in field2. That way, the user can choose the value they want. I'm using a button activated macro to try and generate a new reference, but I'm failing with the necessarySQL command. Any help most greatly appreciated, TIA, Alex Thurgood -- Thurgood Alex <[EMAIL PROTECTED]> Cabinet Michel Richebourg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to specify autoincrement primary key value
Hello, Sorry for my insistency but I would like to resume this discussion again. A few days ago Gleb answered the question I asked before about 0 PK values (look for "Re: auto_increment=0" topic in the archives). I had a look at http://dev.mysql.com/doc/mysql/en/server-sql-mode.html and found that using 0 is not recommended for PK values. But could you explain WHY? As I mentioned before I used 0's for special purpose, e. g. for specifying root user/category. What is the recommended way of doing this? How do you specify, for instance, root category for products (if we want to have _one and only_ root category)? > -Original Message- > From: Martijn Tonies [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 10, 2005 2:31 PM > To: Denis Gerasimov; [EMAIL PROTECTED]; 'MySQL General List' > Subject: Re: How to specify autoincrement primary key value > > > > > > One simple question... AFAIK I can specify value for an > autoincrement > > > > primary key (int) when inserting a record like this: > > > > > > > > INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test') > > > > > > > > But it doesn't work for id = 0. Why? > > > > > > > > I would like to use some primary key values for special purpose, > e.g. > id > > > 0 > > > > means root/default record and so on. > > > > Is that recommended? Are there any alternative ways? > > > > > > Autoincrement starts from 1. That is the way it is, and cannot, I > think, > > > be changed. > > > > Yes, it starts from 1, but concrete value CAN be changed to 0 manually. > > > > Maybe it is better to ask: "Why can I set autoincrement primary key to 0 > but > > cannot do this using INSERT statement?" > > > > > > > > I would advise against using autoincrement keys for "special" uses. > This > > > is muddling two different functions into one. Remeber that primary > keys > > > must be unique: if, at some time, you need to have more than one of a > > > "special" value, you will be in trouble. > > > > Sure, but the idea was to use 0 for root category or "root" access. It > seems > > to be reasonable at least. > > Then again... this is only an non-business-meaning ID value. If it's 0 > or 100 or 1293492342 -> it doesn't matter at all. > > > > The function of autoincrement > > > keys is to assign unique record identifiers. Do not mix this with > other > > > tasks. > > The meaning of auto-inc values is to make people lazy. > > With regards, > > Martijn Tonies > Database Workbench - developer tool for InterBase, Firebird, MySQL & MS > SQL > Server > Upscene Productions > http://www.upscene.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: How to specify autoincrement primary key value
> > > One simple question... AFAIK I can specify value for an autoincrement > > > primary key (int) when inserting a record like this: > > > > > > INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test') > > > > > > But it doesn't work for id = 0. Why? > > > > > > I would like to use some primary key values for special purpose, e.g. id > > 0 > > > means root/default record and so on. > > > Is that recommended? Are there any alternative ways? > > > > Autoincrement starts from 1. That is the way it is, and cannot, I think, > > be changed. > > Yes, it starts from 1, but concrete value CAN be changed to 0 manually. > > Maybe it is better to ask: "Why can I set autoincrement primary key to 0 but > cannot do this using INSERT statement?" > > > > > I would advise against using autoincrement keys for "special" uses. This > > is muddling two different functions into one. Remeber that primary keys > > must be unique: if, at some time, you need to have more than one of a > > "special" value, you will be in trouble. > > Sure, but the idea was to use 0 for root category or "root" access. It seems > to be reasonable at least. Then again... this is only an non-business-meaning ID value. If it's 0 or 100 or 1293492342 -> it doesn't matter at all. > > The function of autoincrement > > keys is to assign unique record identifiers. Do not mix this with other > > tasks. The meaning of auto-inc values is to make people lazy. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to specify autoincrement primary key value
> > > > > Hello, > > > > One simple question... AFAIK I can specify value for an autoincrement > > primary key (int) when inserting a record like this: > > > > INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test') > > > > But it doesn't work for id = 0. Why? > > > > I would like to use some primary key values for special purpose, e.g. id > 0 > > means root/default record and so on. > > Is that recommended? Are there any alternative ways? > > Autoincrement starts from 1. That is the way it is, and cannot, I think, > be changed. Yes, it starts from 1, but concrete value CAN be changed to 0 manually. Maybe it is better to ask: "Why can I set autoincrement primary key to 0 but cannot do this using INSERT statement?" > > I would advise against using autoincrement keys for "special" uses. This > is muddling two different functions into one. Remeber that primary keys > must be unique: if, at some time, you need to have more than one of a > "special" value, you will be in trouble. Sure, but the idea was to use 0 for root category or "root" access. It seems to be reasonable at least. > The function of autoincrement > keys is to assign unique record identifiers. Do not mix this with other > tasks. > > Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to specify autoincrement primary key value
"Denis Gerasimov" <[EMAIL PROTECTED]> wrote on 10/02/2005 10:59:11: > > Hello, > > One simple question... AFAIK I can specify value for an autoincrement > primary key (int) when inserting a record like this: > > INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test') > > But it doesn't work for id = 0. Why? > > I would like to use some primary key values for special purpose, e.g. id 0 > means root/default record and so on. > Is that recommended? Are there any alternative ways? Autoincrement starts from 1. That is the way it is, and cannot, I think, be changed. I would advise against using autoincrement keys for "special" uses. This is muddling two different functions into one. Remeber that primary keys must be unique: if, at some time, you need to have more than one of a "special" value, you will be in trouble. The function of autoincrement keys is to assign unique record identifiers. Do not mix this with other tasks. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to specify autoincrement primary key value
Hello, One simple question... AFAIK I can specify value for an autoincrement primary key (int) when inserting a record like this: INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test') But it doesn't work for id = 0. Why? I would like to use some primary key values for special purpose, e.g. id 0 means root/default record and so on. Is that recommended? Are there any alternative ways? Best regards, Denis Gerasimov Outsourcing Services Manager, VEKOS, Ltd. www.vekos.ru -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: are autoincrement values not always increasing in innodb?
Frank, "my experience MySQL returns the the rows in the order that you inserted them" This is true, if, and only if you have never deleted a record from the table. Like most SQL servers, MySQL leaves deleted records' space in the physical table "unoccupied", but still available. When you insert a record, it first checks if the new record can not be inserted into an already allocated space (previously occupied by a valid record). If so, it will insert it there, else it will be appended to the table. This will explain the order in which your records are listed. Record 5 either got inserted into an open space, OR it was inserted while the other client thread/transaction inserted the other 4 records. If you optimize your table, then only is it truly purged from space previously occupied by deleted records. To answer your question though, the previous situation has nothing to do with your autoinc values, which will always be incremented - guaranteed. This behaviour can be changed though if you actually specify a value for an AUTOINC column during the insert, and thereby not allow MySQL to do or follow it's normal course in incrementing the autoinc. Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon > > -Original Message- > From: "Frank Sonntag" <[EMAIL PROTECTED]> > To: "mysql@lists.mysql.com" > CC: > Subject: are autoincrement values not always increasing in innodb? > Sent: Mon, 27 Dec 2004 00:45:37 GMT > Received: Mon, 27 Dec 2004 00:49:46 GMT > Read: Tue, 28 Dec 2004 10:38:11 GMT > Hi, > > does InnoDB guarantee that the values of an autoincrement column do always > increase? > What happened to me is that a select * from my_table returns something like > > id | ... > > 10 > 11 > 5 > 12 > 13 > > where id is defined as int(10) unsigned NOT NULL auto_increment > and is the primary key of the table. > The inserts corresponding to ids (10, 11, 12, 13) are done inside one > transaction, the insert that generates id = 5, in another (concurrent) one. > > Cheers > Frank > > > > -- > 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: are autoincrement values not always increasing in innodb?
I am aware of that. But from my experience MySQL returns the the rows in the order that you inserted them. Assuming this is the case, I was wondering if the result I have seen means that the order of autoincrement values does not correspond to the order in which inserts are done. Frank On Mon, 27 Dec 2004 14:44, sol beach wrote: > Unless & until you use an ORDER BY clause > the order of the rows returned by SELECT is indeterminate (unpredictable). > > > On Mon, 27 Dec 2004 13:45:37 +1300, Frank Sonntag > > <[EMAIL PROTECTED]> wrote: > > Hi, > > > > does InnoDB guarantee that the values of an autoincrement column do > > always increase? > > What happened to me is that a select * from my_table returns something > > like > > > > id | ... > > > > 10 > > 11 > > 5 > > 12 > > 13 > > > > where id is defined as int(10) unsigned NOT NULL auto_increment > > and is the primary key of the table. > > The inserts corresponding to ids (10, 11, 12, 13) are done inside one > > transaction, the insert that generates id = 5, in another (concurrent) > > one. > > > > Cheers > > Frank > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
are autoincrement values not always increasing in innodb?
Hi, does InnoDB guarantee that the values of an autoincrement column do always increase? What happened to me is that a select * from my_table returns something like id | ... 10 11 5 12 13 where id is defined as int(10) unsigned NOT NULL auto_increment and is the primary key of the table. The inserts corresponding to ids (10, 11, 12, 13) are done inside one transaction, the insert that generates id = 5, in another (concurrent) one. Cheers Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: autoincrement question
You will have to call the last_insert_id() function in order to obtain the autoincrement value. -Original Message- From: dan orlic To: [EMAIL PROTECTED] Sent: 8/25/04 2:26 PM Subject: autoincrement question question: I have an insert statement that has a null for the value of the primary key, id, since that value is also a auto-increment field. The problem i am running into is I need that Id further down the road for map tables, but I have no idea what the id is, since it is in fact, autoincremented when inserted into the DB. My question is this... is there anything I can add to my insert statement that can have mysql return the id value? or am I just nuts? thank you for your help dan -- 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]
autoincrement question
question: I have an insert statement that has a null for the value of the primary key, id, since that value is also a auto-increment field. The problem i am running into is I need that Id further down the road for map tables, but I have no idea what the id is, since it is in fact, autoincremented when inserted into the DB. My question is this... is there anything I can add to my insert statement that can have mysql return the id value? or am I just nuts? thank you for your help dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Control Center autoincrement
Paul Wallace wrote: Hello, May I specify a column to auto increment in the Control Center UI, either on or after creation? thanks Paul. Yes. In the table editor, there should be a 'notebook' ( multiple page thing ) down the bottom of the window. The first page is called 'field properties'. In it, there should be a check box called 'Auto Increment'. Select the field you want, and click the check box. -- 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]
Control Center autoincrement
Hello, May I specify a column to auto increment from within the Control Center UI, either on or after creation? thanks Paul.
Control Center autoincrement
Hello, May I specify a column to auto increment in the Control Center UI, either on or after creation? thanks Paul.
RE: autoincrement problem
For your problem, you need to create a table with a pool of availables key, and when you use one, you delete from this table and when you don't need it, return it here. Osvaldo Sommmer -Original Message- From: Nitin [mailto:[EMAIL PROTECTED] Sent: Saturday, June 12, 2004 3:44 AM To: MySQL Mailing List Subject: autoincrement problem Hi all, What will be the best way to implement auto increment field. I dont want to use auto increment feature, as it only appends the numbers but doesn't check for the values deleted. Suppose, following are the values in table 1abc 2bcd 3cde .. .. .. 9xyz now if 2nd and 3rd rows are deleted, an autoincrement field will still assign 10, 11, 12 to the new values, while I want it to assign the values deleted from the table first. What will be the best way to implement it? Thanks in advance Nitin --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.703 / Virus Database: 459 - Release Date: 6/10/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.703 / Virus Database: 459 - Release Date: 6/10/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: autoincrement problem
First of all, thanks for replying. but, u didnt get my point. I want to use numeric field only as the key field, but not the normal auto increment.. - Original Message - From: "Daniel Kasak" <[EMAIL PROTECTED]> To: "Nitin" <[EMAIL PROTECTED]>; "MySQL Mailing List" <[EMAIL PROTECTED]> Sent: Saturday, June 12, 2004 3:53 PM Subject: Re: autoincrement problem > Nitin wrote: > > >Hi all, > > > >What will be the best way to implement auto increment field. I dont want to use auto increment feature, as it only appends the numbers but doesn't check for the values deleted. Suppose, following are the values in table > > > >1abc > >2bcd > >3cde > >.. > >.. > >.. > >9xyz > > > >now if 2nd and 3rd rows are deleted, an autoincrement field will still assign 10, 11, 12 to the new values, while I want it to assign the values deleted from the table first. > > > >What will be the best way to implement it? > > > >Thanks in advance > >Nitin > > > > > You shouldn't use text fields as primary keys. It's much faster and more > efficient to use a numeric field. > You're also asking for trouble trying to re-use keys. What happens if > someone has a key 'bcd' and then their record gets deleted, and someone > else gets the key 'bcd'. You go back over data and see reference to > 'bcd', and have no idea what it's referring to unless you get the > transaction logs out and check what data the key was referring to on > that particular day. It will be an absolute nightmare to debug, and you > destroy any sane way of auditing your data. > The best way to implement it, therefore, is to *not* implement it. Use > MySQL's auto_increment field as the primary key. If you absolutely > *must* reuse the text 'keys' you have above, then that's up to your > application to handle. Make a char / varchar field and have your code > scan the table for the next available key and then use that in your > 'insert' statement. But it's a *really* bad idea. > > Dan > > -- > 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: autoincrement problem
Nitin wrote: Hi all, What will be the best way to implement auto increment field. I dont want to use auto increment feature, as it only appends the numbers but doesn't check for the values deleted. Suppose, following are the values in table 1abc 2bcd 3cde .. .. .. 9xyz now if 2nd and 3rd rows are deleted, an autoincrement field will still assign 10, 11, 12 to the new values, while I want it to assign the values deleted from the table first. What will be the best way to implement it? Thanks in advance Nitin You shouldn't use text fields as primary keys. It's much faster and more efficient to use a numeric field. You're also asking for trouble trying to re-use keys. What happens if someone has a key 'bcd' and then their record gets deleted, and someone else gets the key 'bcd'. You go back over data and see reference to 'bcd', and have no idea what it's referring to unless you get the transaction logs out and check what data the key was referring to on that particular day. It will be an absolute nightmare to debug, and you destroy any sane way of auditing your data. The best way to implement it, therefore, is to *not* implement it. Use MySQL's auto_increment field as the primary key. If you absolutely *must* reuse the text 'keys' you have above, then that's up to your application to handle. Make a char / varchar field and have your code scan the table for the next available key and then use that in your 'insert' statement. But it's a *really* bad idea. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
autoincrement problem
Hi all, What will be the best way to implement auto increment field. I dont want to use auto increment feature, as it only appends the numbers but doesn't check for the values deleted. Suppose, following are the values in table 1abc 2bcd 3cde .. .. .. 9xyz now if 2nd and 3rd rows are deleted, an autoincrement field will still assign 10, 11, 12 to the new values, while I want it to assign the values deleted from the table first. What will be the best way to implement it? Thanks in advance Nitin
RE: autoincrement column
Keep in mind you can't use truncate in a transaction though. I experienced this problem recently when wanting to delete an entire table and reset my auto increment values. While keeping it in a transaction. From: "Dathan Vance Pattishall" <[EMAIL PROTECTED]> To: "'Arthur Radulescu'" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> Subject: RE: autoincrement column Date: Thu, 8 Apr 2004 12:50:58 -0700 Try truncate table. It essentially drops the table and recreates it. -- DVP > -Original Message- > From: Arthur Radulescu [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 08, 2004 10:36 AM > To: [EMAIL PROTECTED] > Subject: autoincrement column > > Before switching to version 4.0.18 of MySQL the command "delete from > table" > was also reseting the autonincrement column value... It seems that this is > not happening anymore after we have installed this version... > > Any idee about this problem? Is there any other way to reset this? > > > Arthur > > > -- > 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] _ Limited-time offer: Fast, reliable MSN 9 Dial-up Internet access FREE for 2 months! http://join.msn.com/?page=dept/dialup&pgmarket=en-us&ST=1/go/onm00200361ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: autoincrement column
Try truncate table. It essentially drops the table and recreates it. -- DVP > -Original Message- > From: Arthur Radulescu [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 08, 2004 10:36 AM > To: [EMAIL PROTECTED] > Subject: autoincrement column > > Before switching to version 4.0.18 of MySQL the command "delete from > table" > was also reseting the autonincrement column value... It seems that this is > not happening anymore after we have installed this version... > > Any idee about this problem? Is there any other way to reset this? > > > Arthur > > > -- > 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]
autoincrement column
Before switching to version 4.0.18 of MySQL the command "delete from table" was also reseting the autonincrement column value... It seems that this is not happening anymore after we have installed this version... Any idee about this problem? Is there any other way to reset this? Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question on autoincrement
Try this: When you add an AUTO_INCREMENT column, column values are filled in with sequence numbers for you automatically. For MyISAM tables, you can set the first sequence number by executing SET INSERT_ID=value before ALTER TABLE or by using the AUTO_INCREMENT=value table option. See section 14.5.3.1 SET Syntax. >From here: http://www.mysql.com/doc/en/ALTER_TABLE.html On Thu, 01 Apr 2004, Amit Wadhwa wrote: > > Hi All, > I was wondering if there was a way in mySQL wherein I tell the > autoincrement spec where to start from? > Eg. Autoincrement startfrom 1 > And the values go in as: > 10001..10002 and so on,.. instead of 1..2..3.. > ..like sequencing in oracle... > Ò0 > Any help is appreciated, > Thanks > Amit Wadhwa > Data Analyst > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: question on autoincrement
similar-ish to setting your sequence in oracle try this: The create table statement below will start the auto increment at 1 CREATE TABLE TableX ( X_primary_key INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, X_col1 VARCHAR(20) NOT NULL, X_col2 VARCHAR(40) NOT NULL, X_col3 VARCHAR(10) NOT NULL, PRIMARY KEY(X_primary_key) ) AUTO_INCREMENT = 1; also see: ALTER TABLE tbl_name AUTO_INCREMENT = 100 will start your records at 100 ALTER TABLE tbl_name AUTO_INCREMENT = 1000 will start your records at 100 I haven't actually used the last bit of code, it is courtsey of : http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html this should sort you out.. Regards Joe -Original Message- From: Amit Wadhwa [mailto:[EMAIL PROTECTED] Sent: 01 April 2004 23:33 To: [EMAIL PROTECTED] Subject: question on autoincrement Hi All, I was wondering if there was a way in mySQL wherein I tell the autoincrement spec where to start from? Eg. Autoincrement startfrom 1 And the values go in as: 10001..10002 and so on,.. instead of 1..2..3.. ..like sequencing in oracle Any help is appreciated, Thanks Amit Wadhwa Data Analyst -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question on autoincrement
Hi All, I was wondering if there was a way in mySQL wherein I tell the autoincrement spec where to start from? Eg. Autoincrement startfrom 1 And the values go in as: 10001..10002 and so on,.. instead of 1..2..3.. ..like sequencing in oracle Any help is appreciated, Thanks Amit Wadhwa Data Analyst
Re: Reading securely the value of an autoincrement field used as PK
On Tue, Jan 20, 2004 at 01:25:34PM +0100, Tobias Asplund wrote: > On Tue, 20 Jan 2004, Marco Paci wrote: > > > Since the process of inserting a new record and reading its PK field > > value is a two step process implemented by: > > 1) insert into tablename (columnnames) values() > > 2) select last_insert_id() > > ,and since because of the architecture of my application I cannot > > prevent that 2 concurrent insertions won't be done > > http://www.mysql.com/doc/en/LOCK_TABLES.html > > What will happen is: > >|Write-lock on table >|Insert a new record >| Write-lock on table (waiting...) >| >|Read the value of PK field >|Unlocking table(s) >| Write-lock gotten >| Insert new record >| Read the value of PK field >| Unlock table(s) >| >| >| >v > TimeThread 1 Thread 2 > Axis Ignore this. There's no locking needed to call last_insert_id(); read my other post. Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reading securely the value of an autoincrement field used as PK
On Tue, Jan 20, 2004 at 12:35:07PM +0100, Marco Paci wrote: > Environment OS:windows 2003 server, PLATFORM:.NET framework 1.1, > LANGUAGE:C#, .NET PROVIDER:MySQLDriverCS > > In an application I'm developing on top of mysql, I wanna use as primary > key for tables an autoincrement field. > Every time I insert a new record I need to read the value assigned to > the PK field for that record. > Since the process of inserting a new record and reading its PK field > value is a two step process implemented by: > 1) insert into tablename (columnnames) values() > 2) select last_insert_id() > ,and since because of the architecture of my application I cannot > prevent that 2 concurrent insertions won't be done and since I wanna use > a read_commited isolation level for the transaction, I'm wondering if is > there any way to determine securely the value of an insertion. > > I mean. > I've 2 thread th1 and th2. > > > |Insert a new record > |Insert a new Record > > |Read the value of PK field > |Read the value of PK field > | > | > | > | > v > TimeThread 1 Thread 2 > Axis > > > Is the value read by the thread 1 for the PK field correct? Read http://www.mysql.com/doc/en/Getting_unique_ID.html. It's kept on a per-connection basis. Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reading securely the value of an autoincrement field used as PK
* Tobias Asplund > On Tue, 20 Jan 2004, Marco Paci wrote: > > > Since the process of inserting a new record and reading its PK field > > value is a two step process implemented by: > > 1) insert into tablename (columnnames) values() > > 2) select last_insert_id() > > ,and since because of the architecture of my application I cannot > > prevent that 2 concurrent insertions won't be done > > http://www.mysql.com/doc/en/LOCK_TABLES.html > > What will happen is: > >|Write-lock on table >|Insert a new record [...] You seem to be saying that he needs to lock the table? That is wrong. The LAST_INSERT_ID() function is created specifically for this purpose, it will return the id of the last auto_increment record inserted by THIS connection. Two simultaneous users will have two different connections, thus the LAST_INSERT_ID() will return the correct id for both (or any number of) users, regardless of who executed the first/last INSERT. http://www.mysql.com/doc/en/Information_functions.html#IDX1388 > http://www.mysql.com/doc/en/ANSI_diff_Transactions.html#IDX141 > -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reading securely the value of an autoincrement field used as PK
Use a different connection for each thread. last_insert_id() is isolated per connection. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reading securely the value of an autoincrement field used as PK
On Tue, 20 Jan 2004, Marco Paci wrote: > Since the process of inserting a new record and reading its PK field > value is a two step process implemented by: > 1) insert into tablename (columnnames) values() > 2) select last_insert_id() > ,and since because of the architecture of my application I cannot > prevent that 2 concurrent insertions won't be done http://www.mysql.com/doc/en/LOCK_TABLES.html What will happen is: |Write-lock on table |Insert a new record | Write-lock on table (waiting...) | |Read the value of PK field |Unlocking table(s) | Write-lock gotten | Insert new record | Read the value of PK field | Unlock table(s) | | | v TimeThread 1 Thread 2 Axis cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reading securely the value of an autoincrement field used as PK
Environment OS:windows 2003 server, PLATFORM:.NET framework 1.1, LANGUAGE:C#, .NET PROVIDER:MySQLDriverCS In an application I'm developing on top of mysql, I wanna use as primary key for tables an autoincrement field. Every time I insert a new record I need to read the value assigned to the PK field for that record. Since the process of inserting a new record and reading its PK field value is a two step process implemented by: 1) insert into tablename (columnnames) values() 2) select last_insert_id() ,and since because of the architecture of my application I cannot prevent that 2 concurrent insertions won't be done and since I wanna use a read_commited isolation level for the transaction, I'm wondering if is there any way to determine securely the value of an insertion. I mean. I've 2 thread th1 and th2. |Insert a new record |Insert a new Record |Read the value of PK field |Read the value of PK field | | | | v TimeThread 1Thread 2 Axis Is the value read by the thread 1 for the PK field correct? Thank you Marco Paci -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Autoincrement in MYISAM vs INNODB
It is my understanding that at least through 4.0.14, INNODB does not support using autoincrement on the last field in a multi field primary key. i.e. if a table has a primary key of three fields like cpny_ID, acct_ID, list_ID in MYISAM you can add the autoincrement attribute to list_ID and it will sequence within the cpny_ID, acct_ID group. Are there any plans to support this in INNODB? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixing autoincrement
On Tuesday, August 12, 2003, at 09:46 PM, Andrew Rothwell wrote: Hello List, I have a movies database, that I had an autoincrementing field for counting purposes.What I did though was remove some of the rows out of the table, now my table is reporting an incorrect number of movies listed.What I am trying to do is after is have done the following command [snip] There is no 74 Is there a way to force the DB upon removal of a row (74) to renumber the autoincremented fields? Short answer, no. I have tried to flush tables, but that did not work - btw I am using the last stable 3 release - but I will be updating to the latest stable 4 release in the next day or so. THank you Andrew Long answer: that defeats the purpose of an auto-increment. The only good way to do this is to do it programatically, and even then (if there are multiple updates at a time) won't necessarily guarantee unique values (the point of the auto_increment field). If all you need is the current number of movies, might I suggest: SELECT COUNT(*) FROM tablename; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fixing autoincrement
Hello List,
I have a movies database, that I had an autoincrementing field for
counting purposes.What I did though was remove some of the rows out of
the table, now my table is reporting an incorrect number of movies
listed.What I am trying to do is after is have done the following
command
Mysql> delete from movies where movie_number = 74 limit 1;
Query OK, 1 row affected (0.00 sec)
I get the following using mysqldump:
INSERT INTO movies VALUES ('Six feet under','Drama','Peter
Krause','Michael C. Hall','Frances Conroy','Alan Ball','A drama series
that takes a darkly comical look at members of a dysfunctional Pasadena
family that runs an independent funeral
home.','http://us.imdb.com/Title?0248654',73);
INSERT INTO movies VALUES ('Tomb Raider','Action','Angelina Jolie','Jon
Voight','Iain Glen','Simon West','A member of a rich British
aristocratic family, Lara Croft is a \"tomb raider\" who enjoys
collecting ancient artifacts from ruins of temples, cities, etc.
worldwide, and doesn\'t mind going through death-defying dangers to get
them. She is skilled in hand-to-hand combat, weapons training, and
foreign languages - and does them all in tight
outfits.','http://us.imdb.com/Title?0146316',75);
There is no 74 Is there a way to force the DB upon removal of a row (74)
to renumber the autoincremented fields?
I have tried to flush tables, but that did not work -
btw I am using the last stable 3 release - but I will be updating to the
latest stable 4 release in the next day or so. THank you
Andrew
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixing autoincrement
On Tuesday, August 12, 2003, at 09:59 PM, otherguy wrote: On Tuesday, August 12, 2003, at 09:46 PM, Andrew Rothwell wrote: Hello List, I have a movies database, that I had an autoincrementing field for counting purposes.What I did though was remove some of the rows out of the table, now my table is reporting an incorrect number of movies listed.What I am trying to do is after is have done the following command [snip] There is no 74 Is there a way to force the DB upon removal of a row (74) to renumber the autoincremented fields? Short answer, no. I have tried to flush tables, but that did not work - btw I am using the last stable 3 release - but I will be updating to the latest stable 4 release in the next day or so. THank you Andrew Long answer: that defeats the purpose of an auto-increment. The only good way to do this is to do it programatically, and even then (if there are multiple updates at a time) won't necessarily guarantee unique values (the point of the auto_increment field). I should have said something like "programatically with an integer field" (as oppose to auto_increment). If all you need is the current number of movies, might I suggest: SELECT COUNT(*) FROM tablename; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoincrement/last_insert_id race safe?
On Wednesday 26 March 2003 08:25, you wrote: > > A programmer just asked me about a possible race condition, > > and I didn't know what to answer: > > > > If I insert a line using autoincrement, then ask for last_insert_id() > > am I guaranteed to get the same ID I just inserted? > > Yes > > > It seems that another program could be inserting at almost the same > > time, and could increment the counter again before my last_insert_id() > > checks it. In that case, I would not be dealing with the > > same line I just inserted. > > last_insert_id is stored on a per-connection basis, and frecords the > last insert done by that connection so, if your using a connection pooling system then a race condition can happen, but its only due to the connections being shared. anyone happen to know if coldfusion's use of odbc locks the connection per page request or does it just do it on queries and unlocks it as soon as the query is done? -- sql sql sql mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoincrement/last_insert_id race safe?
> A programmer just asked me about a possible race condition, > and I didn't know what to answer: > If I insert a line using autoincrement, then ask for last_insert_id() > am I guaranteed to get the same ID I just inserted? Yes > It seems that another program could be inserting at almost the same > time, and could increment the counter again before my last_insert_id() > checks it. In that case, I would not be dealing with the > same line I just inserted. last_insert_id is stored on a per-connection basis, and frecords the last insert done by that connection -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoincrement/last_insert_id race safe?
Hi, On Wed, Mar 26, 2003 at 03:17:42PM +0100, Steve Rapaport wrote: > A programmer just asked me about a possible race condition, > and I didn't know what to answer: > > If I insert a line using autoincrement, then ask for last_insert_id() > am I guaranteed to get the same ID I just inserted? > Yes. The last inserted id is kept per connection. So unless YOU insert another record using the same connection, the last_insert_id isn't changed. Also, last_insert_id in a new connection will give you NULL, regardless of what you did in a previous connection. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Autoincrement/last_insert_id race safe?
A programmer just asked me about a possible race condition, and I didn't know what to answer: If I insert a line using autoincrement, then ask for last_insert_id() am I guaranteed to get the same ID I just inserted? It seems that another program could be inserting at almost the same time, and could increment the counter again before my last_insert_id() checks it. In that case, I would not be dealing with the same line I just inserted. Does anyone know if this is a genuine concern? If it is, can anyone think of a workaround? Thanks, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing into an AutoIncrement field
At 22:28 -0600 3/21/03, Paul DuBois wrote: At 20:04 -0800 3/21/03, Todd Cary wrote: I would like to avoid the necessity of specifying all of the fields - instead use the following syntax: INSERT INTO teachers VALUES (1,12345,NULL,'Harry','Smith','707-773-4523',2',... However, the first field is an autoincrementing field. Will MySQL ignore my value and overwrite it with the "auto" value? No. It will only generate an auto-increment value if you specify NULL or omit the column value from the statement. In the latter case you must provide a column list so that MySQL knows which data values go in which clients. Er, ... go in which *columns*. -- Paul DuBois http://www.kitebird.com/ sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Importing into an AutoIncrement field
At 20:04 -0800 3/21/03, Todd Cary wrote: I would like to avoid the necessity of specifying all of the fields - instead use the following syntax: INSERT INTO teachers VALUES (1,12345,NULL,'Harry','Smith','707-773-4523',2',... However, the first field is an autoincrementing field. Will MySQL ignore my value and overwrite it with the "auto" value? No. It will only generate an auto-increment value if you specify NULL or omit the column value from the statement. In the latter case you must provide a column list so that MySQL knows which data values go in which clients. If you specify an explicit value for the auto-increment column (as you do above), and the value already exists in the table, you'll get a duplicate key error, assuming that you've declared it as a PRIMARY KEY or UNIQUE index. The next is question is the command line syntax for importing a file liek the above? I have never done it and I am using the Linux version of MySQL. I don't understand the question. It appears to defy parsing. :-) Todd -- Ariste Software, Petaluma, CA 94952 \n [EMAIL PROTECTED] -- Paul DuBois http://www.kitebird.com/ sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Importing into an AutoIncrement field
I would like to avoid the necessity of specifying all of the fields - instead use the following syntax: INSERT INTO teachers VALUES (1,12345,NULL,'Harry','Smith','707-773-4523',2',... However, the first field is an autoincrementing field. Will MySQL ignore my value and overwrite it with the "auto" value? The next is question is the command line syntax for importing a file liek the above? I have never done it and I am using the Linux version of MySQL. Todd -- Ariste Software, Petaluma, CA 94952 \n [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Autoincrement field rollover
> The primary key (message_id)is a UNSIGNED BIGINT, > Which is supposed to be 64 bit, with autoincrement. > The each row is deleted as soon as it is fetched > By the client, and also after the expiry of a period > (~30 days). > 64 bits does provide a large number, but there is a > possibility that it won't be enough. > My question is, does the autoincrement count rollover > To zero normally, or will the rollover cause an error? > Or is there a better way to tackle the problem ? You must have a *lot* of data to store. My quick calculation says that if you create a new row every microsecond - which I would think is all you could expect even of the excellent MySQL - you have enough capacity for over 200,000 years with BIGINT. To solve your problem, invest $1 at compound interest today. After 100,000 years, you will have enough money to pay the MySQL team to implement a SUPERINT of 128 bits - and they will still have 100,000 years to do it. Then a simple ALTER TABLE command will ensure that your database will last longer than the Universe. Alec - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Autoincrement field rollover
At 1:19 +0530 3/7/03, Jeethu Rao wrote: Hi, I'm using a mysql table to store small messages in a p2p messaging server. The primary key (message_id)is a UNSIGNED BIGINT, Which is supposed to be 64 bit, with autoincrement. The each row is deleted as soon as it is fetched By the client, and also after the expiry of a period (~30 days). 64 bits does provide a large number, but there is a possibility that it won't be enough. My question is, does the autoincrement count rollover To zero normally, or will the rollover cause an error? It doesn't roll over. When you reach the upper limit of BIGINT UNSIGNED, you'll get a duplicate key error. Or is there a better way to tackle the problem ? Thanks, Jeethu Rao - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Autoincrement field rollover
Hi, I'm using a mysql table to store small messages in a p2p messaging server. The primary key (message_id)is a UNSIGNED BIGINT, Which is supposed to be 64 bit, with autoincrement. The each row is deleted as soon as it is fetched By the client, and also after the expiry of a period (~30 days). 64 bits does provide a large number, but there is a possibility that it won't be enough. My question is, does the autoincrement count rollover To zero normally, or will the rollover cause an error? Or is there a better way to tackle the problem ? Thanks, Jeethu Rao - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
autoincrement field problem with BCB6
Hallo list! I have an autoincrement field (+others of coarse) in myISAM table. When I try to insert values with queries everything works fine, but when I try to do it with Borland C++ builder 6's TSQLClientDataSet (dbExpress) it throws an exception that the field's value must be set. It continues to do the same thing even if I set TField's property AutoGenerateValue to arAutoInc and Required to false. Does anyone have met the problem and what is the workaround. I think that it probably is libmysql.dll's or dbexpmysql.dll's problem, but I can't solve the problem on my own (and BCB's lists don't give any answer). Please help me, or point me to some information in the inet. Kaspars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Autoincrement : how does it work / how to reset it
Auto_Increment will increment the last_INSERT_ID (which in your case is 10)... so the num field of the new entry will be 11. --- Robert Mena <[EMAIL PROTECTED]> wrote: > Hi, I have been using autoincrement fields for some > time but was wondering how does it work in some > "special" situations. > > Ex. suppose I have an autoincrement field called num > and the last one has value of 10. > > I delete the last on and insert a new one. Which > value will it have ? 10 or 11 > > I read that if I issue a query delete * from table > and > delete from table I have diferent results (in > regards > to the auto increment field). Is this correct ? > > Thanks. > PS. plese CC me directly since for some reason the > messages from the list are taking a while to get in > my mailbox. > > __ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up > now. > http://mailplus.yahoo.com > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list > archive) > > To request this thread, e-mail > <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Autoincrement : how does it work / how to reset it
Hi. On Fri 2003-01-31 at 06:48:45 -0800, [EMAIL PROTECTED] wrote: > Hi, I have been using autoincrement fields for some > time but was wondering how does it work in some > "special" situations. Most of this depends on which MySQL version you use and which table type, unfortunately. OTOH, for the common case all just work fine and relying too much on the edge cases is not a good idea anyhow. Some relevant manual pages are: 1: http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html 2: http://www.mysql.com/doc/en/SEC471.html 3: http://www.mysql.com/doc/en/CREATE_TABLE.html 4: http://www.mysql.com/doc/en/ALTER_TABLE.html > Ex. suppose I have an autoincrement field called num > and the last one has value of 10. > > I delete the last on and insert a new one. Which > value will it have ? 10 or 11 With the ISAM and BDB table handler, you will get 10 [see 3]. With MyISAM 11 [3]. With InnoDB 11, except if you restart MySQL in-between, then you will get 10 [2,3]. The common part is: You will get a new number, which is at least greater than the current maximum value, but numbers may be reused. If you ignore older table handlers (ISAM,BDB) and ignore server restarts (my last was about 100 days before): You will get a new, never-used-before number that is greater than the current maximum value with MyISAM or InnoDB. > I read that if I issue a query delete * from table and > delete from table I have diferent results (in regards > to the auto increment field). Is this correct ? The difference is whether you specify a WHERE clause (delete * is not correct syntax, AFAIK), i.e. with DELETE FROM table_name (without a WHERE) the sequence starts over (for any table handler) if you are in auto-commit mode. [3] With a WHERE clause, nothing special happens. You can also set the value explicitly by using [see 4] ALTER TABLE table_name AUTO_INCREMENT = # HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Autoincrement : how does it work / how to reset it
On Fri, Jan 31, 2003 at 06:48:45AM -0800, Robert Mena wrote: > Hi, I have been using autoincrement fields for some > time but was wondering how does it work in some > "special" situations. > > Ex. suppose I have an autoincrement field called num > and the last one has value of 10. > > I delete the last on and insert a new one. Which > value will it have ? 10 or 11 Hi Robert, Why not test them on your own? :) Anyhow... 11 > I read that if I issue a query delete * from table and > delete from table I have diferent results (in regards > to the auto increment field). Is this correct ? For MyISAM tables, if you run DELETE FROM table, the AUTO_INCREMENT field will be reset to default. There is an exception: if you set AUTO_INCREMENT in a secondary column in a multi-value primary key, then deleting the highest value in the secondary column will allow that value to be reused in the auto-increment sequence. i.e. CREATE TABLE user_log ( id SMALLINT UNSIGNED NOT NULL, event MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (user_name, event) ); mysql> INSERT user_log (id) VALUES (1),(1),(1); Query OK, 3 rows affected (0.00 sec) mysql> SELECT * FROM user_log; ++---+ | id | event | ++---+ | 1 | 1 | | 1 | 2 | | 1 | 3 | ++---+ 3 rows in set (0.00 sec) mysql> DELETE FROM user_log WHERE id=1 AND event=3; Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM user_log; ++---+ | id | event | ++---+ | 1 | 1 | | 1 | 2 | ++---+ 2 rows in set (0.00 sec) mysql> INSERT user_log (id) VALUES (1); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM user_log; ++---+ | id | event | ++---+ | 1 | 1 | | 1 | 2 | | 1 | 3 | ++---+ 3 rows in set (0.00 sec) Cheers! -- Zak Greant <[EMAIL PROTECTED]> | MySQL Advocate | http://zak.fooassociates.com Using and Managing MySQL MySQL Training: Hamburg, March 24-28, 2003 Visit http://mysql.com/training for more information "While we are postponing, life speeds by."--Lucius Annaeus Seneca - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Autoincrement : how does it work / how to reset it
On Fri, 2003-01-31 at 10:48, Robert Mena wrote: > Hi, I have been using autoincrement fields for some > time but was wondering how does it work in some > "special" situations. > > Ex. suppose I have an autoincrement field called num > and the last one has value of 10. > > I delete the last on and insert a new one. Which > value will it have ? 10 or 11 11 > > I read that if I issue a query delete * from table and > delete from table I have diferent results (in regards > to the auto increment field). Is this correct ? Try both. I don't know what you mean. Sorry. > Thanks. > PS. plese CC me directly since for some reason the > messages from the list are taking a while to get in my mailbox. Done! -- __ / \\ @ __ __@ Adolfo Bello <[EMAIL PROTECTED]> / // // /\ / \\ // \ // Bello Ingenieria S.A, ICQ: 65910258 / \\ // / \\ / // // / //cel: +58 416 609-6213 /___// // / <_/ \__\\ //__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (# 609-6213) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Autoincrement : how does it work / how to reset it
Hi, I have been using autoincrement fields for some time but was wondering how does it work in some "special" situations. Ex. suppose I have an autoincrement field called num and the last one has value of 10. I delete the last on and insert a new one. Which value will it have ? 10 or 11 I read that if I issue a query delete * from table and delete from table I have diferent results (in regards to the auto increment field). Is this correct ? Thanks. PS. plese CC me directly since for some reason the messages from the list are taking a while to get in my mailbox. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB is losing records, there are gaps in autoincrement sequence
Robert, yes, if there are multiple UNIQUE keys in a table, then in the REPLACE implementation there is a loop which DELETEs all records which have conflicting UNIQUE key values. Since you have kept the binlog of your database, you can rerun the SQL statements and try to find where your rows magically disappear. I am not fond of the REPLACE statement. It is 'implicit programming', which can produce hard-to-find bugs in your application. Similar implicit bugs arise from the use of ON DELETE/UPDATE CASCADE and triggers. Regards, Heikki sql query Subject: Re: InnoDB is losing records, there are gaps in autoincrement sequence From: Robert Wunderer Date: 24 Jan 2003 12:53:04 +0100 Hi. - Original Message - > InnoDB appears to be losing records. > > The version I am using is 3.32.53 in Windows 2000. > > Gaps are appearing in the autoincrement sequence when > the application does not permit record deletions. > > Records disappear although they have been retrieved > for printing earlier. We seem to be having the same problem: Even though there is not a single "delete" statement referencing the table in question from time to time records seem to disappear. I even let mysql do an update log and couldn't find a delete statement there either. Today I found the following article in the mailing list archives: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:40074 It explains how a REPLACE statement on a table with multiple unique keys might cause more than one row to be deleted. I am not sure if this is definite cause of the disappearing records in my application, but I am going to investigate this further. Regards, Robert. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB is losing records, there are gaps in autoincrement sequence
Hi! - Original Message - From: "My Deja" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Thursday, January 16, 2003 12:39 PM Subject: InnoDB is losing records, there are gaps in autoincrement sequence > InnoDB appears to be losing records. > > The version I am using is 3.32.53 in Windows 2000. > > Gaps are appearing in the autoincrement sequence when > the application does not permit record deletions. > > Records disappear although they have been retrieved > for printing earlier. I have not seen this kind of bug reported. Are you sure you do not roll back the insertions? Or delete the rows? The auto-inc sequence will not be continuous if you roll back insertions, or delete rows. > Does anyone know how this problem can be fixed? > > I am checking the application code to see if records > can be deleted, either deliberately or accidentally. > > Has anyone come across such a problem? > > (sql,query,database,odbc - spam block bypass) Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB losing records, gaps appearing in autoincrement sequence
InnoDB appears to be losing records. The version I am using is 3.32.53 in Windows 2000. Gaps are appearing in the autoincrement sequence when the application does not permit record deletions. Records disappear although they have been retrieved for printing earlier. Does anyone know how this problem can be fixed? I am checking the application code to see if records can be deleted, either deliberately or accidentally. Has anyone come across such a problem? (sql,query,database,odbc - spam block bypass) __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and Autoincrement
Stefan, - Original Message - From: ""Stefan Sturm"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Sunday, December 01, 2002 1:49 PM Subject: MySQL and Autoincrement > Hello, > > when I use autoincrement on a field, now can I start the count whith > 100? with InnoDB tables you have to use the following trick to init the auto-inc counter to 100: INSERT INTO table (99, ...); DELETE FROM table WHERE yourautoinccolumn = 99; > Thank you, > > Stefan Sturm Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com > --- > sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and Autoincrement
Hello. On Sun 2002-12-01 at 12:47:42 +0100, [EMAIL PROTECTED] wrote: > Hello, > > when I use autoincrement on a field, now can I start the count whith > 100? See http://www.mysql.com/doc/en/ALTER_TABLE.html (end of page) Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL and Autoincrement
Hello, when I use autoincrement on a field, now can I start the count whith 100? Thank you, Stefan Sturm --- sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multi-Column Index with AutoIncrement
A month or two back I posted a question asking if an AutoIncrement column in a multicolumn index was supposed to work. At the time, I had accidently produced the intended effect, and couldn't duplicate it. Today, while browsing the MySQL docs, I discovered how to do it properly, and thought those who don't know how it works might want to know. The key (no pun intended) to getting it to work is twofold. First, the table type must be MyISAM or BDB. Second, it will maintain a seperate autoincrement for every unique /prefix/ or, in plain english, for every unique combination of values in the columns that come /before/ the AutoIncrement column in the index. Hope this helps someone. I know it's going to help me significantly. Matthew Walker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Autoincrement question
Alec, Friday, August 09, 2002, 2:01:10 PM, you wrote: AC> If I insert a number, say X, of records using a single statement of the AC> form AC> INSERT INTO table VALUES (...), (...), ..., (...) AC> then retrieve the LAST_INSERT_ID, say Y, is it reasonable to assume that AC> the records will be numbered contiguously from Y-X+1 to Y, even in a AC> multi-user environment? AC> And if not so, would it be reasonable if I LOCK the AC> table round the insert? LAST_INSERT_ID returns values for the first inserted row, if you insert many rows with one INSERT statement. LAST_INSERT_ID returns last id that was generated on this connection. If you insert another value than NULL or 0 to the auto_increment column, it doesn't change LAST_INSERT_ID Please, check the manual. You can find description of this function here: http://www.mysql.com/doc/en/Miscellaneous_functions.html -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Autoincrement question
If I insert a number, say X, of records using a single statement of the form INSERT INTO table VALUES (...), (...), ..., (...) then retrieve the LAST_INSERT_ID, say Y, is it reasonable to assume that the records will be numbered contiguously from Y-X+1 to Y, even in a multi-user environment? And if not so, would it be reasonable if I LOCK the table round the insert? Thanks for any help Alec Cawley mysql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Autoincrement in MultiColumn primary key?
Hmm, this all sounds indeed like a terrific feature .. if it would work !
So, I decided to try it and just see what'll happen. I did the following
things:
* first I created a table:
mysql> create table autoIncrement (
-> id int(11) NOT NULL AUTO_INCREMENT,
-> thingie varchar(20) NOT NULL,
-> primary key(id, thingie)
-> );
Query OK, 0 rows affected (0.28 sec)
* inserted some values:
mysql> insert into autoIncrement (thingie) VALUES ("bla"),
-> ("boe"),
-> ("boer"),
-> ("en"),
-> ("joy");
Query OK, 5 rows affected (0.24 sec)
Records: 5 Duplicates: 0 Warnings: 0
did a select:
mysql> select * from autoIncrement;
++-+
| id | thingie |
++-+
| 1 | bla |
| 2 | boe |
| 3 | boer|
| 4 | en |
| 5 | joy |
++-+
5 rows in set (0.04 sec)
Nothing seen from a counter for each value of thingie. I decided to insert a
value for thingie wich already existed, and to repeat the select:
mysql> insert into autoIncrement (thingie) values ("bla");
Query OK, 1 row affected (0.00 sec)
mysql> select * from autoIncrement;
++-+
| id | thingie |
++-+
| 1 | bla |
| 2 | boe |
| 3 | boer|
| 4 | en |
| 5 | joy |
| 6 | bla |
++-+
6 rows in set (0.00 sec)
Again, no incrementing per value for the second element of the primary key.
Can you please tell me, Matthew, how did you do it? It sounds too good to be
true and so it seems to be, or isn't it?
Greetz,
Wouter
--
Alle door mij verzonden email is careware. Dit houdt in dat het alleen
herlezen en bewaard mag worden als je goed omgaat met al het leven op aarde
en daar buiten. Als je het hier niet mee eens bent dien je mijn mailtje
binnen 24 uur terug te sturen, met opgaaf van reden van onenigheid.
All email sent by me is careware. This means that it can only be reread and
kept if you are good for all the life here on earth and beyond. If you don't
agree to these terms, you should return this email in no more than 24 hours
stating the reason of disagreement.
-Oorspronkelijk bericht-
Van: Matthew Walker [mailto:[EMAIL PROTECTED]]
Verzonden: vrijdag 12 juli 2002 23:30
Aan: [EMAIL PROTECTED]
Onderwerp: Autoincrement in MultiColumn primary key?
filter: sql,query
I did some tests recently on a two column primary key, one of which was
an autoincrement column. It worked nicely, maintaining a separate
increment for every unique value in the second column. I was ecstatic,
as this was exactly what I need for some tables I'm designing.
However, today I tried to use the same idea in a three column primary
key, and it seems to be acting like a lone autoincrement column, instead
of how it acted with two columns.
What I want to know, is this: Is the two-column behavior an official
feature, or is it an unintended effect? I personally /love/ that
behavior, and think it should be a feature, but before I go further, I
need to know if I can count on it to hang around. And if it is a
feature, why it doesn't work in three column indexes.
Please CC the reply-to address in your responses.
Thanks for your time,
Matthew Walker
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Autoincrement in MultiColumn primary key?
filter: sql,query I did some tests recently on a two column primary key, one of which was an autoincrement column. It worked nicely, maintaining a separate increment for every unique value in the second column. I was ecstatic, as this was exactly what I need for some tables I'm designing. However, today I tried to use the same idea in a three column primary key, and it seems to be acting like a lone autoincrement column, instead of how it acted with two columns. What I want to know, is this: Is the two-column behavior an official feature, or is it an unintended effect? I personally /love/ that behavior, and think it should be a feature, but before I go further, I need to know if I can count on it to hang around. And if it is a feature, why it doesn't work in three column indexes. Please CC the reply-to address in your responses. Thanks for your time, Matthew Walker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: primary key that doesn't autoincrement
In the last episode (Jan 09), D Woods said: > I'm a novice at setting up a mysql database and don't know the answer to > this. I usually set up my primary keys as autoincrementing ID fields. Now I > need to have a primary key that isn't an autoincrementing field as I want to > store the CFTOKEN and use it as the primary key. Can I not have a > non-autoincrementing primary key in mysql? Sure. Just leave off the AUTO_INCREMENT when you create the field. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: primary key that doesn't autoincrement
yes, you can -Original Message- From: D Woods [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 4:02 PM To: [EMAIL PROTECTED] Subject: primary key that doesn't autoincrement I'm a novice at setting up a mysql database and don't know the answer to this. I usually set up my primary keys as autoincrementing ID fields. Now I need to have a primary key that isn't an autoincrementing field as I want to store the CFTOKEN and use it as the primary key. Can I not have a non-autoincrementing primary key in mysql? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
primary key that doesn't autoincrement
I'm a novice at setting up a mysql database and don't know the answer to this. I usually set up my primary keys as autoincrementing ID fields. Now I need to have a primary key that isn't an autoincrementing field as I want to store the CFTOKEN and use it as the primary key. Can I not have a non-autoincrementing primary key in mysql? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with the autoincrement
I have a problem with the autoincrement. I have a table with a lot of registers, and the table have a field with auto_increment, but know this field don`t make the increment and always return to us the same number, one more big that the number that the autoincrement have to return. I have pass the table to other mysql server yesterday, and after that the problem began. Anyone have any idea why this problem? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SupportWizard: bug in mysql: "ALTER TABLE CHANGE COLUMN A" can damage autoincrement column B!
Max writes:
> >Description:
> I am using russian values(cp1251 charset) for ENUM items.
> Here is an example how to damage ID field by query to change
> F_Category Enum.
> >How-To-Repeat:
> # Create tableA with ID field.
> CREATE TABLE tableA (
> ID int(11) NOT NULL auto_increment,
> F_ReportID bigint(20) NOT NULL default '0',
> F_Response_to varchar(99) NOT NULL default '',
> F_More_reports mediumtext NOT NULL,
> F_Linked_Reports mediumtext NOT NULL,
> F_Responses mediumtext NOT NULL,
> F_Summary varchar(99) NOT NULL default '',
> F_Description mediumtext NOT NULL,
> F_Solution mediumtext NOT NULL,
> F_Category enum('Bug','Question','Documentation','Enhancement Request','Web
>Error','User Error','Unknown') NOT NULL default 'Question',
> F_Attached_file mediumtext NOT NULL,
> F_Severity enum('Critical','High','Medium','Low') NOT NULL default 'Medium',
> F_User varchar(99) NOT NULL default '',
> F_EndUser varchar(99) NOT NULL default 'nobody',
> F_FullName varchar(99) NOT NULL default '',
> F_E_mail varchar(99) NOT NULL default '',
> F_Telephone varchar(99) NOT NULL default '',
> F_Group varchar(99) NOT NULL default '',
> F_Internal_analysis mediumtext NOT NULL,
> F_State enum('Open','Assigned','In-progress','In-testing','Closed') NOT NULL
>default 'Open',
> F_Std_Solution enum('Yes','No') NOT NULL default 'No',
> F_Accepted enum('Yes','No') NOT NULL default 'No',
> F_Date datetime NOT NULL default '1990-01-01 00:00:00',
> F_Thread_owner varchar(99) NOT NULL default '',
> F_Creator_E_mail varchar(99) NOT NULL default '',
> F_Modified_by varchar(99) NOT NULL default '',
> F_Modification_date datetime NOT NULL default '1990-01-01 00:00:00',
> F_Route mediumtext NOT NULL,
> F_History mediumtext NOT NULL,
> F_E_mail_History mediumtext NOT NULL,
> F_CorrectSolutionCount bigint(20) NOT NULL default '0',
> F_CorrectSolutionCount bigint(20) NOT NULL default '0',
> F_AdjustedSolutionCount bigint(20) NOT NULL default '0',
> F_Assign_date datetime NOT NULL default '1990-01-01 00:00:00',
> UNIQUE KEY ID (ID),
> KEY F_ReportID (F_ReportID),
> KEY F_Category (F_Category),
> KEY F_Severity (F_Severity),
> KEY F_User (F_User),
> KEY F_State (F_State),
> KEY F_Std_Solution (F_Std_Solution),
> KEY F_Accepted (F_Accepted),
> KEY F_Date (F_Date),
> KEY F_Thread_owner (F_Thread_owner),
> KEY F_Modified_by (F_Modified_by),
> KEY F_Modification_date (F_Modification_date),
> KEY F_Assign_date (F_Assign_date),
> KEY F_Assigned_to (F_Assigned_to,F_State)
> ) TYPE=MyISAM;
>
Hi!
We would very much like to solve a problem, but your test case is
faulty.
First, you have two columns of the same name :
> F_CorrectSolutionCount bigint(20) NOT NULL default '0',
> F_CorrectSolutionCount bigint(20) NOT NULL default '0',
second , you have a key using non-existent column :
> KEY F_Assigned_to (F_Assigned_to,F_State)
Please provide us with a working example.
--
Regards,
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SupportWizard: bug in mysql: "ALTER TABLE CHANGE COLUMN A" can damage autoincrement column B!
>Description:
I am using russian values(cp1251 charset) for ENUM items.
Here is an example how to damage ID field by query to change
F_Category Enum.
>How-To-Repeat:
# Create tableA with ID field.
CREATE TABLE tableA (
ID int(11) NOT NULL auto_increment,
F_ReportID bigint(20) NOT NULL default '0',
F_Response_to varchar(99) NOT NULL default '',
F_More_reports mediumtext NOT NULL,
F_Linked_Reports mediumtext NOT NULL,
F_Responses mediumtext NOT NULL,
F_Summary varchar(99) NOT NULL default '',
F_Description mediumtext NOT NULL,
F_Solution mediumtext NOT NULL,
F_Category enum('Bug','Question','Documentation','Enhancement Request','Web
Error','User Error','Unknown') NOT NULL default 'Question',
F_Attached_file mediumtext NOT NULL,
F_Severity enum('Critical','High','Medium','Low') NOT NULL default 'Medium',
F_User varchar(99) NOT NULL default '',
F_EndUser varchar(99) NOT NULL default 'nobody',
F_FullName varchar(99) NOT NULL default '',
F_E_mail varchar(99) NOT NULL default '',
F_Telephone varchar(99) NOT NULL default '',
F_Group varchar(99) NOT NULL default '',
F_Internal_analysis mediumtext NOT NULL,
F_State enum('Open','Assigned','In-progress','In-testing','Closed') NOT NULL default
'Open',
F_Std_Solution enum('Yes','No') NOT NULL default 'No',
F_Accepted enum('Yes','No') NOT NULL default 'No',
F_Date datetime NOT NULL default '1990-01-01 00:00:00',
F_Thread_owner varchar(99) NOT NULL default '',
F_Creator_E_mail varchar(99) NOT NULL default '',
F_Modified_by varchar(99) NOT NULL default '',
F_Modification_date datetime NOT NULL default '1990-01-01 00:00:00',
F_Route mediumtext NOT NULL,
F_History mediumtext NOT NULL,
F_E_mail_History mediumtext NOT NULL,
F_CorrectSolutionCount bigint(20) NOT NULL default '0',
F_CorrectSolutionCount bigint(20) NOT NULL default '0',
F_AdjustedSolutionCount bigint(20) NOT NULL default '0',
F_Assign_date datetime NOT NULL default '1990-01-01 00:00:00',
UNIQUE KEY ID (ID),
KEY F_ReportID (F_ReportID),
KEY F_Category (F_Category),
KEY F_Severity (F_Severity),
KEY F_User (F_User),
KEY F_State (F_State),
KEY F_Std_Solution (F_Std_Solution),
KEY F_Accepted (F_Accepted),
KEY F_Date (F_Date),
KEY F_Thread_owner (F_Thread_owner),
KEY F_Modified_by (F_Modified_by),
KEY F_Modification_date (F_Modification_date),
KEY F_Assign_date (F_Assign_date),
KEY F_Assigned_to (F_Assigned_to,F_State)
) TYPE=MyISAM;
# It is to see that ID exists in this table
select ID from tableA;
# Try to alter F_Category field
ALTER TABLE tableA CHANGE COLUMN F_Category F_Category ENUM('Bug',
'tmp_sync_1005859699', 'Âîïðîñ ïîëüçîâàòåëÿ', 'Îáùèé âîïðîñ', 'Ðàñøèðåííûé çàïðîñ',
'Îøèáêà íà WEB ñåðâåðå', 'Îøèáêà ïîëüçîâàòåëÿ') NOT NULL DEFAULT 'Bug';
# It is to see what happens with ID
select ID from tableA;
>Fix:
none
>Submitter-Id:
>Originator:Max
>Organization:
Integral Solutions Corp.
>MySQL support: none
>Synopsis: autoincrement ID field renamed during changing F_Category ENUM!
>Severity: critical
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.44 (Source distribution)
>Environment:
System: Linux linux 2.4.14 #4 SMP 9 03:46:13 MSK 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs
gcc version 2.95.3 20010315 (SuSE)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
-rwxr-xr-x1 root root 1384168 20 07:52 /lib/libc.so.6
-rw-r--r--1 root root 25215580 20 07:28 /usr/lib/libc.a
-rw-r--r--1 root root 178 20 07:28 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/home/isc/mysql
--localstatedir=/usr/home/isc/mysql/data --with-client-ldflags=-all-static
--with-mysqld-ldflags=-all-static --with-berkeley-db --with-innodb --with-libwrap
--with-extra-charset=all --with-charset=cp1251
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to Get the New Number inserted in a AutoIncrement Field
LAST_INSERT_ID() is what you need. -Original Message- From: ROGGER ALEXIS VASQUEZ MARTINEZ [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 13, 2001 4:45 PM To: [EMAIL PROTECTED] Subject: How to Get the New Number inserted in a AutoIncrement Field I know that this is a old question, but I really need to know how can I get the number that Mysql use in a autoincrement field of a record I just inserted ... Is there any function like LAST_INSERTED or something like that ... Thanks a lot - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to Get the New Number inserted in a AutoIncrement Field
http://www.mysql.com/doc/G/e/Getting_unique_ID.html HTH, /Rob >I know that this is a old question, but >I really need to know how can I get the >number that Mysql use in a autoincrement field >of a record I just inserted ... >Is there any function like LAST_INSERTED or >something like that ... > >Thanks a lot - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
