Database Errors trying to upgrade D6 site to D7

I think this is actually a problem with the updater, not the database.

This happens to me as well:

The first update attempt failed because of some PHP error (my old theme was missing). When I restart the update, I get an unending stream of database errors such as

DatabaseSchemaObjectExistsException: Cannot add index <em class="placeholder">system_list</em> to table <em class="placeholder">system</em>: index already exists. in DatabaseSchema_mysql->addIndex() (line 433 of /web/d7-rc1/includes/database/mysql/schema.inc).

DatabaseSchemaObjectExistsException: Cannot add field <em class="placeholder">url_alias</em>.<em class="placeholder">source</em>: field already exists. in DatabaseSchema_mysql->addField() (line 324 of /web/d7-rc1/includes/database/mysql/schema.inc).

DatabaseSchemaObjectExistsException: Cannot add field <em class="placeholder">url_alias</em>.<em class="placeholder">alias</em>: field already exists. in DatabaseSchema_mysql->addField() (line 324 of /web/d7-rc1/includes/database/mysql/schema.inc).

Note that fixing the first error by manually dropping the index in the database will allow me to proceed to the second error. But to fix the second error I'll have to fix the first error AGAIN, since the upgrade script has added the index again.

It seems that the upgrade script cannot handle partially completed upgrades.

subscribed

When I restart the update -- do you mean you did not restore the database just tried to run the update the second time after an error? That's not even supposed to work.

Yes, I just reran update.php -- that approach seemed perfectly natural to me.

It's fine that it's not supposed to work that way, but I would strongly recommend to add a short note about this to UPGRADE.TXT.

@joemoraca: No, it just means that you'll have to rollback your database before you retry.

I'm having the same issue using latest dev.

I enabled PDO in ubuntu (not using PECL).

I'm rolling back the 6.19 database after each try. I don't get the PDO version check. I've disabled all optional modules in 6.19 database. I'm marking this issue as major, but seems critical to me, I just don't want to block Drupal 7.

I'm getting one error:

DatabaseSchemaObjectExistsException: Cannot add index system_list to table system: index already exists. in DatabaseSchema_mysql->addIndex() (line 433 of /home/books/public_html/includes/database/mysql/schema.inc).

We shouldn't need to rollback an upgrade attempt. The upgrade should be graceful enough to handle a partial upgrade.

@shelleyp, you must upgrade to the dev version and run the update.php, I think that error are allready fixed.

I tried again using latest dev (20 Dec) and I'm still getting the errors.

DatabaseSchemaObjectExistsException: Table role_permission already exists. in DatabaseSchema->createTable() (line 623 of /var/www/includes/database/schema.inc).

I'll try the dev version, see how it goes. Thanks for suggestion.

Update: I tried it with the dev, and still getting this error. And an uncaught exception.

exactly the same problem as #2 when I try do upgrade from Drupal 6.20 to Drupal Drupal 7.0 RC 3 Released...

I tried again usign RC3:

DatabaseSchemaObjectExistsException: Table role_permission already exists. in DatabaseSchema->createTable() (line 623 of /var/www/includes/database/schema.inc).

federico - could you post your PDO version, and also the output of pecl list?

Thanks, I don't know how to check the PDO version, this is the output of phpinfo

PDO Driver for MySQL	enabled
Client API version 	          5.1.41

and this is pecl list

:~$ pecl list
Installed packages, channel pecl.php.net:
=========================================
Package Version State
APC     3.0.19  stable

Hmm, any chance you're running EAccelerator as well as APC (seems unlikely but has been responsible for a lot of similar errors #914742: Add hook_requirements() for buggy versions of eAccelerator)

Also #947616: Warn people not to enable xdebug.show_exception_trace.

Both of these cause bizarre errors in the installer, neither are patched.

I'm open to discussing both of those, btw. It just seems like a lot of workarounds in our code for weirdness in others'.

I'm not running EAcceleartor. I've uninstalled the "php5-xdebug" package and tried again, with no luck.

federico any chance I can get access to that machine? Either catch me on irc.freenode.net #drupal-contribute as chx or email me chx1975 gmail com. Thanks

chx: it's a local machine, I tried again using another machine (ubuntu server 10.04 with pdo 5.1.41) and got:

PDOException: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'weight' at row 1: UPDATE {system} SET weight=:db_update_placeholder_0 WHERE (filename = :db_condition_placeholder_0) ; Array ( [:db_update_placeholder_0] => 1000 [:db_condition_placeholder_0] => profiles/standard/standard.profile ) in system_update_files_database() (line 2233 of /var/www/modules/system/system.module).

