On a PC at channelAustin, I established a new instance of CiviCRM on Ubuntu Server (8.04) and Drupal (6.13). The install has a separate civicrm database. The purpose of this is to have an environment other than our active development server for which to migrate data from Facil to CiviCRM. (See: http://groups.drupal.org/node/23535). It was recommended by several people to set up this separate environment. At this point, however, it's not clear to me what is the best way to replicate the civicrm database that is currently associated with the development site (or whether that is necessary). Here is what I did.
On the development site, I went to phpMyAdmin and downloaded the civicrm db as a civicrm_dev.sql file. Then, on the new Facil migration environment, I went to phpMyAdmin and deleted the tables in the civicrm db associated with that. Then, I imported civicrm_dev.sql. Deleting the tables and importing civicrm_dev.sql worked, but the problem I saw with that immediately was that when I clicked on any of the links on the civicrm page, the browser went to civicrm on the development server.
In other words, in IPADDRESS/drupal/civicrm/dashboard when I clicked on a civicrm link, it took me to a location under dev.channelaustin.org/civicrm/ This was obviously the wrong approach. So I put everything back as it was.
I'm not entirely sure if it is back as it was, because in every path now I'm seeing ?reset=1 at the end. And I don't remember seeing that before. Such as: IPADDRESS/civicrm/group?reset=1
So what is the correct approach? 1) Do I need to be replicating the civicrm db on the development site for the Facil migration site? 2) If so, how? 3) Or do I start with a clean civicrm db on the Facil migration site and use that clean db to import Facil data into?
I don't know, but I'm guessing that since the civicrm db on the development site already has important data, such as email addresses (including "fake" email address placeholders), that I'll need to use that civicrm version.
Any help, either from those working with CiviCRM and the Open Media Project, or only with CiviCRM, is appreciated.
Comments
I had the same issue while
I had the same issue while developing our new site. Actually planning to do the exact same thing as you, Facil to CiviCRM. Take a look at the field 'config_backend' under the 'domain' (civicrm_domain in my instance) table (I assume you only have 1 record in there, I do). Look for the wrong server entered in that record and replace with the correct one. There were 2 entries to look for IIRC. It should match what you put as your base url in civicrm.settings.php. Hope that helps.
This is a Weird CiviCRM Thing
We have hit this issue before. There is a way around the problem. Jessemyn is correct that the important table is the civicrm_domain table. The easiest thing to do when moving a civicrm site is the following:
CiviCRM will look for that entry on the domain table. When it does not find it it will look for the settings file and recreate the entry in the domain table.
Roberto
Or you can do it the right
Or you can do it the right way:) Thanks for the tip rasantiago.
Moving in the right direction
This is moving in the right direction. I also discovered that the TABLE civicrm_menu contains many references to dev.channelaustin.org and that this is what is directing me back to the civicrm instance on dev.channelaustin.org. So I found some instructions (http://wiki.civicrm.org/confluence/display/CRMDOC21/Moving+an+Existing+I...) mentioning TRUNCATE civicrm_menu and Rebuild menu with http:///civicrm/menu/rebuild?reset=1
I tried this and now getting
Sorry. A non-recoverable error has occurred
'navigation': Could not find access_callback in path tree, Could not find access_arguments in path tree, Could not find page_callback in path tree, Could not find page_arguments in path tree, Could not find is_ssl in path tree
Checked phpMyAdmin and the civicrm_menu definitely did not rebuild.
I suppose I'll do a clean re-install and try again.
Zapping config_backend
I think this is the approved way of doing it? Worked for me!
http://wiki.civicrm.org/confluence/display/CRMUPCOMING/Moving+an+Existin...
(snip) After entering the "updateConfigBackend" URL you may get an error suggesting you need to set the Config_Backend column to NULL. Please execute the following sql in your database (using mysql shell or PHPMyAdmin)
update civicrm_domain set config_backend = null;
Possible issue
CiviCRM 2.1.4 is running on dev.channelaustin.org and the new test site I created has CiviCRM 2.2.7.
I wonder home much that is going to matter in trying to move the db from 2.2.7 to 2.1.4 and whether I should upgrade 2.1.4 first.
Never Mix Version
Its like crossing the streams (Ghostbusters reference). Yes. Get everything working on the same version of CiviCRM (preferably 2.2.7)
Good instructions for upgrading CiviCRM
These instructions for upgrading from CiviCRM 2.1 to 2.2 worked well.
http://wiki.civicrm.org/confluence/display/CRMDOC/Upgrade+Drupal+Sites+t...
DBConvert Works Well But Obstacles Ahead
I downloaded and registered DBConvert for MSAccess & MySQL and it works well for exporting the Facil MSAccess (.mdb) database to MySQL. I wasn't able to find a way to add the 'facil_' prefix to all the Facil tables manually, so I imported the Facil mysql export into its own MySQL database and manually added 'facil_' prefix. It'd be good have documentation on that, especially if there's a way to do it through phpMyAdmin.
So, I dumped the Facil db with 'facil_' added to each table into another .sql file and then imported that to the existing Civicrm database. So the Civicrm db now has the civicrm tables, plus the facil tables (as per the instructions from raSANTIAGO). Was curious to see what would happen on running the first script in raSANTIAGO's sequence of scripts developed for PCM.
First obstacle is "Illegal mix of collations". Looks like CiviCRM has a database collation of utf8_unicode_ci, while the Facil export from DBConvert has a collation of utf8_general_ci.
I'm assuming that I need to convert all Facil tables from utf8_general_ci to utf8_unicode_ci to be consistent with CiviCRM. Correct?
UTF Encoding
We did not encounter this issue so I am wondering what might have happened. I am pretty sure that DBConvert can be set for UTF8 Unicode which will solve the issue. I am in Wisconsin right now and do not have a way of verifying but the documentation for DBConvert says that it does support UTF8 Unicode.
I'm now having this issue.
I'm now having this issue. Did you find a fix or workaround?
COLLATE
So I had an issue today that I got worked through. Trying to run the PCM scripts caused a cryptic error that, with digging, turned out to be due to differences in collation*. The civicrm_ data was all of type (forgive my lack of technical understanding here) utf_unicode_ci and the imported facil_ data was all utf8_general_ci. This prevented the tables from being joined or altered or whatever'ed.
The solution was to change the collation, which per the SQL step is quite simple:
http://drupal.org/node/60409
ALTER TABLE facil_tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;Except that it fails (for me) because of foreign keys. The solution was to unlink them, change the collation, and relink. Tried that, failed for me.
So I found a Q&D and free (as in beer / gratis) OSS (not sure about libre) PHP script that will change your entire database. I was apprehensive, but ran it and it worked.
Here's the item:
http://www.phoca.cz/documents/38-tools/154-how-to-change-collation-in-da...
It's a small script, throw it somewhere you can run PHP (I put it on the server). It asks you for the database host, username, password, database name, and the collation you want, and then it steps through the database table by table and even more granularly changing the collation. I was a bit concerned because it seemed to set the CHARACTER SET to binary, but CiviCRM is working and not complaining, and I figured that was easy enough to change in the script if it was wrong.
Worth checking out and adding to the arsenal.
-- Lane
*I'm still not even sure exactly what collation is, but now I know how to change it.
Awesome, I'll definately
Awesome, I'll definately keep this in mind. I solved my problem as well, with a different approach. When using dbconvert for the data transfer to mySQL, you can set the collation (or character set, or both, whatever they are). I set mine to "latin 1" and in the DB is shows up as: latin1_swedish_ci. I simply went back to my saved export from dbconvert and re-ran the import after making the change. The civi scripts then ran fine. I have no clue why this works, but it does...
I tried that, sort of
I set dbconvert to use utf8 but couldn't get it set to unicode from within dbconvert that I could see. I looked through the SQL dump file but couldn't find collation or anything like it, and wasn't as familiar with SQL as I am now. :-)
When I had a problem where I messed something up I dumped my facil_ tables and reimported the facildump.sql file, been good since then. [fingers still crossed]
I don't know what the
I don't know what the correct answer is, but my facil data (migrated by raSANTIAGO) is in the format latin1_swedish_ci and civi tables are utf8_unicode_ci. I haven't tried running the scripts however.
this is what i found too
this is what i found too . . . setting it to latin1 worked
and i did run import scripts
In DBConvert you can specify
In DBConvert you can specify an alternate table name for all the tables you are are importing. See attached.
generate queries to add "facil_" prefix to all facil tables
I found this below and modified it
ran it as SQL command in phpmyadmin
SELECT CONCAT(
'ALTER TABLE ', TABLE_NAME,
' RENAME TO facil_', TABLE_NAME, ';') AS my_queries
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database'
then ran the queries that were generated
this makes adding "facil_" prefix to all facil tables a lot easier