Author Topic: Export all CiviCRM Joomla members with passwords & import in Drupal CiviCRM (Read 5626 times)

Hershel · October 22, 2012, 09:50:03 am

I would use:

mysqldump -u username -p --no-create-info databasename > datafile.sql

Gerhard · October 22, 2012, 10:30:54 am

thx, I tried:

1) 3.4.8 server
mysqldump -h127.0.0.3 -udb360538_15 -p --no-create-info db360538_15 > /kunden/360538_50670/drupaldev/db360538_15_dataonly.sql

2) Live Server
mysql -h127.0.0.3 -udb373540_2 db373540_2 -p < /kunden/373540_68161/webseiten/backup/db360538_15_dataonly.sql

ERROR 1062 (23000) at line 64: Duplicate entry '1' for key 'PRIMARY'

Line 64 seems to be:
INSERT INTO `civicrm_action_mapping` VALUES (1,'civicrm_activity','activity_type','Type','activity_status','Status','activity_date_time',NULL,'activity_contacts');

this is new :-)

Ciao -G

Hershel · October 22, 2012, 10:50:57 am

Looks like your live DB wasn't empty. You must insure that it has ONLY the structure of a clean 3.4.8. It must have no data in it.

Gerhard · October 22, 2012, 12:06:08 pm

yes, could be (making 100 dumpss per day on SSH confuese me...:-)

Ok, now I have started again but now I get:
mysql -h127.0.0.3 -udb373540_2 db373540_2 -p < /kunden/373540_68161/webseiten/backup/db360538_15_dataonly.sql
ERROR 1136 (21S01) at line 15549: Column count doesn't match value count at row 1

?

the dump was made from the 3.4.8 but I have no clue why this happens...

Thx -G

Hershel · October 22, 2012, 01:05:02 pm

You will have to examine it manually and determine the extra field. I had a case recently where I had to manually add a "note" field to civicrm_contribution in order to complete the process you are working on. Mine was earlier versions (ancient even) but anyhow this issue came up.

My "bad" DB had a notes field in civicrm_contribution which didn't exist in the good DB, so I added it manually before I imported my data.

You must figure out what field is problematic. It may not be exactly as I wrote of course, but anyhow, poke around....

Gerhard · October 23, 2012, 01:37:40 am

First of all: thx for not letting me alone in this battle :-) appreciate!
secondly regarding your experience and suggestion with this kind of error in an import:

my error messages says: "at line 15549: Column count doesn't match value count at row 1"

any tip how to get exactly to that line on ssh? My IDE (Netbeans with line count) rejects to open such a huge mysql dump file, my other editors on Windows doesn't have line count.

is vi capable of getting me to that exact line? (vi command editing is not (yet) my cup of tea, to say it somehow British this morning:-)

Thx -G

Hershel · October 23, 2012, 04:31:45 am

Quote from: Gerhard on October 23, 2012, 01:37:40 am

First of all: thx for not letting me alone in this battle :-) appreciate!

Happy to help.

Quote from: Gerhard on October 23, 2012, 01:37:40 am

is vi capable of getting me to that exact line?

Yes, type 15549j. See here: http://www.lagmonster.org/docs/vi.html under Motion. Letter j means go down one line. 15549j means go down 15549 lines.

Gerhard · October 23, 2012, 06:06:02 am

great, thx!
I found in line 15549 the 'civicrm_report_instance' table.

comparing the two (data only dump and civicrm 3.4.8 structure only) I found out about a difference here in their respective 'civicrm_report_instance' table:

version 3.4.8 structure only (original civicrm.mysql from sourceforge)
(16 fields):
id
domain_id
title
report_id
name
args
description
permission
form_values
is_active
email_subject
email_to tex
email_cc text
header text
footer text
navigation_id

Table in mysql I use for the Data only import from my 3.4.8 working civicrm (coming from my "old" Jommla civi mysqql)
(14 fields):
id
domain_id
title
report_id
description
permission
form_values
is_active
email_subject
email_to
email_cc
header
footer
navigation_id

what do I have to do here to fix this (Column count doesn't match value count at row 1)?
Add the 2 fields
name
args
to my data only dump???

I must admit this kind of work is totaly new for me...:-)

Thx again -G

Hershel · October 23, 2012, 06:50:55 am

I'm not 100% sure, but you could try that. I would also recommend you install phpMyAdmin if you can. It makes it a lot easier to examine/edit your DBs and also to make dumps.

Gerhard · October 23, 2012, 07:08:35 am

Quote

I'm not 100% sure, but you could try that.

(I am relieved not being the only one who lacks experience in this case)

and yes I have PHPmyAdmin runing and found out about the fields there.
but for civi crm I often get a server script time out when importing back dumps and on ssh I can avoid that.

anyway I will play around with adjusting the two diff table....

Thx -G

