"Column 'changed' cannot be null query" watchdog error when attempting 'Re-index all content' when using MySQL InnoDB

Correction: Indexing of new nodes is working, it's just that some recent nodes are missing. I'm still unable to re-index the site without the same error occurring.

If I run the query below on my production MySQL database, which is using InnoDB, I get the error "#1048 - Column 'changed' cannot be null". The same query on my test MySQL database, which is using MyISAM apparently returns an 'empty set'. When I change my test database to Innodb, I get the same error. These are the lines along which I am troubleshooting this issue.

Here is the query:

INSERT INTO `apachesolr_search_node` (nid, status, changed)
SELECT n.nid, n.status, GREATEST(n.created, n.changed, c.last_comment_timestamp) AS changed
FROM `node` n
LEFT JOIN `node_comment_statistics` c ON n.nid = c.nid

This has been an issuing in MySQL in years past, but everything I'm finding suggests that all reported bugs related to this condition are resolved. I'm running MySQL 5.1.37, which is only a few months old as of this writing.

Here are the contents of my my.ini (sans comments with the disk paths sanitized):
[client]
port=3306

[mysql]
default-character-set=latin1

[mysqld]
port=3306
bind-address=127.0.0.1
basedir="[path-snip]/mysql/"
datadir="[path-snip]/mysql/Data/"
default-character-set=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=150
max_user_connections = 150
max_connect_errors = 1000
query_cache_size=16M
table_cache=1024
interactive_timeout = 25
wait_timeout = 1800
tmp_table_size=39M
thread_cache_size=286
join_buffer_size = 2M
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=77M
key_buffer_size=63M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=3M
innodb_additional_mem_pool_size=2551K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1250K
innodb_buffer_pool_size=121M
innodb_log_file_size=61M
innodb_thread_concurrency=8
set-variable = lower_case_table_names=0

Note that I updated the title to reflect my findings.

Any ideas?

Mike Hays

Ok, so I'm grokking what is going on here. The bug is deeper than I thought. It has little to do with InnoDB, it just turns out that InnoDB doesn't deal with what is going on as "well" as MyISAM.

The following query:

INSERT INTO `apachesolr_search_node` (nid, status, changed)
SELECT n.nid, n.status, GREATEST(n.created, n.changed, c.last_comment_timestamp) AS changed
FROM `node` n
LEFT JOIN `node_comment_statistics` c ON n.nid = c.nid

Always returns NULL for changed on my site, as I don't have comments enabled. So a query for last_comment_timestamp in node_comment_statistics will always return NULL, and as of MySQL 5.0.13 GREATEST will return NULL if anything it evaluates against IS NULL (c.f., http://bugs.mysql.com/bug.php?id=15610).

Since comments aren't required for Drupal, we need to handle this in the re-index code.

Here is my suggestion:

INSERT INTO `apachesolr_search_node` (nid, status, changed)
SELECT n.nid, n.status, GREATEST(n.created, n.changed, IFNULL(c.last_comment_timestamp,0)) AS changed
FROM `node` n
LEFT JOIN `node_comment_statistics` c ON n.nid = c.nid

In the above case, if node_comment_statistics changed is NULL, it will be set to zero, which will always evaluate as less than node created or changed.

Thoughts?

The attached patch makes the changes proposed in #3.

Very good. I reverted my changes from #4, applied the patch in #7, and my test site re-indexes as expected. I then enabled the Comment module, confirmed that the 'node_comment_statistics' table last_comment_timestamp column is now populated (it is), and re-indexed the site with no errors.

Looks like a winner to me.