[Suggestion]: Using REPLACE INTO

[crashuncle]

Hello Mangos Community,

I see often in the Server console SQL errors like "INSERT into character_spell .... Duplicated Entry" which appear also for Player items and other parts of the DB.

My question is, why we cant use REPLACE INTO? This would overwrite the old entries with the newer information's and it is executed pretty fast / prevent console or log Spam.

Is there any disadvantage about REPLACE INTO which I don't know?

Crashuncle

[Shadowfox]

Personally I usually end up dissecting and putting it into my database, but I've never used the "REPLACE INTO" but I have seen a couple SQL files that were written that way.

[Senpuria]

Yeah, you can certainly use the replace query to replace an entry insted of having it as a duplicate entry.

[kich0]

as far as I know there aren't any disadvantages in using this...

[False.Genesis]

as far as I know there aren't any disadvantages in using this...

afaik REPLACE INTO is not SQL standard, but an extension by mySQL (other db systems might not support it - not sure about postgre, never used it). if used in a transaction, delete+insert have exactly the same effect.

[DasBlub]

afaik REPLACE INTO is not SQL standard, but an extension by mySQL (other db systems might not support it - not sure about postgre, never used it). if used in a transaction, delete+insert have exactly the same effect.

agree, mysql only. not supported in other db systems (like oracle or sybase). suggest to use transaction with delete+insert

[freghar]

afaik REPLACE INTO is not SQL standard, but an extension by mySQL (other db systems might not support it - not sure about postgre, never used it). if used in a transaction, delete+insert have exactly the same effect.

The problem might be at server crash, where only delete statement is sent ... so you'd have to send two queries in one mysql function call ...

I'm no DB expert, but what's the difference between "REPLACE INTO" and "INSERT IGNORE INTO" ?

[charlie2025]

I'm no DB expert, but what's the difference between "REPLACE INTO" and "INSERT IGNORE INTO" ?

If there is some record in table wih some primary key .. lets say 1 and you do

REPLACE INTO `table1` (`primary_key`,`some_other_column`) VALUES

('1','blabla');

this command replace existing record by this new,

if you use

INSERT IGNORE INTO `table1` (`primary_key`,`some_other_column`) VALUES

('1','blabla');

this command does nothing, doesn't replace existing record.. only doesn't return error message "Duplicate entry".. if you would use INSERT INTO, this message would appear.

[freghar]

If there is some record in table wih some primary key .. lets say 1 and you do

REPLACE INTO `table1` (`primary_key`,`some_other_column`) VALUES

('1','blabla');

this command replace existing record by this new,

if you use

INSERT IGNORE INTO `table1` (`primary_key`,`some_other_column`) VALUES

('1','blabla');

this command does nothing, doesn't replace existing record.. only doesn't return error message "Duplicate entry".. if you would use INSERT INTO, this message would appear.

And by Google I guess 'ON DUPLICATE KEY' is also mysql-only ...

[crashuncle]

Well REPLACE INTO has 2 advantages...

- No more duplicate keys (For example in the Char tables always the "newer" stuff get applied and not elder stuff re-appear)

- The chance to loose entries is lower (Read below)

For example:

DELETE FROM ...

[DB Server Disconnected]

INSERT INTO...

[Cant run query]

-> Something would be gone here

REPLACE INTO...

[DB Server Disconnected]

-> No loss, or if the DB Server DC before it simply dont change anything.

(INSERT IGNORE is quite pointless cause that has the same result as a INSERT INTO on a duplicate entry)

[Wyk3d]

If you look closely DELETE+INSERT is always put in a transaction. If the server crashes after delete but before insert, the delete is automatically rolled back and there is no data loss (that is assuming your table is not crashed and you haven't change your tables to MyISAM which doesn't support transactions). That said if you still have errors on the inserts that means something is wrong with the core, and just using REPLACE INTO (slower) to hide the errors instead of fixing the real problem would be a hack.

[dereka]

REPLACE not exist in PostgreSQL!!