CiviCRM Community Forums (archive)

Alright, well I may have found a pretty tedious work around. 

Install absolutely fresh drupal installation and civicrm in fresh database with no modules, then once this is done change my civicrm.settings.php to the old civicrm database while running the upgrade script so it updates the fields.

The 3.1 upgrade script must have some incompatability with another module where civicrm won't even run the script when the fields don't line up.  Where on a fresh install. I've got views, domain access, and a whole wack of social networking modules. 

I'm attempting to upgrade from 3.0.3 to 3.1.3. I followed all the directions here: http://wiki.civicrm.org/confluence/display/CRMDOC/Upgrade+Drupal+Sites+to+3.1

However, when I get to step "8. Run the Upgrade script," I get the following error:

Sorry. A non-recoverable error has occurred.

    DB Error: no such field

    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, date_format FROM civicrm_custom_field WHERE data_type = 'Date'  [nativecode=1054 ** Unknown column 'date_format' in 'field list']
    [type] => DB_Error
    [user_info] => SELECT id, date_format FROM civicrm_custom_field WHERE data_type = 'Date'  [nativecode=1054 ** Unknown column 'date_format' 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, date_format FROM civicrm_custom_field WHERE data_type = 'Date'  [nativecode=1054 ** Unknown column 'date_format' in 'field list']"]
)Just prior to the upgrade, I installed Views, Date_API, and Calendar. However, I disabled all these modules prior to attempting the upgrade.

I next downloaded the database repair tools, and while they did find and fix a few seemingly trivial errors, this had no effect: the upgrade script still throws the same error.

Thanks in advance for any help or suggestions.

-Henry[/code]

Thank you for the helpful suggestion, Deepak!

I followed those instructions, but arriving at step 6 ("Now import your datafile.sql (taken from step1) again in the new database. ") I hit an error as follows:

ERROR 1062 (23000) at line 1160: Duplicate entry '199' for key 1
In the datafile.sql, at line 1160, I find no obvious errors though. The first record has key 199. But, it's the only one:

