Upgrade to 10.3 resulted in deleting configuration from old version

All we need is an easy explanation of the problem, so here it is.

We’ve recently upgraded MariaDB from 10.1 to 10.3 from Cpanel.

After the upgrade we started to having issues in the INSERT clauses where we use

INSERT into table values (”,’one’)

where the first column is an column with integer and auto-increment.

We are getting this error:

"Incorrect integer value: ”"


Issuing the query:

"SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;"

Results in:

@@SQL_MODE: NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

@@GLOBAL.SQL_MODE: NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


When NO_AUTO_VALUE_ON_ZERO is there, newly inserted ID’s do not respect the auto_increment property and the last value is 0

Can you shed some light on what configuration must be made in order to make INSERT’s use empty values in auto-increment columns?

Removing NO_AUTO_VALUE_ON_ZERO is one action.

Thank you in advance

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

As suggested by @nbk, we’ve set global sql mode to the default values from 10.1

NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply