Add support for generated columns

I love using stellar as it's super powerful for development =D We recently bumped into an issue with generated columns due to copying over all columns

Unfortunately, I don't have the bandwidth to dig into fixing stellar properly (likely omitting certain columns on INSERT line)

$ stellar snapshot
Snapshotting database DATABASE_NAME
Traceback (most recent call last):
  File "[...]/site-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
    self.dialect.do_execute(
  File "[...]/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "[...]/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "[...]/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "[...]/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1906, "The value specified for generated column 'COLUMN_NAME' in table 'TABLE_NAME' has been ignored")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "[...]/bin/stellar", line 8, in <module>
    sys.exit(main())
  File "[...]/site-packages/stellar/command.py", line 279, in main
    stellar()
  File "[...]/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "[...]/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "[...]/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "[...]/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "[...]/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "[...]/site-packages/stellar/command.py", line 70, in snapshot
    app.create_snapshot(name, before_copy=before_copy)
  File "[...]/site-packages/stellar/app.py", line 118, in create_snapshot
    self.operations.copy_database(
  File "[...]/site-packages/stellar/operations.py", line 91, in copy_database
    raw_conn.execute('''
  File "[...]/site-packages/sqlalchemy/engine/base.py", line 1248, in execute
    return self._exec_driver_sql(
  File "[...]/site-packages/sqlalchemy/engine/base.py", line 1547, in _exec_driver_sql
    ret = self._execute_context(
  File "[...]/site-packages/sqlalchemy/engine/base.py", line 1814, in _execute_context
    self._handle_dbapi_exception(
  File "[...]/site-packages/sqlalchemy/engine/base.py", line 1995, in _handle_dbapi_exception
    util.raise_(
  File "[...]/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "[...]/site-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
    self.dialect.do_execute(
  File "[...]/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "[...]/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "[...]/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "[...]/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1906, "The value specified for generated column 'status_is_active' in table 'TABLE_NAME' has been ignored")
[SQL:
                INSERT INTO stellar_f14ee991b130be2d.TABLE_NAME SELECT * FROM DATABASE_NAME.TABLE_NAME
            ]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

I did however write up a workaround bash utility for myself and coworkers that others might find useful:

# Usage:
./stellar.sh restore
./stellar.sh snapshot

#!/usr/bin/env bash
# Exit on error, unset variable, or pipe failure
set -euo pipefail

# Wrapper for `stellar` to work around generated columns

# Resolve our command from invocation, ${n:-} does fallback (e.g. `./stellar.sh` without parameter)
# https://stackoverflow.com/a/25066804/1960509
command="${1:-}"

# If we're snapshotting, then drop our troublesome columns
if [ "$command" == "snapshot" ]; then
  echo "Dropping generated columns briefly" 1>&2
  echo "
  SET FOREIGN_KEY_CHECKS=0;
  ALTER TABLE \`TABLE_NAME\`
      DROP
      IF EXISTS
      \`COLUMN_NAME\`;
  " | mysql DB_NAME
fi

stellar $*

# If we just completed a snapshot, or are restoring, then back our troublesome columns
if [ "$command" == "snapshot" ] || [ "$command" == "restore" ]; then
  echo "Restoring generated columns" 1>&2
  echo "
  ALTER TABLE \`TABLE_NAME\`
      ADD COLUMN
      IF NOT EXISTS
      \`COLUMN_NAME\` TINYINT(1)
      AS (IF(\`COLUMN_NAME\` <> 'VALUE', TRUE, NULL)) STORED;
   " | mysql DB_NAME
fi

fastmonkeys / stellar

Add support for generated columns #88