ERROR 1406 (22001): Data too long for column

The error happens because the input data was too long for the column in a database table.

For example, if you defined the column width with varchar(100), and the data is more than 100 characters long, you will get the error:

ERROR 1406 (22001): Data too long for column

This error can also occur when a script is attempting to insert a string in a bit column. For example, a developer might accidentally have written '1' instead of 1 — when inserting bit values, the value should not be quoted, as this would cause MySQL to treat it as a string rather than a bit. A single character might be 8 bits long, while a single bit is just 1 bit long, and hence the error Data too long for column is triggered.

Solutions

To solve the problem, you should be sure that the data does not exceed the column width in the database table before submitting it. This can be done with a combination of strlen and substr

// If the data is longer than the column width, it is violently cut off!
$user_agent ?? $_SERVER['HTTP_USER_AGENT'];
if (strlen($user_agent) > 255) {
  $user_agent = substr($user_agent,0,255);
}

We can also increase the width of the varchar column; in order to increase the width to 500 characters, we can execute the following SQL query:

alter table request_log modify column user_agent varchar(500);

Debugging errors in general

The problem when debugging these errors often is that the CMS you are using might not report the exact error on the front-end, so you will have to track down the error message manually. This can be difficult, because you need to find the exact line in your code where the error is triggered.

Sometimes you might not even be able to tell what exactly the error is, since the front-end error message that is shown is a catch-all 500 - Internal Server Error. So, in order to debug the problem, you will often have to dig through the error log file. If you are using Apache, the website error log will often be located at: /var/log/apache2/my_site_name_com-error.log

Of course, finding the relevant error in the log might prove difficult. However, if you are able to consistently reproduce the error by sending a specific request, you could simply filter for your own IP address:

grep 'xxx.xxx.xxx.xxx' /var/log/apache2/my_site_name_com-error.log

This should tell you the exact line where the error occurred in your PHP code, and allow you to do further debugging.

Another nice trick is to add a "test" request parameter, and then you can perform debugging even on the live server, without effecting users. I.e.:

if (isset($_GET['debugging'])) {
  var_dump($variable_to_debug);
  exit();
}

Finally, if you are worried about a user guessing the test parameter, you can just add a check for you IP address; this will avoid the risk that someone enters in the "debugging" parameter and sees something they should not.

Tell us what you think: