MySQL migration apply through atlas doesn't work

[iSerganov]

When running atlas migrate apply against MySQL 5.7.12 server, the following error occurs:

Error: sql/schema: create "atlas_schema_revisions" table: Error 1071 (42000): Specified key was too long; max key length is 767 bytes

Generated migration file:

-- Create "stations" table
CREATE TABLE `stations` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) NOT NULL,
  `updated_at` datetime(3) NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime(3) NULL,
  `station_name` varchar(100) NOT NULL,
  `deleted` bool NOT NULL,
  `updated_by` varchar(64) NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_stations_deleted_at` (`deleted_at`)
) CHARSET utf8mb4;
-- Create "plugs" table
CREATE TABLE `plugs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) NOT NULL,
  `updated_at` datetime(3) NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime(3) NULL,
  `station_id` bigint unsigned NOT NULL,
  `enabled` bool NOT NULL,
  `weight` bigint NULL,
  `updated_by` varchar(64) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_stations_plugs` (`station_id`),
  INDEX `idx_plugs_deleted_at` (`deleted_at`),
  INDEX `idx_plugs_down_since` (`down_since`),
  CONSTRAINT `fk_stations_plugs` FOREIGN KEY (`station_id`) REFERENCES `stations` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
) CHARSET utf8mb4;

[masseelch]

Hey @iSerganov, thanks for raising this. Unfortunately, I was not able to reproduce this. I couldn't apply your given migration with the following error: Error 1294 (HY000): Invalid ON UPDATE clause for 'updated_at' column. This is because CURRENT_TIMESTAMP can only be used on TIMESTAMP fields.

In addition, the key length issue is known from MySQL version 5.6 and before. It was raised to ~3000 bytes with MySQL 5.7 and later. So can you please check again, if you are indeed running version 5.7.12 and not something older?

Also, please note that MySQL 5.7 will reach EOL in the next month. If possible, please consider to update your database to be on the safe side. However, regardless of what you devide, I will be happy to assist with your above issue.

[iSerganov]

Hey @masseelch! Thank you for your fast reply!

Yeah, I am pretty sure that I have 5.7.12 version.

As well I am am sure that I can use CURRENT_TIMESTAMP with both TIMESTAMP and DATETIME fields - see MySQL 5.7 docs

Also, please note that MySQL 5.7 will reach EOL in the next month. - though that is a good remark - we will consider updating. Thank you! However until we do that, it would be very helpful if you could help me to apply migrations to MySQL 5.7 DB using Atlas.

Another point that makes me think that the issue comes from out of my SQL migration script is the error message I get - it states an error while creating atlas_schema_revisions table.

[masseelch]

Hey @iSerganov.

Are you by any chance able to provide a repository, that shows the bug in action? I tried again to reproduce the issue without success.

[iSerganov]

Unfortunately no, I work in the private repository. But I am sure it should be quite easy to reproduce. I have the latest atlas version 0.14 installed on MacOS using brew.

To reproduce you just need to perform the following steps against MySQL 5.7.12 DB:

1. Setup a Golang app.
2. Declare any struct as gorm model.
3. Create initial migration with atlas migrate diff $(MIGRATION_NAME) --env gorm --dev-url <your-db-url> - note it will generate awesome SQL statement that is perfectly working if you run it against the same DB using mysql cli.
4. Do atlas migrate apply

Here is my atlas.hcl file content:

data "external_schema" "gorm" {
  program = [
    "go",
    "run",
    "-mod=mod",
    "ariga.io/atlas-provider-gorm",
    "load",
    "--path", "./pkg/database/model",
    "--dialect", "mysql"
  ]
}

env "gorm" {
  src = data.external_schema.gorm.url
  migration {
    dir = "file://pkg/database/migrations"
  }
  format {
    migrate {
      diff = "{{ sql . \"  \" }}"
    }
  }
}

As the result I always see Error: sql/schema: create "atlas_schema_revisions" table: Error 1071 (42000): Specified key was too long; max key length is 767 bytes

[masseelch]

Hey @iSerganov, I tried once again to reproduce the issue without success. Please find below the steps I took. It all works as expected. You can see the migration is applied and the atlas_schema_revisions table is created.

As long as I am not able to re-create the issue you are facing, I am unable to help :crying_cat_face:. If possible, please try to create an example repository that shows the issue. It does not need to have any of your private code there.

Also, if you want and can, I am willing to sign a NDA to have a look at the real example you are facing. Contact me on Discord privately, if you want to.

See what I did to reproduce!

```shell ↪ tree . . ├── atlas.hcl ├── go.mod ├── gorm.go ├── go.sum └── migrations 2 directories, 4 files ``` ```shell ↪ cat gorm.go package gorm import "gorm.io/gorm" type Product struct { gorm.Model Code string Price uint } ``` ```shell ↪ cat atlas.hcl data "external_schema" "gorm" { program = [ "go", "run", "-mod=mod", "ariga.io/atlas-provider-gorm", "load", "--path", "./", "--dialect", "mysql" ] } env "gorm" { src = data.external_schema.gorm.url format { migrate { diff = "{{ sql . \" \" }}" } } } ``` ```shell ↪ atlas version atlas version v0.14.0 https://github.com/ariga/atlas/releases/tag/v0.14.0 ``` ```shell ↪ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 5de59678c6c5 mysql:5.7.12 "docker-entrypoint.s…" 18 minutes ago Up 18 minutes 0.0.0.0:3311->3306/tcp, :::3311->3306/tcp blissful_mestorf ``` ```shell ↪ atlas migrate diff initial --env gorm --dev-url mysql://root:pass@localhost:3311/dev ``` ```shell ↪ tree migrations/ migrations/ ├── 20230920063044_initial.sql └── atlas.sum 1 directory, 2 files ``` ```shell ↪ atlas migrate apply --url mysql://root:pass@localhost:3311/gorm Migrating to version 20230920063044 (1 migrations in total): -- migrating version 20230920063044 -> CREATE TABLE `products` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `created_at` datetime(3) NULL, `updated_at` datetime(3) NULL, `deleted_at` datetime(3) NULL, `code` longtext NULL, `price` bigint unsigned NULL, PRIMARY KEY (`id`), INDEX `idx_products_deleted_at` (`deleted_at`) ) CHARSET latin1 COLLATE latin1_swedish_ci; -- ok (11.005814ms) ------------------------- -- 17.042085ms -- 1 migrations -- 1 sql statements ``` ```shell ↪ mysql -h 127.0.0.1 -P 3311 -uroot -ppass Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 5.7.12 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> use gorm; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MySQL [gorm]> show tables; +------------------------+ | Tables_in_gorm | +------------------------+ | atlas_schema_revisions | | products | +------------------------+ 2 rows in set (0.001 sec) ```

[iSerganov]

Hey @masseelch ! Thank you very much for your efforts! The data you have provided made me trying the same steps with the local MySQL instance (in docker) against MySQL 8.0.34 and it works like a charm!

Most likely there is an issue particularly with AWS Aurora MySQL 5.7.12 instance. I will do my best to upgrade it this week and let you know if the issue is gone.

Thank you very much for your assistance once again!

[iSerganov]

After updating DB engine to MySQL 8.0 - migration apply works smoothly. Thank you for your assistance gentlemen, closing the issue.