[MDEV-20001] Potential dangerous regression: INSERT INTO >=100 rows fail for myisam table with HASH indexes - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.4.6, 10.4(EOL)
Fix Version/s: 10.4.13
Component/s: Data Manipulation - Insert
Labels:
None
Environment:
Fedora Core 30 ( 5.1.15-300.fc30.x86_64 )
E5-2660 v3 @ 2.60GHz

Description

Hi everyone, it seems there's a very "weird" and dangerous regression in mysql 10.4.6...

So basically for it to work you need myisam table with UNIQUE KEY ... USING HASH. I was reading in docs it doesn't make a difference, but when used in newest maria it'll make the table behave very strangely, so you can NOT do INSERT INTO with > 100 rows at once.

DROP TABLE IF EXISTS `_hits`;

CREATE TABLE `_hits` (

  `creative_id` int(11) NOT NULL,

  `ip` varchar(15) NOT NULL,

  `date` date NOT NULL,

  UNIQUE KEY (`creative_id`,`date`,`ip`) USING HASH

) ENGINE=myisam DEFAULT CHARSET=utf8;

INSERT INTO `_hits` VALUES

(0, '0.1.1.0', '2018-01-01'),(1, '1.1.1.1', '2018-01-01'),(2, '2.1.1.2', '2018-01-01'),(3, '3.1.1.3', '2018-01-01'),(4, '4.1.1.4', '2018-01-01'),(5, '5.1.1.5', '2018-01-01'),(6, '6.1.1.6', '2018-01-01'),(7, '7.1.1.7', '2018-01-01'),(8, '8.1.1.8', '2018-01-01'),(9, '9.1.1.9', '2018-01-01'),

(10, '10.1.1.10', '2018-01-01'),(11, '11.1.1.11', '2018-01-01'),(12, '12.1.1.12', '2018-01-01'),(13, '13.1.1.13', '2018-01-01'),(14, '14.1.1.14', '2018-01-01'),(15, '15.1.1.15', '2018-01-01'),(16, '16.1.1.16', '2018-01-01'),(17, '17.1.1.17', '2018-01-01'),(18, '18.1.1.18', '2018-01-01'),(19, '19.1.1.19', '2018-01-01'),

(20, '20.1.1.20', '2018-01-01'),(21, '21.1.1.21', '2018-01-01'),(22, '22.1.1.22', '2018-01-01'),(23, '23.1.1.23', '2018-01-01'),(24, '24.1.1.24', '2018-01-01'),(25, '25.1.1.25', '2018-01-01'),(26, '26.1.1.26', '2018-01-01'),(27, '27.1.1.27', '2018-01-01'),(28, '28.1.1.28', '2018-01-01'),(29, '29.1.1.29', '2018-01-01'),

(30, '30.1.1.30', '2018-01-01'),(31, '31.1.1.31', '2018-01-01'),(32, '32.1.1.32', '2018-01-01'),(33, '33.1.1.33', '2018-01-01'),(34, '34.1.1.34', '2018-01-01'),(35, '35.1.1.35', '2018-01-01'),(36, '36.1.1.36', '2018-01-01'),(37, '37.1.1.37', '2018-01-01'),(38, '38.1.1.38', '2018-01-01'),(39, '39.1.1.39', '2018-01-01'),

(40, '40.1.1.40', '2018-01-01'),(41, '41.1.1.41', '2018-01-01'),(42, '42.1.1.42', '2018-01-01'),(43, '43.1.1.43', '2018-01-01'),(44, '44.1.1.44', '2018-01-01'),(45, '45.1.1.45', '2018-01-01'),(46, '46.1.1.46', '2018-01-01'),(47, '47.1.1.47', '2018-01-01'),(48, '48.1.1.48', '2018-01-01'),(49, '49.1.1.49', '2018-01-01'),

(50, '50.1.1.50', '2018-01-01'),(51, '51.1.1.51', '2018-01-01'),(52, '52.1.1.52', '2018-01-01'),(53, '53.1.1.53', '2018-01-01'),(54, '54.1.1.54', '2018-01-01'),(55, '55.1.1.55', '2018-01-01'),(56, '56.1.1.56', '2018-01-01'),(57, '57.1.1.57', '2018-01-01'),(58, '58.1.1.58', '2018-01-01'),(59, '59.1.1.59', '2018-01-01'),

(60, '60.1.1.60', '2018-01-01'),(61, '61.1.1.61', '2018-01-01'),(62, '62.1.1.62', '2018-01-01'),(63, '63.1.1.63', '2018-01-01'),(64, '64.1.1.64', '2018-01-01'),(65, '65.1.1.65', '2018-01-01'),(66, '66.1.1.66', '2018-01-01'),(67, '67.1.1.67', '2018-01-01'),(68, '68.1.1.68', '2018-01-01'),(69, '69.1.1.69', '2018-01-01'),

(70, '70.1.1.70', '2018-01-01'),(71, '71.1.1.71', '2018-01-01'),(72, '72.1.1.72', '2018-01-01'),(73, '73.1.1.73', '2018-01-01'),(74, '74.1.1.74', '2018-01-01'),(75, '75.1.1.75', '2018-01-01'),(76, '76.1.1.76', '2018-01-01'),(77, '77.1.1.77', '2018-01-01'),(78, '78.1.1.78', '2018-01-01'),(79, '79.1.1.79', '2018-01-01'),

(80, '80.1.1.80', '2018-01-01'),(81, '81.1.1.81', '2018-01-01'),(82, '82.1.1.82', '2018-01-01'),(83, '83.1.1.83', '2018-01-01'),(84, '84.1.1.84', '2018-01-01'),(85, '85.1.1.85', '2018-01-01'),(86, '86.1.1.86', '2018-01-01'),(87, '87.1.1.87', '2018-01-01'),(88, '88.1.1.88', '2018-01-01'),(89, '89.1.1.89', '2018-01-01'),

(90, '90.1.1.90', '2018-01-01'),(91, '91.1.1.91', '2018-01-01'),(92, '92.1.1.92', '2018-01-01'),(93, '93.1.1.93', '2018-01-01'),(94, '94.1.1.94', '2018-01-01'),(95, '95.1.1.95', '2018-01-01'),(96, '96.1.1.96', '2018-01-01'),(97, '97.1.1.97', '2018-01-01'),(98, '98.1.1.98', '2018-01-01'),(99, '99.1.1.99', '2018-01-01')

So this code will produce
[Err] 1032 - Can't find record in '_hits'

To make it work correctly again you can do just one of the following:

Change engine to innodb or memory
Remove ANY dataset from insert so it'll do 99 items correctly
Change key, remove "USING HASH"
Change key, remove "UNIQUE"

When you change the engine to aria it exhibits same behaviour as myisam, but it gives different error:
[Err] 1904 - Key/Index cannot be defined on a virtual generated column
(and works correctly for USING BTREE)

It works totally fine in older versions eg. Maria 10.2.9, 10.1.22 etc. I was trying to find somethig related to this and found some report with same bug related to replication. I don't remember the URL and details, it wasn't resolved... so i think it could also be breaking statement based replication and it's probably hard to diagnose.

Most strange thing is that HASH indexes shouldn't even be supported by myisam so it should work like default b-tree index, so in fact it should make no difference if USING HASH is there or not, right?

Thanks for the help

Attachments

Issue Links

relates to

MDEV-371 Unique indexes for blobs

Closed

MDEV-18791 Wrong error upon creating Aria table with long index on BLOB

Closed

MDEV-25102 UNIQUE USING HASH error after ALTER ... DISABLE KEYS

Closed

Activity

Ascending order - Click to sort in descending order

Slawomir Pryczek created issue - 2019-07-09 14:14

Alice Sherepa made changes - 2019-07-09 14:32

Field	Original Value	New Value
Fix Version/s		10.4 [ 22408 ]

Alice Sherepa made changes - 2019-07-09 14:38

Affects Version/s

10.4 [ 22408 ]

Alice Sherepa made changes - 2019-07-09 14:58

Link

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

Alice Sherepa made changes - 2019-07-09 14:58

Link

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

Alice Sherepa made changes - 2019-07-09 14:59

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa added a comment - 2019-07-09 15:16

Thanks a lot for the report! I repeated as described on MariaDB 10.4:

MariaDB [test]> CREATE TABLE `t2` (

    ->   `creative_id` int(11) NOT NULL,

    ->   `ip` varchar(15) NOT NULL,

    ->   `date` date NOT NULL,

    ->   UNIQUE KEY (`creative_id`,`date`,`ip`) USING HASH

    -> ) ENGINE=myisam;

Query OK, 0 rows affected (0.021 sec)

MariaDB [test]> INSERT INTO `t2` select seq, CONCAT(seq,'1.1',seq), '2018-01-01' from seq_1_to_100;

ERROR 1032 (HY000): Can't find record in 't2'

MariaDB [test]> CREATE TABLE `t3` (

    ->   `creative_id` int(11) NOT NULL,

    ->   `ip` varchar(15) NOT NULL,

    ->   `date` date NOT NULL,

    ->   UNIQUE KEY (`creative_id`,`date`,`ip`) USING HASH

    -> ) ENGINE=aria;