Gerhard · October 31, 2012, 03:46:39 am

ok, this is a short wrap-up of what I did so far and what works now (and what doesn't):
imported the joomla 3.4.8 db in an empty mysql
did the manual changes as told in "Migrating from Joomla to Drupal - CiviCRM 4.2 Documentation - CiviCRM Wiki" (http://wiki.civicrm.org/confluence/display/CRMDOC42/Migrating+from+Joomla+to+Drupal)
deleted all jos_ tables (joomla), so it is a civicrm only database
did follow this here:
http://wiki.civicrm.org/confluence/display/CRMDOC42/Ensuring+Schema+Integrity+on+Upgrades

eported data only (disable "Structure") with phpmysql of the joomla sql
new empty mysql
imported 4.1.0 (upgrade from 3.4.8 directly to the current 4.2.x doesn't work!) sites/all/modules/civicrm/sql/civicrm.mysql
imported this here: Take a STRUCTURE only dump for all your custom data tables (civicrm_value_* tables or custom_value_* tables) of your database. from http://wiki.civicrm.org/confluence/display/CRMDOC42/Ensuring+Schema+Integrity+on+Upgrades

civicrm_pcp_block and civicrm_pcp don't get imported (when coming from Joomla!), I wrote down the values of civicrm_pcp_block table (viewing the content of this table in phpmysql), civicrm_pcp was empty anyway, deleted these two tables in my joomla mysql (for import data only) before importing this data into the empty structure only 4.1.0 mysql.
everything went through...
then I opened the table civicrm_pcp_block in the new 4.1.0 mysql and entered the content from my previous joomla mysqql for this table manually with phphmyadmin).
in my Drupal 7 I took a clean 4.1.0 civicrmsettings.php and connected this to my new 4.1.0 mysql with the joomla data in it (in my case I installed a fresh 4.1.0 on another D7 site only to get that clean 4.1.0 civicrmsettings.php (!)
then I imported all users with the Drupal Joomla module from my joomla mysql with the jos_ files in it
now former Joomla users can login to the new Drupal site with their old Joomla username and password (after the import I received a few hundred error messages from the Joomla module, but ignored them (!)

then I enabled
CiviMember Roles Sync in D7, added a new Drupal Role ("Members") and matched all membership-types from the old Joomla civicrm to this role plus the
Current Codes & Expired Codes values clicks.

Now I have it somehow running.

probably I have made even more steps but I forgot what I did in detail here & there

Now to the daily tasks:

Then I took our "old" contribution page "profile" for Membership sign-up HTML snippet and inserted this in a new Drupal node.
Now this form initially asks when you go to it online soemthing like:
if you already have an useraccount on the Site (Drupal), if so, pls. sign in first.

Great, then after signing in the form redirects to the admin page of the profiles in CiviCRM and you get the CiviCRM page:

You don't have permission to access this page. end.

Ok, I open up a new thread for that... ;-)

Goood luck "fellow sufferers"
-G

Hershel · October 31, 2012, 04:30:53 am

Quote from: Gerhard on October 31, 2012, 03:46:39 am

Goood luck "fellow sufferers"

LOL

While misery does love company, CiviCRM can also be fun too.

Gerhard · October 31, 2012, 07:20:34 am

Quote

While misery does love company, CiviCRM can also be fun too.

well maybe when I am through with this migration... (fun)

CiviCRM Community Forums (archive)

News:

Author Topic: Export all CiviCRM Joomla members with passwords & import in Drupal CiviCRM (Read 5626 times)

Hershel

Re: Export all CiviCRM Joomla members with passwords & import in Drupal CiviCRM

Gerhard

Re: Export all CiviCRM Joomla members with passwords & import in Drupal CiviCRM

Hershel

Re: Export all CiviCRM Joomla members with passwords & import in Drupal CiviCRM

Gerhard

Re: Export all CiviCRM Joomla members with passwords & import in Drupal CiviCRM

Hershel

Re: Export all CiviCRM Joomla members with passwords & import in Drupal CiviCRM

Gerhard

Re: Export all CiviCRM Joomla members with passwords & import in Drupal CiviCRM

Hershel

Re: Export all CiviCRM Joomla members with passwords & import in Drupal CiviCRM

Gerhard

Re: Export all CiviCRM Joomla members with passwords & import in Drupal CiviCRM

Hershel

Re: Export all CiviCRM Joomla members with passwords & import in Drupal CiviCRM

Gerhard

Re: Export all CiviCRM Joomla members with passwords & import in Drupal CiviCRM

Gerhard

Re: Export all CiviCRM Joomla members with passwords & import in Drupal CiviCRM

Hershel

Re: Export all CiviCRM Joomla members with passwords & import in Drupal CiviCRM

Gerhard

Re: Export all CiviCRM Joomla members with passwords & import in Drupal CiviCRM