MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Steve Hay Date:July 12 2002 4:27pm
Subject:Re: Bug with AUTO_INCREMENT
View as plain text  
Mark Matthews wrote:

> Steve Hay wrote:
> | I've tried the same myself on MyISAM tables as well now, and I also
> | found that there is no problem - it only seems to happen using BDB 
> tables.
> |
> | However, I need to use BDB tables rather than MyISAM to get transaction
> | support, which is required by the software that I'm working on.
> |
> | In fact, it seems that BDB tables work too if the transaction 
> support is
> | not used, i.e. set AUTOCOMMIT=1 and don't bother with explicit COMMIT
> | statements.
> |
> | Maybe MySQL has a problem with AUTO_INCREMENT when used with 
> transactions?
>
> Is there a reason you can't use InnoDB tables? They are
> transaction-capable, and they have a lot more active development on them
> than BDB. They also support AUTO_INCREMENT.
>
>     -Mark

I had been overlooking InnoDB tables because of the need to specify a 
size for the database when it is created, which seems a rather awkward 
design to me.  The software that I'm working on will be running on many 
databases with widely varying sizes, and not needing to worry about that 
for each one was more appealing.

Anyway, just out of interest, I have now tried your suggestion, and I 
found that InnoDB tables work brilliantly!  The test completes in record 
time with no deadlock errors or duplicate entry errors.

Returning to my real software, I find that it also works like a dream 
(except for the occasional deadlock, which I think SELECT ... FOR UPDATE 
will avoid).

I also note that the latest versions of InnoDB have an "autoextend" 
option which largely removes my problem of worrying about file sizes.

I did have one fright, though: getting a 120MB+ "mysqld.exe" process 
showing up in Task Manager (rather than the 15MB or so that I get with 
--skip-innodb), but it seems that this was due to me having set the 
innodb_buffer_pool_size option to 50% of memory as the manual suggests. 
 If I comment out the setting, leaving it at whatever default vaule it 
uses then the process size comes down to a much more reasonable 19MB!

What is the default innodb_buffer_pool_size setting? The manual doesn't 
seem to say.

Looks like I could end up using InnoDB tables. Thanks for the suggestion.

- Steve

Thread
Bug with AUTO_INCREMENTSteve Hay10 Jul
  • Re: Bug with AUTO_INCREMENTRichard Fox10 Jul
  • Re: Bug with AUTO_INCREMENTSteve Hay10 Jul
    • Re: Bug with AUTO_INCREMENTThomas Spahni10 Jul
  • Re: Bug with AUTO_INCREMENTVictoria Reznichenko12 Jul
  • Re: Bug with AUTO_INCREMENTSteve Hay12 Jul
Re: Bug with AUTO_INCREMENTSteve Hay10 Jul
Re: Bug with AUTO_INCREMENTMark Matthews10 Jul
Re: Bug with AUTO_INCREMENTSteve Hay12 Jul