MySQL 5.0 and eqemu

[aza77]

hi,
here's a short explanation how to get eqemu 6.4 DR1-613 running with mysql 5.0 (5.1 might work too but i didn't test it)

1) change the sourcecode
(the first one is the original source the second one the modified one)

diff -r 6.4/common/database.cpp 6.4a/common/database.cpp
2881c2881
< return MakeAnyLenString(query, "SELECT varname, value, unix_timestamp() FROM variables where unix_timestamp(ts) >= %d", varcache_lastupdate);
---
> return MakeAnyLenString(query, "SELECT variables.varname, variables.value, unix_timestamp() FROM eqemu.variables where unix_timestamp(ts) >= %d", varcache_lastupdate);

2996c2996
< if (RunQuery(query, MakeAnyLenString(&query, "Update variables set value='%s' WHERE varname like '%s'", varvalue, varname), errbuf, 0, &affected_rows)) {
---
> if (RunQuery(query, MakeAnyLenString(&query, "Update eqemu.variables set variables.value='%s' WHERE variables.varname like '%s'", varvalue, varname), errbuf, 0, &affected_rows)) {

3005c3005
< if (RunQuery(query, MakeAnyLenString(&query, "Insert Into variables (varname, value) values ('%s', '%s')", varname, varvalue), errbuf, 0, &affected_rows)) {
---
> if (RunQuery(query, MakeAnyLenString(&query, "Insert Into eqemu.variables (varname, value) values ('%s', '%s')", varname, varvalue), errbuf, 0, &affected_rows)) {

4131c4131
< if (RunQuery(query, MakeAnyLenString(&query, "SELECT value FROM variables WHERE varname='ServerType'"), errbuf, &result)) {
---
> if (RunQuery(query, MakeAnyLenString(&query, "SELECT variables.value FROM eqemu.variables WHERE variables.varname='ServerType'"), errbuf, &result)) {

4167c4167
< if (RunQuery(query, MakeAnyLenString(&query, "SELECT value FROM variables WHERE varname='UseCFGSafeCoords'"), errbuf, &result)) {
---
> if (RunQuery(query, MakeAnyLenString(&query, "SELECT variables.value FROM eqemu.variables WHERE varname='UseCFGSafeCoords'"), errbuf, &result)) {
diff -r 6.4/zone/spawn2.cpp 6.4a/zone/spawn2.cpp

226c226
< MakeAnyLenString(&query, "SELECT id, spawngroupID, x, y, z, heading, respawntime, variance, pathgrid, timeleft, condition, cond_value FROM spawn2 WHERE zone='%s'", zone_name);
---
> MakeAnyLenString(&query, "SELECT id, spawngroupID, x, y, z, heading, respawntime, variance, pathgrid, timeleft, spawn2.condition, cond_value FROM spawn2 WHERE zone='%s'", zone_name);


diff -r 6.4/zone/spawngroup.cpp 6.4a/zone/spawngroup.cpp
161,163c161,163
< "SELECT spawnentry.spawngroupID, npcid, chance, "
< " spawnentry.spawn_limit AS gsl, npc_types.spawn_limit AS sl "
< "FROM spawnentry, spawn2 LEFT JOIN npc_types ON spawnentry.npcID = npc_types.id "
---
> "SELECT spawnentry.spawngroupID, spawnentry.npcID, spawnentry.chance, "
> "spawnentry.spawn_limit AS gsl, npc_types.spawn_limit AS sl "
> "FROM spawnentry INNER JOIN spawn2 LEFT JOIN npc_types ON spawnentry.npcID=npc_types.id "


diff -r 6.4/zone/zone.cpp 6.4a/zone/zone.cpp
1634c1634
< if (RunQuery(query, MakeAnyLenString(&query, "SELECT varname, value FROM variables WHERE varname like 'decaytime%%' ORDER BY varname"), errbuf, &result)) {
---
> if (RunQuery(query, MakeAnyLenString(&query, "SELECT variables.varname, variables.value FROM eqemu.variables WHERE varname like 'decaytime%%' ORDER BY varname"), errbuf, &result)) {



2) add the Spawn2 table

# MySQL-Front Dump 2.5
#
# Host: 192.168.0.9 Database: eq
# --------------------------------------------------------
# Server version 4.0.13-standard


#
# Table structure for table 'spawn2'
#

CREATE TABLE spawn2 (
id int(11) NOT NULL auto_increment,
spawngroupID int(11) NOT NULL default '0',
zone varchar(16) NOT NULL default '',
x float NOT NULL default '0',
y float NOT NULL default '0',
z float NOT NULL default '0',
heading float NOT NULL default '0',
respawntime int(11) NOT NULL default '0',
variance smallint(4) NOT NULL default '0',
pathgrid int(10) NOT NULL default '0',
timeleft bigint(16) NOT NULL default '0',
spawn2.condition MEDIUMINT UNSIGNED NOT NULL DEFAULT '0',
cond_value MEDIUMINT NOT NULL DEFAULT '1',
PRIMARY KEY (id)
) TYPE=MyISAM;

3) add some data to spawn2

eg the PEQ database
https://www.projectf.de/spawn2data.sql

[fathernitwit]

Thanks for the info about whats broken, we will consider changing these field/table names to make mysql happy..


in the future, please post unified diffs (-u), much easier to read.

[aza77]

ok i made a compatible patch for all users.. concerning the mysql5 troubles with eqemu and the availability of all froglok classes.

if you want to enable mysql5 support just uncomment the mysql5db_cflags at world, zone, EMuShareMem and eqlaunch.

to enable all froglok classes just uncomment the ALLFROGS at the world makefile.

enjoy it ;)

https://www.projectf.de/mysql5frog.diff

[Arex]

I guess that this code for support MYSQL5 isnt still updated in eqemu official code? isnt it?

[aza77]

i'll post the new code tomorrow ...

[Arex]

nice Thank You, and could i know if is neccesary to do any changes to DB ? i am not sure, but i remember that last time that i tried source peq-db in mysql 5.0, i got any error...

[aza77]

nah there is no db change needed

[Arex]

yes mate, DB need any change, i have checked it rightnow, at least spawn2 create table script need any modification...

[aza77]

https://www.projectf.de/mysql5.diff

diff was made for 0.7.0-800. all you need to do is add it to your code and the server works with mysql5

[Arex]

u also need modify spawn2 create table script changing condition field for `condition`..

[aza77]

if you import a new db u can either use spawn2.condition or `condition` ;)

[Arex]

i already have compiled code succesfully and same with DB source...have u checked eqemu performance with mysql 5.0? works it so good like with mysql 4.0.24?

Thank you for help!

[aza77]

i didn't notice any slowdown but i already changed to mysql5 some month ago

[abaddon]

call me slow (laffs) but I had a question.. I have crappy machines (lots of them) so I am constantly looking for a faster way to do everything... will 5.x speed up the server?

[eq4me]

no

But you could use machines for dedicated tasks.

Eg. let the mysql Database run on one Machine and the EQEmu Server itself on other machines. You could even run zones from one EQEmu server on multiple machines. As long as you have enough RAM and maybe Swapspace in each machine that is.
I intend do try some more complex setups (under Linux) in the near future and write a Wiki tutorial about it.

[abaddon]

having a seperate machine server the SQL would speed up the server on a whole?

[eq4me]

It is probably save to say that overall MySQL 5 is not faster or slower that previous versions. At work we have like 20 MySQL 4.1 and 6 5.0 installations on comparable hardware. The only reason we are using 5.0 is because of needed new features.

What I meant was that if you use dedicated machines for dedicated tasks you can spread the workload over more than one machine.

Instead of burdening one machine with both the MySQL server and EQEmu zones you could use multiple machines, one for the server and one or more for EQEmu zones.

[John Adams]

Yes, dedicated hardware for difference network services is always the best idea. When a zone loads, it tasks the CPU. When it loads it also fetches data from SQL, which also tasks the CPU. If you have any other tasks going on (LSASS, CSRSS, cidaemon, backup software, blah blah), you're going to see CPU spikes of 100%. Naturally, if there's no CPU left, it slows everything down.

That said, I have not seen the Emu gobble up enough resources to be a problem (except when I code horribly wrong and cause race conditions that eat the machine alive). Best scenario? 1 PC for world/zones. 1 PC for SQL. 1 PC to play from (you). Anything else i am missing?

(perl lives on the World box - if it's even necessary anymore?)