[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: 3555
Joined: 01 Jun 2009, 00:08

[misc] The large db purge on September 2016

Post by abma » 21 Jun 2016, 22:46

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: 816
Joined: 20 Feb 2010, 12:17

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

Post by raaar » 22 Jun 2016, 05:30

60000!

what % of total is that?
0 x

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

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

Post by gajop » 22 Jun 2016, 06:11

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 » 22 Jun 2016, 10:37

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

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

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

Post by PicassoCT » 22 Jun 2016, 15:33

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

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

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

Post by abma » 22 Jun 2016, 16:21

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 » 29 Jun 2016, 16:47

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: 3555
Joined: 01 Jun 2009, 00:08

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

Post by abma » 29 Jun 2016, 17:18

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: 345
Joined: 28 Jul 2010, 16:20

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

Post by FabriceFABS » 10 Jul 2016, 08:48

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: 3555
Joined: 01 Jun 2009, 00:08

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

Post by abma » 23 Jul 2016, 22:14

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: 345
Joined: 28 Jul 2010, 16:20

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

Post by FabriceFABS » 24 Jul 2016, 16:51

OK :-)
0 x

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

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

Post by abma » 28 Jul 2016, 17:52

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: 3023
Joined: 05 Aug 2009, 20:42

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

Post by gajop » 28 Jul 2016, 18:14

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

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

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

Post by PicassoCT » 29 Jul 2016, 00:48

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: 3555
Joined: 01 Jun 2009, 00:08

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

Post by abma » 09 Oct 2016, 01:11

ok, a bit late but done:

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

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

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

Post by ThinkSome » 09 Oct 2016, 16:41

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”