{"id":4180,"date":"2016-09-27T09:00:57","date_gmt":"2016-09-26T23:00:57","guid":{"rendered":"https:\/\/www.flamingspork.com\/blog\/?p=4180"},"modified":"2016-09-29T08:47:42","modified_gmt":"2016-09-28T22:47:42","slug":"mysql-removes-the-frm-7-years-after-drizzle-did","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2016\/09\/27\/mysql-removes-the-frm-7-years-after-drizzle-did\/","title":{"rendered":"MySQL removes the FRM (7 years after Drizzle did)"},"content":{"rendered":"

The new MySQL 8.0.0 milestone release that was recently announced<\/a> brings something that has been a looooong <\/strong>time coming: the removal of the FRM file. I was the one who implemented this in Drizzle way back in 2009<\/a> (July 28th 2009 according to Brian<\/a>)- and I may<\/strong> have had a flashback to removing the tentacles of the FRM when reading the MySQL 8.0.0 announcement.<\/p>\n

As an idea for how long this has been on the cards, I’ll quote Brian from when we removed it in Drizzle:<\/p>\n

We have been talking about getting rid of FRM since around 2003. I remember a drive up to northern Finland with Kaj Arn\u00c3\u00b6, where we spent an hour talking about this. I, David, and MontyW have talked about this for years.<\/p>\n

http:\/\/krow.livejournal.com\/642329.html<\/a><\/p><\/blockquote>\n

Soo… it was a known problem for at least thirteen years<\/strong>. One of the issues removing it was how pervasive all of the FRM related things were. I shudder at the mention of “pack_flag” and Jay Pipes probably does too.<\/p>\n

At the time, we tried a couple of approaches as to how things should look. Our philosophy with Drizzle was that it should get out of the way at let the storage engines be the storage engines and not try to second guess them or keep track of things behind their back. I still think that was the correct architectural approach: the role of Drizzle was to put SQL on top of a storage engine, not to also be one itself.<\/p>\n

Looking at the MySQL code, there’s one giant<\/strong> commit 31350e8ab15179acab5197fa29d12686b1efd6ef.<\/a> I do mean giant<\/strong> too, the diffstat is amazing:<\/p>\n

 786 files changed, 58471 insertions(+), 25586 deletions(-)<\/pre>\n
How anyone even remotely did code review on that I have absolutely no idea<\/strong>. I know the only way I could get it to work in Drizzle was to do it incrementally, a series of patches that gradually chiseled out what needed to be taken out so I could put it an API and the protobuf code.<\/p>\n
Oh, and in case you’re wondering:<\/p>\n
- uint offset,pack_flag;\r\n+ uint offset;<\/pre>\n
Thank goodness. <\/strong>Now, you may not appreciate that as much as I might, but pack_flag was not the height of design, it was… pretty much a catchalll for some kind of data about a field that wasn’t something that already had a field in the FRM. So it may include information on if the field could be null or not, if it’s decimal, how many bytes an integer takes, that it’s a number and how many oh, just don’t ask.
\n<\/strong><\/p>\n
Also gone is the weird interval_id and a whole bunch of limitations because of the FRM format, including one that I either just discovered or didn’t remember: if you used all 256 characters in an enum, you couldn’t create the table as MySQL would pick either a comma or an unused character to be the separator in the FRM!?!<\/p>\n
Also changed is how the MySQL server handles default values. For those not aware, the FRM file contains a static<\/strong> copy of the row containing default values. This means the default values are computed once<\/strong> on table creation and never again (there’s a bunch of work arounds for things like AUTO_INCREMENT and DEFAULT NOW()). The new sql\/default_values.cc is where this is done now.<\/p>\n
For now at least, table metadata is also written to a file that appears to be JSON format. It’s interesting that a SQL database server is using a schemaless file format to describe schema. It appears that these files exist only for disaster recovery or perhaps portable tablespaces. As such, I’m not entirely convinced they’re needed…. it’s just a thing to get out of sync with what the storage engine thinks and causes extra IO on DDL (as well as forcing the issue that you can’t have MVCC into the data dictionary itself).<\/p>\n
What will be interesting is to see the lifting of these various limitations and how MariaDB will cope with that. Basically, unless they switch, we’re going to see some interesting divergence in what you can do in either database.<\/p>\n
There’s certainly differences in how MySQL removed the FRM file to the way we did it in Drizzle. Hopefully some of the ideas we had were helpful in coming up with this different approach, as well as an extra seven years of in-production use.<\/p>\n
At some point I’ll write something up as to the fate of Drizzle and a bit of a post-mortem, I think I may have finally worked out what I want to say…. but that is a post for another day.<\/p>\n","protected":false},"excerpt":{"rendered":"
The new MySQL 8.0.0 milestone release that was recently announced brings something that has been a looooong time coming: the removal of the FRM file. I was the one who implemented this in Drizzle way back in 2009 (July 28th … 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_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_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":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[76,14],"tags":[70,87,340,628],"class_list":["post-4180","post","type-post","status-publish","format-standard","hentry","category-code","category-mysql","tag-drizzle","tag-frm","tag-mariadb","tag-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-15q","jetpack-related-posts":[{"id":1650,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/05\/27\/pluggable-metadata-stores-or-the-revenge-of-table-discovery\/","url_meta":{"origin":4180,"position":0},"title":"Pluggable Metadata stores (or… the revenge of table discovery)","author":"Stewart Smith","date":"2009-05-27","format":false,"excerpt":"Users of the ARCHIVE or NDB storage engines in MySQL may be aware of a MySQL feature known as \"table discovery\". For ARCHIVE, you can copy the archive data file around between servers and it magically works (you don't need to copy the FRM). For MySQL Cluster (NDB) it works\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":1378,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/01\/30\/progress-in-nofrm-branch\/","url_meta":{"origin":4180,"position":1},"title":"Progress in nofrm branch","author":"Stewart Smith","date":"2009-01-30","format":false,"excerpt":"\u00e2\u20ac\u0153Ban FRM Now!\u00e2\u20ac\u009d branch in Launchpad Now we're reading part of the table information out of the proto file on disk instead of the frm. Not everything (yet) but a bit. Good first steps. Had to fix bugs along the way as well (and find weirdness in FRM file format...).\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":1745,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/12\/09\/drizzle-frm-replacement-the-table-proto\/","url_meta":{"origin":4180,"position":2},"title":"Drizzle FRM replacement: the table proto","author":"Stewart Smith","date":"2009-12-09","format":false,"excerpt":"Drizzle originally inherited the FRM file from MySQL (which inherited it from UNIREG). The FRM file stores metadata about a table; what columns it has, what type those columns are, what indexes, any default values, comments etc are all stored in the FRM. In the days of MyISAM, this worked\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":1262,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/11\/22\/drop-table-fail-on-the-road-to-removing-the-frm\/","url_meta":{"origin":4180,"position":3},"title":"drop table fail (on the road to removing the FRM)","author":"Stewart Smith","date":"2008-11-22","format":false,"excerpt":"So... in removing the FRM file in Drizzle, I found a bit of a nugget on how drop table works (currently in the MySQL server and now \"did\" in Drizzle). If you DROP TABLE t1; this is what happens open the .frm file read first 10bytes (oh, and if you\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":1251,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/11\/06\/goodbye-frm-or-at-least-the-steps-to-it\/","url_meta":{"origin":4180,"position":4},"title":"Goodbye FRM (or at least the steps to it)","author":"Stewart Smith","date":"2008-11-06","format":false,"excerpt":"Since before MySQL was MySQL, there has been the .FRM file. Of course, what it really wanted to be was \".form\" -\u00c2\u00a0 a file that stored how to display a form on your (green) CRT. Jan blogged earlier in the year on this still being there, even in MySQL 5.1\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":3231,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/03\/03\/unireg-h-is-gone\/","url_meta":{"origin":4180,"position":5},"title":"unireg.h is finally gone","author":"Stewart Smith","date":"2013-03-03","format":false,"excerpt":"I got rid of unireg.cc way back in 2009 as I rewrote all the FRM related code inside Drizzle to instead use a nice protobuf based structure. If you're wondering what was there, I just quote this part of pack_screens() from unireg.cc in MySQL 5.6: start_row=4; end_row=22; cols=80; fields_on_screen=end_row+1-start_row; We\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":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/4180","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=4180"}],"version-history":[{"count":3,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/4180\/revisions"}],"predecessor-version":[{"id":4187,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/4180\/revisions\/4187"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=4180"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=4180"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=4180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}