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

REPLACE INTO creates 0 value on autoincrement column

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5.10, 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
    • 10.5, 10.6
    • None
    • ubuntu docker

    Description

      Is this a bug or a feature?

      M:1513109 [tmp]>CREATE TABLE `counter_test` (
          ->   `id_counter` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
          ->   `tmstmp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
          ->   `id_ex` bigint(20) NOT NULL,
          ->   `lg` varchar(2) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
          ->   `title_counter` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
          ->   `content_counter` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
          ->   PRIMARY KEY (`id_counter`),
          ->   UNIQUE KEY `id_ex_lg_title_counter` (`id_ex`,`lg`,`title_counter`) USING HASH,
          ->   KEY `title_counter` (`title_counter`(255)),
          ->   KEY `id_ex` (`id_ex`)
          -> ) ENGINE=MyISAM AUTO_INCREMENT=18110 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PACK_KEYS=1 COMMENT='test counter';
      Query OK, 0 rows affected (1.303 sec)
       
      M:1513109 [tmp]>SELECT * FROM tmp.`counter_test`  WHERE  id_ex = '197';
      Empty set (0.001 sec)
       
      M:1513109 [tmp]>REPLACE INTO tmp.counter_test (id_counter,id_ex, lg, title_counter, content_counter) VALUES (null,'197', 'de', 'logfile', '_get_content_body');
      Query OK, 1 row affected (0.000 sec)
       
      M:1513109 [tmp]>SELECT * FROM tmp.`counter_test`  WHERE  id_ex = '197';
      +------------+---------------------+-------+----+---------------+-------------------+
      | id_counter | tmstmp              | id_ex | lg | title_counter | content_counter   |
      +------------+---------------------+-------+----+---------------+-------------------+
      |      18110 | 2021-12-27 13:51:23 |   197 | de | logfile       | _get_content_body |
      +------------+---------------------+-------+----+---------------+-------------------+
      1 row in set (0.000 sec)
       
      M:1513109 [tmp]>REPLACE INTO tmp.counter_test (id_counter,id_ex, lg, title_counter, content_counter) VALUES (null,'197', 'de', 'logfile', '_get_content_body');
      Query OK, 2 rows affected (0.000 sec)
       
      M:1513109 [tmp]>SELECT * FROM tmp.`counter_test`  WHERE  id_ex = '197';
      +------------+---------------------+-------+----+---------------+-------------------+
      | id_counter | tmstmp              | id_ex | lg | title_counter | content_counter   |
      +------------+---------------------+-------+----+---------------+-------------------+
      |          0 | 2021-12-27 13:51:35 |   197 | de | logfile       | _get_content_body |
      +------------+---------------------+-------+----+---------------+-------------------+
      1 row in set (0.000 sec)
       
      M:1513109 [tmp]>select version();
      +-------------------------------------------+
      | version()                                 |
      +-------------------------------------------+
      | 10.5.10-MariaDB-1:10.5.10+maria~focal-log |
      +-------------------------------------------+
      1 row in set (0.059 sec)
      
      

      The same with using null for id_counter.

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              kklepper Karl Klepper
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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