CiviCRM Community Forums (archive)

I have a problem with upgrading a test copy of my site based on 2.2.9 to 3.0.2

Once the files are installed when upgrading the database with the it returns the following error:

Sorry. A non-recoverable error has occurred.

Database check failed - it looks like you have already upgraded to the latest version (v3.0.2) of the database. OR If you think this message is wrong, it is very likely that this a partially upgraded db and you will need to reload the correct db on which upgrade was never tried.

I would greatly appreciate your assistance.

Thank you in advance.

Best regards,

Angelo Luidens

I have the same problem... my civicrm_domain table shows 2.2.7, though I ran the upgrade for 3.0.2.  How do I fix it?

a fair number of these errors are "user originated errors" (i.e. the user did something with the db / did not follow instructions etc). Putting things in a transaction will not help this

lobo

1. was this the first time u tried to do the upgrade
Yes. Tried it several times after that. I have even tried it with the 3.1alpha2.

2. did u touch the database directly via SQL for anything else in the past
Except for converting to INNODB, and uploading the whole database.

3. can u check the version column in the civicrm_domain table of your db and let us know the value
2.2.9
4. what was the name of the package u installed or 2.2.9?
I have tried it with 3.0, 3.0.1, 3.0.2, and 3.1alpha1, and 3.1alpha2

All tests are on a copy of the original data on a test site.

thanx

lobo

I ran civicrm DB Tools command: "php civicrm_db_integrity.php -u username -ppassword -h hostname databasename" and got the following message:

-----------
CiviCRM DB Tools Version 1.2

Can't connect to MySQL server on localhost:
   SQLSTATE[28000] [1045] Access denied for user 'username'@'hostname' (using password: YES)
-----------

Your assistance is greatly appreciated.

Thank you in advance.

My bad.

Since, this is a public forum I changed the security credentials (username, password) and the name of the database to some generic names. Thus basically I copied the error statement and changed the username, password, database name to username, password, and databasename.

I also confirmed in civicrm.settings.php that it is pointing to the right databasename and followed your suggestion using the correct credentials and got the following results:

-----------
CiviCRM DB Tools Version 1.2

Checking database databasename
Oops, can't get table status for table civicrm_navigation
Oops, can't do query:
    SHOW COLUMNS FROM civicrm_navigation
    in civicrm_db_utils.php line 1516:
    42S02: Table 'databasename.civicrm_navigation' doesn't exist
    Call stack:
       class Utils method do_query file civicrm_db_utils.php line 1717
       class Utils method get_db_table_structure file civicrm_db_utils.php line 1437
       class Utils method db_conn file civicrm_db_integrity.php line 57

Fatal error: Call to a member function closeCursor() on a non-object in /var/www/html/civicrm_dbtools-1.2/civicrm_db_utils.php on line 1746
-----------

Your assistance to point me in the right direction is highly appreciated.

Angelo

No.  This is actually the first time I run the DB Tools.

First Attempt
I used a database version that I have applied the dbtools 1.2 with a successful integrity check.
Version of CiviCRM is 2.2.9 and the version of the dababase is also 2.2.9.

Ran the 3.0.3 installation successfully and than attempted the database upgrade with the following results:

    Sorry. A non-recoverable error has occurred.

    DB Error: already exists

    Database Error Code: Duplicate entry '10004' for key 1, 1062

    Return to home page.

Error Details:

Array
(
    [callback] => Array
        (
            

• => CRM_Core_Error
[1] => handle
        )

    

[CCode] => -5
    [message] => DB Error: already exists
    [mode] => 16
    [debug_info] => -- State / province
    -- CRM-4534 CRM-4686 CRM-4769
        INSERT INTO civicrm_state_province
            (id, name, abbreviation, country_id)
        VALUES
            (5218,  'Distrito Federal', 'DIF', 1140),
            (10004, 'Bonaire',          'BON', 1151),
            (10005, 'Curaçao',         'CUR', 1151),
            (10006, 'Saba',             'SAB', 1151),
            (10007, 'St. Eustatius',    'EUA', 1151),
            (10008, 'St. Maarten',      'SXM', 1151),
       (10009, "Jura",       "39" , 1076) [nativecode=1062 ** Duplicate entry '10004' for key 1]
    [type] => DB_Error
    [user_info] => -- State / province
    -- CRM-4534 CRM-4686 CRM-4769
        INSERT INTO civicrm_state_province
            (id, name, abbreviation, country_id)
        VALUES
            (5218,  'Distrito Federal', 'DIF', 1140),
            (10004, 'Bonaire',          'BON', 1151),
            (10005, 'Curaçao',         'CUR', 1151),
            (10006, 'Saba',             'SAB', 1151),
            (10007, 'St. Eustatius',    'EUA', 1151),
            (10008, 'St. Maarten',      'SXM', 1151),
       (10009, "Jura",       "39" , 1076) [nativecode=1062 ** Duplicate entry '10004' for key 1]
    [to_string] => [db_error: message="DB Error: already exists" code=-5 mode=callback callback=CRM_Core_Error::handle prefix="" info="-- State / province
    -- CRM-4534 CRM-4686 CRM-4769
        INSERT INTO civicrm_state_province
            (id, name, abbreviation, country_id)
        VALUES
            (5218,  'Distrito Federal', 'DIF', 1140),
            (10004, 'Bonaire',          'BON', 1151),
            (10005, 'Curaçao',         'CUR', 1151),
            (10006, 'Saba',             'SAB', 1151),
            (10007, 'St. Eustatius',    'EUA', 1151),
            (10008, 'St. Maarten',      'SXM', 1151),
       (10009, "Jura",       "39" , 1076) [nativecode=1062 ** Duplicate entry '10004' for key 1]"]
)


An integrity check afterwards give the following results:
[username][~/www/civicrm_dbtools-1.2]$ php civicrm_db_integrity.php -u username -ppassword -h localhost dababasename
CiviCRM DB Tools Version 1.2

Checking database dababasename
Got version 2.2.9 from civicrm_domain.version
Using schema from CiviCRM 2.2
Oops, table civicrm_navigation is in the database but not the schema
Oops, table civicrm_participant_status_type is in the database but not the schema
Oops, table civicrm_report_instance is in the database but not the schema
Oops, enum column civicrm_custom_field.data_type has values
    'Boolean','Country','Date','File','Float','Int','Link','Memo','Money','StateProvince','String' in the schema but values
    'Boolean','ContactReference','Country','Date','File','Float','Int','Link','Memo','Money','StateProvince','String' in the existing database
Oops, enum column civicrm_custom_field.html_type has values
    'CheckBox','File','Link','Multi-Select Country','Multi-Select State/Province','Multi-Select','Radio','RichTextEditor','Select Country','Select Date','Select State/Province','Select','Text','TextArea' in the schema but values
    'AdvMulti-Select','Autocomplete-Select','CheckBox','File','Link','Multi-Select Country','Multi-Select State/Province','Multi-Select','Radio','RichTextEditor','Select Country','Select Date','Select State/Province','Select','Text','TextArea' in the existing database
Oops, enum column civicrm_uf_field.visibility has values
    'Public User Pages and Listings','Public User Pages','User and User Admin Only' in the schema but values
    'Public Pages and Listings','Public Pages','User and User Admin Only' in the existing database
Oops, enum column civicrm_group.visibility has values
    'Public User Pages and Listings','Public User Pages','User and User Admin Only' in the schema but values
    'Public Pages','User and User Admin Only' in the existing database
Oops, extra foreign key constraint FK_civicrm_participant_status_id on
    table civicrm_participant
8 problems found in database dababasename

Second Attempt
I have removed the records "Bonaire" through "St. Maarten" from the civicrm_state_province table in the database, using PhPMyAdmin. These were added manually out of which I created a script and submitted it to you to add to be distributed with the original source in a next version.  I have also cleaned the database of any anomalies that would would cause the integrity check and the repair to report an issue (e.g. import logs).  The following was the result of the upgrade from 2.2.9 to 3.0.3 with a clean database (copy).

    Sorry. A non-recoverable error has occurred.

    DB Error: no such table

    Database Error Code: Table 'databasename.civicrm_report_instance' doesn't exist, 1146

    Return to home page.

Error Details:

Array
(
    [callback] => Array
        (
            

• => CRM_Core_Error
[1] => handle
        )

    

[CCode] => -18
    [message] => DB Error: no such table
    [mode] => 16
    [debug_info] =>
SHOW COLUMNS
FROM civicrm_report_instance
LIKE 'description'
 [nativecode=1146 ** Table 'databasename.civicrm_report_instance' doesn't exist]
    [type] => DB_Error
    [user_info] =>
SHOW COLUMNS
FROM civicrm_report_instance
LIKE 'description'
 [nativecode=1146 ** Table 'databasename.civicrm_report_instance' doesn't exist]
    [to_string] => [db_error: message="DB Error: no such table" code=-18 mode=callback callback=CRM_Core_Error::handle prefix="" info="
SHOW COLUMNS
FROM civicrm_report_instance
LIKE 'description'
 [nativecode=1146 ** Table 'sgcrm30.civicrm_report_instance' doesn't exist]"]
)

Third Attempt
Ok. So, civicrm_report_instance table is missing. I used the following script I extracted from another database to add the table with no data:

-- phpMyAdmin SQL Dump
-- version 3.1.0
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 13, 2009 at 01:12 PM
-- Server version: 5.1.37
-- PHP Version: 5.2.9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `databasename`
--

-- --------------------------------------------------------

--
-- Table structure for table `civicrm_report_instance`
--
-- Creation: Oct 03, 2009 at 05:54 PM
--

DROP TABLE IF EXISTS `civicrm_report_instance`;
CREATE TABLE IF NOT EXISTS `civicrm_report_instance` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Report Instance ID',
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Report Instance Title.',
  `report_id` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'FK to civicrm_option_value for the report template',
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Report Instance description.',
  `permission` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'permission required to be able to run this instance',
  `form_values` text COLLATE utf8_unicode_ci COMMENT 'Submitted form values for this report',
  `is_active` tinyint(4) DEFAULT NULL COMMENT 'Is this entry active?',
  `email_subject` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Subject of email',
  `email_to` text COLLATE utf8_unicode_ci COMMENT 'comma-separated list of email addresses to send the report to',
  `email_cc` text COLLATE utf8_unicode_ci COMMENT 'comma-separated list of email addresses to send the report to',
  `header` text COLLATE utf8_unicode_ci COMMENT 'comma-separated list of email addresses to send the report to',
  `footer` text COLLATE utf8_unicode_ci COMMENT 'comma-separated list of email addresses to send the report to',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16 ;

Results: Upgrade from 2.2.9 to 3.0.3 was successful.

The menu appears as a long list of items and the current section displayed underneath.

The dbtools integrity check reports the following (an excerpt):

Oops, civicrm_log references civicrm_activity.id 4287
    but that id is not in table civicrm_activity
Oops, civicrm_log references civicrm_activity.id 4288
    but that id is not in table civicrm_activity
Oops, civicrm_log references civicrm_activity.id 4289
    but that id is not in table civicrm_activity
Oops, civicrm_log references civicrm_activity.id 4290
    but that id is not in table civicrm_activity
Table civicrm_openid key UI_openid UNIQUE differs from schema
Oops, extra key FK_civicrm_group_organization_group_id on table civicrm_group_organization
Table civicrm_activity_assignment key UI_activity_assignee_contact_id UNIQUE differs from schema
Table civicrm_activity_target key UI_activity_target_contact_id UNIQUE differs from schema
4265 problems found in database databasename


[username][~/www/username/civicrm_dbtools-1.2]$ php civicrm_db_repair.php -u username -ppassword -h localhost databasename
CiviCRM DB Tools Version 1.2

Repairing database databasename
Got version 3.0.3 from civicrm_domain.version
Using schema from CiviCRM 3.0
Dropping all foreign keys from all tables in the schema
Repair civicrm_savedsearch here soon
4261 rows in civicrm_log refer to rows in civicrm_activity
    that do not exist in the database.
SQL to show these rows is:
SELECT * FROM `civicrm_log`  WHERE `entity_table` = 'civicrm_activity' AND NOT ISNULL(`entity_id`) AND `entity_id` NOT IN ( SELECT id FROM `civicrm_activity` )
These rows cannot be repaired

Action: delete/no/quit [d|n|q] d

Table civicrm_openid key UI_openid UNIQUE differs from schema
Dropping incorrect key UI_openid from civicrm_openid
Adding key UI_openid to civicrm_openid
Table civicrm_activity_assignment key UI_activity_assignee_contact_id UNIQUE differs from schema
Dropping incorrect key UI_activity_assignee_contact_id from civicrm_activity_assignment
Adding key UI_activity_assignee_contact_id to civicrm_activity_assignment
Table civicrm_activity_target key UI_activity_target_contact_id UNIQUE differs from schema
Dropping incorrect key UI_activity_target_contact_id from civicrm_activity_target
Adding key UI_activity_target_contact_id to civicrm_activity_target
Adding foreign key constraints
Repair completed


[username][~/www/username/civicrm_dbtools-1.2]$ php civicrm_db_integrity.php -u username -ppassword -h localhost databasename
CiviCRM DB Tools Version 1.2

Checking database databasename
Got version 3.0.3 from civicrm_domain.version
Using schema from CiviCRM 3.0
Oops, column civicrm_cache.data is specified as
    type 'longtext' in the schema but is
    type 'text' in the existing database
1 problems found in database sgcrm30

Changed the type 'text' to 'longtext' using PhPMyAdmin in the table civicrm_cache.

Next run of the integrity check passes validation. Repair runs without a problem as well.