Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20001

Potential dangerous regression: INSERT INTO >=100 rows fail for myisam table with HASH indexes

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.4.6, 10.4(EOL)
    • 10.4.13
    • Data Manipulation - Insert
    • None
    • 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

          Task - A task that needs to be done. MDEV-371 Unique indexes for blobs

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18791 Wrong error upon creating Aria table with long index on BLOB

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25102 UNIQUE USING HASH error after ALTER ... DISABLE KEYS

          • Major - Major loss of function.
          • Closed

          Activity

            Transition Time In Source Status Execution Times
            Alice Sherepa made transition -
            Open Confirmed
            		44m 28s 1
            Sachin Setiya (Inactive) made transition -
            Confirmed In Review
            		20d 12m 1
            Sachin Setiya (Inactive) made transition -
            Stalled In Review
            		50d 3h 25m 1
            Sergei Golubchik made transition -
            In Review Stalled
            		37d 21h 9m 2
            Sachin Setiya (Inactive) made transition -
            Stalled Closed
            		100d 15h 28m 1

            People

              sachin.setiya.007 Sachin Setiya (Inactive)
              pslawek83 Slawomir Pryczek
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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