I have a client site running version 4.6.5. I need to upgrade their Drupal install to make use of some new modules that do not work with their current version. I would like to upgrade to 4.7.5.

I tried going directly from 4.6.5 to 4.7.5 but that produced a large list of SQL errors and failed upgrade messages.

I restored the database and php files from backups.

I then tried going from 4.6.5 to 4.6.11. That seemed to work. I then tried upgrading from 4.6.11 to 4.7.5 but that produced a large list of SQL errors and failed upgrade messages.

Again I restored the database and php files from backups.

I then tried upgrading to each individual revision step by step, all the way from 4.6.5 to 4.7.5. When I get to the 4.6.11 to 4.7.0 upgrade I get a large list of SQL errors and failed upgrade messages:

* user warning: Table 'search_total' already exists query: CREATE TABLE search_total ( word varchar(50) NOT NULL default '', count int(10) unsigned default NULL, PRIMARY KEY word (word) ) in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Can't DROP 'path'; check that column/key exists query: ALTER TABLE blocks DROP path in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Duplicate column name 'visibility' query: ALTER TABLE blocks ADD visibility tinyint(1) NOT NULL in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Duplicate column name 'pages' query: ALTER TABLE blocks ADD pages text NOT NULL in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Multiple primary key defined query: ALTER TABLE sessions ADD PRIMARY KEY sid (sid) in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Table 'flood' already exists query: CREATE TABLE flood ( event varchar(64) NOT NULL default '', hostname varchar(128) NOT NULL default '', timestamp int(11) NOT NULL default '0' ); in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Duplicate column name 'aid' query: ALTER TABLE accesslog ADD aid int(10) NOT NULL auto_increment, ADD PRIMARY KEY (aid) in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Table 'queue' already exists query: CREATE TABLE queue ( nid int(10) unsigned NOT NULL, uid int(10) unsigned NOT NULL, vote int(3) NOT NULL default '0', PRIMARY KEY (nid, uid) ) in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Unknown column 'votes' in 'field list' query: SELECT nid, votes, score, users FROM node in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Can't DROP 'votes'; check that column/key exists query: ALTER TABLE node DROP votes in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Can't DROP 'score'; check that column/key exists query: ALTER TABLE node DROP score in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Can't DROP 'users'; check that column/key exists query: ALTER TABLE node DROP users in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Table 'vocabulary_node_types' already exists query: CREATE TABLE vocabulary_node_types ( vid int(10) NOT NULL default '0', type varchar(16) NOT NULL default '', PRIMARY KEY (vid, type)) in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Unknown column 'nodes' in 'field list' query: SELECT vid, nodes FROM vocabulary in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Can't DROP 'nodes'; check that column/key exists query: ALTER TABLE vocabulary DROP nodes in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Unknown table 'page' query: DROP TABLE page in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Duplicate column name 'types' query: ALTER TABLE blocks ADD types text in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Duplicate column name 'module' query: ALTER TABLE vocabulary ADD module varchar(255) NOT NULL default '' in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Unknown column 'voters' in 'poll' query: ALTER TABLE poll CHANGE voters polled longtext in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.
* user warning: Multiple primary key defined query: ALTER TABLE term_node ADD PRIMARY KEY (tid,nid) in /home/greg/dev/drupal/includes/database.mysql.inc on line 120.

And I get a bunch of failed upgrade messages:

The following queries were executed
system module
Update #110

* DROP TABLE {search_index}
* CREATE TABLE {search_index} ( word varchar(50) NOT NULL default '', sid int(10) unsigned NOT NULL default '0', type varchar(16) default NULL, fromsid int(10) unsigned NOT NULL default '0', fromtype varchar(16) default NULL, score int(10) unsigned default NULL, KEY sid (sid), KEY fromsid (fromsid), KEY word (word) )
* Failed: CREATE TABLE {search_total} ( word varchar(50) NOT NULL default '', count int(10) unsigned default NULL, PRIMARY KEY word (word) )
* Failed: ALTER TABLE {blocks} DROP path
* Failed: ALTER TABLE {blocks} ADD visibility tinyint(1) NOT NULL
* Failed: ALTER TABLE {blocks} ADD pages text NOT NULL
* DELETE FROM {variable} WHERE name = 'node_cron_last'
* UPDATE {blocks} SET status = 1, custom = 2 WHERE status = 0 AND custom = 1

