0

For the purpose of backup and restore i want few tables to be just backup while performing a restore process. I have created backup tables but now whenever i do restore process it shows:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062      Duplicate entry '1' for key 'PRIMARY':

Actually both tables have same schema and same primary key so whenever it try to update it there is conflict. Can anyone please tell me how to do it ? One more thing can i use drupal Features to do it and if yes please let me how.

0

2 Answers 2

0

You should use

Merge Queries

a Merge query is a combination of an Insert query and an Update query. If a given condition is met, such as a row with a given primary key already existing, then an Update query is run. If not, an Insert query is run.

The above is for drupal

If you are using mysql then you can use UPDATE or ON DUPLICATE KEY UPDATE

5
  • I am using single query as: db_query("INSERT INTO $backup_tbl_name SELECT * FROM $tbl"); , to insert respective backup tables and each backup table has its own schema similar to its parent table. Now how to use merge query to all such tables ? Apr 4, 2016 at 8:15
  • 1
    actually, in this case you should use ON DUPLICATE KEY UPDATE. For example: see my answer here stackoverflow.com/questions/36144788/join-a-specific-table-row. All you have to do is add the last line with the values of your $backup_tbl_name
    – No Sssweat
    Apr 4, 2016 at 8:35
  • Can I use "ON DUPLICATE KEY UPDATE" alone ? As i am using single query to insert multiple tables so i can't use any condition for it. Apr 4, 2016 at 9:27
  • 1
    Drupal's API won't help you with a query that complicated, you'll need to come up with the raw SQL and just run that through db_query.
    – Clive
    Apr 4, 2016 at 9:48
  • @RajenderRajan you can't use ON DUPLICATE KEY UPDATE alone, if you want alone use UPDATE instead.
    – No Sssweat
    Apr 4, 2016 at 22:30
0

I have resolved the issue by doing following thing, hope it may be useful to anyone too.

The cause of the problem is that the parent table and its corresponding backup table have same schema so whenever Restore process is took place it just encounter a problem due to same primary key on both tables. Hence to overcome it,first remove Primary Key from backup table as:

ALTER TABLE $backup_tbl_name DROP PRIMARY KEY, CHANGE $primary_key $primary_key int(11) NOT NULL

and then add a new column 'ID' to all backup tables using :

ALTER TABLE $backup_tbl_name ADD `id` BIGINT unsigned NOT NULL   AUTO_INCREMENT COMMENT 'Auto increment field for backup table.' PRIMARY KEY

and making this newly added column Primary key of backup table.

And at last INSERT query has to be modified as well as during insertion newly added column also has to be pass. And it modified as:

INSERT INTO $backup_tbl_name SELECT * , NULL FROM $tbl;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.