Database errors on upgrade from 3.1.5 to 3.2

[Laryn]

I got a database error on the upgrade script (see #1 below) and I've noticed when I try to click to edit a membership it gives another error (#2 below). I'm not sure where else this will cause problems.

Can I use the database tools v1.2 which are marked as 3.0 for a 3.2 database?

Any other ways I can fix this?

Error #1 (on upgrade script):

Code: [Select]

Upgrade CiviCRM to Version 3.2.0
Sorry. A non-recoverable error has occurred.

DB Error: no such field

Database Error Code: Unknown column 'is_reserved' in 'field list', 1054

Return to home page.

Error Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => INSERT INTO `civicrm_dashboard`
    ( `domain_id`, `label`, `url`, `content`, `permission`, `permission_operator`, `column_no`, `is_minimized`, `is_active`, `weight`, `created_date`, `is_fullscreen`, `is_reserved`)
    VALUES
    ( @domainID, 'My Cases', 'civicrm/dashlet/myCases&reset=1&snippet=4', NULL, 'access my cases and activities', NULL , 0, 0, 1, 2, NULL, 1, 1),
    ( @domainID, 'All Cases', 'civicrm/dashlet/allCases&reset=1&snippet=4', NULL, 'access all cases and activities', NULL , 0, 0, 1, 3, NULL, 1, 1) [nativecode=1054 ** Unknown column 'is_reserved' in 'field list']
    [type] => DB_Error
    [user_info] => INSERT INTO `civicrm_dashboard`
    ( `domain_id`, `label`, `url`, `content`, `permission`, `permission_operator`, `column_no`, `is_minimized`, `is_active`, `weight`, `created_date`, `is_fullscreen`, `is_reserved`)
    VALUES
    ( @domainID, 'My Cases', 'civicrm/dashlet/myCases&reset=1&snippet=4', NULL, 'access my cases and activities', NULL , 0, 0, 1, 2, NULL, 1, 1),
    ( @domainID, 'All Cases', 'civicrm/dashlet/allCases&reset=1&snippet=4', NULL, 'access all cases and activities', NULL , 0, 0, 1, 3, NULL, 1, 1) [nativecode=1054 ** Unknown column 'is_reserved' in 'field list']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO `civicrm_dashboard`
    ( `domain_id`, `label`, `url`, `content`, `permission`, `permission_operator`, `column_no`, `is_minimized`, `is_active`, `weight`, `created_date`, `is_fullscreen`, `is_reserved`)
    VALUES
    ( @domainID, 'My Cases', 'civicrm/dashlet/myCases&reset=1&snippet=4', NULL, 'access my cases and activities', NULL , 0, 0, 1, 2, NULL, 1, 1),
    ( @domainID, 'All Cases', 'civicrm/dashlet/allCases&reset=1&snippet=4', NULL, 'access all cases and activities', NULL , 0, 0, 1, 3, NULL, 1, 1) [nativecode=1054 ** Unknown column 'is_reserved' in 'field list']"]
)

Error #2 (on "Edit Membership" on a contact)

Code: [Select]

Sorry. A non-recoverable error has occurred.

DB Error: no such field

Database Error Code: Unknown column 'label' in 'field list', 1054

Return to home page.

Error Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => SELECT  id, label 
 FROM civicrm_membership_status
 
 WHERE (  civicrm_membership_status.is_active = 1 ) 
 
 
 ORDER BY weight 
 [nativecode=1054 ** Unknown column 'label' in 'field list']
    [type] => DB_Error
    [user_info] => SELECT  id, label 
 FROM civicrm_membership_status
 
 WHERE (  civicrm_membership_status.is_active = 1 ) 
 
 
 ORDER BY weight 
 [nativecode=1054 ** Unknown column 'label' in 'field list']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT  id, label 
 FROM civicrm_membership_status
 
 WHERE (  civicrm_membership_status.is_active = 1 ) 
 
 
 ORDER BY weight 
 [nativecode=1054 ** Unknown column 'label' in 'field list']"]
)
[/code][/code]

[Kurund Jalmi]

Dashboard was added in 3.1.x, so if you are already on 3.1.5 then you should not get below errors.

Can I use the database tools v1.2 which are marked as 3.0 for a 3.2 database?

There are lot of db changes to CiviCRM since v3.0, so that tool might not work with 3.2

So can you check your civicrm_domain table for version?

Kurund

[Laryn]

Interesting. The version in the database is recorded as:  3.2.beta1.upgrade

Is it possible that there was an error when updating to 3.1.5 which is carrying through? If so, how can I solve it?

[Kurund Jalmi]

1. I would suggest restore back to your backup.
2. Make sure you version in civicrm_domain is correct.
3. Retry upgrade.

If that does not solves the problem may be some problem with your existing db.

Hth
Kurund

[Laryn]

I'll try to load and upgrade the database again.

Side Question: Our database is about 900 megs... Can we compress that or remove extraneous information somehow? It seems a little out of control.

EDIT: The backup db does list version as 3.1.5

[Kurund Jalmi]

Our database is about 900 megs... Can we compress that or remove extraneous information somehow? It seems a little out of control.

I don't think you can compress or remove any data from civicrm tables.

Kurund

[Donald Lobo]

can u check your tables and see which take the most space

there are a couple of "logging" tables (mailing events, membership log) which store old historical information. if this information is not important to you, u can potentially delete the older records

in general, we do need to build a better archival / logging / delete system to get rid of historical information when the db gets big

lobo

[Laryn]

Lobo, here are the largest tables:

• civicrm_log: 752 MB
• civicrm_membership_log: 196 MB

[Donald Lobo]

you can delete most of the records from civicrm_log. you will lose the date/time history of when a contact was changed, but might not be important in your case

the same for membership_log

lobo

[Laryn]

Okay, I dumped over 5 million records from civicrm_log so we'll see how it goes from here. Maybe it'll speed things up a bit, too.

To come back full circle to the earlier conversation, we had some issues reloading the old database (possibly due to cached files after the upgrade) but I decided that if there were errors previously that went unchecked, the old database already contained them. I went through the upgrade scripts and tried my best to update the database manually (having to skip some items that contained template code). It was strange how many of them had some SQL that seemed to have been run already and others that didn't.

I managed to get the databases updated to the point where things seem to be functioning again on version 3.2, so I think we'll just go forward from here...

Thanks to everyone for the help!