Alex Stetsenko

Apparently some web applications do store uploaded files in the database as blob field by default (for instance it is true for Mantis bug tracker). While it is acceptable (but not too common?) to store files in the database, it may cause certain problems like uploading large files (particularly 1Mb+).

Normally the first thing to look at would be the actual application configuration. OK, Mantis is configured to accept files up to 5Mb by default. I.e. no problems here. Wait… but PHP is configured to accept files up to 2Mb by default… So where does 1Mb restriction come from?

The answer is that it comes from the database. MySQL has limits on packet size. The largest possible packet that can be transmitted to or from a MySQL 5.1 server or client is 1Gb. However by default it is 1Mb! As result any packets larger than 1Mb will cause packet too large error.

There is max_allowed_packet option in the configuration which allows to set the packet size. For example:

[mysqld]
max_allowed_packet=16M

Coming on December, 10 2010. Johny Depp does not make bad movies, especially with Angelina Jolie. 🙂

Recently I’ve run into another MySQL bug. This time it is related to temporary tables. The bug was reported as #12257.

Basically select * from tmpTable does not work within stored procedure if any column is altered since the table has been originally created. So MySQL keeps converting * to a old column list while some columns may not exist anymore.

A simple workaround allows to eliminate impact of this bug as well as using select * is not recommended anyway. However the most surprising part of this story that this bug cannot be resolved for more than 5 years: it was reported in 2005.

Here is another story that shows how commas are important..

A panda walks into a café. He orders a sandwich, eats it, then takes out a gun and fires it at the other patrons. “Why?” asks the confused, surviving waiter, as the panda makes towards the exit. The panda produces a badly punctuated wildlife manual and tosses it over his shoulder.
“Well, I’m a panda,” he says, at the door. “Look it up”. The waiter turns to the information on pandas in the manual and finds an explanation. “Panda. Large black-and-white bear-like animal, native to China. Eats, shoots, and leaves.”.

The poorly punctuated manual caused all of these problems for the panda and the people in the café. The correct punctuation for the last sentence in the story should not include any commas (simply eats shoots and leaves).

Thanks to Lucy with ESL Podcasts for such a good article.

Amazing video how arriving passengers were given a welcome home to remember from T-Mobile at Heathrow Terminal 5.

Guys at PM Hut (amazing blog on project management) came up with a great list of the 32 most commonly misused words and phrases.
Although the post is titled Even a Project Manager Needs Good Grammar, I believe such list can work as a good ‘flashcard’ for anyone including those who studies either for SAT or GRE exams.

Some notable of those words are quoted below:

Affect/Effect – The way you “affect” someone can have an “effect” on them. “Affect” is usually a verb and “Effect” is a noun.
Complement/Compliment – I often must compliment my wife on how her love for cooking perfectly complements my love for grocery shopping.
Discreet/Discrete – We can break people into two discrete (separate) groups, the discreet (secretive) and indiscreet.
Elicit/Illicit – Some people post illicit things on message boards to elicit outrageous reactions from others.
Flair/Flare – A flair is a talent, while a flare is a burst (of anger, fire, etc.)
Inflammable – Don’t let the prefix confuse you, if something is inflammable it can catch on fire.
Imply/Infer – A reader infers what an author implies. In other words, when you imply something, you hint at it. When you infer something, you draw a conclusion based on clues.
Literally – If you say “His head literally exploded because he was so mad!” then we should see brains splattered on the ceiling.
Lose/Loose – If your pants are too loose you may lose them. That would be almost as embarrassing as misusing these two words.
Percent/Percentage – The word “percent” should only be used when a specific number is given. “Percentage” is more of a general term.
Stationary/Stationery – You are stationary when you aren’t moving. Stationery is something you write on.
Unique – Something can’t be “kind of unique” or even “very unique.” It’s either one of a kind or it isn’t. There is no in between when it comes to unique.
Lie/Lay – After you lay the books on the table, go lie down on the couch.
Allude/Elude – When someone alludes to something in conversation (indirectly references), if you aren’t paying attention the meaning may elude you (escape you).

Tom Cook gives an amazing talk at Velocity 2010 on a day in the life of Facebook operations.
Learn the numbers behind Facebook (400 million of users and growing!), how the code is pushed, what tools are used to monitor the servers and many other interesting stuff behind the scenes.

It is still common to store IP addresses as a varchar(15) field though it is possible to use integer type instead. Unlike the varchar type, integer has fixed size and uses only 4 bytes.

INET_ATON() is used to convert an IP address to a number and INET_NTOA() – for the reverse operation.

SELECT INET_ATON('127.0.0.1');
SELECT INET_NTOA(2130706433);

It is important to use INT UNSIGNED with INET_ATON() so that IP addresses for which the first octet is greater than 127 is stored correctly.

Also PHP has similar functions – ip2long() and long2ip(). However ip2long() function may return negative results in certain cases. To make it always positive unsigned intereger ip2long() call has to be paired with printf() or sprintf() function:

printf('%u', ip2long('128.0.0.1');

Thus plan the IP address column datatype accordingly.

PROCEDURE ANALYSE examines the result from a query and returns an analysis of the results that suggests optimal data types for each column that may help reduce table sizes. Simply append PROCEDURE ANALYSE to the end of a select statement:

SELECT f1, f2 FROM t PROCEDURE ANALYSE(20, 2000);

where

• 10 is is the maximum number of distinct values which is checked for columns;
• 2000 is the maximum amount of memory which can be allocated per column while trying to find all distinct values.

This MySQL feature can be helpful after importing new data or for checking your existing tables to verify whether columns datatype was designed providently.