Hello, anders_dog:
Thank you for your interest and feedback.
anders_dog wrote:The MySQL create table SQL is incorrect. It looks like it's designed for SQLite.
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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`playerid`, `race`)) COMMENT='Warcraft 3 FT XP Storage' ;
Should be:
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(14) NOT NULL,
PRIMARY KEY (`playerid`, `race`))
COMMENT='Warcraft 3 FT XP Storage' ;
i.e., drop the DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP bit
I'll suggest that you try your SQL statement, play with a friend for a while and then inspect the
war3users table. Look at the field
time, and you'll notice that you don't get the last time on the server, which is needed for pruning.
Actually the first SQL statement was built for
MySQL, since it doesn't run on
SQLite which doesn't understand neither "
ON UPDATE CURRENT_TIMESTAMP", nor "
COMMENT='Warcraft 3 FT XP Storage'".
I recall somebody complaining about
CURRENT_TIMESTAMP before, I don't remember if it was you, but it's definitely needed and is standard both for
MySQL and
SQLite, so your problem might have something to do with the way the database server is configured, or you have an outdated version of the database, you see, that
CREATE statement is in the game also, so can you please post what specific error is it rendering?
anders_dog wrote:Also, I'd like to suggest changing the vault to table conversion awk script to something like this:
Code: Select all
awk '{ if (/;|#|^ *$|\/\//) next ; if (NF != 14) next ; printf "REPLACE INTO war3users (playerid, playername, xp, race, skill1, skill2, skill3, skill4, `time`) VALUES ( "%s", "%s", "%s", "%s", "%s", "%s", "%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), strftime("%Y"), $(NF-3), $(NF-2), $(NF-1), $(NF), "00" }' <./amxmodx/data/vault.ini |mysql -u amx -p<amx user password> amx
Or the hex version of " if you prefer. It's just that ' is such a common character in players' names.
Oops, Lazarus made a boo boo. I thought I had that fixed, but probably got mixed versions when either brought the
Mini-HOWTO from the old forums or split it up to make it modular. I'll fix it and change it to:
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
which is how I had it fixed before.
Once again thank you for the feedback,
Regards,