phpmyadmin SQL Error with import all tables

[panier]

Hello,
i did install the com_virtuemart.2.0.20b in a really empty joomla installation (joomla manually installed without examples @ strato.de, the virtuemart with examples) and did test to transfer the whole database with phpmyadmin Export.

The local import to xampp (PHP/5.3.1, Perl/v5.10.1,MySQL-Client-Version: 5.1.41) of the whole database  was not possible.
The failure is:
INSERT INTO `Demo_virtuemart_states` (`virtuemart_state_id`, `virtuemart_vendor_id`, `virtuemart_country_id`, `virtuemart_worldzone_id`, `state_name`, `state_3_code`, `state_2_code`, `ordering`, `shared`, `published`, `created_on`, `created_by`, `modified_on`, `modified_by`, `locked_on`, `locked_by`) VALUES (390, 1, 10, 0, 'San Juan', 'SJN', 'SJ', 0, 0, 1, '0000-00-00 00:00:00', 0, '0000-00-00 00:00:00', 0, '0000-00-00 00:00:00', 0), (391, 1, 10, 0, 'San Luis', 'SLU', 'SL', 0, 0, 1, '0000-00-00 00:00:00', 0, '0000-00-00 00:00:00', 0, '0000-00-00 00:00:00', 0), (392, 1, 10, 0, 'Santa Cruz', 'SCZ', 'SC', 0, 0, 1, '0000-00-00 00:00:00', 0, '0000-00-00 00:00:00', 0, '0000-00-00 00:00:00', 0), (393, 1, 10, 0, 'Santa Fe', 'SFE', 'SF', 0, 0, 1, '0000-00-00 00:00:00', 0, '0000-00-00 00:00:00', 0, '0000-00-00 00:00:00', 0), (394, 1, 10, 0, 'Santiago Del Estero', 'SEN', 'SE', 0, 0, 1, '0000-00-00 00:00:00', 0, '0000-00-00 00:00:00', 0, '0000-00-00 00:00:00', 0), (397, 1, 11, 0, 'Aragatso[...]

MySQL meldet: Dokumentation
#1062 - Duplicate entry '84-???' for key 'idx_state_3_code'

Without the virtuemart I have no problem, export and import work fine.
How ist the correct procedure to transfer a joomla database with virtuemart?
Any Idea how to work around this bug?

Thanks.

Best regards
Burkhard

[franzpeter]

Did you truncate the tables before! importing? There is already something in the VM table? The idx_state_code duplicate shows, that there is already something in the table (or at least inside the index of that table). To exclude that you should configure the export! file with phpmyadmin by selecting: drop table configuration.

[sp1der]

Hello. I have chances upon this thread as I am also facing this issue. not wanting to hijack this thread.

My joomla version is 2.5.11 and vm is on 2.0.14. My site is running fine except when I try to restore the database backup to another site the same problem surface.

The funny thing is there isnt any vm configuration amendment or usage of vm at all till date and i wonder what went wrong.

error from phpadmin:

SQL query:

INSERT INTO `tbp01_virtuemart_states` (`virtuemart_state_id`, `virtuemart_vendor_id`, `virtuemart_country_id`, `virtuemart_worldzone_id`, `state_name`, `state_3_code`, `state_2_code`, `ordering`, `shared`, `published`, `created_on`, `created_by`, `modified_on`, `modified_by`, `locked_on`, `locked_by`) VALUES (1,1,223,0,'Alabama','ALA','AL',0,0,1,'0000-00-00 00:00:00',0,'0000-00-00 00:00:00',0,'0000-00-00 00:00:00',0),(2,1,223,0,'Alaska','ALK','AK',0,0,1,'0000-00-00 00:00:00',0,'0000-00-00 00:00:00',0,'0000-00-00 00:00:00',0),(3,1,223,0,'Arizona','ARZ','AZ',0,0,1,'0000-00-00 00:00:00',0,'0000-00-00 00:00:00',0,'0000-00-00 00:00:00',0),(4,1,223,0,'Arkansas','ARK','AR',0,0,1,'0000-00-00 00:00:00',0,'0000-00-00 00:00:00',0,'0000-00-00 00:00:00',0),(5,1,223,0,'California','CAL','CA',0,0,1,'0000-00-00 00:00:00',0,'0000-00-00 00:00:00',0,'0000-00-00 00:00:00',0),(6,1,223,0,'Colorado','COL','CO',0,0,1,'0000-00-00 00:00:00',0,'0000-00-00 00:00:00',0,'0000-00-00 00:00:00',0),(7,1,223,0,'Connect[...]

MySQL said: Documentation
#1062 - Duplicate entry '84-ΛÎ' for key 'idx_state_3_code'

[Milbo]

Panier, I have absolutly clue what you try todo. Seems u r missing some simple knowledge? No one uses phpmyadmin to import/export a database. No one does it, yeh beginner try,... .like I did, but it is like using a car with a horse. You may read here http://www.mysqldumper.de/ (has nothing todo with VM).

If you want to learn how to prepare a site at home and then to transfer them, use akeeba backup or similar.

[franzpeter]

Milbo, that is not true. Why should phpmyadmin not work? If the database is not too big, it works with phpmyadmin.  Seems that panier and sp1der just need to mirror the database. The only thing to take care about is the configuration of phpmyadmin for export. If both databases, the one to export and the one to import should have exactly the same tables and data I would tell phpmyadmin to add create table statements and it should not check foreign key. For the default data it should work. The target database should not contain any table, only the database should have been created.
Sorry, I do not use akeeba, because sometimes it seems to work for others not. So a backup is there to ensure, that your data could get restored after problems. That it works sometimes and sometimes not is simply just not enough!

[Milbo]

I gave the URL to the mysql dumper. As you said, if the database is not too big. mysqldumper takes care of this. you can import with it 500 MB or more, no problem.

[slammy]

Hi panier,

I faced the same problem while inserting the dump (yet running jml 2.5.11 and vm sr 2.0.14 and trying to update to 2.0.22a), but not with phpmyadmin. I am using Linux shell to insert a dumpfile via mysql console.

The problem in our *.SQL-dump statement seems to be the following:

There is the table named virtuemart_states, and there are two fields Name idx_state_3_code and idx_state_2_code. Both were defined same time as unique and index, and because of the table contents, there are duplicated 3_codes and 2_codes in it, the insert stops and gives out this error, because the fields are defined as unique, so duplicate entrys are not allowed and the insert stopps ...

I did the following to get it done (witout any warranty to u, maybe Milbo can say if this is okay this way ...):

Open your *.SQL File with Editor and search for "virtuemart_states", there you will find some code similar to this:

delete the lines which are crossed out in the Statement:



CREATE TABLE `hgm89_virtuemart_states` (
  `virtuemart_state_id` smallint(1) unsigned NOT NULL AUTO_INCREMENT,
  `virtuemart_vendor_id` smallint(1) unsigned NOT NULL DEFAULT '1',
  `virtuemart_country_id` smallint(1) unsigned NOT NULL DEFAULT '1',
  `virtuemart_worldzone_id` smallint(1) unsigned NOT NULL DEFAULT '0',
  `state_name` char(64) DEFAULT NULL,
  `state_3_code` char(3) DEFAULT NULL,
  `state_2_code` char(2) DEFAULT NULL,
  `ordering` int(2) NOT NULL DEFAULT '0',
  `shared` tinyint(1) NOT NULL DEFAULT '0',
  `published` tinyint(1) NOT NULL DEFAULT '1',
  `created_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` int(11) NOT NULL DEFAULT '0',
  `modified_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_by` int(11) NOT NULL DEFAULT '0',
  `locked_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `locked_by` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`virtuemart_state_id`),
UNIQUE KEY `idx_state_3_code` (`virtuemart_country_id`,`state_3_code`),
  UNIQUE KEY `idx_state_2_code` (`virtuemart_country_id`,`state_2_code`),
  KEY `i_virtuemart_vendor_id` (`virtuemart_vendor_id`),
  KEY `i_virtuemart_country_id` (`virtuemart_country_id`)
) ENGINE=MyISAM AUTO_INCREMENT=554 DEFAULT CHARSET=utf8 COMMENT='States that are assigned to a country' AUTO_INCREMENT=554 ;

--
-- Daten für Tabelle `hgm89_virtuemart_states`
--

After I´ve done that, the dump was inserted without any errors.

@Milbo: should I restore manually index and unique with phpmyadmin on the fields again?

I don´t know why I had duplicate entrys in this table, but it seems that it will work that way.
regards jens

[lordimac]

Hi, running into the same Problem with an SQL Dump from another Server. Dumped all Databases and when restoring the SQL File I get this Error:

ERROR 1062 (23000) at line 86342: Duplicate entry '84-???' for key 'idx_state_3_code'

[Dan1980]

Hi guys,

Same here. Tried importing via phpMyAdmin and getting the same error as mentioned earlier.

#1062 - Duplicate entry '84-ΛÎ' for key 'idx_state_3_code'

I tried slammy's suggestion above, but it didn't work. Ended up getting another error...

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ENGINE=MyISAM AUTO_INCREMENT=730 DEFAULT CHARSET=utf8 COMMENT='States that are' at line 20

Which is clearly because I had taken out those lines as suggest by slammy. Unfortunately that little trick didn't work for me.

Tried also uploading a zipped/compressed version, as mentioned earlier in this thread. No luck either.

Anyone else have suggestions on how to fix this? Or how to migrate a site to a different host when this particular issue crops up?

FYI: Am running Joomla 2.5.19 and VirtueMart 2.0.26a. Currently in an environment with PHP 5.3.23, and planning on moving to an environment with PHP 5.3.27 (so not much difference, just adding that info here for completeness).

Thanks

[Milbo]

This is strange, because the key is unique for "country_id + state_id". And actually this is very important.

[slammy]

Hi Dan1980,

there is one commata in my post which is wrong in the last sql-setting of a key. This line last commata -> KEY `i_virtuemart_vendor_id` (`virtuemart_vendor_id`),
- very sry for that. This is the right way:

CREATE TABLE `hgm89_virtuemart_states` (
  `virtuemart_state_id` smallint(1) unsigned NOT NULL AUTO_INCREMENT,
  `virtuemart_vendor_id` smallint(1) unsigned NOT NULL DEFAULT '1',
  `virtuemart_country_id` smallint(1) unsigned NOT NULL DEFAULT '1',
  `virtuemart_worldzone_id` smallint(1) unsigned NOT NULL DEFAULT '0',
  `state_name` char(64) DEFAULT NULL,
  `state_3_code` char(3) DEFAULT NULL,
  `state_2_code` char(2) DEFAULT NULL,
  `ordering` int(2) NOT NULL DEFAULT '0',
  `shared` tinyint(1) NOT NULL DEFAULT '0',
  `published` tinyint(1) NOT NULL DEFAULT '1',
  `created_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` int(11) NOT NULL DEFAULT '0',
  `modified_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_by` int(11) NOT NULL DEFAULT '0',
  `locked_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `locked_by` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`virtuemart_state_id`),
UNIQUE KEY `idx_state_3_code` (`virtuemart_country_id`,`state_3_code`),
  UNIQUE KEY `idx_state_2_code` (`virtuemart_country_id`,`state_2_code`),
  KEY `i_virtuemart_vendor_id` (`virtuemart_vendor_id`) ,
  KEY `i_virtuemart_country_id` (`virtuemart_country_id`)
) ENGINE=MyISAM AUTO_INCREMENT=554 DEFAULT CHARSET=utf8 COMMENT='States that are assigned to a country' AUTO_INCREMENT=554 ;

--
-- Daten für Tabelle `hgm89_virtuemart_states`
--

can you try again?

[Milbo]

actually we have now

Code Select Expand


PRIMARY KEY (`virtuemart_state_id`),
  KEY `i_virtuemart_vendor_id` (`virtuemart_vendor_id`),
  UNIQUE KEY `idx_state_3_code` (`virtuemart_vendor_id`,`virtuemart_country_id`,`state_3_code`),
  UNIQUE KEY `idx_state_2_code` (`virtuemart_vendor_id`,`virtuemart_country_id`,`state_2_code`),
  KEY `i_virtuemart_country_id` (`virtuemart_country_id`),
  KEY `i_ordering` (`ordering`),
  KEY `i_shared` (`shared`),
  KEY `i_published` (`published`)


[Milbo]

Heyho, I just used the backup data of slammy with the new keys, works!

[Dan1980]

there is one commata in my post which is wrong in the last sql-setting of a key. This line last commata -> KEY `i_virtuemart_vendor_id` (`virtuemart_vendor_id`),
- very sry for that. This is the right way:

That worked!! Thanks Slammy.

Wow, it was just a tiny comma that made all the difference.

Perfect, thanks guys, I appreciate the fast responses.

[slammy]

good to hear that it worked Dan1980

Pls could you mark the thread as solved by editing [solved] in the topic-header and set the icon by selecting solved from the dropdown, thank u!
regards jense