About the "cannot add index 'system_list'" error, that simply means that the upgrade failed previously and was attempted again without restoring a backup. A more meaningful error will likely appear before the 'system_list' error in the php logs. I'm not sure what the "role_permission" error is about though.

The last error about "Numeric value out of range" looks meaningless as well.

I'm not convinced that there is an actual bug in this issue, seems more likely that http://drupal.org/requirements or UPGRADE.txt are not being followed. Otherwise it would be more widely reproducible.

I am putting down to needs info until we get a reproducible bug report. Sorry I can not see how could I fix anything here. If you can start from a D6 database and upgrade and it fails and restoring it restarting fails the same place and make that dump available somehow... that'd be a usable bug report for example. These two word "i tried foo" reports contain nothing tangible, no two reports are the same. (There is no trace of an OS, webserver and PHP version either).

Edit: local machines can run VNC or sshd too. You can do VNC reverse connection to bypass firewalls. Where there is a will, there is a way.

A one-off issue is not critical/release blocking if no one else is able to reproduce it yet.

About the "cannot add index 'system_list' error, I'm sure I dropped the database after every failed attempt, then I copied the D6 database again from the original site (running live without issues), after disabling every optional module and setting the theme to Garland.

I changed the priority to critical because I thought I was not the only one facing this issue.

About VNC reverse connection to bypass firewall, sorry, I didn't know about that, maybe I don't have the required skills to participate here. Thanks anyway, I'll see if I can configure a VPS to give you access to the server (i'll have to remove the personal info of every user and other sensitive data to complain with the disclaimers of my site). But I spent a lot of time on this, you didn't need to say that there is no will from my side, that's not true.

If it helps somehow:

System 	Linux server2 2.6.18-194.8.1.el5.028stab070.5 #1 SMP Fri Sep 17 19:10:36 MSD 2010 x86_64 

ubuntu server 10.04

Apache Version 	Apache/2.2.14 (Ubuntu)
Apache API Version 	20051115 

Timeouts 	Connection: 300 - Keep-Alive: 15 

PHP Version 	5.3.2-1ubuntu4.5 

memory_limit	128M

Mysql Client API version 	5.1.41 

DOM/XML 	enabled
DOM/XML API Version 	20031129 

GD Support 	enabled
GD Version 	2.0 

register_globals	Off

safe_mode	Off

PDO Client API version 	5.1.41 

@federico, it's very likely that if you can provide a database (D6, before upgrade) and perhaps a tarball, that this could be replicated.

If you can replicate this every time you try with the same starting point, then it may be worth chasing.

Solved using another machine!

I am having the same issue. Following instructions per http://drupal.org/node/570162, though one installation I mistakenly autocompleted the address to install.php instead of update.php. Downloaded a new copy of the database to a new location and re-downloaded core module and all components from a functional live site. Drupal 7.0 was a fresh download yesterday when it failed again. I'm working on MAMP on a local machine and am happy to provide any additional information. The error is:
"DatabaseSchemaObjectExistsException: Cannot add index system_list to table system: index already exists. in DatabaseSchema_mysql->addIndex() (line 433 of /Applications/MAMP/htdocs/bonustomato/web/includes/database/mysql/schema.inc)."

Below that I get:
Uncaught exception thrown in session handler.

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ssid' in 'where clause': SELECT 1 AS expression FROM {sessions} sessions WHERE ( (sid = :db_condition_placeholder_0) AND (ssid = :db_condition_placeholder_1) ) FOR UPDATE; Array ( [:db_condition_placeholder_0] => c821958eae86b5803a10d46c0909cbeb [:db_condition_placeholder_1] => ) in _drupal_session_write() (line 204 of /Applications/MAMP/htdocs/bonustomato/web/includes/session.inc).

I've tried the process several different times using variations on the downloaded components and database from the functional live site, and continue to have the same result. Site was previously on 6.12 and upgraded to 6.20 without issue.

Thanks in advance to anyone that can provide assistance.

The same issue as #32. Trying to upgrade from 6.20 to 7.x following 7's upgrade.txt and having errors:

DatabaseSchemaObjectExistsException: Cannot add index system_list to table system: index already exists. in DatabaseSchema_mysql->addIndex() (line 433 of ...\includes\database\mysql\schema.inc).

