Phpmyadmin export problem

Question

My shared host does not allow access to SSH. I am trying to export a database using phpmyadmin and import to a new server. I keep getting this error, and I'm not sure how to fix it. Any help is appreciated.

Error SQL query: -- -- Indexes for dumped tables -- -- -- Indexes for table `EWRporta_blocks` -- ALTER TABLE `EWRporta_blocks` ADD PRIMARY KEY ( `block_id` ) , ADD KEY `title` ( `title` ) ; MySQL said: Documentation #1068 - Multiple primary key defined 

Answer 1

I ran into this problem several times, and modonoghue has one right way to handle it, dropping your tables and completely re-creating them.

Problem and General Solution

Basically what happens is that you are trying to run INSERT statements that insert values ​​into primary keys that already exist, which gives you the duplicate key error. The database has no idea how to process multiple records with the same key, because the SQL logic is based on each row that has a primary key that is completely unique.

What you want to do is to save all the values ​​in the exported sql file in a query that, when you re-import the file, removes all existing values ​​(provided that you want to restore it at some point and not worry about the data saved between the export date and date of import!) and inserts all exported values ​​... or in some other way avoids trying to add a new record with an existing key (see below).

One way to export a specific database (TRUNCATE):

• In other words, when you use PHPMYADMIN to export your sql file, click "Custom - show all possible options."
• In the "Format-specific parameters" section, make sure that the <structure of the structure and data is selected (otherwise you may lose your tables and not have data to restore them !!!)
• In the "Data creation options" section, select "Trim the table before pasting" - this will delete all existing data in the tables.

Upon import, all existing data will be deleted from each table (TRUNCATE), and all exported data will be written back to the tables (INSERT), but the tables themselves will not be deleted (DROP).

IGNORE vs TRUNCATE (alternative route)

You can skip Step 3 above (TRUNCATE) and instead check the box "Instead of using INSERT ..." IGNORE INSERTS

Basically, IGNORE simply skips duplicates in the exported data and does not allow you to delete existing data. This is good if you want to just add back the lost data without deleting the data that has been changed / added since the last export.

Superuser (ON DUPLICATE KEY UPDATE)

There is also an INSERT INTO ... ON DUPLICATE KEY UPDATE ... which allows you to say exactly what to do if there is a duplicate key. This will prevent just ignoring two entries with identical keys, which may not be identical. However, it’s harder to set up correctly.

Answer 2

As others said, clear and recreate the tables. In phpmyadmin, select the "Add DROP TABLE" check box when exporting. Then, the described problem should be resolved upon import.

Answer 3

Delete Comment Generated Using Export Tool

 -- -- Indexes for dumped tables -- -- -- Indexes for table `EWRporta_blocks` -- 

and try to execute the request only after

ALTER TABLE EWRporta_blocks ADD PRIMARY KEY ( block_id ), ADD KEY title ( title );