MAJOR ERRORS after civicrm upgraded to 5.35.1 wordpress

Question

I am getting multiple errors since upgrading our civicrm to the latest version.

When I try to enable debugging i receive db constraint issues and log file shows: Apr 06 11:54:29 [error] $Fatal Error Details = Array ( [callback] => Array ( [0] => CRM_Core_Error [1] => exceptionHandler )

[code] => -3
[message] => DB Error: constraint violation
[mode] => 16
[debug_info] => INSERT INTO civicrm_setting (`component_id`,`contact_id`,`created_date`,`created_id`,`domain_id`,`id`,`is_domain`,`name`,`value`) VALUES

(NULL,NULL,"20210406115429","2","1",NULL,"1","debug_enabled","s:1:"1";") [nativecode=1048 ** Column 'id' cannot be null] [type] => DB_Error [user_info] => INSERT INTO civicrm_setting (component_id,contact_id,created_date,created_id,domain_id,id,is_domain,name,value) VALUES (NULL,NULL,"20210406115429","2","1",NULL,"1","debug_enabled","s:1:"1";") [nativecode=1048 ** Column 'id' cannot be null] [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="INSERT INTO civicrm_setting (component_id,contact_id,created_date,created_id,domain_id,id,is_domain,name,value) VALUES (NULL,NULL,"20210406115429","2","1",NULL,"1","debug_enabled","s:1:"1";") [nativecode=1048 ** Column 'id' cannot be null]"] )

Also when I click on civicrm in wordpress menu the screen only shows: Sorry, due to an error, we are unable to fulfill your request at the moment. You may want to contact your administrator or service provider with more details about what action you were performing when this occurred. DB Error: no such field

Answer 1

Do you have advanced logging (Administer menu » System Settings » Misc) on? If so can you turn it off before upgrading and try enabling it after upgrade?

Answer 2

OK everyone, after much head scratching, log file digging and googling I managed to fix my issues as follows:

Goal required:

Migrate our WADT.org.uk web site from a Linux shared hosted package with 123reg to a cPanel package with cron facility with 123Reg.

I followed all the steps recommended by my provider - whoops - including the backup and restore of the MYSQL database that powers WordPress and CiviCRM.

This was the start of my issue highway

When you migrate a MySQL database the tables loose all of their auto incrementing fields and primary keys including all WordPress and CiviCRM tables - DOH!

This issue causes tons of platform issues including - new WordPress users created with ID's = 0, posts to not appear and CiviCRM ids all go to pot and contacts stop working etc.

To fix this issue I completely removed CiviCRM and contact data and then I had to address the auto increment / missing key issue.

I put the script 1 below together and this seems to have done the trick for WordPress, then I did a fresh install of CiviCRM 5.35.1, ran script 2 to identify any tables set to MYISAM and altered any such tables to be INNODB (e.g. if you add the CiviVolunteer extension new tables are created in the database with the storage engine of MYISAM and these must be altered to innodb before use otherwise CiviCRM throws an error)

All in all this has been a long learning curve but having just performed the 5.36.1 upgrade without issue then WOOHOO I am a happy bunny - for the moment!

Here are some scripts I used just in case they could be helpful to anyone (Remember to check database structures and add new tables to the script if required).

SCRIPT 1

Replace WPPrefix_ with your WordPress database prefix :-)

1.

