{"id":1903,"date":"2010-04-21T18:07:04","date_gmt":"2010-04-21T08:07:04","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=1903"},"modified":"2013-12-18T08:53:00","modified_gmt":"2013-12-17T22:53:00","slug":"storage-engine-api-write_row-create-select-and-ddl","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2010\/04\/21\/storage-engine-api-write_row-create-select-and-ddl\/","title":{"rendered":"Storage Engine API: write_row, CREATE SELECT and DDL"},"content":{"rendered":"<p>(this probably applies exactly the same for <a href=\"http:\/\/mysql.com\">MySQL<\/a> and <a href=\"http:\/\/www.drizzle.org\">Drizzle<\/a>&#8230; but I&#8217;m just speaking about current <a href=\"http:\/\/www.drizzle.org\">Drizzle<\/a> here)<\/p>\n<p>In my current <a href=\"https:\/\/code.edge.launchpad.net\/~stewart\/drizzle\/embedded-innodb-create-select-transaction-arrgh\/+merge\/23815\">merge request for the embedded-innodb\u00ef\u00bb\u00bf-create-select-transaction-arrgh<\/a> branch (also see <a href=\"http:\/\/bazaar.launchpad.net\/~stewart\/drizzle\/embedded-innodb-create-select-transaction-arrgh\/revision\/1423\">this specific revision<\/a>), you&#8217;ll notice an odd hoop that we have to jump through to make CREATE SELECT statements work with an engine such as InnoDB.<\/p>\n<p>Basically, this is what happens:<\/p>\n<ul>\n<li>start transaction<\/li>\n<li>start executing SELECT QUERY (well, prepare executing it and fetch a row)<\/li>\n<li>create table<\/li>\n<li>attempt to insert into table<\/li>\n<\/ul>\n<p>But&#8230; 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&#8217;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&#8217;t have this problem. However, we don&#8217;t have that.<\/p>\n<p>So? What do we do? If we&#8217;re in ::write_row and we get an error and we&#8217;re running a SQLCOM_CREATE_TABLE sql_command (yes, we get to poke into current_session-&gt;lex-&gt;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.<\/p>\n<p>This is not a sane part of the interface; it won&#8217;t be an issue for many engines but it is needed here.<\/p>\n<p>This blog post (but not the whole blog) is published under the\u00c2\u00a0<a href=\"http:\/\/creativecommons.org\/licenses\/by-sa\/3.0\/\">Creative Commons Attribution-Share Alike License<\/a>. Attribution is by linking back to this post and mentioning my name (Stewart Smith).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(this probably applies exactly the same for MySQL and Drizzle&#8230; but I&#8217;m just speaking about current Drizzle here) In my current merge request for the embedded-innodb\u00ef\u00bb\u00bf-create-select-transaction-arrgh branch (also see this specific revision), you&#8217;ll notice an odd hoop that we have &hellip; <a href=\"https:\/\/www.flamingspork.com\/blog\/2010\/04\/21\/storage-engine-api-write_row-create-select-and-ddl\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[75,14],"tags":[70,275,257,80],"class_list":["post-1903","post","type-post","status-publish","format-standard","hentry","category-drizzle-work-et-al","category-mysql","tag-drizzle","tag-embedded_innodb","tag-innodb","tag-storage-engine-api"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-uH","jetpack-related-posts":[{"id":1874,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/03\/30\/storing-the-table-message-in-embedded-innodb\/","url_meta":{"origin":1903,"position":0},"title":"Storing the table message in Embedded InnoDB","author":"Stewart Smith","date":"2010-03-30","format":false,"excerpt":"One of the exciting things[1] about working on a storage engine in Drizzle is that you get to manage your own metadata. When the database engine you're writing the storage engine interface for has a pretty complete data dictionary (e.g. Embedded InnoDB) you could just directly use it. At some\u2026","rel":"","context":"In &quot;drizzle&quot;","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2168,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/10\/25\/storage-engine-api-state-graph\/","url_meta":{"origin":1903,"position":1},"title":"Storage Engine API state graph","author":"Stewart Smith","date":"2010-10-25","format":false,"excerpt":"Drizzle still has a number of quirks inherited from the MySQL Storage Engine API (e.g. BLOBs, row buffer, CREATE SELECT and lack of DDL transaction boundaries, key tuple format). One of the things we fixed a long time ago was to have proper methods for StorageEngines to be called for:\u2026","rel":"","context":"In &quot;code&quot;","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"State transitions for a transaction. Transaction can be empty OR have one or more statements","src":"https:\/\/i0.wp.com\/www.flamingspork.com\/blog\/wp-content\/uploads\/2010\/10\/edge30-300x163.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1813,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/03\/12\/writing-a-storage-engine-for-drizzle-part-2-create-table\/","url_meta":{"origin":1903,"position":2},"title":"Writing A Storage Engine for Drizzle, Part 2: CREATE TABLE","author":"Stewart Smith","date":"2010-03-12","format":false,"excerpt":"The DDL code paths for Drizzle are increasingly different from MySQL. For example, the embedded_innodb StorageEngine CREATE TABLE code path is completely different than what it would have to be for MySQL. This is because of a number of reasons, the primary one being that Drizzle uses a protobuf message\u2026","rel":"","context":"In &quot;drizzle&quot;","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1655,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/06\/09\/drizzle-pluggable-metadatastore-or-no-table-definition-file-on-disk\/","url_meta":{"origin":1903,"position":3},"title":"Drizzle pluggable MetadataStore (or: no table definition file on disk)","author":"Stewart Smith","date":"2009-06-09","format":false,"excerpt":"My code is shaping up rather nicely (see https:\/\/code.launchpad.net\/~stewart\/drizzle\/discovery) and I'm planning to submit a merge-request for it later today. I'm about to commit code that implements a MetadataStore for the ARCHIVE engine. This means that for ARCHIVE tables, you only have the .ARZ file on disk. The table definition\u2026","rel":"","context":"In &quot;General&quot;","block_context":{"text":"General","link":"https:\/\/www.flamingspork.com\/blog\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2250,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/01\/06\/no-implicit-commit-on-the-road-to-transactional-ddl\/","url_meta":{"origin":1903,"position":4},"title":"No implicit commit (on the road to transactional DDL)","author":"Stewart Smith","date":"2011-01-06","format":false,"excerpt":"A long time ago, in a time that can only serve to make some feel old and others older, MySQL didn't support transactions. Each statement was executed as it went, there was no ROLLBACK (or COMMIT or crash recovery etc). Then there were transactions. Other RDBMSs implement auto_commit functionality, but\u2026","rel":"","context":"In &quot;code&quot;","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1849,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/03\/16\/embedded-innodb-querying-the-configuration\/","url_meta":{"origin":1903,"position":5},"title":"Embedded InnoDB: querying the configuration","author":"Stewart Smith","date":"2010-03-16","format":false,"excerpt":"I am rather excited about being able to do awesome things such as this to get the current configuration of your server: drizzle> SELECT NAME,VALUE -> FROM DATA_DICTIONARY.INNODB_CONFIGURATION -> WHERE NAME IN (\"data_file_path\", \"data_home_dir\"); +----------------+-------+ | NAME | VALUE | +----------------+-------+ | data_file_path | NULL | | data_home_dir | .\/\u2026","rel":"","context":"In &quot;drizzle&quot;","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1903","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/comments?post=1903"}],"version-history":[{"count":4,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1903\/revisions"}],"predecessor-version":[{"id":3621,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1903\/revisions\/3621"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=1903"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=1903"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=1903"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}