[MDEV-22756] SQL Error (1364): Field 'DB_ROW_HASH_1' doesn't have a default value - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.4.12, 10.4.13, 10.4(EOL), 10.5, 10.6
Fix Version/s: 10.11.3, 10.4.29, 10.5.20, 10.6.13, 10.8.8, 10.9.6, 10.10.4
Component/s: Data Manipulation - Insert
Labels:
None
Environment:
Windows 10 64-bit

Description

For the following table:

CREATE TABLE `demo_upsert` (

    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,

    `Url` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',

    `LastListID` BIGINT(20) NULL DEFAULT NULL,

    `Statistics` BIGINT(20) NOT NULL DEFAULT '0',

    PRIMARY KEY (`ID`) USING BTREE,

    UNIQUE INDEX `Url` (`Url`) USING HASH

COLLATE='utf8mb4_unicode_ci'

ENGINE=InnoDB

When I run the following query:

INSERT INTO demo_upsert (Url, LastListID) SELECT Url, 10 FROM demo_upsert ON DUPLICATE KEY UPDATE LastListID=10

I get the following error:
SQL Error (1364): Field 'DB_ROW_HASH_1' doesn't have a default value

Attachments

Issue Links

is duplicated by

MDEV-26846 SQL Error: Field 'DB_ROW_HASH_1' doesn't have a default value

Closed

relates to

MDEV-371 Unique indexes for blobs

Closed

MDEV-31072 InnoDB is USING HASH and Optimizer is confused

Open

Activity

Ascending order - Click to sort in descending order

Serge Rogatch created issue - 2020-05-30 07:53

Serge Rogatch made changes - 2020-05-30 07:54

Field	Original Value	New Value
Description	For the following table: {{CREATE TABLE `demo_upsert` ( `ID` BIGINT(20) NOT NULL AUTO_INCREMENT, `Url` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci', `LastListID` BIGINT(20) NULL DEFAULT NULL, `Statistics` BIGINT(20) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) USING BTREE, UNIQUE INDEX `Url` (`Url`) USING HASH ) COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB ;}} When I run the following query: {{INSERT INTO demo_upsert (Url, LastListID) SELECT Url, 10 FROM demo_upsert ON DUPLICATE KEY UPDATE LastListID=10}} I get the following error: {{SQL Error (1364): Field 'DB_ROW_HASH_1' doesn't have a default value}}	For the following table: CREATE TABLE `demo_upsert` ( `ID` BIGINT(20) NOT NULL AUTO_INCREMENT, `Url` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci', `LastListID` BIGINT(20) NULL DEFAULT NULL, `Statistics` BIGINT(20) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) USING BTREE, UNIQUE INDEX `Url` (`Url`) USING HASH ) COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB ; When I run the following query: INSERT INTO demo_upsert (Url, LastListID) SELECT Url, 10 FROM demo_upsert ON DUPLICATE KEY UPDATE LastListID=10 I get the following error: SQL Error (1364): Field 'DB_ROW_HASH_1' doesn't have a default value

Sergei Golubchik made changes - 2020-06-02 20:49

Description

For the following table:

CREATE TABLE `demo_upsert` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `Url` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `LastListID` BIGINT(20) NULL DEFAULT NULL,
    `Statistics` BIGINT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (`ID`) USING BTREE,
    UNIQUE INDEX `Url` (`Url`) USING HASH
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

When I run the following query:

INSERT INTO demo_upsert (Url, LastListID) SELECT Url, 10 FROM demo_upsert ON DUPLICATE KEY UPDATE LastListID=10

I get the following error:
SQL Error (1364): Field 'DB_ROW_HASH_1' doesn't have a default value

For the following table:
{code:sql}
CREATE TABLE `demo_upsert` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `Url` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `LastListID` BIGINT(20) NULL DEFAULT NULL,
    `Statistics` BIGINT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (`ID`) USING BTREE,
    UNIQUE INDEX `Url` (`Url`) USING HASH
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;
{code}
When I run the following query:
{code:sql}
INSERT INTO demo_upsert (Url, LastListID) SELECT Url, 10 FROM demo_upsert ON DUPLICATE KEY UPDATE LastListID=10
{code}
I get the following error:
SQL Error (1364): Field 'DB_ROW_HASH_1' doesn't have a default value

Alice Sherepa made changes - 2020-06-03 08:09

Link

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

Alice Sherepa made changes - 2020-06-03 08:31

Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]

Alice Sherepa made changes - 2020-06-03 08:32

Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]

Alice Sherepa added a comment - 2020-06-03 08:35

Thanks for the report! I repeated on 10.4-10.5

CREATE TABLE t1 (url TEXT NOT NULL, UNIQUE INDEX url (url) USING hash);

INSERT INTO t1 (url) SELECT url FROM t1;

mysqltest: At line 2: query 'INSERT INTO t1 (url) SELECT url FROM t1' failed: 1364: Field 'DB_ROW_HASH_1' doesn't have a default value

Alice Sherepa added a comment - 2020-06-03 08:35 Thanks for the report! I repeated on 10.4-10.5 CREATE TABLE t1 (url TEXT NOT NULL, UNIQUE INDEX url (url) USING hash); INSERT INTO t1 (url) SELECT url FROM t1; mysqltest: At line 2: query 'INSERT INTO t1 (url) SELECT url FROM t1' failed: 1364: Field 'DB_ROW_HASH_1' doesn't have a default value

Alice Sherepa made changes - 2020-06-03 08:35

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2020-06-03 08:35

Assignee

Sachin Setiya [ sachin.setiya.007 ]

Alice Sherepa made changes - 2021-10-18 10:31

Affects Version/s

10.6 [ 24028 ]

Alice Sherepa made changes - 2021-10-18 10:31

Fix Version/s

10.6 [ 24028 ]

Alice Sherepa made changes - 2021-10-18 10:32

Link

This issue is duplicated by ~~MDEV-26846~~ [ ~~MDEV-26846~~ ]

Alice Sherepa made changes - 2021-10-18 10:35

Assignee

Sachin Setiya [ sachin.setiya.007 ]

Oleksandr Byelkin [ sanja ]

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

Workflow

MariaDB v3 [ 109327 ]

MariaDB v4 [ 144258 ]

Bastian Waidelich added a comment - 2023-04-05 12:20

I'm puzzled that this bug still exists after such a long time, but for anyone that comes across the same issue:
A potential work around is to add the nullable column(s) to the unique index:

// ...

UNIQUE INDEX `Url` (`Url`, `LastListID`) USING HASH

Bastian Waidelich added a comment - 2023-04-05 12:20 I'm puzzled that this bug still exists after such a long time, but for anyone that comes across the same issue: A potential work around is to add the nullable column(s) to the unique index: // ... UNIQUE INDEX `Url` (`Url`, `LastListID`) USING HASH

Oli Sennhauser made changes - 2023-04-18 08:24

Link

This issue relates to MDEV-31072 [ MDEV-31072 ]

James Kevin O'Halloran added a comment - 2023-04-28 01:30

I just encountered this bug on 4/27/23 on @@version 10.4.27-MariaDB-1:10.4.27+maria~ubu2004.

I really don't want to add nullable columns to the unique index! ugh.

James Kevin O'Halloran added a comment - 2023-04-28 01:30 I just encountered this bug on 4/27/23 on @@version 10.4.27-MariaDB-1:10.4.27+maria~ubu2004. I really don't want to add nullable columns to the unique index! ugh.

Sergei Golubchik made changes - 2023-04-28 08:54

Assignee

Oleksandr Byelkin [ sanja ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2023-04-28 08:54

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2023-04-28 09:03

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2023-04-28 10:07

Status

Stalled [ 10000 ]

In Testing [ 10301 ]

Sergei Golubchik made changes - 2023-04-28 12:33

Component/s		Data Definition - Create Table [ 14503 ]
Fix Version/s		10.4.29 [ 28510 ]
Fix Version/s		10.5.20 [ 28512 ]
Fix Version/s		10.6.13 [ 28514 ]
Fix Version/s		10.9.6 [ 28520 ]
Fix Version/s		10.10.4 [ 28522 ]
Fix Version/s		10.11.3 [ 28524 ]
Fix Version/s		10.8.8 [ 28518 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Resolution		Fixed [ 1 ]
Status	In Testing [ 10301 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2023-04-28 12:33

Component/s		Data Manipulation - Insert [ 10101 ]
Component/s	Data Definition - Create Table [ 14503 ]

James Kevin O'Halloran added a comment - 2023-04-28 18:37

Sergei, I see you closed this bug after adding an update entitled exclude generated columns from the "has default value" check.

Just in case it is useful, I encountered this bug on a table with no generated columns.

James Kevin O'Halloran added a comment - 2023-04-28 18:37 Sergei, I see you closed this bug after adding an update entitled exclude generated columns from the "has default value" check . Just in case it is useful, I encountered this bug on a table with no generated columns.

Sergei Golubchik added a comment - 2023-04-28 18:57

DB_ROW_HASH_1 is the name of the hidden generated column which is internally created to implement the unique constraint. You can see that the test case in the commit does not use any generated columns either.

Sergei Golubchik added a comment - 2023-04-28 18:57 DB_ROW_HASH_1 is the name of the hidden generated column which is internally created to implement the unique constraint. You can see that the test case in the commit does not use any generated columns either.

James Kevin O'Halloran added a comment - 2023-04-28 21:46

ah thank you for explaining that! cheers.

James Kevin O'Halloran added a comment - 2023-04-28 21:46 ah thank you for explaining that! cheers.

People

Assignee:: Sergei Golubchik

Reporter:: Serge Rogatch

Votes:: 1 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2020-05-30 07:53

Updated:: 2023-04-28 21:46

Resolved:: 2023-04-28 12:33

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