Author Topic: CiviCampaign can't reserve respondents (Read 1729 times)

bostaurus · February 18, 2012, 08:38:33 am

I am testing a fresh install of Civi 3.4.8 (drupal 6.24) on a test server. User is Root with ALL privileges for the Civi mysql database.
I cannot reserve respondents for a campaign survey. I get a "DB Error: can not create" and it apparently can't create the necessary temporary table.
I saw someone else had this problem on an older forum post but it didn't appear to be resolved.

Can someone help? Is this a bug or something wrong with my setup???

I'm not a very experienced developer so if it's a bug I'm not sure how much help I'd be in fixing it.

Thanks...

Leslie (bostaurus)

Code: [Select]

Database Error Code: Can't create table 'civi_3_4_8_test_crm.civicrm_survey_respondent_temp_7b345b57e5af6b6226cf20d332f74f70' (errno: 150), 1005
Additional Details:

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

    [code] => -15
    [message] => DB Error: can not create
    [mode] => 16
    [debug_info] => 
     CREATE TEMPORARY TABLE civicrm_survey_respondent_temp_7b345b57e5af6b6226cf20d332f74f70 (
            id int unsigned NOT NULL AUTO_INCREMENT,
            survey_contact_id int unsigned NOT NULL,  
PRIMARY KEY ( id ),
 CONSTRAINT FK_civicrm_survey_respondent FOREIGN KEY (survey_contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE ) [nativecode=1005 ** Can't create table 'civi_3_4_8_test_crm.civicrm_survey_respondent_temp_7b345b57e5af6b6226cf20d332f74f70' (errno: 150)]
    [type] => DB_Error
    [user_info] => 
     CREATE TEMPORARY TABLE civicrm_survey_respondent_temp_7b345b57e5af6b6226cf20d332f74f70 (
            id int unsigned NOT NULL AUTO_INCREMENT,
            survey_contact_id int unsigned NOT NULL,  
PRIMARY KEY ( id ),
 CONSTRAINT FK_civicrm_survey_respondent FOREIGN KEY (survey_contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE ) [nativecode=1005 ** Can't create table 'civi_3_4_8_test_crm.civicrm_survey_respondent_temp_7b345b57e5af6b6226cf20d332f74f70' (errno: 150)]
    [to_string] => [db_error: message="DB Error: can not create" code=-15 mode=callback callback=CRM_Core_Error::handle prefix="" info="
     CREATE TEMPORARY TABLE civicrm_survey_respondent_temp_7b345b57e5af6b6226cf20d332f74f70 (
            id int unsigned NOT NULL AUTO_INCREMENT,
            survey_contact_id int unsigned NOT NULL,  
PRIMARY KEY ( id ),
 CONSTRAINT FK_civicrm_survey_respondent FOREIGN KEY (survey_contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE ) [nativecode=1005 ** Can't create table 'civi_3_4_8_test_crm.civicrm_survey_respondent_temp_7b345b57e5af6b6226cf20d332f74f70' (errno: 150)]"]
)

Donald Lobo · February 18, 2012, 02:09:52 pm

i would double check the permissions of the user and/or the ability to create temp tables. seems like civicrm cannot create a temp table as that db user and hence the error

lobo

bostaurus · February 18, 2012, 03:27:10 pm

The database setting in civicrm.settings.php is root@localhost/mydatabase...etc. There is no password for root.
root has all privileges for the crm database, including create tables.
I am logged in to the drupal site as admin and admin has all permissions for the civicrm module.

Is there something else I need to do for ensure that the code can create the temporary table?

Thanks so much for your help!!!

Leslie Bostaurus

bostaurus · February 26, 2012, 12:12:49 pm

This error is definitely NOT caused by a permissions problem with creating temporary tables.

I could reproduce the error by running the create temporary table sql code in phpMyadmin. It would fail there, too, with the same error code (#1005, errno150). If I removed the foreign key constraint, however, I could get the sql code to run in phpMyadmin just fine. Also, if I ran the complete code (including the foreign key constraint) but creating a regular table, not a temporary table, it would also work fine.

I searched and searched and found lots of stuff about the mysql error #1005 errno 150 which is what civicrm was throwing, and also the same error I was reproducing in phpMyadmin. I tried a bunch of different things based on all the stuff I read (making sure data types exactly the same in related columns, etc.), but nothing worked. I still kept getting the error if I tried to create the temp table with the foreign constraint.

So finally, I edited the Interview.php file and just removed the foreign key constraint line from the sql statement and that seems to have worked, and I haven't been able to encounter any problems related to lack of the constraint (completely deleted contacts don't appear in civicampaign searches, etc.)

I no longer get the error. Does this make sense to anyone? I'm a total newbie so I'm kind of flailing away here and would like to know if this really was a bug, or just something serendipitous that happened in my experimenting.

Thanks,

Leslie (bostaurus)

Donald Lobo · February 26, 2012, 12:33:26 pm

what version of mysql are u using? I just tried the create table on my mysql db (5.1.61) with the FK constraint and it worked fine

lobo

bostaurus · February 26, 2012, 12:57:14 pm

my SQL version for me is 5.5.16

CiviCRM Community Forums (archive)

News:

Author Topic: CiviCampaign can't reserve respondents (Read 1729 times)

bostaurus

CiviCampaign can't reserve respondents

Donald Lobo

Re: CiviCampaign can't reserve respondents

bostaurus

Re: CiviCampaign can't reserve respondents

bostaurus

Re: CiviCampaign can't reserve respondents

Donald Lobo

Re: CiviCampaign can't reserve respondents

bostaurus

Re: CiviCampaign can't reserve respondents