Sql error 1406 sqlstate 22001

Fix for MySQL ERROR 1406: Data too long for column” but it shouldn’t be?

This error can occur if you try to set data higher than the allowed limit. As an example, you cannot store a string in a column of type bit because varchar or string takes size higher than bit data type.

You need to use the following syntax for bit type column:

To understand the above syntax, let us create a table. The query to create a table is as follows:

Insert some records in the table using insert command. The query to insert record is as follows:

Display all records from the table using select statement. The query is as follows:

The following is the output:

The actual sample output snapshot is as follows:

The error is the following as discussed above. It gets generated in the below query:

To avoid the above error, you need to prefix b before ‘1’. Now the query is as follows:

Check the table records once again using select statement. The query is as follows:

The following is the output:

The actual sample output snapshot is as follows:

Look at the is Student column.

Now we will update the same id with the value 0. This will give a blank value with corresponding Id. The query is as follows:

Check the record of particular row updated above. Here is the Id 9. Now row has been updated with record Id 9. The query is as follows:

Источник

TablePlus

Data truncation error 1406 — Data too long for column …

September 16, 2019

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

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 :

And you insert the first row of data:

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:

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:

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.

Источник

Fix MYSQL ERROR 1406 (22001) at line.

Hello, I’m following your tutorial on how to import databases in mysql

I receive the following error after attempting to import:

ERROR 1406 (22001) at line 5450: Data too long for column ‘IP’ at row 1

I suspect the error is due to how the import is executed: By creating a blank database first, then importing.

Is there a way to import the database without creating a blank one beforehand?

This is for a WordPress database.

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

It’s highly unlikely the issue to be related to the database existing on the instance when importing it.

This issue is with the structure of the database. What I’ll suggest is to update the IP column to let’s say be a longer varchar structure. Like varchar (30) for instance. Once you have done that, you can export the database again and import it into your current setup.

Popular Topics

Questions

Sign up for Infrastructure as a Newsletter.

Working on improving health and education, reducing inequality, and spurring economic growth? We’d like to help.

You get paid; we donate to tech nonprofits.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow – whether you’re running one virtual machine or ten thousand.

Источник

Sql error 1406 sqlstate 22001

I have an application which connects to SQL through ODBC 11.

ODBC statement is :

SELECT PID
FROM PENTITY PENTITY01 WHERE ((NUM1 NOT BETWEEN ? + 10.7895 AND ? + 200.6734 AND NUM2 NOT IN (5996/ 8, ? — 89.3892, ? + 80.7543))

and the SQLBindparameter statement is :

static UCHAR num1[12]=12.589

rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, sqlType, precision, scale,
&num1, sizeof(num1), NULL);

With this SQLBindparameter statement I am getting error, It is working without any error if I change the value to 12.

The same code is working when connecting to SQL server 2008.

Thanks in advance.

Thank you for your question. I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

Thank you for your understanding and support.

Elvis Long
TechNet Community Support

During my research the difination for «UCHAR», it’s an integer type.

A UCHAR is an 8-bit integer with the range: 0 through 255 decimal. Because a UCHAR is unsigned, its first bit (Most Significant Bit (MSB)) is not reserved for signing.

This type is declared as follows: typedef unsigned char UCHAR, *PUCHAR;

I don’t know why your code in SQL Server 2008 R2 is working. I’m not sure whether it is casted automaticlly. I will do more research for this issue.

Have a good day.

From my research, I found:

«[Microsoft][ODBC SQL Server Driver] String data right truncation » error may be returned from a call to
SQLBindParameter if the size of the string parameter being used is greater than the size of the column being compared to. In other words if the string size of the to the left of the is less than the string size of the to the right , ODBC may return this error.

The resolution is to make the string size of the to the right of the less than or equal to the string size of the on the left.

It is difficult to track down this type of problem when third party development applications are being used. ODBC Trace can be used to help determine if this problem is occuring.

