I encountered a strange behavior with MariaDB 10.3.29 today.
Here is the table in question:
CREATE TABLE `quick` (
`userid` int(11) NOT NULL,
`room1` varchar(32) NOT NULL,
`room2` varchar(32) NOT NULL,
`room3` varchar(32) NOT NULL,
`room4` varchar(32) NOT NULL,
`sounduser` varchar(24) NOT NULL,
`soundchannel` varchar(24) NOT NULL,
`desktopnotify` int(1) unsigned NOT NULL,
`friendly` int(1) unsigned NOT NULL,
`poll` int(1) unsigned NOT NULL,
`emailnotify` int(1) unsigned NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
It's a relatively simply table.
Here is a SQL query that is currently failing:
INSERT INTO quick (userid, room1, room2, room3, room4, sounduser, soundchannel, desktopnotify,
emailnotify) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY
UPDATE room1 = VALUES(room1), room2 = VALUES(room2), room3 = VALUES(room3), room4 = VALUES(room4), sounduser = VALUES(sounduser),
soundchannel = VALUES(soundchannel), desktopnotify = VALUES(desktopnotify), emailnotify = VALUES(emailnotify)
The query fails with the error Field 'friendly' doesn't have a default value
Obviously, friendly is not one of the columns being updated in the above query. The strange thing is that friendly is not even null (the value is currently 1), there are no rows where friendly does not have a value, and we aren't even updating this column in the query.
I know that this has to do with the NOT NULL constraint, but the value isn't currently NULL, and we're not updating it to make it NULL - in fact, we're not touching the column and its non-null value at all in any way - so why would it complain about this?
I went ahead and modified the query to remove other columns, and it basically whines if every single column is not provided.
This behavior seems rather bizarre, almost as if it's trying to delete the row and then add data using the updated values provided. A regular UPDATE ... SET query works fine.
I can't find any documentation about this behavior. Why is the query behaving like this? Is there any way to get it to work properly, or should ON DUPLICATE KEY UPDATE be swapped out for a 3-part SQL query with a regular UPDATE?