ERROR 1904 (HY000): Key/Index cannot be defined on a virtual generated column

Alice Sherepa added a comment - 2019-07-09 15:16 Thanks a lot for the report! I repeated as described on MariaDB 10.4: MariaDB [test]> CREATE TABLE `t2` ( -> `creative_id` int(11) NOT NULL, -> `ip` varchar(15) NOT NULL, -> `date` date NOT NULL, -> UNIQUE KEY (`creative_id`,`date`,`ip`) USING HASH -> ) ENGINE=myisam; Query OK, 0 rows affected (0.021 sec) MariaDB [test]> INSERT INTO `t2` select seq, CONCAT(seq,'1.1',seq), '2018-01-01' from seq_1_to_100; ERROR 1032 (HY000): Can't find record in 't2' MariaDB [test]> CREATE TABLE `t3` ( -> `creative_id` int(11) NOT NULL, -> `ip` varchar(15) NOT NULL, -> `date` date NOT NULL, -> UNIQUE KEY (`creative_id`,`date`,`ip`) USING HASH -> ) ENGINE=aria; ERROR 1904 (HY000): Key/Index cannot be defined on a virtual generated column

Alice Sherepa made changes - 2019-07-09 15:16

Assignee

Sachin Setiya [ sachin.setiya.007 ]

Sachin Setiya (Inactive) added a comment - 2019-07-28 18:26 - edited

create table t1(a int, unique(a) using hash);

--let $count=99

--let insert_stmt= insert into t1 values(200)

while ($count)

  --let $insert_stmt=$insert_stmt,($count)

  --dec $count

--eval $insert_stmt

drop table t1;

simpler test , 100 insert passes 99 insert fails , debugging
one by one insert is safe

Sachin Setiya (Inactive) added a comment - 2019-07-28 18:26 - edited create table t1(a int, unique(a) using hash); --let $count=99 --let insert_stmt= insert into t1 values(200) while ($count) { --let $insert_stmt=$insert_stmt,($count) --dec $count } --eval $insert_stmt drop table t1; simpler test , 100 insert passes 99 insert fails , debugging one by one insert is safe

Sachin Setiya (Inactive) added a comment - 2019-07-29 03:39

It fails on first insert itself, Even this also fails

create table t1(a int, unique(a) using hash);

--let $count=99

--let insert_stmt= insert into t1 values(1)

while ($count)

  --let $insert_stmt=$insert_stmt,(1)

  --dec $count

--eval $insert_stmt

drop table t1;

Sachin Setiya (Inactive) added a comment - 2019-07-29 03:39 It fails on first insert itself, Even this also fails create table t1(a int, unique(a) using hash); --let $count=99 --let insert_stmt= insert into t1 values(1) while ($count) { --let $insert_stmt=$insert_stmt,(1) --dec $count } --eval $insert_stmt drop table t1;

Sachin Setiya (Inactive) added a comment - 2019-07-29 07:03

I think something is going on in myisam

Case when insert is less then 100

#0  handler::ha_write_row (this=0x7fffe01524c0, buf=0x7fffe00124a0 "\371\001") at /home/sachin/10.4/server/sql/handler.cc:6664

6664      Log_func *log_func= Write_rows_log_event::binlog_row_logging_function;

(gdb) p table->file->file->s->state->key_map

$3 = 1

Case when insert is >= 100

Thread 6 "mysqld" hit Breakpoint 1, handler::ha_write_row (this=0x7fffe008c300, buf=0x7fffe01a0890 "\371\001") at /home/sachin/10.4/server/sql/handler.cc:6664

6664      Log_func *log_func= Write_rows_log_event::binlog_row_logging_function;

(gdb)  p table->file->file->s->state->key_map

$1 = 0

Sachin Setiya (Inactive) added a comment - 2019-07-29 07:03 I think something is going on in myisam Case when insert is less then 100 #0 handler::ha_write_row (this=0x7fffe01524c0, buf=0x7fffe00124a0 "\371\001") at /home/sachin/10.4/server/sql/handler.cc:6664 6664 Log_func *log_func= Write_rows_log_event::binlog_row_logging_function; (gdb) p table->file->file->s->state->key_map $3 = 1 Case when insert is >= 100 Thread 6 "mysqld" hit Breakpoint 1, handler::ha_write_row (this=0x7fffe008c300, buf=0x7fffe01a0890 "\371\001") at /home/sachin/10.4/server/sql/handler.cc:6664 6664 Log_func *log_func= Write_rows_log_event::binlog_row_logging_function; (gdb) p table->file->file->s->state->key_map $1 = 0