Update #111

* DELETE FROM {variable} WHERE name LIKE 'throttle_%'
* Failed: ALTER TABLE {sessions} ADD PRIMARY KEY sid (sid)

Update #112

* Failed: CREATE TABLE {flood} ( event varchar(64) NOT NULL default '', hostname varchar(128) NOT NULL default '', timestamp int(11) NOT NULL default '0' );

Update #113

* Failed: ALTER TABLE {accesslog} ADD aid int(10) NOT NULL auto_increment, ADD PRIMARY KEY (aid)

Update #114

* Failed: CREATE TABLE {queue} ( nid int(10) unsigned NOT NULL, uid int(10) unsigned NOT NULL, vote int(3) NOT NULL default '0', PRIMARY KEY (nid, uid) )
* Failed: ALTER TABLE {node} DROP votes
* Failed: ALTER TABLE {node} DROP score
* Failed: ALTER TABLE {node} DROP users

Update #115

* No queries

Update #116

* DELETE FROM {system} WHERE name = 'admin'

Update #117

* Failed: CREATE TABLE {vocabulary_node_types} ( vid int(10) NOT NULL default '0', type varchar(16) NOT NULL default '', PRIMARY KEY (vid, type))

Update #118

* Failed: ALTER TABLE {vocabulary} DROP nodes

Update #119

* No queries

Update #120

* No queries

Update #121

* Failed: DROP TABLE {page}

Update #122

* Failed: ALTER TABLE {blocks} ADD types text

Update #123

* Failed: ALTER TABLE {vocabulary} ADD module varchar(255) NOT NULL default ''
* UPDATE {vocabulary} SET module = 'taxonomy'
* UPDATE {vocabulary} SET module = 'forum' WHERE vid = 2

Nothing after Upgrade #123 contains any failed messages so I did not include it.

I've wasted nearly half a day and didn't even get the site upgraded. Where do I go from here?

Comments

styro’s picture

you'd get when trying to run the upgrade a second time. ie most of them sound like trying to change things that have already been changed.

Have you tried running the site on 4.6.11 or 4.7.0 to see which one works?

I don't really have many other ideas though.

--
Anton
New to Drupal? | Forum posting tips | Troubleshooting FAQ

cog.rusty’s picture

The 'search_total' table was introduced more than 2 years ago, definitely before 4.6. See

http://cvs.drupal.org/viewcvs/drupal/drupal/database/database.mysql?hide...

Why the hell is it trying to create it now during the upgrade? You didn't select the updates to run manually, did you?

---------

Edited to add: Starting from 4.6.5, it seems that the updates should start from 132. See

http://cvs.drupal.org/viewcvs/drupal/drupal/database/updates.inc?rev=1.1...

oniryx’s picture

Hi, here is how I manage to do a similar upgrade without all those red messages.

1. untar/unzip the drupal 4.7.5 package
2. create a contrib directory under modules *
3. untar/unzip all contrib modules for version 4.7 that are used for 4.6 *
4. check in every contrib modules if there is an UPDATE.txt or UPGRADE.txt, read them
5. copy the sites/default/settings.php 4.6 to 4.7
6. run the http://www.myhost.com/update.php

* make sure to have the correct permission on each of those files

mlncn’s picture

I had essentially the same problem, except that it was a clean install of 4.7.6 -- only thing special about it was it is on a multisite with a site that had existed, and I don't know it's full past-- but that site, which I upgraded from 4.7.2 with all its modules, did so with no errors whatsoever.

At least I knew what the problem probably was thanks to this thread.

So I asked, what update number should Drupal database 4.7.6 have?

The answer is 184 for "system", according to the fat and happy main Drupal site.

For some reason my sister site for Subsite -- a completely fresh 4.7.6 install, but with the shared tables deleted -- is trying to update itself from update version number 110 – the very first possible for the system component.

The symptoms:

Just on going to http://subsite.example.org/update.php?op=selection, I get this (note that even though the directory says drupal-4.7.2, it is in fact 4.7.6 now. And again, the site that actually updated is fine; the the fresh site to share a couple tables thinks it has a disaster, and so it has one):

