One auth, two different versioned realms.

Bizzycola · Apr 7, 2015

I am not entirely familiar with the auth server protocol for WoW, so I dunno if it changes a lot between 3.3.5 and Cataclysm(Does Blizzard even let you play older versions)? But I was curious if it is possible to have one auth server with two different realm servers, one being 4.3.4 and the other 3.3.5?
I assume probably not but I figured I'd ask anyway.

DrkJstr · Apr 7, 2015

yes, its possible. some minor tweaks are required.

ExO · Apr 7, 2015

Yes this is indeed possible, and actually not very hard to do either.
You can check out this tutorial where you can find out how to do it and eventually get further information:
http://www.emucoach.com/showthread.php?362-Tutorial-How-to-add-another-realm-on-your-server

If there's anything you don't understand, or whatever it could be - just ask here!

Bizzycola · Apr 8, 2015

Ah so it does work! Thanks for that!

DrkJstr · Apr 9, 2015

It is important to note that you will need the auth server from 4.3.4 (it will support both 3.3.5 & 4.3.4). The Auth server from 3.3.5 does not have the BNET support required

frankthedruid · May 5, 2015

I have tried to do this and the wotlk realm won't connect to the auth db
trinity version
TrinityCore rev. 058457dda922 2015-03-27 21:38:23 +0100 (3.3.5 branch) (Win32, Release)
error
Could not prepare statements of the Login database, see log for details.

DrkJstr · May 6, 2015

what do the logs say?

frankthedruid · May 6, 2015

3.3.5
db logs
In mysql_stmt_prepare() id: 8, sql: "INSERT INTO account_banned VALUES (?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()+?, 'Trinity Auth', 'Failed login autoban', 1)"
Column count doesn't match value count at row 1
In mysql_stmt_prepare() id: 28, sql: "INSERT INTO account_banned VALUES (?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()+?, ?, ?, 1)"
Column count doesn't match value count at row 1
In mysql_stmt_prepare() id: 38, sql: "UPDATE account SET lock_country = ? WHERE id = ?"
Unknown column 'lock_country' in 'field list'
In mysql_stmt_prepare() id: 43, sql: "UPDATE account SET reg_mail = ? WHERE id = ?"
Unknown column 'reg_mail' in 'field list'
In mysql_stmt_prepare() id: 47, sql: "UPDATE account SET last_attempt_ip = ? WHERE username = ?"
Unknown column 'last_attempt_ip' in 'field list'
In mysql_stmt_prepare() id: 76, sql: "INSERT INTO logs_ip_actions (account_id,character_guid,type,ip,systemnote,unixtime,time) VALUES (?, ?, ?, (SELECT last_ip FROM account WHERE id = ?), ?, unix_timestamp(NOW()), NOW())"
Table 'auth.logs_ip_actions' doesn't exist
In mysql_stmt_prepare() id: 77, sql: "INSERT INTO logs_ip_actions (account_id,character_guid,type,ip,systemnote,unixtime,time) VALUES (?, ?, ?, (SELECT last_attempt_ip FROM account WHERE id = ?), ?, unix_timestamp(NOW()), NOW())"
Table 'auth.logs_ip_actions' doesn't exist
In mysql_stmt_prepare() id: 78, sql: "INSERT INTO logs_ip_actions (account_id,character_guid,type,ip,systemnote,unixtime,time) VALUES (?, ?, ?, ?, ?, unix_timestamp(NOW()), NOW())"
Table 'auth.logs_ip_actions' doesn't exist
In mysql_stmt_prepare() id: 79, sql: "INSERT INTO logs_ip_actions (account_id,character_guid,type,ip,systemnote,unixtime,time) VALUES ((SELECT id FROM account WHERE username = ?), 0, 1, ?, ?, unix_timestamp(NOW()), NOW())"
Table 'auth.logs_ip_actions' doesn't exist
In mysql_stmt_prepare() id: 84, sql: "INSERT INTO account_muted VALUES (?, UNIX_TIMESTAMP(), ?, ?, ?)"
Table 'auth.account_muted' doesn't exist

