Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 5 post(s) |
Natasha Starlight
|
Posted - 2007.10.17 01:25:00 -
[1]
Has something happened to the refIDs returned by the Wallet Journal API in today's patch? For me at least they seem to be around 470,000,000 where pre-patch they were around 1,550,000,000. As far as I can make out it isn't just that post-patch wallet journal transactions have a "new-style" refID, it seems transactions from a day or two ago (pre-patch) have been "re-assigned" a new refID too! Which is causing havoc with my database (duplicated data, refID is the key.)
Devs, is this an intentional change? Am I alone in seeing this? Happy to provide any assistance you may need.
|
TheNecromancer
Caldari The Royal Order
|
Posted - 2007.10.17 04:43:00 -
[2]
nice
I aint the only one who have notice problems with the journal api
hope some dev reply to this post
|
Salvis Tallan
Gallente The Shadow Order SMASH Alliance
|
Posted - 2007.10.17 05:16:00 -
[3]
I also noticed this issue, and it is quite a large one. Im willing to bet all API projects that us this data depend on the RefID being constant and unique... and now suddenly the numbers have changed ------
|
Ambo
2nd Outcasters
|
Posted - 2007.10.17 07:43:00 -
[4]
Yeah, I have the same problem.
|
TheNecromancer
Caldari The Royal Order
|
Posted - 2007.10.17 13:42:00 -
[5]
bumb bump any reply ..plzzzzzzzz
is this an error..or what ????
:(
|
ronwestnz
The Three Brothers Trading Company
|
Posted - 2007.10.17 17:10:00 -
[6]
Edited by: ronwestnz on 17/10/2007 17:10:33 Looks like the data tables where re-indexed when they moved the to the new system.
I had to dump my transaction and journal tables and repopulate them with the new data. i only have 500 journal entry's thou, hate to think what the big corps would have.
|
|
CCP Garthagk
|
Posted - 2007.10.17 23:42:00 -
[7]
This is the first I've heard of this. I will investigate and try to figure out what happened.
-------------- Garthagk - EVE Software Group - EVE API Guy
To err is human to really ***** up takes admins. -Hellmar |
|
Ambo
2nd Outcasters
|
Posted - 2007.10.18 07:12:00 -
[8]
crap, I've got about 20,000 odd entries in my database from the last several months.
Do you have any advice on how to recalculate all the IDs to be compatible with the new numbering?
Thanks.
|
Natasha Starlight
|
Posted - 2007.10.18 09:03:00 -
[9]
Quote: Edit: Operations says that yes, this happened. I'm trying to get more information about whether or not this was a one-off occurrence or whether we can expect this kind of thing to happen from time to time. I'll update again when I know more...
Thanks for the response. I'll set about updating my database. As far as I can see "old_refID - 1085796677 = new_refID". Do you know if this formula always holds? If not I guess I'll just leave them alone.
If this is likely to occur from time to time is it something we could get advance warning on? And perhaps a conversion formula after the fact?
|
|
CCP Garthagk
|
Posted - 2007.10.18 09:25:00 -
[10]
I'm told that this happens every few months for various reasons when the table is cleaned up to make room for new data. You can't rely on the refIDs being immutable.
I'm trying to find out if the order is guaranteed, i.e. if a flat subtraction can map old to new. Will update when I know.
-------------- Garthagk - EVE Software Group - EVE API Guy
To err is human to really ***** up takes admins. -Hellmar |
|
|
|
CCP Garthagk
|
Posted - 2007.10.18 09:42:00 -
[11]
CCP Valar says: "The magic number is 1085796677. Just subtract that."
Thanks to the Operations team for helping us out here! Now we can go fix our databases and carry on with our lives.
-------------- Garthagk - EVE Software Group - EVE API Guy
To err is human to really ***** up takes admins. -Hellmar |
|
Natasha Starlight
|
Posted - 2007.10.18 09:51:00 -
[12]
Originally by: CCP Garthagk CCP Valar says: "The magic number is 1085796677. Just subtract that."
Thanks to the Operations team for helping us out here! Now we can go fix our databases and carry on with our lives.
Cheers Garthagk.
|
Ambo
2nd Outcasters
|
Posted - 2007.10.18 11:11:00 -
[13]
Awesome, thankyou!
|
Thirler
Minmatar The Arrow Project Morsus Mihi
|
Posted - 2007.10.20 10:58:00 -
[14]
Has anyone come up with a solid strategy to solve this yet?
I'm thinking of just counting ahead (adding the magic number several times). Arrow Capital Ship Sale |
|
CCP Garthagk
|
Posted - 2007.10.22 12:18:00 -
[15]
The magic number is the number from this particular incident - next time the number will be something different. It's not static.
Anyway, for a long term solution you could look into doing something like having generations of data, i.e., right now we're in generation 2, that last batch (last 6 months) was generation 1... then have your own primary key.
For example, a journal storage table could be:
CREATE TABLE journal_entries ( localID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ccpGeneration SMALLINT UNSIGNED NOT NULL, ccpRefID INT UNSIGNED NOT NULL, ownerID INT UNSIGNED NOT NULL, /* char OR corp ID */ ...other...
UNIQUE ccpID (ccpGeneration, ccpRefID) )
Pseudo-MySQL, but you get the idea. You would have to keep track of what generation you're in currently, and then when storing the data do a check against the refID and see if they have suddenly changed. For example, here's some pseudo-code for downloading someone's journal:
// setup our variables, this is what we think the current universe is! $currentGeneration = 1; $characterID = 2723742347;
// now get the highest refID we have on file for this user! // and any pedants who want to gripe about not using bound parameters can bite me, // this is pseudo-code only! $latestReferenceID = LocalDatabaseRunSQL( SELECT MAX(ccpRefID) FROM journal_entries WHERE ownerID = $characterID AND ccpGeneration = $currentGeneration );
// now hit the API for this character's journal page, we're assuming // here that there are no errors... really you should always be checking // for errors :-) $newData = API.GetJournalForCharacter( $characterID );
// iterate over each row, find the highest ID in this dump $newMaxRefID = 0; for each $row in $newData { if ( $row->refID > $newMaxRefID ) $newMaxRefID = $row->refID; }
// now, if the highest ID we found is below the highest ID we've already // got, then we can safely assume that the generation has changed. this is // because we should never get back less data than we already have! if ( $newMaxRefID < $latestReferenceID ) { // ... } else { // just store the row in the database, everything's cool }
Long-winded, but you get the idea. You would store our reference IDs only as a way of ensuring no duplicate transactions, but use your own generated IDs internally for your database.
You would then store a table of (ccpGeneration, refIdDecrementValue) so you can properly map between two generations. In the case that the generation has changed, you need to get the new difference ID (which you can calculate manually by comparing two transactions or ask around for). With the new ID, you can compare if a transaction has already been seen by looking at the previous generation - decrementor and seeing if it matches the one you've gotten.
At any rate, this is a suggestion, there might be easier ways of doing this - in fact, I'm sure there probably is? This seems a little longwinded and troublesome. I'd love to hear other ways people are addressing this problem.
-------------- Garthagk - EVE Software Group - EVE API Guy
To err is human to really ***** up takes admins. -Hellmar |
|
Ix Forres
Vanguard Frontiers INVICTUS.
|
Posted - 2007.10.22 13:09:00 -
[16]
Originally by: CCP Garthagk The magic number is the number from this particular incident - next time the number will be something different. It's not static.
-- snip --
Would a more logical approach be to just add the magic number onto the RefID at the API end of things, thus keeping the RefID static?
ISKsense | Blog |
Thirler
Minmatar The Arrow Project Morsus Mihi
|
Posted - 2007.10.22 13:22:00 -
[17]
I've postponed my personal deadline by just adding the magic number to the ids from the eveapi to get the ids I use. This is a really easy solution but it will run into problems when the integer max is reached. I used mysql + php. In the case of mysql that is easy to fix. But php and bigint don't provide a great solution. If anyone wants to get into bigint with php, read the following pages: http://www.php.net/manual/en/language.types.integer.php http://www.php.net/manual/en/ref.gmp.php (phps version of bigint)
I'm still thinking on the proper solutions. Yours was definitely something I hadn't thought of.
My other option so far is: Totally leave out the ids, this means everything goes by timestamp. This surely is an ugly solution, because we still need to be able to decide which entries are duplicates. Especially as the resolution of the timestamps is 1 minute. The key to why this could work without many entries falsely deleted is the account balance field. Killing the same type of rat within one minute happens often, but the entries will have a different balance field. To narrow down the chance of false duplicates even more you could also compare the known IDs (as returned by eve when the transaction was first seen) and having slightly different IDs would mean they are not duplicates, and large difference of IDs(but same timestamp) would mean they could be duplicates.
This wouldn't solve the same problem for transactions, as they don't have a balance field. However transactions shouldn't wrap around more than once every few years (my calculations show 35m entries in 50 days, meaning they'll reach 1.5b in 4-5 years if they keep going at this pace). Arrow Capital Ship Sale |
|
CCP Garthagk
|
Posted - 2007.10.22 13:53:00 -
[18]
Originally by: Ix Forres Would a more logical approach be to just add the magic number onto the RefID at the API end of things, thus keeping the RefID static?
Nah, I don't know when the generation changes. The API is stateless (generally speaking) so it wouldn't know when things change out from under it.
And relying on me (or someone) knowing when this happens, updating the API before it spits out any new requests with bad data, is a chain so weak it's not woth relying on. I'd rather see support for this implemented in data archival.
-------------- Garthagk - EVE Software Group - EVE API Guy
To err is human to really ***** up takes admins. -Hellmar |
|
Druadan
Gallente Aristotle Enterprises Ethereal Dawn
|
Posted - 2007.10.22 18:03:00 -
[19]
How about a new API function, or, more ideally, an overload of an existing function, that retrieves only the data time with the largest refID for the set of data asked for. That way the individual developer doesn't need to iterate over the set to determine the largest refID.
That's the only meaningful optimisation I can think of, but I'm yet to work with the API, so I'm throwing that out there as a Suggestion From Beyond. Hope I haven't misinterpreted the scope.
### I nearly finish carriers, and they nerf it. I nearly finish Amarr recons, and they make them useless. Vagabond pilots beware... I have bought Minmatar Cruiser. |
Conscious
Gallente Haru Chai
|
Posted - 2007.10.24 02:28:00 -
[20]
Well as someone who was depending on a unique refid this makes me rather grumpy. I'm not seeing how their internal logs, which I assume the GMs need, work well without some unique identifiers on records. Are records deleted based on date?...deleted characters? The best solution I've seen here has to do with using the timestamp as an ID. Though it seems it would still be possible to loose some records, as someone pointed out, that only resolves to the minute. If someone has a large number of items happening, like salvage buy orders, it could be very easy to lose data. I assume this same problem exists with transactions?
|
|
Shinhan
Phoenix Knights Dark Nebula Galactic Empire
|
Posted - 2007.10.24 06:55:00 -
[21]
Edited by: Shinhan on 24/10/2007 07:05:35 How about sort first by timestamp and then by refID?
Maybe even make a compound primary key timestamp+refID...
-- Selling apples, 1 signature each. ѼѼѼѼѼѼѼ |
Thirler
Minmatar The Arrow Project Morsus Mihi
|
Posted - 2007.10.24 11:24:00 -
[22]
The fact that the ids aren't unique isn't a real problem (more on that below)
The real problem is that the same event can have a different ID at a later time. Problems with that arise when adding new events: is this event already in the database? Also if transactions gets reset it means the link from journal (argID1) to transactions is not entirely solid. Solution to this is to run transactions and journal at the roughly the same time, and then join on both time and id. As the change will only happen at downtime, just don't run journal before downtime and transactions after. The solution Garthagk proposed to this is more robust but requires some manual intervention.
As the order is not too important in almost all applications, just making the time primary order and key secondary will give sufficient ordering.
As some people have put up suggestions for a primary. This is something really important to notice (perhaps a good thing to put in the next version of the documentation): There is no primary key in this data! (Possibly the whole row is unique though, not sure about that.) Not even when you ignore the reset of the IDs. A transaction can be from one corp wallet to another corp wallet. Giving entries with the same id, same time but a different account id (the best way is to store all wallets info in the same table with just an added accountid) Even when adding the the accountid to the primary key, that can still go wrong: If you pay the rent of a manufacturing slot in your own station with your master wallet. The money will be payed to and taken from the master wallet, at the same time, with the same refid. Arrow Capital Ship Sale |
Eswidrol
|
Posted - 2007.10.26 22:22:00 -
[23]
I no longer use primary key since I saw that by sending money from one corp wallet to another I would get the same row in both account. Same for transactions. I changed the primary to index and, based on my need, had no impact. Also I find it usefull to add the accountkey in the insert so I can do report per wallet...
The only thing I want to avoid is to import already inserted rows when operations will do their cleanup. So I can start from Garthagk idea to detect the change and if it's the case I check more columns in the row to decide if I import. The next time I call the API I will have the new type of MaxRefID.
About linking transaction to journal, I think the best way would be to use your own primary key as the foreign key and you create the link by comparing the date, the accountKey and the amount. This leave only to some rare cases (same date, account and amount) that can be prevented : If you detect >1 then do the link by matching the lower transactionID to the lower journalID. (or higher vs higher depending how you see the life )
|
DarkArrow
Minmatar Republic Military School
|
Posted - 2007.10.27 11:18:00 -
[24]
|
Dr Slurm
General Commodities
|
Posted - 2007.10.27 13:53:00 -
[25]
Originally by: Eswidrol
About linking transaction to journal, I think the best way would be to use your own primary key as the foreign key and you create the link by comparing the date, the accountKey and the amount. This leave only to some rare cases (same date, account and amount) that can be prevented : If you detect >1 then do the link by matching the lower transactionID to the lower journalID. (or higher vs higher depending how you see the life )
Wouldn't it make more sense to just properly order the results of your query so they all come out in order, both transaction and journal results? <sig>
Tired of the inane ramblings of the incompetent? Click here </sig> |
Thirler
Minmatar The Arrow Project Morsus Mihi
|
Posted - 2007.10.29 14:42:00 -
[26]
The order does make sense but I think (need to double check) that per request the order isn't guaranteed within the xml, also your xml parser might not have a fixed ordering.
Also it only applies to certain jounral entries. And for a database the ordering isn't a valid linking mechanism. Arrow Capital Ship Sale |
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |