[misc] The large db purge on September 2016

[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"?

[raaar]

60000!

what % of total is that?

[gajop]

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

[jamerlan]

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

[PicassoCT]

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

[abma]

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.

[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)?

[abma]

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

[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 ?

[abma]

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.

[FabriceFABS]

OK

[abma]

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!

[gajop]

Delay the consumption of that beer until after the purge

[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.

[abma]

ok, a bit late but done:

before purge: ~117.000
after purge: ~45.000

[ThinkSome]

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.