loader error Error 1265: Data Truncated

[httpcheck]

Question

Before asking a question, make sure you have:

• Searched existing Stack Overflow questions.
• Googled your question.
• Searched open and closed GitHub issues
• Read the documentation:
  • TiDB Readme
  • TiDB Doc

mysql version: 5.6.26

tidb: Release Version: v2.0.7 Git Commit Hash: 29ec059cb3b7d14b6f52c2f219f94a89570162bc Git Commit Branch: release-2.0 UTC Build Time: 2018-09-07 12:36:02 GoVersion: go version go1.11 linux/amd64 TiKV Min Version: 2.0.0-rc.4.1

I synchronized data from mysql to the tidb cluster. When loader executed sql and import data into tidb cluster,there were something wrong! I tried again,but it still reported an error.

I even executed sql on the tidb's command line, but it did's work!

[crazycs520]

@bianxindong , Could you provide the table schema and the the data you insert that cause the error Data Truncated.

[httpcheck]

Could you provide your mail for me? There is something sensitive that I can't put it on the github.

[crazycs520]

@bianxindong , I just try to reproduce and found: execute in MySQL 5.7:

mysql root@127.0.0.1:test> create table t2 (a enum("ab","cd") not null default 'cd',b int);
Query OK, 0 rows affected
Time: 0.014s
mysql root@127.0.0.1:test> insert into t2 values("",1);
(1265, u"Data truncated for column 'a' at row 1")

The enum column insert "" will return error.

Those sql execute ok in MySQL 5.6. But as you see, the insert will also return error in MySQL 5.7.

And TiDB currently compatible with MySQL5.7.

[httpcheck]

Is there plan to be compatible with MySQL5.6? Our mysql' environments are most v5.6.26 and it's difficult for me to update mysql and synchronize data.

[crazycs520]

@bianxindong , Em..., sorry, Temporarily there is no plan to be compatible with MySQL5.6.

[httpcheck]

@crazycs520 I had migrated data from many MySQL5.6 databases to TiDB cluster, it was the first time to fail because of compatibility problem... Maybe I should update the slave MySQL and try it again...

[httpcheck]

@crazycs520 Do you have any experience about using v5.7 database as v5.6 database's slave?

[crazycs520]

@bianxindong , Em...sorry, I have no experience like this. 😥

[httpcheck]

@crazycs520 QAQ OK, maybe it's a chance rather than a challenge for me.

[httpcheck]

@crazycs520 I had tried to execute the sql on MySQL5.7. It worked ok!

MySQL:

I backup data from MySQL5.7 and executed the command ‘diff’. They are the same!

[crazycs520]

@bianxindong ， What kind of values do you insert? For if you insert "" to sort column( enum column ), you should get error in MySQL 5.7.

[httpcheck]

@crazycs520 Could you provide your mail for me? There is something sensitive that I can't put it on the github.

[crazycs520]

@bianxindong chenshuang@pingcap.com

[ghost]

Confirming that this issue exists in master (wrong error code and message for truncated string):

drop table if exists t2;
create table t2 (a enum("ab","cd") not null default 'cd',b int);
insert into t2 values("",1);
..
mysql> insert into t2 values("",1);
ERROR 1366 (HY000): Incorrect enum value: '' for column 'a' at row 1
mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-859-gccfc9b2ad
Edition: Community
Git Commit Hash: ccfc9b2ad0dcf8e447210de5f559d7fc208db968
Git Branch: master
UTC Build Time: 2020-07-29 09:37:45
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

vs MySQL 5.7:

mysql [localhost:5731] {msandbox} (test) > insert into t2 values("",1);
ERROR 1265 (01000): Data truncated for column 'a' at row 1