It’s strange to encounter a maintenance problem today:

Execute SQL statement insert into test.COLUMNS select * from information_ schema.COLUMNS ; error 1406 (22001): data too long, field len 30, data len 48

The background of the problem is like this:

1. Tidb4.0 distributed database system, de information_ schema.TABLES Copy a table structure as like as two peas, then build a blank table to a temporary library CSDN.

2. Execute insert into on the command line of tidb test.COLUMNS select * from information_ schema.COLUMNS ;

3. Try mysqldump from information_ If you export data from the tables table of the schema and import it into the tables table of the CSDN library, you will still report an error.

4. Two libraries information were checked_ Schema and CSDN libraries have the same table structure.

If there is an error, it should be that the field is only 30 long, but the entered data lacks 48 strings. Then check the table structure carefully

CREATE TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) DEFAULT NULL,
  `TABLE_SCHEMA` varchar(64) DEFAULT NULL,
  `TABLE_NAME` varchar(64) DEFAULT NULL,
  `COLUMN_NAME` varchar(64) DEFAULT NULL,
  `ORDINAL_POSITION` bigint(64) DEFAULT NULL,
  `COLUMN_DEFAULT` text DEFAULT NULL,
  `IS_NULLABLE` varchar(3) DEFAULT NULL,
  `DATA_TYPE` varchar(64) DEFAULT NULL,
  `CHARACTER_MAXIMUM_LENGTH` bigint(21) DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint(21) DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) DEFAULT NULL,
  `NUMERIC_SCALE` bigint(21) DEFAULT NULL,
  `DATETIME_PRECISION` bigint(21) DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
  `COLLATION_NAME` varchar(32) DEFAULT NULL,
  `COLUMN_TYPE` text DEFAULT NULL,
  `COLUMN_KEY` varchar(3) DEFAULT NULL,
  `EXTRA` varchar(30) DEFAULT NULL,
  `PRIVILEGES` varchar(80) DEFAULT NULL,
  `COLUMN_COMMENT` varchar(1024) DEFAULT NULL,
  `GENERATION_EXPRESSION` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

If you see that a field extra is’ extra ‘varchar (30) default null, check whether the value of the data in this field exceeds the value.

mysql-17:31:41> select length(EXTRA) l,EXTRA from information_schema.TABLES order by l desc limit 50;
+----+--------------------------------------------------+
| l  | EXTRA                                            |
+----+--------------------------------------------------+
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |

Check the length (extra). The maximum length is 48. Is that the reason for the character set?In a database like mysql, the character set is different and the space occupied is different.

First look at the character set of the library. It’s the same:

+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| csdn     | CREATE DATABASE `csdn` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+

+--------------------+--------------------------------------------------------------------------------+
| Database           | Create Database                                                                |
+--------------------+--------------------------------------------------------------------------------+
| INFORMATION_SCHEMA | CREATE DATABASE `INFORMATION_SCHEMA` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+--------------------+--------------------------------------------------------------------------------+

Let’s look at the definition of character set in the following table again. Is it the same

use information_schema;
CREATE TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) DEFAULT NULL,
  `TABLE_SCHEMA` varchar(64) DEFAULT NULL,
  `TABLE_NAME` varchar(64) DEFAULT NULL,
  `COLUMN_NAME` varchar(64) DEFAULT NULL,
  `ORDINAL_POSITION` bigint(64) DEFAULT NULL,
  `COLUMN_DEFAULT` text DEFAULT NULL,
  `IS_NULLABLE` varchar(3) DEFAULT NULL,
  `DATA_TYPE` varchar(64) DEFAULT NULL,
  `CHARACTER_MAXIMUM_LENGTH` bigint(21) DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint(21) DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) DEFAULT NULL,
  `NUMERIC_SCALE` bigint(21) DEFAULT NULL,
  `DATETIME_PRECISION` bigint(21) DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
  `COLLATION_NAME` varchar(32) DEFAULT NULL,
  `COLUMN_TYPE` text DEFAULT NULL,
  `COLUMN_KEY` varchar(3) DEFAULT NULL,
  `EXTRA` varchar(30) DEFAULT NULL,
  `PRIVILEGES` varchar(80) DEFAULT NULL,
  `COLUMN_COMMENT` varchar(1024) DEFAULT NULL,
  `GENERATION_EXPRESSION` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


use csdn;
CREATE TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) DEFAULT NULL,
  `TABLE_SCHEMA` varchar(64) DEFAULT NULL,
  `TABLE_NAME` varchar(64) DEFAULT NULL,
  `COLUMN_NAME` varchar(64) DEFAULT NULL,
  `ORDINAL_POSITION` bigint(64) DEFAULT NULL,
  `COLUMN_DEFAULT` text DEFAULT NULL,
  `IS_NULLABLE` varchar(3) DEFAULT NULL,
  `DATA_TYPE` varchar(64) DEFAULT NULL,
  `CHARACTER_MAXIMUM_LENGTH` bigint(21) DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint(21) DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) DEFAULT NULL,
  `NUMERIC_SCALE` bigint(21) DEFAULT NULL,
  `DATETIME_PRECISION` bigint(21) DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
  `COLLATION_NAME` varchar(32) DEFAULT NULL,
  `COLUMN_TYPE` text DEFAULT NULL,
  `COLUMN_KEY` varchar(3) DEFAULT NULL,
  `EXTRA` varchar(30) DEFAULT NULL,
  `PRIVILEGES` varchar(80) DEFAULT NULL,
  `COLUMN_COMMENT` varchar(1024) DEFAULT NULL,
  `GENERATION_EXPRESSION` text NOT NULL,
  KEY `idx_pri` (`TABLE_SCHEMA`,`TABLE_NAME`,`COLUMN_KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Of course, the solution is very simple, which is to modify the character length in the tables table of CSDN library

alter table csdn.TABLES modify `EXTRA` varchar(128) DEFAULT NULL;

But why information_ In the tables table of the schema library, why can 48 characters be accessed in ‘extra’ varchar (30) default null?This problem is worthy of further study.