Field 'xxxxxxxxxxxxxx' doesn't have a default value issue after 2.0.19->2.1.4

[oldnick]

1. I migrated from an old hosting to a new one because of the outdated PHP version the old one provide.
2. Upgraded from SMF 2.0.19 to 2.1.4.
3. Just started to test new version of the forum and quite frequently get new records in the error log. Most of the errors are related to absent default value in various columns (fields) of diffirent tables.

Is there an analogue of repair_settings.php which updates or alters all tables in DB with all necessary default values, etc?
Or is there a reference database dump that can be used as a model for altering my current database?

[@rjen]

The upgrade takes care of all standard fields . If you are getting these errors they must be related to fields added by mods.

Adding a default value to those must be done manually. This has been explained many times before: search the forum for instructions.

[oldnick]

My 1st thought was the same - it must be some mod's field which causing the trouble but it's not.
I made a clean install of SMF 2.1.4 and my "prefix_members" table has a field "passwd_flood" varchar(12) which does not have default value.
And the error log record refers namely to this field.

Code Select Expand

Subs-Members.php (Line 717)  Backtrace information

Type of error: Database
Error messageSelect
DB error: Field 'passwd_flood' doesn't have a default value
So my question is still actual.
Any idea how to catch all "no default value" fields in all tables (which belong to clean SMF 2.1.4, not mods) and to make them have one?
Or how to make the script work as it intended even with missing default value in a field of a table?

[Kindred]

No, there is no tool... nor is one needed.
And I can not recreate your issue.

Although password_flood has a default value of none, I can not get my system to generate the error

So, it appears that something is amiss with your install which is causing error reports that don't happen in other sites.

[oldnick]

Is SMF 2.1.4's code sensitive to PHP version or to SQL engine?
Maybe that's the problem. My hosting is on PHP Version 8.2.25 and Database - 10.5.15-MariaDB-cll-lve-log.

[Aleksi "Lex" Kilpinen]

PHP 8.2 and MariaDB 10.x should be fine.

[Illori]

i believe over time mysql/MariaDB have made changes that make a default required and since it was not in the past this has to be done manually.

[shawnb61]

passwd_flood was added in 2.0, and the default of '' was added at that time.  New 2.0 & 2.1 installs have the default also.

Some background on "Strict mode..."  When using the mysql setting 'STRICT_TRANS_TABLES', "a value is considered missing when a new row to be inserted does not contain a value for a non-NULL column, and there is no explicit DEFAULT clause in its definition."

I.e., with STRICT_TRANS_TABLES off, for NOT NULL columns with no default & no value specified by an insert, you are literally asking your DB to make a guess what to put there...  It would happily do so...

Older versions of MySQL did not have strict mode on as a default.  Newer versions are more strict. 

Starting with SMF2.1, STRICT_TRANS_TABLES is explicitly set by SMF.  Prior to 2.1, no sql_mode was specified, meaning, SMF just used the database default. 

SMF generally has always provided defaults or values for NOT NULL columns. I.e., whether strict mode was on or off, generally, SMF has been OK.

Historically, mods were all over the place, though.  If an older mod was running under a newer version of MySQL, there could be problems.  SMF2.0 allowed you to override the DB's sql_mode (basically removing it...) by setting "$mysql_set_mode = true;" in Settings.php.  This was intended to be a temporary fix to help you get past issues until the mods were fixed.  SMF2.0 only.

This is no longer supported in 2.1.  In 2.1+, we are forcing all mods to honor strict mode as well. 

[shawnb61]

Or is there a reference database dump that can be used as a model for altering my current database?

That's what this utility is intended to do:
https://raw.githubusercontent.com/sbulen/sjrbTools/refs/heads/master/smf_db_compare.php

I just updated this tool to support all versions of SMF, from 1.x to 3.x.  (No other file copies needed...  Put another way, you're helping me test it!  )

Right click, download it, copy it to your forum root & run it.  Adds are in green, generally OK.  Removals are red, generally bad.  Yellows are changes.  I think you'd be OK to focus on default columns that are yellow.

If this utility isn't working for you, you can go directly to the source DB definition used by SMF to figure out what the defaults should be:
https://github.com/SimpleMachines/SMF/blob/release-2.1/other/install_2-1_mysql.sql

[shawnb61]

I just did a quick 2.0.19 install under MariaDB 11.4 & upgraded to 2.1.4, & do not see similar issues.

So I'm not sure where/how you lost some of your defaults.

I am seeing a difference at the db level in how single quotes are presented, which might be related...  (It's definitely impacting my smf_db_compare.php utility...)