[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 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 ]

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

Link

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

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 ]

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