Richard Jones' Log: Roundup RDBMS fun and games

Wed, 12 Nov 2003

I've just checked in a working postgresql backend for Roundup. That'll make a few people a lot happier.

Along the way, I cleaned up the general rdbms backend implementation. MySQL exhibited some fun behaviour... when running the following:

    set autocommit=0;
    CREATE TABLE ids (id varchar(255)) TYPE=InnoDB;
    CREATE INDEX ids_id_idx on ids(id);
    insert into ids values ('1');
    delete from ids where id='1';
    select * from ids where id='1';
    select * from ids where id=1;
    drop table ids;

That will list an empty table on the first select, and the contents of the table on the second select. Note the id column is clearly a varchar. Now the fun part. Commenting out the "CREATE INDEX" statement makes BOTH selects work!

It's a bug in the InnoDB implementation. Switching to BDB makes it work too. BDB is a lot slower though. I've reported it as MySQL bug 1810.

Until it's addressed, I'll be defaulting the table type in the mysql backend to BDB (which is the setting in maint-0-6, thankfully)