I tried doing a CCK update, and got quite a few database update errors. Parsing through them, here are what appeared to be the root causes:

  • The MySQL 'TEXT' type cannot have a DEFAULT value (other than NULL) (http://dev.mysql.com/doc/refman/5.1/en/data-type-defaults.html). This broke update 6000, which broke everything related to updating the node_field table.
  • The cache_content table is missing a column. The 'serialized' column appears to be new with Drupal 6, and the cache_content table isn't being updated with the update.php script. I do know, however, that everything works fine here when installing from scratch.

I've updated the content.install file to fix these two problems. I've only modified the content_update_6000 function. Now it looks like I need to investigate the node_revisions.install file...

-Fractile81

CommentFileSizeAuthor
content.install.txt9.95 KBfractile81

Comments

karens’s picture

karens commented
Title: Update to Drupal 6 Fails » Update problem: MySQL 'TEXT' type cannot have a DEFAULT value (other than NULL)

The problem with text fields might be the core bug reported at http://drupal.org/node/195169.

The update to add the extra column to the cache table actually needs another fix. That table will be unusable until this update runs, so we also need to set the schema version variable to know if it has run or not and return 'cache' instead of 'content_cache' as the table name to use for the cache in content_cache_tablename() so that table is not used until it is fixed. I am committing that update now.

Leaving this open and renaming it for the remaining issue, which still needs investigation.

fractile81’s picture

fractile81 commented

It looks like the issue you linked to has been resolved as far as Drupal 6 is concerned. It appears they've gone the route of a schema not defining the default for a TEXT/BLOB field, and making the code auto-populate the defaults as needed. Does this also apply to my other defaults-related bug?

I've been playing with trying to get this to work in a clean way with content_update_6000();, and I haven't been successful. Changing the columns field to not have a default throws an error ('Data truncated', MySQL Errno #1265) because it's still not supposed to be NULL. Removing the final 'NOT NULL' requirement allowed the table to be created, and could easily be adjusted at that point. As the DB API works today, it doesn't look like a single db_add_field(); will work for that field.

There's also a new problem now on updating with the content_install_types(); function. Because I'm updating without any other CCK modules installed yet, the $module_field_types and $module_widgets arrays aren't being created at all, throwing a ton of notices and warnings on lines 32 and 39. These could benefit from checking to see if those variables aren't empty. The content_field_instance_expand(); function in content_crud.inc, line 110, is also throwing a lot of notices because the $widget_types array isn't populated completely, and would also benefit from some form of check.

fractile81’s picture

fractile81 commented

Alright, here are the steps I've taken to try and get CCK to update for me with the HEAD build from Dec 16:

  1. Updated content_update_6000(); to not set the columns field in the node_field table to have a DEFAULT value, and to accept NULL values.
  2. Initialized the $module_field_types and $module_widgets variables to be empty arrays.
  3. Added a check around line content_crud.inc:110 to make sure $widget_types[$field['widget_type']] is set.
  4. Fixed text_update_6001(); to reference $db_info['table'] instead of $table, as the queries on lines 107 and 110 weren't working correctly since $table wasn't instantiated. Also added [] after the $ret variable on both lines so that they don't destroy the SQL log.

I'm listing these because I can't make a patch, and because I'd rather these steps be reviewed for soundness. It's worth noting that I had to drop any custom indexes on TEXT fields I'd made in the content tables, or else it'd get a "BLOB/TEXT 'X' used in key specification without a key length" error.

It appears that update.php now lists all modules available for updates, regardless of whether they're enabled or not. In testing the update process in various ways, the CCK-field/widget module updates didn't appear to work properly without the individual module being enabled. No surprise there, just wanted to pass that information along.

Finally, I'm having problems with the text.install file right now, specifically text_update_6001();. This runs too long for me (I have a rather large content field, both data-wise and field-wise), causing a PHP timeout. I'm not sure how to fix this, other than spreading it out across updates. On the bright side, I was able to figure out why my content fields were breaking in D6 (see node #200359 -- I'll cross-post this information). Apparently the db_field_set_no_default(); call is breaking the fields. I was able to confirm commenting this line out in nodereference.install allowed me to insert data without defining all fields.

I realize this is a lot of information. Should any of this be spun-off into its own issue?

yched’s picture

yched commented

I committed a fix for points 2 and 4 above.
Still pondering 1 and 3...

And, right, maybe the separate issues (text_update_6001 timeout, db_field_set_no_default(), etc, could use their own separate threads ?

fractile81’s picture

fractile81 commented

Just wanted to mention that I did another update on the last nightly (Feb 10th, 2008) and still have this problem. Here's some update output:

The following queries were executed
content module
Update #6000

    * ALTER TABLE {node_field} ADD `module` VARCHAR(127) NOT NULL DEFAULT ''
    * Failed: ALTER TABLE {node_field} ADD `db_columns` TEXT NOT NULL DEFAULT ''
    * ALTER TABLE {node_field} ADD `active` TINYINT NOT NULL DEFAULT 0
    * ALTER TABLE {node_field_instance} ADD `widget_module` VARCHAR(127) NOT NULL DEFAULT ''
    * ALTER TABLE {node_field_instance} ADD `widget_active` TINYINT NOT NULL DEFAULT 0
    * DROP TABLE {cache_content}
    * CREATE TABLE {cache_content} ( `cid` VARCHAR(255) NOT NULL DEFAULT '', `data` LONGBLOB DEFAULT NULL, `expire` INT NOT NULL DEFAULT 0, `created` INT NOT NULL DEFAULT 0, `headers` TEXT DEFAULT NULL, `serialized` SMALLINT NOT NULL DEFAULT 0, PRIMARY KEY (cid), INDEX expire (expire) ) /*!40100 DEFAULT CHARACTER SET UTF8 */
    * DELETE FROM {cache}

This was only updating the base content.module file. I haven't tested updating the other modules yet.

fractile81’s picture

fractile81 commented
Component: General » upgrade path

Changing the component.

Oh, and mentioning that what I outlined above (removing the DEFAULT value and the NOT NULL = TRUE setting) is what I had to do to get this to work again.

karens’s picture

karens commented
Version: 6.x-1.x-dev » 6.x-2.x-dev

Bringing this forward. Is this still applicable?

willwade’s picture

willwade commented

Just to say - I ran this again tonight with 6.x-2.0-rc6 and it failed with the same errors as at: http://drupal.org/node/256073
My guess is that somewhere in the upgrade path Ive missed an upgrade.. I'm guessing this is not common.

Wierdly this time I'm actually getting two sets of errors (they repeat for each of my content types):

* user warning: Unknown column 'nf.active' in 'where clause' query: SELECT * FROM node_field_instance nfi JOIN node_field nf ON nfi.field_name = nf.field_name WHERE nf.active = 1 AND nfi.widget_active = 1 AND nfi.type_name = 'blog' ORDER BY nfi.weight ASC, nfi.label ASC in /home/willwade/public_html/sites/all/modules/cck/includes/content.crud.inc on line 455.

and the one I think which is more related to the above:

* user warning: Unknown column 'widget_module' in 'field list' query: UPDATE node_field_instance SET widget_module = 'text', widget_type = 'text_textfield' WHERE field_name = 'field_url' AND type_name = 'content_ot_courses' in

Any suggestions on fixing this manually?

yched’s picture

yched commented

We still have problems when text.module updates are ran before content.module updates.
This would exactly produce the errors in #256073: text module Update #6000 failed
We'd probably need the text update to '#abort' if content updates haven't been run.

I'm not sure I see how this case (text updates running before content updates) would cause the 'active' and 'widget_module' columns to be missing from {node_field_instance}...

willwade : meanwhile, the manual fix requires you look at those columns definitions in content.install's content_schema(), and create them manually in phpMyadmin or equivalent.

yched’s picture

yched commented
Priority: Normal » Critical

"We'd probably need the text update to '#abort' if content updates haven't been run."

We need to do that before a release.

yched’s picture

yched commented
Status: Active » Fixed

It's actually not just text updates. noderef, for instance, has updates calling content_clear_type_cache(), which breaks badly if content updates haven't run yet.
I added a check in all helper modules updates (including the ones that do not striclty need it, just for consistency)

yched’s picture

yched commented

Side note : what's the best way to publicly advise all contrib field modules to do the same ?

karens’s picture

karens commented

This might be important enough to post to the developers list. You can refer them to the way you handled it in one of the core files as an example of what they need to do.

yched’s picture

yched commented

Right - I opened a separate (fixed) 'task', and advertised it on dev ML and g.d.o/cck : #304813: CCK module developers read this!!

cpelham’s picture

cpelham commented

I also just upgraded from Drupal 5.7 to 6.4 and then from CCK 5.x-1.9 to 6 rc6 and got the errors such as:

Failed: UPDATE {node_field_instance} SET widget_module = 'text', widget_type = 'text_textfield' WHERE field_name = 'field_url' AND type_name = 'content_ot_courses'

and

user warning: Unknown column 'nf.active' in 'where clause' query: SELECT * FROM node_field_instance nfi JOIN node_field nf ON nfi.field_name = nf.field_name WHERE nf.active = 1 AND nfi.widget_active = 1 AND nfi.type_name = 'blog' ORDER BY nfi.weight ASC, nfi.label ASC in /home/willwade/public_html/otmatters/sites/all/modules/cck/includes/content.crud.inc on line 436.

I have read through this thread but do not recognize what I am supposed to do to resolve this. Cann anyone state the solution more clearly for me? Do I need to roll my system back and go through the upgrade again but this time with the lastest CCK 6 dev version instead?

yched’s picture

yched commented

"Do I need to roll my system back and go through the upgrade again but this time with the lastest CCK 6 dev version instead?"

Unfortunately, yes, it's the safest thing to do :-(

[edit : if by any chance you made an intermediate backup of the db after the core D6 upgrade but before the CCK upgrade, you can start from there, though.
If you didn't - well, do it next time, it's always a good idea ;-) ]

schildi’s picture

schildi commented

In my case (upgrading form D5.10 0> D6.4) module "text" is not installed/activated prior to cck (but views is installed).
And I get the same message as mentioned above when trying to install cck (cck-6.x-2.x-dev.tar.gz from 12. September):

user warning: Unknown column 'nf.active' in 'where clause' query: content_field_instance_read /* drupaladmin : content_field_instance_read */ SELECT * FROM node_field_instance nfi JOIN node_field nf ON nfi.field_name = nf.field_name WHERE nf.active = 1 AND nfi.widget_active = 1 AND nfi.type_name = 'adresse' ORDER BY nfi.weight ASC, nfi.label ASC in /srv/www/htdocs/drupal/sites/all/modules/cck/includes/content.crud.inc on line 455.

and

user warning: Unknown column 'nf.active' in 'where clause' query: content_schema /* drupaladmin : content_schema */ SELECT * FROM node_field_instance nfi LEFT JOIN node_field nf ON nf.field_name = nfi.field_name WHERE nf.active = 1 AND nfi.widget_active = 1 in /srv/www/htdocs/drupal/sites/all/modules/cck/content.install on line 183.

What can be done else to solve the problem?

yched’s picture

yched commented

OK - this thread has seen several evolutions and it is starting to get confused. i suggest we deal with this in a "fresh" issue :
http://drupal.org/node/307908

schildi : can you also provide the additional informations I ask over in my comment over there ?

Anonymous’s picture

Anonymous (not verified) commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.