{"id":2041,"date":"2010-06-29T13:19:04","date_gmt":"2010-06-29T03:19:04","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=2041"},"modified":"2010-06-29T13:19:04","modified_gmt":"2010-06-29T03:19:04","slug":"enum-now-works-properly-in-drizzle","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2010\/06\/29\/enum-now-works-properly-in-drizzle\/","title":{"rendered":"ENUM now works properly (in Drizzle)"},"content":{"rendered":"

Over at the Drizzle blog<\/a>, the recent 2010-06-07 tarball was announced<\/a>. This tarball release has my fixes for the ENUM type, so that it now works as it should. I was quite amazed that such a small block of code could have so many bugs! One of the most interesting was the documented limit we inherited from MySQL (see the MySQL Docs on ENUM<\/a>) of a maximum of 65,535 elements for an ENUM column.<\/p>\n

This all started out from a quite innocent comment of Jay<\/a>‘s in a code review for adding support for the ENUM data type to the embedded_innodb engine. It was all pretty innocent… saying that I should use a constant instead of the magic 0x10000 number as a limit on an assert for sanity of values getting passed to the engine. Seeing as there wasn’t a constant already in the code for that (surprise number 1), I said I’d fix it properly in a separate patch (creating a bug for it so it wouldn’t get lost) and the code went in.<\/p>\n

So, now, a few weeks after that, I got around to dealing with that bug (because hey, this was going to be an easy fix that’ll give me a nice sense of accomplishment). A quick look in the Field_enum code raised my suspicions of bugs… I initially wondered if we’d get any error message if a StorageEngine returned a table definition that had too many ENUM elements (for example, 70,000). So, I added a table to the tableprototester<\/a> plugin (a simple dummy engine that is loaded for testing the parsing of specially constructed table messages) that had 70,000 elements for a single ENUM column. It didn’t throw an error. Darn. It did, however, have an incredibly<\/strong> large result for SHOW CREATE TABLE.<\/p>\n

Often with bugs like this I may try to see if the problem is something inherited from MySQL. I’ll often file a bug with MySQL as well if that’s the case. If I can, I’ll\u00c2\u00a0sometimes<\/a> attach the associated patch from Drizzle that fixes the bug, sometimes<\/a> with a patch directly for and tested on MySQL (if it’s not going to take me too long). If these patches are ever applied is a whole other thing – and sometimes you get things like “each engine is meant to have auto_increment behave differently!” – which doesn’t inspire confidence.<\/p>\n

But anyway, the MySQL limit is somewhere between 10850 and 10900. This is not at all what’s documented<\/a>. I’ve filed the appropriate bug (Bug #54194<\/a>) with reproducible test case and the bit of problematic code. It turns out that this is (yet another) limit of the FRM file. The limit is “about 64k FRM”. The bit of code in MySQL that was doing the checking for the ENUM limit was this:<\/p>\n

\r\n<\/pre>\n
\/* Hack to avoid bugs with small static rows in MySQL *\/\r\n  reclength=max(file->min_record_length(table_options),reclength);\r\n  if (info_length+(ulong) create_fields.elements*FCOMP+288+\r\n      n_length+int_length+com_length > 65535L || int_count > 255)\r\n  {\r\n    my_message(ER_TOO_MANY_FIELDS, ER(ER_TOO_MANY_FIELDS), MYF(0));\r\n    DBUG_RETURN(1);\r\n  }<\/pre>\n
So it’s no surprise to anyone how this specific limit (the number of elements in an ENUM) got missed when I converted Drizzle from using an FRM over to a protobuf based structure.<\/p>\n
So a bunch of other cleanup later, a whole lot of extra testing and I can pretty confidently state that the ENUM type in Drizzle does work exactly how you think it would.<\/p>\n
Either way, if you’re getting anywhere near 10,000 choices for an ENUM column you have no doubt already lost<\/strong>.<\/p>\n","protected":false},"excerpt":{"rendered":"
Over at the Drizzle blog, the recent 2010-06-07 tarball was announced. This tarball release has my fixes for the ENUM type, so that it now works as it should. I was quite amazed that such a small block of code … 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":[75,1,14],"tags":[85,70,313,87,315,628,185],"class_list":["post-2041","post","type-post","status-publish","format-standard","hentry","category-drizzle-work-et-al","category-general","category-mysql","tag-bug","tag-drizzle","tag-enum","tag-frm","tag-limits","tag-mysql","tag-protobuf"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-wV","jetpack-related-posts":[{"id":2036,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/06\/03\/new-create-table-performance-record\/","url_meta":{"origin":2041,"position":0},"title":"New CREATE TABLE performance record!","author":"Stewart Smith","date":"2010-06-03","format":false,"excerpt":"\u00ef\u00bb\u00bf\u00ef\u00bb\u00bf4 min 20 sec So next time somebody complains about NDB taking a long time in CREATE TABLE, you're welcome to point them to this :) A single CREATE TABLE statement It had ONE column It was an ENUM column. With 70,000 possible values. It was 605kb of SQL. It\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":2041,"position":1},"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":2303,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/16\/fixed-in-drizzle-no-more-gotchas\/","url_meta":{"origin":2041,"position":2},"title":"Fixed in Drizzle: No more “GOTCHA’s”","author":"Stewart Smith","date":"2011-03-16","format":false,"excerpt":"\u00a0 At the upcoming MySQL Conference and Expo, I'm going to give a Thursday afternoon (2pm) session entitled Fixed in Drizzle: No more \"GOTCHA's\". I plan to have a lot of fun with this session.. If you go back to the very start of when I started submitting code to\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":1763,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/12\/12\/the-table-protobuf-message-format\/","url_meta":{"origin":2041,"position":3},"title":"The Table protobuf message format","author":"Stewart Smith","date":"2009-12-12","format":false,"excerpt":"If you've ever opened up drizzled\/message\/table.proto in the Drizzle source tree you will have seen what's in the table message: the structure that describes a database table in Drizzle. Previously I've talked about the Table message more generally, giving a fair bit of history of the FRM file and how\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":1390,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/02\/15\/the-frm-file-format\/","url_meta":{"origin":2041,"position":4},"title":"The FRM file format","author":"Stewart Smith","date":"2009-02-15","format":false,"excerpt":"It's fortunate that I'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'll need drizzled\/base.h and drizzled\/structs.h and drizzled\/table_share.h is also a good one to have open.\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":4180,"url":"https:\/\/www.flamingspork.com\/blog\/2016\/09\/27\/mysql-removes-the-frm-7-years-after-drizzle-did\/","url_meta":{"origin":2041,"position":5},"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 "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\/2041","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=2041"}],"version-history":[{"count":2,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2041\/revisions"}],"predecessor-version":[{"id":2044,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2041\/revisions\/2044"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=2041"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=2041"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=2041"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}