Mysql and integers: INT, TINYINT and all that jazz

Today I stumbled on a discussion here where I work about what’s the best INT field in Mysql to represent boolean values and about what’s the real meaning of the *INT(number) definition.

So, I ended looking in the online Mysql manual for answer but also in the “High Performance MySQL 2nd edition” written by worldwide-fame MySQL hackers (and published by O’Really).

To resume, as Mysql Manual says: BOOLEAN is an alias for TINYINT(1), so you can use both, although BIT could be a better suited solution.

But what about the parenthesis thingie? Here there are two different opinions on the matter. According to High Performance Mysql’s authors (page 82, emphasis’ mine):

MySQL lets you specify a “width” for integer types, such as INT(11). This is meaningless for most applications: it does not restrict the legal range of values, but simply specifies the number of characters MySQL’s interactive tools (such as commandline client) will reserve for display purposes. For storage and computational purposes, INT(1) is identical to INT(20)

but now let’s look at this other page in Mysql Manual (emphasis mine):

When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, because in these cases MySQL assumes that the data fits into the original column width.

Now…who’s right? I use to trust in the Percona & OpenQuery crew but anyway the official Mysql Manual seems pretty clear about some cases in which the INT(x) value is important.
Any idea?

Postfix as relay to a SMTP requiring authentication

Sometimes you may in need to use an external SMTP provider to send your emails, and usually ISPs give instruction on how to configure mail clients such as Outlook or Thunderbird. But what if you are already using an internal SMTP server such as Postfix?

These guidelines are for Debian (but may be helpful with other systems as well) and are related to Postfix. The SMTP provider in the example is AuthSMTP which is a well known provider for SMTP relaying.

Given you already have a working Postfix environment, first of all edit your main.cf and add these lines:

relayhost = [mail.authsmtp.com]
smtp_sasl_auth_enable=yes
smtp_sasl_password_maps=hash:/etc/postfix/sasl-passwords
smtp_sasl_mechanism_filter = digest-md5
smtp_sasl_security_options=


then, create with $EDITOR a file called /etc/postfix/sasl-passwords and fill it with something like this:

[mail.authsmtp.com] yourusername:yourpassword

then, compile the map file

# postmap hash:/etc/postfix/sasl-passwords

now we are almost done, just restart postfix and it should work.

Now, probably it won’t really work and you’ll start to see messages like these in your postfix log:

warning: SASL authentication failure: No worthy mechs found
SASL authentication failed; cannot authenticate to server mail.authsmtp.com

that’s because you are missing some SASL packages from Debian. Issue

# aptitude install libsasl2-modules

and it should install all the missing packages and make the thing work :)