database maintenance

[ojota]

gave me great joy to see the updated IdFactory.java. thank you very much.

[janiii]

..and i was bitched up that i commited it..

[toastgodsupreme]

..and i was bitched up that i commited it..

Why?

[MELERIX]

cos DS say that in a db ~100-150mb cleanup become ~10min...

but for me this contribution is fine, and janiii are not doing nothing bad.

[toastgodsupreme]

cos DS say that in a db ~100-150mb cleanup become ~10min...

but for me this contribution is fine, and janiii are not doing nothing bad.

Lol, it's not like it adds THAT much more to the routines. Just a few missing ones. Besides, the first time they're cleaned out will take the longest. After that any future cleanups will be nearly instant. Better to have a clean database and wait a few more minutes on ONE restart than have a dirty DB.

And if the devs were REALLY concerned about it, they'd remove ALL those checks from idfactory and simply include a cleanup script that admins can run whenever they wanted. But since they decided to hardcode it initially, it HAS to be maintained and kept up to date with the database changes, otherwise it's pointless.

[MELERIX]

Yep I agree.

Btw, Janiii don't worry you're not doing nothing bad in core-side.

[_DS_]

LOL. janiii was too emotional of calling this bitching:
<__DS__> janiii, you know why i am not commit this idfactory contrib ?
<janiii> why? you didnt write the reason on forum, or?
<__DS__> you tryed it on base ~100-150mb ?
<janiii> i cant read minds
<__DS__> on my server cleanup become ~10min
<janiii> why you dont write it then on forum why it doesnt get commited or what has to be changed on it?
<__DS__> try yourself...

It was all "bitching".
Also. "Besides, the first time they're cleaned out will take the longest. After that any future cleanups will be nearly instant." is a false. Why ? (SELECT object_id FROM items) now done twice.
Clan notices cleanup done twice.
And last, but not least:
stmt.executeUpdate("UPDATE characters SET clanid=0, clan_privs=0, wantspeace=0, subpledge=0, lvl_joined_academy=0, apprentice=0, sponsor=0, clan_join_expiry_time=0, clan_create_expiry_time=0 WHERE characters.clanid NOT IN (SELECT clan_id FROM clan_data);");
Question: is clan id 0 in the clan_data ?

Conclusion: you see yourself.

[toastgodsupreme]

When you mentioned in the old forums, how checking for accounts would be an issue for servers that had multiple game servers on one login server, I resolved that with config options.

You didn't like that though because you feared that it may cause problems for dumb admins. So I went a step further and left it out of config options to only be enabled if a dev actually went into the code and uncommented it.

I'm not sitting around ignoring advice. I take it when I see that it's appropriate. But that means you guys need to fill the role of giving it when needed. Feedback, etc. VERY needed so we can get things to an agreeable stage to get committed. At this point, I'm just about done with contributing anything more to this project because of that exact reason. And so are a lot of people.

(SELECT object_id FROM items) now done twice.

Maybe if you had told me initially when we had the old forum thread going OR when i reposted it to the new forums. BUT YOU DIDN'T SAY A THING. I could've optimized that had you mentioned it.

Clan notices cleanup done twice.

Again, you didn't say anything, no one did. And whoops, ONE mistake. Because you guys don't make mistakes and need to make multiple commits to get something right? C'mon... you're not perfect so don't expect me to be.

And last, but not least:
stmt.executeUpdate("UPDATE characters SET clanid=0, clan_privs=0, wantspeace=0, subpledge=0, lvl_joined_academy=0, apprentice=0, sponsor=0, clan_join_expiry_time=0, clan_create_expiry_time=0 WHERE characters.clanid NOT IN (SELECT clan_id FROM clan_data);");
Question: is clan id 0 in the clan_data ?

Conclusion: you see yourself.

Old code:

Code: Select all

stmt.executeUpdate("UPDATE characters SET clanid=0 WHERE characters.clanid NOT IN (SELECT clan_id FROM clan_data);");

Conclusion: It was fucked before I touched it, I just made sure other things got fixed aside from just the clanid. Don't fucking point fingers at me when I didn't mess it up, I merely expanded on what was already there.


So now let's make PROGRESS... instead of bitching about it, how about you or another dev fixes it?

Code: Select all

