Database error when upgrading from Drupal 6.37 to Drupal 7.41

Question

I have updated my site from Drupal 6.21 to 6.37 and it worked fine. Now I am trying to upgrade it to Drupal 7.41. I have changed the core as per the instructions that we follow for upgrading, but there is a database error I am seeing and don't know how can we fix it. I have googled and there were few threads asking to manually increasing the field size https://www.drupal.org/node/1133024 but it doesn't helped much and shows the same error again.

How can I fix this error?

Answer 1

Heading ##I see this post is a few days old, hope you are still watching.

What is happening is that the update is trying to 're-shape' the type field in the system table, to look like this in D7:

type | character varying(12) | not null default ''::character varying However in D6 the field looked like this:

type | character varying(255) | not null default ''::character varyin g

Now you have old content in that field that is longer than 12 and would be truncated and therefore the ALTER TABLE command fails. Now, this is what I would call a messed up update system.

I think there are several options to fix this.

First make a backup of your system table.

Then open a database connection to your drupal database and check what the longest entry will be. I am assuming you are using Postgres:

select (length(type)), type from system group by type; length | type --------+-------- 5 | theme 6 | module

That is my output, now you can check which entry is longer than 12 characters, and see if it contained any useful data.

Now, the easiest way is probably to simply do the field truncations yourself:

ALTER TABLE system ALTER COLUMN type TYPE VARCHAR(12) USING SUBSTR(type, 1, 12)

Then attempt the migration again, you still have your backup should this go wrong.

Second option depends on if you need these defined types, possibly they can simply be deleted:

select type from system where length(type) > 12; Turn this into a delete statement if you will.

Third option is possible if no code will rely on the field size being 12.

The code for the db update is in modules/system/system.install line 2112:

function system_update_7018() {
  db_drop_index('system', 'modules');
  db_drop_index('system', 'type_name');
  db_change_field('system', 'type', 'type', array('type' => 'varchar', 'length' => 12, 'not null' => TRUE, 'default' => ''));
  db_add_index('system', 'type_name', array('type', 'name'));
}

change length => 12 to the maximum desired length, and run the dbupdate again.

I can't say which consequences each of the approaches will have and each can possibly cause follow-up errors and break other stuff, so you should try them all.

Hint: use drush for the updates, there will be a lot of repetitive tasks, roll-back-update-roll-back-update.