General error: 1364 with STRICT_TRANS_TABLES disabled

Question

I have a Mariadb 10.2.26 server, with "STRICT_TRANS_TABLES" mode disabled. Only mode enabled is "NO_ENGINE_SUBSTITUTION". But even like this, I am getting "General error: 1364 Field '' doesn't have a default value". Does anyone have any idea why? Thanks.

When do you get this error (insert,update?) and what is your insert,update statement? — P.Salmon, Aug 27 '19 at 10:53
I get it on a simple insert query. Create table and statement I guess are not relevant here, as it is an old table with more then 100 fields, a lot of them without default values and not nullable. That's why I am trying to avoid this error by sql_mode settings. The issue is why I am still getting error 1364 even with STRICT_TRANS_TABLES disabled. — user3199269, Aug 27 '19 at 10:58
if your solution is to modify sql_mode to make a query work you choose the very wrong approach.. Also a disabled `STRICT_TRANS_TABLES` (strict mode) is asking for trouble.. — Raymond Nijland, Aug 27 '19 at 11:06
I agree. But it's a big old database I have to work upon, with almost 400 tables with more than 2000 fields in that situation. I rather take this approach for now to complete my project than spend weeks to fix the db first. — user3199269, Aug 27 '19 at 11:10

score 0 · Answer 1 · answered Aug 27 '19 at 15:13

Here's a small sample of the "incompatible changes" made over the years.

----- 2014-09-25 5.7.5 Milestone 15 -- SQL Mode Notes -- Incompatible Change -----

