Beamtic's logo
  1. Create user
Home

Share via:

MySQL ERROR 1406: Data too long for column

How to deal with the 1406 (22001) Data too long for column error message.

50 views

Edited: 2020-07-23 17:26

ERROR 1406 (22001): Data too long for column

This error happens because you are trying to insert data that is longer than the column width.

There is at least a few solutions to this problem.

We can truncate the data, cutting off the data that goes beyond the column boundary; this is however not ideal, as it might lead to data loss and data corruption, and it does not deal with the underlying reason for the problem very effectively - it just moves it beyond the horizon. So, what can we do instead?

Assuming you got access to the PHP code, you should most likely be validating the data length before submitting it to the database. Doing this will ensure that the data is never too long for the column.

You can also make the column itself longer in order to fit more data into the column, this can be done with the alter table query:

alter table request_log modify column user_agent varchar(500);

The maximum size of a varchar column is 65.535 characters.

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, as a developer might accidentally have written '1' instead of 1; when inserting bit values, the value should not be quoted, as that would cayse 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.

Comments

  1. How to create a MySQL Database with UTF-8 encoding.
  2. How to perform simple SELECT statements in SQL to communicate with SQL databases.
  3. How to create new users in MySQL and control their permissions for better security.
  4. The error happens when importing database backups using the SOURCE command, either because you got the path wrong, or because you used the command incorrectly.
  5. How to correctly solve a problem with MySQL Server that prevents logging in as root.

More in: MySQL