DELETE and INSERT instead of REPLACE? (MySQL)

[lillecarl]

I just saw that the character saving does 1st, delete the player, then insert him, how come you dont just replace him? That would be more efficent and reduce the load on the database server right?

EDIT: Just read this, thats why;): https://github.com/kicho/mangos/commit/78b5ca8964f9907df8a0285acb5c968acff7adfd

- LilleCarl

[Lynx3d]

That's very simple, because "REPLACE" does not exist in the SQL standard.

It's just a custom extension in several SQL-compatible server implementations.

[lillecarl]

But MaNGOS is compatible with MySQL and PostgreSQL? And i think both got REPLACE?

[Skirnir]

And i think both got REPLACE?

Nope. See for 8.4 [1] and for upcoming 9.0 [2]

Regards

Skirnir

[1] http://www.postgresql.org/docs/8.4/static/sql-commands.html

[2] http://www.postgresql.org/docs/9.0/static/sql-commands.html

EDIT: Other than that I don't know how functional PostgreSQL is in MaNGOS. The worlddatabase providers I know only support MySQL and it's a pain in the … to convert these files to Postgre and if I got it right MaNGOS has only for MySQL prepared statements.

[lillecarl]

And i think both got REPLACE?

Nope. See for 8.4 [1] and for upcoming 9.0 [2]

Regards

Skirnir

[1] http://www.postgresql.org/docs/8.4/static/sql-commands.html

[2] http://www.postgresql.org/docs/9.0/static/sql-commands.html

EDIT: Other than that I don't know how functional PostgreSQL is in MaNGOS. The worlddatabase providers I know only support MySQL and it's a pain in the … to convert these files to Postgre and if I got it right MaNGOS has only for MySQL prepared statements.

Then drop postgre support? And optimize for mysql?

- LilleCarl

[Skirnir]

Erratum: Just saw 9.0 was already released.

Why should PostgreSQL support be dropped? Iirc PostgreSQL was the best database for servers with a huge amount of characters - until the prepared statements patch was accepted. Since no one wrote a patch for PostgreSQL this might currently be different.

Another thing is, no one can foresee how long MySQL will be developed as opensource. Oracle has it's very own database and there are a few good reasons why they could drop MySQL. As long as compatible alternatives like MariaDB are available, fine. Still no one knows how long these will be supported. PostgreSQL won't disappear that fast. That's why dropping MySQL could be the other alternative ;-)

Regards

Skirnir

[lillecarl]

Oh well, but im mysql goes closed, then wouldnt someone else pick the development up?

[Machiavelli]

Actually REPLACE is no different than DELETE+INSERT in terms of performance. The ideal query would be INSERT... ON DUPLICATE KEY UPDATE.

[lillecarl]

Actually REPLACE is no different than DELETE+INSERT in terms of performance. The ideal query would be INSERT... ON DUPLICATE KEY UPDATE.

Could you explain that futher? And also, how much does it differ between update and delete+insert queries (in pure speed)

- LilleCarl

[VladimirMangos]

in mysql in fact REPLACE is just macro for DELETE+INSERT, so exactly same time expected

[Feanordev]

Well, thing is I've heard player can (with really small chance) lose character inbetween DELETE and INSERT query. and REPLACE prevents such case.

[Schmoozerd]

I have heard that indeed characters can be lost.

However this must be fixed proper with sql transactions and rollbacks to force that DELETE + INSERT are only done when both will succeed

(And afaik they should be already secure..)

[lillecarl]

Well, thing is I've heard player can (with really small change) lose character inbetween DELETE and INSERT query. and REPLACE prevents such case.

I think that is if your INSERT query is invalid, i think REPLACE checks if the query will work before replacing?

[herrtrigger]

Well, thing is I've heard player can (with really small change) lose character inbetween DELETE and INSERT query. and REPLACE prevents such case.

in mysql in fact REPLACE is just macro for DELETE+INSERT, so exactly same time expected

[Machiavelli]

These are the most likely scenario's why DELETE+INSERT would cause data loss.

1. Desynchronisation - the database layer causes the INSERT to be executed before the DELETE (causing a duplicate error, and eventually a deletion of the record)

2. Disconnection - the connection to the MySQL server severs after DELETE statement, the INSERT statement can not be executed/

Both can be prevented by proper transaction usage (for which the database layer respects serialization, so the DELETE is actually executed before the INSERT). - I'm not sure how this is done in MaNGOS.

As for why INSERT .. ON DUPLICATE KEY UPDATE is faster; the MySQL server will not waste time re-checking indexes for both INSERT and DELETE statements.

[desteny]

As for why INSERT .. ON DUPLICATE KEY UPDATE is faster; the MySQL server will not waste time re-checking indexes for both INSERT and DELETE statements.

i can confirm this, on some large tables like characters and so on the index recalculation after delete and insert will produce a notable amount of workload for the database. and it even causes a kind of fragmentation witch could be optimized only with the mysql optimize command.

my personal suggest is to use the INSERT .. ON DUPLICATE KEY UPDATE statement for SaveToDB methods, it will improve the database performance and reduces maintainance intervalls (optimize) and it even helps to keep the code more simple. (in some cases where you have to check)

one word on the transactions: i think each update / insert statement should be executed in a transaction, because it perevents data loss ore inconsistency, of course the performance is not as good as dirty write, but who wants to loose data or have consistency problems? no one, and the most database querys (insert update) are executed async, for this reason it will not realy reduce the performance.

I could not tell you if this is already like this on mangos, but at trinity it works like this (in the most cases) and it works fine