separate sqlwrite and sqlread server #378

H-M-H · 2015-11-22T00:27:51Z

So, the idea is to separate the sql-servers, so you can specify different servers to read from and to write to. For example you could write to a distant masterserver and read from localhost (should replicate from master). Like this it is possible to have a few core/master-sql-servers and a whole replication-ring is no longer necessary.
Additionally this Pull Requests adds the possibility to specify more than one sql-server which makes it possible to fall back to another server if the first specified is unreachable.

In order to make all this stuff possible I restructured the sql-connection-stuff, we now have:

CSqlServer: This class provides access to one sql-server, connect and disconnect automatically lock the object to avoid several threads accessing it simultaneously.

CSqlConnector: This class is used to connect a sql-server, it will iterate over the list of given sql-servers and try to connect to one of them.

CSqlScore: This class now only contains things relevant to actually inserting records into db or querying some stuff. The sql-connection stuff has been moved to the above classes.

CSqlData: This struct contains the information relevant for the sql-query/statement.

CSqlExecData: This struct is used to call a function that does some sql-stuff within its own thread. It also supplies objects of CSqlData and CSqlServer to the called function.

H-M-H · 2015-11-22T19:49:22Z

@MyNewBie well it should work, did not test it though.

MyNewBie · 2015-11-23T07:24:20Z

@H-M-H sql_server compiled done but get crash after running , cant connect to MySQL database.

H-M-H · 2015-11-23T13:56:57Z

This is very likely a problem with the sql library, the same thing happened on my mac. To fix it you could try:

compile sql libs on your own
check the compiler flags and additionally libs used for compiling sql libs and try to make them the same (on my mac for instance sql libs were compiled with -stdlib=libc++ but ddnet with -stdlib=libstdc++)

H-M-H · 2015-11-23T22:00:11Z

I guess I am done now, ofcourse this still needs extensive tests and possibly a review :).

def- · 2015-12-08T00:09:52Z

Any plan when this will be ready? Would be nice for the new Chile server, since it doesn't have enough RAM to run its own database. Would also like to get rid of a few other database servers, RUS and CHN especially. If we also remove South Africa we keep these servers as database servers: ddnet.tw, GER, USA, CAN. That would be good enough I guess.

Edit: I still believe this should use the same servers for writing and reading, just fallback ones when the main one goes down. The 100 ms delay for getting /rank should be survivable.

def- · 2015-12-15T00:32:22Z

Any plan when this is ready? We're currently failing to store many ranks on Chile and Brazil because of DDoS attacks and other network problems.

H-M-H · 2015-12-15T08:39:27Z

It is nearly done, I will try to finish it this week.

def- · 2016-01-12T18:08:00Z

Any plan when this is ready? We're currently failing to store many ranks on Chile and Brazil because of DDoS attacks and other network problems.

Still happens all the time, not sure why people even still play on these servers as their ranks are mostly not saved at all.

-moved all connectionstuff to a new class -moved sqlstring functions to an own file -do not give threads access to CSqlScore

custom configs

+ added #ifdef for sql_server.cpp (fixes compilation for release mode)

TODO: move the sqlserver objects somewhere else so they wont be destroyed on every reload

as sqlconnections should not last only until next mapreload

each sqlserver has its own lock now -> it is required that every connect call is followed by a disconnect call

reading from and writing to several different servers is now possible TODO: -handle Exceptions properly (try another sqlserver) -if everything fails while writing write the insert to a file

- write failed sqlinserts to a file - improved structure

H-M-H · 2016-05-03T22:01:03Z

Okay, this fixes problems on mapchange/reload. New ranks will be saved even if the map is changed during the insert. Additionally some other unwanted behaviour is fixed (threads created before a mapchange notice the change and act accordingly now).

def- · 2016-05-03T22:17:07Z

src/engine/server/server.h

+
+#if defined (CONF_SQL)
+	CSqlServer* m_apSqlReadServers[MAX_SQLSERVERS];
+	CSqlServer* m_apSqlWriteServers[MAX_SQLSERVERS];;


double semicolon

Good point, but there are more. Will fix this.

def- · 2016-05-03T22:43:12Z

What you're doing with SaveScoreThread and SaveTeamScoreThread looks dangerous. When you keep %s characters in code to use them later, it's possible that user-strings could contain them as well, no?

H-M-H · 2016-05-03T22:51:56Z

Right, i already thought about putting 'record' back instead of '%s', but this assumes that every server is configured with 'record' as prefix. '%s' felt better but indeed not perfect. Any ideas to improve that ?

This commit introduced that btw: 8dacd88 .

def- · 2016-05-04T20:52:10Z

This is being tested by HMH on Chile. He can merge it himself when he feels it's ready for general use.

CSqlData is const for threadfunctions now to avoid modification from within the threadfunctions as these might be called several times. Previously this was a problem as ClearString could possibily be applied multiple times to the same string. To solve this the class CSqlString has been added. This class takes a const char* and copies it. Additionally a clearstring is created from the given const char*. This enables access to the original as well as the cleared string safe for sql-statements. sql_string_helpers got an own source file now. A crashbug from CSqlServer has been fixed (pointer has not been set back to 0)

def- · 2016-05-13T22:45:47Z

How are the Chile tests going?

H-M-H · 2016-05-14T10:16:08Z