Uncaught exception thrown in session handler.

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ssid' in 'where clause': SELECT 1 AS expression FROM {sessions} sessions WHERE ( (sid = :db_condition_placeholder_0) AND (ssid = :db_condition_placeholder_1) ) FOR UPDATE; Array ( [:db_condition_placeholder_0] => 73613c88076d259e3edbc52bf77e0680 [:db_condition_placeholder_1] => ) in _drupal_session_write() (line 204 of ...\includes\session.inc).

resolved manually using mysql `drop index` & `alter table drop column` statements

ihatie, can you explain how you resolved that manually? Forgive my ignorance of manual mysql manipulation. Thanks!

subscribe

Stumbled over the same role_permission error today. Solution for me: make sure you completely empty the database before you try the upgrade again. I just imported the mysqldump again, so an existing role_permission table was left behind from a previous upgrade attempt which caused the error.

I am still getting this error.

I have read this thread and tried everything mentioned. I have

• Disabled all modules
• Did a db backup
• Copied Drupal 7.4 code (this is in a new site. I am wanting to the the site running on DR 7 before making it production
• Restored from above backup to a blank (no tables) database
• set $update_free_access = TRUE;
• Run site.com/upgrade.php

I still get:
DatabaseSchemaObjectExistsException: Cannot add index <em class="placeholder">system_list</em> to table <em class="placeholder">system</em>: index already exists. in DatabaseSchema_mysql->addIndex() (line 432 of C:\inetpub\wwwroot\civiCRM4.1\includes\database\mysql\schema.inc).

Upgrading from 6.19 to 7.4
Running php 5.3.5 on IIS 7
Mysql 5.0.81

I get the same error upgrading from Drupal 6.22 to 7.7. Looks like the system_list index was removed from system_update_7018() (see http://drupal.org/node/563106#comment-2610716 ) but re-instated in system_update_7062().

Edit: false alarm, my database hadn't been properly clean up before retrying. Emptying all tables and restarting the migration process worked fine for me.

Same error messages as #33 when upgrading from 6.22 to 7.7.

See also 6.2 to 7 error PDOException: SQLSTATE[42S22]

Might be related to #717834: The dependencies declared in core's hook_update_dependencies() implementations aren't actually correct which is supposed to be fixed in 7.8.

Hello,

Database Schema errors usually appear if an update.php script did not finish.

Like mine:
DatabaseSchemaObjectExistsException: Cannot add index system_list to table system: index already exists. in DatabaseSchema_mysql->addIndex()...

I was able to get past this error by increasing php max_exec_time in php.ini from 30s to something more likely to give the update script some time (i put in 3600s, just for the update, and reverted it back later)

After that do a database backup restoration (once the update script failed the database becomes unusable)...

You should then be able to run your update.php without trouble...

I just got bit by this bug

"DatabaseSchemaObjectExistsException: Cannot add index system_list to table system: index already exists. in DatabaseSchema_mysql->addIndex() (line 432 of /var/www/d7/includes/database/mysql/schema.inc).
The website encountered an unexpected error. Please try again later."

seems to be executing immediately so it's not a time out issue

I have ran all the updates for latest drupal 6, now migrating to D7 seems to be an issue

ema_version from system where filename not like "%sites/all/modules%";;
+---------------+--------------+----------------+
| name          | type         | schema_version |
+---------------+--------------+----------------+
| aggregator    | module       |              0 |
| block         | module       |              0 |
| blog          | module       |              0 |
| blogapi       | module       |             -1 |
| book          | module       |             -1 |
| color         | module       |           6001 |
| comment       | module       |           6003 |
| contact       | module       |              0 |
| dblog         | module       |           6000 |
| drupal        | module       |              0 |
| basicevent    | module       |             -1 |
| event_all_day | module       |             -1 |
| event_views   | module       |             -1 |
| event         | module       |             -1 |
| eventrepeat   | module       |             -1 |
| filter        | module       |              0 |
| forum         | module       |           6000 |
| help          | module       |              0 |
| legacy        | module       |             -1 |
| locale        | module       |             -1 |
| menu          | module       |              0 |
| node          | module       |              0 |
| openid        | module       |             -1 |
| path          | module       |              0 |
| php           | module       |             -1 |
| ping          | module       |             -1 |
| poll          | module       |              0 |
| profile       | module       |              0 |
| search        | module       |              0 |
| statistics    | module       |           6000 |
| syslog        | module       |             -1 |
| system        | module       |           6055 |
| taxonomy      | module       |              0 |
| throttle      | module       |             -1 |
| tracker       | module       |              0 |
| translation   | module       |             -1 |
| trigger       | module       |             -1 |
| update        | module       |             -1 |
| upload        | module       |              0 |
| user          | module       |              0 |
| watchdog      | module       |              0 |
| bluemarine    | theme        |             -1 |
| chameleon     | theme        |             -1 |
| marvin        | theme        |             -1 |
| phptemplate   | theme_engine |             -1 |
| garland       | theme        |             -1 |
| minnelli      | theme        |             -1 |
| pushbutton    | theme        |             -1 |
+---------------+--------------+----------------+
48 rows in set (0.00 sec)

not sure where to go from here , if i drop that index, it recreates it, then i end up back in the same place

One often has to do multiple upgrades to get where they're going. However, I suspect you might have done the upgrade more than one time without reloading the D6 database from scratch.

When repetitively trying upgrades, make sure to start with the *original* *unaltered* D6 database before starting over again.

Is it possible this is what happened to you?

Putting this back to fixed, as it appears to be more of a support request than a bug report against this issue. You can reopen if you're sure it's this same issue.

Yes, actually to throw a curve ball, I'm upgrading from drupal 5, but i'm doing all the upgrades in D6, then moving to D7.

Turns out it might have something todo with my PHP timeout settings, even tho all the updates for D6 are marked as complete, the D6 upgrade might have terminated before it was really finished, trying on the D7 again now, will update this issue if required

Just to add to the pot, I was getting this error during an upgrade process and realized that even though I was reimporting a fresh sql file every time, it wasn't actually dropping the new tables created by the upgrade process.

Dropping the database and recreating it before reattempting the update will make absolutely certain you are starting fresh, and solved the error for me.

But if you drop the database and create a fresh one, that is not really upgrading is it? what if you have a current site you want to upgrade?

Sorry, what I mean is that you should
1. Make a backup of your D6 database
2. Upgrade
3. If upgrade does not complete or you encounter any errors - DROP the database and import the one you made on step 1.
4. Try to resolve the cause of errors
5. Go back to 2, and repeat.

If you if you don't do the drop on step 3 and just reimport your DB from step 1, you will end up with extra tables that will cause the updater to throw errors, regardless of whether you ever fix what was causing them in the first place, whether it be timeout issues, missing dependencies, or whatever.

"DatabaseSchemaObjectExistsException: Cannot add index system_list to table system: index already exists. in DatabaseSchema_mysql->addIndex() (line 432 of /var/www/d7/includes/database/mysql/schema.inc).
The website encountered an unexpected error. Please try again later."

I worked past this error by using phpmyadmin to clean up every last database anomaly reported by the D6 Schema module. There was one anomaly that I simply left in place (an inconsistency in specifying "unique" for an index field in the Blog_API table, I think). After that, I got update.php to work correctly.

At one point when trying to update to 7.12, I cleaned up all the schema inconsistencies except for the blog_api thing and, bizarrely, I also failed to remove the extra system_list index noted in the error message. Once I removed the system_list index, though, the conversion process took off like gangbusters.

Now, the system_list table may have been there because of earlier (failed) D7 conversions - I'd been trying since Drupal Chicago to do the conversion. Still, it would have been nice, given that it hits a lot of people, if that sort of thing could be fixed.

You need to make sure you clean up all data from previous failed installs if you didn't then reattempting an update will fail.

After many red herring chases and misleading posts regarding artifacts related to not following best practices on the D6 -> D7 upgrade, this thread helped set me straight, and I am grateful.

My workflow requires that I will "reflow" updated content through the D6 -> D7 update procedure, so I automated this with drush. The other component is two local folders: a full D6 mirror of the site, and the "clean" D7 version, each with its own virtual host.

Here's from my shell script:

echo "delete old database"
echo Y | /Applications/MAMP/Library/bin/mysqladmin -u xxxx -pxxxx drop drupal-db
echo "create empty database"
/Applications/MAMP/Library/bin/mysqladmin -u xxxx -pxxxx create drupal-db
echo "mysql install"
mamp-mysql -pxxxx drupal-db < ~/Downloads/2012-08-21-_16-22-31_-drupal-db.sql
echo "Enable garland"
drush pm-enable garland --y
echo "Set garland as default and admin theme"
drush vset theme_default garland
drush vset admin_theme garland
echo "set maintenance mode to on"
drush vset maintenance_mode 1
echo "set private path"
drush vset file_directory_path "sites/default/files/private"
echo "export active modules to text file"
drush pml --no-core --type=module --status=enabled --pipe > /Volumes/client/supporting-files/d6modules.txt 
echo "disable non-core modules"
cat /Volumes/client/supporting-files/d6modules.txt | xargs drush --y dis 
echo "done"

Closing as there doesnt seem to be an issue here, just support requests of the same thing over and over. The 'solution' was posted many times, such as #47.

Also drop those indexes before running the upgrade if they are a problem

Still I am getting below error:
DatabaseSchemaObjectExistsException: Cannot add field url_alias.source: field already exists. in DatabaseSchema_mysql->addField()

Upgrade 6.23 to 7.0

please help. give me some steps for solutions. urgent

You're supposed to upgrade to the latest D7, not 7.0.

I have been upgrading my multi-site Drupal from Drupal 5 to Drupal 7 in a VM on FreeBSD 9.1 with PostgreSQL 9.0 in a VM (for dev purposes only, final destination is a barebones remote server)

OS: FreeBSD 9.1
Apache 22-2.2.23
PHP 5.2.17_12
PostgreSQL 9.0.10

I upgraded both sites from Drupal 5 to Drupal 6 and had to do some manual data fixes to put the blocks back where they were before. The theme was set to Garland for both.

When I run update.php for the default site, it seems to run through without an issue. When I run update.php for the secondary site it never gets to the point of being able to run it.

DatabaseSchemaObjectExistsException: Cannot add index <em class="placeholder">system_list</em> to table <em class="placeholder">system</em>: index already exists. in DatabaseSchema_pgsql->addIndex() (line 481 of /usr/local/www/drupal5/includes/database/pgsql/schema.inc).
The website encountered an unexpected error. Please try again later. 

The secondary site has a prefix set to the table names, while the default does not.

The system table does not have an index called "system_list" but does have "system_system_list_idx" which I don't know if it is the same.

 \d system
                              Table "public.system"
     Column     |          Type          |               Modifiers
----------------+------------------------+----------------------------------------
 filename       | character varying(255) | not null default ''::character varying
 name           | character varying(255) | not null default ''::character varying
 type           | character varying(12)  | not null default ''::character varying
 status         | integer                | not null default 0
 bootstrap      | integer                | not null default 0
 schema_version | smallint               | not null default (-1)
 weight         | integer                | not null default 0
 info           | bytea                  |
 owner          | character varying(255) | not null default ''::character varying
Indexes:
    "system_pkey" PRIMARY KEY, btree (filename)
    "system_system_list_idx" btree (status, bootstrap, type, weight, name)
    "system_type_name_idx" btree (type, name)

NOTE: I am not sure what you mean by dropping the database and how this is done without losing all of the content and settings. I do have dumps of the database.

Drop the index system_list (not table, index) before you begin the D7 upgrade. Or manually create the index D7 wants. Or ignore the error.

I don't see "system_list" above as an index for the table. So I tried dropping it blindly with DROP INDEX system_list; and get

# DROP INDEX system_list;
ERROR:  index "system_list" does not exist

So do I need to run instead

DROP INDEX system_system_list_idx;

Is that the same thing?

A bit unsettling that broken = 'works as designed'.

Core doesn't upgrade itself at all. The available migrate tools somewhat kinda half migrate some stuff.

I guess a lot of people are starting from scratch or making consultants happy.

These are dark days Drupaleers.

i am upgrading locally from drupal 6.34 to drupal 7.1 and getting similar error : DatabaseSchemaObjectExistsException: Cannot add index system_list to table

For anyone who stumbles across this via a search, note that the issue from #25:

I tried again using another machine (ubuntu server 10.04 with pdo 5.1.41) and got:

PDOException: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'weight' at row 1: UPDATE {system} SET weight=:db_update_placeholder_0 WHERE (filename = :db_condition_placeholder_0) ; Array ( [:db_update_placeholder_0] => 1000 [:db_condition_placeholder_0] => profiles/standard/standard.profile ) in system_update_files_database() (line 2233 of /var/www/modules/system/system.module).

is likely caused by #1007038: profiles/standard update query blocks 6 to 7 update if the site started on Drupal 4.6 or earlier (PDOException: SQLSTATE[22003]: Numeric value out of range).