Storage Engine API: write_row, CREATE SELECT and DDL

(this probably applies exactly the same for MySQL and Drizzle… but I’m just speaking about current Drizzle here)

In my current merge request for the embedded-innodb-create-select-transaction-arrgh branch (also see this specific revision), you’ll notice an odd hoop that we have to jump through to make CREATE SELECT statements work with an engine such as InnoDB.

Basically, this is what happens:

  • start transaction
  • start executing SELECT QUERY (well, prepare executing it and fetch a row)
  • create table
  • attempt to insert into table

But… we have to do the DDL statement (i.e. the CREATE TABLE) in its own transaction. This means that the outer transaction (running the SELECT) shouldn’t be able to see it. Except it does. We can create a cursor on this table. However, when we try and do something with it (e.g. ib_cursor_first()) we then get the error message DB_MISSING_HISTORY from InnoDB. With a data dictionary that was REPEATABLE READ, we shouldn’t have this problem. However, we don’t have that.

So? What do we do? If we’re in ::write_row and we get an error and we’re running a SQLCOM_CREATE_TABLE sql_command (yes, we get to poke into current_session->lex->sql_command to find this out) we just magically restart the transaction so that we can (properly) see the created table and write rows to it.

This is not a sane part of the interface; it won’t be an issue for many engines but it is needed here.

This blog post (but not the whole blog) is published under the Creative Commons Attribution-Share Alike License. Attribution is by linking back to this post and mentioning my name (Stewart Smith).

4 thoughts on “Storage Engine API: write_row, CREATE SELECT and DDL

  1. What file does Drizzle use to export services to storage engines?

  2. We’re trying to do things a little differently. Because we are being very, very modular, pretty much everything becomes an API used by other modules.

    So… drizzled::internal is a C++ namespace for things that people certainly should not use (e.g. left over mysys), things should be ‘static’ unless they are meant to be exposed to the outside world.

    This means we still have some ugliness… although it’s getting easier to find (and fix) it at least.

  3. Pingback: Log Buffer #186, a Carnival of the Vanities for DBAs | The Pythian Blog

  4. Pingback: Where are they now: MySQL Storage Engines | Ramblings

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.