Issuing a query that triggers a trigger on a timestamp field makes the mysql server go away.
Query are inside file query.log and syslog.log contain a trace.
You need two databases JosmWeb and josm_web, load JosmWeb with JosmWeb.Auction.schema.sql and JosmWeb.AuctionHasAuctionItems.schema.sql. Load josm_web with objlistaJosm.sql, run the update query inside query.log.
The same update query works in Oracle mysql-server-5.6 (and even back to 5.0).
Tested on clean install of both MariaDB 10.1 and MySQL 5.6.
Stack trace from 10.1 commit d80b8442a68093106e00a9a38b7b2c593002a72c
#3 <signal handler called>
#4 0x00005591e7e6a105 in set_field_to_new_field (field=0x7fa4791c8d48, new_field=0x7fa4790b2de0) at /src/10.1/sql/item.cc:2387
#5 0x00005591e7e6a14e in Item_field::switch_to_nullable_fields_processor (this=0x7fa4791c8c70, arg=0x7fa4790b2de0 "\210\320\024y\244\177") at /src/10.1/sql/item.cc:2395
#6 0x00005591e7b66bdb in Item::walk (this=0x7fa4791c8c70, processor=&virtual table offset 944, walk_subquery=true, arg=0x7fa4790b2de0 "\210\320\024y\244\177") at /src/10.1/sql/item.h:1367
#7 0x00005591e7bbd444 in switch_to_nullable_trigger_fields (items=..., table=0x7fa478e28670) at /src/10.1/sql/sql_base.cc:8837
#8 0x00005591e7cefe93 in mysql_update (thd=0x7fa47e7c9470, table_list=0x7fa4791c8510, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7fa484cd46a0, updated_return=0x7fa484cd4730) at /src/10.1/sql/sql_update.cc:459
#9 0x00005591e7c19559 in mysql_execute_command (thd=0x7fa47e7c9470) at /src/10.1/sql/sql_parse.cc:3749
#10 0x00005591e7fa17e6 in sp_instr_stmt::exec_core (this=0x7fa4791c8dc8, thd=0x7fa47e7c9470, nextp=0x7fa484cd4f74) at /src/10.1/sql/sp_head.cc:3213
#11 0x00005591e7fa0f18 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fa4791c8e08, thd=0x7fa47e7c9470, nextp=0x7fa484cd4f74, open_tables=false, instr=0x7fa4791c8dc8) at /src/10.1/sql/sp_head.cc:2979
#12 0x00005591e7fa14ea in sp_instr_stmt::execute (this=0x7fa4791c8dc8, thd=0x7fa47e7c9470, nextp=0x7fa484cd4f74) at /src/10.1/sql/sp_head.cc:3129
#13 0x00005591e7f9cddc in sp_head::execute (this=0x7fa4791c7888, thd=0x7fa47e7c9470, merge_da_on_success=false) at /src/10.1/sql/sp_head.cc:1317
#14 0x00005591e7f9d91f in sp_head::execute_trigger (this=0x7fa4791c7888, thd=0x7fa47e7c9470, db_name=0x7fa47905cdb8, table_name=0x7fa47905cdc8, grant_info=0x7fa47913bc38) at /src/10.1/sql/sp_head.cc:1646
#15 0x00005591e7ce7115 in Table_triggers_list::process_triggers (this=0x7fa47913ba88, thd=0x7fa47e7c9470, event=TRG_EVENT_UPDATE, time_type=TRG_ACTION_AFTER, old_row_is_record1=true) at /src/10.1/sql/sql_trigger.cc:2176
#16 0x00005591e7cf1163 in mysql_update (thd=0x7fa47e7c9470, table_list=0x7fa4790527c0, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7fa484cd5ca0, updated_return=0x7fa484cd5d30) at /src/10.1/sql/sql_update.cc:860
#17 0x00005591e7c19559 in mysql_execute_command (thd=0x7fa47e7c9470) at /src/10.1/sql/sql_parse.cc:3749
#18 0x00005591e7c2477a in mysql_parse (thd=0x7fa47e7c9470, rawbuf=0x7fa479052688 "update objlistaJosm set HogstaBudTid='2009-11-15 08:00:11'", length=58, parser_state=0x7fa484cd65e0) at /src/10.1/sql/sql_parse.cc:7303
#19 0x00005591e7c13474 in dispatch_command (command=COM_QUERY, thd=0x7fa47e7c9470, packet=0x7fa47d9c92f1 "update objlistaJosm set HogstaBudTid='2009-11-15 08:00:11'", packet_length=58) at /src/10.1/sql/sql_parse.cc:1488
#20 0x00005591e7c121a6 in do_command (thd=0x7fa47e7c9470) at /src/10.1/sql/sql_parse.cc:1109
#21 0x00005591e7d47798 in do_handle_one_connection (thd_arg=0x7fa47e7c9470) at /src/10.1/sql/sql_connect.cc:1349
#22 0x00005591e7d474fc in handle_one_connection (arg=0x7fa47e7c9470) at /src/10.1/sql/sql_connect.cc:1261
#23 0x00005591e844dbb2 in pfs_spawn_thread (arg=0x7fa47d3b0e70) at /src/10.1/storage/perfschema/pfs.cc:1860
#24 0x00007fa4849510a4 in start_thread () from /lib64/libpthread.so.0
#25 0x00007fa482abf04d in clone () from /lib64/libc.so.6
Reduced test case
CREATE TABLE `Auction` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`auctionEnded` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`status` tinyint(4) NOT NULL DEFAULT '0',
`bidTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`md5` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `auctionEnded` (`auctionEnded`),
KEY `status` (`status`)
) DEFAULT CHARSET=utf8;
CREATE TABLE `objlistaJosm` (
`ObjListaNr` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Marknadspris` int(11) DEFAULT NULL,
`Status` tinyint(3) unsigned NOT NULL DEFAULT '0',
`SlagAvgiftMoms` char(20) DEFAULT NULL,
`MomsBudgivning` varchar(95) DEFAULT NULL,
`UppdatKastor` tinyint(3) unsigned NOT NULL DEFAULT '0',
INSERT INTO `objlistaJosm` VALUES (240017,708877,0,'Personbilar V.6','Sundsvall','BMW','Drive20d (Aut+GPS+190hk)',2015,355000,'1',401600,0,NULL,NULL,NULL,NULL,NULL,NULL,500,0,NULL,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Ja',NULL,0,NULL,'2016-02-02 15:26:27',0,'','SEK','/objekt/nr/7433/Drive20d_(Aut+GPS+190hk).html','BMW (Aut+GPS+190hk)',1,1,0,0,'Personbil',1,1,808977,'foobar AB','Ja','Personbil','Automat','1305',0,'mil','Diesel','7.1','',1,1,NULL,365456,285136);
--delimiter ;;
CREATE DEFINER=`root`@`%` TRIGGER `SET_AUCTIONSTART` BEFORE UPDATE ON `Auction` FOR EACH ROW BEGIN
IF (NEW.status = 1 OR NEW.status = 11) AND (OLD.status <> 1 and OLD.status <> 11) THEN
SET NEW.auctionStart = NOW();
END IF;
END;;
CREATE DEFINER=`root`@`%` TRIGGER `afterUpdateOnObjlistaJosm` AFTER UPDATE ON `objlistaJosm` FOR EACH ROW BEGIN
IF IFNULL(NEW.Budnr,0) != IFNULL(OLD.Budnr,0) THEN UPDATE Auction t1 JOIN AuctionHasAuctionItems t2 ON t1.id = t2.auctionId SET t1.bidId = IFNULL(NEW.Budnr,0) WHERE t2.auctionItemId = OLD.Objektnr;
END IF;
IF IFNULL(NEW.HogstaBudBelopp,0) != IFNULL(OLD.HogstaBudBelopp,0) THEN UPDATE Auction t1 JOIN AuctionHasAuctionItems t2 ON t1.id = t2.auctionId SET t1.bid = IFNULL(NEW.HogstaBudBelopp,0) WHERE t2.auctionItemId = OLD.Objektnr;
END IF;
IF IFNULL(NEW.HogstaBudTid,'0000-00-00 00:00:00') != IFNULL(OLD.HogstaBudTid,'0000-00-00 00:00:00')
THEN UPDATE Auction t1
JOIN AuctionHasAuctionItems t2
ON t1.id = t2.auctionId
SET t1.bidTime = IFNULL(NEW.HogstaBudTid,'0000-00-00 00:00:00')
WHERE t2.auctionItemId = OLD.Objektnr;
END IF;
IF IFNULL(NEW.AuktionStangdTid,'0000-00-00 00:00:00') != IFNULL(OLD.AuktionStangdTid,'0000-00-00 00:00:00')
THEN UPDATE Auction t1
JOIN AuctionHasAuctionItems t2
ON t1.id = t2.auctionId
SET t1.auctionEnded = IFNULL(NEW.AuktionStangdTid,'0000-00-00 00:00:00'), t1.status = 2
WHERE t2.auctionItemId = OLD.Objektnr;
END IF;
IF IFNULL(NEW.`status`,'0') != IFNULL(OLD.`status`,'0') THEN UPDATE Auction t1 JOIN AuctionHasAuctionItems t2 ON t1.id = t2.auctionId SET t1.`status` = NEW.status WHERE t2.auctionItemId = OLD.Objektnr;
END IF;
IF IFNULL(NEW.visas,'0') != IFNULL(OLD.visas,'0') THEN UPDATE Auction t1 JOIN AuctionHasAuctionItems t2 ON t1.id = t2.auctionId SET t1.`status` = IF(IFNULL(NEW.visas,'0') = 0,0,NEW.`status`) WHERE t2.auctionItemId = OLD.Objektnr;
END IF;
END;;
--delimiter ;
update objlistaJosm set HogstaBudTid="2009-11-15 08:00:11" where ObjListaNr='240017';
Easy to guess, the problem was introduced by this change:
commit 0686c34d22a5cbf93015012eaf77a4a977b63afb
Author: Sergei Golubchik <serg@mariadb.org>
Date: Sat Nov 14 22:51:54 2015 +0100
MDEV-8605 MariaDB not use DEFAULT value even when inserted NULL for NOT NULLABLE column
NOT NULL constraint must be checked *after* the BEFORE triggers.
That is for INSERT and UPDATE statements even NOT NULL fields
must be able to store a NULL temporarily at least while
BEFORE INSERT/UPDATE triggers are running.
Elena Stepanova
added a comment - - edited Thanks for the report and the test case.
Stack trace from 10.1 commit d80b8442a68093106e00a9a38b7b2c593002a72c
#3 <signal handler called>
#4 0x00005591e7e6a105 in set_field_to_new_field (field=0x7fa4791c8d48, new_field=0x7fa4790b2de0) at /src/10.1/sql/item.cc:2387
#5 0x00005591e7e6a14e in Item_field::switch_to_nullable_fields_processor (this=0x7fa4791c8c70, arg=0x7fa4790b2de0 "\210\320\024y\244\177") at /src/10.1/sql/item.cc:2395
#6 0x00005591e7b66bdb in Item::walk (this=0x7fa4791c8c70, processor=&virtual table offset 944, walk_subquery=true, arg=0x7fa4790b2de0 "\210\320\024y\244\177") at /src/10.1/sql/item.h:1367
#7 0x00005591e7bbd444 in switch_to_nullable_trigger_fields (items=..., table=0x7fa478e28670) at /src/10.1/sql/sql_base.cc:8837
#8 0x00005591e7cefe93 in mysql_update (thd=0x7fa47e7c9470, table_list=0x7fa4791c8510, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7fa484cd46a0, updated_return=0x7fa484cd4730) at /src/10.1/sql/sql_update.cc:459
#9 0x00005591e7c19559 in mysql_execute_command (thd=0x7fa47e7c9470) at /src/10.1/sql/sql_parse.cc:3749
#10 0x00005591e7fa17e6 in sp_instr_stmt::exec_core (this=0x7fa4791c8dc8, thd=0x7fa47e7c9470, nextp=0x7fa484cd4f74) at /src/10.1/sql/sp_head.cc:3213
#11 0x00005591e7fa0f18 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fa4791c8e08, thd=0x7fa47e7c9470, nextp=0x7fa484cd4f74, open_tables=false, instr=0x7fa4791c8dc8) at /src/10.1/sql/sp_head.cc:2979
#12 0x00005591e7fa14ea in sp_instr_stmt::execute (this=0x7fa4791c8dc8, thd=0x7fa47e7c9470, nextp=0x7fa484cd4f74) at /src/10.1/sql/sp_head.cc:3129
#13 0x00005591e7f9cddc in sp_head::execute (this=0x7fa4791c7888, thd=0x7fa47e7c9470, merge_da_on_success=false) at /src/10.1/sql/sp_head.cc:1317
#14 0x00005591e7f9d91f in sp_head::execute_trigger (this=0x7fa4791c7888, thd=0x7fa47e7c9470, db_name=0x7fa47905cdb8, table_name=0x7fa47905cdc8, grant_info=0x7fa47913bc38) at /src/10.1/sql/sp_head.cc:1646
#15 0x00005591e7ce7115 in Table_triggers_list::process_triggers (this=0x7fa47913ba88, thd=0x7fa47e7c9470, event=TRG_EVENT_UPDATE, time_type=TRG_ACTION_AFTER, old_row_is_record1=true) at /src/10.1/sql/sql_trigger.cc:2176
#16 0x00005591e7cf1163 in mysql_update (thd=0x7fa47e7c9470, table_list=0x7fa4790527c0, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7fa484cd5ca0, updated_return=0x7fa484cd5d30) at /src/10.1/sql/sql_update.cc:860
#17 0x00005591e7c19559 in mysql_execute_command (thd=0x7fa47e7c9470) at /src/10.1/sql/sql_parse.cc:3749
#18 0x00005591e7c2477a in mysql_parse (thd=0x7fa47e7c9470, rawbuf=0x7fa479052688 "update objlistaJosm set HogstaBudTid='2009-11-15 08:00:11'", length=58, parser_state=0x7fa484cd65e0) at /src/10.1/sql/sql_parse.cc:7303
#19 0x00005591e7c13474 in dispatch_command (command=COM_QUERY, thd=0x7fa47e7c9470, packet=0x7fa47d9c92f1 "update objlistaJosm set HogstaBudTid='2009-11-15 08:00:11'", packet_length=58) at /src/10.1/sql/sql_parse.cc:1488
#20 0x00005591e7c121a6 in do_command (thd=0x7fa47e7c9470) at /src/10.1/sql/sql_parse.cc:1109
#21 0x00005591e7d47798 in do_handle_one_connection (thd_arg=0x7fa47e7c9470) at /src/10.1/sql/sql_connect.cc:1349
#22 0x00005591e7d474fc in handle_one_connection (arg=0x7fa47e7c9470) at /src/10.1/sql/sql_connect.cc:1261
#23 0x00005591e844dbb2 in pfs_spawn_thread (arg=0x7fa47d3b0e70) at /src/10.1/storage/perfschema/pfs.cc:1860
#24 0x00007fa4849510a4 in start_thread () from /lib64/libpthread.so.0
#25 0x00007fa482abf04d in clone () from /lib64/libc.so.6
Reduced test case
CREATE TABLE `Auction` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`auctionEnded` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`status` tinyint(4) NOT NULL DEFAULT '0',
`bidTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`md5` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `auctionEnded` (`auctionEnded`),
KEY `status` (`status`)
) DEFAULT CHARSET=utf8;
CREATE TABLE `objlistaJosm` (
`ObjListaNr` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Marknadspris` int(11) DEFAULT NULL,
`Status` tinyint(3) unsigned NOT NULL DEFAULT '0',
`SlagAvgiftMoms` char(20) DEFAULT NULL,
`MomsBudgivning` varchar(95) DEFAULT NULL,
`UppdatKastor` tinyint(3) unsigned NOT NULL DEFAULT '0',
`UppdatKastorVad` char(80) DEFAULT NULL,
`MomsText` varchar(95) DEFAULT NULL,
`AuktionStanger` datetime DEFAULT NULL,
`AuktionForlangSek` int(10) unsigned DEFAULT NULL,
`AuktionNedrakning` datetime DEFAULT NULL,
`HogstaBudTid` datetime DEFAULT NULL,
PRIMARY KEY (`ObjListaNr`)
);
INSERT INTO `objlistaJosm` () VALUES ();
CREATE TRIGGER `SET_AUCTIONSTART` BEFORE UPDATE ON `Auction` FOR EACH ROW BEGIN END;
CREATE TRIGGER `afterUpdateOnObjlistaJosm` AFTER UPDATE ON `objlistaJosm` FOR EACH ROW UPDATE Auction SET bidTime = NEW.HogstaBudTid;
update objlistaJosm set HogstaBudTid='2009-11-15 08:00:11';
Original test case (same as in attachments, just as a single MTR test
set names utf8;
CREATE USER 'root'@'%';
GRANT ALL ON *.* to 'root'@'%';
CREATE TABLE `AuctionHasAuctionItems` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`auctionId` int(10) DEFAULT '0',
`auctionItemId` int(10) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `index` (`auctionId`,`auctionItemId`),
KEY `auctionitemid_idx` (`auctionItemId`)
) ENGINE=MyISAM;
CREATE TABLE `Auction` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`auctionType` tinyint(4) NOT NULL DEFAULT '1',
`auctionStart` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`countdownStart` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`countdownTimer` int(11) NOT NULL DEFAULT '0',
`auctionEnded` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`status` tinyint(4) NOT NULL DEFAULT '0',
`bidId` int(11) NOT NULL DEFAULT '0',
`bid` int(11) NOT NULL DEFAULT '0',
`bidTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`reservationPrice` int(11) NOT NULL DEFAULT '0',
`marketPrice` int(11) NOT NULL DEFAULT '0',
`biddingFee` int(11) NOT NULL DEFAULT '0',
`biddingFeeDealer` int(11) NOT NULL DEFAULT '0',
`lowestBidIncrement` int(11) NOT NULL DEFAULT '0',
`vat` tinyint(4) NOT NULL DEFAULT '0',
`deductibleVat` tinyint(4) DEFAULT '0',
`biddingFeeVat` tinyint(4) NOT NULL DEFAULT '0',
`currency` varchar(3) CHARACTER SET utf8 COLLATE utf8_swedish_ci NOT NULL DEFAULT 'SEK',
`export` tinyint(4) NOT NULL DEFAULT '0',
`businessSystemId` tinyint(4) NOT NULL DEFAULT '0',
`updateBusinessSystem` tinyint(4) NOT NULL DEFAULT '0',
`readByBusinessSystem` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`preliminaryDate` tinyint(4) NOT NULL DEFAULT '0',
`isTestAuction` tinyint(3) unsigned DEFAULT '0',
`md5` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `auctionEnded` (`auctionEnded`),
KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `objlistaJosm` (
`ObjListaNr` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ObjektNr` int(10) unsigned NOT NULL,
`ListNr` int(10) unsigned NOT NULL DEFAULT '0',
`AuktionsTyp` char(60) DEFAULT NULL,
`Lagerort` char(60) DEFAULT NULL,
`Fabrikat` char(30) DEFAULT NULL,
`Modell` char(75) DEFAULT NULL,
`Arsmodell` int(11) DEFAULT NULL,
`Reservationspris` int(11) DEFAULT NULL,
`WebBud` char(5) NOT NULL DEFAULT '0',
`Marknadspris` int(11) DEFAULT NULL,
`Status` tinyint(3) unsigned NOT NULL DEFAULT '0',
`HogstaBudBelopp` int(11) DEFAULT NULL,
`HogstaBudAlias` char(30) DEFAULT NULL,
`Budnr` int(11) DEFAULT NULL,
`Handlaggare` char(5) DEFAULT NULL,
`LagstaPris` int(11) DEFAULT NULL,
`HogstaPris` int(11) DEFAULT NULL,
`LagstaHojning` int(11) DEFAULT NULL,
`Visas` tinyint(3) unsigned NOT NULL DEFAULT '0',
`SlagAvgift` int(11) DEFAULT NULL,
`SlagAvgiftHandlare` int(11) DEFAULT NULL,
`SlagAvgiftMoms` char(20) DEFAULT NULL,
`MomsBudgivning` varchar(95) DEFAULT NULL,
`UppdatKastor` tinyint(3) unsigned NOT NULL DEFAULT '0',
`UppdatKastorVad` char(80) DEFAULT NULL,
`MomsText` varchar(95) DEFAULT NULL,
`AuktionStanger` datetime DEFAULT NULL,
`AuktionForlangSek` int(10) unsigned DEFAULT NULL,
`AuktionNedrakning` datetime DEFAULT NULL,
`HogstaBudTid` datetime DEFAULT NULL,
`AuktionStangdTid` datetime DEFAULT NULL,
`SlagavgiftText` varchar(255) DEFAULT NULL,
`Export` varchar(45) DEFAULT NULL,
`Losenord` varchar(45) DEFAULT NULL,
`LosenordKrav` tinyint(3) unsigned NOT NULL DEFAULT '0',
`Grupp` varchar(8) DEFAULT NULL,
`TimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Ikon` smallint(5) unsigned NOT NULL DEFAULT '0',
`Villkor` varchar(90) DEFAULT NULL,
`Valuta` varchar(45) DEFAULT NULL,
`ObjektUrl` varchar(255) DEFAULT NULL,
`ListBenamning` varchar(90) DEFAULT NULL,
`VisaObjektJOSM` int(10) unsigned NOT NULL DEFAULT '0',
`VisaObjektExport` int(10) unsigned NOT NULL DEFAULT '0',
`VisaObjektNorge` int(10) unsigned NOT NULL DEFAULT '0',
`VisaObjektJOSMPro` int(10) NOT NULL DEFAULT '0',
`ArendeTyp` varchar(45) DEFAULT NULL,
`Finansiering` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Erbjud ej finansiering när värde = 0. (False)',
`Mall` smallint(5) unsigned DEFAULT NULL,
`BildObjektNr` int(10) unsigned DEFAULT NULL,
`AvtalspartNamn` varchar(95) DEFAULT NULL,
`Dragkrok` varchar(255) DEFAULT NULL,
`Objektstyp` varchar(255) DEFAULT NULL,
`Vaxellada` varchar(255) DEFAULT NULL,
`Matarstallning` varchar(255) DEFAULT NULL,
`importerad` tinyint(1) NOT NULL DEFAULT '0',
`MatarstallningEnhet` varchar(45) DEFAULT NULL,
`Drivmedel` varchar(45) DEFAULT NULL,
`VLAkod` varchar(15) DEFAULT NULL,
`AlternativtDrivmedel` varchar(45) DEFAULT NULL,
`AccepterarAutobud` tinyint(1) unsigned NOT NULL DEFAULT '0',
`business_system_id` int(10) unsigned NOT NULL DEFAULT '1',
`countdown_override` varchar(45) DEFAULT NULL,
`marketPrice_To` int(10) NOT NULL DEFAULT '0',
`marketPrice_From` int(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`ObjListaNr`),
UNIQUE KEY `unique_ObjektNr` (`ObjektNr`),
KEY `FabrikatModell` (`Fabrikat`,`Modell`),
KEY `Reservationspris` (`Reservationspris`),
KEY `Lagerort` (`Lagerort`),
KEY `Marknadspris` (`Marknadspris`),
KEY `Nedrakning` (`AuktionNedrakning`),
KEY `AuktionStanger` (`AuktionStanger`),
KEY `ListaSok` (`ListNr`,`Visas`,`Status`,`AuktionNedrakning`,`Grupp`) USING BTREE,
KEY `Fabrikat` (`Fabrikat`),
KEY `Modell` (`Modell`),
KEY `Visas` (`Visas`),
KEY `Grupp` (`Grupp`),
KEY `Status` (`Status`),
KEY `AuktionStangdTid` (`AuktionStangdTid`),
KEY `ListNr` (`ListNr`),
KEY `TimeStamp` (`TimeStamp`),
KEY `HogstaBudAlias` (`HogstaBudAlias`),
KEY `business_system_id` (`business_system_id`),
KEY `Avslutade` (`Status`,`importerad`,`UppdatKastor`,`business_system_id`)
) ENGINE=MyISAM;
INSERT INTO `objlistaJosm` VALUES (240017,708877,0,'Personbilar V.6','Sundsvall','BMW','Drive20d (Aut+GPS+190hk)',2015,355000,'1',401600,0,NULL,NULL,NULL,NULL,NULL,NULL,500,0,NULL,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Ja',NULL,0,NULL,'2016-02-02 15:26:27',0,'','SEK','/objekt/nr/7433/Drive20d_(Aut+GPS+190hk).html','BMW (Aut+GPS+190hk)',1,1,0,0,'Personbil',1,1,808977,'foobar AB','Ja','Personbil','Automat','1305',0,'mil','Diesel','7.1','',1,1,NULL,365456,285136);
--delimiter ;;
CREATE DEFINER=`root`@`%` TRIGGER `SET_AUCTIONSTART` BEFORE UPDATE ON `Auction` FOR EACH ROW BEGIN
IF (NEW.status = 1 OR NEW.status = 11) AND (OLD.status <> 1 and OLD.status <> 11) THEN
SET NEW.auctionStart = NOW();
END IF;
END;;
CREATE DEFINER=`root`@`%` TRIGGER `afterUpdateOnObjlistaJosm` AFTER UPDATE ON `objlistaJosm` FOR EACH ROW BEGIN
IF IFNULL(NEW.Budnr,0) != IFNULL(OLD.Budnr,0) THEN UPDATE Auction t1 JOIN AuctionHasAuctionItems t2 ON t1.id = t2.auctionId SET t1.bidId = IFNULL(NEW.Budnr,0) WHERE t2.auctionItemId = OLD.Objektnr;
END IF;
IF IFNULL(NEW.HogstaBudBelopp,0) != IFNULL(OLD.HogstaBudBelopp,0) THEN UPDATE Auction t1 JOIN AuctionHasAuctionItems t2 ON t1.id = t2.auctionId SET t1.bid = IFNULL(NEW.HogstaBudBelopp,0) WHERE t2.auctionItemId = OLD.Objektnr;
END IF;
IF IFNULL(NEW.HogstaBudTid,'0000-00-00 00:00:00') != IFNULL(OLD.HogstaBudTid,'0000-00-00 00:00:00')
THEN UPDATE Auction t1
JOIN AuctionHasAuctionItems t2
ON t1.id = t2.auctionId
SET t1.bidTime = IFNULL(NEW.HogstaBudTid,'0000-00-00 00:00:00')
WHERE t2.auctionItemId = OLD.Objektnr;
END IF;
IF IFNULL(NEW.AuktionStangdTid,'0000-00-00 00:00:00') != IFNULL(OLD.AuktionStangdTid,'0000-00-00 00:00:00')
THEN UPDATE Auction t1
JOIN AuctionHasAuctionItems t2
ON t1.id = t2.auctionId
SET t1.auctionEnded = IFNULL(NEW.AuktionStangdTid,'0000-00-00 00:00:00'), t1.status = 2
WHERE t2.auctionItemId = OLD.Objektnr;
END IF;
IF IFNULL(NEW.`status`,'0') != IFNULL(OLD.`status`,'0') THEN UPDATE Auction t1 JOIN AuctionHasAuctionItems t2 ON t1.id = t2.auctionId SET t1.`status` = NEW.status WHERE t2.auctionItemId = OLD.Objektnr;
END IF;
IF IFNULL(NEW.visas,'0') != IFNULL(OLD.visas,'0') THEN UPDATE Auction t1 JOIN AuctionHasAuctionItems t2 ON t1.id = t2.auctionId SET t1.`status` = IF(IFNULL(NEW.visas,'0') = 0,0,NEW.`status`) WHERE t2.auctionItemId = OLD.Objektnr;
END IF;
END;;
--delimiter ;
update objlistaJosm set HogstaBudTid="2009-11-15 08:00:11" where ObjListaNr='240017';
Easy to guess, the problem was introduced by this change:
commit 0686c34d22a5cbf93015012eaf77a4a977b63afb
Author: Sergei Golubchik <serg@mariadb.org>
Date: Sat Nov 14 22:51:54 2015 +0100
MDEV-8605 MariaDB not use DEFAULT value even when inserted NULL for NOT NULLABLE column
NOT NULL constraint must be checked *after* the BEFORE triggers.
That is for INSERT and UPDATE statements even NOT NULL fields
must be able to store a NULL temporarily at least while
BEFORE INSERT/UPDATE triggers are running.
People
Sergei Golubchik
jostein martinsen
Votes:
0Vote for this issue
Watchers:
3Start watching this issue
Dates
Created:
Updated:
Resolved:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
Bug
Major
Thanks for the report and the test case.
Stack trace from 10.1 commit d80b8442a68093106e00a9a38b7b2c593002a72c
#3 <signal handler called>#4 0x00005591e7e6a105 in set_field_to_new_field (field=0x7fa4791c8d48, new_field=0x7fa4790b2de0) at /src/10.1/sql/item.cc:2387#5 0x00005591e7e6a14e in Item_field::switch_to_nullable_fields_processor (this=0x7fa4791c8c70, arg=0x7fa4790b2de0 "\210\320\024y\244\177") at /src/10.1/sql/item.cc:2395#6 0x00005591e7b66bdb in Item::walk (this=0x7fa4791c8c70, processor=&virtual table offset 944, walk_subquery=true, arg=0x7fa4790b2de0 "\210\320\024y\244\177") at /src/10.1/sql/item.h:1367#7 0x00005591e7bbd444 in switch_to_nullable_trigger_fields (items=..., table=0x7fa478e28670) at /src/10.1/sql/sql_base.cc:8837#8 0x00005591e7cefe93 in mysql_update (thd=0x7fa47e7c9470, table_list=0x7fa4791c8510, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7fa484cd46a0, updated_return=0x7fa484cd4730) at /src/10.1/sql/sql_update.cc:459#9 0x00005591e7c19559 in mysql_execute_command (thd=0x7fa47e7c9470) at /src/10.1/sql/sql_parse.cc:3749#10 0x00005591e7fa17e6 in sp_instr_stmt::exec_core (this=0x7fa4791c8dc8, thd=0x7fa47e7c9470, nextp=0x7fa484cd4f74) at /src/10.1/sql/sp_head.cc:3213#11 0x00005591e7fa0f18 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fa4791c8e08, thd=0x7fa47e7c9470, nextp=0x7fa484cd4f74, open_tables=false, instr=0x7fa4791c8dc8) at /src/10.1/sql/sp_head.cc:2979#12 0x00005591e7fa14ea in sp_instr_stmt::execute (this=0x7fa4791c8dc8, thd=0x7fa47e7c9470, nextp=0x7fa484cd4f74) at /src/10.1/sql/sp_head.cc:3129#13 0x00005591e7f9cddc in sp_head::execute (this=0x7fa4791c7888, thd=0x7fa47e7c9470, merge_da_on_success=false) at /src/10.1/sql/sp_head.cc:1317#14 0x00005591e7f9d91f in sp_head::execute_trigger (this=0x7fa4791c7888, thd=0x7fa47e7c9470, db_name=0x7fa47905cdb8, table_name=0x7fa47905cdc8, grant_info=0x7fa47913bc38) at /src/10.1/sql/sp_head.cc:1646#15 0x00005591e7ce7115 in Table_triggers_list::process_triggers (this=0x7fa47913ba88, thd=0x7fa47e7c9470, event=TRG_EVENT_UPDATE, time_type=TRG_ACTION_AFTER, old_row_is_record1=true) at /src/10.1/sql/sql_trigger.cc:2176#16 0x00005591e7cf1163 in mysql_update (thd=0x7fa47e7c9470, table_list=0x7fa4790527c0, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7fa484cd5ca0, updated_return=0x7fa484cd5d30) at /src/10.1/sql/sql_update.cc:860#17 0x00005591e7c19559 in mysql_execute_command (thd=0x7fa47e7c9470) at /src/10.1/sql/sql_parse.cc:3749#18 0x00005591e7c2477a in mysql_parse (thd=0x7fa47e7c9470, rawbuf=0x7fa479052688 "update objlistaJosm set HogstaBudTid='2009-11-15 08:00:11'", length=58, parser_state=0x7fa484cd65e0) at /src/10.1/sql/sql_parse.cc:7303#19 0x00005591e7c13474 in dispatch_command (command=COM_QUERY, thd=0x7fa47e7c9470, packet=0x7fa47d9c92f1 "update objlistaJosm set HogstaBudTid='2009-11-15 08:00:11'", packet_length=58) at /src/10.1/sql/sql_parse.cc:1488#20 0x00005591e7c121a6 in do_command (thd=0x7fa47e7c9470) at /src/10.1/sql/sql_parse.cc:1109#21 0x00005591e7d47798 in do_handle_one_connection (thd_arg=0x7fa47e7c9470) at /src/10.1/sql/sql_connect.cc:1349#22 0x00005591e7d474fc in handle_one_connection (arg=0x7fa47e7c9470) at /src/10.1/sql/sql_connect.cc:1261#23 0x00005591e844dbb2 in pfs_spawn_thread (arg=0x7fa47d3b0e70) at /src/10.1/storage/perfschema/pfs.cc:1860#24 0x00007fa4849510a4 in start_thread () from /lib64/libpthread.so.0#25 0x00007fa482abf04d in clone () from /lib64/libc.so.6Reduced test case
CREATE TABLE `Auction` (`id` int(10) NOT NULL AUTO_INCREMENT,`auctionEnded` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',`status` tinyint(4) NOT NULL DEFAULT '0',`bidTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',`md5` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`),KEY `auctionEnded` (`auctionEnded`),KEY `status` (`status`)) DEFAULT CHARSET=utf8;CREATE TABLE `objlistaJosm` (`ObjListaNr` int(10) unsigned NOT NULL AUTO_INCREMENT,`Marknadspris` int(11) DEFAULT NULL,`Status` tinyint(3) unsigned NOT NULL DEFAULT '0',`SlagAvgiftMoms` char(20) DEFAULT NULL,`MomsBudgivning` varchar(95) DEFAULT NULL,`UppdatKastor` tinyint(3) unsigned NOT NULL DEFAULT '0',`UppdatKastorVad` char(80) DEFAULT NULL,`MomsText` varchar(95) DEFAULT NULL,`AuktionStanger` datetime DEFAULT NULL,`AuktionForlangSek` int(10) unsigned DEFAULT NULL,`AuktionNedrakning` datetime DEFAULT NULL,`HogstaBudTid` datetime DEFAULT NULL,PRIMARY KEY (`ObjListaNr`));INSERT INTO `objlistaJosm` () VALUES ();CREATE TRIGGER `SET_AUCTIONSTART` BEFORE UPDATE ON `Auction` FOR EACH ROW BEGIN END;CREATE TRIGGER `afterUpdateOnObjlistaJosm` AFTER UPDATE ON `objlistaJosm` FOR EACH ROW UPDATE Auction SET bidTime = NEW.HogstaBudTid;update objlistaJosm set HogstaBudTid='2009-11-15 08:00:11';Original test case (same as in attachments, just as a single MTR test
set names utf8;CREATE USER 'root'@'%';GRANT ALL ON *.* to 'root'@'%';CREATE TABLE `AuctionHasAuctionItems` (`id` int(10) NOT NULL AUTO_INCREMENT,`auctionId` int(10) DEFAULT '0',`auctionItemId` int(10) DEFAULT '0',PRIMARY KEY (`id`),KEY `index` (`auctionId`,`auctionItemId`),KEY `auctionitemid_idx` (`auctionItemId`)) ENGINE=MyISAM;CREATE TABLE `Auction` (`id` int(10) NOT NULL AUTO_INCREMENT,`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`auctionType` tinyint(4) NOT NULL DEFAULT '1',`auctionStart` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',`countdownStart` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',`countdownTimer` int(11) NOT NULL DEFAULT '0',`auctionEnded` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',`status` tinyint(4) NOT NULL DEFAULT '0',`bidId` int(11) NOT NULL DEFAULT '0',`bid` int(11) NOT NULL DEFAULT '0',`bidTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',`reservationPrice` int(11) NOT NULL DEFAULT '0',`marketPrice` int(11) NOT NULL DEFAULT '0',`biddingFee` int(11) NOT NULL DEFAULT '0',`biddingFeeDealer` int(11) NOT NULL DEFAULT '0',`lowestBidIncrement` int(11) NOT NULL DEFAULT '0',`vat` tinyint(4) NOT NULL DEFAULT '0',`deductibleVat` tinyint(4) DEFAULT '0',`biddingFeeVat` tinyint(4) NOT NULL DEFAULT '0',`currency` varchar(3) CHARACTER SET utf8 COLLATE utf8_swedish_ci NOT NULL DEFAULT 'SEK',`export` tinyint(4) NOT NULL DEFAULT '0',`businessSystemId` tinyint(4) NOT NULL DEFAULT '0',`updateBusinessSystem` tinyint(4) NOT NULL DEFAULT '0',`readByBusinessSystem` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',`preliminaryDate` tinyint(4) NOT NULL DEFAULT '0',`isTestAuction` tinyint(3) unsigned DEFAULT '0',`md5` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`),KEY `auctionEnded` (`auctionEnded`),KEY `status` (`status`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;CREATE TABLE `objlistaJosm` (`ObjListaNr` int(10) unsigned NOT NULL AUTO_INCREMENT,`ObjektNr` int(10) unsigned NOT NULL,`ListNr` int(10) unsigned NOT NULL DEFAULT '0',`AuktionsTyp` char(60) DEFAULT NULL,`Lagerort` char(60) DEFAULT NULL,`Fabrikat` char(30) DEFAULT NULL,`Modell` char(75) DEFAULT NULL,`Arsmodell` int(11) DEFAULT NULL,`Reservationspris` int(11) DEFAULT NULL,`WebBud` char(5) NOT NULL DEFAULT '0',`Marknadspris` int(11) DEFAULT NULL,`Status` tinyint(3) unsigned NOT NULL DEFAULT '0',`HogstaBudBelopp` int(11) DEFAULT NULL,`HogstaBudAlias` char(30) DEFAULT NULL,`Budnr` int(11) DEFAULT NULL,`Handlaggare` char(5) DEFAULT NULL,`LagstaPris` int(11) DEFAULT NULL,`HogstaPris` int(11) DEFAULT NULL,`LagstaHojning` int(11) DEFAULT NULL,`Visas` tinyint(3) unsigned NOT NULL DEFAULT '0',`SlagAvgift` int(11) DEFAULT NULL,`SlagAvgiftHandlare` int(11) DEFAULT NULL,`SlagAvgiftMoms` char(20) DEFAULT NULL,`MomsBudgivning` varchar(95) DEFAULT NULL,`UppdatKastor` tinyint(3) unsigned NOT NULL DEFAULT '0',`UppdatKastorVad` char(80) DEFAULT NULL,`MomsText` varchar(95) DEFAULT NULL,`AuktionStanger` datetime DEFAULT NULL,`AuktionForlangSek` int(10) unsigned DEFAULT NULL,`AuktionNedrakning` datetime DEFAULT NULL,`HogstaBudTid` datetime DEFAULT NULL,`AuktionStangdTid` datetime DEFAULT NULL,`SlagavgiftText` varchar(255) DEFAULT NULL,`Export` varchar(45) DEFAULT NULL,`Losenord` varchar(45) DEFAULT NULL,`LosenordKrav` tinyint(3) unsigned NOT NULL DEFAULT '0',`Grupp` varchar(8) DEFAULT NULL,`TimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`Ikon` smallint(5) unsigned NOT NULL DEFAULT '0',`Villkor` varchar(90) DEFAULT NULL,`Valuta` varchar(45) DEFAULT NULL,`ObjektUrl` varchar(255) DEFAULT NULL,`ListBenamning` varchar(90) DEFAULT NULL,`VisaObjektJOSM` int(10) unsigned NOT NULL DEFAULT '0',`VisaObjektExport` int(10) unsigned NOT NULL DEFAULT '0',`VisaObjektNorge` int(10) unsigned NOT NULL DEFAULT '0',`VisaObjektJOSMPro` int(10) NOT NULL DEFAULT '0',`ArendeTyp` varchar(45) DEFAULT NULL,`Finansiering` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Erbjud ej finansiering när värde = 0. (False)',`Mall` smallint(5) unsigned DEFAULT NULL,`BildObjektNr` int(10) unsigned DEFAULT NULL,`AvtalspartNamn` varchar(95) DEFAULT NULL,`Dragkrok` varchar(255) DEFAULT NULL,`Objektstyp` varchar(255) DEFAULT NULL,`Vaxellada` varchar(255) DEFAULT NULL,`Matarstallning` varchar(255) DEFAULT NULL,`importerad` tinyint(1) NOT NULL DEFAULT '0',`MatarstallningEnhet` varchar(45) DEFAULT NULL,`Drivmedel` varchar(45) DEFAULT NULL,`VLAkod` varchar(15) DEFAULT NULL,`AlternativtDrivmedel` varchar(45) DEFAULT NULL,`AccepterarAutobud` tinyint(1) unsigned NOT NULL DEFAULT '0',`business_system_id` int(10) unsigned NOT NULL DEFAULT '1',`countdown_override` varchar(45) DEFAULT NULL,`marketPrice_To` int(10) NOT NULL DEFAULT '0',`marketPrice_From` int(10) NOT NULL DEFAULT '0',PRIMARY KEY (`ObjListaNr`),UNIQUE KEY `unique_ObjektNr` (`ObjektNr`),KEY `FabrikatModell` (`Fabrikat`,`Modell`),KEY `Reservationspris` (`Reservationspris`),KEY `Lagerort` (`Lagerort`),KEY `Marknadspris` (`Marknadspris`),KEY `Nedrakning` (`AuktionNedrakning`),KEY `AuktionStanger` (`AuktionStanger`),KEY `ListaSok` (`ListNr`,`Visas`,`Status`,`AuktionNedrakning`,`Grupp`) USING BTREE,KEY `Fabrikat` (`Fabrikat`),KEY `Modell` (`Modell`),KEY `Visas` (`Visas`),KEY `Grupp` (`Grupp`),KEY `Status` (`Status`),KEY `AuktionStangdTid` (`AuktionStangdTid`),KEY `ListNr` (`ListNr`),KEY `TimeStamp` (`TimeStamp`),KEY `HogstaBudAlias` (`HogstaBudAlias`),KEY `business_system_id` (`business_system_id`),KEY `Avslutade` (`Status`,`importerad`,`UppdatKastor`,`business_system_id`)) ENGINE=MyISAM;INSERT INTO `objlistaJosm` VALUES (240017,708877,0,'Personbilar V.6','Sundsvall','BMW','Drive20d (Aut+GPS+190hk)',2015,355000,'1',401600,0,NULL,NULL,NULL,NULL,NULL,NULL,500,0,NULL,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Ja',NULL,0,NULL,'2016-02-02 15:26:27',0,'','SEK','/objekt/nr/7433/Drive20d_(Aut+GPS+190hk).html','BMW (Aut+GPS+190hk)',1,1,0,0,'Personbil',1,1,808977,'foobar AB','Ja','Personbil','Automat','1305',0,'mil','Diesel','7.1','',1,1,NULL,365456,285136);--delimiter ;;CREATE DEFINER=`root`@`%` TRIGGER `SET_AUCTIONSTART` BEFORE UPDATE ON `Auction` FOR EACH ROW BEGINIF (NEW.status = 1 OR NEW.status = 11) AND (OLD.status <> 1 and OLD.status <> 11) THENSET NEW.auctionStart = NOW();END IF;END;;CREATE DEFINER=`root`@`%` TRIGGER `afterUpdateOnObjlistaJosm` AFTER UPDATE ON `objlistaJosm` FOR EACH ROW BEGINIF IFNULL(NEW.Budnr,0) != IFNULL(OLD.Budnr,0) THEN UPDATE Auction t1 JOIN AuctionHasAuctionItems t2 ON t1.id = t2.auctionId SET t1.bidId = IFNULL(NEW.Budnr,0) WHERE t2.auctionItemId = OLD.Objektnr;END IF;IF IFNULL(NEW.HogstaBudBelopp,0) != IFNULL(OLD.HogstaBudBelopp,0) THEN UPDATE Auction t1 JOIN AuctionHasAuctionItems t2 ON t1.id = t2.auctionId SET t1.bid = IFNULL(NEW.HogstaBudBelopp,0) WHERE t2.auctionItemId = OLD.Objektnr;END IF;IF IFNULL(NEW.HogstaBudTid,'0000-00-00 00:00:00') != IFNULL(OLD.HogstaBudTid,'0000-00-00 00:00:00')THEN UPDATE Auction t1JOIN AuctionHasAuctionItems t2ON t1.id = t2.auctionIdSET t1.bidTime = IFNULL(NEW.HogstaBudTid,'0000-00-00 00:00:00')WHERE t2.auctionItemId = OLD.Objektnr;END IF;IF IFNULL(NEW.AuktionStangdTid,'0000-00-00 00:00:00') != IFNULL(OLD.AuktionStangdTid,'0000-00-00 00:00:00')THEN UPDATE Auction t1JOIN AuctionHasAuctionItems t2ON t1.id = t2.auctionIdSET t1.auctionEnded = IFNULL(NEW.AuktionStangdTid,'0000-00-00 00:00:00'), t1.status = 2WHERE t2.auctionItemId = OLD.Objektnr;END IF;IF IFNULL(NEW.`status`,'0') != IFNULL(OLD.`status`,'0') THEN UPDATE Auction t1 JOIN AuctionHasAuctionItems t2 ON t1.id = t2.auctionId SET t1.`status` = NEW.status WHERE t2.auctionItemId = OLD.Objektnr;END IF;IF IFNULL(NEW.visas,'0') != IFNULL(OLD.visas,'0') THEN UPDATE Auction t1 JOIN AuctionHasAuctionItems t2 ON t1.id = t2.auctionId SET t1.`status` = IF(IFNULL(NEW.visas,'0') = 0,0,NEW.`status`) WHERE t2.auctionItemId = OLD.Objektnr;END IF;END;;--delimiter ;update objlistaJosm set HogstaBudTid="2009-11-15 08:00:11" where ObjListaNr='240017';Easy to guess, the problem was introduced by this change:
commit 0686c34d22a5cbf93015012eaf77a4a977b63afbAuthor: Sergei Golubchik <serg@mariadb.org>Date: Sat Nov 14 22:51:54 2015 +0100MDEV-8605 MariaDB not use DEFAULT value even when inserted NULL for NOT NULLABLE columnNOT NULL constraint must be checked *after* the BEFORE triggers.That is for INSERT and UPDATE statements even NOT NULL fieldsmust be able to store a NULL temporarily at least whileBEFORE INSERT/UPDATE triggers are running.