{"id":1390,"date":"2009-02-15T22:11:09","date_gmt":"2009-02-15T12:11:09","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=1390"},"modified":"2014-01-31T20:10:33","modified_gmt":"2014-01-31T10:10:33","slug":"the-frm-file-format","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2009\/02\/15\/the-frm-file-format\/","title":{"rendered":"The FRM file format"},"content":{"rendered":"<p>It&#8217;s fortunate that I&#8217;m watching <a href=\"http:\/\/en.wikipedia.org\/wiki\/Veronica_Mars\">Veronica Mars<\/a> again with a mate; a more-than-you-think amount of detective work is required to understand the relationship (and format) of the TABLE_SHARE, the FRM file and HA_CREATE_INFO. Oh, also you&#8217;ll need drizzled\/base.h and drizzled\/structs.h and drizzled\/table_share.h is also a good one to have open.<\/p>\n<p>The FRM file is really a FoRM file from UNIREG (see <a href=\"http:\/\/virtualblueness.net\/mysql\/manual_Unireg.html\">copies of really old mysql docs around the place<\/a> or even better, the links off <a href=\"http:\/\/sheeri.com\/\">Sheeri<\/a>&#8216;s <a href=\"http:\/\/web.archive.org\/web\/20100204060513\/http:\/\/www.sheeri.com\/content\/unireg-and-automatic-database-ui-generatio\">blog post<\/a>). Also, <a href=\"http:\/\/jan.kneschke.de\/2008\/8\/2\/mysql-internals-screens-or-frm-files\">Jan has some thoughts on FRM<\/a> too and <a href=\"http:\/\/blogs.sun.com\/thava\/entry\/dump_mysql_frm_file_header\">Thava has a scary frmdump php<\/a> script.<\/p>\n<p>I have to agree completely with Jan:<\/p>\n<ul>\n<li>&#8220;the internals document is missing all the interesting parts&#8221;<br \/>\nI&#8217;ve just read the source. Everything in the internals doc is easily gotten from the source, so when I finally did take a close look it was &#8220;I know all this&#8221;. I can&#8217;t fault the docs team here at all &#8211; I&#8217;d place this 3rd from the bottom in priorities. In fact, it&#8217;s better to fix it than to document it.<\/li>\n<li>&#8221; get the hands dirty and get into the code &#8230; it got really dirty&#8221;<br \/>\nOh yeah &#8211; and it&#8217;s only gotten worse with things added to it.<\/li>\n<\/ul>\n<p>It contains interesting nuggets like unireg_check (or unireg_type, depending on where you read) that does:<\/p>\n<pre> enum utype  { NONE,DATE,SHIELD,NOEMPTY,CASEUP,PNR,BGNR,PGNR,YES,NO,REL,\r\n                CHECK,EMPTY,UNKNOWN_FIELD,CASEDN,NEXT_NUMBER,INTERVAL_FIELD,\r\n                BIT_FIELD, TIMESTAMP_OLD_FIELD, CAPITALIZE, BLOB_FIELD,\r\n                TIMESTAMP_DN_FIELD, TIMESTAMP_UN_FIELD, TIMESTAMP_DNUN_FIELD};<\/pre>\n<p>But really only the timestamp things&#8230; which should be default magic, but it&#8217;s somewhere tied in (Jay had thoughts last time I spoke to him&#8230; hopefully going away soon). A bunch of these aren&#8217;t ever used and are just relics from UNIREG. In fact&#8230; I went and removed what wasn&#8217;t needed and just ended up with:<\/p>\n<pre>\u00c2\u00a0 enum utype\u00c2\u00a0 { NONE,\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 NEXT_NUMBER,\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 TIMESTAMP_OLD_FIELD,\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 TIMESTAMP_DN_FIELD, TIMESTAMP_UN_FIELD, TIMESTAMP_DNUN_FIELD};<\/pre>\n<p>Which does seem a <strong>bit<\/strong> nicer. The\u00c2\u00a0 fact that TIMESTAMP_OLD_FIELD is used as in interim value is, wel, scary. At least with a smaller set of possiblities it will be easier to convert into the proto format.<\/p>\n<p>A hint of a brighter future is in the comment there:<\/p>\n<pre>\/*\r\n    We use three additional unireg types for TIMESTAMP to overcome limitation\r\n    of current binary format of .frm file. We'd like to be able to support\r\n    NOW() as default and on update value for such fields but unable to hold\r\n    this info anywhere except unireg_check field. This issue will be resolved\r\n    in more clean way with transition to new text based .frm format.\r\n    See also comment for Field_timestamp::Field_timestamp().\r\n  *\/<\/pre>\n<p>Hrrm&#8230; a text based FRM? That would be much nicer to read the code for. Unfortunately, it doesn&#8217;t really exist. <strong>Some<\/strong> FRMs are text in MySQL, but not ones to do with tables. You can look at the FRM for a VIEW in a text editor and see the SQL quite easily (the file format is text).<\/p>\n<p>So I can&#8217;t go look at any nice text based format code &#8211; it&#8217;s all uint2korr() and friends. Yes folks, this is about the only place left in the code with function names in Swedish. <a href=\"http:\/\/www.tranexp.com:2000\/InterTran?url=http%3A%2F%2F&amp;type=text&amp;text=korr&amp;from=swe&amp;to=eng\">What does korr mean<\/a>? &#8220;accurate, correct, correctly&#8221;. If you look at korr.h, you&#8217;ll see that it&#8217;s just for storing in machine independent format: low byte first.<\/p>\n<p>My favourite korr functions:<\/p>\n<ul>\n<li>uint3korr<br \/>\nwhich reads 4 bytes, so remember to alloc it, initialise it or <a href=\"http:\/\/www.valgrind.org\">Valgrind<\/a> will make you its bitch.<\/li>\n<li>uint5korr<br \/>\nerr&#8230; 5 bytes of course<\/li>\n<li>uint6korr<br \/>\n6 bytes (getting the pattern now)<\/li>\n<li>uint7korr<br \/>\nwhich doesn&#8217;t actually exist. Nobody loves Seven &#8211; George Costanza was wrong.<\/li>\n<\/ul>\n<p>It also (as Jan showed) does the whole layout on a 80 column terminal for you! This functionality is going, going gone in Drizzle and won&#8217;t be coming back.<\/p>\n<p>There&#8217;s also an &#8220;empty record on start of formfile&#8221; (see make_empty_rec in unireg.cc). This bit is going to cause me some pain relatively soon. Not so much for writing something like it out (default values can be easily put in the proto) but by then constructing it on open (with some careful footing around the issue of the egg coming before the chicken).<\/p>\n<p>Incidently, when discussing with <a href=\"http:\/\/www.fooishbar.org\">Daniel Stone<\/a> about this (and explaining all the weirdness) it did cause him to exclaim &#8220;omg, it&#8217;s XKB!&#8221; &#8211; so that probably helps the X hackers in the room to relate.<\/p>\n<p>The biggest test in moving from FRM to proto is to only rewrite this part of the code &#8211; the TABLE_SHARE, field, Create_foo etc have sooo many bits I want to change\/fix. Going down the rat-hole into an endless cycle of fixing is always a possibility. Sometimes (like with unireg_type) the cleanup lets me really discover what the code is doing, so that&#8217;s being done (but will go away &#8220;soon&#8221;).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>It&#8217;s fortunate that I&#8217;m watching Veronica Mars again with a mate; a more-than-you-think amount of detective work is required to understand the relationship (and format) of the TABLE_SHARE, the FRM file and HA_CREATE_INFO. Oh, also you&#8217;ll need drizzled\/base.h and drizzled\/structs.h &hellip; <a href=\"https:\/\/www.flamingspork.com\/blog\/2009\/02\/15\/the-frm-file-format\/\">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,87,628],"class_list":["post-1390","post","type-post","status-publish","format-standard","hentry","category-drizzle-work-et-al","category-mysql","tag-drizzle","tag-frm","tag-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-mq","jetpack-related-posts":[{"id":1745,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/12\/09\/drizzle-frm-replacement-the-table-proto\/","url_meta":{"origin":1390,"position":0},"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 &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":4180,"url":"https:\/\/www.flamingspork.com\/blog\/2016\/09\/27\/mysql-removes-the-frm-7-years-after-drizzle-did\/","url_meta":{"origin":1390,"position":1},"title":"MySQL removes the FRM (7 years after Drizzle did)","author":"Stewart Smith","date":"2016-09-27","format":false,"excerpt":"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 2009 according to Brian)- and I may have had a\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":1251,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/11\/06\/goodbye-frm-or-at-least-the-steps-to-it\/","url_meta":{"origin":1390,"position":2},"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 &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":1378,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/01\/30\/progress-in-nofrm-branch\/","url_meta":{"origin":1390,"position":3},"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 &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":1262,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/11\/22\/drop-table-fail-on-the-road-to-removing-the-frm\/","url_meta":{"origin":1390,"position":4},"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 &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":1650,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/05\/27\/pluggable-metadata-stores-or-the-revenge-of-table-discovery\/","url_meta":{"origin":1390,"position":5},"title":"Pluggable Metadata stores (or&#8230; 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 &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\/1390","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=1390"}],"version-history":[{"count":5,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1390\/revisions"}],"predecessor-version":[{"id":3662,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1390\/revisions\/3662"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=1390"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=1390"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=1390"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}