Page 1 of 1

MySQL for Warcraft 3 FT simple Mini-HOWTO

Posted: Wed Jul 06, 2005 11:24 am
by Lazarus Long
[anchor]a0[/anchor]Index:
  1. I - [goto=a1]Prerequisites[/goto]

    II - [goto=a2]Warcraft 3 FT[/goto]
    1. [goto=a2d1]Stopping the server[/goto]
    2. [goto=a2d2]Configuring Warcraft 3 FT[/goto]
    III - [goto=a3]Converting XP to MySQL[/goto]
    1. [goto=a3d1]Skipping (for fresh server installation)[/goto]
    2. [goto=a3d2]Preparing the database[/goto]
    3. [goto=a3d3]Converting Warcraft 3 FT XP[/goto]
      1. [goto=a3d3d1]Converting from Files[/goto]
        1. [goto=a3d3d1d1]Using the Vault to MySQL conversion page[/goto]
        2. [goto=a3d3d1d2]Using a Do-It-Yourself approach[/goto]
      2. [goto=a3d3d2]Converting from SQLite[/goto]
    IV - [goto=a4]Wrap[/goto]
    1. [goto=a4d1]Starting the server[/goto]
[anchor]a1[/anchor]I - Prerequisites:
  1. [anchor]a1d1[/anchor]You have a working Warcraft 3 FT server installation using either the vault.ini file or the SQLite server for XP storage (later on you'll be [goto=a3]converting it[/goto]).
  2. [anchor]a1d2[/anchor]You have read, followed and completed the MySQL for AMX Mod X simple Mini-HOWTO (The simple Mini-HOWTO series structure is being modularized so this document you are reading contains only partial information, you will not be able to complete it without following the above link).
[anchor]a2[/anchor]II - Warcraft 3 FT:
  1. [anchor]a2d1[/anchor]Start by stopping your game server in the usual way for your system. If you are running a listen server simply quit the game, if you are running a dedicated server you may have several ways to stop it, if everything else fails issue the command

    Code: Select all

    quit
    in the game console.
  2. [anchor]a2d2[/anchor]You now have to configure Warcraft 3 FT to access the MySQL database:
    1. [anchor]a2d2d1[/anchor]The Warcraft 3 FT configuration file is a text file and you will be editing it with your system text editor, either Notepad for Microsoft Windows, or vi for GNU Linux (your default editor might not be vi, so use the one you are used to, I'll be using vi for the remaining of this Mini-HOWTO, so replace where appropriate).
    2. [anchor]a2d2d2[/anchor]Locate your base directory for the game (don't ask me where it is, you installed the thing) and change to the AMX Mod X configuration folder.
      1. [anchor]a2d2d2d1[/anchor]Issue the following command in a Command Prompt for Microsoft Windows (replace <steam account> with your Steam login name and <game mod folder> with the game you are running, either "Counter-Strike\cstrike", "Condition Zero\czero", "Day of Defeat\dod", "Dedicated Server\cstrike", "Dedicated Server\czero" or "Dedicated Server\dod")

        Code: Select all

        cd "SteamApps\<steam account>\<game mod folder>\addons\amxmodx\configs"
      2. [anchor]a2d2d2d2[/anchor]Issue the following command in a shell for GNU Linux (replace <game mod> with the game you are running, either cstrike, czero or dod)

        Code: Select all

        cd <game mod>/addons/amxmodx/configs
    3. [anchor]a2d2d3[/anchor]Edit the war3FT.cfg configuration file.
      1. [anchor]a2d2d3d1[/anchor]Issue the following command in a Command Prompt for Microsoft Windows

        Code: Select all

        notepad war3FT.cfg
      2. [anchor]a2d2d3d2[/anchor]Issue the following command in a shell for GNU Linux

        Code: Select all

        vi war3FT.cfg
      3. [anchor]a2d2d3d3[/anchor]Edit mp_savexp, sv_mysql, FT_mysql_host, FT_mysql_user, FT_mysql_pass, FT_mysql_db and sv_mysqltablename to reflect your configuration (you might want to have a look at sv_mysql_save_end_round and/or at sv_mysql_auto_pruning and sv_daysbeforedelete). Be sure to read the comments in war3FT.cfg, since they are very helpful, the following is a snippet of the war3FT.cfg reflecting our changes

        Code: Select all

        // Warcraft 3 Frozen Throne Configuration File
        // Note: NO MATTER WHAT, you must have [color=green]a database[/color] module running
        // even if you're not using it
        
        [color=green]mp_savexp		1[/color]		// Enables saving of experience (uses a vault, default is 0)
        [color=green]sv_mysql		1[/color]		// this will save w/MySQL (note: to use this mp_savexp must also be 1, default is 0)
        FT_saveby		0		// What should I save this as?  steam id = 0, IP = 1, name = 2 (works for vault and mysql, default is 0)
        
        // MySQL configuration
        FT_mysql_host		"127.0.0.1"	// Host Name
        [color=green]FT_mysql_user		"amxx"[/color]		// User Name
        [color=green]FT_mysql_pass		"<amxx user password>"		// Password
        [color=green]FT_mysql_db		"amxx"[/color]		// Database Name
        sv_mysql_auto_pruning	0		// Automatically prunes the database of old users at a mapchange occuring between 5:36 to 5:59 AM (default is 0, doesn't work for vault)
        sv_daysbeforedelete	31		// However many days before deleting XP or pruning from MySQL database (default is 31)
        sv_mysqltablename	"war3users"	// Table Name, no need to change (default is war3users)
        sv_mysql_save_end_round 0		// Save Users at the end of each round (causes slight lag, default is 0)
        FT_disable_savexp	0		// Set this to 1 to disable users typing /savexp (default is 0)
[anchor]a3[/anchor]III - Converting XP to SQLite:
  1. [anchor]a3d1[/anchor]If you don't have any Warcraft 3 FT XP you would like to convert to SQLite database format you can [goto=a4]wrap it up[/goto].
  2. [anchor]a3d2[/anchor]Preparing the database:
    1. [anchor]a3d2d1[/anchor]Login as the user amxx you have created before by issuing the following command either in a Command Prompt for Microsoft Windows or in a shell for GNU Linux (note that there is no space between -p and the password)

      Code: Select all

      mysql -u amxx -p<amxx user password> amxx
    2. [anchor]a3d2d2[/anchor]Create the Warcraft 3 FT war3users table by issuing the following command in the MySQL interactive shell

      Code: Select all

      CREATE TABLE `war3users` (`playerid` VARCHAR(35) NOT NULL DEFAULT '', `playername` VARCHAR(35) NOT NULL DEFAULT '', `xp` INT(11) NOT NULL DEFAULT '0', `race` TINYINT(4) NOT NULL DEFAULT '0', `skill1` TINYINT(4) NOT NULL DEFAULT '0', `skill2` TINYINT(4) NOT NULL DEFAULT '0', `skill3` TINYINT(4) NOT NULL DEFAULT '0', `skill4` TINYINT(4) NOT NULL DEFAULT '0', `time` TIMESTAMP NOT NULL, PRIMARY KEY (`playerid`, `race`)) COMMENT='Warcraft 3 FT XP Storage' ;
      quit
  3. [anchor]a3d3[/anchor]Converting Warcraft 3 FT XP:
    1. [anchor]a3d3d1[/anchor]Converting from Files:
      1. [anchor]a3d3d1d1[/anchor]Using the Vault to MySQL conversion page:
        1. [anchor]a3d3d1d1d1[/anchor]Allow access from the Internet:
          1. [anchor]a3d3d1d1d1d1[/anchor]For Microsoft Windows open a Command Prompt and issue the following commands (replace <MySQL basedir> with the path where you installed the MySQL server - usually "%ProgramFiles%\MySQL\MySQL Server 4.1", with %ProgramFiles% being your Program Files base folder)

            Code: Select all

            cd <MySQL basedir>
            notepad my.ini
          2. [anchor]a3d3d1d1d1d2[/anchor]For GNU Linux open a shell and issue the following commands (replace <MySQL confdir> with the path where your MySQL server has it's configuration file - usually "/etc/mysql")

            Code: Select all

            cd <MySQL confdir>
            vi my.cnf
          3. [anchor]a3d3d1d1d1d3[/anchor]Locate the server section and make sure that you comment out the "bind-address=127.0.0.1" entry by inserting a hash "#" before it. Save the file and exit the file editor.
          4. [anchor]a3d3d1d1d1d4[/anchor]Restart your server, by issuing the following for Microsoft Windows

            Code: Select all

            NET STOP MySQL
            NET START MySQL
            or the following for GNU Linux

            Code: Select all

            /etc/init.d/mysql restart
        2. [anchor]a3d3d1d1d2[/anchor]Login as the MySQL root user by issuing the following command either in the above Command Prompt for Microsoft Windows or the above shell for GNU Linux (note that there is no space between -p and the password)

          Code: Select all

          mysql -u root -p<root user password> mysql
        3. [anchor]a3d3d1d1d3[/anchor]Grant access to the Vault to MySQL conversion page to your AMX Mod X database by issuing the following commands in the MySQL interactive shell

          Code: Select all

          GRANT USAGE ON * . * TO 'amxx'@'38.113.137.7' IDENTIFIED BY '<amxx user password>' ;
          GRANT USAGE ON * . * TO 'amxx'@'war3ft.com' IDENTIFIED BY '<amxx user password>' ;
          GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES , LOCK TABLES ON `amxx` . * TO 'amxx'@'38.113.137.7' ;
          GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES , LOCK TABLES ON `amxx` . * TO 'amxx'@'war3ft.com' ;
          
        4. [anchor]a3d3d1d1d4[/anchor]Access the Vault to MySQL conversion page and enter the requested information (if you are accessing the conversion page from a different machine from were you have your game server you must copy your vault.ini to this machine prior to doing this step)

          Code: Select all

          MySQL Server: [color=green]<your MySQL server IP or name>[/color]
          Username: [color=green]amxx[/color]
          Password: [color=green]<amxx user password>[/color]
          Database name: [color=green]amxx[/color]
          Table name: war3users
          Vault file: [color=green]<path to your vault.ini file>[/color]
        5. [anchor]a3d3d1d1d5[/anchor]After a correct vault.ini conversion (read the output of the Vault to MySQL conversion page) return to the MySQL interactive shell and issue the following commands

          Code: Select all

          REVOKE ALL PRIVILEGES ON * . * FROM 'amxx'@ '38.113.137.7' ;
          REVOKE ALL PRIVILEGES ON * . * FROM 'amxx'@ 'war3ft.com' ;
          REVOKE ALL PRIVILEGES ON `amxx` . * FROM 'amxx'@ '38.113.137.7' ;
          REVOKE ALL PRIVILEGES ON `amxx` . * FROM 'amxx'@ 'war3ft.com' ;
          DELETE FROM `user` WHERE User = 'amxx' AND Host = '38.113.137.7' ;
          DELETE FROM `user` WHERE User = 'amxx' AND Host = 'war3ft.com' ;
          quit
        6. [anchor]a3d3d1d1d6[/anchor]Revoke access from the Internet:
          1. [anchor]a3d3d1d1d6d1[/anchor]For Microsoft Windows issue the following in the above Command Prompt

            Code: Select all

            notepad my.ini
          2. [anchor]a3d3d1d1d6d2[/anchor]For GNU Linux issue the following in the above shell

            Code: Select all

            vi my.cnf
          3. [anchor]a3d3d1d1d6d3[/anchor]Locate the server section and remove the hash "#" from the "bind-address=127.0.0.1" entry. Save the file and exit the file editor.
          4. [anchor]a3d3d1d1d6d4[/anchor]Restart your server, by issuing the following for Microsoft Windows

            Code: Select all

            NET STOP MySQL
            NET START MySQL
            or the following for GNU Linux

            Code: Select all

            /etc/init.d/mysql restart
      2. [anchor]a3d3d1d2[/anchor]Using a Do-It-Yourself approach in case you are behind a firewall you don't have control of or in case the above approach doesn't work for you:
        1. [anchor]a3d3d1d2d1[/anchor]Populate the Warcraft 3 FT XP table with your players XP (you can find the values to enter in the following SQL statements in the vault.ini file in your AMX Mod X data directory)
          1. [anchor]a3d3d1d2d1d1[/anchor]If your server is running on Microsoft Windows you are really out of luck, you will have to do this by hand (and if so be aware that there might be hundreds of entries in your vault.ini) so issue the following commands in the SQLite interactive shell

            Code: Select all

            REPLACE INTO `war3users` (`playerid`, `playername`, `xp`, `race`, `skill1`, `skill2`, `skill3`, `skill4`) VALUES ('<1st user id>', '<1st user name>', <1st user XP>, <1st user race>, <1st user skill1 level>, <1st user skill2 level>, <1st user skill3 level>, <1st user skill4 level>) ;
            REPLACE INTO `war3users` (`playerid`, `playername`, `xp`, `race`, `skill1`, `skill2`, `skill3`, `skill4`) VALUES ('<2nd user id>', '<2nd user name>', <2nd user XP>, <2nd user race>, <2nd user skill1 level>, <2nd user skill2 level>, <2nd user skill3 level>, <2nd user skill4 level>) ;
            ...
            REPLACE INTO `war3users` (`playerid`, `playername`, `xp`, `race`, `skill1`, `skill2`, `skill3`, `skill4`) VALUES ('<last user id>', '<last user name>', <last user XP>, <last user race>, <last user skill1 level>, <last user skill2 level>, <last user skill3 level>, <last user skill4 level>) ;
          2. [anchor]a3d3d1d2d1d2[/anchor]If your server is running on GNU Linux you can either do as above for Microsoft Windows (recommended if you have 1 or 2 players) or you may exit the SQLite interactive shell (as indicated in the [goto=a3d3d1d2d2]next point[/goto]) and issue the following command in a shell (run this in the AMX Mod X configs directory - you already should be there - and note that there is no space between -p and the password)

            Code: Select all

            awk '{ if (/;|#|^ *$|\/\//) next ; if (NF != 14) next ; printf "REPLACE INTO `war3users` (`playerid`, `playername`, `xp`, `race`, `skill1`, `skill2`, `skill3`, `skill4`) VALUES (\"%s\", \"%s\", %s, %s, %s, %s, %s, %s);\n", substr($(NF-13), 0, length($(NF-13))-2), $(NF-5), $(NF-11), $(NF-10), $(NF-9), $(NF-8), $(NF-7), $(NF-6)}' <../data/vault.ini |mysql -u amxx -p<amxx user password> amxx
        2. [anchor]a3d3d1d2d2[/anchor]Exit the MySQL interactive shell by issuing the following command

          Code: Select all

          quit
    2. [anchor]a3d3d2[/anchor]Converting from SQLite:
      1. [anchor]a3d3d2d1[/anchor]If you had your game server previously configured to save/restore your players XP to/from a SQLite database you can convert it to MySQL:
        1. [anchor]a3d3d2d1d1[/anchor]Exit the MySQL interactive shell by issuing the following command

          Code: Select all

          quit
        2. [anchor]a3d3d2d1d2[/anchor]For Microsoft Windows:
          1. [anchor]a3d3d2d1d2d1[/anchor]Issue the following commands in a Command Prompt (replace <SQLite complete database path> with the full path, including filename and extension, of the SQLite database you are converting from, note the dot at the beginning of ".dump" and ".quit" and that you won't see a prompt when you enter ".quit", just do it anyway)

            Code: Select all

            echo .dump admins >sqlite.cfg
            sqlite3 -init sqlite.cfg "<SQLite complete database path>" >war3ft.sql
            .quit
            del sqlite.cfg
            notepad war3ft.sql
          2. [anchor]a3d3d2d1d2d2[/anchor]Edit the war3ft.sql file and remove the two first and the last lines from it, the only lines that should remain are those that start with "INSERT". Exit Notepad saving the file.
          3. [anchor]a3d3d2d1d2d3[/anchor]Issue the following commands in a Command Prompt (note that there is no space between -p and the password)

            Code: Select all

            mysql -u amxx -p<amxx password> amxx <war3ft.sql
            del war3ft.sql
        3. [anchor]a3d3d2d1d3[/anchor]For GNU Linux:
          1. [anchor]a3d3d2d1d3d1[/anchor]Issue the following commands in a shell (replace <SQLite complete database path> with the full path, including filename and extension, of the SQLite database you are converting from, note the dot at the beginning of ".dump" and ".quit" and that you won't see a prompt when you enter ".quit", just do it anyway)

            Code: Select all

            echo .dump admins >sqlite.cfg
            sqlite3 -init sqlite.cfg "<SQLite complete database path>" >war3ft.sql
            .quit
            rm sqlite.cfg
            vi war3ft.sql
          2. [anchor]a3d3d2d1d3d2[/anchor]Edit the war3ft.sql file and remove the two first and the last lines from it, the only lines that should remain are those that start with "INSERT". Exit vi saving the file.
          3. [anchor]a3d3d2d1d3d3[/anchor]Issue the following commands in a shell (note that there is no space between -p and the password)

            Code: Select all

            mysql -u amxx -p<amxx password> amxx <war3ft.sql
            rm war3ft.sql
[anchor]a4[/anchor]IV - Wrap:
  1. [anchor]a4d1[/anchor]You are done, it's now time to start your game server in the usual way for your system.
OK, I hope I didn't make any big mistake, or forget something important, if so, feel free to correct me, and I'll try to update this Mini-HOWTO. Thanks for reading this far.


Edits:
  • 2005-07-06 - migrated from the previous support forum
  • 2005-07-21 - updated the URLs to reflect the new homepage
  • 2005-08-12 - indexed the post (somewhat buggy since it doesn't seem to work with Firefox)
  • 2005-08-27 - split the Mini-HOWTO to keep AMX Mod X generic setup separated from the MODs specific
  • 2005-08-27 - added information about converting from SQLite
  • 2005-08-28 - fixed a bunch of small typos
  • 2005-09-03 - changed the awk script to allow the ' character in names, by sugestion of anders_dog
  • 2005-09-03 - removed the dealing with the timestamp from the awk script and changed the amx user to amxx (I must have been on acid)
  • 2005-09-05 - changed the CREATE SQL statement to support pre-4.1 versions of MySQL, by sugestion of anders_dog
  • 2005-10-02 - added reference paths for a dedicated server install under Microsoft Windows