Field 'xxxx' doesn't have a default value

We are running mariadb version 10.3.27 When trying to insert a record we get the error: Field 'xxxx' doesn't have a default value

Our admin has to go in and change the sql mode to: NO_ENGINE_SUBSTITUTION and the errors go away...

A few weeks, or a month go by and the errors start again. So our admin has to change the sql mode again...

Trying to change this field to have a default value, id int(11) not null default none primary auto_increment

This is one of the fields giving us the error, but we can't give it a default value. We tried this query, but it doesn't work: ALTER TABLE `customer` MODIFY `customer_id` int(11) NULL AUTO_INCREMENT

Answer Answered by Ian Gilfillan in this comment.

You haven't provided the queries that are giving the error, but since this looks like an OpenCart installation, it's probably an issue with the OpenCart application sometimes not populating a field. With the definition you've provided, you will get the error you mentioned if you try an insert without specifying a particular value for a NOT NULL field. A query like

ALTER TABLE customer MODIFY org_sponsor VARCHAR(16) NOT NULL DEFAULT '';

or

ALTER TABLE customer MODIFY org_sponsor VARCHAR(16) NULL;

will work around the error you mention, but you should test if this doesn't have other unforeseen consequences. The application may not handle NULL values correctly, for example.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.