Index: D:/Documents/L2J/L2_GameServer/java/net/sf/l2j/gameserver/idfactory/IdFactory.java===================================================================--- D:/Documents/L2J/L2_GameServer/java/net/sf/l2j/gameserver/idfactory/IdFactory.java  (revision 3244)+++ D:/Documents/L2J/L2_GameServer/java/net/sf/l2j/gameserver/idfactory/IdFactory.java  (working copy)@@ -202,7 +202,6 @@            cleanCount += stmt.executeUpdate("DELETE FROM clanhall_functions WHERE clanhall_functions.hall_id NOT IN (SELECT id FROM clanhall WHERE ownerId <> 0);");            cleanCount += stmt.executeUpdate("DELETE FROM siege_clans WHERE siege_clans.clan_id NOT IN (SELECT clan_id FROM clan_data);");            cleanCount += stmt.executeUpdate("DELETE FROM clan_notices WHERE clan_notices.clan_id NOT IN (SELECT clan_id FROM clan_data);");-           cleanCount += stmt.executeUpdate("DELETE FROM clan_notices WHERE clan_notices.clan_id NOT IN (SELECT clan_id FROM clan_data);");            cleanCount += stmt.executeUpdate("DELETE FROM auction_bid WHERE auction_bid.bidderId NOT IN (SELECT clan_id FROM clan_data);");            //Untested, leaving commented out until confirmation that it's safe/works properly. Was initially removed because of a bug. Search for idfactory.java changes in the trac for further info.            //cleanCount += stmt.executeUpdate("DELETE FROM auction WHERE auction.id IN (SELECT id FROM clanhall WHERE ownerId <> 0) AND auction.sellerId=0;");@@ -217,7 +216,7 @@            stmt.executeUpdate("UPDATE clan_data SET auction_bid_at = 0 WHERE auction_bid_at NOT IN (SELECT auctionId FROM auction_bid);");            stmt.executeUpdate("UPDATE clan_subpledges SET leader_id=0 WHERE clan_subpledges.leader_id NOT IN (SELECT charId FROM characters) AND leader_id > 0;");            stmt.executeUpdate("UPDATE castle SET taxpercent=0 WHERE castle.id NOT IN (SELECT hasCastle FROM clan_data);");-           stmt.executeUpdate("UPDATE characters SET clanid=0, clan_privs=0, wantspeace=0, subpledge=0, lvl_joined_academy=0, apprentice=0, sponsor=0, clan_join_expiry_time=0, clan_create_expiry_time=0 WHERE characters.clanid NOT IN (SELECT clan_id FROM clan_data);");+           stmt.executeUpdate("UPDATE characters SET clanid=0, clan_privs=0, wantspeace=0, subpledge=0, lvl_joined_academy=0, apprentice=0, sponsor=0, clan_join_expiry_time=0, clan_create_expiry_time=0 WHERE characters.clanid > 0 AND characters.clanid NOT IN (SELECT clan_id FROM clan_data);");            stmt.executeUpdate("UPDATE clanhall SET ownerId=0, paidUntil=0, paid=0 WHERE clanhall.ownerId NOT IN (SELECT clan_id FROM clan_data);");                        stmt.close(); 

That took less than 5 minutes for me so imagine how fast you could've done it. Probably in less time than it took you to copy/paste janiii's convo with you and for you to disect it.

Like I said earlier... IF YOU DON'T LIKE THE FACT THAT ON YOUR MASSIVE DATABASE IT TAKES FOREVER TO RUN THESE CHECKS, MAYBE YOU SHOULD CONSIDER REMOVING IT FROM BEING HARDCODED AND INCLUDE IT AS A SCRIPT FOR ADMINS TO RUN.

And I'm not saying spend 6 months talking about it in the inner circle and in that time do nothing at all to improve it until a decision is made. That's just an assbackwards way of doing things. Fix it first, include proper checks and cleanups, and then spend your 6 months discussing it.

[_DS_]

[quote="toastgodsupreme"Old code:

Code: Select all

stmt.executeUpdate("UPDATE characters SET clanid=0 WHERE characters.clanid NOT IN (SELECT clan_id FROM clan_data);");

Conclusion: It was fucked before I touched it, I just made sure other things got fixed aside from just the clanid. Don't fucking point fingers at me when I didn't mess it up, I merely expanded on what was already there.
[/quote]
SET clanid=0 does not broke expiry times and does not allow player immediately join clan after restart. I think what after some thinking another underwater stones can be found.

[toastgodsupreme]

[quote="toastgodsupreme"Old code:

Code: Select all

stmt.executeUpdate("UPDATE characters SET clanid=0 WHERE characters.clanid NOT IN (SELECT clan_id FROM clan_data);");

Conclusion: It was fucked before I touched it, I just made sure other things got fixed aside from just the clanid. Don't fucking point fingers at me when I didn't mess it up, I merely expanded on what was already there.

SET clanid=0 does not broke expiry times and does not allow player immediately join clan after restart. I think what after some thinking another underwater stones can be found. [/quote]

I LOVE how you jump from one reason to the next to hate on it.

Wow. First it was because clanid 0 didn't exist in clan data. Now it's because my original code reset expiry timers, oh but wait, I believe the solution i posted resolves that matter to! LORDY LORDY two fixes in one!!!

So now, it only affects people where they have a clan ID set, but the clan doesn't exist. And if they have an id of 0 already, then it doesn't affect them at all. Sounds good to me. And like I said, it took me all of a few seconds to fix it. Why are you still posting in this thread instead of fixing it? It takes you longer to hit the refresh key over and over and over waiting for a reply, then replying, than it would for you to fix it.

I don't understand that. Not one bit. You have the power, you have the tools, you know WHAT to fix, you know how to fix it (i assume, since I do, then you must)... yet... you post.

I post because I can not commit. If I could, it'd be resolved, this thread would be locked, and I'd be working on committing other things, like the siege update to get rid of silly strings and duplicate announcements. But we post. Post post post. And we spend 6 months talking about or ignoring it. Either way, nothing gets fixed.

[_DS_]

I LOVE how you jump from one reason to the next to hate on it.

Wow. First it was because clanid 0 didn't exist in clan data. Now it's because my original code reset expiry timers, oh but wait, I believe the solution i posted resolves that matter to! LORDY LORDY two fixes in one!!!

Original code reset expiry timers because clanid 0 does not exist in the clan_data, so query affect all clanless people.

Last question: why i am found this issue after 2 hours from installing on live, and no one, including author of the contrib, does not found and fix this bug before ? Answer: because no one bother to test it.

And please does not write so huge pointless messages, its a waste of the time - i does not read them.

[janiii]

Last question: why i am found this issue after 2 hours from installing on live, and no one, including author of the contrib, does not found and fix this bug before ? Answer: because no one bother to test it.

so why YOU didn't report YOUR test results?

[_DS_]

Because i got them only today's morning after update to current ? BTW, i report them here.

[ojota]

L2J Revision : 3347
L2JDP Revision 6455

sorry, there is some novelty of the test. thank you very much