CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site

This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Discussion »
  • Internationalization and Localization (Moderators: Michał Mach, mathieu) »
  • multilingual database corruption or fail
Pages: [1]

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

jimmy_row

  • I’m new here
  • *
  • Posts: 23
  • Karma: 0
  • CiviCRM version: 4.4.5
  • CMS version: Drupal 7.27
  • MySQL version: 5.5.37
  • PHP version: 5.4
multilingual database corruption or fail
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
  • [1] => simpleHandler
            )

       
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

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Re: multilingual database corruption or fail
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...
CiviHosting and CiviOnline -- The CiviCRM hosting experts, since 2007

See here for the official: What to do if you think you've found a bug.

mathieu

  • Administrator
  • Ask me questions
  • *****
  • Posts: 620
  • Karma: 36
    • Work
  • CiviCRM version: 4.7
  • CMS version: Drupal
  • MySQL version: MariaDB 10
  • PHP version: 7
Re: multilingual database corruption or fail
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
CiviCamp Montréal, 29 septembre 2017 | Co-founder / consultant / turn-key CiviCRM hosting for Quebec/Canada @ SymbioTIC.coop

jimmy_row

  • I’m new here
  • *
  • Posts: 23
  • Karma: 0
  • CiviCRM version: 4.4.5
  • CMS version: Drupal 7.27
  • MySQL version: 5.5.37
  • PHP version: 5.4
Re: multilingual database corruption or fail
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

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Re: multilingual database corruption or fail
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. :)
CiviHosting and CiviOnline -- The CiviCRM hosting experts, since 2007

See here for the official: What to do if you think you've found a bug.

jimmy_row

  • I’m new here
  • *
  • Posts: 23
  • Karma: 0
  • CiviCRM version: 4.4.5
  • CMS version: Drupal 7.27
  • MySQL version: 5.5.37
  • PHP version: 5.4
Re: multilingual database corruption or fail
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

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Re: multilingual database corruption or fail
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.
CiviHosting and CiviOnline -- The CiviCRM hosting experts, since 2007

See here for the official: What to do if you think you've found a bug.

jimmy_row

  • I’m new here
  • *
  • Posts: 23
  • Karma: 0
  • CiviCRM version: 4.4.5
  • CMS version: Drupal 7.27
  • MySQL version: 5.5.37
  • PHP version: 5.4
Re: multilingual database corruption or fail
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

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Re: multilingual database corruption or fail
July 31, 2013, 04:46:31 am
I think this is more technical and complicated than can be solved via this forum. :(
CiviHosting and CiviOnline -- The CiviCRM hosting experts, since 2007

See here for the official: What to do if you think you've found a bug.

jimmy_row

  • I’m new here
  • *
  • Posts: 23
  • Karma: 0
  • CiviCRM version: 4.4.5
  • CMS version: Drupal 7.27
  • MySQL version: 5.5.37
  • PHP version: 5.4
Re: multilingual database corruption or fail
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.8)
- 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

Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Discussion »
  • Internationalization and Localization (Moderators: Michał Mach, mathieu) »
  • multilingual database corruption or fail

This forum was archived on 2017-11-26.