Register for your free account! | Forgot your password?

Go Back   elitepvpers > Metin2 > Metin2 PServer - Discussions / Questions > Metin2 PServer Guides & Strategies
You last visited: Today at 21:08

  • Please register to post and access all features, it's quick, easy and FREE!


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

Reply
 
Old   #1
 
elite*gold: 100
Join Date: Jun 2009
Posts: 162
Received Thanks: 692
Arrow [How-To-Fix] Error 1265 - Data truncated for column '' at row

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: CLICK

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:
  1. 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...;
  2. 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.
  3. 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



lollo_9_1 is offline  
Thanks
22 Users
Old 11/16/2014, 21:55   #2

 
elite*gold: 233
Join Date: Aug 2014
Posts: 1,410
Received Thanks: 305
thank you very much very usefull


.0x404´Err0xR is offline  
Old 01/28/2015, 20:38   #3

 
elite*gold: 81
Join Date: Nov 2012
Posts: 973
Received Thanks: 528
thanks, that helped me a lot!
.PolluX is offline  
Old 07/06/2015, 08:10   #4
 
elite*gold: 100
Join Date: Dec 2014
Posts: 93
Received Thanks: 51
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,


fcsk_aim is offline  
Old 07/06/2015, 18:02   #5
 
elite*gold: 2518
Join Date: Feb 2008
Posts: 1,368
Received Thanks: 1,287
I think there is a reason why ymir done that.
I'l check that out later.

Kind regards
Sphinx
Sphinx² is offline  
Old 07/07/2015, 08:31   #6
 
elite*gold: 100
Join Date: Jun 2009
Posts: 162
Received Thanks: 692
Quote:
Originally Posted by fcsk_aim View Post
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:
  1. [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.
  2. [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).
  3. 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.
lollo_9_1 is offline  
Thanks
4 Users
Old 07/07/2015, 09:31   #7
 
elite*gold: 100
Join Date: Dec 2014
Posts: 93
Received Thanks: 51
Thanks marty , it worked.

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


fcsk_aim is offline  
Reply

Tags
data truncated for column, lollo_9_1, mysql, setaffectflag, size



« [Service]Modell korrigieren bzw. verbessern. | [RLS] Schneller Items/Yang aufheben »

Similar Threads
Data truncated for column 'size' at row 1
11/25/2014 - Metin2 PServer - Discussions / Questions - 3 Replies
http://i.epvpimg.com/ii2Wf.png Decompressing... Table Created: affect Table Created: banword Table Created: change_empire Table Created: costume_system Table Created: guild Table Created: guild_comment Table Created: guild_grade
[MySQL] Data truncated for column -> bitte um Hilfe
01/22/2014 - Metin2 PServer - Discussions / Questions - 2 Replies
http://i.epvpimg.com/G4Wih.png Mobproto hab ich auch komplett entfernt und jegliche gefundene .sql's aus EPvP ausprobiert, jedesmal der selbe Fehler KEINE Ahnung warum .. Bitte um Hilfe bin echt am verzweifeln daran
Data truncated for column 'size' at row 1
04/16/2013 - Metin2 PServer - Discussions / Questions - 0 Replies
http://i.epvpimg.com/ii2Wf.png Decompressing... Table Created: affect Table Created: banword Table Created: change_empire Table Created: costume_system Table Created: guild Table Created: guild_comment Table Created: guild_grade
ERROR 1054 (42S22) at line 1: Unknown column 'name' in 'where clause'
02/09/2012 - Metin2 PServer - Discussions / Questions - 0 Replies
#closerquest



All times are GMT +1. The time now is 21:08.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.

Support | Contact Us | FAQ | Advertising | Privacy Policy | Abuse
Copyright ©2018 elitepvpers All Rights Reserved.