-- Starting with WPPrefix_users DELETE FROM WPPrefix_users WHERE ID = 0;
    -- ALTER TABLE WPPrefix_users ADD PRIMARY KEY  (ID); ALTER TABLE WPPrefix_users ADD KEY user_login_key (user_login); ALTER TABLE
    WPPrefix_users ADD KEY user_nicename (user_nicename); ALTER TABLE
    WPPrefix_users ADD KEY user_email (user_email);
    -- ALTER TABLE WPPrefix_users MODIFY ID bigint(20) unsigned NOT NULL auto_increment;
    -- Starting with WPPrefix_usermeta
    -- DELETE FROM WPPrefix_usermeta WHERE umeta_id = 0;
    -- ALTER TABLE WPPrefix_usermeta ADD PRIMARY KEY  (umeta_id); ALTER TABLE WPPrefix_usermeta ADD KEY user_id (user_id); ALTER TABLE
    WPPrefix_usermeta ADD KEY meta_key (meta_key(191));
    -- ALTER TABLE WPPrefix_usermeta MODIFY umeta_id bigint(20) unsigned NOT NULL auto_increment;
    -- Starting with WPPrefix_posts DELETE FROM WPPrefix_posts WHERE ID = 0; ALTER TABLE WPPrefix_posts ADD PRIMARY KEY  (ID); ALTER TABLE WPPrefix_posts ADD KEY post_name (post_name(191)); ALTER TABLE
    WPPrefix_posts ADD KEY type_status_date
    (post_type,post_status,post_date,ID); ALTER TABLE WPPrefix_posts ADD
    KEY post_parent (post_parent); ALTER TABLE WPPrefix_posts ADD KEY
    post_author (post_author); ALTER TABLE WPPrefix_posts MODIFY ID
    bigint(20) unsigned NOT NULL auto_increment;
    -- Starting with WPPrefix_comments DELETE FROM WPPrefix_comments WHERE comment_ID = 0; ALTER TABLE WPPrefix_comments ADD PRIMARY KEY 
    (comment_ID); ALTER TABLE WPPrefix_comments ADD KEY comment_post_ID
    (comment_post_ID); ALTER TABLE WPPrefix_comments ADD KEY
    comment_approved_date_gmt (comment_approved,comment_date_gmt); ALTER
    TABLE WPPrefix_comments ADD KEY comment_date_gmt (comment_date_gmt);
    ALTER TABLE WPPrefix_comments ADD KEY comment_parent
    (comment_parent); ALTER TABLE WPPrefix_comments ADD KEY
    comment_author_email (comment_author_email(10)); ALTER TABLE
    WPPrefix_comments MODIFY comment_ID bigint(20) unsigned NOT NULL
    auto_increment;
    -- Starting with WPPrefix_links DELETE FROM WPPrefix_links WHERE link_id = 0; ALTER TABLE WPPrefix_links ADD PRIMARY KEY  (link_id);
    ALTER TABLE WPPrefix_links ADD KEY link_visible (link_visible);
    ALTER TABLE WPPrefix_links MODIFY link_id bigint(20) unsigned NOT
    NULL auto_increment;
    -- Starting with WPPrefix_options DELETE FROM WPPrefix_options WHERE option_id = 0; ALTER TABLE WPPrefix_options ADD PRIMARY KEY 
    (option_id); ALTER TABLE WPPrefix_options ADD UNIQUE KEY option_name
    (option_name); ALTER TABLE WPPrefix_options ADD KEY autoload
    (autoload); ALTER TABLE WPPrefix_options MODIFY option_id bigint(20)
    unsigned NOT NULL auto_increment;
    -- Starting with WPPrefix_postmeta DELETE FROM WPPrefix_postmeta WHERE meta_id = 0; ALTER TABLE WPPrefix_postmeta ADD PRIMARY KEY 
    (meta_id); ALTER TABLE WPPrefix_postmeta ADD KEY post_id (post_id);
    ALTER TABLE WPPrefix_postmeta ADD KEY meta_key (meta_key(191));
    ALTER TABLE WPPrefix_postmeta MODIFY meta_id bigint(20) unsigned NOT
    NULL auto_increment;
    -- Starting with WPPrefix_terms DELETE FROM WPPrefix_terms WHERE term_id = 0; ALTER TABLE WPPrefix_terms ADD PRIMARY KEY  (term_id);
    ALTER TABLE WPPrefix_terms ADD KEY slug (slug(191)); ALTER TABLE
    WPPrefix_terms ADD KEY name (name(191)); ALTER TABLE WPPrefix_terms
    MODIFY term_id bigint(20) unsigned NOT NULL auto_increment;
    -- Starting with WPPrefix_term_taxonomy DELETE FROM WPPrefix_term_taxonomy WHERE term_taxonomy_id = 0; ALTER TABLE
    WPPrefix_term_taxonomy ADD PRIMARY KEY  (term_taxonomy_id); ALTER
    TABLE WPPrefix_term_taxonomy ADD UNIQUE KEY term_id_taxonomy
    (term_id,taxonomy); ALTER TABLE WPPrefix_term_taxonomy ADD KEY
    taxonomy (taxonomy); ALTER TABLE WPPrefix_term_taxonomy MODIFY
    term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment;
    -- Starting with WPPrefix_term_relationships DELETE FROM WPPrefix_term_relationships WHERE object_id = 0; DELETE FROM
    WPPrefix_term_relationships WHERE term_taxonomy_id = 0; ALTER TABLE
    WPPrefix_term_relationships ADD PRIMARY KEY 
    (object_id,term_taxonomy_id); ALTER TABLE
    WPPrefix_term_relationships ADD KEY term_taxonomy_id
    (term_taxonomy_id);
    -- Starting with WPPrefix_termmeta DELETE FROM WPPrefix_termmeta WHERE meta_id = 0; ALTER TABLE WPPrefix_termmeta ADD PRIMARY KEY 
    (meta_id); ALTER TABLE WPPrefix_termmeta ADD KEY term_id (term_id);
    ALTER TABLE WPPrefix_termmeta ADD KEY meta_key (meta_key(191));
    ALTER TABLE WPPrefix_termmeta MODIFY meta_id bigint(20) unsigned NOT
    NULL auto_increment;
    -- Starting with WPPrefix_commentmeta DELETE FROM WPPrefix_commentmeta WHERE meta_id = 0; ALTER TABLE
    WPPrefix_commentmeta ADD PRIMARY KEY  (meta_id); ALTER TABLE
    WPPrefix_commentmeta ADD KEY comment_id (comment_id); ALTER TABLE
    WPPrefix_commentmeta ADD KEY meta_key (meta_key(191)); ALTER TABLE
    WPPrefix_commentmeta MODIFY meta_id bigint(20) unsigned NOT NULL
    auto_increment;

SCRIPT 2

Run the following to identify any tables set to the MyISAM storage engine:

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'database' and ENGINE = 'myISAM'

Use the following to alter any tables identified in the above script:

ALTER TABLE WPPrefix_comments ENGINE=InnoDB;

---

Finally, thank you everyone - I am loving CiviCRM etc and its all new to me! If I have stated anything incorrectly then I apologise because I am by no means an expert with any of this so please use any of my notes as may fit.

Thanks, Lee Cullinan