Internal temporary (to help with query execution)<\/li>\n<\/ul>\nIf you’re lucky enough to be creating one of the first three types, you go through an increasingly lovely pile of code that constructs a nice protobuf message about what the table should look like and hands all responsibility over to the storage engine as to how to do that. The basic idea is that Drizzle gets the heck out of the way and lets the storage engine do its thing. This code path looks rather different than what we inherited from MySQL. For a start, we actually have a StorageEngine object rather than just lumping everything into the handler (which we correctly name a Cursor). However… the final part, the internal temporary table code is a bit closer to what we inherited from MySQL. There is a good reason for that, it’s ass.<\/p>\n
For a start, the table::Singular object is still abused by Item_sum_distinct (see the setup() method) as a tuple (a table with no actual table). This is not ideal and just throws a spanner in the works for refactoring a bunch of code.<\/p>\n
The second big problem is that create_tmp_table() doesn’t actually use any normal API calls, instead it manually sets up the table::Singular object. This includes setting up the fields for the table::Singular object in a slightly different way depending on which bit of code called create_tmp_table().<\/p>\n
The third big problem is that it’s not storage engine agnostic. Instead of using any existing and sensible way to go and create a temporary table by using the storage engine API it instead creates a series of MI_COLUMNDEF structures which as you may be able to guess, are MyISAM specific and internal data structures.<\/p>\n
The forth big problem is that if we end up using HEAP (again, like MyISAM, hard coded) we don’t even call the create table method on the engine. The HEAP (or MEMORY engine as it’s now known) is magic in that it can create tables on open()!<\/p>\n
All of these issues make it really, really hard to have another engine with the ability to handle internal temporary tables. You may recall that MariaDB does include the ability to use the Aria engine for internal temporary tables. No, they did not refactor any of this code, they just made a copy of the code and put in Aria where MyISAM was along with some #ifdef for the feature.<\/p>\n
Over the past several years I’ve tried a few times to tease this code out and start the process of turning it into something that is palatable. Every one of those times I’ve either failed or gotten sufficiently frustrated that I’ve given up.<\/p>\n
I now have a new strategy though. After looking at the code for a good few hours a few weekends ago, I think I have an idea of where to start…. (now just for a few more free weekends to implement it).<\/p>\n","protected":false},"excerpt":{"rendered":"
A few weekends ago, I started to again look at the code in Drizzle for producing internal temporary tables. Basically, we have a few type of tables: Standard Temporary (from CREATE TEMPORARY TABLE) Temporary (from ALTER TABLE) Internal temporary (to … Continue reading →<\/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":[76,75,1],"tags":[70,340,628,507],"class_list":["post-3234","post","type-post","status-publish","format-standard","hentry","category-code","category-drizzle-work-et-al","category-general","tag-drizzle","tag-mariadb","tag-mysql","tag-temporary-tables"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-Qa","jetpack-related-posts":[{"id":1869,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/03\/23\/on-tableidentifier-and-the-death-of-path-as-a-parameter-to-storageengines\/","url_meta":{"origin":3234,"position":0},"title":"on TableIdentifier (and the death of path as a parameter to StorageEngines)","author":"Stewart Smith","date":"2010-03-23","format":false,"excerpt":"As anybody who has ever implemented a Storage Engine for MySQL will know, a bunch of the DDL calls got passed a parameter named \"path\". This was a filesystem path. Depending on what platform you were running, it may contain \/ or \\ (and no, it's not consistent on each\u2026","rel":"","context":"In "drizzle"","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":3302,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/04\/20\/the-memory-storage-engine\/","url_meta":{"origin":3234,"position":1},"title":"The MEMORY storage engine","author":"Stewart Smith","date":"2013-04-20","format":false,"excerpt":"I recently wrote about\u00c2\u00a0Where are they now: MySQL Storage Engines\u00c2\u00a0and\u00c2\u00a0The MERGE storage engine: not dead, just resting\u00e2\u20ac\u00a6. or forgotten.\u00c2\u00a0Today, it's the turn of the MEMORY storage engine - otherwise known as HEAP. This is yet another piece of the MySQL server that sits largely unmaintained and unloved. The MySQL Manual\u2026","rel":"","context":"In "drizzle"","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":1743,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/10\/21\/first-flesh-wound-in-create_tmp_table\/","url_meta":{"origin":3234,"position":2},"title":"First flesh wound in create_tmp_table()","author":"Stewart Smith","date":"2009-10-21","format":false,"excerpt":"If you have needed a good reason to drink heavily and forget, may I suggest taking a look at create_tmp_table() and those who call it. It's probably one of the best illustrations of rot and awful, incomprehensible APIs in the server (Drizzle inherited it from MySQL). In the normal paths\u2026","rel":"","context":"In "drizzle"","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":1274,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/11\/26\/temporary-tables-in-any-engine\/","url_meta":{"origin":3234,"position":3},"title":"Temporary tables in any engine….","author":"Stewart Smith","date":"2008-11-26","format":false,"excerpt":"Well... nearly any engine. I have a plan forming in my head to add some hooks to engines to help with creating temporary tables (the ones created while executing a query, not ones created during ALTER TABLE). Currently, if you ALTER TABLE and we require a temporary table, it's still\u2026","rel":"","context":"In "drizzle"","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":1285,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/12\/04\/what-constitutes-an-identifier-for-a-table\/","url_meta":{"origin":3234,"position":4},"title":"What constitutes an identifier for a table?","author":"Stewart Smith","date":"2008-12-04","format":false,"excerpt":"Well... there's: database table name (both of these are quite obvious). But then you have: temporary tables Well... two types of temporary tables: those created in the course of query execution, typically in \/tmp\/ those created during ALTER TABLE, typically in the database directory You may have seen these \"#sql-foo.frm\"\u2026","rel":"","context":"In "drizzle"","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":3291,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/04\/18\/where-are-they-now-mysql-storage-engines\/","url_meta":{"origin":3234,"position":5},"title":"Where are they now: MySQL Storage Engines","author":"Stewart Smith","date":"2013-04-18","format":false,"excerpt":"There was once a big hooplah about the MySQL Storage Engine Architecture and how it was easy to just slot in some other method of storage instead of the provided ones. Over the years I've repeatedly mentioned how this wasn't really the case and that it was remarkably non trivial.\u2026","rel":"","context":"In "code"","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"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\/3234","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=3234"}],"version-history":[{"count":1,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3234\/revisions"}],"predecessor-version":[{"id":3278,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3234\/revisions\/3278"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=3234"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=3234"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=3234"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}