[MDEV-11842] Fail to insert on a table where a field has no default - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 10.1.21
Fix Version/s: 10.1.22, 10.2.5
Component/s: Triggers
Labels:
- 10.2-ga
- innodb
- insert
- regression
Environment:
Centos 6.8, GCL

Sprint:
10.1.22

Description

Mariadb updated this morning from 10.1.20 to 10.1.21

After the update if inserting a few fields into the table where there are other fields that have not been set and do not have defaults, The insert fails without any error. Setting default values on all fields resolves the error.

CREATE TABLE `vendors` (

	`id` INT(11) NOT NULL AUTO_INCREMENT,

	`vendor_name` VARCHAR(100) NOT NULL,

	`address1` VARCHAR(128) NOT NULL DEFAULT '',

	`address2` VARCHAR(40) NOT NULL DEFAULT '',

	`regionId` INT(11) NULL DEFAULT NULL,

	`areaId` INT(11) NULL DEFAULT NULL,

	`cityId` INT(11) NULL DEFAULT NULL,

	`townId` INT(11) NULL DEFAULT NULL,

	`city_code` VARCHAR(5) NOT NULL DEFAULT '',

	`post_code` VARCHAR(15) NOT NULL DEFAULT '',

	PRIMARY KEY (`id`)

) COLLATE='utf8_general_ci' ENGINE=InnoDB;

insert into vendors (`post_code`) VALUES ('CR0 1NN');

The above works for about 20 inserts then fails without error.

setting a default for vendor_name allows the query to work again.

Attachments

Issue Links

duplicates

MDEV-11886 Trigger prevents insert after update to 10.1.21-MariaDB-1~trusty

Closed

MDEV-11951 Triggers doesn't work

Closed

MDEV-12100 INSERT silently ignored

Closed

relates to

MDEV-19761 Before Trigger not processed for Not Null Columns if no explicit value and no DEFAULT

Closed

Activity

Ascending order - Click to sort in descending order

Habib created issue - 2017-01-19 14:50

Stanislav Petr added a comment - 2017-01-20 04:47 - edited

I see the same issue, but only when BEFORE INSERT trigger exists. For example:

Now lets create test table

CREATE TABLE `test` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `data1` varchar(100) NOT NULL,

  `data2` varchar(100) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

Insert without default value generates a warning message:

MariaDB [test]> insert into `test` (`data2`) values ('x');

Query OK, 1 row affected, 1 warning (0.17 sec)

MariaDB [test]> show warnings;

+---------+------+--------------------------------------------+

| Level   | Code | Message                                    |

+---------+------+--------------------------------------------+

| Warning | 1364 | Field 'data1' doesn't have a default value |

+---------+------+--------------------------------------------+

1 row in set (0.00 sec)

This is OK - 1 row inserted. And now try to create BEFORE INSERT trigger:

CREATE DEFINER = CURRENT_USER TRIGGER `test_trigger`

BEFORE INSERT ON `test` FOR EACH ROW BEGIN END;

And repeat the same insert:

MariaDB [test]> insert into `test` (`data2`) values ('x');

Query OK, 0 rows affected (0.00 sec)

0 rows affected!!!!

Stanislav Petr added a comment - 2017-01-20 04:47 - edited I see the same issue, but only when BEFORE INSERT trigger exists. For example: Now lets create test table CREATE TABLE `test` ( `id` int (11) NOT NULL AUTO_INCREMENT, `data1` varchar (100) NOT NULL , `data2` varchar (100) NOT NULL DEFAULT '' , PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; Insert without default value generates a warning message: MariaDB [test]> insert into `test` (`data2`) values ('x'); Query OK, 1 row affected, 1 warning (0.17 sec) MariaDB [test]> show warnings; +---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1364 | Field 'data1' doesn't have a default value | +---------+------+--------------------------------------------+ 1 row in set (0.00 sec) This is OK - 1 row inserted. And now try to create BEFORE INSERT trigger: CREATE DEFINER = CURRENT_USER TRIGGER `test_trigger` BEFORE INSERT ON `test` FOR EACH ROW BEGIN END ; And repeat the same insert: MariaDB [test]> insert into `test` (`data2`) values ('x'); Query OK, 0 rows affected (0.00 sec) 0 rows affected!!!!

Habib added a comment - 2017-01-20 13:41

I forgot to mention taht a before insert trigger exists.

Habib added a comment - 2017-01-20 13:41 I forgot to mention taht a before insert trigger exists.

Elena Stepanova made changes - 2017-01-23 14:48

Field	Original Value	New Value
Status	Open [ 1 ]	Confirmed [ 10101 ]

Elena Stepanova made changes - 2017-01-23 14:48

Priority

Major [ 3 ]

Blocker [ 1 ]

Elena Stepanova made changes - 2017-01-23 14:50

Description

Mariadb updated this morning from 10.1.20 to 10.1.21

After the update if inserting a few fields into the table where there are other fields that have not been set and do not have defaults, The insert fails without any error. Setting default values on all fields resolves the error.

CREATE TABLE `vendors` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`vendor_name` VARCHAR(100) NOT NULL,
`address1` VARCHAR(128) NOT NULL DEFAULT '',
`address2` VARCHAR(40) NOT NULL DEFAULT '',
`regionId` INT(11) NULL DEFAULT NULL,
`areaId` INT(11) NULL DEFAULT NULL,
`cityId` INT(11) NULL DEFAULT NULL,
`townId` INT(11) NULL DEFAULT NULL,
`city_code` VARCHAR(5) NOT NULL DEFAULT '',
`post_code` VARCHAR(15) NOT NULL DEFAULT ''
PRIMARY KEY (`id`)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

insert into vendors (`post_code`) VALUES ('CR0 1NN');

The above works for about 20 inserts then fails without error.

setting a default for vendor_name allows the query to work again.

Mariadb updated this morning from 10.1.20 to 10.1.21

After the update if inserting a few fields into the table where there are other fields that have not been set and do not have defaults, The insert fails without any error. Setting default values on all fields resolves the error.

{code:sql}
CREATE TABLE `vendors` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`vendor_name` VARCHAR(100) NOT NULL,
`address1` VARCHAR(128) NOT NULL DEFAULT '',
`address2` VARCHAR(40) NOT NULL DEFAULT '',
`regionId` INT(11) NULL DEFAULT NULL,
`areaId` INT(11) NULL DEFAULT NULL,
`cityId` INT(11) NULL DEFAULT NULL,
`townId` INT(11) NULL DEFAULT NULL,
`city_code` VARCHAR(5) NOT NULL DEFAULT '',
`post_code` VARCHAR(15) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) COLLATE='utf8_general_ci'
ENGINE=InnoDB

insert into vendors (`post_code`) VALUES ('CR0 1NN');
{code}

The above works for about 20 inserts then fails without error.

setting a default for vendor_name allows the query to work again.

Elena Stepanova made changes - 2017-01-23 14:50

Description

Mariadb updated this morning from 10.1.20 to 10.1.21

After the update if inserting a few fields into the table where there are other fields that have not been set and do not have defaults, The insert fails without any error. Setting default values on all fields resolves the error.

{code:sql}
CREATE TABLE `vendors` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`vendor_name` VARCHAR(100) NOT NULL,
`address1` VARCHAR(128) NOT NULL DEFAULT '',
`address2` VARCHAR(40) NOT NULL DEFAULT '',
`regionId` INT(11) NULL DEFAULT NULL,
`areaId` INT(11) NULL DEFAULT NULL,
`cityId` INT(11) NULL DEFAULT NULL,
`townId` INT(11) NULL DEFAULT NULL,
`city_code` VARCHAR(5) NOT NULL DEFAULT '',
`post_code` VARCHAR(15) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) COLLATE='utf8_general_ci'
ENGINE=InnoDB

insert into vendors (`post_code`) VALUES ('CR0 1NN');
{code}

The above works for about 20 inserts then fails without error.

setting a default for vendor_name allows the query to work again.

Mariadb updated this morning from 10.1.20 to 10.1.21

After the update if inserting a few fields into the table where there are other fields that have not been set and do not have defaults, The insert fails without any error. Setting default values on all fields resolves the error.

{code:sql}
CREATE TABLE `vendors` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`vendor_name` VARCHAR(100) NOT NULL,
`address1` VARCHAR(128) NOT NULL DEFAULT '',
`address2` VARCHAR(40) NOT NULL DEFAULT '',
`regionId` INT(11) NULL DEFAULT NULL,
`areaId` INT(11) NULL DEFAULT NULL,
`cityId` INT(11) NULL DEFAULT NULL,
`townId` INT(11) NULL DEFAULT NULL,
`city_code` VARCHAR(5) NOT NULL DEFAULT '',
`post_code` VARCHAR(15) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

insert into vendors (`post_code`) VALUES ('CR0 1NN');
{code}

The above works for about 20 inserts then fails without error.

setting a default for vendor_name allows the query to work again.

Elena Stepanova added a comment - 2017-01-23 15:25 - edited

Thanks for the report and test case.

The problem appeared with the following revision:

commit ef8003eb9a23007ac5d606530dcdcc3ea2f0c039

Author: Sergei Golubchik <serg@mariadb.org>

Date:   Mon Jan 16 18:23:02 2017 +0100

    MDEV-11698 Old Bug possibly not fixed; BEFORE INSERT Trigger on NOT NULL

The debug version fails with the assertion failure:

10.1 a14638581b4c8ef175e68dccff07967d819b3b7e
mysqld: /data/src/10.1/sql/protocol.cc:532: void Protocol::end_statement(): Assertion `0' failed.
170123 17:24:48 [ERROR] mysqld got signal 6 ;

#7 0x00007f7024a37312 in __GI___assert_fail (assertion=0x7f7027b3e130 "0", file=0x7f7027b3e1b0 "/data/src/10.1/sql/protocol.cc", line=532, function=0x7f7027b3eba0 <Protocol::end_statement()::__PRETTY_FUNCTION__> "void Protocol::end_statement()") at assert.c:101
#8 0x00007f7027188886 in Protocol::end_statement (this=0x7f701bb76600) at /data/src/10.1/sql/protocol.cc:532
#9 0x00007f702723e1c0 in dispatch_command (command=COM_QUERY, thd=0x7f701bb76070, packet=0x7f701baa4071 "insert into `test` (`data2`) values ('x')", packet_length=41) at /data/src/10.1/sql/sql_parse.cc:1941
#10 0x00007f702723b9a3 in do_command (thd=0x7f701bb76070) at /data/src/10.1/sql/sql_parse.cc:1109
#11 0x00007f7027371982 in do_handle_one_connection (thd_arg=0x7f701bb76070) at /data/src/10.1/sql/sql_connect.cc:1349
#12 0x00007f70273716e6 in handle_one_connection (arg=0x7f701bb76070) at /data/src/10.1/sql/sql_connect.cc:1261
#13 0x00007f7027654c76 in pfs_spawn_thread (arg=0x7f701ba8cff0) at /data/src/10.1/storage/perfschema/pfs.cc:1860
#14 0x00007f70269390a4 in start_thread (arg=0x7f7028144b00) at pthread_create.c:309
#15 0x00007f7024af187d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

Elena Stepanova added a comment - 2017-01-23 15:25 - edited Thanks for the report and test case. The problem appeared with the following revision: commit ef8003eb9a23007ac5d606530dcdcc3ea2f0c039 Author: Sergei Golubchik <serg@mariadb.org> Date: Mon Jan 16 18:23:02 2017 +0100 MDEV-11698 Old Bug possibly not fixed; BEFORE INSERT Trigger on NOT NULL The debug version fails with the assertion failure: 10.1 a14638581b4c8ef175e68dccff07967d819b3b7e mysqld: /data/src/10.1/sql/protocol.cc:532: void Protocol::end_statement(): Assertion `0' failed. 170123 17:24:48 [ERROR] mysqld got signal 6 ; #7 0x00007f7024a37312 in __GI___assert_fail (assertion=0x7f7027b3e130 "0", file=0x7f7027b3e1b0 "/data/src/10.1/sql/protocol.cc", line=532, function=0x7f7027b3eba0 <Protocol::end_statement()::__PRETTY_FUNCTION__> "void Protocol::end_statement()") at assert.c:101 #8 0x00007f7027188886 in Protocol::end_statement (this=0x7f701bb76600) at /data/src/10.1/sql/protocol.cc:532 #9 0x00007f702723e1c0 in dispatch_command (command=COM_QUERY, thd=0x7f701bb76070, packet=0x7f701baa4071 "insert into `test` (`data2`) values ('x')", packet_length=41) at /data/src/10.1/sql/sql_parse.cc:1941 #10 0x00007f702723b9a3 in do_command (thd=0x7f701bb76070) at /data/src/10.1/sql/sql_parse.cc:1109 #11 0x00007f7027371982 in do_handle_one_connection (thd_arg=0x7f701bb76070) at /data/src/10.1/sql/sql_connect.cc:1349 #12 0x00007f70273716e6 in handle_one_connection (arg=0x7f701bb76070) at /data/src/10.1/sql/sql_connect.cc:1261 #13 0x00007f7027654c76 in pfs_spawn_thread (arg=0x7f701ba8cff0) at /data/src/10.1/storage/perfschema/pfs.cc:1860 #14 0x00007f70269390a4 in start_thread (arg=0x7f7028144b00) at pthread_create.c:309 #15 0x00007f7024af187d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

Elena Stepanova made changes - 2017-01-23 15:25

Fix Version/s		10.1 [ 16100 ]
Fix Version/s		10.2 [ 14601 ]
Assignee		Sergei Golubchik [ serg ]

Elena Stepanova made changes - 2017-01-23 15:26

Component/s

Triggers [ 10109 ]

Elena Stepanova made changes - 2017-01-23 15:26

Labels

innodb insert

innodb insert regression

Elena Stepanova made changes - 2017-01-23 16:18

Link

This issue duplicates ~~MDEV-11886~~ [ ~~MDEV-11886~~ ]

Elena Stepanova made changes - 2017-01-24 12:21

Labels

innodb insert regression

10.2-ga innodb insert regression

Elena Stepanova made changes - 2017-02-01 18:08

Link

This issue duplicates ~~MDEV-11951~~ [ ~~MDEV-11951~~ ]

azurit added a comment - 2017-02-15 19:42

Any news? Will this be fixed in 10.1.22? When it's going to be released? Thank you.

azurit added a comment - 2017-02-15 19:42 Any news? Will this be fixed in 10.1.22? When it's going to be released? Thank you.

Sergei Golubchik added a comment - 2017-02-16 12:12

Yes, it will be fixed in 10.1.22 (that's what the priority "Blocker" mean). The planned release date it at the main https://jira.mariadb.org page (at the moment it says 2017-03-10)

Sergei Golubchik added a comment - 2017-02-16 12:12 Yes, it will be fixed in 10.1.22 (that's what the priority "Blocker" mean). The planned release date it at the main https://jira.mariadb.org page (at the moment it says 2017-03-10)

Elena Stepanova made changes - 2017-02-21 10:03

Link

This issue duplicates ~~MDEV-12100~~ [ ~~MDEV-12100~~ ]

Sergei Golubchik made changes - 2017-03-01 22:59

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2017-03-01 22:59

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2017-03-02 06:41

Component/s	Data Manipulation - Update [ 10805 ]
Fix Version/s		10.1.22 [ 22502 ]
Fix Version/s		10.2.5 [ 22117 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Rasmus Johansson (Inactive) made changes - 2017-03-02 12:12

Sprint

10.1.22 [ 143 ]

Anel Husakovic made changes - 2019-06-26 09:54

Link

This issue relates to ~~MDEV-19761~~ [ ~~MDEV-19761~~ ]

Sergei Golubchik made changes - 2021-12-06 21:44

Workflow

MariaDB v3 [ 79278 ]

MariaDB v4 [ 151561 ]

People

Assignee:: Sergei Golubchik

Reporter:: Habib

Votes:: 8 Vote for this issue

Watchers:: 12 Start watching this issue

Dates

Created:: 2017-01-19 14:50

Updated:: 2019-06-26 09:54

Resolved:: 2017-03-02 06:41

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration