Page 1 of 1

[misc] The large db purge on September 2016

Posted: 21 Jun 2016, 22:46
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"?

;-)

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

Posted: 22 Jun 2016, 05:30
by raaar
60000!

what % of total is that?

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

Posted: 22 Jun 2016, 06:11
by gajop
If only we required emails on registration so people could be reached in case they still want to keep their account.

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

Posted: 22 Jun 2016, 10:37
by jamerlan
Please don't forget to make a backup :-) And to test that this backup works!

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

Posted: 22 Jun 2016, 15:33
by PicassoCT
is there a exp borderline where we consider a acount to be worth existing on?

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

Posted: 22 Jun 2016, 16:21
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. ;-)

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

Posted: 29 Jun 2016, 16:47
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)?

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

Posted: 29 Jun 2016, 17:18
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 :-)

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

Posted: 10 Jul 2016, 08:48
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 ?

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

Posted: 23 Jul 2016, 22:14
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.

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

Posted: 24 Jul 2016, 16:51
by FabriceFABS
OK :-)

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

Posted: 28 Jul 2016, 17:52
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! :-)

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

Posted: 28 Jul 2016, 18:14
by gajop
Delay the consumption of that beer until after the purge :mrgreen:

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

Posted: 29 Jul 2016, 00:48
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.

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

Posted: 09 Oct 2016, 01:11
by abma
ok, a bit late but done:

before purge: ~117.000
after purge: ~45.000

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

Posted: 09 Oct 2016, 16:41
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.