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

Pseudo empty string in unique ENUM breaks dump/restore

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
    • 10.4, 10.5, 10.6
    • Data types

    Description

      INSERT IGNORE can insert a value into ENUM which for most practical purposes is treated as an empty string.
      It can further mysqldump / restore, because it is written in a dump or outfile as a regular empty string, and upon further INSERT causes a duplicate key error for a unique field.

      CREATE TABLE t (f enum('','a','b') NOT NULL PRIMARY KEY, i INT);
      INSERT IGNORE INTO t VALUES (NULL,1),('',2);
      SELECT i, f, f = '' FROM t;
      SELECT * FROM t WHERE f = '';
      SELECT * FROM t WHERE f = 0;
      SELECT * FROM t WHERE f = 1;
       
      SELECT * INTO OUTFILE 't.data' FROM t;
      TRUNCATE TABLE t;
      LOAD DATA INFILE 't.data' INTO TABLE t;
      SELECT * FROM t;
       
      # Cleanup
      DROP TABLE t;
      --let $datadir= `select @@datadir`
      --remove_file $datadir/test/t.data
      

      10.3 7d96cb470

      MariaDB [test]> INSERT IGNORE INTO t VALUES (NULL,1),('',2);
      Query OK, 2 rows affected, 1 warning (0.008 sec)
      Records: 2  Duplicates: 0  Warnings: 1
       
      MariaDB [test]> SELECT i, f, f = '' FROM t;
      +------+---+--------+
      | i    | f | f = '' |
      +------+---+--------+
      |    1 |   |      1 |
      |    2 |   |      1 |
      +------+---+--------+
      2 rows in set (0.014 sec)
      

      So, the row with NULL is actually inserted, and the value in the ENUM column is said to be equal to ''. But not quite:

      MariaDB [test]> SELECT * FROM t WHERE f = '';
      +---+------+
      | f | i    |
      +---+------+
      |   |    2 |
      +---+------+
       
      MariaDB [test]> SELECT * FROM t WHERE f = 0;
      +---+------+
      | f | i    |
      +---+------+
      |   |    1 |
      +---+------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> SELECT * FROM t WHERE f = 1;
      +---+------+
      | f | i    |
      +---+------+
      |   |    2 |
      +---+------+
      

      However, upon selecting, these values become indistinguishable, so an outfile would contain two rows with an empty string in the f position, and INSERT in the dump will also try to insert two regular empty strings. Naturally both fail, already regardless strict mode or IGNORE, because they violate the unique key.
      Same happens with any invalid value, not just NULL.

      MariaDB [test]> SELECT * INTO OUTFILE 't.data' FROM t;
      Query OK, 2 rows affected (0.001 sec)
       
      MariaDB [test]> TRUNCATE TABLE t;
      Query OK, 0 rows affected (0.079 sec)
       
      MariaDB [test]> LOAD DATA INFILE 't.data' INTO TABLE t;
      ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'
      

      In mysqldump:

      CREATE TABLE `t` (
        `f` enum('','a','b') NOT NULL,
        `i` int(11) DEFAULT NULL,
        PRIMARY KEY (`f`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
      /*!40101 SET character_set_client = @saved_cs_client */;
       
      --
      -- Dumping data for table `t`
      --
       
      LOCK TABLES `t` WRITE;
      /*!40000 ALTER TABLE `t` DISABLE KEYS */;
      INSERT INTO `t` VALUES ('',1),('',2);
      /*!40000 ALTER TABLE `t` ENABLE KEYS */;
      UNLOCK TABLES;
      

      Thus the whole dump becomes unloadable.

      Reproducible on all MariaDB and on MySQL (tried 8.0).

      Attachments

        Activity

          People

            bar Alexander Barkov
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.