Tag Archives: tidb

[Solved] tidb-cdc Create Task Error: Unknown or incorrect time zone

1. Error reporting details

fail to open MySQL connection: [CDC:ErrMySQLConnectionError]Error 1298: Unknown or incorrect time zone: 'Asia/Shanghai'

2. Troubleshooting

# Login tidb and check the time zone
show variables like '%time_zone%';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| system_time_zone | Asia/Shanghai |
| time_zone        | SYSTEM        |
+------------------+---------------+
# login mysql and check the time zone
show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

It can be found that the time zone of the upstream tidb is North America/USA, while the time zone of the downstream MySQL is CST

3. Solution

Method 1: load the time zone

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p

Method 2: write sink to upstream and downstream time zones

cdc cli changefeed create --sink-uri="mysql://user:password@mysql_ip:mysql_port/?time-zone=CST" --pd=http://pd_ip:pd_port

TIDB-kafka server: Message was too large, server rejected it to avoid allocation error

1、 Background

Using drainer to synchronize to Kafka, an error is reported:

 ["fail to produce message to kafka, please check the state of kafka server"] [error="kafka: Failed to produce message to topic test-tidb: kafka server: Message was too large, server rejected it to avoid allocation error."]

2、 Settle

Error reason: if a large transaction is executed in tidb, the generated binlog data will be large, which may exceed the message size limit of Kafka.

Solution: you need to adjust the configuration parameters of Kafka cluster, as shown below.

message.max.bytes=1073741824
replica.fetch.max.bytes=1073741824
fetch.message.max.bytes=1073741824

Tidb-dm Synchronous error: binlog checksum mismatch, data may be corrupted

1. Cause of problem:

Binlog checked 4G

2. Processing relay

1. When an upstream confirmation error occurs, the size of the corresponding binlog file exceeds 4GB

2. Stop DM worker

Note that instead of stop task, stop the corresponding DM worker process

3. Copy the upstream binlog file to the relay log directory as the relay log file.

4. Modify the relay .meta file

Update the corresponding relay.meta file in the relay log directory to pull from the next binlog. If DM worker is enabled_gtid, when modifying the relay.meta file, you also need to modify the gtid corresponding to the next binlog. If enable is not enabled_gtid, there is no need to modify the gtid.

ERROR 1406 (22001): Data Too Long, field len 30, data len 48

 

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.