1160 INSERT INTO `civicrm_value_constituent_information_1` (`id`, `entity_id`, `most_important_issue_1`, `marital_status_2`, `marriage_date_3`) VALUES (199,304,NULL,NULL,NULL),(200,305,NULL,NULL,NULL),(201,306,NULL,'',NULL),(202,307,NULL,'',NULL),(203,308,NULL,''     ,NULL),(204,309,NULL,NULL,NULL),(205,310,NULL,NULL,NULL),(206,311,NULL,'',NULL),(207,312,NULL,'',NULL),(208,313,NULL,'',NULL),(209,314,NULL,NULL,NULL),(210,315,NULL,NULL,NULL),(211,316,NULL,NULL,NULL),(212,317,NULL,'',NULL),(214,319,NULL,'',NULL),(217,322,NU     LL,NULL,NULL),(218,323,NULL,NULL,NULL),(219,324,NULL,NULL,NULL),(220,325,NULL,'',NULL),(221,326,NULL,NULL,NULL),(222,327,NULL,'',NULL),(223,328,NULL,'',NULL),(224,329,NULL,NULL,NULL),(225,330,NULL,NULL,NULL),(226,331,NULL,NULL,NULL),(227,332,NULL,NULL,NULL),     (228,333,NULL,NULL,NULL),(229,334,NULL,NULL,NULL),(230,335,NULL,'',NULL),(231,336,NULL,NULL,NULL),(232,337,NULL,'',NULL),(233,338,NULL,NULL,NULL),(235,340,NULL,NULL,NULL),(236,341,NULL,NULL,NULL),(237,342,NULL,NULL,NULL),(238,343,NULL,NULL,NULL),(239,344,NUL     L,NULL,NULL),(240,345,NULL,NULL,NULL),(241,346,NULL,NULL,NULL),(242,347,NULL,NULL,NULL),(243,348,NULL,NULL,NULL),(244,349,NULL,NULL,NULL),(245,350,NULL,NULL,NULL),(246,351,NULL,NULL,NULL),(247,352,NULL,'',NULL),(248,353,NULL,NULL,NULL),(249,354,NULL,'',NULL)     ,(250,355,NULL,NULL,NULL),(251,356,NULL,NULL,NULL),(252,357,'SocJus','S',NULL),(253,358,NULL,'',NULL),(255,360,NULL,NULL,NULL),(256,361,NULL,NULL,NULL),(257,362,NULL,'',NULL),(258,363,NULL,'',NULL),(259,364,NULL,'',NULL),(260,365,NULL,NULL,NULL),(262,367,NUL     L,NULL,NULL),(263,368,NULL,'',NULL),(265,370,NULL,NULL,NULL),(266,371,NULL,NULL,NULL),(267,372,NULL,'',NULL),(269,374,NULL,NULL,NULL),(270,375,NULL,NULL,NULL),(271,376,NULL,NULL,NULL),(272,377,NULL,NULL,NULL),(273,378,NULL,NULL,NULL),(274,379,NULL,NULL,NULL)     ,(275,380,NULL,'',NULL),(276,381,NULL,'',NULL),(277,382,NULL,NULL,NULL),(278,383,NULL,NULL,NULL),(279,384,NULL,'',NULL),(280,385,NULL,NULL,NULL),(281,386,NULL,NULL,NULL),(283,388,NULL,NULL,NULL),(284,389,NULL,NULL,NULL),(285,390,NULL,NULL,NULL),(288,393,NULL     ,NULL,NULL),(289,394,NULL,NULL,NULL),(290,395,NULL,NULL,NULL),(291,396,NULL,NULL,NULL),(292,397,NULL,NULL,NULL),(293,398,NULL,NULL,NULL),(294,399,NULL,NULL,NULL),(295,400,NULL,NULL,NULL),(296,401,NULL,NULL,NULL),(297,402,NULL,NULL,NULL),(298,403,NULL,NULL,NU     LL),(299,404,NULL,NULL,NULL),(300,405,NULL,NULL,NULL),(301,406,NULL,NULL,NULL),(302,407,NULL,NULL,NULL),(303,408,NULL,NULL,NULL),(304,409,NULL,NULL,NULL),(305,410,NULL,NULL,NULL),(306,411,NULL,NULL,NULL),(307,412,NULL,NULL,NULL),(308,413,NULL,NULL,NULL),(309     ,414,NULL,NULL,NULL),(310,415,NULL,NULL,NULL),(311,416,NULL,NULL,NULL),(312,417,NULL,'',NULL),(314,419,NULL,NULL,NULL),(315,420,NULL,'',NULL),(316,421,NULL,NULL,NULL),(317,422,NULL,NULL,NULL),(318,423,NULL,NULL,NULL),(319,424,NULL,NULL,NULL),(320,425,NULL,''     ,NULL),(322,427,NULL,NULL,NULL),(323,428,NULL,NULL,NULL),(324,429,NULL,NULL,NULL),(325,430,NULL,'',NULL),(326,431,NULL,NULL,NULL),(327,432,NULL,NULL,NULL),(328,433,NULL,NULL,NULL),(329,434,NULL,NULL,NULL),(330,435,NULL,NULL,NULL),(331,436,NULL,NULL,NULL),(33     2,437,NULL,NULL,NULL),(333,438,NULL,NULL,NULL),(334,439,NULL,'',NULL),(336,441,NULL,NULL,NULL),(337,442,NULL,NULL,NULL),(338,443,NULL,'',NULL),(339,444,NULL,NULL,NULL),(340,445,NULL,NULL,NULL),(341,446,NULL,NULL,NULL),(342,447,NULL,NULL,NULL),(343,448,NULL,N     ULL,NULL),(344,449,NULL,NULL,NULL),(345,450,NULL,NULL,NULL),(346,451,NULL,NULL,NULL),(347,452,NULL,NULL,NULL),(350,455,NULL,NULL,NULL),(351,456,NULL,NULL,NULL),(352,457,NULL,NULL,NULL),(353,458,NULL,NULL,NULL),(354,459,NULL,NULL,NULL),(355,460,NULL,NULL,NULL     ),(356,461,NULL,NULL,NULL),(357,462,NULL,NULL,NULL),(358,463,NULL,NULL,NULL),(359,464,NULL,NULL,NULL),(360,465,NULL,NULL,NULL),(361,466,NULL,NULL,NULL),(362,467,NULL,NULL,NULL),(363,468,NULL,NULL,NULL),(364,469,NULL,NULL,NULL),(365,470,NULL,NULL,NULL),(366,4     71,NULL,NULL,NULL),(367,472,NULL,NULL,NULL),(368,473,NULL,NULL,NULL),(369,474,NULL,NULL,NULL),(370,475,NULL,NULL,NULL),(371,476,NULL,NULL,NULL),(372,477,NULL,NULL,NULL),(373,478,NULL,'',NULL),(374,479,NULL,NULL,NULL),(375,480,NULL,NULL,NULL),(376,481,NULL,NU     LL,NULL),(377,482,NULL,NULL,NULL),(378,483,NULL,'',NULL),(379,484,NULL,NULL,NULL),(382,487,NULL,NULL,NULL),(383,488,NULL,NULL,NULL),(384,489,NULL,'',NULL),(385,490,NULL,'',NULL),(386,493,NULL,'',NULL),(387,494,NULL,'',NULL),(388,495,NULL,'',NULL),(389,497,NU     LL,'',NULL),(390,498,NULL,'',NULL),(391,499,NULL,'',NULL),(392,500,NULL,'',NULL),(393,501,NULL,'',NULL),(394,502,NULL,'',NULL),(395,507,NULL,'',NULL),(397,510,NULL,'',NULL),(398,511,NULL,'',NULL),(399,514,NULL,'',NULL),(400,517,NULL,'',NULL),(401,518,NULL,''     ,NULL),(402,521,NULL,'',NULL),(403,522,NULL,'',NULL),(404,527,NULL,'',NULL),(405,528,NULL,'',NULL),(406,529,NULL,'',NULL),(408,533,NULL,'',NULL),(409,534,NULL,'',NULL),(410,537,NULL,'',NULL),(411,542,NULL,'',NULL),(412,544,NULL,'',NULL),(413,545,NULL,'',NULL     ),(414,550,NULL,'',NULL),(415,554,NULL,'',NULL);
But, since "Constituent Information" is just some sample data, I delete all the fields in that custom data group, and then delete the custom data group (I do this using CiviCRM web GUI). Then, I drop the new database, create a new one, redo the exports, and basically start over. Now, when I get to step 6 (the data import), I get:

ERROR 1062 (23000) at line 1159: Duplicate entry '1' for key 1. Now I look at 1159 and find a lot of strange control characters (^A). They are in the dump file, and also in the database, if I browse with mysql or whatever:

INSERT INTO `civicrm_value_earth_day_activities_3` (`id`, `entity_id`, `earth_day_garden_activities_5`, `earth_day_garden_activities_2010_6`, `earth_day_2010_activities_list_7`) VALUES (1,64,NULL,NULL,'^ASmokey the Bear^ASun Safety^ASprouting Seeds^ANutrition in the Garden^AHow Long Does it Last?^AMusic!^A'),(2,65,NULL,NULL,'^ASmokey the Bear^AVermicomposting (with worms!)^AGrowing Paper^AReduce, Reuse, Recycle^AArt in the Garden^AMusic!^A'),(3,66,NULL,NULL,'^ABird Benefits^AVermicomposting (with worms!)^AGrowing Paper^AArt in the Garden^ANutrition in the Garden^ALeave No Trace^A'),(4,67,NULL,NULL,'^ASun Safety^AArt in the Garden^ASprouting Seeds^APlanting^ANutrition in the Garden^AWeed or Flower?^A'),(5,68,NULL,NULL,'^ABird Benefits^AVermicomposting (with worms!)^AGrowing Paper^AArt in the Garden^ANutrition in the Garden^ANatural Pest Control^A'),(6,69,NULL,NULL,'^ABird Benefits^AVermicomposting (with worms!)^AGrowing Paper^AArt in the Garden^ASprouting Seeds^APlanting^A'),(7,70,NULL,NULL,'^AVermicomposting (with worms!)^AGrowing Paper^AArt in the Garden^ASprouting Seeds^APlanting^ANutrition in the Garden^A'),(8,71,NULL,NULL,'^ABird Benefits^ASun Safety^AGrowing Paper^AReduce, Reuse, Recycle^AArt in the Garden^AMusic!^A'),(9,72,NULL,NULL,'^ABird Benefits^AVermicomposting (with worms!)^AArt in the Garden^ASprouting Seeds^ANatural Pest Control^AWeed or Flower?^A'),(10,73,NULL,NULL,'^ABird Benefits^ASun Safety^AReduce, Reuse, Recycle^AArt in the Garden^AI Need Sun!^AMusic!^A'),(11,74,NULL,NULL,'^AWild Flower Fire Prevention^ABird Benefits^AVermicomposting (with worms!)^AReduce, Reuse, Recycle^AArt in the Garden^AMusic!^A');
/*!40000 ALTER TABLE `civicrm_value_earth_day_activities_3` ENABLE KEYS */;
UNLOCK TABLES;
Could the weird ^A control characters be causing the problem? It seems that all of my custom fields have them. I could search/replace them out with sed or something, if I knew what the field separator should actually be?

Thanks again for any suggestions.

-Henry