EQEmulator Forums - Mercenary Query Altering 'merchant

EQEmulator Forums (https://www.eqemulator.org/forums/index.php)

- Support::Windows Servers (https://www.eqemulator.org/forums/forumdisplay.php?f=587)

- - Mercenary Query Altering 'merchant_id' Question (PEQ) (https://www.eqemulator.org/forums/showthread.php?t=43371)

Mercenary Query Altering 'merchant_id' Question (PEQ)

My offline PEQ server works until I add mercenaries. The mercenaries and merc vendors themselves work fine.

Normal vendors, however, show no items to purchase and I think I know why.

The MySQL query I've used to add mercs sets my general 'merchant_id', and I'm wondering if the following might just be a typo or specific to the database the author of this query was using at the time (final line):

DROP TABLE IF EXISTS `merc_merchant_entries`;
CREATE TABLE `merc_merchant_entries` (
`merc_merchant_entry_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`merc_merchant_template_id` int(10) UNSIGNED NOT NULL,
`merchant_id` int(11) UNSIGNED NOT NULL,

Note that the query focuses entirely on 'merc_merchant[...]' values and only once at the end alters simply 'merchant_id'.

Can I just change this 'merchant_id' value in this query to reflect some universal number for what this ought to be? Is this specific to my database and represents a value I should provide instead?

I'm using a possibly older version of PEQ database installer and ROF2 client, with the addition of a MySQL query to implement mercenaries posted in the forums somewhere around here.

Finally, dumb question: I'm new to this, so does it matter in what order I close my multiple server windows after logging out of Norrath? I ask because I know the database is updated by the client, and without knowing the proper way to shutdown the server, I've clicked it all off in a different sequence each time, and twice I've experienced problems zoning into (new) Nektulos from both Neriak and ECommons, where my character cannot move and loses hair, sky doesn't appear, distant land elements vanish, and NPCs all float high up in the air). Setting GM status and #zone Halas gets me killed and returned to Nektulos, where everything is fine again for some reason.

I am deeply appreciative of the hard work that went into all of this! Just stuck on these two items or maybe they could be related?

Thank you!

These are the complete merc tables, just need to run the query in mysql and make sure mercs are enabled in the rule_values.

http://tinyurl.com/emu-merc-tables

Thank you very much--I really appreciate the lightning-fast response!

For the record, the mercenary .sql query posted by Taleason was the one that nearly worked flawlessly:

https://drive.google.com/file/d/1Vaq...ew?usp=sharing

I'll just delete that one and use the one you posted.

Also, look at the db version table in database and see how old it is. (current is 9167).
Sometimes, it helps to know that, if someone has backup tables, (like mercs), with all the updates by the PEQ team, it could be a pita sourcing in any tables, if column structure doesn't match.

[Edit]

I understand if there isn't any time for a response, I'm already benefitting from a lot of grace and charity here.

I've removed my previous mercenary_tables.sql and used the provided mercs.sql with no errors, although I still recieve the identical bug (vendors have nothing to sell, but will still buy).

I am using Akkadius' installer package found here (on Windows 10): http://analytics.akkadius.com/dl/eqe..._files_x64.zip

Vendors work fine if I remove mercenaries entirely.

I want to figure this out before I dip my toes into something like learning to edit LUA scripts or further material. Maybe I shouldn't! haha...

Which I found by manually typing the link provided on this page into a new window:

https://eqemu.gitbook.io/server/cate...lation-windows

Well, how embarrasing!!!

Whomever exported that merc sql appears to have used a wildcard of "merc" so the merchantlist and merchanlist_temp tables were exported to it. When you import this mercs.sql it deletes your existing merchantlist and inserts an older non-compat version.

You have two options:

1-
A>On your working database, export the merchantlist tables into an sql file.
B>Import mercs.sql, then import your merchanlist.sql from your previously working db

2-
A> Edit the mercs.sql and remove all lines pertaining to merchantlist
B> Import the now smaller mercs.sql

Quote:

Originally Posted by rencro (Post 267033)

Or, go get the the one I posted and you won't have to go through all that....

Update -- I backed up my database and tried both. The original consistently prompts the empty merchant error which persists after several reboots of the server.

I restored my backup and tried Huppy's again, and it seems using that one, I can enter a zone to find empty merchant lists, but then if I zone, camp, reboot server, zone back in, the same merchants are back to normal. After some unknown number of hours, I can experience that glitch again but perfectly subject to the same solution, so who knows. Thank you Huppy!

Quote:

Originally Posted by Vaulicet (Post 267043)

After some unknown number of hours, I can experience that glitch again

I have no idea, whats causing it for you. Those merc tables i dumped are from one of my working server projects, but it's using db version 9159. I'm not sure how old yours is, or if that's even relevant to your issue.

I know lots of updates happen frequently with the PEQ/Source teams, so it's hard to say. Did you look at the db_version table in your database ? Things can go smooth when tables from the same versions of db's get sourced in, but issues can happen when one of the databases are older/newer.

I built my luclin server project from an old peq database and ID's for some things have changed in some tables. (just as an example)

I was able to replicate this issue on a test box, with a fresh database and source code. (merchants had no items showing).

Server logs were giving me an sql error for a few missing columns from an older update that didn't seem to make it into the database, when it was getting updated after installing it. (2020_04_11_expansions_content_filters.sql)

There was only a few queries in that update that didn't make it in, but the rest of it did. After manually sourcing them in, the merchants were stocked up again ;)

Code:

ALTER TABLE `merchantlist` ADD `min_expansion` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0';

ALTER TABLE `merchantlist` ADD `max_expansion` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0';

ALTER TABLE `merchantlist` ADD `content_flags` varchar(100) NULL;

ALTER TABLE `merchantlist` ADD `content_flags_disabled` varchar(100) NULL;

These are the intial errors I got int he logs:

Code:

[08-13-2021 :: 10:20:39] [Zone] [MySQL Error] [1054] [Unknown column 'merchantlist.min_expansion' in 'where clause']

[SELECT DISTINCT merchantlist.merchantid, merchantlist.slot, merchantlist.item, merchantlist.faction_required, merchantlist.level_required, merchantlist.alt_currency_cost, merchantlist.classes_required, merchantlist.probability FROM merchantlist, npc_types, spawnentry, spawn2 WHERE npc_types.merchant_id = merchantlist.merchantid AND npc_types.id = spawnentry.npcid AND spawnentry.spawngroupid = spawn2.spawngroupid AND spawn2.zone = 'rivervale' AND spawn2.version = 0  AND (merchantlist.min_expansion <= 99 OR merchantlist.min_expansion = 0) AND (merchantlist.max_expansion >= 99 OR merchantlist.max_expansion = 0) AND (merchantlist.content_flags IS NULL)  AND (merchantlist.content_flags_disabled IS NULL)  ORDER BY merchantlist.slot]



[08-13-2021 :: 11:00:54] [Zone] [MySQL Error] [1054] [Unknown column 'content_flags_disabled' in 'where clause']

[SELECT item, slot, faction_required, level_required, alt_currency_cost, classes_required, probability FROM merchantlist WHERE merchantid = 19051  AND (min_expansion <= 99 OR min_expansion = 0) AND (max_expansion >= 99 OR max_expansion = 0) AND (content_flags IS NULL)  AND (content_flags_disabled IS NULL)  ORDER BY slot]