[Solved] Integrity constraint violation: 1062

By masu0105 on 8 Jan 2014 at 15:49 UTC

Hi!

I am trying to upgrade my Drupal version from 7.14 to 7.23. I am using drush to do it and it works fine until it tries to update the database then it fails with the error:

WD php: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'tmd': INSERT INTO {block} [error]
(status, weight, region, pages, cache) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2,
:db_insert_placeholder_3, :db_insert_placeholder_4); Array
(
[:db_insert_placeholder_0] => 0
[:db_insert_placeholder_1] => 0
[:db_insert_placeholder_2] => -1
[:db_insert_placeholder_3] =>
[:db_insert_placeholder_4] => 4
)
in drupal_write_record() (line 7166 of docroot/includes/common.inc).
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'tmd': INSERT INTO {block} (status, weight, region, pages, cache) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4); Array
(
[:db_insert_placeholder_0] => 0
[:db_insert_placeholder_1] => 0
[:db_insert_placeholder_2] => -1
[:db_insert_placeholder_3] =>
[:db_insert_placeholder_4] => 4
)
i drupal_write_record() (linje 7166 av docroot/includes/common.inc)

From what i can se in the database on line is inserted in the blocks table before it fails. I have tried to delete all entries in the blocks table but still tha same error. Anyone that can help me solve this problem?

/ Markus

Comments

If you google for this (e.g.

John_B commented 10 January 2014 at 08:07

If you google for this (e.g. 'drupal tmd integrity constraint violation') you find references to similar bugs caused by trying to write duplicate data to blocks table. If you are trying the update on cheap hosting I would re-try it on a decent server or desktop environment. If that does not work, I would be inclined to try the core update with your contributed and custom modules disabled.

Digit Professionals specialising in Drupal, WordPress & CiviCRM support for publishers in non-profit and related sectors

Hi, and thank you for the

masu0105 commented 13 January 2014 at 06:52

Hi, and thank you for the response. I already googled it and it did not help me. We are using are own servers so they should be powerfull enough. I was hoping that i did not have to disable all custom and contrib modules since we have close to 100 and the only thing i could think of in our custom modules that could break it is if there is a problem using 0 as a key in the hook_block_info, even though the documentation of hook_block_info says that you can use numeric keys. I will try to search a bit more and see what i can find but otherwise i will try disabling the modules after all :( Do you have any idea why the core update requires writing to the block table?

deleted duplicate

John_B commented 10 January 2014 at 08:08

deleted duplicate

Digit Professionals specialising in Drupal, WordPress & CiviCRM support for publishers in non-profit and related sectors

Running update.php clears

John_B commented 13 January 2014 at 07:48

Running update.php clears caches, which calls _block_rehash(), which as I understand it re-regiseters blocks created by modules, and this is probably what is happening, so it is far from certain that this problem is caused by the update writing to the block table. Quite why a cache clear after update causes problems when (I assume) a cache clear without updating does not, is probably a better question, and the answer may well be that the core updates breaks one of the contributed modules.

You could always try updating everything on dev copy of the site, it only takes seconds with drush up (being sure to first lock any modules you have hacked). This will not deal with the situation where a contributed module requires an update which so far is released only in the dev version because it will download full releases.

Digit Professionals specialising in Drupal, WordPress & CiviCRM support for publishers in non-profit and related sectors

I found the problem. We had a

masu0105 commented 14 January 2014 at 08:38

I found the problem. We had a hook_block_info_alter that was changing some block settings without checking if the blocks where in blocks array.

function example_block_info_alter(&$blocks, $theme, $code_blocks) {
    $blocks['apachesolr_search']['sort']['cache'] = DRUPAL_NO_CACHE;
    $blocks['facetapi']['6MD2FwsKP1hzbU6U0TiKOrJpHmgAQmG8']['cache'] = DRUPAL_CACHE_PER_PAGE;
}

When that hook was called it added the keys for the blocks and since the key 'theme', 'module' and 'path' that are used as a unique key in the blocks table where empty, it caused the database exception.

Thanks for pointing me in the right direction.

/ Markus

Today I had faced same issue

jamadar commented 6 August 2015 at 15:01

Today I had faced same issue and I resolved this by truncating all the caching tables using mysql query.
I followed following steps:
1. Connect Sql server using - drush sql-cli
2. Truncate the all the cache tables and they are as follows:
truncate cache;
truncate cache_admin_menu;
truncate cache_block;
truncate cache_bootstrap;
truncate cache_field;
truncate cache_filter;
truncate cache_form;
truncate cache_image;
truncate cache_libraries;
truncate cache_media_xml;
truncate cache_menu;
truncate cache_page;
truncate cache_path;
truncate cache_token;
truncate cache_views;
truncate cache_views_data;
3. Then run drush rr
4. Once the registry rebuild run drush cc all or you can try to update/build the job again.