[misc] The large db purge on September 2016

[misc] The large db purge on September 2016

Happenin' news on what is happening in the community. Content releases, new tutorials, other cool stuff.
Post Reply
abma
Spring Developer
Posts: 3571
Joined: 01 Jun 2009, 00:08

[misc] The large db purge on September 2016

Post by abma »

A long time ago, it feels like yesterday, some cleanup code for the lobby server database was added.

The code will remove accounts which weren't used for more than 3 years.

For old user accounts no last login time was recorded, so it was set for all users without login time to the oldest known login time which was "2013-09-23 22:28:30".

So pretty soon around 60.000 unused user accounts will be deleted.

So a quiz-question: At which exact date will the SQL command delete users with their last login set to "2013-09-23 22:28:30"?

;-)
0 x

raaar
Metal Factions Developer
Posts: 832
Joined: 20 Feb 2010, 12:17

Re: [misc] The large db purge on September 2016

Post by raaar »

60000!

what % of total is that?
0 x

gajop
Moderator
Posts: 3027
Joined: 05 Aug 2009, 20:42

Re: [misc] The large db purge on September 2016

Post by gajop »

If only we required emails on registration so people could be reached in case they still want to keep their account.
1 x

User avatar
jamerlan
Balanced Annihilation Developer
Posts: 683
Joined: 20 Oct 2009, 13:04

Re: [misc] The large db purge on September 2016

Post by jamerlan »

Please don't forget to make a backup :-) And to test that this backup works!
0 x

User avatar
PicassoCT
Journeywar Developer & Mapper
Posts: 10293
Joined: 24 Jan 2006, 21:12

Re: [misc] The large db purge on September 2016

Post by PicassoCT »

is there a exp borderline where we consider a acount to be worth existing on?
1 x

abma
Spring Developer
Posts: 3571
Joined: 01 Jun 2009, 00:08

Re: [misc] The large db purge on September 2016

Post by abma »

PicassoCT wrote:is there a exp borderline where we consider a acount to be worth existing on?
no. after 3 years not playing spring based games all exp is gone. ;-)
0 x

User avatar
PepeAmpere
Posts: 586
Joined: 03 Jun 2010, 01:28

Re: [misc] The large db purge on September 2016

Post by PepeAmpere »

abma, is there is some unique key/index still in the DB for each registered player? I mean e.g. If @pepeampere is deleted and someone will register new account with the same name, do we have some way how to identify that some 1 year old replay was played by @pepeampere no. 1 and current new replays belongs to the new account (possibly new player)?
0 x

abma
Spring Developer
Posts: 3571
Joined: 01 Jun 2009, 00:08

Re: [misc] The large db purge on September 2016

Post by abma »

yes. also players can rename, so there should be nothing where the name is used expect in lobbies / for humans to be readable :-)
0 x

User avatar
FabriceFABS
Posts: 348
Joined: 28 Jul 2010, 16:20

Re: [misc] The large db purge on September 2016

Post by FabriceFABS »

I was really wondering about this DB growing issue : We see often some new accounts, but the number of spring players seems the same.
It's a very good initiative, even if we don't get any perf gain.
And to be curious, you think it will bring trivial gain ? What is the DB filesize actually ?
0 x

abma
Spring Developer
Posts: 3571
Joined: 01 Jun 2009, 00:08

Re: [misc] The large db purge on September 2016

Post by abma »

FabriceFABS wrote:And to be curious, you think it will bring trivial gain ? What is the DB filesize actually ?
db is small. the gains are IMHO:

- privacy!
- less conflicts with existing player names for new players
- faster / smaller backup
- most users will have a correct registration date

basicly it simplifies maintainance: i.e. copying the db to the development server will take zero time.
0 x

User avatar
FabriceFABS
Posts: 348
Joined: 28 Jul 2010, 16:20

Re: [misc] The large db purge on September 2016

Post by FabriceFABS »

OK :-)
0 x

abma
Spring Developer
Posts: 3571
Joined: 01 Jun 2009, 00:08

Re: [misc] The large db purge on September 2016

Post by abma »

abma wrote:So a quiz-question: At which exact date will the SQL command delete users with their last login set to "2013-09-23 22:28:30"?

Code: Select all

select DATE_ADD('2013-09-23 22:28:30', INTERVAL 1095 DAY);
+----------------------------------------------------+
| DATE_ADD('2013-09-23 22:28:30', INTERVAL 1095 DAY) |
+----------------------------------------------------+
| 2016-09-22 22:28:30                                |
+----------------------------------------------------+
1 row in set (0.00 sec)

so after "2016-09-22 22:28:30" a lot of accounts will be deleted. As nobody tried to answer i get the beer! :-)
0 x

gajop
Moderator
Posts: 3027
Joined: 05 Aug 2009, 20:42

Re: [misc] The large db purge on September 2016

Post by gajop »

Delay the consumption of that beer until after the purge :mrgreen:
1 x

User avatar
PicassoCT
Journeywar Developer & Mapper
Posts: 10293
Joined: 24 Jan 2006, 21:12

Re: [misc] The large db purge on September 2016

Post by PicassoCT »

I lost everything during the purge, my name, my exp, my admin rights, my virtual items and pets. Even my dignity is gone.

I want full recompensation, else there be suits in law.
0 x

abma
Spring Developer
Posts: 3571
Joined: 01 Jun 2009, 00:08

Re: [misc] The large db purge on September 2016

Post by abma »

ok, a bit late but done:

before purge: ~117.000
after purge: ~45.000
0 x

User avatar
ThinkSome
Posts: 306
Joined: 14 Jun 2015, 13:36

Re: [misc] The large db purge on September 2016

Post by ThinkSome »

gajop wrote:If only we required emails on registration so people could be reached in case they still want to keep their account.

An invite system had crossed my mind before. It would create a sense of exclusivity among players, possibly increasing their chance of staying. And everyone new would have someone old show them "how it is done" wrt. lobbies and stuff.
0 x

Post Reply

Return to “Community Blog”