CiviCRM Community Forums (archive)

OK...my upgrade to v2.0b2 from 1.9.12432 failed at step 2. No problem I thought I'll just restore my db and run through step 2 (Rebuild your 1.9 Database to Ensure Schema Integrity) which is detailed at http://wiki.civicrm.org/confluence/display/CRMDOC/Upgrade+Joomla+Sites+to+2.0

Rebuild your 1.9 Database to Ensure Schema Integrity
   If your 1.9 database has been upgraded from a prior version - and you did not previously run these steps - you must complete the following steps in order to prevent errors during the upgrade to 2.0.

    * Create a new empty database. We'll call it civicrm19Fix for this example.
    * Load the 1.9 table structure into this new database by sourcing civicrm/sql/civicrm_41.mysql (in your existing 1.9 codebase).

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

    * Dump the data ONLY from your existing 1.9 database to a file:

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

    * Populate the new empty database with your data by sourcing the dumped file:

      shell > mysql -u crm_db_username -pcrm_db_password civicrm19Fix < dumpFile

I created a new table civicrm19Fix and granted user rights to it. However I couldn't get the scripts to run against this. Kept get 1045 access denied errors. Maybe my host has locked down shell mysql access....?

So can this be done in phpMyAdmin?

Recreating the empty schema is achieved by using the import function and pointing to the civicrm_41.mysql file. This method could be used for importing the dump file too.

But how to create the dump file? What does -c -e -n -t mean?

     -c  complete insert statements
     -e  (--extended inserts) Allows utilization of the new, much faster INSERT syntax
     -n  (--no-create-db) This option suppresses the CREATE DATABASE statements that are otherwise included in the output if the --databases or --all-databases option is given.
     -t  (--no-create-info) Do not write CREATE TABLE statements that re-create each dumped table.

So would this do the trick?

Options
          Add custom comment into header (\n splits lines) [leave blank]
          [    ] Enclose export in a transaction
          [    ] Disable foreign key checks
          SQL compatibility mode [NONE]

[    ] Structure
          [    ] Add DROP TABLE / DROP VIEW
          [    ] Add IF NOT EXISTS
          [    ] Add AUTO_INCREMENT value
          [    ] Enclose table and field names with backquotes
          [    ] Add CREATE PROCEDURE / FUNCTION
          Add into comments
          [    ] Creation/Update/Check dates
[ x ] Data
          [ x ] Complete inserts
          [ x ] Extended inserts
          Maximal length of created query [a big number??]
          [    ] Use delayed inserts
          [    ] Use ignore inserts
          [    ] Use hexadecimal for BLOB
          Export type [ INSERT ]

Trying this gives a FK error on the acl table straight off. Can I disable the FK checks and still have a valid install?

Think I'll give this a go when I've got more time...

Michal, Thanks this has got my though this part of it. I don't know why but I really struggled due to getting access denied errors. In the end I did this:

On a SSH shell I ran
mysql -u crm_db_username -pcrm_db_pword db_civicrm19Fix < civicrm_41.mysql;


from a local mySQL  prompt on my PC I ran from C:\wamp\mysql\bin
mysqldump -h domain.com -P 3306 -u crm_db_username -pcrm_db_pword -c -e -n -t db_civicrm > dumpFile.sql

Copy the dumpfile up to the root of the servers and On a SSH shell I then ran
mysql -u crm_db_username -pcrm_db_pword db_civicrm19Fix < dumpFile.sql;

now I just need to try the upgrade! fingers crossed.

Harry - If you could document this approach in a "Troubleshooting" section at the bottom of the Upgrade documentation - that would be awesome. Maybe put an anchor link like "'Don't have shell mysql access?" - in the main section...

Ok - that's cool if / when you can get to it :-)