Mini How-To suggestion and correction

Want to talk about war3ft or make suggestions? Post them here

Moderator: Forum Moderator

Post Reply
anders_dog
Militia
Posts: 68
Joined: Thu Sep 01, 2005 5:23 pm

Mini How-To suggestion and correction

Post by anders_dog » Thu Sep 01, 2005 5:36 pm

Hi there,

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.

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.

Just a suggestion.

Cheers
Anders

User avatar
Lazarus Long
Lead PITA SOB
Posts: 618
Joined: Tue Jul 05, 2005 9:24 pm

Re: Mini How-To suggestion and correction

Post by Lazarus Long » Thu Sep 01, 2005 8:06 pm

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,
Lazarus

anders_dog
Militia
Posts: 68
Joined: Thu Sep 01, 2005 5:23 pm

Re: Mini How-To suggestion and correction

Post by anders_dog » Fri Sep 02, 2005 4:35 am

Lazarus Long wrote:Hello, anders_dog:
Thank you for your interest and feedback.
You're welcome! It only takes a moment to give feedback, but I know how much I appreciate it on my jobs, whether it's good or bad news.
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.
Hmmm. If so then the code itself needs looking at -- this is from the mysql manual for pre 4.1 TIMESTAMP behaviour:
Automatic updating of the first TIMESTAMP column in a table occurs under any of the following conditions:

* You explicitly set the column to NULL.
* The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.
* The column is not specified explicitly in an UPDATE statement and some other column changes value. An UPDATE that sets a column to the value it does not cause the TIMESTAMP column to be updated; if you set a column to its current value, MySQL ignores the update for efficiency.
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'm unfamiliar with sqlite syntax, so assumed that those statements were for it rather than mysql. The latter chokes on the CURRENT_TIMESTAMP bit completely. The create table instruction I quoted above is more or less the same as the one built into wc3ft. I'm not as up with the play on mysql standards as I was maybe 6 months back, but, I can tell you right now that that sql statement will not run on a mysql server.
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?
As above -- the embedded create table sql does not have any of that CURRENT_TIMESTAMP stuff. It's not needed for MySQL as long as the date is set to NULL, or left out of the field list on updates.
Once again thank you for the feedback,
Regards,
And, once again, you're quite welcome. Thanks for a great mod. Our punters certainly enjoy it!

Cheers
Anders

User avatar
Lazarus Long
Lead PITA SOB
Posts: 618
Joined: Tue Jul 05, 2005 9:24 pm

Re: Mini How-To suggestion and correction

Post by Lazarus Long » Fri Sep 02, 2005 7:34 pm

Hello, anders_dog:
anders_dog wrote:Hmmm. If so then the code itself needs looking at -- this is from the mysql manual for pre 4.1 TIMESTAMP behaviour:
Automatic updating of the first TIMESTAMP column in a table occurs under any of the following conditions:

* You explicitly set the column to NULL.
* The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.
* The column is not specified explicitly in an UPDATE statement and some other column changes value. An UPDATE that sets a column to the value it does not cause the TIMESTAMP column to be updated; if you set a column to its current value, MySQL ignores the update for efficiency.
And now you're going to make me install MySQL 4.0, sigh... I've tested the CREATE statement yesterday before posting on 4.1, and again today on 5.0 and 4.1 and they digested it flawless, so you're going to make me have yet another database server running, to keep checking the SQL code. Why do they keep changing things around?

I'll check if the behaviour that you stated above remains the same in 4.1 and 5.0, but since I tried it out before coming out with this code seems that it might had changed, if I'm wrong I'll update both the Mini-HOWTO and send a patch to the code to Geesu, else I'll have to make a patch to check MySQL version and use the correct CREATE statement. Thanks for bringing this up.

anders_dog wrote:I'm unfamiliar with sqlite syntax, so assumed that those statements were for it rather than mysql. The latter chokes on the CURRENT_TIMESTAMP bit completely. The create table instruction I quoted above is more or less the same as the one built into wc3ft. I'm not as up with the play on mysql standards as I was maybe 6 months back, but, I can tell you right now that that sql statement will not run on a mysql server.
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?
As above -- the embedded create table sql does not have any of that CURRENT_TIMESTAMP stuff. It's not needed for MySQL as long as the date is set to NULL, or left out of the field list on updates.
OK, I got it now, as I thought you have an older version of the database server, since 4.0 has now lost the recommended status and will be kept in the GA status, just until 5.0 leaves beta.

