other dbs seem to support a table we don't

Bug #707846 reported by Monty Taylor on 2011-01-26
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Drizzle
Wishlist
Unassigned

Bug Description

SQLAlchemy creates this for everyone else:

CREATE TABLE test2 (
 id INTEGER NOT NULL,
 id2 INTEGER NOT NULL AUTO_INCREMENT,
 data VARCHAR(50),
 PRIMARY KEY (id, id2),
 FOREIGN KEY(id) REFERENCES test (id)
);

BUT - for us we don't support it because of the auto_inc. Should we?

Brian Aker (brianaker) wrote :

We can remove the requirement that AUTO_INCREMENT generate an index, it is not required.

On Thu, 03 Feb 2011 03:23:25 -0000, Brian Aker <email address hidden> wrote:
> We can remove the requirement that AUTO_INCREMENT generate an index, it
> is not required.

This would cause problems for InnoDB as the way it gets the max auto inc
number on startup is to do a reverse index scan reading the last record.

--
Stewart Smith

Lee Bieber (kalebral) on 2011-02-17
Changed in drizzle:
importance: Undecided → Wishlist
status: New → Confirmed
milestone: none → future
Stewart Smith (stewart) wrote :

The following should be added to docs:

InnoDB (and HailDB) don't store a current auto_increment value. In fact, the lower levels know absolutely nothing about auto_increment. The auto_increment feature is entirely implemented in the interface to Drizzle from the lower level storage. On opening a table, the index on the auto_increment column is found and then InnoDB/HailDB reads the last record in that index and sets the value of the auto_increment field in that row to the last used auto_increment value.

To support auto_increment on non-index fields, we would either have to maintain a current auto_increment number in another (system) table - having to update it in a sep txn for each auto_increment operation and generating lots of unneeded undo log OR on opening the table do a full table scan to find the current auto_increment value.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers