I just upgraded comment notify on my Drupal 6.14 development server to 1.3 and now I get this error message everytime I want to be notified about a new comment in forum:

user warning: Field 'notified' doesn't have a default value query: INSERT INTO comment_notify (cid, notify, notify_hash) values (30, 1, 'e3452da90bfb761b3854fd813ba2d983') in C:\Inet\htdocs\drupal-6.14\sites\all\modules\comment_notify\comment_notify.module on line 259.

The database backend is MySql 5.1. Everything is running on Windows XP on my devel machine. I did run update after upgrading the module and it showed two changes to my database.

Files: 
CommentFileSizeAuthor
#29 639618_default_notified_7x.patch560 bytesgreggles
#29 639618_default_notified_6x.patch625 bytesgreggles
#9 639618_notified_warning_mysql.patch969 bytesgreggles
FAILED: [[SimpleTest]]: [MySQL] 59 pass(es), 1 fail(s), and 0 exception(es).
[ View ]

Comments

Title:user warning: Field 'notified' doesn't have a default value queryuser warning: Field 'notified' doesn't have a default value

Is no one else experiencing this problem?

I am not experiencing this. A bigger question is whether it causes any real bugs or if it is just a warning.

It might have something to do with the PHP error/warning settings. Possibly with less strict settings, you would not see the message - as it is probably the case with most production systems. On my devel system I use the most strict settings so I find any unclean coding in my own code ;-)

Generally I believe it should be fixed in the module, because the warning points to some slightly uncorrect coding: inserting a row in a database with no value for a specific field and no default value set for that field in the database definition.

It might be an issue on systems with strict warning settings where a user actually might get this message displayed - which then can be irritating.

Yes, that definitely makes sense. However if you look at the error it is a mysql error and looking at the query it is inserting into three columns including the notified column and it inserts three values.

You are right, the error is generated by the MySQL engine. If you look at comment_notify.module line 259, you'll see the following SQL command

<?php
db_query
("INSERT INTO {comment_notify} (cid, notify, notify_hash) values (%d, %d, '%s')", $comment['cid'], $notify, $notify_hash);
?>

But the structure of the comment_notify table shows four fields: cid, notify, notify_hash and notified

When you then compare the structure with the SQL statement you'll see that the field 'notified' is not inserted, hence the php warning.

I just found two other related issues:

#634136: Error: Field 'notified' doesn't have a default value and from that - #410358: database scheme update code (PgSQL).

So, this should be fixed if you are using 6.x-1.3 and have run update.php

Sorry to be so persistent but the issues you quoted relate to PgSQL and not MySql.

#410358 even says that it does not affect MySql. Alex_Tutubalin wrote on March 25, 2009 - 05:48 in that thread:

"MySQL adds DEFAULT 0 setting for NOT NULL columns itself (I've asked some MySQL gurus). So, my changes means nothing for MySQL sites "

I looked at the structure of my comment_notify table and there is no default setting for the field 'notified'. Hence the warning message.

So I guess somewhere on the way the table structure with MySQL is not set correctly.

I thought they related to MySQL as did the person in the issue, but your advice makes it clear that it affects MySQL at least some of the time as well. I'm open to a patch, but given that it's working OK for me now I'm not super motivated to make that patch.

But of course I would love to have this fixed - as you say it's a sign of sloppy code and should be fixed for a variety of reasons.

Status:Active» Needs review
StatusFileSize
new969 bytes
FAILED: [[SimpleTest]]: [MySQL] 59 pass(es), 1 fail(s), and 0 exception(es).
[ View ]

@ghowen - Any chance you could test this patch? Or provide a better one?

Status:Needs review» Needs work

The last submitted patch, 639618_notified_warning_mysql.patch, failed testing.

Status:Needs work» Needs review

#9: 639618_notified_warning_mysql.patch queued for re-testing.

Status:Needs review» Needs work

The last submitted patch, 639618_notified_warning_mysql.patch, failed testing.

Version:6.x-1.3» 6.x-1.4
Status:Needs work» Reviewed & tested by the community

I just had the same problem with version 6.x-1.4 and the attached patch fixed it. Thanks!

Status:Reviewed & tested by the community» Fixed

Thanks for the review, stella.

Now fixed http://drupal.org/cvs?commit=390490

This doesn't impact Drupal 7.

Stella, was this a big enough bug that you suggest making a new release? I'm not affected by it on my configuration so I'm not sure how bad it is.

If there are other changes coming then I'd wait for them before doing a release. If there are no plans to do a release any time soon, then yeah maybe do one alright. I don't think there's anything special about my configuration though. The error just appeared when I replied to a comment on a node which someone else created. However I'm not the only one working on this site so maybe changes to the default config were made. If you want me to check anything out for you, let me know. By the way, just in case it makes any difference, it happened in a MySQL server installed on Windows (sigh)

