Error importing civicrm data base after Multi Language is set

[bobd]

Using Civicrm 3.0.1


I have done a test (export & import) just before setting civicrm to multi language and it works fine.

I set civicrm to multi language - export - then import to empty database and I get the following error.
_________________________________
Error

SQL query: Documentation

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `civicrm1124`.`civicrm_address_en_US` AS select `civicrm1124`.`civicrm_address`.`id` AS `id`,`civicrm1124`.`civicrm_address`.`contact_id` AS `contact_id`,`civicrm1124`.`civicrm_address`.`location_type_id` AS `location_type_id`,`civicrm1124`.`civicrm_address`.`is_primary` AS `is_primary`,`civicrm1124`.`civicrm_address`.`is_billing` AS `is_billing`,`civicrm1124`.`civicrm_address`.`street_number` AS `street_number`,`civicrm1124`.`civicrm_address`.`street_number_suffix` AS `street_number_suffix`,`civicrm1124`.`civicrm_address`.`street_number_predirectional` AS `street_number_predirectional`,`civicrm1124`.`civicrm_address`.`street_name` AS `street_name`,`civicrm1124`.`civicrm_address`.`street_type` AS `street_type`,`civicrm1124`.`civicrm_address`.`street_number_postdirectional` AS `street_number_postdirectional`,`civicrm1124`.`civicrm_address`.`street_unit` AS `street_unit`,`civicrm1124`.`civicrm_address`.`cou[...]

MySQL said: Documentation
#1050 - Table 'civicrm_address_en_US' already exists
_________________________________________________________

Bob

[Piotr Szotkowski]

Using Civicrm 3.0.1
I have done a test (export & import) just before setting civicrm to multi language and it works fine.

I set civicrm to multi language - export - then import to empty database and I get the following error.

#1050 - Table 'civicrm_address_en_US' already exists

This means that the import code does not drop views before trying to recreate them.

If the export was full, you can try dropping and recreating the database, then import.

[bobd]

I am using phpMyAdmin with the default settings as my interface to mysql.
I created a new empty database and then imported and got the error(previous post).

[mathieu]

If I may hijack this thread, since It's about multi-language imports: i ran into issues while mysql was trying to import the trigger statements. The bug only happens with sql exports generated by phpmyadmin (latest stable, 2.11.8 ). Exporting using the mysql command line works fine.

Here is the error when I try to import a dump generated by phpmyadmin:

ERROR 1146 (42S02) at line 1899: Table 'civimltest.v' doesn't exist

The code which caused this is:

CREATE TRIGGER `civimltest`.`civicrm_contact_before_insert` BEFORE INSERT ON `civimltest`.`civicrm_contact`
 FOR EACH ROW BEGIN IF NEW.first_name_fr_FR IS NOT NULL THEN SET NEW.first_name_en_US = NEW.first_name_fr_FR; ELSEIF NEW.first_name_en_US IS NOT NULL THEN SET [...]
THEN SELECT v.label_en_US INTO @suffix FROM civicrm_option_value v JOIN civicrm_option_group g ON (v.option_group_id = g.id) [...]

Full query here: http://paste2.org/p/618954

When using mysqldump, it uses the following form:
/*!50003 SET @SAVE_SQL_MODE=@@SQL_MODE*/;
DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`myuser`@`%` */ /*!50003 TRIGGER `civicrm_contact_before_insert` BEFORE INSERT ON `civicrm_contact`  [...]

After import, when I check "show triggers", it does list me the triggers, so it seems to import correctly.

It's not a major issue since our system generates daily backups using the command line tool, I just wanted to report the issue in case others also run into it.

EDIT: in fact, it seems like a known bug which has been fixed in the 3.x branch of phpmyadmin: http://sourceforge.net/tracker/index.php?func=detail&aid=2825919&group_id=23067&atid=377408

matt (bgm on #civicrm)

[bobd]

It did worked when I used the backup created with the AlternC interface. I reported in this form because all I knew was that this worked before enabling multi-language and did not worked after I enabled multi-language.

Thanks for resolving this.