Rebuild db for upgrade question

[FatherShawn]

In the documentation Upgrade Joomla Sites to 2.0 I need some further clarification of step 2, Rebuild your 1.9 Database to Ensure Schema Integrity.

My civicrm tables are installed in the common database used by Joomla! The instructions read to me as if the civicrm tables are in their own db.  Is that an option?  Is it preferred?  I don't remember where to specify that db if so.

[Donald Lobo]

thats a great question and i'm surprised it has not come up before

not sure what the joomla users do and how they rebuild the db for step 2. I'll allow brian to answer and hopefully update the docs

lobo

[lcdweb]

As I understand it, you do NOT need to actually install Joomla in order to do the schema check (I didn't). You can simply run the SQL file referenced in the upgrade page to build your schema, import your existing data, and then use that "corrected" schema moving forward. I don't know the ins-and-outs of why that step is needed, but I'm guessing that it's possible there's a field or two, or maybe an index setting, that could potentially be missing from existing 1.9 installs?

To answer Father Shawn's points more specifically --

No, you don't want to run your live CiviCRM tables in a different database from your Joomla tables. They should all be in one. At least, that's the default setup, and I think you'll create a headache without need if you break it into a separate database. However, for this temporary schema fix, you will want to create a temp db in order to create the schema and import your live data, then export the "fixed" schema -- either create it on a testing server or you could just create a new temp db on your production server.

When you export data from the temp-schema-fix-db, you probably want to include the drop tables function so when you import it back into your live db, it clears out the "old" schema.

-Brian

PS - keep lots of backups

[Dave Greenberg]

Brian - It seems like our docs for doing this part of the upgrade need fixing. If you can post your approach here as a step-by-step procedure - I'll take that and format it / integrate it with the wiki doc. (It might also help us think about / come up w/ some ways to make it easier once we understand a working approach better :-) ). Thx!

[FatherShawn]

Thanks for helping work through this.  I'm planning to upgrade after an event completes in 10 days as I don't want to obsolete registration urls that I've used in emails...

As I read Brian's reply the documentation line the reads:
shell > mysqldump -u crm_db_username -pcrm_db_password -c -e -n -t civicrm19 > dumpFile

Should be
shell > mysqldump -u Joomla_db_username -pJoomla_db_password -c -e -n -t Joomla_database_name > dumpFile

In other words, since the CiviCRM tables are stored in the Joomla database, its the Joomla database that we want to dump and reimport? 

Also, I usually just use the -p flag alone when working with mysql in the shell. Is there anything other than stylistic differences in specifying the password with the flag?

[FatherShawn]

OK, I've given this process a trial run, and I have a comment and a couple of questions.

First, I used my proposed change:

shell > mysqldump -u Joomla_db_username -p -c -e -n -t Joomla_database_name > dumpFile

I gave the password at the prompt and the dump completed without a hitch.

Then I executed
shell > mysql -u crm_db_username -p civicrm19Fix < dumpFile

Which generated an error message at line 900-something complaining that table jos_banner does not exist.  "Bother!" I thought.  But wait!  The civicrm19Fix temp db is only civiCRM.  I checked, and yes jos_banner is the first Joomla table.  I checked the fixdb and it now had data, so I hoped that the process worked and went on. Ending with an error was uncomfortable, but I was backed-up so ...

I went through the upgrade steps: uninstalled, edited configure.php, installed, pointed to the upgrade url and clicked through all the steps. The final step redirects to an invalid url on the wiki.

The activity history step choked, indicating that there was 3rd party activity data and that this would be preserved in an activity history table.  I don't have any 3rd party civicrm modules and checking the indicated table the source of the activity was civiCRM core - email sent.

I've exported that activity history table incase it's helpful for debug and rolled the site back to the pre-upgrade backup. 

I expected the upgrade scripts accessed in Step 7 to ask me for the name of the fix database generated in step 2.  As far as I can tell, the fix database is never used?!?  Shouldn't that data in the fix database be loaded in place of the civicrm tables in the Joomla database at some point?

After we've talked this through, I'll give it another shot!

[Donald Lobo]

here's a crack at a solution with a combined db. Make backups before you use. Lets call the original db, civicrmDB

shell> mysqldump -u crm_db_username -pcrm_db_password -c -e -n -t civicrmDB > dumpFile

shell> edit the dumpFile and delete ALL the joomla / drupal / misc tables. i.e. ALL tables that do not start with a civicrm_ prefix. Save your changes back to the dumpFile

shell> mysql -u crm_db_username -pcrm_db_password civicrmDB < civicrm_41.mysql

shell> mysql -u crm_db_username -pcrm_db_password civicrmDB < dumpFile

lobo

[FatherShawn]

OK, I think that should go more like this then:
shell> mysqldump -u crm_db_username -pcrm_db_password -c -e -n -t JoomlaDB > JoomlaDump.sql
shell> cp JoomlaDump.sql civicrmDB.sql

Now I'd work on the copy civicrmDB.sql so that I have JoomlaDump.sql as a backup.  Now, there's a lot of stuff between the end of the civicrm tables and the end of file.  The file ends like this:
*!40000 ALTER TABLE `jos_weblinks` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


So I'm guessing that I'd want to delete everything up to and including line 40000?

Then load the new schema directly into the combined JoomlaDB
mysql -u crm_db_username -pcrm_db_password JoomlaDB < civicrm_41.mysql

Then reload the CiviCRM data into the JoomlaDB
shell> mysql -u crm_db_username -pcrm_db_password civicrmDB < civicrmDB.sql

Is this what you're thinking?

[FatherShawn]

I have two ideas for a cleaner process.

Idea A

How difficult is it to move tables from one db to another?  Could one:

• Create a fixDB
• move the civicrm tables to the fixDB
• dump the fixDB
• clear the fixDB
• load civicrm_41.mysql into the fixDB
• load the dump back into the fixDB
• move the civicrm tables back to the JoomlaDB

Idea B

Could an sql procedure file be written to select and export only the civiCRM tables?  I think that using and editor to wipe a huge hunk of the sqldump might be more than a bit scary for many users...

[mcsmom]

It seems like the instructions are missing some steps.

What confusing to me is, for example, the instructions go through this whole process of creating a database with just the civicrm tables and then never actually tell you to do anything with it. You make it and then go run the installer on the existing database. Shouldn't you want to change the database in some way prior to installing? If you're using a separate database, which is totally fine and a nice feature, shouldn't you change the database name somewhere?

Also it says you should have run this when upgrading to 1.9 but it never said to do  this on the 1.8 to 1.9 instructions (which I just  followed immediately prior to attempting 1.9 to 2.0.2). It just said to run the civicrm_upgradedb_v1.8_v1.9_41.sql. 

Also, is there a reason this has to be done in shell and can't be done in phpmyadmin?

[mcsmom]

Where is says this in step 7

Point your web browser to the following URL:
http://<your_joomla_home>/administrator/index2.php?option=com_civicrm&option=com_civicrm&task=civicrm/upgrade&reset=1

Is option=com_civicrm supposed to be there twice?

[Dave Greenberg]

Is option=com_civicrm supposed to be there twice?

No, it's not. I've fixed that. However we really need to settle on a solid set of steps to deal w/ the schema rebuild in the "combined DB" situation. I think Father Shawn (with some input from Lobo) is close to having a decent draft set of steps. Hopefully he will update the wiki w/ these once they've got a good working approach.

What confusing to me is, for example, the instructions go through this whole process of creating a database with just the civicrm tables and then never actually tell you to do anything with it. You make it and then go run the installer on the existing database. Shouldn't you want to change the database in some way prior to installing? If you're using a separate database, which is totally fine and a nice feature, shouldn't you change the database name somewhere?

Also it says you should have run this when upgrading to 1.9 but it never said to do  this on the 1.8 to 1.9 instructions (which I just  followed immediately prior to attempting 1.9 to 2.0.2). It just said to run the civicrm_upgradedb_v1.8_v1.9_41.sql. 

Also, is there a reason this has to be done in shell and can't be done in phpmyadmin?

Some background ... The upgrade to 2.0 is the first one that is controlled by a PHP script - rather than a simple single MySQL script. AND, because of the numerous schema changes - we need to make sure that we're dealing with an intact 1.9 schema - including expected foreign keys - prior to running the upgrade. Hence the steps about rebuilding the DB to ensure schema integrity - which are the ones that definitely need improvement / correction.

I think all the steps for that process can be done via phpMyAdmin. It would be good to get instructions for that in parallel in the doc once the basic steps are clearer.

[mcsmom]

Yeah, one idea might be to just put the link to the rebuild script right in the information page that shows after a successful install.

Here's where I am, kind of making things up as I go and using phpMyAdmin.(assuming you backed up the whole database  already)

1. From the existing Joomla database export all of the civicrm tables, data only, foreign keys disabled.
2, Create a blank database. (civic19fix)
3. Run civicrm_41.mysql
4. Import the data from the joomla database tables.
5. Drop the civicrm tables from the Joomla database
6. Import the civic19fix data.

It's not really necessary to have it in a separate database, you could just go to step 5 and then do step 3 and import the data from step 1  back.


That would be:
1. From the existing Joomla database export all of the civicrm tables, data only, foreign keys disabled.
2. Drop the civicrm tables from the Joomla database
3. Run civicrm_41.mysql to create the new schema
4. Import the backuped data.

[FatherShawn]

mcsmom-

That's my "idea A" above.  Did it work?

[mcsmom]

Yes, it's the same idea except in phpmyadmin. So far I've gotten through step 6 but I'm stuck on step 7.

And yes I do think it is relatively easy to write a script for doing it.

Many hosts don't even allow shell access or only with special permission so it does not make sense to assume all users can do that.