MySQL8 causes dumps to omit defaulted columns using CURRENT_TIMESTAMP

[roccato]

Say I had a table
CREATE TABLE `test` ( `id` int NOT NULL AUTO_INCREMENT, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`);

The code mydumper uses to detect generated columns picks up created_at and updated_at because the information_schema.COLUMNS extra column for them contains DEFAULT_GENERATED and it searches for values like %%GENERATED%%

I think it is reasonable to say that the expectation when dumping and and loading is to insert the original defaulted values. Not create new ones.
To that end I suggest changing detect_generated_fields in https://github.com/maxbube/mydumper/blob/master/mydumper.c where clause to include extra not like '%%DEFAULT_GENERATED%%'

[fredricj]

Duplicate of #221

[fcolope]

I have made rpm package for Centos 7 with this patch, and mydumper now works making backups of tables with problematic timestamp.

The problem is loading the backup. Myloader does not create table from mydumper backups, showing a critical error:

** (myloader:49352): CRITICAL **: 13:58:11.687: Error restoring pruebas.oauth_consumer from file productiondb.oauth_consumer-schema.sql.gz: Invalid default value for 'updated_at'

I think myloader.c needs equivalent patch.

[davidducos]

@fcolope , I tested with @roccato table:

mysql> show create table test.test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

executed export and import like this:

root@ps8:~/mydumper# date; /root/git/mydumper/mydumper -B test -T test; date
Mon Jan 11 13:50:37 UTC 2021
Mon Jan 11 13:50:37 UTC 2021
root@ps8:~/mydumper# date; /root/git/mydumper/myloader -d export-20210111-135037/ -B test2 -o ; date
Mon Jan 11 13:51:07 UTC 2021
Mon Jan 11 13:51:07 UTC 2021

The table was created correctly on test2:

mysql> show create table test2.test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

with this information:

mysql> select table_schema,table_name,COLUMN_NAME,extra from information_schema.COLUMNS where table_name='test';
+--------------+------------+-------------+-----------------------------------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | EXTRA                                         |
+--------------+------------+-------------+-----------------------------------------------+
| test         | test       | created_at  | DEFAULT_GENERATED                             |
| test         | test       | id          | auto_increment                                |
| test         | test       | updated_at  | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| test2        | test       | created_at  | DEFAULT_GENERATED                             |
| test2        | test       | id          | auto_increment                                |
| test2        | test       | updated_at  | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+------------+-------------+-----------------------------------------------+
6 rows in set (0.00 sec)

I run this test over: Percona Server 8.0.19-10.
and this is the create table in the file:
root@ps8:~/mydumper# cat export-20210111-135037/test.test-schema.sql

/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;

/*!40103 SET TIME_ZONE='+00:00' */;
CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Can you give me more info about your issue?

[fcolope]

Hi @davidducos ,

After some tests, by loading data dumped by mydumper with this patch, the problem is not caused by myloader.

The problem was the sql_mode in mysql 8.0.

In Magento 2 , some tables has default values as 0.

# mysql -Bse "use stagingdb; show create table oauth_consumer\G;" 
*************************** 1. row ***************************
       Table: oauth_consumer
Create Table: CREATE TABLE `oauth_consumer` (
  `entity_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created At',
  `updated_at` timestamp NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated At',
  `name` varchar(255) NOT NULL COMMENT 'Name of consumer',
  `key` varchar(32) NOT NULL COMMENT 'Key code',
  `secret` varchar(32) NOT NULL COMMENT 'Secret code',
  `callback_url` text COMMENT 'Callback URL',
  `rejected_callback_url` text NOT NULL COMMENT 'Rejected callback URL',
  PRIMARY KEY (`entity_id`),
  UNIQUE KEY `OAUTH_CONSUMER_KEY` (`key`),
  UNIQUE KEY `OAUTH_CONSUMER_SECRET` (`secret`),
  KEY `OAUTH_CONSUMER_CREATED_AT` (`created_at`),
  KEY `OAUTH_CONSUMER_UPDATED_AT` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='OAuth Consumers'

And MySQL 8.0 , by default, in sql_mode has NO_ZERO_DATE and NO_ZERO_IN_DATE.
Zero value is not allowed in timestamps when this sql_mode options are enabled, and makes mysql to reject tables being imported by myloader.

We have change MySQL sql_mode to allow this definition, because Magento 2 uses sql_mode="" setting inside php code.

It's not a problem of myloader. Sorry.

Best regards.

[davidducos]

Ok, closing it then.