Author Topic: multilingual database corruption or fail (Read 2443 times)

jimmy_row · July 29, 2013, 08:00:57 pm

I have a site that ran well for a time as a multi-lingual site with three languages. Somewhere along the line, however, something got messed up and I am no longer able to add another language or revert to single language - I get an unexplained DB error. I saw something elsewhere that suggested that some tables that were supposed to be changed upon the addition of a language didn't get updated, and it looks like this is the case when I view the schema in phpMyadmin. On a completely fresh install of the latest civicrm 4.3.5, the problem disappears for new sites. Therefore, I suspect it had something to do with 4.2.8 that I was using before, I think it may have been a beta version or something.

I'm looking for help, either to a) manually revert everything to single language and start fresh, without losing my data; or 2) fixing the language problem without reverting.

FYI, here is some output that occurs after I try to revert to single language:

Initialization Error

Array
(
[callback] => Array
(

=> CRM_Core_Error

Code: [Select]

 => -18
    [message] => DB Error: no such table
    [mode] => 16
    [debug_info] => 
SELECT
   option_value.id          as id,
   option_value.label       as label,
   option_value.value       as value,
   option_value.name        as name,
   option_value.description as description,
   option_value.weight      as weight,
   option_value.is_active   as is_active,
   option_value.is_default  as is_default
FROM
   civicrm_option_value_fr_CA  as option_value,
   civicrm_option_group_fr_CA  as option_group  WHERE option_group.id = option_value.option_group_id  AND option_group.name = 'languages' ORDER BY weight [nativecode=1146 ** Table 'civicalacs.civicrm_option_value_fr_CA' doesn't exist]
    [type] => DB_Error
    [user_info] => 
SELECT
   option_value.id          as id,
   option_value.label       as label,
   option_value.value       as value,
   option_value.name        as name,
   option_value.description as description,
   option_value.weight      as weight,
   option_value.is_active   as is_active,
   option_value.is_default  as is_default
FROM
   civicrm_option_value_fr_CA  as option_value,
   civicrm_option_group_fr_CA  as option_group  WHERE option_group.id = option_value.option_group_id  AND option_group.name = 'languages' ORDER BY weight [nativecode=1146 ** Table 'civicalacs.civicrm_option_value_fr_CA' doesn't exist]
    [to_string] => [db_error: message="DB Error: no such table" code=-18 mode=callback callback=CRM_Core_Error::simpleHandler prefix="" info="
SELECT
   option_value.id          as id,
   option_value.label       as label,
   option_value.value       as value,
   option_value.name        as name,
   option_value.description as description,
   option_value.weight      as weight,
   option_value.is_active   as is_active,
   option_value.is_default  as is_default
FROM
   civicrm_option_value_fr_CA  as option_value,
   civicrm_option_group_fr_CA  as option_group  WHERE option_group.id = option_value.option_group_id  AND option_group.name = 'languages' ORDER BY weight [nativecode=1146 ** Table 'civicalacs.civicrm_option_value_fr_CA' doesn't exist]"]
)

Hershel · July 30, 2013, 05:25:31 am

My guess is that the table civicrm_option_value_fr_CA is missing. Perhaps you can make a fresh install elsewhere of your version (4.2.8 it seems) and add that language and then copy the table over...

mathieu · July 30, 2013, 07:46:49 am

civicrm_option_value_fr_CA is a MySQL "View", not a regular table.

Can you check if you have other views, such as civicrm_event_fr_CA?

One very annoying thing about MySQL export/import and Views, is that by default it exports who the 'definer' of the view is. This needs to be removed from the sql dump if you are moving to another system, otherwise the import on the new system will complain about permissions (and one should not import as root, since it won't complain about permissions, but then usage will fail, which is another potential cause here).

For example:

Code: [Select]

mysql> show create view civicrm_option_value_fr_CA;
CREATE ALGORITHM=UNDEFINED DEFINER=`exampleuser`@`localhost` SQL SECURITY DEFINER
VIEW `civicrm_option_value_fr_CA` AS select
`civicrm_option_value`.`id` AS `id`,
`civicrm_option_value`.`option_group_id` AS `option_group_id`,
`civicrm_option_value`.`value` AS `value`,
`civicrm_option_value`.`name` AS `name`,
`civicrm_option_value`.`grouping` AS `grouping`,
`civicrm_option_value`.`filter` AS `filter`,
`civicrm_option_value`.`is_default` AS `is_default`,
`civicrm_option_value`.`weight` AS `weight`,
`civicrm_option_value`.`is_optgroup` AS `is_optgroup`,
`civicrm_option_value`.`is_reserved` AS `is_reserved`,
`civicrm_option_value`.`is_active` AS `is_active`,
`civicrm_option_value`.`component_id` AS `component_id`,
`civicrm_option_value`.`domain_id` AS `domain_id`,
`civicrm_option_value`.`visibility_id` AS `visibility_id`,
`civicrm_option_value`.`label_fr_CA` AS `label`,
`civicrm_option_value`.`description_fr_CA` AS `description` from `civicrm_option_value`

In the above, the "DEFINER=`exampleuser`@`localhost` SQL SECURITY DEFINER" must be removed when importing views. Same goes for Triggers. NB: above example is from a CiviCRM 4.3.5 install.

For reference: http://dev.mysql.com/doc/refman/5.5/en/create-view.html

jimmy_row · July 30, 2013, 11:50:28 am

Yes, I tried the first solution and it failed -- importing the old db just messed up the language settings.

It sounds like I could avoid this problem making a fresh install, enabling the languages, then doing an sql dump properly from the old db, and importing it without 'definer' and 'trigger' (not sure what this means), and not as root. I had been importing as root.

Does that sound right? If so, any tips on how to construct the sql dump properly? I'm not very familiar with it....

Hershel · July 30, 2013, 12:32:44 pm

Trying to do a data transfer via SQL is a bit tricky. It can be done but it's not so easy.

I think you would want to make a new DB, and then TRUNCATE all tables and then use phpMyAdmin to dump "data only" from all tables (you can also make such a dump from MySQL CLI) and then import that. This may not work so easily, but you can try.

jimmy_row · July 30, 2013, 01:21:06 pm

OK, what's Truncate and how do I do that? I'm using phpmyadmin to import, but not to export (different servers for my test and production sites). Any chance I could do the data only dump via webmin?

Hershel · July 30, 2013, 01:35:34 pm

Quote from: jimmy_row on July 30, 2013, 01:21:06 pm

OK, what's Truncate and how do I do that?

http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html

Quote from: jimmy_row on July 30, 2013, 01:21:06 pm

Any chance I could do the data only dump via webmin?

Don't know webmin but I think you can not.

jimmy_row · July 30, 2013, 07:17:56 pm

oh god, you weren't kidding when you said it may not work easily. Managed to use phpmyadmin to truncate all the tables, but had to set foreign key constraint to 0. However, when I go to import the data dump, I run into foreign key constraint issues again. Here is the actual feedback from mysql:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`civicalacs`.`civicrm_activity`, CONSTRAINT `FK_civicrm_activity_source_contact_id` FOREIGN KEY (`source_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL)

Hershel · July 31, 2013, 04:46:31 am

I think this is more technical and complicated than can be solved via this forum.

jimmy_row · July 31, 2013, 11:23:58 am

Well, I think I finally figured it out... Here is a step-by-step of what I did.

- create fresh install of original civicrm version (4.2.

- add desired languages
- install phpmyadmin
- select civicrm database, choose select all tables
- with all selected, select 'empty' from dropdown menu
- on confirmation page, copy sql code for truncating the ~150 tables
- go back, select 'sql' from phpmyadmin menu
- paste in sql, add "SET FOREIGN_KEY_CHECKS=0;" at beginning of each line
- run code
- export database from old site as data only
- try to import, find out foreign key constraints prevent that
- add SET FOREIGN_KEY_CHECKS=0; to beginning of SQL file, run, discover this kind of works
- new problem: some columns are too long, need to be changed in format, but no matter what can't seem to get that to work
- back to original database in phpmyadmin, discover that you can export db as data, with foreign key contraints ignored, and as REPLACE (obviating the need to truncate all tables); export data excluding problematic tables (conribution_page, and event), as well as custom profile and field tables;
- import, works well
- go back to phymyadmin for old db, export custom tables as "INSERT", works well
- go into new install of civicrm, re-add apprpriate langauges.
- now just have to rebuild the event and contribution page texts

Thanks everybody for your assistance!

JG

CiviCRM Community Forums (archive)

News:

Author Topic: multilingual database corruption or fail (Read 2443 times)

jimmy_row

multilingual database corruption or fail

Hershel

Re: multilingual database corruption or fail

mathieu

Re: multilingual database corruption or fail

jimmy_row

Re: multilingual database corruption or fail

Hershel

Re: multilingual database corruption or fail

jimmy_row

Re: multilingual database corruption or fail

Hershel

Re: multilingual database corruption or fail

jimmy_row

Re: multilingual database corruption or fail

Hershel

Re: multilingual database corruption or fail

jimmy_row

Re: multilingual database corruption or fail