Schema management CLI for MySQL

This change will allow users to ignore certain tables or schemas while using skeema. The main idea behind this is to ignore tables that were created by pt-online-schema-change or gh-ost. In addition it makes sense to allow entire schemas to be ignored besides the system schemas

I recently migrated both my dev and prod RDS databases from MySQL to Aurora MySQL

After the migration, I was able to make schema changes on dev using skeema with no problems, however when I tried running the same changes on prod, I kept getting errors like the following:

2020-03-27 12:07:25 [WARN]  Skipping table `telemedicine_sessions_cancelled`: unable to generate DDL due to use of unsupported features. Use --debug for more information.
2020-03-27 12:07:25 [DEBUG] --- Expected CREATE
2020-03-27 12:07:25 [DEBUG] +++ MySQL-actual SHOW CREATE
2020-03-27 12:07:25 [DEBUG] @@ -9,2 +9,2 @@
2020-03-27 12:07:25 [DEBUG] -  CONSTRAINT `_fk-telemedicine-sessios-cancelled-session` FOREIGN KEY (`session_id`) REFERENCES `telemedicine_sessions` (`id`),
2020-03-27 12:07:25 [DEBUG] -  CONSTRAINT `fk-telemedicine-session-cancelled-clinic` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`)
2020-03-27 12:07:25 [DEBUG] +  CONSTRAINT `fk-telemedicine-session-cancelled-clinic` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`),
2020-03-27 12:07:25 [DEBUG] +  CONSTRAINT `_fk-telemedicine-sessios-cancelled-session` FOREIGN KEY (`session_id`) REFERENCES `telemedicine_sessions` (`id`)
2020-03-27 12:07:25 [WARN]  Skipping table `telemedicine_sessions_rejected`: unable to generate DDL due to use of unsupported features. Use --debug for more information.
2020-03-27 12:07:25 [DEBUG] --- Expected CREATE
2020-03-27 12:07:25 [DEBUG] +++ MySQL-actual SHOW CREATE
2020-03-27 12:07:25 [DEBUG] @@ -8,2 +8,2 @@
2020-03-27 12:07:25 [DEBUG] -  CONSTRAINT `__fk-telemedicine-session-rejected-clinic` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`),
2020-03-27 12:07:25 [DEBUG] -  CONSTRAINT `fk-telemedicine-sessios-rejected-session` FOREIGN KEY (`session_id`) REFERENCES `telemedicine_sessions` (`id`)
2020-03-27 12:07:25 [DEBUG] +  CONSTRAINT `fk-telemedicine-sessios-rejected-session` FOREIGN KEY (`session_id`) REFERENCES `telemedicine_sessions` (`id`),
2020-03-27 12:07:25 [DEBUG] +  CONSTRAINT `__fk-telemedicine-session-rejected-clinic` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`)
2020-03-27 12:07:25 [WARN]  Skipping table `telemedicine_sessions`: unable to generate DDL due to use of unsupported features. Use --debug for more information.
2020-03-27 12:07:25 [DEBUG] --- Expected CREATE
2020-03-27 12:07:25 [DEBUG] +++ MySQL-actual SHOW CREATE
2020-03-27 12:07:25 [DEBUG] @@ -39,2 +39,2 @@
2020-03-27 12:07:25 [DEBUG] -  CONSTRAINT `_fk-telemedicine-session-clinics` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`),
2020-03-27 12:07:25 [DEBUG] -  CONSTRAINT `telemedicine_sessions_ibfk_1` FOREIGN KEY (`practitioner_id`) REFERENCES `users` (`id`)
2020-03-27 12:07:25 [DEBUG] +  CONSTRAINT `telemedicine_sessions_ibfk_1` FOREIGN KEY (`practitioner_id`) REFERENCES `users` (`id`),
2020-03-27 12:07:25 [DEBUG] +  CONSTRAINT `_fk-telemedicine-session-clinics` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`)

I tried changing the order of the foreign keys in my CREATE TABLE statements to match what it wants, but that doesn't seem to change anything, I still get the same errors.

