Log field in {node_revisions} table has no default value

Is it possible you are running MySQL 5 in strict mode?

See http://drupal.org/node/43316 for more info.

Is there a reason why not all columns have a default value ?
I can write a patch to add default values..

seems the right thing to make it strict-mode compatible if it's not hard to do.

This error is caused by running MySQL 5 in strict mode.

BLOB/TEXT fields cannot have default values (see http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html).

Inserting a blank string seems to mess up the revision log, so maybe we should insert a NULL instead.

That comment is referring to a crazy bug dww found:

- If revisions are turned off for a node type...
- And there was a pre-existing revision log....
- And you edit the node and submit it again, leaving the message blank...
- Then, the existing message in database gets overwritten with a blank value.

So we need some way to have that NOT happen, but also fix this problem for strict mode compliance.

@webchick - the behavior you describe actually sounds correct. If I edit a node and put no log message, why would the old message still be relevant?

Because chances are you didn't erase the node log message, but rather just left it alone (it defaults to blank).

Maybe the solution is if the node has revisions turned off, then make the log message default to whatever is the log message in the database; else default to nothing (since it's really bloody annoying when you're using the node revisions system as intended and you need to erase the old log message before inputting a new one as you had to do in 4.6), and get rid of that extra logic in the SQL building stuff.

What would happen if we always appended a space to the end of the log field every time it gets saved?

*sigh* using the previously entered log message by default is a bug: http://drupal.org/node/39124
how's this?

Makes sense to me. Tested it and it appears to be working properly.

Hopefully someone can test it under strict mode to confirm it fixes the warning.

the old title made it seem like we were talking about the UI, in which case we're back to http://drupal.org/node/39124. just want to make it clear the bug here is at the SQL layer...

I have that bug on my system.

Description of my testing server : Windows XP, SP 2, Apache 2.0.59, PHP 5.1.4, MySQL 5.0.

I installed the beta 2 version from scratch. With a new database.

When I publish content, such as a "page", I get this message :

user warning: Field 'log' doesn't have a default value query: INSERT INTO node_revisions (nid, vid, title, body, teaser, timestamp, uid, format) VALUES (2, 2, 'Hello', 'blabla', 'blabla', 1165305156, 1, 1) in F:\htdocs\drupal-5.0-beta2\includes\database.mysql.inc on line 167.

Why is that a NEW bug ?

I have verion 5 beta 1 and version 4.7.4 working perfectly with mySQL set up the way it is now. Only beta 2 won't work.

How do I know if I am using MySQL in strict mode ? What is "strict mode" ?

It's not the only issue I have.

When I install beta 2 I get tons of warning messages. Then when I close the browser and reopen drupal I get the Welcome screen and I am able to register my admin and start off - but when I add content it does not work, as stated.

When I install I get this page of warnings (although the installation is said to be succesful) :

Warning: Field 'pages' doesn't have a default value query: INSERT INTO blocks (module,delta,theme,status) VALUES('user', 0, 'garland', 1) in F:\htdocs\drupal-5.0-beta2\includes\database.mysql.inc on line 167

Warning: Field 'pages' doesn't have a default value query: INSERT INTO blocks (module,delta,theme,status) VALUES('user', 1, 'garland', 1) in F:\htdocs\drupal-5.0-beta2\includes\database.mysql.inc on line 167

Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at F:\htdocs\drupal-5.0-beta2\includes\database.mysql.inc:167) in F:\htdocs\drupal-5.0-beta2\includes\bootstrap.inc on line 735

Warning: Cannot modify header information - headers already sent by (output started at F:\htdocs\drupal-5.0-beta2\includes\database.mysql.inc:167) in F:\htdocs\drupal-5.0-beta2\includes\bootstrap.inc on line 413

Warning: Cannot modify header information - headers already sent by (output started at F:\htdocs\drupal-5.0-beta2\includes\database.mysql.inc:167) in F:\htdocs\drupal-5.0-beta2\includes\bootstrap.inc on line 414

Warning: Cannot modify header information - headers already sent by (output started at F:\htdocs\drupal-5.0-beta2\includes\database.mysql.inc:167) in F:\htdocs\drupal-5.0-beta2\includes\bootstrap.inc on line 415

Warning: Cannot modify header information - headers already sent by (output started at F:\htdocs\drupal-5.0-beta2\includes\database.mysql.inc:167) in F:\htdocs\drupal-5.0-beta2\includes\bootstrap.inc on line 416

Warning: Cannot modify header information - headers already sent by (output started at F:\htdocs\drupal-5.0-beta2\includes\database.mysql.inc:167) in F:\htdocs\drupal-5.0-beta2\includes\bootstrap.inc on line 417
Drupal installation complete

