After my entry yesterday about MySQL truncating data, several people have pointed out that MySQL 4.1 or later gives you a warning. Yes, this is true. You can even see it in the example I gave:

Query OK, 1 row affected, 1 warning (0.00 sec)

I ignored mentioning this, but perhaps should have said something about it. I reason I didn't mention it was because I didn't feel that a warning really helped anyone. Developers have enough problems remembering to check for errors, let along remembering to check in case there was a warning as well. Plus, they'd then have to work out if the warning was something serious or something they could ignore. There's also the question of how well the language bindings present this information. Take for example, PHP. The mysqli extension gained support for checking for warnings in PHP5 and gives the following code as an example of getting warnings:

mysqli_query($link, $query);

if (mysqli_warning_count($link)) {
   if ($result = mysqli_query($link, "SHOW WARNINGS")) {
      $row = mysqli_fetch_row($result);
      printf("%s (%d): %s\n", $row[0], $row[1], $row[2]);
      mysqli_free_result($result);
   }
}

Hardly concise code. As of 5.1.0, there is also mysqli_get_warnings(), but is undocumented beyond noting its existence. The MySQL extension does not support getting warning information. The PDO wrapper doesn't provide any way to get this information.

In perl, DBD::mysql has a mysql_warning_count() function, but presumably would have to call "SHOW WARNINGS" like in the PHP example. Seems Python's MYSQLdb module will raise an exception on warnings in certain cases. Mostly using the Cursor object.

In java, you can set the jdbcCompliantTruncation connection parameter to make the driver throw java.sql.DataTruncation exceptions, as per the JDBC spec, which makes you wonder why this isn't set by default. Unfortunately this setting is usually outside the programmer's control. There is also the java.sql.Statement.getWarnings(), but once again, you need to check this after every statement. Not sure if ORM tools like Hibernate check this or not.

So, yes MySQL does give you a warning, but in practice is useless.

MySQL in its standard configuration has this wonderful "feature" of truncating your data if it can't fit in the field.

mysql> create table foo (bar varchar(4));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo (bar) values ("12345");
Query OK, 1 row affected, 1 warning (0.00 sec)

In comparison, PostgeSQL does:

psql=> create table foo (bar varchar(4));
CREATE TABLE
psql=> insert into foo (bar) values ('12345');
ERROR:  value too long for type character varying(4)

You can make MySQL do the right thing by setting the SQL Mode option to include STRICT_TRANS_TABLES or STRICT_ALL_TABLES. The difference is that the former will only enable it for transactional data storage engines. As much as I'm loathed to say it, I don't recommend using STRICT_ALL_TABLES, as an error during updating a non-transational table will result in a partial update, which is probably worse than a truncated field. Setting the mode to TRADITIONAL includes both these and a couple of related issues (NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO) You can set the mode using:

• On the command line:

  --sql-mode="TRADITIONAL"

• In /etc/mysql/my.cnf:

  sql-mode="TRADITIONAL"

• At runtime:

  SET GLOBAL sql_mode="TRADITIONAL"
  SET SESSION sql_mode="TRADITIONAL"

Just say no to databases that happily throw away your data