These SQL mode changes were made:
Strict SQL mode for transactional storage engines ([STRICT_TRANS_TABLES](http://dev.mysql.comhttps://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_strict_trans_tables))
is now enabled by default.
Implementation of the [ONLY_FULL_GROUP_BY](http://dev.mysql.comhttps://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by)
SQL mode has been made more sophisticated, to no longer reject deterministic queries that previously were rejected.
    MySQL now recognizes when a nonaggregated selected column is functionally dependent on (uniquely determined by) GROUP BY columns.

    MySQL has an extension to standard SQL that permits references in the HAVING clause to aliased expressions in the select list.
Previously, enabling ONLY_FULL_GROUP_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions. This restriction has been lifted so that the HAVING clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY is enabled.
In consequence, [ONLY_FULL_GROUP_BY](http://dev.mysql.comhttps://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by)
is now enabled by default, to prohibit nondeterministic queries containing expressions not guaranteed to be uniquely determined within a group.
The changes to the default SQL mode result in a default [sql_mode](http://dev.mysql.comhttps://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode)
system variable value with these modes enabled: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION.
The [ONLY_FULL_GROUP_BY](http://dev.mysql.comhttps://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by)
mode is now included in the modes comprised by the ANSI SQL mode.
A new function, ANY_VALUE(), is available that can be used to force MySQL to accept queries that it thinks should be rejected with
ONLY_FULL_GROUP_BY enabled. The function return value and type are the same as the return value and type of its argument, but the function result is not checked for the ONLY_FULL_GROUP_BY SQL mode.

If you find that having ONLY_FULL_GROUP_BY enabled causes queries for existing applications to be rejected, either of these actions should restore operation:
If it is possible to modify an offending query, do so, either so that nondeterministic nonaggregated columns are functionally dependent
on GROUP BY columns, or by referring to nonaggregated columns using ANY_VALUE().
If it is not possible to modify an offending query (for example, if it is generated by a third-party application), set the sql_mode
system variable at server startup to not enable ONLY_FULL_GROUP_BY.

For more information about SQL modes and GROUP BY queries, see Server SQL Modes, and MySQL Handling of GROUP BY. (Bug #18486310)

----- 2014-03-31 5.7.4 Milestone 14 -- Functionality Added or Changed -- Incompatible Change -----

The deprecated ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes now do nothing. Instead, their previous effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES). In other words, strict mode now means the same thing as the previous meaning of strict mode plus the ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes. This change reduces the number of SQL modes with an effect dependent on strict mode and makes them part of strict mode itself.

To prepare for the SQL mode changes in this version of MySQL, it is advisable before upgrading to read SQL Mode Changes in MySQL 5.7. That discussion provides guidelines to assess whether your applications will be affected by these changes.

The deprecated ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes are still recognized so that statements that name them do not produce an error, but will be removed in a future version of MySQL. To make advance preparation for versions of MySQL in which these modes do not exist, applications should be modified to not refer to those mode names.

----- 2014-03-27 5.6.17 General Availability -- SQL Mode Notes -- Incompatible Change -----

The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes now are deprecated and setting the sql_mode value to include any of them generates a warning. In MySQL 5.7, these modes do nothing. Instead, their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES). The motivation for the change in MySQL 5.7 is to reduce the number of SQL modes with an effect dependent on strict mode and make them part of strict mode itself.

To make advance preparation for an upgrade to MySQL 5.7, see SQL Mode Changes in MySQL 5.7. That discussion provides guidelines to assess whether your applications will be affected by the SQL mode changes in MySQL 5.7.

----- 2013-02-05 5.6.10 General Availability -- Bugs Fixed -- InnoDB -----

During an online DDL operation, changing a column from nullable to NOT NULL could succeed or fail differently depending on whether the ALTER TABLE statement used ALGORITHM=INPLACE or ALGORITHM=COPY. An operation with ALGORITHM=COPY would succeed even if the column contained NULL values, while an operation with ALGORITHM=INPLACE failed because of the possibility that the column contained NULL values. Now, making a column NOT NULL in combination with the ALGORITHM=INPLACE clause is allowed, but only if the sql_mode configuration option includes the STRICT_TRANS_TABLES or STRICT_ALL_TABLES setting. If the ALGORITHM clause is not specified with the ALTER TABLE statement, the online DDL operation will use ALGORITHM=INPLACE if possible, or ALGORITHM=COPY if not. (Bug #15961327)

----- 2012-11-07 5.6.8 Release Candidate -- Installation Notes -- -----

On Unix platforms, mysql_install_db now creates a default option file named my.cnf in the base installation directory. This file is created from a template included in the distribution package named my-default.cnf. You can find the template in or under the base installation directory. When started using mysqld_safe, the server uses my.cnf file by default. If my.cnf already exists, mysql_install_db assumes it to be in use and writes a new file named my-new.cnf instead.

With one exception, the settings in the default option file are commented and have no effect. The exception is that the file changes the sql_mode system variable from its default of NO_ENGINE_SUBSTITUTION to also include STRICT_TRANS_TABLES:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

This setting produces a server configuration that results in errors rather than warnings for bad data in operations that modify transactional tables. See Server SQL Modes.

The my-default.cnf template replaces the older sample option files (my-small.cnf, my-medium.cnf, and so forth), which are no longer distributed.

----- 2004-12-01 5.0.2 -- Functionality Added or Changed -- -----

Added STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and TRADITIONAL SQL modes. The TRADITIONAL mode is shorthand for all the preceding modes. When using mode TRADITIONAL, MySQL generates an error if you try to insert a wrong value in a column. It does not adjust the value to the closest possible legal value.

----- 2004-12-01 5.0.2 -- Functionality Added or Changed -- -----

The compilation flag DONT_USE_DEFAULT_FIELDS was removed because you can get the same behavior by setting the sql_mode system variable to STRICT_TRANS_TABLES.

----- 2004-12-01 5.0.2 -- Functionality Added or Changed -- -----

MySQL now remembers which columns were declared to have default values. In STRICT_TRANS_TABLES/STRICT_ALL_TABLES mode, you now get an error if you do an INSERT without specifying all columns that don't have a default value. A side effect of this is that when you do SHOW CREATE for a new table, you no longer see a DEFAULT value for a column for which you didn't specify a default value.

General error: 1364 with STRICT_TRANS_TABLES disabled

1 Answers1