world log
TrinityCore rev. 058457dda922 2015-03-27 21:38:23 +0100 (3.3.5 branch) (Win32, Release) (worldserver-daemon)
<Ctrl-C> to stop.

______ __
/\__ _\ __ __/\ \__
\/_/\ \/ _ __ /\_\ ___ /\_\ \, _\ __ __
\ \ \/\`'__\/\ \ /' _ `\/\ \ \ \/ /\ \/\ \
\ \ \ \ \/ \ \ \/\ \/\ \ \ \ \ \_\ \ \_\ \
\ \_\ \_\ \ \_\ \_\ \_\ \_\ \__\\/`____ \
\/_/\/_/ \/_/\/_/\/_/\/_/\/__/ `/___/> \
C O R E /\___/
http://TrinityCore.org \/__/

Using configuration file worldserver.conf.
Using SSL version: OpenSSL 1.0.2a 19 Mar 2015 (library: OpenSSL 1.0.2a 19 Mar 2015)
Using Boost version: 1.57.0
Could not prepare statements of the Login database, see log for details.

4.3.4
auth
EmuCoach V2 rev. 0000-00-00 00:00:00 +0000 (Archived) (Win32, Release) (authserver)
<Ctrl-C> to stop.

Using configuration file authserver.conf.
OpenSSL 1.0.1c 10 May 2012 (Library: OpenSSL 1.0.0g 18 Jan 2012)
Opening DatabasePool 'auth'. Asynchronous connections: 1, synchronous connections: 1.
MySQL client library: 5.5.9
MySQL server ver: 5.5.9-log
Connected to MySQL database at 127.0.0.1
MySQL client library: 5.5.9
MySQL server ver: 5.5.9-log
Connected to MySQL database at 127.0.0.1
DatabasePool 'auth' opened successfully. 2 total connections running.
Started auth database connection pool.
Updating Realm List...
Added realm "azeroth" at 10.96.20.5:8086.
Added realm "Cataclysm" at 10.96.20.5:8085.
The auth server process priority class has been set to HIGH

DrkJstr · May 6, 2015

ERROR: Column count doesn't match value count at row 1

In order to know why account_banned is throwing errors, I'd need to see the structure, considering 4.3.4 and 3.3.5 use the same structure; but here are fixes for the rest of the issues listed.

Code:

-- Table: account (fixes)


ALTER TABLE account 
ADD COLUMN `lock_country` varchar(2) NOT NULL DEFAULT '00' AFTER `locked`,
ADD COLUMN `reg_mail` varchar(255) NOT NULL DEFAULT '' AFTER `email`,
ADD COLUMN `last_attempt_ip` varchar(15) NOT NULL DEFAULT '127.0.0.1' AFTER `last_ip`;


-- Table: logs_ip_ations (create)


