How can I use Drush to sanitize a database on a remote server?

Question

I'm on Pantheon and I want to run some custom SQL queries to sanitize my dev server.

I have the queries in a file, custom_sanitize.sqlqueries.

It looks something like this:

UPDATE user__field_useragent SET field_useragent_value = 'Sputnik';
UPDATE node__field_useragent SET field_useragent_value = 'Sputnik';
UPDATE node_revision__field_useragent SET field_useragent_value = 'Sputnik';

TRUNCATE TABLE watchdog;

Goal

I want to run these queries on a remote server with a single Drush command.

My Current Workaround

I connect to the database with Sequel Pro, cut and paste the queries in, and run them.

What I tried

Based on the Drush 8 sql-query documentation, I tried to execute my file like this:

drush @alias.dev sql-query --file=custom_sanitize.sqlqueries

This fails:

failed to open stream: No     [warning]
such file or directory drush.inc:904

I assume this is because my local drush calls the remote drush on the server, and the file path is different.

So then I tried to input the content of the file this way:

`drush @alias.dev sql-connect` < custom_sanitize.sqlqueries

This fails with the following message:

-bash: PTY: command not found

So then I tried inputting the queries in at the command line:

drush @alias.dev sql-query "UPDATE node_revision__field_useragent SET field_useragent_value = 'Sputnik';TRUNCATE TABLE watchdog;"

This also fails:

PTY allocation request failed on channel 0
Command not supported as typed.

I also tried using hook_drush_sql_sync_sanitize(), but I couldn't get my hook picked up by the drush on Pantheon.

Answer 1

Pantheon's terminus can skip drush, and pipe SQL directly into a "mysql connection command"

echo 'UPDATE users SET pass="12345"' | `terminus connection:info mysite.dev --field=mysql_command`

(source)

Answer 2

You can use sql-cli command for that:

drush @alias.dev sql-cli < custom_sanitize.sql

Answer 3

You could use the db_sanitization Quicksilver example, and sanitize automatically every time you pulled the database into your dev environment.

If that doesn't work for you, open a ticket with Pantheon, and I will look at it on Monday (if someone else doesn't help you sooner than that).

Answer 4

As a workaround, you can input the queries one by one:

drush @alias.dev sql-query "TRUNCATE table watchdog"

So, you can change every query in custom_sanitize.sqlqueries to be drush @alias.dev sql-query "myquery", and then execute that file.