Database<->spells_us.txt mismatch

[Charles082986]

Using the PEQ Editor (linked from EOC 2.0), I exported my spells_new table to a spells_us.txt file. Unfortunately, the files have a different number of columns.

The database and newly exported file both have 237 columns:

732^Ice Comet^PLAYER_1^^^^Your skin is rent by massive shards of deadly ice.^'s skin is rent by massive shards of deadly ice.^^200^0^0^0^5000^1500^1500^0^0^0^203
^-700^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^808^0^0^0^0^0^0^0^0^0^0^0^2519^2053^-1^-1^-1^-1^1^1^1^1^-1^-1^-1^-1^6
^100^100^100^100^100^100^100^100^100^100^100^0^0^0^3^0^254^254^254^254^254^254^254^254^254^254^254^5^0^24^-1^0^0^255^255^255^255^255^255^255^255^255^255^255^49^255^255^255^255
^44^13^0^23^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^100^0^56^71^0^-10^0^1^0^0^0^0^0^732^25^14^0^0^-1^-1^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^27^134^-7
^126^0^0^0^0^0^0^2^59^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^-1^0^0^0^1^0^0^1^1^1^0^-1^0^0^0^3^51^-1^0^1^0^0^1^0^1^0^0^0^0^0^0

The former spells_us.txt I had (copied from my Titanium directory) has 215.

732^Ice Comet^PLAYER_1^^^^Your skin is rent by massive shards of deadly ice.^'s skin is rent by massive shards of deadly ice.^^200^0^0^0^5000^2500^2250^0^0^0^203
^-700^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^808^0^0^0^0^0^0^0^0^0^0^0^2519^2053^-1^-1^-1^-1^1^1^1^1^-1^-1^-1^-1^6
^0^0^0^100^100^100^100^100^100^100^100^0^0^0^3^0^254^254^254^254^254^254^254^254^254^254^254^5^0^24^-1^0^0^255^255^255^255^255^255^255^255^255^255^255^49^255^255^255^255
^44^13^0^23^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^100^0^56^71^0^-10^0^1^0^0^0^0^0^732^25^14^0^0^-1^-1^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^27^134^-7
^126^0^0^0^0^0^0^2^59^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^0^-1^0^0^0^1^0^0^1^1

When I use the titanium file, spell particles work.

When I use the newly exported file, no spell particles work. The spells are still properly identified, however my character simply waves his hands around wildly after the cast bar runs it's course and the enemy falls over dead, like we're both terrible LARPers on LSD.

[Charles082986]

After spending until about 3:00 AM digging around, here's what I found.

The differing fields were created by this SQL statement:
https://github.com/EQEmu/Server/blob...spells_new.sql

If I simply omit those fields from the text file using the following SQL statement, everything still breaks:

SELECT CONCAT(id,’^’,name,’^’,player_1,’^’,teleport_zone,’^’,you_cast,’^’,other_casts,’^’,cast_on_you,’^’,cast_on_other,’^’,spell_fades,’^’,range,’^’,aoerange,’^’,pushback,’^’,pushup,’^’,cast_time,’^’,recovery_time,’^’,recast_time,’^’,buffdurationformula,’^’,buffduration,’^’,AEDuration,’^’,mana,’^’,effect_base_value1,’^’,effect_base_value2,’^’,effect_base_value3,’^’,effect_base_value4,’^’,effect_base_value5,’^’,effect_base_value6,’^’,effect_base_value7,’^’,effect_base_value8,’^’,effect_base_value9,’^’,effect_base_value10,’^’,effect_base_value11,’^’,effect_base_value12,’^’,effect_limit_value1,’^’,effect_limit_value2,’^’,effect_limit_value3,’^’,effect_limit_value4,’^’,effect_limit_value5,’^’,effect_limit_value6,’^’,effect_limit_value7,’^’,effect_limit_value8,’^’,effect_limit_value9,’^’,effect_limit_value10,’^’,effect_limit_value11,’^’,effect_limit_value12,’^’,max1,’^’,max2,’^’,max3,’^’,max4,’^’,max5,’^’,max6,’^’,max7,’^’,max8,’^’,max9,’^’,max10,’^’,max11,’^’,max12,’^’,icon,’^’,memicon,’^’,components1,’^’,components2,’^’,components3,’^’,components4,’^’,component_counts1,’^’,component_counts2,’^’,component_counts3,’^’,component_counts4,’^’,NoexpendReagent1,’^’,NoexpendReagent2,’^’,NoexpendReagent3,’^’,NoexpendReagent4,’^’,formula1,’^’,formula2,’^’,formula3,’^’,formula4,’^’,formula5,’^’,formula6,’^’,formula7,’^’,formula8,’^’,formula9,’^’,formula10,’^’,formula11,’^’,formula12,’^’,LightType,’^’,goodEffect,’^’,Activated,’^’,resisttype,’^’,effectid1,’^’,effectid2,’^’,effectid3,’^’,effectid4,’^’,effectid5,’^’,effectid6,’^’,effectid7,’^’,effectid8,’^’,effectid9,’^’,effectid10,’^’,effectid11,’^’,effectid12,’^’,targettype,’^’,basediff,’^’,skill,’^’,zonetype,’^’,EnvironmentType,’^’,TimeOfDay,’^’,classes1,’^’,classes2,’^’,classes3,’^’,classes4,’^’,classes5,’^’,classes6,’^’,classes7,’^’,classes8,’^’,classes9,’^’,classes10,’^’,classes11,’^’,classes12,’^’,classes13,’^’,classes14,’^’,classes15,’^’,classes16,’^’,CastingAnim,’^’,TargetAnim,’^’,TravelType,’^’,SpellAffectIndex,’^’,disallow_sit,’^’,deities0,’^’,deities1,’^’,deities2,’^’,deities3,’^’,deities4,’^’,deities5,’^’,deities6,’^’,deities7,’^’,deities8,’^’,deities9,’^’,deities10,’^’,deities11,’^’,deities12,’^’,deities13,’^’,deities14,’^’,deities15,’^’,deities16,’^’,field142,’^’,field143,’^’,new_icon,’^’,spellanim,’^’,uninterruptable,’^’,ResistDiff,’^’,dot_stacking_exempt,’^’,deleteable,’^’,RecourseLink,’^’,no_partial_resist,’^’,field152,’^’,field153,’^’,short_buff_box,’^’,descnum,’^’,typedescnum,’^’,effectdescnum,’^’,effectdescnum2,’^’,npc_no_los,’^’,field160,’^’,reflectable,’^’,bonushate,’^’,field163,’^’,field164,’^’,ldon_trap,’^’,EndurCost,’^’,EndurTimerIndex,’^’,IsDiscipline,’^’,field169,’^’,field170,’^’,field171,’^’,field172,’^’,HateAdded,’^’,EndurUpkeep,’^’,numhitstype,’^’,numhits,’^’,pvpresistbase,’^’,pvpresistcalc,’^’,pvpresistcap,’^’,spell_category,’^’,field181,’^’,field182,’^’,field183,’^’,field184,’^’,can_mgb,’^’,nodispell,’^’,npc_category,’^’,npc_usefulness,’^’,MinResist,’^’,MaxResist,’^’,viral_targets,’^’,viral_timer,’^’,nimbuseffect,’^’,ConeStartAngle,’^’,ConeStopAngle,’^’,sneaking,’^’,not_extendable,’^’,field198,’^’,field199,’^’,suspendable,’^’,viral_range,’^’,songcap,’^’,field203,’^’,field204,’^’,no_block,’^’,field206,’^’,spellgroup,’^’,rank,’^’,field209,’^’,field210,’^’,CastRestriction,’^’,allowrest,’^’,InCombat,’^’,OutofCombat) as Spell From spells_new;

If I JUST copy over the Ice Comet spell, the Ice Comet spell works, and everything else remains broken.

This leads me to two assumptions, one based on the other:
1) If there is an error in any spell, the whole file is broken.
2) There is an error in one of my spells.

[Charles082986]

I've loaded the data from PEQ, ensuring that my database has no custom changes, and all exports still fail to run spell particles. I can only assume that the spells_new table from PEQ has bad data in it somewhere.

[Charles082986]

Problem solved.

The PEQ database from august of 2015 contains spell IDs that go all the way up to 49999. I amended my SQL export statement to only export spells where the id is less than 45000 (as indicated in this post: http://www.eqemulator.org/forums/sho...42&postcount=8)

Magically, everything works now. I'm not sure what client can support spell ID's of up to 50k, but I'm using the ROF2 client, which stops at 45k. My initial assumption had been that the latest client would be capable of handling anything from the latest database, and that was not the case.