Text Storage Length Exceeds 65535 in TiDB 5.0.4

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB5.0.4 text 存储长度超过65535

| username: TiDBer_yyy

[TiDB Usage Environment] Production Environment
[TiDB Version] 7.1.0 && 5.0.4
[Background] TiDB upgrade, verifying data consistency, found that TiDB version 5.0.4 can store text exceeding 65535, while version 7.1 truncates it.

5.0.4 Test Environment:
Text field length exceeds 65535, can be saved.
image

Version 7.1.0:
Text field length exceeds 65535, will be truncated.
image

| username: TiDB_C罗 | Original post link

Post the table structure.

| username: TiDBer_yyy | Original post link

CREATE TABLE `control_base_info` (
  `I_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '',
  `I_INFO_CATEGORY` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `I_INFO_SOURCE` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `CH_INFO` text NOT NULL COMMENT '',
  `CH_CODE` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  `CH_NAME` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  `D_CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
  `D_UPDATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '',
  PRIMARY KEY (`I_ID`) /*T![clustered_index] CLUSTERED */,
  KEY `I__INFO_SOURCE` (`I__INFO_SOURCE`),
  UNIQUE KEY `UNIQ_TAX_CAREGORY` (`CH_CODE`,`CH_NAME`,`I_INFO_CATEGORY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=330001 COMMENT=''
| username: TiDB_C罗 | Original post link

There is no such field as CH_RISK_INFO.

| username: TiDBer_yyy | Original post link

Desensitized, CH_INFO field

| username: zhanggame1 | Original post link

The text type shouldn’t be limited to such a small amount. Check if there’s an issue with the SQL you wrote. Try running it manually to see.

| username: TiDBer_yyy | Original post link

The utf8mb4 character set should store 65535*4=262140, it shouldn’t just store 65535. Suspect there might be some parameter settings.

| username: TiDBer_yyy | Original post link

Switch to longtext and re-import using Lightning, no issues.

| username: redgame | Original post link

The maximum length of the LONGTEXT type is 4GB.

| username: TiDBer_yyy | Original post link

Yes, it’s strange. Version 5.0 can store more than 65535, but version 7 cannot.
sql_mode='' I suspect there is some parameter controlling this.

| username: MrSylar | Original post link

I think 1) It is incorrect that 5.0.4 can insert 2) The official documentation’s data type restriction is wrong. It seems to be written based on the maximum allowed length of a row. Could the official team review this?

| username: MrSylar | Original post link

65535 This is compatible with MySQL.

| username: Kongdom | Original post link

Could it be caused by the character set? Can someone with this version try to reproduce it?

| username: TiDBer_yyy | Original post link

Could it be caused by the character set? Can someone with this version try to reproduce it?

For the same table, restored using dumpling, the sorting set for version 7 is new_collations_enabled_on_first_bootstrap=false.