Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL)
-
None
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
-
MDEV-10177 Invisible columns
-
- Closed
-
Activity
anel thanks.
I didn't know how mysqldump took this info.
so VARCHAR(30) is too small for the EXTRA column
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)