Firefox PHP

Field 'recent_author' doesn't have a default value

Posted by Ulf Dunkel 
Field 'recent_author' doesn't have a default value
March 23, 2021 08:11AM
Hi folks,
I run Phorum 5.2.23 with mysql Ver 8.0.23 on Ubuntu 20.04.1 for Linux on x86_64 ((Ubuntu)).

Since my last Server update, I frequently run into issues when creating new topics:

Phorum Database Error
Sorry, a Phorum database error occurred.
Please try again later!
Error:
Field 'recent_author' doesn't have a default value (1364): INSERT INTO phorum_messages (forum_id, datestamp, thread, parent_id, author, subject, email, ip, user_id, moderator_post, status, sort, msgid, body, closed, moved, meta) VALUES (1, 1616483228, 0, 0, 'Ulf Dunkel', 'Titel des neuen Beitrags', '', 'port-92-201-89-222.dynamic.as20676.net', 2, 1, 2, 2, '98f60ee75991504e3708e69c575f5437.CalamusForumde', 'Test', 0, 0, 'a:1:{s:14:\"show_signature\";i:1;}')
Backtrace:
Function phorum_database_error called at
{path to Phorum}/include/db/mysql/mysqli.php:212
----
Function phorum_db_interact called at
{path to Phorum}/include/db/mysql.php:976
----
Function phorum_db_post_message called at
{path to Phorum}/include/posting/action_post.php:149
----
Function include called at
{path to Phorum}/posting.php:595
----

How can I fix this issue best?

Greetings, Ulf

Regards, Ulf Dunkel
Re: Field 'recent_author' doesn't have a default value
March 23, 2021 03:13PM
The short answer: It appears mysql is running in "strict mode", and will not take an empty string.

MODES... [dev.mysql.com]

There is an IGNORE if you are unable to turn off "strict mode"... [dev.mysql.com]

Would need to be added to the insert statement, however I would put it on its own line, with just the field you need to ignore, otherwise bad data could be inserted into any of those fields.
Re: Field 'recent_author' doesn't have a default value
March 24, 2021 11:32AM
Hi Scott, thank you for digging into this. What can I do myself to solve this problem?

Regards, Ulf Dunkel
Re: Field 'recent_author' doesn't have a default value
March 24, 2021 12:10PM
I have added

sql_mode = 'ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO'

to my mysqld.cnf file, thus removing the parameters STRICT_TRANS_TABLES + NO_ENGINE_SUBSTITUTION which were set before.

Now creating new topics works for me again.

Maybe it would be better to update the Phorum MySQL commands accordingly.

Regards, Ulf Dunkel
Re: Field 'recent_author' doesn't have a default value
March 24, 2021 02:31PM
Quote

STRICT_TRANS_TABLES + NO_ENGINE_SUBSTITUTION
Strict was set and the default engine was changed to innodb in the 5.x versions years ago.

The other thing to check is the search table.
If it doesn't work, then it needs to be myisam to work with Phorum. All of the other tables may be innodb.

myisam info: [dev.mysql.com]
Re: Field 'recent_author' doesn't have a default value
March 25, 2021 03:09PM
I am able again to write new postings, but whenever I want to reply to a posting, I still get a similar error. When I retry to send my reply, I am informed that the same reply has already been sent.

So I would like to know what do I have to change in my MySQL settings in order to no longer run into these conflicts, please.

Regards, Ulf Dunkel
Re: Field 'recent_author' doesn't have a default value
March 26, 2021 11:16AM
I still have no progress for fixing this issue because I really do not understand how I should have to set up my mysql settings.

I now have set the SQL mode in /etc/mysql/conf.d/mysql.conf.d/mysqld.cnf to
sql_mode = 'ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION'

This gives the following result when I try to add a new Phorum message or reply:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups, phorum_user_group_xref AS usergroup, pho' at line 4 (1064): SELECT DISTINCT user.user_id AS user_id, user.email AS email FROM phorum_users AS user, phorum_groups AS groups, phorum_user_group_xref AS usergroup, phorum_forum_group_xref AS forumgroup WHERE user.user_id = usergroup.user_id AND usergroup.group_id = groups.group_id AND groups.group_id = forumgroup.group_id AND forum_id = 11 AND permission & 64 > 0 AND usergroup.status >= 1 AND user.moderation_email = 1
Backtrace:
Function phorum_database_error called at
{path to Phorum}/include/db/mysql/mysqli.php:212
----
Function phorum_db_interact called at
{path to Phorum}/include/db/mysql.php:3117
----
Function phorum_db_user_get_moderators called at
{path to Phorum}/include/api/user.php:3165
----
Function phorum_api_user_list_moderators called at
{path to Phorum}/include/email_functions.php:384
----
Function phorum_email_moderators called at
{path to Phorum}/include/posting/action_post.php:292
----
Function include called at
{path to Phorum}/posting.php:595
----

I'd really love to be advised what SQL mode I should set up in order to get the Phorum work again as expected.

Thank you in advance.

Regards, Ulf Dunkel
Re: Field 'recent_author' doesn't have a default value
March 26, 2021 11:26AM
If I completely clear the sql_mode variable in my mysqld.cnf file (
sql_mode = ''
), I still get an error like this:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups,
                phorum_user_group_xref AS usergroup,
                pho' at line 4 (1064): SELECT DISTINCT user.user_id AS user_id,
                user.email AS email
         FROM   phorum_users AS user,
                phorum_groups AS groups,
                phorum_user_group_xref AS usergroup,
                phorum_forum_group_xref AS forumgroup
         WHERE  user.user_id       = usergroup.user_id AND
                usergroup.group_id = groups.group_id AND
                groups.group_id    = forumgroup.group_id AND
                forum_id           = 11 AND
                permission & 64 > 0 AND
                usergroup.status  >= 1
                AND user.moderation_email = 1
Back trace:
Function phorum_database_error called at
{path to Phorum}/include/db/mysql/mysqli.php:212
----
Function phorum_db_interact called at
{path to Phorum}/include/db/mysql.php:3117
----
Function phorum_db_user_get_moderators called at
{path to Phorum}/include/api/user.php:3165
----
Function phorum_api_user_list_moderators called at
{path to Phorum}/include/email_functions.php:384
----
Function phorum_email_moderators called at
{path to Phorum}/include/posting/action_post.php:292
----
Function include called at
{path to Phorum}/posting.php:595
----

Regards, Ulf Dunkel
Re: Field 'recent_author' doesn't have a default value
March 26, 2021 02:40PM
I haven't installed mysql 8.x so can't check the results of changing this.
I did a _quick_ check for reserved words, looking for "groups". There are more possibilities for your issue.
Quote

Note

Each of the following words now is a reserved word and cannot be used as an identifier without identifier quoting: CUME_DIST, DENSE_RANK, FIRST_VALUE, GROUPS, LAG, LAST_VALUE, LEAD, NTH_VALUE, NTILE, OVER, PERCENT_RANK, RANK, ROW_NUMBER, WINDOW.

Identifier quoting (there may be other examples)
[dev.mysql.com]
Re: Field 'recent_author' doesn't have a default value
March 26, 2021 03:03PM
Thank you for your quick reply, Scott. MySQL 8 is released since January 2021. I always thought it to be a good idea that developers are ahead, not behind. ;-)

