|
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.
|
|
JosmWeb.Auction.schema.sql