Django 1.11 troubleshooting

Mistakes

* should have noticed earlier that stage and prod were already configured to cycle gunicorn workers based on a request cap due to longstanding memory leak issues.

Loading prod/loadtest database schemas for testing migrations locally

Dump schema from replica

# note single space preceding next two lines
 prod_readonly_pass=''
 loadtest_readonly_pass=''
ssh pwnage101@tools-gp.edx.org mysqldump -u read_only -h prod-edx-replica-rds.edx.org -p"\"$prod_readonly_pass\"" --no-data --lock-tables=false wwc > prod_replica_wwc_schemas.sql
ssh pwnage101@tools-gp.edx.org mysqldump -u read_only -h loadtest-edx-rds.edx.org -p"\"$loadtest_readonly_pass\"" --no-data --lock-tables=false wwc > loadtest_wwc_schemas.sql

Sanitize auto increments for better diffs:

sed 's/ AUTO_INCREMENT=[0-9]*//' <prod_replica_wwc_schemas.sql >prod_replica_wwc_schemas_no_autoincrement.sql
sed 's/ AUTO_INCREMENT=[0-9]*//' <loadtest_wwc_schemas.sql >loadtest_wwc_schemas_no_autoincrement.sql

Dump data from django_migrations from replica

ssh pwnage101@tools-gp.edx.org mysqldump -u read_only -h prod-edx-replica-rds.edx.org -p"\"$prod_readonly_pass\"" --lock-tables=false wwc django_migrations > prod_replica_wwc_django_migrations.sql
ssh pwnage101@tools-gp.edx.org mysqldump -u read_only -h loadtest-edx-rds.edx.org -p"\"$loadtest_readonly_pass\"" --lock-tables=false wwc django_migrations > loadtest_wwc_django_migrations.sql

Load prod schema and django_migrations into devstack

docker-compose exec -T mysql mysql <<EOF
DROP DATABASE edxapp;
CREATE DATABASE edxapp;
EOF
docker-compose exec -T mysql mysql edxapp </path/to/prod_replica_wwc_schemas_no_autoincrement.sql
docker-compose exec -T mysql mysql edxapp </path/to/prod_replica_wwc_django_migrations.sql

Test the auth migration

# in devstack repo:
docker-compose exec -T mysql mysql <<EOF
SET global general_log = 1;
SET global log_output = 'table';
EOF

# in lms-shell:
./manage.py lms migrate auth 0007_alter_validators_add_error_messages
./manage.py lms migrate auth 0008_alter_user_username_max_length

# in devstack repo:
echo 'select * from mysql.general_log' | docker-compose exec -T mysql mysql edxapp >mysql_general_log.csv

Testing migrations in a sanitized database recovered from prod

RDS/MySQL logging facilities

# enable general log (assumes "loadtest-edx-mysql56-1.11-with-query-log" is the name of the parameter group)
rds-modify-db-parameter-group loadtest-edx-mysql56-1.11-with-query-log --parameters "name=general_log,value=ON,method=immediate"

# retrieve general log
ssh -t tools-gp.edx.org "mysql --user=read_only --host=<hostname_of_db> -p --execute='select * from mysql.general_log;'" >query_log.csv

# retrieve binlog (must be root user)
first_binlog=$(ssh -t tools-gp.edx.org "mysql --user=root --host=<hostname_of_db> -p --execute='show binary logs;' --skip-column-names" | head -n 1)
ssh tools-gp.edx.org "mysqlbinlog --raw --result-file=/tmp/binlogs/ --read-from-remote-server --to-last-log --user=root --host="<hostname_of_db>" -p $first_binlog"

# view binlog locally
scp -r tools-gp.edx.org:/tmp/binlogs .
mysqlbinlog binlogs/*

test the migration with logging to assert the expected SQL statements will execute

# capture the current state of django migrations
ssh -t tools-gp.edx.org "mysql --user=read_only --host=<hostname_of_db> -p --execute='use wwc; select * from django_migrations;' --batch" >django_migrations_before_cms_migration.csv

# do something in mysql to mark the point in time before migrations begin:
select "before cms migration";

# on an instance containing all the migrations for the django 1.11 upgrade:
DB_MIGRATION_USER=<migration user name> DB_MIGRATION_PASS=<migration user password> /edx/bin/edxapp-migrate-cms
DB_MIGRATION_USER=<migration user name> DB_MIGRATION_PASS=<migration user password> /edx/bin/edxapp-migrate-lms