XF 2.1 XF\Db\Exception: MySQL query error [1048]: Column 'result_cache_id' cannot be null

[rhakoome]

When people are trying to see tags in my forum (like https://www.hiking-site.nl/prikbord/index.php?tags/wildkamperen/page-2), they get an error-message which I see also in the logs (see below).

What can I do to asolve this?

Raymond

Stack trace
INSERT INTO xf_tag_result_cache (tag_id, user_id, expiry_date, results, result_cache_id, cache_date) VALUES (?, ?, ?, ?, ?, ?)
------------

#0 src/XF/Db/Mysqli/Statement.php(196): XF\Db\AbstractStatement->getException('MySQL query err...', 1048, '23000')
#1 src/XF/Db/Mysqli/Statement.php(77): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1048, '23000')
#2 src/XF/Db/AbstractAdapter.php(94): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(221): XF\Db\AbstractAdapter->query('INSERT INTO `x...', Array)
#4 src/XF/Mvc/Entity/Entity.php(1452): XF\Db\AbstractAdapter->insert('xf_tag_result_c...', Array, false)
#5 src/XF/Mvc/Entity/Entity.php(1184): XF\Mvc\Entity\Entity->_saveToSource()
#6 src/XF/Pub/Controller/Tag.php(149): XF\Mvc\Entity\Entity->save()
#7 src/XF/Mvc/Dispatcher.php(350): XF\Pub\Controller\Tag->actionTag(Object(XF\Mvc\ParameterBag))
#8 src/XF/Mvc/Dispatcher.php(261): XF\Mvc\Dispatcher->dispatchClass('XF:Tag', 'Tag', Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Tag), Object(XF\Mvc\Reply\Reroute))
#9 src/XF/Mvc/Dispatcher.php(113): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Tag), Object(XF\Mvc\Reply\Reroute))
#10 src/XF/Mvc/Dispatcher.php(55): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#11 src/XF/App.php(2184): XF\Mvc\Dispatcher->run()
#12 src/XF.php(391): XF\App->run()
#13 index.php(20): XF::runApp('XF\\Pub\\App')
#14 {main}

 

[Chris D]

I'm not 100% certain about this, but this may indicate that your database structure isn't as expected.

result_cache_id is the primary key of the table and so it can be null because it should be marked as auto_increment meaning a null value will be replaced with the AUTO_INCREMENT value for the table, usually the next ID.

Could you run the query SHOW CREATE TABLE xf_tag_result_cache and report back?

 

[rhakoome]

Hi Chris,

CREATE TABLE xf_tag_result_cache (​

result_cache_id int(10) unsigned NOT NULL,​

tag_id int(10) unsigned NOT NULL,​

user_id int(10) unsigned NOT NULL,​

cache_date int(10) unsigned NOT NULL,​

expiry_date int(10) unsigned NOT NULL,​

results mediumblob NOT NULL​

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ​

Raymond

 

[Chris D]

That would appear to prove what I was saying.

Have you ever restored this table or this database from a backup? This may be a sign of a much wider problem.

You can run a query to fix this table:

ALTER TABLE xf_tag_result_cache
MODIFY COLUMN result_cache_id INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT;

But really it would be useful to try and work out why this happened in the first place and whether other tables could be affected.

 

[rhakoome]

Chris,

That did the trick! And yes, it was copied from one server to another (previous version) and then upgraded to version 2.*

Thank you so much for your support!

Raymond

 

[Chris D]

Just that table or all tables? I'd be very concerned if all tables as to whether other primary/AI keys were missing.

Though if this is the only server error you were getting then perhaps it only applied to this table.

 

[rhakoome]

So far it seems the only one. Plus the one of emoticons returning erros, but that's because I did not run the command to change the database yet.

Thanks again!