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

Mysqldump - INVISIBLE column error

Details

    Description

      Hello,
      when a table contains a INVISIBLE column having
      DEFAULT current_timestamp() ON UPDATE current_timestamp()
      the mysqldump is not reporting the complete insert as it does having only
      DEFAULT current_timestamp().

      here the use case:

       
      		 
      CREATE TABLE `table1` (
      		 
        `id` int(11) NOT NULL,
      		 
        `name` varchar(20) DEFAULT NULL,
      		 
        `lastedatet` datetime NOT NULL INVISIBLE DEFAULT current_timestamp()
      		 
      ) ENGINE=InnoDB;
      		 
       
      		 
      insert into table1 (id,name) values (1,'andre');
      		 
       
      		 
      CREATE TABLE `table2` (
      		 
        `id` int(11) NOT NULL,
      		 
        `name` varchar(20) DEFAULT NULL,
      		 
        `lastedatet` datetime NOT NULL INVISIBLE DEFAULT current_timestamp() ON UPDATE current_timestamp()
      		 
      ) ENGINE=InnoDB;
      		 
       
      		 
      insert into table2 (id,name) values (1,'andre');
      		 
       
      		 
       
      		 
      [root@m1 ~]# mysqldump andre table1 | grep INSERT
      		 
      INSERT INTO `table1` (`id`, `name`, `lastedatet`) VALUES (1,'andre','2022-04-07 10:33:01');
      		 
       
      		 
      [root@m1 ~]# mysqldump andre table2 | grep INSERT
      		 
      INSERT INTO `table2` VALUES (1,'andre','2022-04-07 10:33:36');

      The dump on table2 is not reporting the complete insert and when
      importing this table is giving error:

      INSERT INTO `table2` VALUES (1,'andre','2022-04-07 10:33:36');
      		 
      ERROR 1136 (21S01): Column count doesn't match value count at row 1

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-10177 Invisible columns

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

          Activity

            Ascending order - Click to sort in descending order
            anel Anel Husakovic added a comment -

            The problem here is not with mysqldump but rather with show fields command that doesn't take into account INVISIBLE key word, so mysqldump that uses show fields doesn't know that INVISIBLE exists in returned row result.

            MariaDB [test]> show fields from table2;
            		 
            +------------+-------------+------+-----+---------------------+--------------------------------+
            		 
            | Field      | Type        | Null | Key | Default             | Extra                          |
            		 
            +------------+-------------+------+-----+---------------------+--------------------------------+
            		 
            | id         | int(11)     | NO   |     | NULL                |                                |
            		 
            | name       | varchar(20) | YES  |     | NULL                |                                |
            		 
            | lastedatet | datetime    | NO   |     | current_timestamp() | on update current_timestamp(), |
            		 
            +------------+-------------+------+-----+---------------------+--------------------------------+
            		 
            3 rows in set (0.001 sec)
            		 
             
            		 
            MariaDB [test]> show fields from table1;
            		 
            +------------+-------------+------+-----+---------------------+-----------+
            		 
            | Field      | Type        | Null | Key | Default             | Extra     |
            		 
            +------------+-------------+------+-----+---------------------+-----------+
            		 
            | id         | int(11)     | NO   |     | NULL                |           |
            		 
            | name       | varchar(20) | YES  |     | NULL                |           |
            		 
            | lastedatet | datetime    | NO   |     | current_timestamp() | INVISIBLE |
            		 
            +------------+-------------+------+-----+---------------------+-----------+
            		 
            3 rows in set (0.001 sec)
            		 
             

            anel Anel Husakovic added a comment - The problem here is not with mysqldump but rather with show fields command that doesn't take into account INVISIBLE key word, so mysqldump that uses show fields doesn't know that INVISIBLE exists in returned row result. MariaDB [test]> show fields from table2; +------------+-------------+------+-----+---------------------+--------------------------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------------------+--------------------------------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | | lastedatet | datetime | NO | | current_timestamp() | on update current_timestamp(), | +------------+-------------+------+-----+---------------------+--------------------------------+ 3 rows in set (0.001 sec)   MariaDB [test]> show fields from table1; +------------+-------------+------+-----+---------------------+-----------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------------------+-----------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | | lastedatet | datetime | NO | | current_timestamp() | INVISIBLE | +------------+-------------+------+-----+---------------------+-----------+ 3 rows in set (0.001 sec)  
            aponzo Andrea Ponzo (Inactive) added a comment -

            anel thanks.
            I didn't know how mysqldump took this info.
            so VARCHAR(30) is too small for the EXTRA column

            aponzo Andrea Ponzo (Inactive) added a comment - anel thanks. I didn't know how mysqldump took this info. so VARCHAR(30) is too small for the EXTRA column

            People

              serg Sergei Golubchik
              aponzo Andrea Ponzo (Inactive)
              Votes:
              1 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.