Insert in table with auto_increment PK doesn't work any more with CF7 and MariaDB 10.5

My hosting provider has replaced MySQL 5.7 by MariaDB 10.5. Now the creation of new database entries results in an error:

22007, 1366, Incorrect integer value: '' for column `d0229d0d`.`bsc_wettkampf`.`id` at row 1

Updating existing database entries works fine.
Column bsc_wettkampf.id is defined as follows:

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

How do I fix this in CF7?

you mean when you use the "Save Data" action ?

Hi Joerg,

I had the same problem but with MySQL5.7. The problem has just occured, when I upgraded from PHP7.4 to PHP8.1 and from Joomla3.10 to Joomla 4.1. I think in some combinations there is a problem, that wrong are values submitted, when a field is (or should be) empty (unset) even when the database default and auto increment are set correct.

When you set the 'id' field value to 0, creating a new entry should work (has worked in my case).

Same problem described here:
https://www.chronoengine.com/forums/topics/view/110274/bug-hy000-1366-incorrect-integer-value-for-column-id-at-row-1

Similar problem with date/datetime empty fields (not solved):
https://www.chronoengine.com/forums/topics/view/110499/writing-empty-date-datetime-fields-to-database

Nicolas

Hi Max,
it is indeed a Save Data Action (I would have liked to upload a screenshot, but I didn't know how).
Nicolas' suggestion didn't work for me. Either the update of an existing entry doesn't work or the insert of a new entry, depending on what I do with the PK field.
Jörg

is the "id" field set as Primary key in the table ?

Hi Max,

column "id" is a primary key with auto_increment, see below.

CREATE TABLE `bsc_wettkampf` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`wettkampf` VARCHAR(75) NOT NULL COLLATE 'utf8mb4_general_ci',
	`sportjahr` YEAR NULL DEFAULT NULL,
	`verband_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `FK_bsc_wettkampf_bsc_sportverband` (`verband_id`) USING BTREE,
	CONSTRAINT `FK_wettkampf_sportverband` FOREIGN KEY (`verband_id`) REFERENCES `d0229d0d`.`bsc_sportverband` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
)

Jörg

Hi Jorg,

did you try this on v8 ?

Hi Max,

I could fix it, but I think I found a bug in CF7.

Instead of using a Save Data action for both update and insert, I am now using one for update (with a run condition) followed by one for insert. By intensive testing I learned , that the Save Data action does only work properly for inserts (when primary key is auto increment), if the field "Data Sets" is kept empty and the pk column is left out.

Here comes the bug in CF7: Everytime I open the Save Data action, CF7 automatically puts "Whole Form Data" in the field "Data Sets". Therefore, I must always remember to remove "Whole Form Data" before saving the form. That's annoying and errorprone.

Could you please fix this? Thank you so much.

Jörg