[MDEV-28253] Mysqldump - INVISIBLE column error Created: 2022-04-07  Updated: 2022-04-08  Resolved: 2022-04-07

Status: Closed
Project: MariaDB Server
Component/s: Information Schema
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4

Type: Bug Priority: Major
Reporter: Andrea Ponzo (Inactive) Assignee: Sergei Golubchik
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-10177 Invisible columns Closed

 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



 Comments   
Comment by Anel Husakovic [ 2022-04-07 ]

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)
 
 

Comment by Andrea Ponzo (Inactive) [ 2022-04-08 ]

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

Generated at Thu Feb 08 09:59:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.