CiviCRM Community Forums (archive)

In using my 2.0.5 (Drupal 5) CiviCRM, I noticed that when I have a custom tab which has a file field (which we use for an image) if I upload one image, and then edit the tab and upload another, the database is not updated correctly and if I then attempt to delete the file, an SQL error is generated. What I discovered in the DB is that:

civicrm_value_1_Pictures table (my custom tab table) row remained the same
civicrm_file table changed the file name to the new one, which is good, AND it changed the file_type_id to the id of the row, which seems quite odd.
civicrm_entity_file table had added a matching row, and this is the error.

When one tries to delete, it fails and I do believe it's due to the duplicate rows in civicrm_entity_file.

Anyhow I wanted to see if 2.1 has this so I went to the sandbox and I tried to make a new custom data group called 'picture test' and I left all default settings on that page alone (such as 'used for contacts' and 'inline' etc.)

When I submitted it I got this:

New Custom Data Group
 Sorry. A non-recoverable error has occurred.


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

DB Error: can not create

Database Error Code: Can't create table './civicrm_sandbox/civicrm_value_picture_test_4.frm' (errno: 150), 1005

Return to home page.

Error Details:


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

    [code] => -15
    [message] => DB Error: can not create
    [mode] => 16
    [debug_info] => CREATE TABLE civicrm_value_picture_test_4 (
        `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Default MySQL primary key',
        `entity_id` int unsigned NOT NULL COMMENT 'Table that this extends',
        PRIMARY KEY ( id ),
        UNIQUE INDEX unique_entity_id (  entity_id ),
        CONSTRAINT FK_civicrm_value_picture_test_4_entity_id FOREIGN KEY ( `entity_id` ) REFERENCES civicrm_contact_he_IL ( id ) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; [nativecode=1005 ** Can't create table './civicrm_sandbox/civicrm_value_picture_test_4.frm' (errno: 150)]
    [type] => DB_Error
    [user_info] => CREATE TABLE civicrm_value_picture_test_4 (
        `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Default MySQL primary key',
        `entity_id` int unsigned NOT NULL COMMENT 'Table that this extends',
        PRIMARY KEY ( id ),
        UNIQUE INDEX unique_entity_id (  entity_id ),
        CONSTRAINT FK_civicrm_value_picture_test_4_entity_id FOREIGN KEY ( `entity_id` ) REFERENCES civicrm_contact_he_IL ( id ) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; [nativecode=1005 ** Can't create table './civicrm_sandbox/civicrm_value_picture_test_4.frm' (errno: 150)]
    [to_string] => [db_error: message="DB Error: can not create" code=-15 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TABLE civicrm_value_picture_test_4 (
        `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Default MySQL primary key',
        `entity_id` int unsigned NOT NULL COMMENT 'Table that this extends',
        PRIMARY KEY ( id ),
        UNIQUE INDEX unique_entity_id (  entity_id ),
        CONSTRAINT FK_civicrm_value_picture_test_4_entity_id FOREIGN KEY ( `entity_id` ) REFERENCES civicrm_contact_he_IL ( id ) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; [nativecode=1005 ** Can't create table './civicrm_sandbox/civicrm_value_picture_test_4.frm' (errno: 150)]"]
)

However, I am wondering if the sandbox is broken anyway. Someone did change many links and tabs to Hebrew, so it is clear that someone has been playing with it a bit.

[/code]

I made a new custom group and added to it a File type of field and I edited one contact to upload a picture, no other edits whatsoever. This is the result:

Ms Anne M Adams Sr
 Sorry. A non-recoverable error has occurred.


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

DB Error: constraint violation

Database Error Code: Cannot add or update a child row: a foreign key constraint fails (`civicrm_sandbox/civicrm_log`, CONSTRAINT `FK_civicrm_log_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE), 1452

Return to home page.

Error Details:


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

    [code] => -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_log (entity_table , entity_id , data , modified_id , modified_date ) VALUES ('civicrm_contact' ,  39 , 'civicrm_contact,39' ,  102 ,  20080826014810 )  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm_sandbox/civicrm_log`, CONSTRAINT `FK_civicrm_log_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_log (entity_table , entity_id , data , modified_id , modified_date ) VALUES ('civicrm_contact' ,  39 , 'civicrm_contact,39' ,  102 ,  20080826014810 )  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm_sandbox/civicrm_log`, CONSTRAINT `FK_civicrm_log_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_log (entity_table , entity_id , data , modified_id , modified_date ) VALUES ('civicrm_contact' ,  39 , 'civicrm_contact,39' ,  102 ,  20080826014810 )  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm_sandbox/civicrm_log`, CONSTRAINT `FK_civicrm_log_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]"]
)

[/code]

The original issue (with FK referencing a view) happens only on multi-lingual sites, and so it didn’t occur after reseting sandbox to the default, single-language install.

That said, I fixed this issue in r16836 + r16837 and updated sandbox, so it should now work for multi-lingual sites as well.