[How-To-Fix] Error 1265 - Data truncated for column '' at row

11/16/2014 17:31 lollo_9_1#1

Intro
Almost any of you got such an error when you tried to import your own mysql backups via .sql, at least, once in your lifetime, instead of using mysqldump.

Anyway, you can get this weird error on tables such as player.mob_proto and player.skill_proto.

Code:

#skill_proto
1265 - Data truncated for column 'setAffectFlag' at row 1
1265 - Data truncated for column 'setFlag' at row 1
#mob_proto
1265 - Data truncated for column 'size' at row 1
#and so on

Why
Since mysql5, there's a flag called STRICT_TRANS_TABLES that forbids you to set a wrong field into enum fields returning an error.
Usually, you should not have '' as an enum value because it's the result of a bad flag value set in it.
To be more precise:
The 0 element of an enum is interpreted as '', but '' is not the 0 element.
If you have an enum such as ('A', 'B', 'C'): A is 1, B is 2, C is 3, and everything else not in the enum is truncated as 0.
The STRICT_TRANS_TABLES won't truncate the value to 0, but it will generate an error.

If you run a query like this, you could see their positions very easily:

Code:

select apply, apply+0 from player.item_attr;

Result: [Only registered and activated users can see links. Click Here To Register...]

Your aim is to either remove the STRICT_TRANS_TABLES flag or the truncated '' fields to NULL.

How-To-Fix
You have many solutions to fix this awful error:

Add the IGNORE parameter to your INSERT or UPDATE queries to consider this error just a warning.

Code:

/* change this */
INSERT INTO smth ...therestofthequery...;
/* to */
INSERT IGNORE INTO smth ...therestofthequery...;

You should replace the empty string fields '' as NULL for the relative enum/set ones.
It could be boring, and db could not read such NULL fields at all causing a crash.
NB: You should also set the field NULLable.
Remove the STRICT_TRANS_TABLES flag from the sql_mode variable putting this at the beginning of the query:
Code:
```
SET sql_mode=(SELECT REPLACE(@@sql_mode,'STRICT_TRANS_TABLES',''));
```
Or just:
Code:
```
SET sql_mode = '';
```
NB: mysqldump does such a thing automatically.

Obviously, the better choice should be editing the sql_mode.

What-To-Know

[Only registered and activated users can see links. Click Here To Register...]
[Only registered and activated users can see links. Click Here To Register...]
[Only registered and activated users can see links. Click Here To Register...]
[Only registered and activated users can see links. Click Here To Register...]

11/16/2014 21:55 Alergix2#2

thank you very much ;) very usefull

01/28/2015 20:38 .PolluX#3

thanks, that helped me a lot! :)

07/06/2015 08:10 fcsk_aim#4

For me not work , i have this error:

Code:

SYSERR: Jul  6 09:07:01 :: ChildLoop: AsyncSQL: query failed: Data truncated for column 'setRaceFlag' at row 1 (query: replace into mob_proto (vnum, name, locale_name, type, rank, battle_type, level, size, ai_flag, setRaceFlag, setImmuneFlag, on_click, empire, drop_item, resurrection_vnum, folder, st, dx, ht, iq, damage_min, damage_max, max_hp, regen_cycle, regen_percent, exp, gold_min, gold_max, def, attack_speed, move_speed, aggressive_hp_pct, aggressive_sight, attack_range, polymorph_item, enchant_curse, enchant_slow, enchant_poison, enchant_stun, enchant_critical, enchant_penetrate, resist_sword, resist_twohand, resist_dagger, resist_bell, resist_fan, resist_bow, resist_fire, resist_elect, resist_magic, resist_wind, resist_poison, dam_multiply, summon, drain_sp, skill_vnum0, skill_level0, skill_vnum1, skill_level1, skill_vnum2, skill_level2, skill_vnum3, skill_level3, skill_vnum4, skill_level4, sp_berserk, sp_stoneskin, sp_godspeed, sp_deathblow, sp_revive) values (1301, "???", "Soldat Brotac", 0, 1, 0,

07/06/2015 18:02 Sphinx�#5

I think there is a reason why ymir done that.
I'l check that out later.

Kind regards
Sphinx

07/07/2015 08:31 lollo_9_1#6

Quote:
Originally Posted by fcsk_aim
For me not work , i have this error:
Code:
SYSERR: Jul  6 09:07:01 :: ChildLoop: AsyncSQL: query failed: Data truncated for column 'setRaceFlag' at row 1 (query: replace into mob_proto
...)

This is the Mirror2DB feature by ymir.
You should disable it in the source files since it's useless. (it also makes 4k queries every time you run the db)

If you want to keep it, you have few ways to solve it:

[TEMPORARY]
Run
Code:
```
SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
```
and restart the metin2 server. (to create a new mysql session with the updated sql_mode value)
It will last till mysql restart/server reboot.
[PERMANENT]
Create the file /etc/my.cnf and paste:
Code:
```
[mysqld] 
sql_mode = NO_ENGINE_SUBSTITUTION
```
then restart mysql.
You can restart the mysql server or via reboot (#shutdown -r now) or just restarting its service (#service mysql-server restart).
Source editing that function to return NULL instead of '' for those fields

Note: the default values are always "NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLE", so I just removed the second one since it's the key in this problem.

07/07/2015 09:31 fcsk_aim#7

Thanks marty , it worked.

Can you tell me how to disable this from the source ? :D
Solution number 3 worked.
Anyway , it works . Thx

03/28/2023 07:24 rachelgomez123#8

Here are some common causes and how to fix: 

1. Datatype mismatch. 
First, check if the data type of the column is right for the input data. Maybe its defined length is smaller than it should be, or maybe there’s a misalignment that resulted in a value trying to be stored in a field with different datatype.

2. Wrong terminating character
If you manually insert each line into the table and it works just fine, the error occurs only when you load multiple lines, then it’s likely the command didn’t receive proper terminating character.

So check your file’s terminating character and specify it in the LOAD command 

If it’s terminated by a tab :
FIELDS TERMINATED BY '\t'
If it’s terminated by a comma
FIELDS TERMINATED BY ','
Then you’re good to go.

Need a good MySQL GUI? TablePlus provides a native client that allows you to access and manage MySQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Regards,
Rachel Gomez