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.

[Jln]

You know what. It works.
Common users can post and create topics.

Actually the answer was looking me face to face. There is tick box A_I in id_msg and id_topic. There very much should be. For some reason, update maybe? server change maybe? it was unticked.
We are testing now it with admins, moderators and regular users. These two small changes apparently fixed it.

Settings:
Default: None
Attributes: Unsigned
A-I: X

Thank you very much for your help and ideas! 

[Aleksi "Lex" Kilpinen]

A-I means auto increment, so the DB generates the next value automatically - unless the field was actually originally meant to auto increment, I'm not really sure this is the way... You now have the Database essentially guessing what should be there.

The usual safe solution for the error message has been mentioned many times now, that is Default: NULL.

Edit:
Actually, yeah - id_msg in smf_messages is meant to be A-I, id_topic in smf_messages is meant to be NOT NULL Default 0.
If this was not the case, something has gone very wrong at some point.

From install_2-0_mysql.sql

Code Select Expand

CREATE TABLE {$db_prefix}messages (
  id_msg int(10) unsigned NOT NULL auto_increment,
  id_topic mediumint(8) unsigned NOT NULL default '0',
  id_board smallint(5) unsigned NOT NULL default '0',
  poster_time int(10) unsigned NOT NULL default '0',
  id_member mediumint(8) unsigned NOT NULL default '0',
  id_msg_modified int(10) unsigned NOT NULL default '0',
  subject varchar(255) NOT NULL default '',
  poster_name varchar(255) NOT NULL default '',
  poster_email varchar(255) NOT NULL default '',
  poster_ip varchar(255) NOT NULL default '',
  smileys_enabled tinyint(4) NOT NULL default '1',
  modified_time int(10) unsigned NOT NULL default '0',
  modified_name varchar(255) NOT NULL default '',
  body text NOT NULL,
  icon varchar(16) NOT NULL default 'xx',
  approved tinyint(3) NOT NULL default '1',
  PRIMARY KEY (id_msg),
  UNIQUE topic (id_topic, id_msg),
  UNIQUE id_board (id_board, id_msg),
  UNIQUE id_member (id_member, id_msg),
  KEY approved (approved),
  KEY ip_index (poster_ip(15), id_topic),
  KEY participation (id_member, id_topic),
  KEY show_posts (id_member, id_board),
  KEY id_topic (id_topic),
  KEY id_member_msg (id_member, approved, id_msg),
  KEY current_topic (id_topic, id_msg, id_member, approved),
  KEY related_ip (id_member, poster_ip, id_msg)
) ENGINE=MyISAM;

Also, id_topic in smf_topics should be A-I, by default there is no id_msg in here.

Code Select Expand

CREATE TABLE {$db_prefix}topics (
  id_topic mediumint(8) unsigned NOT NULL auto_increment,
  is_sticky tinyint(4) NOT NULL default '0',
  id_board smallint(5) unsigned NOT NULL default '0',
  id_first_msg int(10) unsigned NOT NULL default '0',
  id_last_msg int(10) unsigned NOT NULL default '0',
  id_member_started mediumint(8) unsigned NOT NULL default '0',
  id_member_updated mediumint(8) unsigned NOT NULL default '0',
  id_poll mediumint(8) unsigned NOT NULL default '0',
  id_previous_board smallint(5) NOT NULL default '0',
  id_previous_topic mediumint(8) NOT NULL default '0',
  num_replies int(10) unsigned NOT NULL default '0',
  num_views int(10) unsigned NOT NULL default '0',
  locked tinyint(4) NOT NULL default '0',
  unapproved_posts smallint(5) NOT NULL default '0',
  approved tinyint(3) NOT NULL default '1',
  PRIMARY KEY (id_topic),
  UNIQUE last_message (id_last_msg, id_board),
  UNIQUE first_message (id_first_msg, id_board),
  UNIQUE poll (id_poll, id_topic),
  KEY is_sticky (is_sticky),
  KEY approved (approved),
  KEY id_board (id_board),
  KEY member_started (id_member_started, id_board),
  KEY last_message_sticky (id_board, is_sticky, id_last_msg),
  KEY board_news (id_board, id_first_msg)
) ENGINE=MyISAM;