Sachin Setiya (Inactive) added a comment - 2019-07-29 07:54

It does not work in earlier version , before 10.4 we did not had hash index for innodb and myisam
So hash index was converted to btree, without user knowledge

MariaDB [test]> create table t1(a int, unique(a)using hash);

Query OK, 0 rows affected (0.003 sec)

MariaDB [test]> show create table t1;

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

| Table | Create Table                                                                                                             |

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

| t1    | CREATE TABLE `t1` (

  `a` int(11) DEFAULT NULL,

  UNIQUE KEY `a` (`a`) USING HASH

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

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

1 row in set (0.001 sec)

MariaDB [test]> show keys from t1;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| t1    |          0 | a        |            1 | a           | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |

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

1 row in set (0.001 sec)

Sachin Setiya (Inactive) added a comment - 2019-07-29 07:54 It does not work in earlier version , before 10.4 we did not had hash index for innodb and myisam So hash index was converted to btree, without user knowledge MariaDB [test]> create table t1(a int, unique(a)using hash); Query OK, 0 rows affected (0.003 sec) MariaDB [test]> show create table t1; +-------+--------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, UNIQUE KEY `a` (`a`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-------+--------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec) MariaDB [test]> show keys from t1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t1 | 0 | a | 1 | a | A | NULL | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.001 sec)

Sachin Setiya (Inactive) added a comment - 2019-07-29 09:58

Reason for this is , myisam disables the index in bulk insert (>= 100)

  if (file->state->records == 0 && can_enable_indexes &&

      (!rows || rows >= MI_MIN_ROWS_TO_DISABLE_INDEXES))

    if (file->open_flag & HA_OPEN_INTERNAL_TABLE)

      file->update|= HA_STATE_CHANGED;

      mi_clear_all_keys_active(file->s->state.key_map);

    else

      my_bool all_keys= MY_TEST(flags & HA_CREATE_UNIQUE_INDEX_BY_SORT);

      mi_disable_indexes_for_rebuild(file, rows, all_keys);

#define MI_MIN_ROWS_TO_DISABLE_INDEXES 100

Sachin Setiya (Inactive) added a comment - 2019-07-29 09:58 Reason for this is , myisam disables the index in bulk insert (>= 100) if (file->state->records == 0 && can_enable_indexes && (!rows || rows >= MI_MIN_ROWS_TO_DISABLE_INDEXES)) { if (file->open_flag & HA_OPEN_INTERNAL_TABLE) { file->update|= HA_STATE_CHANGED; mi_clear_all_keys_active(file->s->state.key_map); } else { my_bool all_keys= MY_TEST(flags & HA_CREATE_UNIQUE_INDEX_BY_SORT); mi_disable_indexes_for_rebuild(file, rows, all_keys); } } #define MI_MIN_ROWS_TO_DISABLE_INDEXES 100

Sachin Setiya (Inactive) added a comment - 2019-07-29 15:11

patch http://lists.askmonty.org/pipermail/commits/2019-July/013913.html

Sachin Setiya (Inactive) added a comment - 2019-07-29 15:11 patch http://lists.askmonty.org/pipermail/commits/2019-July/013913.html

Sachin Setiya (Inactive) made changes - 2019-07-29 15:11

Assignee	Sachin Setiya [ sachin.setiya.007 ]	Michael Widenius [ monty ]
Status	Confirmed [ 10101 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2019-08-07 10:22

Assignee

Michael Widenius [ monty ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2019-08-20 12:38

Assignee	Sergei Golubchik [ serg ]	Sachin Setiya [ sachin.setiya.007 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sachin Setiya (Inactive) made changes - 2019-10-09 16:04

Assignee	Sachin Setiya [ sachin.setiya.007 ]	Sergei Golubchik [ serg ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2019-10-25 15:47

Assignee	Sergei Golubchik [ serg ]	Sachin Setiya [ sachin.setiya.007 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sachin Setiya (Inactive) made changes - 2020-02-03 07:15

Fix Version/s		10.4.13 [ 24223 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Alice Sherepa made changes - 2021-03-12 10:56

Link

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

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

Workflow

MariaDB v3 [ 98106 ]

MariaDB v4 [ 156457 ]

People

Assignee:: Sachin Setiya (Inactive)

Reporter:: Slawomir Pryczek

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2019-07-09 14:14

Updated:: 2021-03-12 10:56

Resolved:: 2020-02-03 07:15

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