The only thing that allowed me to update any of the affected tables was deleting its foreign keys entirely, but it gives me the same errors when I try to add them back in.

A new flag is introduced, --ddl-wrapper-comment (bool, default false for backward compatibility), which controls output of skeema diff (or skeema push --dry-run) command.

When this flag is given, each DDL is wrapped by well formed comments, in the likeness of:

-- skeema:ddl:begin
CREATE TABLE `table_0` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- skeema:ddl:end

The purpose of this flag is to assist with parsing of skeema diff output. As a concrete example, we want to be able to run skeema diff and review the statements generated by the tool, on a per-statement basis. When diff contains multiple DDL statements (multiple CREATE, ALTER, DROP) it is a difficult task to parse it. Toekizing by semicolon (;) is unsafe, because a semicolon can appear as, say, a VARCHARs default value, or in a column comment.

With the above wrapper comments the parsing operation is safe and simple. The wrapper comments text is extremely unlikely to appear as a valid text in a DDL.

I hope this PR finds you well. It confirms to existing coding standards. As I looked into a unit test, I found it difficult to follow the docker usage in generating & comparing diffs:

https://github.com/skeema/skeema/blob/94f380a5c46124ee743a221f9b2bb0b67b1c7859/skeema_cmd_test.go#L442-L453

I also noted the above uses --brief and so does not compare actual DDL statements; and so I'm unsure how to test wrapper statements. I'm happy to work on this with guidance, as needed.

cc @github/database-infrastructure

We had skeema running nicely yesterday on Mariadb10.2 , we then updated to 10.3 and are getting errors on all table statements. We have messed around with user permissions including using the root user that has all grants on the db and the skeema temp db. The error message we are getting is the following.

2019-04-04 21:48:02 [WARN] Skipping table myTable: unable to generate DDL due to use of unsupported features. Use --debug for more information. 2019-04-04 21:48:02 [DEBUG] --- Expected CREATE 2019-04-04 21:48:02 [DEBUG] +++ MySQL-actual SHOW CREATE

it's really confusing because there is not SHOW CREATE in our sql files.... Any help would be awesome.

Hi Guys,

We ran into a problem using skeema push to update a development environment. The environment already has the database setup using skeema, we are trying to deploy an update. when we run push. we got the error complaining the database already exists and ignore all the following changes. I wonder if anyone have seem this issue or got any tips in solving this problem? Many thanks in advance.

CREATE DATABASE BZ_main CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 2019-03-20 19:02:28 [ERROR] Error running DDL on dataserver:3306 BZ_main: Error 1007: Can't create database 'BZ_main'; database exists 2019-03-20 19:02:28 [WARN] Skipping 25 remaining operations for dataserver:3306 BZ_main due to previous error 2019-03-20 19:02:28 [INFO] dataserver:3306 BZ_main: push complete

It would be great to be able to use ssl client certs for skeema. Example entry from a user's .my.cnf file:

[client]
ssl_ca=/path/to/ca_cert.pem
ssl_cert=/path/to/client_cert.pem
ssl_key=/path/to/private.key

When defining a list of schemas to pull in the config file, only the first item of the list gets pulled into the current directory. Expected would be the pull of all schemas into their respective directories.

Steps to reproduce:

1. Create new databases and tables:

CREATE USER 'skeema'@'%' IDENTIFIED BY 'skeema';
GRANT SELECT ON *.* TO 'skeema'@'%';
GRANT ALL ON _skeema_tmp.* TO  'skeema'@'%';
CREATE DATABASE test01;
CREATE DATABASE test02;
CREATE TABLE test01.testtable01 (id INTEGER NOT NULL PRIMARY KEY, val VARCHAR(255));
CREATE TABLE test01.testtable02 (id INTEGER NOT NULL PRIMARY KEY, val VARCHAR(255));
CREATE TABLE test02.testtable03 (id INTEGER NOT NULL PRIMARY KEY, val VARCHAR(255));
CREATE TABLE test02.testtable04 (id INTEGER NOT NULL PRIMARY KEY, val VARCHAR(255));

