Update insert id from backup damp ON DUPLICATE KEY UPDATE LAST_INSERT_ID

[leonardos1978 33]

Hi, i have any errors when i try update deleted terrytory from backup damp
Error Code: 1062. Duplicate entry '241786' for key 'PRIMARY'

I call to datebase

Spoiler

INSERT INTO `construction` (`id`,`class`,`account_uid`,`spawned_at`,`position_x`,`position_y`,`position_z`,`direction_x`,`direction_y`,`direction_z`,`up_x`,`up_y`,`up_z`,`is_locked`,`pin_code`,`damage`,`territory_id`,`last_updated_at`,`deleted_at`) VALUES
(241786,'Exile_Construction_WoodGate_Static','76561198048299857','2018-03-13 19:25:02',13794.481445,6825.724609,-500,0.448578,0.893744,0,0,0,1,0,'000000',0,0,'2018-03-13 19:25:02',NULL) 

 ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id + 1);

please help me)

Edited January 23 by leonardos1978

[leonardos1978 33]

Auto answer: Without ID on this schema damp writed into database. 

Edited January 23 by leonardos1978

[Z80CPU 527]

Hello @leonardos1978,

What I do when I do a restore, as this ELIMINATES ANY ISSUES, I DELETE the old DB.  THEN I restore.  ZERO ERRORS and ZERO ISSUES.

Just make sure you have a PROPER DB SAVE FIRST!  ;)

 

[leonardos1978 33]

2 hours ago, Z80CPU said:

Hello @leonardos1978,

What I do when I do a restore, as this ELIMINATES ANY ISSUES, I DELETE the old DB.  THEN I restore.  ZERO ERRORS and ZERO ISSUES.

Just make sure you have a PROPER DB SAVE FIRST! 

 

I meen, when territory is deleted and i restore territory, constructions, containers and more from dump.

[Z80CPU 527]

BINGO!  Because you are NOT JUST RESTORING THE DELETED BASE!  This is why!

AGAIN - The EASIEST method is to DELETE THE DB AND RESTORE THE WHOLE THING!  ZERO ERRORS!

Yes, you CAN restore 'this and that', BUT, you MUST get the correct fields/keys OR YOU WILL GET ERRORS!

The error you are getting means you are OVERWRITING AN EXISTING ITEM!  In other words, you are restoring OTHER THINGS besides that deleted 'base'.  You have 2 players, A and B.  B's base gets deleted so you restore it.  When YOU do the restore, you are restoring A's AND B's!  BOTH OF THEM!  You SHOULD be restoring B's ONLY.  THIS is your problem!

The REAL question is why are you deleting/restoring ANYWAY?  If these people have to be away for a while, you can change the 'pay date' in the DB to a future date and they will NOT BE DELETED (till that date)!  If they are lazy and do not pay on time, oh well, THIS IS THE GAME! 

 

So, these are you're options:

#1 - Restore PROPERLY/ONLY those items that have been deleted.  There are MANY ways to do this.  (You have been restoring EVERYTHING)  <-- BEST METHOD

#2 - Merge the old DB with the current DB. <-- Works, but takes a lot of work and prone to a lot of errors

#3 - Delete the current DB and restore the backup. <-- The EASIEST, but EVERYBODY gets 'rolled back'

 

Tips:

MySQL BackUp Tips     MySQL Restore Tips

These 'Tips' will tell you/show you how to do this PROPERLY.

 

[leonardos1978 33]

The question is more than simple, if I make a query to the database, then I can rename the id to the new numbering value and everything goes smoothly bypassing auto-numbering

INSERT INTO `construction` ( `class`,` account_uid`, `spawned_at`,` position_x`, `position_y`,` position_z`, `direction_x`,` direction_y`, `direction_z`,` up_x`, `up_x`, `up_y`,` up_z`, `is_locked`,` pin_code`, `повреждение`,` территория_id, `last_updated_at`,` Удаленный_кат`) VALUES 
('Exile_Construction_WoodGate_Static', '76561198048299857', '2018-03-13 19: 25: 02 ', 13794.481445,6825.724609, -500,0,448578,0,893744,0,0,0,1,0,' 000000 ', 0,0,' 2018-03-13 19: 25: 02 ', NULL ) ; 

But i want make it without "dance with tambourine" using a secure database merge.

P.S. It's all right with merge, without ID, null errors.

Edited January 25 by leonardos1978

[Tech_Support 5]

On 1/23/2019 at 9:06 AM, leonardos1978 said:

Hi, i have any errors when i try update deleted terrytory from backup damp
Error Code: 1062. Duplicate entry '241786' for key 'PRIMARY'

I call to datebase

  Reveal hidden contents

INSERT INTO `construction` (`id`,`class`,`account_uid`,`spawned_at`,`position_x`,`position_y`,`position_z`,`direction_x`,`direction_y`,`direction_z`,`up_x`,`up_y`,`up_z`,`is_locked`,`pin_code`,`damage`,`territory_id`,`last_updated_at`,`deleted_at`) VALUES
(241786,'Exile_Construction_WoodGate_Static','76561198048299857','2018-03-13 19:25:02',13794.481445,6825.724609,-500,0.448578,0.893744,0,0,0,1,0,'000000',0,0,'2018-03-13 19:25:02',NULL) 

 ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id + 1);

please help me)

if you dont want to do it the easy way well then find entry id '241786' and drop it then update it

when you try to insert your code this id is already in place so error = Duplicate entry