Once again thank you for your interest and so well documented report.

Best regards,
Lazarus

anders_dog
Militia
Posts: 68
Joined: Thu Sep 01, 2005 5:23 pm

Post by anders_dog » Sat Sep 03, 2005 5:47 pm

You're welcome! Now -- the actual code for creating the db table in the plugin is fine. It looks like this:

Code: Select all

format (mquery, 511, "CREATE TABLE IF NOT EXISTS `%s` ( `playerid` VARCHAR(35) NOT NULL, `playername` VARCHAR(35) NOT NULL, `xp` INT(11) NOT NULL, `race` TINYINT(4) NOT NULL, `skill1` TINYINT(4), `skill2` TINYINT(4), `skill3` TINYINT(4), `skill4` TINYINT(4), `time` TIMESTAMP( 14 ) NOT NULL, PRIMARY KEY (playerid,race))",mysqltablename)
i.e.,

Code: Select all

CREATE TABLE IF NOT EXISTS `wc3users` (
`playerid` VARCHAR(35) NOT NULL, 
`playername` VARCHAR(35) NOT NULL,
`xp` INT(11) NOT NULL,
`race` TINYINT(4) NOT NULL, 
`skill1` TINYINT(4), 
`skill2` TINYINT(4), 
`skill3` TINYINT(4), 
`skill4` TINYINT(4), 
`time` TIMESTAMP( 14 ) NOT NULL, 
PRIMARY KEY (playerid,race))
So, geesu's query will run fine, even on MySQL 3.x. I wouldn't bother installing 4.0 unless you've got time on your hands and you're feeling bored. :-)

Cheers
Anders

User avatar
Lazarus Long
Lead PITA SOB
Posts: 618
Joined: Tue Jul 05, 2005 9:24 pm

Post by Lazarus Long » Sat Sep 03, 2005 6:25 pm

Actualy it isn't, it was changed in CVS when adapted to SQLite support, we'll have to re-adapt it.
Lazarus

User avatar
Geesu
<b>King of the world!</b>
Posts: 3159
Joined: Tue Jul 05, 2005 9:24 pm
Contact:

Post by Geesu » Sun Sep 04, 2005 11:38 am

Let me know what I need to change Laz, I'm confused :/
No Support via PM

User avatar
Lazarus Long
Lead PITA SOB
Posts: 618
Joined: Tue Jul 05, 2005 9:24 pm

Post by Lazarus Long » Sun Sep 04, 2005 3:21 pm

Hi, Geesu:

Wait a bit longer, I'm testing it. You know how's my drill, I have to make sure before saying it's this, or that.

It takes a while testing with SQLite and 3 MySQL server versions.

Cheers,
Lazarus

anders_dog
Militia
Posts: 68
Joined: Thu Sep 01, 2005 5:23 pm

Post by anders_dog » Sun Sep 04, 2005 3:27 pm

Lazarus Long wrote:Hi, Geesu:

Wait a bit longer, I'm testing it. You know how's my drill, I have to make sure before saying it's this, or that.

It takes a while testing with SQLite and 3 MySQL server versions.

Cheers,

If you like I can give you ssh access to a server with MySQL 4.0.x and WC3FT on it.

User avatar
Lazarus Long
Lead PITA SOB
Posts: 618
Joined: Tue Jul 05, 2005 9:24 pm

Post by Lazarus Long » Sun Sep 04, 2005 7:22 pm

anders_dog wrote:If you like I can give you ssh access to a server with MySQL 4.0.x and WC3FT on it.
Thank you, but no need to, I got it (anyway it's better to do it in a non-production server since it's less disruptive). You were right, it only was needed to take off "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" from the statement and it runs on all three MySQL versions.

Once again thank you for bringing this up. I'll be updating the Mini-HOWTO's and I'm sure that Geesu will update CVS.

Best regards,
Lazarus

Post Reply