CREATE TABLE logs_ip_actions (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Identifier',
  `account_id` int(10) unsigned NOT NULL COMMENT 'Account ID',
  `character_guid` int(10) unsigned NOT NULL COMMENT 'Character Guid',
  `type` tinyint(3) unsigned NOT NULL,
  `ip` varchar(15) NOT NULL DEFAULT '127.0.0.1',
  `systemnote` text COMMENT 'Notes inserted by system',
  `unixtime` int(10) unsigned NOT NULL COMMENT 'Unixtime',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp',
  `comment` text COMMENT 'Allows users to add a comment',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used to log ips of individuals';


-- Table: account_muted (create)


CREATE TABLE `account_muted` (
  `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  `mutedate` int(10) unsigned NOT NULL DEFAULT '0',
  `mutetime` int(10) unsigned NOT NULL DEFAULT '0',
  `mutedby` varchar(50) NOT NULL,
  `mutereason` varchar(255) NOT NULL,
  PRIMARY KEY (`guid`,`mutedate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mute List';

frankthedruid · May 7, 2015

Thanks for the db info. But I have no idea what it means. I just learned how to back up and restore my db.

DrkJstr · May 7, 2015

you can run those queries against your auth database to bring in add the missing tables for 3.3.5. The account_banned is still not fixed in these queries because I need the table structure in order determine the cause of the error

DrkJstr · May 7, 2015

you can run the query against your auth database and it should correct the most of the errors with your 3.3.5 server. I still need the database structure for account_banned in order to write a fix for that one.

Here is a query to grab the structure, run it and let me know the results

Code:

-- Table Description

DESC account_banned;

frankthedruid · May 8, 2015

View attachment flap2.png.
View attachment flap3.png

this is what i get

DrkJstr · May 8, 2015

easy fix then

Code:

ALTER TABLE `account_banned` DROP COLUMN `realm`;

frankthedruid · May 10, 2015

I get these two errors when running the first query.

-- Table: logs_ip_ations (create)

CREATE TABLE logs_ip_actions (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Identifier$
`account_id` int(10) unsigned NOT NULL COMMENT 'Account ID',
`character_guid` int(10) unsigned NOT NULL COMMENT 'Character Guid',
`type` tinyint(3) unsigned NOT NULL,
`ip` varchar(15) NOT NULL DEFAULT '127.0.0.1',
`systemnote` text COMMENT 'Notes inserted by system',
`unixtime` int(10) unsigned NOT NULL COMMENT 'Unixtime',
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp',
`comment` text COMMENT 'Allows users to add a comment',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used to log ips of individua$
;
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Account ID',
`character_guid` int(10) unsigned NOT NULL COMMENT 'Character Gu' at line 5 */
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 0 of 1 query: 0.000 sec. */

-- Table: account_muted (create)

CREATE TABLE `account_muted` (
`guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Iden$
`mutedate` int(10) unsigned NOT NULL DEFAULT '0',
`mutetime` int(10) unsigned NOT NULL DEFAULT '0',
`mutedby` varchar(50) NOT NULL,
`mutereason` varchar(255) NOT NULL,
PRIMARY KEY (`guid`,`mutedate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mute List';;
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0',
`mutetime` int(10) unsigned NOT NULL DEFAULT '0',
`mutedby` varchar(50' at line 5 */
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 0 of 1 query: 0.000 sec. */

DrkJstr · May 10, 2015

Silly me, pasting from terminal is bad (and I know that too.. )

Here is the fixed version (all fixes)

Code:

-- Table: account (fixes)

ALTER TABLE account 
ADD COLUMN `lock_country` varchar(2) NOT NULL DEFAULT '00' AFTER `locked`,
ADD COLUMN `reg_mail` varchar(255) NOT NULL DEFAULT '' AFTER `email`,
ADD COLUMN `last_attempt_ip` varchar(15) NOT NULL DEFAULT '127.0.0.1' AFTER `last_ip`;


-- Table: account_banned (fix)

ALTER TABLE `account_banned` DROP COLUMN `realm`;


-- Table: logs_ip_actions (create)

CREATE TABLE logs_ip_actions (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Identifier',
  `account_id` int(10) unsigned NOT NULL COMMENT 'Account ID',
  `character_guid` int(10) unsigned NOT NULL COMMENT 'Character Guid',
  `type` tinyint(3) unsigned NOT NULL,
  `ip` varchar(15) NOT NULL DEFAULT '127.0.0.1',
  `systemnote` text COMMENT 'Notes inserted by system',
  `unixtime` int(10) unsigned NOT NULL COMMENT 'Unixtime',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp',
  `comment` text COMMENT 'Allows users to add a comment',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used to log ips of individuals';


-- Table: account_muted (create)

CREATE TABLE `account_muted` (
  `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  `mutedate` int(10) unsigned NOT NULL DEFAULT '0',
  `mutetime` int(10) unsigned NOT NULL DEFAULT '0',
  `mutedby` varchar(50) NOT NULL,
  `mutereason` varchar(255) NOT NULL,
  PRIMARY KEY (`guid`,`mutedate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mute List';

My apologies for the error..

frankthedruid · May 13, 2015

Thanks for all the help.
I am getting some errors I will try to give details soon.

frankthedruid · May 13, 2015

This query seems to cause problems for both 3.3.5 and 4.3.4. Neither will start.

-- Table: account_banned (fix)

ALTER TABLE `account_banned` DROP COLUMN `realm`;

4.3.4 works fine
This is the 3.3.5 error I get without it.

In mysql_stmt_prepare() id: 8, sql: "INSERT INTO account_banned VALUES (?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()+?, 'Trinity Auth', 'Failed login autoban', 1)"
Column count doesn't match value count at row 1
In mysql_stmt_prepare() id: 28, sql: "INSERT INTO account_banned VALUES (?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()+?, ?, ?, 1)"
Column count doesn't match value count at row 1

frankthedruid · May 13, 2015

DB errors when I run the query

when I run 4.3.4 auth

EmuCoach V2 rev. 0000-00-00 00:00:00 +0000 (Archived) (Win32, Release) (authserver)
<Ctrl-C> to stop.

Using configuration file authserver.conf.
OpenSSL 1.0.1c 10 May 2012 (Library: OpenSSL 1.0.1h 5 Jun 2014)
Opening DatabasePool 'auth'. Asynchronous connections: 1, synchronous connections: 1.
MySQL client library: 5.5.9
MySQL server ver: 5.5.9-log
Connected to MySQL database at 127.0.0.1
In mysql_stmt_prepare() id: 8, sql: "INSERT INTO account_banned VALUES (?, ?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()+?, 'EmuCoach realmd', 'Failed login autoban', 1)"
Column count doesn't match value count at row 1
In mysql_stmt_prepare() id: 27, sql: "INSERT INTO account_banned VALUES (?, ?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()+?, ?, ?, 1)"
Column count doesn't match value count at row 1
MySQL client library: 5.5.9
MySQL server ver: 5.5.9-log
Connected to MySQL database at 127.0.0.1
DatabasePool auth NOT opened. There were errors opening the MySQL connections. Check your SQLDriverLogFile for specific errors.
Cannot connect to database

4.3.4 world

In mysql_stmt_prepare() id: 8, sql: "INSERT INTO account_banned VALUES (?, ?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()+?, 'EmuCoach realmd', 'Failed login autoban', 1)"
Column count doesn't match value count at row 1
In mysql_stmt_prepare() id: 27, sql: "INSERT INTO account_banned VALUES (?, ?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()+?, ?, ?, 1)"
Column count doesn't match value count at row 1

Using configuration file worldserver.conf.
Using SSL version: OpenSSL 1.0.1c 10 May 2012 (library: OpenSSL 1.0.1h 5 Jun 2014)
Using ACE version: 6.1.4
EmuCoach V2 rev. 0000-00-00 00:00:00 +0000 (Archived) (Win32, Release) (worldserver-daemon)
<Ctrl-C> to stop.

Make sure to visit our forum - www.emucoach.com
EmuCoach Repack 434 - V2
In mysql_stmt_prepare() id: 8, sql: "INSERT INTO account_banned VALUES (?, ?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()+?, 'EmuCoach realmd', 'Failed login autoban', 1)"
Column count doesn't match value count at row 1
In mysql_stmt_prepare() id: 27, sql: "INSERT INTO account_banned VALUES (?, ?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()+?, ?, ?, 1)"
Column count doesn't match value count at row 1
DatabasePool auth NOT opened. There were errors opening the MySQL connections. Check your SQLDriverLogFile for specific errors.
Cannot connect to login database 127.0.0.1;3306;root;ascent;auth

3.3.5 errors

In mysql_stmt_prepare() id: 13, sql: "SELECT a.sha_pass_hash, a.id, a.locked, a.lock_country, a.last_ip, aa.gmlevel, a.v, a.s, a.token_key FROM account a LEFT JOIN account_access aa ON (a.id = aa.id) WHERE a.username = ?"
Unknown column 'a.token_key' in 'field list'
In mysql_stmt_prepare() id: 72, sql: "SELECT id, weight, text FROM autobroadcast WHERE realmid = ? OR realmid = -1"
Table 'auth.autobroadcast' doesn't exist