Steps to reproduce the issue

Run below code in any model file.

$db = $this->getDbo();
$db->setQuery(
   "INSERT INTO " . $destination_db->qn('#__categories') .
    " (id)" .
    " VALUES (0)"
);
try {
  $destination_db->execute();
} catch (\RuntimeException $exc) {
   echo $exc->getMessage();
}

Expected result

Create an empty new row in table categories

Actual result

You get error message:
Field 'description' doesn't have a default value

System information (as much as possible)

I attach php Info of my system, even though, that does not seem to be relevant.
phpInfo.pdf

Additional comments

When I run above SQL directly from phpMyAdmin it runs without problems.

As a temporary solution, if you run below:

$db->setQuery("set @@sql_mode = ''");
$db->execute();

before the execution of the insert, then it runs as expected.

If you run :

$db->setQuery('SELECT @@GLOBAL.sql_mode;');
echo "Global: ".$db->loadResult()."<br>";
$db->setQuery('SELECT @@SESSION.sql_mode;');
echo "Session: ".$db->loadResult();

then you get:
Global: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Session: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

The STRICT_TRANS_TABLES should not be there.

This was problem back in 2010 and is described here: MySQL strict mode incompatibility

Up to Joomla 3.5 no such problem exists, but it reappear in Joomla 4, so something is wrong with MySQLi adapter.