Ошибка 1406 mysql - Решение и исправление самых разных ошибок на TopOshibok.ru

Error Code: 1406. Data too long for column

CREATE  TABLE `TEST` 
(

  `idTEST` INT NOT NULL ,

  `TESTcol` VARCHAR(45) NULL ,

  PRIMARY KEY (`idTEST`) 
);

Now Insert some values

INSERT INTO TEST
VALUES
(
    1,
    'Vikas'
)

select 

SELECT * FROM TEST;

Inserting record more than the length

INSERT INTO TEST
VALUES
(
    2,
    'Vikas Kumar Gupta Kratika Shukla Kritika Shukla'
)

If we select the length

SELECT LENGTH('Vikas Kumar Gupta Kratika Shukla Kritika Shukla')

 '47'

And it is showing the error message

Error Code: 1406. Data too long for column

But what is my expectation is, I want to insert at least first 45 characters in Table

please let me know if the question is not clear.

I know the cause of this error. I am trying to insert values more than the length of datatype.

I want solution in MySQL as It is possible in MS SQL. So I hope it would also be in MySQL.

asked Apr 11, 2013 at 12:39

MySQL will truncate any insert value that exceeds the specified column width.

to make this without error try switch your SQL mode to not use STRICT.

Mysql reference manual

EDIT:

To change the mode

This can be done in two ways:

Open your my.ini (Windows) or my.cnf (Unix) file within the MySQL installation directory, and look for the text «sql-mode».

Find:

Code:

# Set the SQL mode to strict 
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Replace with:

Code:

# Set the SQL mode to strict 
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

You can run an SQL query within your database management tool, such as phpMyAdmin:

Code:

SET @@global.sql_mode= '';

PHA

1,5885 gold badges18 silver badges37 bronze badges

answered Apr 11, 2013 at 12:56

echo_Meecho_Me

37.1k5 gold badges58 silver badges78 bronze badges

I think that switching off the STRICT mode is not a good option because the app can start losing the data entered by users.

If you receive values for the TESTcol from an app you could add model validation, like in Rails

validates :TESTcol, length: { maximum: 45 }

If you manipulate with values in SQL script you could truncate the string with the SUBSTRING command

INSERT INTO TEST
VALUES
(
    1,
    SUBSTRING('Vikas Kumar Gupta Kratika Shukla Kritika Shukla', 0, 45)
);

answered Oct 27, 2016 at 12:23

Hirurg103Hirurg103

4,7832 gold badges34 silver badges50 bronze badges

This happened to me recently.
I was fully migrate to MySQL 5.7, and everything is in default configuration.

All previously answers are already clear and I just want to add something.

This 1406 error could happen in your function / procedure too and not only to your table’s column length.

In my case, I’ve trigger which call procedure with IN parameter varchar(16) but received 32 length value.

I hope this help someone with similar problem.

answered Apr 22, 2020 at 9:58

Besides the answer given above, I just want to add that this error can also occur while importing data with incorrect lines terminated character.

For example I save the dump file in csv format in windows. then while importing

LOAD DATA INFILE '/path_to_csv_folder/db.csv' INTO TABLE table1
 FIELDS TERMINATED BY ',' 
 ENCLOSED BY '"'
 ESCAPED BY '"'
 LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Windows saved end of line as \r\n (i.e. CF LF) where as I was using \n. I was getting crazy why phpMyAdmin was able to import the file while I couldn’t. Only when I open the file in notepadd++ and saw the end of file then I realized that mysql was unable to find any lines terminated symbol (and I guess it consider all the lines as input to the field; making it complain.)

Anyway after making from \n to \r\n; it work like a charm.

LOAD DATA INFILE '/path_to_csv_folder/db.csv' INTO TABLE table1
 FIELDS TERMINATED BY ',' 
 ENCLOSED BY '"'
 ESCAPED BY '"'
 LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

answered Sep 23, 2020 at 10:01

This is a step I use with ubuntu. It will allow you to insert more than 45 characters from your input but MySQL will cut your text to 45 characters to insert into the database.

Run command

sudo nano /etc/mysql/my.cnf
Then paste this code

[mysqld]
sql-mode=»NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION»
restart MySQL

sudo service mysql restart;

answered Sep 3, 2019 at 17:58

JoeJoe

