Hello Guys,
first of all thx you all for your great effort.

I've installed few minutes ago the addon (ArmorySync) and as soon as I tryed to use it I got the following error message:

****************
Database 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 '(players.dateupdatedutc,'%m/%d/%y %H:%i:%s') <= DATE_SUB(NOW(),
SQL:
SELECT members.name FROM `roster_members` members LEFT JOIN `roster_players` players ON members.name = players.name WHERE members.level >= 10 AND ( ISNULL(players.name) OR STR_TO_DATE(players.dateupdatedutc,'%m/%d/%y %H:%i:%s') <= DATE_SUB(NOW(), INTERVAL 24 HOUR) ) ORDER BY members.last_online DESC
File: index.php
Line: 37

****************

Server Information:
OS Windows NT
Server Software Microsoft-IIS/6.0
MySQL Version 4.0.27-standard-log
PHP Version 4.4.1


Do you have any ideas on what I could do to fix it ?

Thank you in advance,
Checco

Let me dig around to find the answer, and then I'm going to post it in a sticky post because this question comes up quite often. Look for it shortly.

Thx Kristoff,
we will be in touch

This isn't the answer I was looking for, but it should work. This is from Pugro:

To completely force a refresh you could try modifying the query that retrieves the members to update within index.php:

Code: Select all


   $query =
      "SELECT members.name " .
      "FROM `".ROSTER_MEMBERSTABLE."` members " .
      "LEFT JOIN `".ROSTER_PLAYERSTABLE."` players " .
      "ON members.name = players.name " .
      "WHERE members.level >= " . $addon_conf['ArmorySync']['MinLevel'] . " " .
      "AND ( ISNULL(players.name) " .
            "OR STR_TO_DATE(players.dateupdatedutc,'%m/%d/%y %H:%i:%s') <= DATE_SUB(NOW(), INTERVAL " . $addon_conf['ArmorySync']['SynchCutoffTime'] . " HOUR) ) " .
      "ORDER BY " . $addon_conf['ArmorySync']['MemberSorting'];


Try removing the lines:

Code: Select all


      "AND ( ISNULL(players.name) " .
            "OR STR_TO_DATE(players.dateupdatedutc,'%m/%d/%y %H:%i:%s') <= DATE_SUB(NOW(), INTERVAL " . $addon_conf['ArmorySync']['SynchCutoffTime'] . " HOUR) ) " .



and re-running again, that should try and update all characters no matter when the last update was detected.

Hi Kristoff

had the same Error, but your solution works for me.

I tested it and it work so so.
I mean that I have to specify as minimum level an high level like 65-60 in order to exclude timeout operation.
For now it's ok, I hope that it could work better in future expecially for the part that I removed that is so usefull in case of long list of player.

Thx Kirstoff

I am most interested in a fix for this also.

I know a little about SQL. The error is occuring in the STR_TO_DATE function. I have been trying a few different things, and this function doesn't like the format of the date stored in the DB. Not sure if it has something to do with the data-type of the field.

If I figure out anything more, I will let everyone know.

Hi,

I use the newest vision - released today.

Ich have copy it into the addon folder, an edit the config.php to the european location - nothing else.

And I have the same error!

http://www.dasaltebuendnis.de/roster/ad ... rmorySynch

bump.

i have the same error its something to do with mysql4 as my linux server runs 5 and it runs fine on there. any fix yet

this might help

http://www.modwest.com/help/kb6-265.html

The error 1064 occurs due to Mysql version mismatch,because differnt
versions have differnt
reserved words,which if used by a previous version causes the error to
occur.

I've added a configuration parameter to turn off filtering by date updated. I've also added an optional configuration parameter to filter by class. That'll have to do for now, unless someone wants to write a mysql date to php time function, and I can filter that way.

Kristoff22 wrote:I've added a configuration parameter to turn off filtering by date updated. I've also added an optional configuration parameter to filter by class.

Where can I find it?

I need to do a new version upload. Should be available later this week.

I'm getting the same
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 'AND ( ISNULL(players.name) OR STR_TO_DATE(players.dateupdatedutc,'') <= DATE_SUB' at line 1
SQL:
SELECT members.name , ISNULL(players.name) AS playerExists , STR_TO_DATE(players.dateupdatedutc,'') AS playerLastUpdated FROM `roster_members` members LEFT JOIN `roster_players` players ON members.name = players.name WHERE members.level >= AND ( ISNULL(players.name) OR STR_TO_DATE(players.dateupdatedutc,'') <= DATE_SUB(NOW(), INTERVAL HOUR) OR STR_TO_DATE(players.dateupdatedutc,'') >= DATE_ADD(NOW(), INTERVAL 1 YEAR) ) ORDER BY
File: index.php
Line: 42


I looked at the the code snippet above but mine looks different

"SELECT members.name " .
", ISNULL(players.name) AS playerExists " .
", STR_TO_DATE(players.dateupdatedutc,'". $addon_conf['ArmorySync']['DateUpdatedFormat'] ."') AS playerLastUpdated " .
"FROM `".ROSTER_MEMBERSTABLE."` members " .
"LEFT JOIN `".ROSTER_PLAYERSTABLE."` players " .
"ON members.name = players.name " .
"WHERE members.level >= " . $addon_conf['ArmorySync']['MinLevel'] . " " .
"AND ( ".
"ISNULL(players.name) " .
"OR STR_TO_DATE(players.dateupdatedutc,'". $addon_conf['ArmorySync']['DateUpdatedFormat'] ."') <= DATE_SUB(NOW(), INTERVAL " . $addon_conf['ArmorySync']['SynchCutoffTime'] . " HOUR) " .
"OR STR_TO_DATE(players.dateupdatedutc,'". $addon_conf['ArmorySync']['DateUpdatedFormat'] ."') >= DATE_ADD(NOW(), INTERVAL 1 YEAR) " .
") " .
"ORDER BY " . $addon_conf['ArmorySync']['MemberSorting'];

Ok... this one took me a little digging to figure out.

The error I was getting was caused by the mySQL server version I was running. STR_TO_DATE, DATE, DATE_SUB/ADD are only made available as of version 4.1.1

If your server is running anything older these functions will cause an error. I have upgraded to version 5 and everything is fine now.

Hope this helps.

hi, any updates on this . i keep geting this error. My server has MYSQL Ver.5.0.32 and i try to sync with eu armory.

Code: Select all

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 'AND ( ISNULL(players.name) OR STR_TO_DATE(players.dateupdatedutc,'') <= DATE_SUB' at line 1

any help pls?