Jump to content

DELETE and INSERT instead of REPLACE? (MySQL)


Guest lillecarl

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 1 month later...

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue. Privacy Policy Terms of Use