* user warning: Duplicate column name 'severity' query: ALTER TABLE subsite_watchdog ADD severity tinyint(3) unsigned NOT NULL default '0' in /var/www/html/drupal-4.7.2/includes/database.mysql.inc on line 121.
* user warning: Duplicate column name 'referer' query: ALTER TABLE subsite_watchdog ADD COLUMN referer varchar(128) NOT NULL in /var/www/html/drupal-4.7.2/includes/database.mysql.inc on line 121.
* user warning: Duplicate column name 'cache' query: ALTER TABLE sessions ADD cache int(11) NOT NULL default '0' AFTER timestamp in /var/www/html/drupal-4.7.2/includes/database.mysql.inc on line 121.

I have two outputs of the errors I get when I try to update where it lists "Select versions" for system module and politely, and horribly wrongly, suggests 110.

This time I will try telling it in no uncertain terms, No updates available, and pressing update.

It did nothing, exactly as hoped for.

Now I will studiously ignore those three errors and continue on my merry way.

Went back to http://subsite.example.org/update.php?op=selection it now agrees, no updates available. So far, so good.

If anyone knows what could cause this, I'd love to know. It is not a contributed module visible only to Subsite. I tried deleting all of those

There was a duplicate Taxonomy.module (got moved to modules/contributed before the upgrade) -- but again, that's visible to the existing site and it upgraded just fine.
~ben

People Who Give a Damn :: http://pwgd.org/ :: Building the infrastructure of a network for everyone
Agaric Design Collective :: http://AgaricDesign.com/ :: Open Source Web Development

benjamin, Agaric

DJoh’s picture

I think your analysis of the problem is correct.

I'm trying to go from a 4.6.11 site, which is happy as a clam, to 4.7.anything. I first tried 4.7.10, but it barked errors, and then (after fully restoring) to 4.7.0 and in BOTH cases it's suggested DB update point was 110.

Example of errors:
user warning: Table 'search_total' already exists query: CREATE TABLE search_total ( word varchar(50) NOT NULL default '', count int(10) unsigned default NULL, PRIMARY KEY word (word) ) in /home/.gateaux/sarahsb/test.SarahHouseSB.org/includes/database.mysql.inc on line 120.
and 20 more like it.

I guess it's picking the wrong upgrade point for me too.
Now where the heck do I find out what system version number I should use? been searching for an hour now....

DJoh’s picture

since my CHANGELOG states that Drupal 4.6.0 came out on 4-15-2005, then
"2005-04-14" => "update_129",
"2005-05-06" => "update_130",
"2005-05-07" => "update_131",
"2006-12-19" => "update_132",
(from database/updates.inc)
shows i should start from System Module Update Version # 129???
(it does show that 110 is the first Drupal 4.5.0 version, so for some reason that's where it's defaulting to)
here goes...

DJoh’s picture

nope, that's not it either. whoever decided to use huge integers as version numbers (or whoever didn't make their upgrade script frendly) is making my life very difficult.

I had similar massive problems with the update.php script when going from 4.5.x to 4.6.x (the watchdog 'severity' entry problem [ http://drupal.org/node/19095#comment-35362 ] ... hilarious)

jpr’s picture

FWIW, I had a similar problem trying to go from 4.6.0 to 4.7.0 or anything beyond. Basically the upgrade broke at the 4.7.0 boundary.

I resolved the problem by first upgrading to 4.6.6, the last 4.6.x release prior to the 4.7.0 release and then upgrading to 4.7.0 and then going from there to the latest 4.7.6 (at this time).

I wasn't able to debug the problem well because there just weren't any errors to debug. The upgrade progress bar just "jumped" from 0% to 100% and didn't show any upgrade output. Once I did the 4.6.6 direct to 4.7.0 upgrade the progress bar showed intermediate progress and the upgrade largely succeeded. There were a couple of table alterations that failed, but only because they had already been performed on the database. In other words, they were false alarms.

cog.rusty’s picture

It makes sense when stepping between major versions to use their latest minor releases to be safe, since minor releases contain bug fixes. That is, 4.6.0 -> 4.6.6 => 4.7.7 => 5.2.

There is one point under discussion -- currently each new minor release announcement advices to upgrade to the previous minor release first, rather than jump into the new one. This may turn out "too cautious" or not.