Field 'id_msg' doesn't have a default value

[Jln]

I am just a volunteer trying to make sense of a broken forum.
Current SMF version 2.0.19, Freshly upgraded (so writing and other operations are possible). Using PHP 8.0 according to the hosting.

Users receive an error message when they try to post:

Field 'id_msg' doesn't have a default value
File: /public_html/SMF/Sources/Subs-Post.php
Line: 1843

The problem appeared after forum moved to another hosting provider. Many errors have been fixed but this serious one persists.
I wonder how to proceed.

[Doug Heffernan]

What mods do you have installed?

[Jln]

Currently no mods installed.

I was suspecting fault in file permissions but they are typical 755 except agreement.txt, settings.php and settings_bak.php that are 644.

[Kindred]

?? files can't be 7 permission -- that's a directory level permission. FIles might need to be be 664

[shawnb61]

You need to give id_msg a default value in phpmyadmin.

[Jln]

You need to give id_msg a default value in phpmyadmin.

I wonder might there be somewhere a sample that shows me what to write and where. 

[Doug Heffernan]

I wonder might there be somewhere a sample that shows me what to write and where. 

The first step imo would be to identify the field in question. Personally I think that it might come from a third party mod. You said that you don't have any currently installed, but that field could have been left behind from a mod that when it was uninstalled it didn't remove the custom field that it added.

[Sir Osis of Liver]

id_msg is a core field in 2.0/2.1, it has no default value.  The error may be due to some remnant mod code.  You can assign a value of NULL to the field in messages table with phpmyadmin, or upload a clean set of files.  The latter would be better if you're not running any mods and don't have major customizations.

[Illori]

uploading a set of clean files will NOT fix this issue as it is in the database and not in the files.

[Sir Osis of Liver]

Well, no.  In a core install id_msg doesn't have a default value, and it doesn't cause a problem.  So either there's some remnant mod code that requires a default value, or the mysql implementation is borked.  OP doesn't have any mods installed, so simplest thing to try is clean files. If that doesn't fix it, then it's a mysql issue, which may be above OP's pay grade.

[Jln]

A "clean set of files" is something I don't have a luxury to have. I have a database that comes from years back and have been used up till now. 

Upgrade to 2.0.19 automatically uninstalled all mods and uploaded fresh SMF files.

I was reading about a same kind of problem with field 'spam'.
https://www.simplemachines.org/community/index.php?topic=474935.0
My problem is that id_msg is a necessary field in smf_messages and smf_topics and it shouldn't be deleted like that.

[Aleksi "Lex" Kilpinen]

The real solution to "doesn't have a default value" is to assign a default value.
Most commonly, NULL is safe and works to hide the error. Assigning the default value of NULL basically just tells your database engine that it's fine to leave this field empty.

What probably brought this up, is the upgrade - You used to have a mod that made sure this field was never empty, so it never triggered an error.
Now, there is no more mod code to fill it, and the Database is telling you it needs to be filled because it has no default value assigned.

[Jln]

What probably brought this up was changing the server provider. Previously this version worked fine. 

I found out that SQL is "strict" and doesn't allow undefined values. I tried to make it less strict with advice of stackoverflow:
set global sql_mode='';

Result was not good:
#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Looks like default server policy.

I wonder is there something I could try myself. There is nothing to lose.

[Aleksi "Lex" Kilpinen]

Assign the default value?

[Sesquipedalian]

Assign the default value?

This is the best solution.

[Jln]

Experimenting.

I went to smf_messages and I found four options for id_msg (with primary key icon)
DEFAULT can be set as None, As defined, NULL or CURRENT_TIMESTAMP
None = this is old default, brings error
As defined: 1 = I attempted this. id_msg 1 is not used. Post goes... somewhere, it never appears. At least got rid of error message
NULL = not even allowed, it becomes automatically None after I change it.
CURRENT_TIMESTAMP = Naturally doesn't even fit in field.

As defined: 0 = post goes... somewhere. And doesn't show up. It exists, because next post brings error message of duplicate entry.

As defined: (last post number +1) allows a post. At least I see it when I log in. Maybe others don't. It also doesn't appear on recent posts list.
Of course last post number+1 as set value doesn't increment id_msg and further posts are duplicates and are not allowed.

An integer as DEFAULT seems to be not the solution. We would need a variable that increments +1 after post, whatever the name of that might be. 

How to assign default value correctly?

[Doug Heffernan]

How to assign default value correctly?

The best way to do that imo is through phpMyAdmin. Go to the table that has the column that needs to be changed, click the Structure button and when editing the aforementioned column untick the "NOT NULL" option and save the changes.

 

[Jln]

There is Null box, unticked, It has been unticked all the time. It says indeed "Null", not "Not Null".

Auto-Increment box is unticked. I tested and ticked it into effect in id_msg and id_topic ind smf_messages and smf_topics.
No error messages.
It would seem I can write and open new topic. Messages have new incremented id_msg number.

Now it even seems that unlogged user can see my recent posts in recent post list. This is interesting.