* warning: Cannot modify header information - headers already sent by (output started at F:\htdocs\drupal-5.0-beta2\includes\database.mysql.inc:167) in F:\htdocs\drupal-5.0-beta2\includes\common.inc on line 141.
* warning: Cannot modify header information - headers already sent by (output started at F:\htdocs\drupal-5.0-beta2\includes\database.mysql.inc:167) in F:\htdocs\drupal-5.0-beta2\includes\common.inc on line 141.

Congratulations, Drupal has been successfully installed.

Please review the messages above before continuing on to your new site.

@Chill35: please open a seperate bug report for:

"Warning: Field 'pages' doesn't have a default value query: INSERT INTO blocks (module,delta,theme,status) VALUES('user', 0, 'garland', 1) in F:\htdocs\drupal-5.0-beta2\includes\database.mysql.inc on line 167"

Thanks.

Ok I did, Heine : http://drupal.org/node/101421

Tanks.

dww,

I am not sure if I DO run Mysql strict or not, do not know how to check that.

I installed the patch and it solved the problem. I am now able to create content that displays. (And no warning message when I create said content.)

Thanks.

I changed the status to RTBC, please change it back if you feel it needs more reviewing.

I find it strange that we have to use this technique. At other places in the code, this doesn't appear to be a problem. I wonder why that is.

When $revisions_table_values['log'] is empty, $revisions_table_types['log'] = "'%s'" should become $revisions_table_types['log'] = "''" which appears to be valid SQL, not?

Dries: The reason this is a problem here is that currently, appending log to the SQL string is in a conditional because of the bug described @ #5. We only want to add log to the SQL query when there's something to save.

However, new nodes when inserted always need to insert a value to get around strict mode compliance. Hence the addition of the $node->is_new check.

Oh hold on. Do you mean just this?

Could someone affected by this bug try this patch?

if that works for php5, even better. i've noticed that some of the code i used to generate causes warnings/errors on php5 (my test sites are all php4), and i don't have a mysql5/strict test site, either. so, i was just coding extra defensively, since i was basically operating in the dark. ;) but, if that's all bloat, and webchick's more simple patch (with the same basic logic: always include 'log' in the SQL if it's a new entry) causes no warnings, all the better.

thanks,
-derek

i guess we should bump this back to needs review, pending confirmation that the smaller patch still works in all configurations.

+1, this clears the issue up for me on PHP5.2

Tested under strict mode - works for me as well.

FYI - Here's some info on how to activate/deactivate MySQL strict mode.

Hey thanks for that, webernet!

Would you mind putting that in the handbook somewhere? Maybe in the setting up a test environment page?

Committed to CVS. Thanks.

i would like to open this issue again.

i just updated mysql from 4.1 to 5.0.27 and i began getting error messages becuase my sql mode is STRICT_TRANS_TABLES

the issue was easily fixed by making log to default to 'null' if nothing is passed.

the issue should be looked into more thoroughly because i see drupal has tendencies to store only the information that it is being passed, so without default values in the DB it will then fail. having drupal 'discover' which fields it must provide will allow it to populate some default fields.

ok, after clarification in IRC, this is indeed a bug. there's 1 case in the logic in node.module we missed: empty log message, new revision, editing an existing node. easy fix. patch coming momentarily, stay tuned.

this should fix it. applies cleanly to DRUPAL-5 and with offset to HEAD.

+1, i tested it on my site and everything worked great: RTBC

+1, i tested it on my site and everything worked great: RTBC

Committed to CVS HEAD. Needs to go into DRUPAL-5, Neil.

oh, and while we're on the topic... ;) how about we sync the mysql and pgsql schemas in this regard and give log a default off '' on mysql, just like we've had on pgsql since at least 4.7.x (that's as far back as i cared to check)? patch for HEAD (6.x) only, since a schema change is obviously out of the question for 5.x.

@drumm: patch #30 is for DRUPAL-5
@dries: how about this one for HEAD?

thanks folks,
-derek

@dww, because afaik you can't give default values to TEXT fields: http://dev.mysql.com/doc/refman/5.1/en/blob.html

"BLOB and TEXT columns cannot have DEFAULT values."

weird, when i ran my proposed update, mysql didn't give any warnings or errors at all. :( *sigh* alas. oh well, i guess forget about #34 then, and we should leave all the code to specify log in the query corrrectly.

so, all we need here is #30 committed to DRUPAL-5.

thanks,
-derek

Committed to 5.

I have the same problem on my "team editors." I solved this problem, when allowed to "administer nodes" for this role (my "team editor") on admin/ user/permissions.
This does not allow me to create hidden pages for the role "team editor". Indeed, the group "team editor" shows all the pages.

Someone knows how can I fix it?