Here is an example where the customer has submitted a query «select count(*) from type1 where type1 = ?», type1 is varchar(5) and the data type being passed by the application is char[9].

Here is the relevant portion of the trace. The following information from the «exit» of SQLDescribeParam

SWORD * 0x0095e898 (12)

UDWORD * 0x0095e880 (5)

Maps to the following with the actual value in parenthesis — SQL_VARCHAR Size 5:

The «exit» value from SQLBindParameter provides the following
information:

SWORD 1
SWORD 1
SQL Data Type SWORD 12
Parameter Size UDWORD 5
SWORD 0
Value PTR 0x0181c188
Value Buffer Size SDWORD 5
String Length SDWORD * 0x0181c103 (9)

The string length parameter is the length of the string being bound to the parameter, in this instance there is a size mismatch which results in the SQLError and the SQLErrorW with the message «[Microsoft][ODBC SQL Server
Driver] String data right truncation » .

Источник

Export to relational database fails with SQL error: SQLCODE=-302, SQLSTATE=22001

Troubleshooting

Problem

When you export crawled, analyzed, or searched documents to a relational database, the export fails and the message “DB2 SQL error: SQLCODE=-302, SQLSTATE=22001” is written to the system log in the ES_NODE_ROOT/logs directory.

Cause

The length of an exported field or facet value is longer than the length of the corresponding column in the database table.

Resolving The Problem

You can resolve this problem in one of the following ways:

• Configure IBM Cognos Content Analytics to truncate any exported field or facet value that is longer than the length of the of the corresponding column in the database table. In your database mapping file, set the value of the policy property to truncate.

Restriction: IBM Cognos Content Analytics does not truncate binary content. If your binary content exceeds 1 MB, you must recreate the database table to solve this problem.
• Recreate the database table so that its columns are large enough to contain the exported field or facet values.

To recreate the database table:

1. Determine which table needs to be recreated by checking the SQL error message in the export audit log in the ES_NODE_ROOT/logs/audit directory. For example, the following message indicates that the DEVICEAVAILABILITY column from the ESADMIN.DEVICEAVAILABILITY table is not long enough to store the corresponding field values.

11/27/09 06:05:06.922 GMT+09:00 [Error] [ES_INFO_GENERAL_AUDIT_INFO] [] []
serverx.ibm.com:4368:120:X’0′:bd.java:com.ibm.db2.jcc.am.bd.a:668
FFQX0717I 251658517
com.ibm.db2.jcc.am.co: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null,
DRIVER=3.57.82
com.ibm.db2.jcc.am.co: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null,
DRIVER=3.57.82
at com.ibm.db2.jcc.am.bd.a(bd.java:668)
.
at com.ibm.db2.jcc.am.lm.executeQuery(lm.java:628)
at com.ibm.es.oze.export.rdb.query.dml.Search.execute(Search.java:82)
at java.lang.Thread.run(Thread.java:736)

11/27/09 06:05:06.922 GMT+09:00 [Warning] [ES_INFO_GENERAL_AUDIT_INFO] [] []
serverx.ibm.com:4368:120:X’0′:DB2Utils.java:com.ibm.es.oze.export.rdb.utils.DB2Utils.d
LogSQLException:157
FFQX0717I SELECT «ID»,»DEVICEAVAILABILITY» FROM «ESADMIN».»DEVICEAVAILABILITY» WHERE
«DEVICEAVAILABILITY»=?

In your database mapping file, increase the length of the column in the appropriate table definition. In our example, the database mapping file contains the following definition for the DEVICEAVAILABILITY column in the ESADMIN.DEVICEAVAILABILITY table:

Increase the length of the DEVICEAVAILABILITY column by modifying the size that is specified for the type attribute. For example, change type=»CHAR(4)» to type=»CHAR(6)».

If you receive this error for a column that contains binary content, ensure that you specify a size for the BLOB column in its table definition. For example, change type=“BLOB» to type=“BLOB(10M)». If no size is specified, DB2 by default creates a column that can store 1 MB.