Pretty good, already saved more than 100 ranks and found + fixed some bugs. Currently I am testing if fallbacks work fine with more than one sql-server.

H-M-H added server Related to the server-side. incomplete More information needs to be provided for this issue. cleanup Code cleanup and refactoring. labels Nov 22, 2015

H-M-H force-pushed the sqlmasters branch from 1592d16 to 67f980b Compare November 23, 2015 21:55

H-M-H removed the incomplete More information needs to be provided for this issue. label Nov 23, 2015

H-M-H added the next release label Jan 7, 2016

H-M-H force-pushed the sqlmasters branch from 700cd68 to fb96bdb Compare January 17, 2016 17:31

H-M-H force-pushed the sqlmasters branch from fb96bdb to 04afba6 Compare January 27, 2016 22:11

H-M-H added 14 commits February 20, 2016 15:33

Cleanup of Sqlconnection

9ed66f7

-moved all connectionstuff to a new class -moved sqlstring functions to an own file -do not give threads access to CSqlScore

added an exec command to sever_autoexec.cfg for

66bec0c

custom configs

started with sqlmasters

5e44565

+ added #ifdef for sql_server.cpp (fixes compilation for release mode)

mastersqlservers are working now

6ba10d2

TODO: move the sqlserver objects somewhere else so they wont be destroyed on every reload

moved sqlserver stuff into CServer-class

15f2e07

as sqlconnections should not last only until next mapreload

fixed use of locks for threading:

c4ab59e

each sqlserver has its own lock now -> it is required that every connect call is followed by a disconnect call

ConAddSqlMaster will not block the main thread anymore

7199ae3

try next sqlmaster also if the current write fails

2d41ce3

restructured sqlconnection

1314085

reading from and writing to several different servers is now possible TODO: -handle Exceptions properly (try another sqlserver) -if everything fails while writing write the insert to a file

handle exceptions from sql properly

88ed739

- write failed sqlinserts to a file - improved structure

'r' and 'w' instead of 0 and 1 for sql read/write servers

1e302ae

whether to setup a db can be configured for each server now

e35de5a

more generic format of saved sql-inserts

8dacd88

updated sql-command usage

f6be7e2

H-M-H added 3 commits May 3, 2016 23:07

Merge branch 'master' into sqlmasters

c9e65c7

added CSqlPlayerData for simple tasks like getting best time

94f163f

checkbirthday now also sends a broadcast to the 'birthdaychild'

a93e845

def- reviewed May 3, 2016
View reviewed changes

H-M-H added 2 commits May 4, 2016 00:24

removed double semicolons

e351080

fixed indentation

13688bb

H-M-H added 3 commits May 4, 2016 15:32

wait for score-threads on shutdown

cd41220

fixed birthdaycheck and the format of sql-inserts for the failure-file

a7c0cd5

put sql_string_helpers into an own namespace

5dc47ed

H-M-H added 5 commits May 4, 2016 23:37

made writing to sql-failurefile threadsafe

30ca8d8

also destroy locks created by CSqlServer

931d99e

do not write nullbytes to failed_sql.sql

4bf1a68

fixed includes

6a2438e

H-M-H force-pushed the sqlmasters branch from 7c640e0 to 6a2438e Compare May 11, 2016 13:02

H-M-H added 2 commits May 11, 2016 15:26

fixed use of Str and ClrStr

42fc8a0

fixed /load message

2bcbe52

H-M-H added 4 commits May 16, 2016 15:37

set timeout for sql-reads/writes

a073d22

do not log the sql-passwords

c484386

Merge branch 'master' into sqlmasters

b648b53

don't clear sql-mapstring twice

ea5892b

H-M-H merged commit ada3b9a into ddnet:master May 28, 2016

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

separate sqlwrite and sqlread server #378

separate sqlwrite and sqlread server #378

H-M-H commented Nov 22, 2015

H-M-H commented Nov 22, 2015

MyNewBie commented Nov 23, 2015

H-M-H commented Nov 23, 2015

H-M-H commented Nov 23, 2015

def- commented Dec 8, 2015

def- commented Dec 15, 2015

H-M-H commented Dec 15, 2015

def- commented Jan 12, 2016

H-M-H commented May 3, 2016

def- May 3, 2016

H-M-H May 3, 2016

def- commented May 3, 2016

H-M-H commented May 3, 2016 •

edited

def- commented May 4, 2016

def- commented May 13, 2016

H-M-H commented May 14, 2016

separate sqlwrite and sqlread server #378

separate sqlwrite and sqlread server #378

Conversation

H-M-H commented Nov 22, 2015

H-M-H commented Nov 22, 2015

MyNewBie commented Nov 23, 2015

H-M-H commented Nov 23, 2015

H-M-H commented Nov 23, 2015

def- commented Dec 8, 2015

def- commented Dec 15, 2015

H-M-H commented Dec 15, 2015

def- commented Jan 12, 2016

H-M-H commented May 3, 2016

def- May 3, 2016

Choose a reason for hiding this comment

H-M-H May 3, 2016

Choose a reason for hiding this comment

def- commented May 3, 2016

H-M-H commented May 3, 2016 • edited

def- commented May 4, 2016

def- commented May 13, 2016

H-M-H commented May 14, 2016

H-M-H commented May 3, 2016 •

edited