four data types for storing binary data<\/a> – TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.\u00a0 Somehow I always forget about these and use BLOB.\u00a0 BLOB works just fine, but it has a limit on size, which is rather low – 64 KBytes.\u00a0 The mean thing here is that it will work just fine with most of the test data – text files, short PDFs, and small pictures.\u00a0 Once the application is tested and put into production, the corrupted files will start coming in.\u00a0 Re-writing all parts that deal with uploading, moving, cleaning, escaping, and encrypting binary data takes time.\u00a0 Going through file reading and writing routines is boring too, and it won’t help either.<\/p>\nBy the time, the issue is discovered and all fields are changed to LONGBLOB, it is often very late, and you’ve lost your weekend, as well as a lot of large files. This post is an attempt to save my (and your) sanity.<\/p>\n
Reminder: use LONGBLOB instead of BLOB for file storage, unless you are absolutely sure about the maximum size of incoming data<\/strong>.<\/p>\n\n","protected":false},"excerpt":{"rendered":"\nIf you ever mention that your web application uses database to store files, you risk being flamed into oblivion.\u00a0 Indeed, in most cases, it is a bad idea, since file system is more effecient when it comes to files.\u00a0 However, there are cases when it makes sense to have files saved in the database. Maybe … Continue reading BLOB is bad for your (mental) health<\/span><\/a><\/p>\n\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_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},"_links_to":"","_links_to_target":""},"categories":[1,18],"tags":[1559,3066,773,74],"keyring_services":[],"class_list":["post-11301","post","type-post","status-publish","format-standard","hentry","category-general","category-programming","tag-databases","tag-mysql","tag-sql","tag-tips"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":14795,"url":"https:\/\/mamchenkov.net\/wordpress\/2011\/04\/27\/mysql-export-csv-into-outfile-triggers-access-denied-error\/","url_meta":{"origin":11301,"position":0},"title":"MySQL export CSV into OUTFILE triggers “access denied” error","author":"Leonid Mamchenkov","date":"April 27, 2011","format":false,"excerpt":"I came across a weird problem today. \u00a0Gladly, the web is full of solutions, but I'm going to post this anyway, just to have it nearby for the next time. \u00a0I needed to export the results of some query into a CSV file directly from MySQL. \u00a0I prepared my query,\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":14439,"url":"https:\/\/mamchenkov.net\/wordpress\/2011\/02\/10\/day-in-brief\/","url_meta":{"origin":11301,"position":1},"title":"Day in brief","author":"Leonid Mamchenkov","date":"February 10, 2011","format":false,"excerpt":"I'm at FXCC (16, Spyrou Kyprianou ave, Divine Clock Tower, Limassol) [pic]: http:\/\/4sq.com\/eEJmwd # Shared: Confession: There's an iPhone App For That http:\/\/bit.ly\/fq6rWJ # Shared: MPAA Threatens To Disconnect Google From Internet http:\/\/bit.ly\/fIycem # Shared: Cisco Linksys Routers Still Don't Support IPv6 http:\/\/bit.ly\/enAfwl # Damnit! I keep typing 'blog' instead\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":12480,"url":"https:\/\/mamchenkov.net\/wordpress\/2010\/04\/21\/unit-tests-with-cakephp\/","url_meta":{"origin":11301,"position":2},"title":"Unit tests with CakePHP","author":"Leonid Mamchenkov","date":"April 21, 2010","format":false,"excerpt":"I've spent a large part of yesterday setting up the testing environment for a CakePHP project.\u00a0 As always, every time I do something that I have done before, I wanted to do it better, using all the experienced that was acquired previously.\u00a0 And this often leads to the discovery of\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":14884,"url":"https:\/\/mamchenkov.net\/wordpress\/2011\/05\/16\/mysql-prompt\/","url_meta":{"origin":11301,"position":3},"title":"MySQL prompt","author":"Leonid Mamchenkov","date":"May 16, 2011","format":false,"excerpt":"I've been using MySQL for quite a few years by now, but only today I learned that it is possible to define MySQL prompt. \u00a0As per this blog post, all it takes is a couple of lines in .my.cnf file with something like: That alone will help to prevent a\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":28362,"url":"https:\/\/mamchenkov.net\/wordpress\/2018\/02\/04\/encrypt-mysql-data-using-aes-technique\/","url_meta":{"origin":11301,"position":4},"title":"Encrypt MySQL data using AES technique","author":"Leonid Mamchenkov","date":"February 4, 2018","format":false,"excerpt":"I came across this blog post from a while back, which demonstrates how to use AES encryption for the data in MySQL database. This seems rather easy and straightforward (apart from a little calculation one needs to do for the VARBINARY field types).\u00a0 The only thing that I'm concerned about\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":11395,"url":"https:\/\/mamchenkov.net\/wordpress\/2008\/10\/25\/oracle-and-php-the-deadly-mix\/","url_meta":{"origin":11301,"position":5},"title":"Oracle and PHP – the deadly mix","author":"Leonid Mamchenkov","date":"October 25, 2008","format":false,"excerpt":"WI've spent most of the last week getting into, around, and out of the issues related to interoperability of Oracle and PHP.\u00a0 Before you start laughing, cursing, and blaming, Oracle wasn't my choice of the database for this specific project.\u00a0 It's just the company already had it installed and working\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/posts\/11301","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/comments?post=11301"}],"version-history":[{"count":0,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/posts\/11301\/revisions"}],"wp:attachment":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/media?parent=11301"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/categories?post=11301"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/tags?post=11301"},{"taxonomy":"keyring_services","embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/keyring_services?post=11301"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}