Details
-
Type:
Bug
-
Status: Done/Fixed
-
Priority:
Major
-
Resolution: Won't Fix
-
Affects Version/s: 2.0
-
Fix Version/s: None
-
Component/s: Core CiviCRM
-
Labels:None
Description
Hello,
I'm trying to upgrade our 1.9 to 2.0. As written in the instructions, I'm recreating the database with a clean schema from civicrm.sql and dumping and re-importing all our old data.
As I was afraid, that causes a lot of troubles.
Right now I'm trying to build an sql script to alter our database in way it can be imported in a 2.0 schema.
Till now, i have this list:
drop table accept_credit_card
drop table civicrm_acl_group;
drop table civicrm_acl_group_join;
drop table civicrm_activity_history;
drop table civicrm_activity_type
But then I'm at the civicrm_address table and that's where it becomes really painfull.
This is our current database (1.9.11960) civicrm_address table :
---------------------------------------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
---------------------------------------------------------------------------+
| id | int(10) unsigned | PRI | NULL | auto_increment | |
| location_id | int(10) unsigned | UNI | 0 | ||
| street_address | varchar(96) | YES | NULL | ||
| street_number | int(11) | YES | NULL | ||
| street_number_suffix | varchar(8) | YES | NULL | ||
| street_number_predirectional | varchar(8) | YES | NULL | ||
| street_name | varchar(64) | YES | MUL | NULL | |
| street_type | varchar(8) | YES | NULL | ||
| street_number_postdirectional | varchar(8) | YES | NULL | ||
| street_unit | varchar(16) | YES | NULL | ||
| supplemental_address_1 | varchar(96) | YES | NULL | ||
| supplemental_address_2 | varchar(96) | YES | NULL | ||
| supplemental_address_3 | varchar(96) | YES | NULL | ||
| city | varchar(64) | YES | MUL | NULL | |
| county_id | int(10) unsigned | YES | MUL | NULL | |
| state_province_id | int(10) unsigned | YES | MUL | NULL | |
| postal_code | varchar(12) | YES | NULL | ||
| postal_code_suffix | varchar(12) | YES | NULL | ||
| usps_adc | varchar(32) | YES | NULL | ||
| country_id | int(10) unsigned | YES | MUL | NULL | |
| geo_coord_id | int(10) unsigned | YES | MUL | NULL | |
| geo_code_1 | float | YES | NULL | ||
| geo_code_2 | float | YES | NULL | ||
| timezone | varchar(8) | YES | NULL | ||
| note | varchar(255) | YES | NULL |
---------------------------------------------------------------------------+
25 rows in set (0.00 sec)
And this is the clean 2.0 scheme were I should import the data into:
---------------------------------------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
---------------------------------------------------------------------------+
| id | int(10) unsigned | PRI | NULL | auto_increment | |
| contact_id | int(10) unsigned | YES | MUL | NULL | |
| location_type_id | int(10) unsigned | YES | MUL | NULL | |
| is_primary | tinyint(4) | YES | MUL | 0 | |
| is_billing | tinyint(4) | YES | MUL | 0 | |
| street_address | varchar(96) | YES | NULL | ||
| street_number | int(11) | YES | NULL | ||
| street_number_suffix | varchar(8) | YES | NULL | ||
| street_number_predirectional | varchar(8) | YES | NULL | ||
| street_name | varchar(64) | YES | MUL | NULL | |
| street_type | varchar(8) | YES | NULL | ||
| street_number_postdirectional | varchar(8) | YES | NULL | ||
| street_unit | varchar(16) | YES | NULL | ||
| supplemental_address_1 | varchar(96) | YES | NULL | ||
| supplemental_address_2 | varchar(96) | YES | NULL | ||
| supplemental_address_3 | varchar(96) | YES | NULL | ||
| city | varchar(64) | YES | MUL | NULL | |
| county_id | int(10) unsigned | YES | MUL | NULL | |
| state_province_id | int(10) unsigned | YES | MUL | NULL | |
| postal_code_suffix | varchar(12) | YES | NULL | ||
| postal_code | varchar(12) | YES | NULL | ||
| usps_adc | varchar(32) | YES | NULL | ||
| country_id | int(10) unsigned | YES | MUL | NULL | |
| geo_code_1 | double | YES | NULL | ||
| geo_code_2 | double | YES | NULL | ||
| timezone | varchar(8) | YES | NULL |
---------------------------------------------------------------------------+
26 rows in set (0.00 sec)
I admit that we have been running CiviCRM for a long time now, which means we did quite some upgrades and schema changes. It's well possible that made mistake somewhere that causes this. However, I think in that cause I should be able to find the changes back in the old upgrade scripts then. It's concerning that some fields seem to have disappeared and are nowhere in the scripts anymore...
$ civicrm/sql > grep 'geo_coord_id' upgrade => no results
$ civicrm/sql > grep note upgrade | grep address
and also the location_id in civicrm_address can be found nowhere (I do find it back in group_contact)
and the list goes on.
How can one reimport fields if they don't exist in the new database scheme? Or did I miss something and Is there a script somewhere to remove those unused fields?
Any suggestions for a fix would be welcome.