Regards, Ulf Dunkel
Re: Field 'recent_author' doesn't have a default value
March 26, 2021 03:44PM
I now have tried successfully the following patch.

/include/db/mysql.php
3108: {$PHORUM['groups_table']} AS PHORUM_groups,
3112: usergroup.group_id = PHORUM_groups.group_id AND
3113: PHORUM_groups.group_id    = forumgroup.group_id AND
3299: $users[$perm[0]]['PHORUM_groups'][$perm[1]] = $perm[1];

/include/db/mysqli.php
1875: $sql = "SELECT DISTINCT user.user_id, user.email, user.moderation_email FROM {$PHORUM['user_table']} AS user, {$PHORUM['groups_table']} AS PHORUM_groups, {$PHORUM['user_group_xref_table']} AS usergroup, {$PHORUM['forum_group_xref_table']} AS forumgroup WHERE user.user_id = usergroup.user_id AND usergroup.group_id = PHORUM_groups.group_id AND PHORUM_groups.group_id = forumgroup.group_id AND forum_id = $forum_id AND permission & ".PHORUM_USER_ALLOW_MODERATE_MESSAGES." > 0 AND usergroup.status >= ".PHORUM_USER_GROUP_APPROVED;

/include/db/postgresql.php
1799: $sql = "SELECT DISTINCT U.user_id, U.email, U.moderation_email FROM {$PHORUM['user_table']} AS U, {$PHORUM['groups_table']} AS PHORUM_groups, {$PHORUM['user_group_xref_table']} AS usergroup, {$PHORUM['forum_group_xref_table']} AS forumgroup WHERE U.user_id = usergroup.user_id AND usergroup.group_id = PHORUM_groups.group_id AND PHORUM_groups.group_id = forumgroup.group_id AND forum_id = $forum_id AND permission & ".PHORUM_USER_ALLOW_MODERATE_MESSAGES." > 0 AND usergroup.status >= ".PHORUM_USER_GROUP_APPROVED;

/include/controlcenter/groupmod.php
201: $PHORUM['DATA']['PHORUM_GROUPS'] = array();
212: $PHORUM['DATA']['PHORUM_GROUPS'][] = array(

/include/controlcenter/groups.php
56: $PHORUM['DATA']['PHORUM_Groups'] = phorum_readable_groups();

Regards, Ulf Dunkel
Re: Field 'recent_author' doesn't have a default value
March 26, 2021 05:29PM
Thanks for the fixes.
Sorry, only registered users may post in this forum.

Click here to login