MySQL Syntax error !

Forum Dedicated to PvPLog, (The WoW PvP data collector addon), and PvPLog related topics

Moderator: bsmorgan

MySQL Syntax error !

Postby Satis » Wed Jan 17, 2007 2:01 pm

Hi I'm running Roster 1.7.2 and have PvPLog v.2.3.2

When I upload the PvPLog.lua file it gives me na syntax error as showen down below.
Code: Select all
PvPLog cannot update
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 ''2007-01-17 17:02:33 AND `realm` = ''' at line 1

I have attached my PvPLog.lua file

It's an small log only 2 duels noted inside it.

What can the problem be?

Oh and by the way:
I used the PvPLog.lua stored inside wowfolder/WTF/AccountName/SavedVariables

Hope you can give me an answere on this.
Attachments
pvplog.lua
(3.12 KiB) Downloaded 65 times
Satis
WR.net Apprentice
WR.net Apprentice
 
Posts: 17
Joined: Tue Jan 16, 2007 9:17 am

Re: MySQL Syntax error !

Postby Kareena » Wed Jan 17, 2007 8:32 pm

These have just appeared for me too following the install of the latest patch to both luaparser.php and wowdb.php.

My Roster: http://www.theorderofhope.org/roster/index.php

PvPLog.lua also attached.

MySQL Version: 4.1.21-log
Attachments
pvplog.lua
PvPLog.lua for testing errors described above.
(55.85 KiB) Downloaded 74 times
User avatar
Kareena
WR.net Apprentice
WR.net Apprentice
 
Posts: 7
Joined: Thu Aug 03, 2006 10:02 pm

MySQL Syntax error !

Postby zanix » Wed Jan 17, 2007 10:45 pm

Try this

Open
lib/wowdb.php

Find
Code: Select all
         $playerInfo = $data[$index];
         $playerName = $playerInfo['name'];
         $playerDate = date('Y-m-d G:i:s', strtotime($playerInfo['date']));

         // skip if entry already there
         $querystr = "SELECT `guild` FROM `".ROSTER_PVP2TABLE."` WHERE `index` = '$index' AND `member_id` = '$memberId' AND `name` = '$playerName' AND `date` = '$playerDate'";


Replace with
Code: Select all
         $playerInfo = $data[$index];
         $playerName = $playerInfo['name'];
         $playerDate = date('Y-m-d G:i:s', strtotime($playerInfo['date']));
         $playerRealm = $playerInfo['realm'];

         // skip if entry already there
         $querystr = "SELECT `guild` FROM `".ROSTER_PVP2TABLE."` WHERE `index` = '$index' AND `member_id` = '$memberId' AND `name` = '".$this->escape( $playerName )."' AND `date` = '".$this->escape( $playerDate ).( !empty($playerRealm) ? " AND `realm` = '".$this->escape( $playerRealm )."'" : '' );
Read the Forum Rules, the WiKi, and Search before posting!
WoWRoster v2.1 - SigGen v0.3.3.523 - WoWRosterDF
User avatar
zanix
Admin
Admin
WoWRoster.net Dev Team
WoWRoster.net Dev Team
UA/UU Developer
UA/UU Developer
 
Posts: 5543
Joined: Mon Jul 03, 2006 8:29 am
Location: Idaho Falls, Idaho
Realm: Doomhammer (PvE) - US

Re: MySQL Syntax error !

Postby Satis » Thu Jan 18, 2007 2:00 pm

Okay first the string I should finde was not mutch diferent then the one that I hade to replace.

My String in wowdb.php
Code: Select all
$playerInfo = $data[$index];
$playerName = $playerInfo['name'];
$playerDate = date('Y-m-d G:i:s', strtotime($playerInfo['date']));
$playerRealm = $playerInfo['realm'];

// skip if entry already there
$querystr = "SELECT `guild` FROM `".ROSTER_PVP2TABLE."` WHERE `index` = '$index' AND `member_id` = '$memberId' AND `name` = '".$this->escape( $playerName )."' AND `date` = '".$this->escape( $playerDate )." AND `realm` = '".$this->escape( $playerRealm )."'";


And this one was the one that I should replace:
Code: Select all
$playerInfo = $data[$index];
$playerName = $playerInfo['name'];
$playerDate = date('Y-m-d G:i:s', strtotime($playerInfo['date']));
$playerRealm = $playerInfo['realm'];

// skip if entry already there
$querystr = "SELECT `guild` FROM `".ROSTER_PVP2TABLE."` WHERE `index` = '$index' AND `member_id` = '$memberId' AND `name` = '".$this->escape( $playerName )."' AND `date` = '".$this->escape( $playerDate ).( !empty($playerRealm) ? " AND `realm` = '".$this->escape( $playerRealm )."'" : '' );


The only diferent in those 2 are som extra added in the query string.

Now I tryed to replace it with the one you posted and I still get the same error.

BUT

I found the problem :thumleft:

in the query string, you have missed an single quote ( ' ) to close the data input info.

This string is from the original query string in my wowdb.php
Here is the problem:
Code: Select all
AND `date` = '".$this->escape( $playerDate )." AND

and here is the fix:
Code: Select all
AND `date` = '".$this->escape( $playerDate )."' AND


Notis the single quote that i have added right before the last AND statement.


In the new query you posted to replace with then problem is right here:
Code: Select all
AND `date` = '".$this->escape( $playerDate ).( !empty($playerRealm) ? " AND `realm` = '".$this->escape( $playerRealm )."'" : '' );

and here is the fix:
Code: Select all
AND `date` = '".$this->escape( $playerDate )."'".( !empty($playerRealm) ? " AND `realm` = '".$this->escape( $playerRealm )."'" : '' );

Notis the [ "'". ] i have placed.

Now after I did that there was no problem at all.
No error, no problem, only success :thumright:

I have tryed both queryes (The original one that was in my wowdb.php and the one to replace with) and they both work.

Now the question is, witch one of the queryes should I use ??

Have fun
//Satis
Satis
WR.net Apprentice
WR.net Apprentice
 
Posts: 17
Joined: Tue Jan 16, 2007 9:17 am

Re: MySQL Syntax error !

Postby Danysia » Thu Jan 18, 2007 5:03 pm

I agree with Satis.

The bug is in line 2098 of wowdb.php, missing ' after the date-part before AND.

Correct line:
Code: Select all
$querystr = "SELECT `guild` FROM `".ROSTER_PVP2TABLE."` WHERE `index` = '$index' AND `member_id` = '$memberId' AND `name` = '".$this->escape( $playerName )."' AND `date` = '".$this->escape( $playerDate )."' AND `realm` = '".$this->escape( $playerDate )."' AND `realm` = '".$this->escape( $playerRealm )."'";
Danysia
WR.net Apprentice
WR.net Apprentice
 
Posts: 1
Joined: Sat Aug 26, 2006 9:06 pm

MySQL Syntax error !

Postby zanix » Thu Jan 18, 2007 10:50 pm

Oops...

Do use the new query
If realm is blank, it shouldn't try to match based on realm as well
Last edited by zanix on Thu Jan 18, 2007 10:57 pm, edited 1 time in total.
Read the Forum Rules, the WiKi, and Search before posting!
WoWRoster v2.1 - SigGen v0.3.3.523 - WoWRosterDF
User avatar
zanix
Admin
Admin
WoWRoster.net Dev Team
WoWRoster.net Dev Team
UA/UU Developer
UA/UU Developer
 
Posts: 5543
Joined: Mon Jul 03, 2006 8:29 am
Location: Idaho Falls, Idaho
Realm: Doomhammer (PvE) - US

Re: MySQL Syntax error !

Postby bsmorgan » Fri Feb 09, 2007 10:05 am

Make sure you have edited lib/wowdb.php with the changes posted in this thread. This query including the realm test is necessary to eliminate duplicate entries. There's more than one attempt to get the query string right but there's a good one posted in this thread.

Also see the thread labeled PvPLog 2.3.2 for the SQL necessary to update the guild name if you have PvPLog data from both before and after 2.3.2.
There's an in game command (/pl fixguild) that will get all the data in your savedvariables file consistent.

If this doesn't fix the problem, then I'll need to see an example. Please post a URL of the roster showing the problem and attach the PvPLog.lua file here so I can see if I can reproduce the problem.

Regards,

Brad
User avatar
bsmorgan
WoW Interface Developer
WoW Interface Developer
 
Posts: 160
Joined: Tue Nov 28, 2006 10:17 pm
Location: Colorado Springs, Colorado

Re: MySQL Syntax error !

Postby Angelkiller » Fri Feb 09, 2007 11:05 am

I've discovered another problem. It seems when i Upload the PvPlog Data its creating duplicate records..
Angelkiller
WR.net Apprentice
WR.net Apprentice
 
Posts: 8
Joined: Fri Feb 02, 2007 5:18 pm

MySQL Syntax error !

Postby dt1375 » Fri Feb 09, 2007 6:40 pm

Same with our roster.
Is it a must to delete det pvplog.lua file after each upload, or is the script matching earlier data, just not to duplicate them, and there is a script error?

Anyone wiht no duplicate records on the PVPlog part?
User avatar
dt1375
WR.net Apprentice
WR.net Apprentice
 
Posts: 17
Joined: Wed Jan 24, 2007 1:26 pm

Re: MySQL Syntax error !

Postby dt1375 » Sat Feb 10, 2007 2:17 pm

Tnx bsmorgan...

Sortet out with the code explenation as mentioned above.
Deletetd the data in sql, and restarted the uploading. Works okay no, and no dupes.
User avatar
dt1375
WR.net Apprentice
WR.net Apprentice
 
Posts: 17
Joined: Wed Jan 24, 2007 1:26 pm

Re: MySQL Syntax error !

Postby Dreadan » Sat Feb 10, 2007 4:24 pm

Could someone post which string to use to fix the duplicate issue?

I'm using the one that Danysia posted but it's duplicating everything
Dreadan
WR.net Apprentice
WR.net Apprentice
 
Posts: 50
Joined: Tue Jul 04, 2006 7:42 pm

MySQL Syntax error !

Postby dt1375 » Sat Feb 10, 2007 5:43 pm

My lines 2097 and 2098 in /lib/wowdb.php
// skip if entry already there
$querystr = "SELECT `guild` FROM `".ROSTER_PVP2TABLE."` WHERE `index` = '$index' AND `member_id` = '$memberId' AND `name` = '".$this->escape( $playerName )."' AND `date` = '".$this->escape( $playerDate )."'".( !empty($playerRealm) ? " AND `realm` = '".$this->escape( $playerRealm )."'" : '' );
User avatar
dt1375
WR.net Apprentice
WR.net Apprentice
 
Posts: 17
Joined: Wed Jan 24, 2007 1:26 pm

Re: MySQL Syntax error !

Postby Dreadan » Sat Feb 10, 2007 7:15 pm

Thank you it seems to be working better :thumright:
Last edited by Dreadan on Sat Feb 10, 2007 7:16 pm, edited 2 times in total.
Dreadan
WR.net Apprentice
WR.net Apprentice
 
Posts: 50
Joined: Tue Jul 04, 2006 7:42 pm


Return to PvPLog

Who is online

Users browsing this forum: No registered users and 1 guest