1

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.

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)

2

You can use sql-cli command for that:

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

  • I tried that but i get the following output when sending a .sql file with a single line about truncating the watchdog table: Pseudo-terminal will not be allocated because stdin is not a terminal. TRUNCATE TABLE watchdog; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A TERM environment variable not set. On the server, the watchdog table is not truncated. – Patrick Kenny Feb 20 '16 at 12:45
  • can you ssh to the remote? It seems it is rather an ssh problem than drush. – Елин Й. Feb 20 '16 at 14:02
  • No ssh on Pantheon, alas. – Patrick Kenny Feb 20 '16 at 14:13
  • Then, I guess, you are out of luck, unless there are some instructions special to Pantheon. I have no experience with Pantheon, so I cannot help any further. Did you ask the Pantheon support about that? – Елин Й. Feb 20 '16 at 14:23
1

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).

  • Alas, the example didn't work for me (in Drupal 8). I opened a ticket. – Patrick Kenny Feb 20 '16 at 16:02
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.