Also ensure that you specify a column size that is large enough to store your binary content data. For example, if you crawl files that are no more than 5 MB, set the BLOB column size to 5 MB. If you do not know the size of the data, you can ensure that the column is large enough by specifying 32 MB as the column size because the maximum page size that can be crawled is 32 MB. However, specifying 32 MB as the column size might unnecessarily consume database server resources if most of the crawled data is less than 32 MB.

Источник

1. How to configure phpMyAdmin with automatic login by setting auth_type to config.

2. How to create new users in MySQL and control their permissions for better security.

3. How to generate sitemaps dynamically using PHP.

4. How to perform simple SELECT statements in SQL to communicate with SQL databases.

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

More in: MySQL

This error can occur if you try to set data higher than the allowed limit. As an example, you cannot store a string in a column of type bit because varchar or string takes size higher than bit data type.

You need to use the following syntax for bit type column:

anyBitColumnName= b ‘1’
OR
anyBitColumnName= b ‘0’

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table IncasesensitiveDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(10),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.70 sec)

Insert some records in the table using insert command. The query to insert record is as follows:

mysql> insert into ErrorDemo(Name,isStudent) values('John',1);
Query OK, 1 row affected (0.18 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('Sam',0);
Query OK, 1 row affected (0.21 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('Mike',0);
Query OK, 1 row affected (0.16 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('Larry',1);
Query OK, 1 row affected (0.23 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('Carol',1);
Query OK, 1 row affected (0.11 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('Robert',0);
Query OK, 1 row affected (0.17 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('James',1);
Query OK, 1 row affected (0.18 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('Bob',1);
Query OK, 1 row affected (0.19 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('David',1);
Query OK, 1 row affected (0.15 sec)
mysql> insert into ErrorDemo(Name,isStudent) values('Ricky',0);
Query OK, 1 row affected (0.17 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from ErrorDemo;

The following is the output:

+----+--------+-----------+
| Id | Name   | isStudent |
+----+--------+-----------+
|  1 | John   |           |
|  2 | Sam    |           |
|  3 | Mike   |           |
|  4 | Larry  |           |
|  5 | Carol  |           |
|  6 | Robert |           |
|  7 | James  |           |
|  8 | Bob    |           |
|  9 | David  |           |
| 10 | Ricky  |           |
+----+--------+-----------+
10 rows in set (0.00 sec)

The actual sample output snapshot is as follows:

The error is the following as discussed above. It gets generated in the below query:

mysql> update ErrorDemo set isStudent='1' where Id=9;
ERROR 1406 (22001): Data too long for column 'isStudent' at row 1

To avoid the above error, you need to prefix b before ‘1’. Now the query is as follows:

mysql> update ErrorDemo set isStudent=b'1' where Id=9;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Check the table records once again using select statement. The query is as follows:

mysql> select *from ErrorDemo;

The following is the output:

+----+--------+-----------+
| Id | Name   | isStudent |
+----+--------+-----------+
|  1 | John   |           |
|  2 | Sam    |           |
|  3 | Mike   |           |
|  4 | Larry  |           |
|  5 | Carol  |           |
|  6 | Robert |           |
|  7 | James  |           |
|  8 | Bob    |           |
|  9 | David  |           |
| 10 | Ricky  |           |
+----+--------+-----------+
10 rows in set (0.00 sec)

The actual sample output snapshot is as follows:

Look at the is Student column.

Now we will update the same id with the value 0. This will give a blank value with corresponding Id. The query is as follows:

mysql> update ErrorDemo set Name='Maxwell', isStudent=b'0' where Id=9;
Query OK, 1 row affected (0.16 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Check the record of particular row updated above. Here is the Id 9. Now row has been updated with record Id 9. The query is as follows:

mysql> select *from ErrorDemo where Id=9;

The following is the output:

+----+---------+-----------+
| Id | Name    | isStudent |
+----+---------+-----------+
|  9 | Maxwell |           |
+----+---------+-----------+
1 row in set (0.00 sec)