[misc] The large db purge on September 2016
[misc] The large db purge on September 2016
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"?
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
60000!
what % of total is that?
what % of total is that?
Re: [misc] The large db purge on September 2016
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
Please don't forget to make a backup :-) And to test that this backup works!
Re: [misc] The large db purge on September 2016
is there a exp borderline where we consider a acount to be worth existing on?
Re: [misc] The large db purge on September 2016
no. after 3 years not playing spring based games all exp is gone.PicassoCT wrote:is there a exp borderline where we consider a acount to be worth existing on?
- PepeAmpere
- Posts: 589
- Joined: 03 Jun 2010, 01:28
Re: [misc] The large db purge on September 2016
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
yes. also players can rename, so there should be nothing where the name is used expect in lobbies / for humans to be readable
- FabriceFABS
- Posts: 354
- Joined: 28 Jul 2010, 16:20
Re: [misc] The large db purge on September 2016
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 ?
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
db is small. the gains are IMHO:FabriceFABS wrote:And to be curious, you think it will bring trivial gain ? What is the DB filesize actually ?
- 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
- Posts: 354
- Joined: 28 Jul 2010, 16:20
Re: [misc] The large db purge on September 2016
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
Delay the consumption of that beer until after the purge
Re: [misc] The large db purge on September 2016
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.
I want full recompensation, else there be suits in law.
Re: [misc] The large db purge on September 2016
ok, a bit late but done:
before purge: ~117.000
after purge: ~45.000
before purge: ~117.000
after purge: ~45.000
Re: [misc] The large db purge on September 2016
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.