2. Create the .skeema file:

[test]
host=127.0.0.1
port=3306
user=skeema
password=skeema
schema=test01,test02

3. Fetch the schema: skeema pull test

Expected result: A tree like 127.0.0.1/{test01,test02}/testtable??.sql

Actual result: Only the tables from DB test01 are pulled into the current directory. Likewise, when the order is reversed, the test01 tables are removed and files for the test02 tables are created.

Skeema appears to use the same return code of 1 for warnings and when differences are detected. We would like to have our pipeline that uses Skeema fail on warnings because we don't want the pipeline to proceed on most warnings.

Is there any way to split out diffs vs warnings? Is there any planned change to modify the return codes to separate these?

see exit.go

Continuing from https://twitter.com/EvanElias/status/1168732285639831553

Does this look like a good workflow? https://s.natalian.org/2019-09-03/add-env.mp4

Does skeema pull pull from all configured environments? I think it's just "production", but I don't have a "production" environment...

In retrospect, I wish I inited from dev. I would prefer to see the diff between folders, since then it would be clear in my mind what is the source and what is the target.

This PR adds 3 foreign-key related lint detectors:

1. non-unique-fk-ref InnoDB allows FK references to non-unique keys in the parent table but it is advised against in the mysql reference manual. Personally I came across this as I had issues with code generation tools (from schema to code) that didn't handle our FK references to non-unique keys. A lint warning should be useful.
2. fk-missing-parent-table InnoDB lets you create a FK which references a non-existent table (with FOREIGN_KEY_CHECKS=0), added a detector for that. It doesn't seem to allow a reference to an existing table but non-existent column though, so not checking that.
3. duplicate-fk Detects if there are several FK definitions that are identical (except for their names).

Some notes:

• All the detectors assume that InnoDB is used but doesn't check the engine explicitly. AFAIK InnoDB is the only engine that checks the FKs, but it could still be interesting to check that they are valid in the linter? e.g. if FKs are used for documentation purposes.
• The function getCoveringKey(table *tengo.Table, fkColumns []*tengo.Column) *tengo.Index is useful for other code as well and should be moved to the tengo package as a method on tengo.Table.
• I have only tested this with mysql 5.7.26.

To support use of multiple mysql.cnf files, or to force to use none of the regular .my.cnf settings by pointing to an empty one.

ps. should also prove useful to work around this issue : 2019-03-14 13:06:23 [ERROR] Option host cannot be set via .my.cnf for this command but that's less important.

Describe the bug I must be missing something because I know I've gotten this tool to work before, however I also know I had the same issue where --debug doesn't print out any logging.

Environment

• Skeema version: 1.8.1-community, commit 320c1f4ea6c571db20532b79643e4b7f05b7a98c, released 2022-07-28T18:57:46Z
• Database vendor, version, OS/platform: MariaDB 10.5.15 on Debian 10
• Any other environmental notes or special configuration:

To Reproduce Not sure how this could be reproduced, but here are the following commands I've tried:

• ./skeema init -h localhost -S /var/run/mysqld/mysqld.sock --schema tc -d database -u root -p --debug production
• ./skeema init -h localhost -S /var/run/mysqld/mysqld.sock -u root -p --schema tc --debug
• Both of the above with sudo
• As well as both of the above from the root account
• As well as many different iterations with and without --debug

Each time it asks for the db password (which I know is correct because I can login via mysql), and then does nothing. The cpu usage goes to 100% and there is no output and nothing happens. Am I missing something?

When running skeema pull, the file .skeema is always read and re-generated.

I believe that this is conceptionally wrong, if the .skeema file is considered an "option file". Option files and settings should always be considered read-only by programs, and never touched or modified. The only time where it makes sense for the option file to be modified is by operations that have it as their explicit purpose, for example skeema init or skeema add-environment.

Practically, this is an issue in the following cases:

• It is impossible to add comments in order to clarify or explain the choice for certain options. Adding comments to the .skeema file is syntactically valid, but they are removed by every single invocation of skeema pull.
• If skeema directories and .skeema files are version-controlled (which is one of the main advantages of the skeema tool), then people with different versions of skeema will cause a change in the generator= line of the .skeema file every time they run skeema pull, even if there would be no changes otherwise. This can lead to avoidable merge conflicts.

Would it be possible to change skeema to do one of the following?

• a) Simply not touch the file at all and open it read-only except when skeema init or skeema add-environment are used
• b) Not touch the file if no changes to it are required
• c) Add an option that prevents any changes to files at all, or an option that prevents changes when an operation does not require them

Describe the bug In MariaDB (and possibly others), system-versioned (and bitemporal) tables have a table_type of SYSTEM VERSIONED instead of BASE TABLE in information_schema.tables. As a result, any system-versioned tables are silently ignored by Skeema and the SQL files deleted during a pull.

Environment

• Skeema version: 1.8.0
• MariaDB 10.3.4+

To Reproduce

1. Create a table with system versioning, e.g:

CREATE TABLE t (
   x INT
) WITH SYSTEM VERSIONING;

2. Do skeema pull. The table t will be ignored.

Expected behavior The schema for table t would be written to t.sql.

Additional context From my testing, this appears to be a one line fix to line 187 of internal/tengo/introspect.go by replacing

AND t.table_type = 'BASE TABLE'

with both table types:

AND t.table_type IN ('BASE TABLE', 'SYSTEM VERSIONED')

See also this PR which implements this: https://github.com/paperasteroid/skeema/pull/1

This is a proposal to adjust the mechanics of Skeema's built-in Docker interactions. If you use Skeema's workspace=docker option, this change could affect you. We aren't necessarily going ahead with this change soon, or at all. Community feedback will be greatly appreciated in steering the decision.

Background

Skeema has built-in interactions with Docker in two places:

• The workspace=docker option: when enabled, Skeema performs its temporary scratch-space operations on an ephemeral database instance in an automatically-managed Docker container, instead of using a temporary schema (e.g. _skeema_tmp) on your real DB instance. This option is recommended in high-volume production environments, as well as situations where Skeema is operating over a WAN/VPN with high network latency between skeema and your databases.
• Skeema's integration testing suites use Docker extensively, to perform automated end-to-end testing against all supported database vendors and versions. In combination (MySQL + Percona Server + MariaDB), each release of Skeema Community Edition is thoroughly tested against 16 different Dockerized database images.

These interactions currently use Docker's API, by wrapping the third-party package fsouza/go-dockerclient. This means they interact with a local Docker socket, and do not currently require a docker binary to be present.

Proposed change

Instead of using the Docker API, Skeema could shell out to the docker CLI, parsing JSON responses where needed (e.g. docker inspect output). This would require a docker binary to be present and in your $PATH if you wish to use workspace=docker, or if you wish to run Skeema's integration testing suite locally.

Motivations

Fewer Golang package dependencies, smaller binaries

Although fsouza/go-dockerclient is a very nice library, it has quite a lot of dependencies. The vast majority of these packages handle container-related functionality that Skeema does not need or use, but it nonetheless bloats Skeema's dependency graph.

These indirect dependencies frequently have security advisories, which aren't relevant to Skeema (since the affected dependency code paths are not used or accessible in Skeema), but nonetheless cause spurious Dependabot alerts from GitHub.

We expect the size of the skeema binary could be reduced by over 25% just from the removal of fsouza/go-dockerclient and its dependencies.

Compatibility with non-Docker container systems (e.g. podman)

Podman is gaining popularity as a daemon-less, root-less alternative to Docker. For some use-cases, it can serve as a drop-in replacement for the docker binary.

Although Podman also offers an optional network-accessible API, using it negates some of Podman's benefits. Switching Skeema's Docker interactions to use the docker binary should provide a better path for users who wish to substitute in a podman binary, or any other Docker-compatible binary.

Future feature integrations

Improvements in Skeema's shellout logic could be leveraged in the Docker CLI interaction code. For example, Skeema Premium CLI includes extensive SSH integration. Hypothetically, this could be used to enable secure execution of docker commands on a remote host, alongside an automatic SSH tunnel to reach the ephemeral containerized database -- without even needing to expose either the Docker daemon or the containerized database outside of localhost on the remote host.

Potential drawbacks

Some users enable workspace=docker while also running skeema itself inside Docker. This configuration is already a bit difficult -- it requires either bind-mounting the Docker socket into Skeema's container, or using a special Docker-in-Docker image. See #89 for more background.

If the change proposed in this RFC is implemented, such a configuration would become slightly more difficult: in addition to bind-mounting the Docker socket into Skeema's container, you would also need to install the Docker CLI inside Skeema's container, using your base image’s packaging system. (Although bind-mounting works for the Docker socket, bind-mounting the docker binary is not recommended, as this reportedly does not function properly.)

If you use this type of configuration, we'd love to hear your thoughts on this proposal and get a better sense of whether this would be too disruptive of a change.

In legacy production databases with multiple instances, it's not always feasible to wait for a column reorder operation to execute against a large table, particularly if there's a chance of it being interrupted by a constraint violation or the like. A flag to ignore column order when diffing/pushing would be very helpful.

Describe the bug We've recently changed the partitioning key on one of our tables from the primary key to a timestamp.

Before the change, skeema worked fine, after the change, skeema diff fails with:

2021-10-23 04:40:34 [WARN]  Skipping table `snapshots`: Skeema does not support generating a diff of this table. Use --debug to see which properties of this table are not supported.
2021-10-23 04:40:34 [DEBUG] --- Expected CREATE
2021-10-23 04:40:34 [DEBUG] +++ MySQL-actual SHOW CREATE
2021-10-23 04:40:34 [DEBUG] @@ -18 +18 @@
2021-10-23 04:40:34 [DEBUG] -) ENGINE=InnoDB AUTO_INCREMENT=142294075 DEFAULT CHARSET=utf8mb4
2021-10-23 04:40:34 [DEBUG] +) ENGINE=InnoDB AUTO_INCREMENT=142294076 DEFAULT CHARSET=utf8mb4
2021-10-23 04:40:34 [DEBUG] @@ -20 +20 @@
2021-10-23 04:40:34 [DEBUG] -(PARTITION start VALUES LESS THAN (0) ENGINE = InnoDB,
2021-10-23 04:40:34 [DEBUG] +(PARTITION `start` VALUES LESS THAN (0) ENGINE = InnoDB,

Looks like start is a keyword in mysql, so mysql is quoting it in SHOW CREATE TABLE, and this confuses skeema.

Old table looked like this:

CREATE TABLE `snapshots` (
  `snapshot_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
[...]
  PRIMARY KEY (`snapshot_id`),
[...]
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE ( snapshot_id)
(PARTITION p3840143034 VALUES LESS THAN (3840143034) ENGINE = InnoDB,
 PARTITION p3854209492 VALUES LESS THAN (3854209492) ENGINE = InnoDB,
[...]
 PARTITION p4276203232 VALUES LESS THAN (4276203232) ENGINE = InnoDB,
 PARTITION p4290269690 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

New table looks like this:

CREATE TABLE `snapshots` (
  `snapshot_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
[...]
  PRIMARY KEY (`snapshot_id`,`date`),
[...]
) ENGINE=InnoDB AUTO_INCREMENT=142299500 DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(date))
(PARTITION `start` VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION from201909 VALUES LESS THAN (1569902400) ENGINE = InnoDB,
[...]
 PARTITION from202211 VALUES LESS THAN (1669870800) ENGINE = InnoDB,
 PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Environment

• Skeema version: 1.5.3-community, commit 390663c6d7d5784e371fcf0683548e7fc0c50522
• Database vendor, version, OS/platform: Percona Server 5.7.35-3 on CentOS 7

To Reproduce See above.

Expected behavior Expected not to get an error when running skeema diff