logging sql alterations for migrations

[vampirefrog]

Hello. Is there a way to log MySQL ALTER/CREATE TABLE statements so that I might use those logs as migration queries when deploying? Basically when I modify an entity on the development server, let's say I add or rename a column, I don't want to replace all the rows (ie import a mysql dump) on the staging server, I just want to alter the table on the staging server, and keep its existing rows.

Is this possible somehow?
Thanks.

[tanya]

Hello
set logger level in data/config.php to 'DEBUG'

[vampirefrog]

Here is a script I made. I hope it's correct, it seems to work for me. It reads the MySQL ALTER and CREATE queries from the log file, then deletes the log files. It greps for the string 'SCHEMA, Execute Query:'.

#!/bin/sh

# fill these in
LOCAL_DB_NAME=
LOCAL_DB_USER=
LOCAL_DB_PASS=

REMOTE_HOST=
REMOTE_USER=
REMOTE_DIR=
REMOTE_CHOWN=

# copy remote config locally and get DB settings
scp $REMOTE_HOST:$REMOTE_DIR/data/config.php /tmp/deploy_config.php &&
REMOTE_DB_NAME="`php -r "\\\$config = include '/tmp/deploy_config.php'; echo \\\$config['database']['dbname'];"`"
REMOTE_DB_USER="`php -r "\\\$config = include '/tmp/deploy_config.php'; echo \\\$config['database']['user'];"`"
REMOTE_DB_PASS="`php -r "\\\$config = include '/tmp/deploy_config.php'; echo \\\$config['database']['password'];"`"
rm -f /tmp/deploy_config.php

echo "REMOTE_DB_NAME=$REMOTE_DB_NAME REMOTE_DB_USER=$REMOTE_DB_USER REMOTE_DB_PASS=$REMOTE_DB_PASS"

if [ -z "$REMOTE_DB_NAME" ]; then echo "Empty REMOTE_DB_NAME"; exit 1; fi
if [ -z "$REMOTE_DB_USER" ]; then echo "Empty REMOTE_DB_USER"; exit 1; fi
if [ -z "$REMOTE_DB_PASS" ]; then echo "Empty REMOTE_DB_PASS"; exit 1; fi

# files
rsync \
    --verbose \
    --recursive \
    --delete \
    --times \
    --exclude=\*.sql \
    --exclude=deploy.sh \
    --exclude=data/config.php \
    --exclude=data/cache/* \
    --exclude=.git \
    --exclude=node_modules/ \
    --exclude=vendor/bin/phpunit \
    --exclude=vendor/zendframework/zend-crypt/doc/book/index.md \
    . \
    $REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR

# remote db backup
ssh $REMOTE_USER@$REMOTE_HOST "mysqldump -u$REMOTE_DB_USER -p$REMOTE_DB_PASS $REMOTE_DB_NAME > \"$REMOTE_DIR/$REMOTE_DB_NAME-`date +'%Y-%m-%d_%H-%M-%S'`\".sql"

#mysqldump -u$LOCAL_DB_USER -p$LOCAL_DB_PASS $LOCAL_DB_NAME | ssh $REMOTE_USER@$REMOTE_HOST mysql -u$REMOTE_DB_USER -p$REMOTE_DB_PASS -D$REMOTE_DB_NAME

# find schema alteration queries and send them to remote
MIGRATE_FILE=migrate-`date +'%Y-%m-%d_%H-%M-%S'`.sql
grep -h 'SCHEMA, Execute Query:' data/logs/espo-*.log | sed -e "s/^.*\?SCHEMA, Execute Query: \(.*\?\) \[\] \[\]/\\1;/g" > $MIGRATE_FILE
sudo rm -f data/logs/espo-*.log
ssh $REMOTE_USER@$REMOTE_HOST mysql -u$REMOTE_DB_USER -p$REMOTE_DB_PASS -D$REMOTE_DB_NAME < $MIGRATE_FILE

# update remote config but with remote DB settings
cat data/config.php | sed \
    -e "s/'user' => '[^']\+'/'user' => '$REMOTE_DB_USER'/" \
    -e "s/'password' => '[^']\+'/'password' => '$REMOTE_DB_PASS'/" \
    -e "s/'dbname' => '[^']\+'/'dbname' => '$REMOTE_DB_NAME'/" > /tmp/deploy_config2.php
scp /tmp/deploy_config2.php $REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR/data/config.php
rm -f /tmp/deploy_config2.php

# clear remote cache
ssh $REMOTE_USER@$REMOTE_HOST "cd $REMOTE_DIR && php clear_cache.php"