8111 gold badge8 silver badges14 bronze badges

Since this question is prioritized on search-results, I will quickly say that you can pre-truncate your data before saving using substr(); then move on to the more serious issue of saving large data resulting in Error Code: 1406. Data too long for column.

I disagree with all answers and comments advising on turning off the strict mode. The presumption is, data that needs saving must be saved — not left to the chance of mysteriously disappearing at will without notice. Good table structure is advised but if you must save any large data, you can change the column’s capacity to:

TEXT: 65,535 characters - 64 KB
MEDIUMTEXT: 16,777,215 - 16 MB
LONGTEXT: 4,294,967,295 characters - 4 GB

answered Dec 14, 2021 at 13:33

AjowiAjowi

4593 silver badges12 bronze badges

Try to check the limits of your SQL database. Maybe you’r exceeding the field limit for this row.

answered Jul 23, 2015 at 14:49

TerranologyTerranology

62010 silver badges15 bronze badges

I got the same error while using the imagefield in Django.
post_picture = models.ImageField(upload_to='home2/khamulat/mydomain.com/static/assets/images/uploads/blog/%Y/%m/%d', height_field=None, default=None, width_field=None, max_length=None)

I just removed the excess code as shown above to post_picture = models.ImageField(upload_to='images/uploads/blog/%Y/%m/%d', height_field=None, default=None, width_field=None, max_length=None) and the error was gone

answered Oct 7, 2020 at 14:42

mirunimiruni

8711 bronze badges

I got this error after creating my table structure first with a primary key set then trying to upload a csv file. My CSV file had information in the primary key column. It was an export from another sql server. No matter how I tried to export and import, it wouldn’t work.

What I did to solve it was to drop my primary key column in my db and my csv, upload, then add my primary key column back.

answered Mar 3, 2021 at 21:34

Although the answers above indicate to update my.ini file, but I feel it would be better to alter column lengeth to TEXT or LONGTEXT, so that any higher length can be added.

answered Aug 7, 2022 at 15:09

I have also faced the same problem when I am trying to save 0 in a Boolean type column but mistakenly I was passing 0 as an string.

Make sure you will pass the correct data with satisfying datatype.

In my case I was trying to save ‘0’ instead of 0 in a Boolean type column.

answered Jul 16 at 10:01

Go to your Models and check, because you might have truncated a number of words for that particular column eg. max_length=»150″.

answered Jun 18, 2017 at 12:38

Источник

When inserting values to a table in MySQL, you might run into this error:

Error Code: 1406. Data too long for column

That error message means you are inserting a value that is greater than the defined maximum size of the column.

The solution to resolve this error is to update the table and change the column size.

Example

We have a simple table employees:

CREATE TABLE employees (
 id int(5),
 full_name varchar(5)
);

And you insert the first row of data:

INSERT INTO employees
 VALUES(1, 'Alex with an extra long full name, longer than 10');

Since the full_name value is longer than the defined length, MySQL will throw the error Data too long for column. To resolve that, modify the column size:

ALTER TABLE employees 
  MODIFY COLUMN full_name varchar(50);

Another workaround is to disable the STRICT mode, MySQL will automatically truncate any insert values that exceed the specified column width instead of showing the error. The trade-off is, you might not be aware that your data won’t fit and end up losing original data.

To disable the STRICT mode, you can:

Edit your MySQL configuration file:

Open your my.ini (Windows) or my.cnf (Unix) file and look for “sql-mode”, and remove the tag STRICT_TRANS_TABLES

Or execute this command:

SET @@global.sql_mode= '';

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS

Источник

The Error Code: 1406 in MySQL is thrown when a value being inserted into a column exceeds the maximum length specified for the column data type. This can occur when a string value is being inserted into a VARCHAR column, for example, and the string length exceeds the maximum length defined for the column. The error message «Data too long for column» indicates that the data being inserted is too long for the defined column data type.

Method 1: Increase the Column Length

To fix the MySQL Error Code: 1406 — Data too long for column, you can increase the column length of the respective column. Here’s how you can do it:

First, you need to find out the name of the table and the column that is causing the error. You can do this by running the following SQL query:

This will display the error message along with the name of the table and column causing the error.
Once you have identified the column causing the error, you can increase its length. You can do this using the ALTER TABLE statement. Here’s an example:
```
ALTER TABLE table_name MODIFY column_name VARCHAR(255);
```
In this example, we are increasing the length of the column_name column in the table_name table to 255 characters.
After you have increased the column length, you can try inserting the data again. If the error persists, you may need to increase the column length further.
```
ALTER TABLE table_name MODIFY column_name VARCHAR(500);
```
In this example, we are increasing the length of the column_name column in the table_name table to 500 characters.
Keep increasing the column length until the error disappears.
```
ALTER TABLE table_name MODIFY column_name VARCHAR(1000);
```
In this example, we are increasing the length of the column_name column in the table_name table to 1000 characters.

Remember to adjust the column length according to your needs. It is important to note that increasing the column length can have implications on the performance and storage requirements of your database. So, it is always better to keep the column length as short as possible to avoid any unnecessary overhead.

Method 2: Truncate the Data to Fit the Column Length

If you are encountering the «Error Code: 1406. Data too long for column» in MySQL, you can fix it by truncating the data to fit the column length. Here are the steps to do it:

Identify the column that is causing the error.
Determine the maximum length of the column by running the following query:

SHOW COLUMNS FROM table_name WHERE Field = 'column_name';

Replace table_name and column_name with the actual table and column names.

Update the data in the column by truncating it to fit the maximum length of the column. You can use the SUBSTR() function to truncate the data. Here is an example query:

UPDATE table_name SET column_name = SUBSTR(column_name, 1, max_length) WHERE LENGTH(column_name) > max_length;

Replace table_name, column_name, and max_length with the actual table name, column name, and maximum length of the column.

Repeat the process for any other columns that are causing the error.

By following these steps, you should be able to fix the «Error Code: 1406. Data too long for column» issue in MySQL by truncating the data to fit the column length.

Method 3: Change the Column Data Type

To fix the Error Code: 1406. Data too long for column in MySQL by changing the column data type, you can follow these steps:

Identify the column that is causing the error. You can do this by looking at the error message or by checking the table structure.
Determine the maximum length of data that can be stored in the column. You can do this by checking the data type and the column definition.
Modify the column data type to allow for a longer length of data. For example, if the column is currently defined as VARCHAR(10), you can change it to VARCHAR(20) to allow for more characters.
Alter the table to apply the changes to the column. You can use the ALTER TABLE statement to modify the column data type.

Here is an example code to change the column data type:

-- Identify the column causing the error
DESCRIBE my_table;

-- Modify the column data type
ALTER TABLE my_table MODIFY my_column VARCHAR(20);

-- Apply the changes to the table
DESCRIBE my_table;

In this example, we are modifying the my_column column in the my_table table to allow for a longer length of data. We are changing the data type from VARCHAR(10) to VARCHAR(20).

Note that changing the column data type may affect the existing data in the column. Make sure to backup your data before making any changes to the table structure.

Method 4: Use Stored Procedures with Dynamic SQL

How to Fix Error Code 1406 «Data too long for column» in MySQL using Stored Procedures with Dynamic SQL

To fix the «Data too long for column» error in MySQL, you can use stored procedures with dynamic SQL. Here are the steps to follow:

Create a stored procedure that accepts the table name, column name, and new column length as parameters.

DELIMITER //
CREATE PROCEDURE `change_column_length`(IN `table_name` VARCHAR(255), IN `column_name` VARCHAR(255), IN `new_length` INT)
BEGIN
    SET @query = CONCAT('ALTER TABLE ', table_name, ' MODIFY COLUMN ', column_name, ' VARCHAR(', new_length, ')');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Call the stored procedure and pass the table name, column name, and new column length as arguments.

CALL change_column_length('my_table', 'my_column', 50);

This will modify the length of the «my_column» column in the «my_table» table to 50 characters.

Note: Make sure to backup your data before making any changes to your database.

That’s it! This method should help you fix the «Data too long for column» error in MySQL.

Источник

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

7937 views

By. Jacob

Edited: 2020-08-29 11:02

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. 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.

How to configure phpMyAdmin with automatic login by setting auth_type to config.
How to create new users in MySQL and control their permissions for better security.
How to generate sitemaps dynamically using PHP.
How to perform simple SELECT statements in SQL to communicate with SQL databases.
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.

Kickstart Your Career

Get certified by completing the course

Get Started

Источник