Charcoal HQ

ArtOfCode

12:02 AM

Hey @Undo, what d'ye think of this?

web-based SQL console for your database, integratable into your existing app

Undo

sets off all kinds of warning bells

Cool, though

ArtOfCode

@Undo there's the thing - it did for me initally, too.

Most things are covered - the only abuse vector I'm still seeing is people running heavy queries and DOSing the server

Undo

First, we should probably figure out how to export consumable data dumps

Host them on S3 and mark the public, and you're ready to go

ArtOfCode

Could add a query timeout to the MySQL server to avoid that, which would probably be a sensible idea anyway given the length of time some searches run for

angussidney

Morning

Undo

12:09 AM

'Morning!

ArtOfCode

@Undo some shell commands and an SQL script

Undo

So what columns do we actually need to exclude?

users.email, token, password, to start

ArtOfCode

# export-dump.sh
mysqldump metasmoke_production > dump.sql
mysqladmin create dump_database
mysql dump_database < dump.sql
mysql dump_database < sanitize.sql

with relevant usernames and passwords where required, which can just go in source since this doesn't need to be put on GH

Undo

Would be nice to be able to deploy it with Cap, though

ArtOfCode

shrug you'd have to make modifications for that, since I have zero clue how it works

angussidney

12:14 AM

@ThomasWard you didn't actually break smokey then, it just violated some ratelimiting backoffs on the API. Back in the day, we used to got them a lot, but now they shouldn't be fixed, so it's a little strange that it popped up....

Undo

Right now I'm interested by github.com/twitter/AnomalyDetection

angussidney

@Andy good game :)

dorukayhan

is there a way to make smokey get flagging again without restarting him after telling him to !!/stopflagging?

Undo

You have to ping one of angus, Art, Andy or me

Restarting Smokey won't do anything either, stopflagging just tells metasmoke to change a setting

Thomas Ward

@dorukayhano MetaSmoke handles the determination of whether something gets flaged.

@dorukayhan Smokey has the real-time post code, which feeds to MS, which in turn lets things work; so until the setting is switched MS side, as @Undo said, even a Smokey restart doesn't restart flagging

dorukayhan

12:18 AM

@ThomasWard oh

angussidney

@Undo or hichris

Thomas Ward

MetaSmoke has no realtime post parsing. Smokey has no flagging code.

to summarize

ArtOfCode

@Undo full sanitization SQL:

-- sanitize.sql
ALTER TABLE `api_keys`
	DROP `key`;

ALTER TABLE `api_tokens`
	DROP `code`,
	DROP `token`;

DROP TABLE `flags`;

DROP TABLE `ignored_users`;

ALTER TABLE `smoke_detectors`
	DROP `access_token`;

ALTER TABLE `users`
	DROP `email`,
	DROP `encrypted_password`,
	DROP `current_sign_in_ip`,
	DROP `last_sign_in_ip`,
	DROP `reset_password_token`,
	DROP `reset_password_sent_at`,
	DROP `api_token`;

Undo

Can't actually drop stuff, though, that'll bork stuff on import.

ArtOfCode

Depends if the dump tries to delete and recreate tables, or just fill existing tables with data

Thomas Ward

12:21 AM

if you configure the dump to create a temp table that contains a copy of the dump, then alter that temporary table, and dump that you don't break imports.

Undo

Not sure

Thomas Ward

that said, it's much more painful

(1) dump. (2) create temporary database. (3) dump -> temporary (4) alter temporary (5) dump temporary. (6) modified dataset

ArtOfCode

or you could just UPDATE... SET...

@ThomasWard this is precisely what those shell commands and dump SQL do

Thomas Ward

indeed

oops

@ArtOfCode didn't see any shell code, already looked now and saw it

ArtOfCode

look further up :P

Thomas Ward

12:24 AM

system accidentally punted Google Chrome into the "kill it" list

Jan Dvorak

That was an accident?

Thomas Ward

glares at @JanDvorak

ArtOfCode

-- sanitize.sql
UPDATE `api_keys` SET `key` = '';
UPDATE `api_tokens` SET `code` = '', `token` = '';
DROP TABLE `flags`;
DROP TABLE `ignored_users`;
UPDATE `smoke_detectors` SET `access_token` = '';
UPDATE `users` SET
	`email` = '',
	`encrypted_password` = '',
	`current_sign_in_ip` = '',
	`last_sign_in_ip` = '',
	`reset_password_token` = '',
	`reset_password_sent_at` = NULL,
	`api_token` = '';

that's the alternative

ArtOfCode

12:40 AM

@Undo also, what time/date does the metasmoke server think it is?

Transcript for