No defaults with node_save();

Changing all fields to default to NULL values was a design decision for D6. The main reason for doing this was because we want to be able to do things like create EMPTY / NOT EMPTY Views filters and had no way to do it when we had some fields where empty is a blank and some where empty is zero and others where NULL is empty. The content module also uses this to do its new handling of multiple values.

We now use Schema API and drupal_write_record to do the inserts, and drupal_write_record checks the schema and automatically populates all the fields from the schema when it writes the record. So at this point, you must specifically supply values for all fields. I would have thought a value of NULL would be accepted, rather than triggering an error, though. This may be a quirk of using drupal_write_record with a schema that defines everything with NULL defaults.

Have you tried saving the node with drupal_execute() instead of node_save()? Let us know if that works better. You might have to poke around to get the parameters just right.

I think we need to investigate this if it behaves differently in D6 than it did in D5, but it raises a number of questions.

The real 'default values' are not the NULL values in the schema, but whatever default value you set up in the field settings, so the best fix would be to populate the field with those values. This is really an API issue though, because there is no problem when you create fields using the UI. In the UI when you create a new node the default values are automatically inserted, but I'm not sure how to translate that into a method that will work when you create fields programmatically.

The question is what really happened in D5 for those fields where you provided no value. Did they get an empty value or the default value you set up in the field settings? I assume it was the database default value not the field settings default value, which means it 'worked' (no errors), but not necessarily exactly correctly.

Just a quick response (I'll be digging into this some more), but I can tell you for certain that my problems were stemming from a query to the effect of INSERT INTO {content_type_xyz} (vid,nid,field_abc_value) VALUES (123,456,'789');, where there were multiple other fields in the Content Type's definition that all default to NULL. It's these other, unspecified values that were triggering the error I mentioned in the OP. I could re-execute the same query in MySQL Query Browser and get the same error. If it helps, I'm running MySQL 5.0, Drupal 6-beta4 (with a few tweaks), and yesterday's CCK nightly on HEAD.

@moshe: If I remember correctly, I did try to use the drupal_execute(); function, and the schema was not coming back correctly when I passed in the node form id ([CCK-type]_node_form). I had passed an empty array for $form_state, so if I need to change that, I can.

I was thinking about the CCK code before, but node_save also uses drupal_write_record, which uses the schema to supply any values you're missing, so I am starting to think that what is needed is a fix for drupal_write_record to do something more intelligent when there is no value supplied for a field with a NULL default value.

Just to be clear, what you did was to create an an empty node object, supply some field values but leave others undefined, and then do a node_save()?

Using drupal_execute might actually be the easiest way to fix this, if we can figure out the right way to call it, because if done right it should allow the content module to populate the default values in the form.

Here's some sample code for a content type representing an Organization (orgnode):

This worked in D5. As to whether it's a good way to do this...

Anyway, if we do go the route of using drupal_execute at some point (which wasn't working when I tested it, looked like the schema wasn't being loaded properly), it would be terribly nice to have a function with a friendlier/more intuitive name that calls drupal_execute (should it fix this).

As an aside, I can confirm this same problem is happening with shared tables as well. Also, your thought about what is the correct default in a CCK context is interesting, and I would *think* it should be from the CCK field's information and not the default NULL (unless that is the fields default).

I did think while writing drupal_write_record() that it would be nice if schema api let you specify a function for a default value instead of a static value. That function could get called (with what params?) when a default is needed (i.e. drupal_write_record()).

Anyway, I think the above is not likely to be fruitful. We have a couple of options

• use drupal_execute(), and fix any bug that is found there
• use content_nodeapi('pre_save') to populate $node object with defaults while smartly not interfering when user wanted to send empty values. the pre-save operation is now found at top of node_save().

#2 could be nice - using drupal_execute to programmatically create a node adds a lot overhead (not to say we shouldn't squash bugs we find there if any...). I worked on a site where we had to create lots of nodes on cron, using node_save was much faster / safer...

For those undefined fields, using field settings defaults instead of schema defaults would be nice (and ensure that drupal_execute and direct node_save produce consistent results...).

But aside from that, I'm not sure I get what is causing the errors fractile81 reports.
I have a content_type_story table, with a few fields stored in it.
Doing an INSERT INTO content_type_story( vid, nid ) VALUES ( 10, 10 ) raises no error for me, since all other columns accept null values...

Here's a real table that I'm having this problem with:

This query then fails for me with the original error:

INSERT INTO content_field_page_breadcrumb (vid,nid) VALUES (-1,-1);

If it helps any, the tables I'm using are updated from D5.

Just to keep thinking about this, I know that in D5 a node_load(); was called for an existing node, making sure old data wasn't wiped out just because it didn't exist. Was the Schema or Form APIs used to replace that in D6, and is that where we'd have to go to get the defaults (sounds like it's Schema API)? Is this something a hook_presave(); function could be used to fix?

