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^10 0^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^2 54^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^10 0^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/master/utils/sql/git/required/2014_08_02_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,’^’,c ast_on_other,’^’,spell_fades,’^’,range,’^’,aoerang e,’^’,pushback,’^’,pushup,’^’,cast_time,’^’,recove ry_time,’^’,recast_time,’^’,buffdurationformula,’^ ’,buffduration,’^’,AEDuration,’^’,mana,’^’,effect_ base_value1,’^’,effect_base_value2,’^’,effect_base _value3,’^’,effect_base_value4,’^’,effect_base_val ue5,’^’,effect_base_value6,’^’,effect_base_value7, ’^’,effect_base_value8,’^’,effect_base_value9,’^’, effect_base_value10,’^’,effect_base_value11,’^’,ef fect_base_value12,’^’,effect_limit_value1,’^’,effe ct_limit_value2,’^’,effect_limit_value3,’^’,effect _limit_value4,’^’,effect_limit_value5,’^’,effect_l imit_value6,’^’,effect_limit_value7,’^’,effect_lim it_value8,’^’,effect_limit_value9,’^’,effect_limit _value10,’^’,effect_limit_value11,’^’,effect_limit _value12,’^’,max1,’^’,max2,’^’,max3,’^’,max4,’^’,m ax5,’^’,max6,’^’,max7,’^’,max8,’^’,max9,’^’,max10, ’^’,max11,’^’,max12,’^’,icon,’^’,memicon,’^’,compo nents1,’^’,components2,’^’,components3,’^’,compone nts4,’^’,component_counts1,’^’,component_counts2,’ ^’,component_counts3,’^’,component_counts4,’^’,Noe xpendReagent1,’^’,NoexpendReagent2,’^’,NoexpendRea gent3,’^’,NoexpendReagent4,’^’,formula1,’^’,formul a2,’^’,formula3,’^’,formula4,’^’,formula5,’^’,form ula6,’^’,formula7,’^’,formula8,’^’,formula9,’^’,fo rmula10,’^’,formula11,’^’,formula12,’^’,LightType, ’^’,goodEffect,’^’,Activated,’^’,resisttype,’^’,ef fectid1,’^’,effectid2,’^’,effectid3,’^’,effectid4, ’^’,effectid5,’^’,effectid6,’^’,effectid7,’^’,effe ctid8,’^’,effectid9,’^’,effectid10,’^’,effectid11, ’^’,effectid12,’^’,targettype,’^’,basediff,’^’,ski ll,’^’,zonetype,’^’,EnvironmentType,’^’,TimeOfDay, ’^’,classes1,’^’,classes2,’^’,classes3,’^’,classes 4,’^’,classes5,’^’,classes6,’^’,classes7,’^’,class es8,’^’,classes9,’^’,classes10,’^’,classes11,’^’,c lasses12,’^’,classes13,’^’,classes14,’^’,classes15 ,’^’,classes16,’^’,CastingAnim,’^’,TargetAnim,’^’, TravelType,’^’,SpellAffectIndex,’^’,disallow_sit,’ ^’,deities0,’^’,deities1,’^’,deities2,’^’,deities3 ,’^’,deities4,’^’,deities5,’^’,deities6,’^’,deitie s7,’^’,deities8,’^’,deities9,’^’,deities10,’^’,dei ties11,’^’,deities12,’^’,deities13,’^’,deities14,’ ^’,deities15,’^’,deities16,’^’,field142,’^’,field1 43,’^’,new_icon,’^’,spellanim,’^’,uninterruptable, ’^’,ResistDiff,’^’,dot_stacking_exempt,’^’,deletea ble,’^’,RecourseLink,’^’,no_partial_resist,’^’,fie ld152,’^’,field153,’^’,short_buff_box,’^’,descnum, ’^’,typedescnum,’^’,effectdescnum,’^’,effectdescnu m2,’^’,npc_no_los,’^’,field160,’^’,reflectable,’^’ ,bonushate,’^’,field163,’^’,field164,’^’,ldon_trap ,’^’,EndurCost,’^’,EndurTimerIndex,’^’,IsDisciplin e,’^’,field169,’^’,field170,’^’,field171,’^’,field 172,’^’,HateAdded,’^’,EndurUpkeep,’^’,numhitstype, ’^’,numhits,’^’,pvpresistbase,’^’,pvpresistcalc,’^ ’,pvpresistcap,’^’,spell_category,’^’,field181,’^’ ,field182,’^’,field183,’^’,field184,’^’,can_mgb,’^ ’,nodispell,’^’,npc_category,’^’,npc_usefulness,’^ ’,MinResist,’^’,MaxResist,’^’,viral_targets,’^’,vi ral_timer,’^’,nimbuseffect,’^’,ConeStartAngle,’^’, ConeStopAngle,’^’,sneaking,’^’,not_extendable,’^’, field198,’^’,field199,’^’,suspendable,’^’,viral_ra nge,’^’,songcap,’^’,field203,’^’,field204,’^’,no_b lock,’^’,field206,’^’,spellgroup,’^’,rank,’^’,fiel d209,’^’,field210,’^’,CastRestriction,’^’,allowres t,’^’,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/showpost.php?p=235542&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.