Re: InnoDB and auto_increment fields
> > stored? Why isn't this done automatically as it is for [ISAM] tables? > As for the "why", I'm not a MySQL developer, but I believe the reason > goes something like this: When ISAM tables were implemented, they did > it the "wrong" way. When other table types came along, they fixed > this "bug" and do it the "right" way. Ahh, ok. Thanks for the info. > You seem to disagree about which is better, but I believe the above is > the explanation for the switch in behavior. I really have no opinion as to which is "better". Certainly, restarting on an empty table automatically is easier. But for reasons that you point out, depending on your application, it could lead to unforseen problems. Chris - 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 and auto_increment fields
> stored? Why isn't this done automatically as it is for [ISAM] tables? As for the "why", I'm not a MySQL developer, but I believe the reason goes something like this: When ISAM tables were implemented, they did it the "wrong" way. When other table types came along, they fixed this "bug" and do it the "right" way. You seem to disagree about which is better, but I believe the above is the explanation for the switch in behavior. As for why the MyISAM/InnoDB way is "right", I believe it has to do with the idea that autoincrement values are very commonly used in situations where you want not just a key that's unique in the table, but one that will never be used again. Specifically, in ISAM tables, when you delete the highest-keyed record, that key will be given out again with the next insert. Depending on how your application is written, that could lead to unpleasant race conditions that would be easily avoided by simply not reusing key values at all. --Pete sql, db - 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 and auto_increment fields
At 11:26 -0500 10/25/02, Chris Boget wrote: sql, query (*sigh*, I hate this filter) I have an auto_increment key set up on my InnoDB table. Whenever I delete all the records, the number isn't reset. However, for my ISAM tables, whenever I delete all the records, the auto_increment number is reset. That's how ISAM tables work. Is there a reason for this in InnoDB? Yes: That's how InnoDB tables work. (So do MyISAM tables.) The handler for each table type handles AUTO_INCREMENT columns slightly differently. You're running across one of those differences. Is there a way that I can reset the auto_increment number when all the records are deleted? ALTER TABLE t AUTO_INCREMENT = 1; I have a temporary table that has records added and deleted all the time. I wouldn't want the record number to eventually reach the ceiling for the datatype of the field. Any insight would be greatly appreciated. Chris - 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 and auto_increment fields
CB> However, for my ISAM tables, whenever I delete all the CB> records, the auto_increment number is reset. CB> Is there a reason for this in InnoDB? Is there a way that I CB> can reset the auto_increment number when all the records CB> are deleted? > try exec this query: > alter table auto_increment = 0; hmm, that did the trick. But why must I do this manually? Does it have something to do with how the InnoDB tables/records are stored? Why isn't this done automatically as it is for MyISAM tables? Thanks for the info! Chris - 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 and auto_increment fields
Dobrý den, sexta-feira, 25 de outubro de 2002, 14:26:05, napsal jste: CB> sql, query (*sigh*, I hate this filter) CB> I have an auto_increment key set up on my InnoDB table. CB> Whenever I delete all the records, the number isn't reset. CB> However, for my ISAM tables, whenever I delete all the CB> records, the auto_increment number is reset. CB> Is there a reason for this in InnoDB? Is there a way that I CB> can reset the auto_increment number when all the records CB> are deleted? I have a temporary table that has records CB> added and deleted all the time. I wouldn't want the record CB> number to eventually reach the ceiling for the datatype of CB> the field. CB> Any insight would be greatly appreciated. CB> Chris try exec this query: alter table auto_increment = 0; - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 -- $ look into "my eyes" look: cannot open my eyes - Reply: [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
InnoDB and auto_increment fields
sql, query (*sigh*, I hate this filter) I have an auto_increment key set up on my InnoDB table. Whenever I delete all the records, the number isn't reset. However, for my ISAM tables, whenever I delete all the records, the auto_increment number is reset. Is there a reason for this in InnoDB? Is there a way that I can reset the auto_increment number when all the records are deleted? I have a temporary table that has records added and deleted all the time. I wouldn't want the record number to eventually reach the ceiling for the datatype of the field. Any insight would be greatly appreciated. Chris - 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