Is this something hook_schema_alter(); could help with? I mean, should content.module have a content_schema_alter(); hook that can update a schema? That'd only be executed when there's a cache flush, so that could be forced when there's an update to content type.

It would be nice to not have to use drupal_execute(); since that enforces a form's required fields. That is, unless these requirements could be met with the CCK defaults before they'd be checked for in the form.

the whole point is that cck defaults would be passed into the form and would be there at time of required checking ... note that you can still form_alter to remove required fields.

@fractile81 -- I notice you have a text field in your example and we already know there can be special issues with text fields that have NULL values, so what system configuration are you using? Remove that field and try the same thing with other fields and see what happens. Your issue may really just be an issue for that data type.

As to requiring the use of drupal_execute(), I would prefer to find a way to do this that wouldn't require that since drupal_execute() is problematic in lots of ways. We're getting a lot of other new API capability in this version, perhaps we can work something in to make it easier to programmatically create nodes that have proper default values inserted if you don't supply anything.

I can confirm the same problem on a VARCHAR(10) and VARCHAR(255) field on a different table. These tables are updated from D5, and it makes me wonder if there's some table settings that prevent this from working properly?

I went back and tried playing with this a little. I've found that I can do an INSERT like I mentioned in #9 on the old, D5 table. A D5 and D6 DESCRIBE on the same table in #9 look identical, but I can consistently get the error with the D6 table.

Going further, I've found that doing a CREATE TABLE test SELECT * FROM content_field_page_breadcrumb, then trying to do an INSERT on the 'test' table still doesn't work. However, if I use:

from a dump of the troubled table, and then do an INSERT, the data will be added without any errors (I can have the same success on any other table I create by hand in MySQL). In fact, if I just do an ALTER ... MODIFY ... on each troubled VARCHAR/TEXT field (numerical fields don't appear to have this problem) and specify a new NULL default, it appears to fix the column. While this will fix my temporary problem, how would this apply to a CCK or Drupal upgrade?

So, this thread appears to span two different issues (which may not be CCK related, or even Drupal related?):

1. CCK default values aren't being used when creating an INSERT that lacks sufficient data
2. Somewhere in the upgrade process (CCK or Drupal, not sure which), my CCK tables have broken

Any thoughts or suggestions to try? I can try re-upgrading again with the most recent nightly if that might help.

@KarenS - I've given some general specs in #4 (MySQL 5.0, Drupal 6-beta4 (with a few tweaks), and the CCK nightly on HEAD from Dec 12th). I'm also running this all on PHP 5.2.5 in IIS 6. What else might be helpful to provide?

Not that this will come as any surprise, but I can confirm this problem when saving a node in general. For example, a content type with "multiple" checked and no data in the field when saved also causes this error. Here's the error:

If anyone has any ideas why my tables may have broken, that seems to be the biggest breaking point (and may be for anyone else that has this problem when upgrading). As an aside, I haven't defined anything in my CCK content types with a default. Might that also be a factor in this?

Are you running MySQL in strict mode ?

I believe so, yes:

> SELECT @@sql_mode;
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

While doing some testing with the CCK update process, I believe I figured out how my content tables were breaking (see http://drupal.org/node/198502#comment-664981). Basically, the db_field_set_no_default(); call is the culprit. I found that after running an update that used this function, the effected table would not let me INSERT as outlined earlier in this issue. However, if I suppressed the function call, I was able to do proper INSERTs after an update.

I'm not sure at what level this would need to be fixed. This is included in CCK for a reason, and any other module that uses this would end up causing the same problem, so simply omitting it wouldn't be a real fix. Should I spin this specific issue off into its own for the Schema API folks to look at?

I cannot seem to reproduce on my own setup (MySQL 5.0.51, strict mode enabled for testing) :
The db_field_set_no_default() call does output an error (BLOB/TEXT column 'field_text_value' can't have a default value query: ALTER TABLE content_field_text ALTER COLUMN field_text_value DROP DEFAULT, which sounds a bit silly), but I still can do inserts like INSERT INTO content_field_text (vid,nid) VALUES (100,100) after that...

Alright, I've created a separate issue for the database-level DEFAULT problem (http://drupal.org/node/206411).

Pulling this issue back on track, there's a problem doing a node_save(); where default values for CCK fields aren't being populated before INSERTing into the database. Using a node_save(); worked in Drupal 5, but those defaults were being handled at the database-level. There's an additional support issue regarding the use of node_save(); and it not working.

It sounds like moshe's comment in #7 about using the 'pre_save' hook might be a good way to go about populating these defaults. Is this the direction the solution should go?

As an side, we also have use cases where 'arbitrary' but still valid values should be returned, instead of 'defaults'. see http://drupal.org/node/187599?

I take it this was never addressed. I followed all the referenced topics and never found a way to make sure cck defaults were applied when saving a node with node_save().