Perhaps this is just the default config for Mysql on windows?

Status:Fixed» Closed (fixed)

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

Version:6.x-1.4» 7.x-1.x-dev

I hate to reopen this one, but I got an error in a site I upgraded from D6 to D7.

PDOException: SQLSTATE[HY000]: General error: 1364 Field 'notified' doesn't have a default value: INSERT INTO {comment_notify} (cid, notify, notify_hash) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2); Array ( [:db_insert_placeholder_0] => 1400 [:db_insert_placeholder_1] => 0 [:db_insert_placeholder_2] => PsZPxE1yqm2RD1758BfYoAeZ_cwKsWYpTc-0G_tLWjE ) in comment_notify_add_notification() (line 143 of /home/tgeller/webapps/tomgeller/sites/all/modules/comment_notify/comment_notify.inc).

That's using the 7.x-dev version of comment_notify.

Good luck!

Status:Closed (fixed)» Needs work

Reopening.

I have this problem too

DOException: SQLSTATE[HY000]: General error: 1364 Field 'notified' doesn't have a default value: INSERT INTO {comment_notify} (cid, notify, notify_hash) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2); Array ( [:db_insert_placeholder_0] => 58839 [:db_insert_placeholder_1] => 0 [:db_insert_placeholder_2] => 0vgMCYoYhciISDJuNzafk-Z6S16VNSl1dnxzrMIqvRY ) в функции comment_notify_add_notification() (line 143 in file /sites/all/modules/comment_notify/comment_notify.inc).

I'm experiencing this on my D6->D7 upgrade right now. It prevents any comments from being successfully submitted.

I have no updates pending when I go to update.php either.

I'm using the latest 7.x-1.x-dev.

Here's the table schema as it is installed on my MySQL box. You can see it is missing default values. It seems this was changed during the life of comment_notify, but there was never an update.php function provided for the schema changes?

CREATE TABLE `comment_notify` (
  `cid` int(10) unsigned NOT NULL,
  `notify` tinyint(4) NOT NULL,
  `notify_hash` varchar(128) NOT NULL DEFAULT '' COMMENT 'A hash of unique information about the commenter.  Used for unsubscribing users.',
  `notified` tinyint(4) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `notify_hash` (`notify_hash`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `comment_notify_user_settings` (
  `uid` int(10) unsigned NOT NULL,
  `node_notify` tinyint(4) NOT NULL DEFAULT '0',
  `comment_notify` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Interestingly, the notify_hash's comment has been updated to the d7 version comment.

Obviously, disabling, uninstalling and re-installing the module fixes the issue since the database table is then created properly.

Priority:Normal» Critical

Critical since it breaks the comment submission.

@JohnAlbin, did you do all 6.x updates prior to updating to 7.x? And was this originally a 5.x install?

I have a vague memory there were some db updates that we dropped in the newer version much like core does.

did you do all 6.x updates prior to updating to 7.x? And was this originally a 5.x install?

I did do all the 6.x update prior to updating to 7.x. That's best practice! :-) Was it a 5.x install originally? Maybe, I can't remember. But I definitely didn't install comment notify until it was in its 6.x incarnation. My git logs show it was version 6.x-1.2 that was first installed on my site.

The site has moved hosting several times, however.

Hmm, ok.

The column was added in and it does seem that this update function was dropped for 7.x.

<?php
function comment_notify_update_6004() {
 
$ret = array();
 
db_add_field($ret, 'comment_notify', 'notified', array('type' => 'int', 'size' => 'small', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0));
 
// Set the value in the notified column to 1 for all existing records.
 
$ret[] = update_sql('UPDATE {comment_notify} SET notified = 1');
  return
$ret;
}
?>

That has a default 0 line in there which is the same as the hook_schema.

The bit about "originally from 6.x-1.2 was key to figuring this out. It seems that the stable 6.x-1.2 release didn't have the default but it was magically added later. Ugh.

So, I guess we need to fix the table definitions with http://api.drupal.org/api/drupal/includes--database.pgsql.inc/function/d...

We don't have any indexes so this should be a smallish fix.

Is there some easier way?

The bit about "originally from 6.x-1.2 was key to figuring this out.

Yay for git logs!

I've already done the work-around for the bug as I stated in comment #23 above, so I won't be able to test any code fixes.

Status:Needs work» Needs review
StatusFileSize
new625 bytes
new560 bytes

Ok, here are some patches for 6x and 7x. I will probably make a new 6.x after release this.

My only self criticism is that the update in 7.x might not be necessary for everyone and we could probably optimize that by checking if the column has a default before running the db_change_field. It feels like a micro-optimization unlikely to be worth the extra effort and potential for bugs...

In my brief test on a 6.x. vintage site that was affected by this bug it changed it to a default of zero.

I'm inclined to commit this but it would be great to get a review.

Status:Fixed» Closed (fixed)

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