{"action":"created","comment":{"url":"https://api.github.com/repos/google/grr/pulls/comments/24530758","id":24530758,"diff_hunk":"@@ -0,0 +1,636 @@\n+#!/usr/bin/env python\n+# -*- mode: python; encoding: utf-8 -*-\n+\n+\"\"\"An implementation of a data store based on mysql.\"\"\"\n+\n+\n+import Queue\n+import threading\n+import thread\n+import time\n+import MySQLdb\n+import re\n+\n+from MySQLdb import cursors\n+\n+from grr.lib import aff4\n+from grr.lib import config_lib\n+from grr.lib import data_store\n+from grr.lib import rdfvalue\n+from grr.lib import utils\n+from grr.lib.data_stores import common\n+\n+# pylint: disable=nonstandard-exception\n+class Error(data_store.Error):\n+  \"\"\"Base class for all exceptions in this module.\"\"\"\n+# pylint: enable=nonstandard-exception\n+\n+\n+class MySQLConnection(object):\n+  \"\"\"A Class to manage MySQL database connections.\"\"\"\n+\n+  def __init__(self, queue=None):\n+    self.queue = queue\n+    try:\n+      self._MakeConnection(database=config_lib.CONFIG[\"Mysql.database_name\"])\n+    except MySQLdb.OperationalError as e:\n+      # Database does not exist\n+      if \"Unknown database\" in str(e):\n+        dbh = self._MakeConnection()\n+        cursor = dbh.cursor()\n+        cursor.execute(\"Create database `%s`\" %\n+                       config_lib.CONFIG[\"Mysql.database_name\"])\n+\n+        self._MakeConnection(database=config_lib.CONFIG[\"Mysql.database_name\"])\n+      else:\n+        raise\n+\n+  def _MakeConnection(self, database=\"\"):\n+    try:\n+      connection_args = dict(\n+          user=config_lib.CONFIG[\"Mysql.database_username\"],\n+          db=database, charset=\"utf8\",\n+          passwd=config_lib.CONFIG[\"Mysql.database_password\"],\n+          cursorclass=cursors.DictCursor)\n+      if config_lib.CONFIG[\"Mysql.host\"]:\n+        connection_args[\"host\"] = config_lib.CONFIG[\"Mysql.host\"]\n+      if config_lib.CONFIG[\"Mysql.port\"]:\n+        connection_args[\"port\"] = config_lib.CONFIG[\"Mysql.port\"]\n+\n+      self.dbh = MySQLdb.connect(**connection_args)\n+      self.cursor = self.dbh.cursor()\n+      self.cursor.connection.autocommit(True)\n+\n+      return self.dbh\n+    except MySQLdb.OperationalError as e:\n+      # This is a fatal error, we just raise the top level exception here.\n+      if \"Access denied\" in str(e):\n+        raise Error(str(e))\n+      raise\n+\n+  def __enter__(self):\n+    return self\n+\n+  def __exit__(self, unused_type, unused_value, unused_traceback):\n+    if self.queue:\n+      self.queue.put(self)\n+\n+  def Execute(self, *args):\n+    retries = 50\n+    for i in range(1, retries):\n+      try:\n+        self.cursor.execute(*args)\n+        return self.cursor.fetchall()\n+      except MySQLdb.Error:\n+        time.sleep(.1)\n+        try:\n+          self._MakeConnection(database=config_lib.CONFIG[\"Mysql.database_name\"])\n+        except MySQLdb.OperationalError:\n+          pass\n+\n+    try:\n+      self.cursor.execute(*args)\n+      return self.cursor.fetchall()\n+    except MySQLdb.Error:\n+      raise\n+\n+\n+class ConnectionPool(object):\n+  \"\"\"A pool of connections to the mysql server.\n+\n+  Usage:\n+\n+  with data_store.DB.pool.GetConnection() as connection:\n+    connection.Execute(.....)\n+  \"\"\"\n+\n+  def __init__(self, pool_size=5):\n+    self.connections = Queue.Queue()\n+    for _ in range(pool_size):\n+      self.connections.put(MySQLConnection(self.connections))\n+\n+  def GetConnection(self):\n+    return self.connections.get(block=True)\n+\n+\n+class MySQLAdvancedDataStore(data_store.DataStore):\n+  \"\"\"A mysql based data store.\"\"\"\n+\n+  POOL = None\n+  SYSTEM_TABLE = \"system\"\n+\n+  def __init__(self):\n+    # Use the global connection pool.\n+    if MySQLAdvancedDataStore.POOL is None:\n+      MySQLAdvancedDataStore.POOL = ConnectionPool()\n+    self.pool = self.POOL\n+\n+    self.to_insert = []\n+    self._CalculateAttributeStorageTypes()\n+    self.database_name = config_lib.CONFIG[\"Mysql.database_name\"]\n+    self.lock = threading.Lock()\n+\n+    super(MySQLAdvancedDataStore, self).__init__()\n+\n+  def Initialize(self):\n+    try:\n+      self._ExecuteQuery(\"desc `aff4`\")\n+    except MySQLdb.Error:\n+      self.RecreateTables()\n+\n+  def DropTables(self):\n+    #Drop all existing tables\n+    rows = self._ExecuteQuery(\"select table_name from information_schema.tables \"\n+                              \"where table_schema='%s'\" % self.database_name)\n+    for row in rows:\n+      self._ExecuteQuery(\"drop table `%s`\" % row[\"table_name\"])\n+\n+  def RecreateTables(self):\n+    \"\"\"Drops the tables and creates a new ones.\"\"\"\n+    self.DropTables()\n+\n+    self._CreateTables()\n+\n+  def Transaction(self, subject, lease_time=None, token=None):\n+    return MySQLTransaction(self, subject, lease_time=lease_time, token=token)\n+\n+  def Size(self):\n+    query = (\"SELECT table_schema, Sum(data_length + index_length) `size` \"\n+             \"FROM information_schema.tables \"\n+             \"WHERE table_schema = \\\"%s\\\" GROUP by table_schema\" %\n+             self.database_name)\n+\n+    result = self._ExecuteQuery(query, [])\n+    if len(result) != 1:\n+      return -1\n+    return int(result[0][\"size\"])\n+\n+  def DeleteAttributes(self, subject, attributes, start=None, end=None, sync=None, token=None):\n+    \"\"\"Remove some attributes from a subject.\"\"\"\n+    _ = sync  # Unused\n+    self.security_manager.CheckDataStoreAccess(token, [subject], \"w\")\n+    if not attributes:\n+      return\n+\n+    for attribute in attributes:\n+      timestamp = self._MakeTimestamp(start, end)\n+      predicate = utils.SmartUnicode(attribute)\n+      query, args = self._BuildQuery(\"DELETE\", subject, predicate, timestamp)\n+      self._ExecuteQuery(query, args)\n+\n+  def DeleteAttributesRegex(self, subject, regexes, token=None):\n+    self.security_manager.CheckDataStoreAccess(token, [subject], \"w\")\n+\n+    for regex in regexes:\n+      query, args = self._BuildQuery(\"DELETE\", subject, regex, is_regex=True)\n+      self._ExecuteQuery(query, args)\n+\n+  def DeleteSubject(self, subject, token=None, sync=False):\n+    _ = sync\n+    self.security_manager.CheckDataStoreAccess(token, [subject], \"w\")\n+\n+    query, args = self._BuildQuery(\"DELETE\", subject)\n+    self._ExecuteQuery(query, args)\n+\n+  def ResolveMulti(self, subject, predicates, token=None, timestamp=None):\n+    \"\"\"Resolves multiple predicates at once for one subject.\"\"\"\n+    self.security_manager.CheckDataStoreAccess(\n+        token, [subject], self.GetRequiredResolveAccess(predicates))\n+\n+    for predicate in predicates:","path":"lib/data_stores/mysql_advanced_data_store.py","position":200,"original_position":200,"commit_id":"0ddcd58d361f472b88052f692b213a10d0a66b6d","original_commit_id":"0ddcd58d361f472b88052f692b213a10d0a66b6d","user":{"login":"scudette","id":3856546,"avatar_url":"https://avatars.githubusercontent.com/u/3856546?v=3","gravatar_id":"","url":"https://api.github.com/users/scudette","html_url":"https://github.com/scudette","followers_url":"https://api.github.com/users/scudette/followers","following_url":"https://api.github.com/users/scudette/following{/other_user}","gists_url":"https://api.github.com/users/scudette/gists{/gist_id}","starred_url":"https://api.github.com/users/scudette/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/scudette/subscriptions","organizations_url":"https://api.github.com/users/scudette/orgs","repos_url":"https://api.github.com/users/scudette/repos","events_url":"https://api.github.com/users/scudette/events{/privacy}","received_events_url":"https://api.github.com/users/scudette/received_events","type":"User","site_admin":false},"body":"It may be that round trips are insignificant in mysql compared to the time\nit takes to process the data. In sqlite we kind of assume that round trips\nare 0 because its really a local file anyway. Not sure how much we should\nworry about round trips in mysql.\n\nOn 11 February 2015 at 21:28, Sean Gillespie <notifications@github.com>\nwrote:\n\n> In lib/data_stores/mysql_advanced_data_store.py\n> <https://github.com/google/grr/pull/96#discussion_r24530234>:\n>\n> > +      query, args = self._BuildQuery(\"DELETE\", subject, regex, is_regex=True)\n> > +      self._ExecuteQuery(query, args)\n> > +\n> > +  def DeleteSubject(self, subject, token=None, sync=False):\n> > +    _ = sync\n> > +    self.security_manager.CheckDataStoreAccess(token, [subject], \"w\")\n> > +\n> > +    query, args = self._BuildQuery(\"DELETE\", subject)\n> > +    self._ExecuteQuery(query, args)\n> > +\n> > +  def ResolveMulti(self, subject, predicates, token=None, timestamp=None):\n> > +    \"\"\"Resolves multiple predicates at once for one subject.\"\"\"\n> > +    self.security_manager.CheckDataStoreAccess(\n> > +        token, [subject], self.GetRequiredResolveAccess(predicates))\n> > +\n> > +    for predicate in predicates:\n>\n> I'm pretty sure the round trips actually speed things up in a real GRR\n> environment. The problem is when you do the bulk query using WHERE IN or OR\n> MySQL can't use the indexes efficiently so the bulk query takes much longer\n> doing table scans than the individual optimized queries take in aggregate\n> using indexes. I am going to see if our DBA can come up with a better\n> optimization though.\n>\n> —\n> Reply to this email directly or view it on GitHub\n> <https://github.com/google/grr/pull/96/files#r24530234>.\n>\n","created_at":"2015-02-11T20:35:04Z","updated_at":"2015-02-11T20:35:04Z","html_url":"https://github.com/google/grr/pull/96#discussion_r24530758","pull_request_url":"https://api.github.com/repos/google/grr/pulls/96","_links":{"self":{"href":"https://api.github.com/repos/google/grr/pulls/comments/24530758"},"html":{"href":"https://github.com/google/grr/pull/96#discussion_r24530758"},"pull_request":{"href":"https://api.github.com/repos/google/grr/pulls/96"}}},"pull_request":{"url":"https://api.github.com/repos/google/grr/pulls/96","id":27218676,"html_url":"https://github.com/google/grr/pull/96","diff_url":"https://github.com/google/grr/pull/96.diff","patch_url":"https://github.com/google/grr/pull/96.patch","issue_url":"https://api.github.com/repos/google/grr/issues/96","number":96,"state":"open","locked":false,"title":"MySQLAdvanced Datastore & Updated Benchmark Tests","user":{"login":"pidydx","id":8451350,"avatar_url":"https://avatars.githubusercontent.com/u/8451350?v=3","gravatar_id":"","url":"https://api.github.com/users/pidydx","html_url":"https://github.com/pidydx","followers_url":"https://api.github.com/users/pidydx/followers","following_url":"https://api.github.com/users/pidydx/following{/other_user}","gists_url":"https://api.github.com/users/pidydx/gists{/gist_id}","starred_url":"https://api.github.com/users/pidydx/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pidydx/subscriptions","organizations_url":"https://api.github.com/users/pidydx/orgs","repos_url":"https://api.github.com/users/pidydx/repos","events_url":"https://api.github.com/users/pidydx/events{/privacy}","received_events_url":"https://api.github.com/users/pidydx/received_events","type":"User","site_admin":false},"body":"","created_at":"2015-01-12T19:20:11Z","updated_at":"2015-02-11T20:35:04Z","closed_at":null,"merged_at":null,"merge_commit_sha":"2dafda01a44bc2a8a544df4384dca5ba63822c74","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/google/grr/pulls/96/commits","review_comments_url":"https://api.github.com/repos/google/grr/pulls/96/comments","review_comment_url":"https://api.github.com/repos/google/grr/pulls/comments/{number}","comments_url":"https://api.github.com/repos/google/grr/issues/96/comments","statuses_url":"https://api.github.com/repos/google/grr/statuses/0ddcd58d361f472b88052f692b213a10d0a66b6d","head":{"label":"pidydx:MySQLAdvanced","ref":"MySQLAdvanced","sha":"0ddcd58d361f472b88052f692b213a10d0a66b6d","user":{"login":"pidydx","id":8451350,"avatar_url":"https://avatars.githubusercontent.com/u/8451350?v=3","gravatar_id":"","url":"https://api.github.com/users/pidydx","html_url":"https://github.com/pidydx","followers_url":"https://api.github.com/users/pidydx/followers","following_url":"https://api.github.com/users/pidydx/following{/other_user}","gists_url":"https://api.github.com/users/pidydx/gists{/gist_id}","starred_url":"https://api.github.com/users/pidydx/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pidydx/subscriptions","organizations_url":"https://api.github.com/users/pidydx/orgs","repos_url":"https://api.github.com/users/pidydx/repos","events_url":"https://api.github.com/users/pidydx/events{/privacy}","received_events_url":"https://api.github.com/users/pidydx/received_events","type":"User","site_admin":false},"repo":{"id":25012093,"name":"grr","full_name":"pidydx/grr","owner":{"login":"pidydx","id":8451350,"avatar_url":"https://avatars.githubusercontent.com/u/8451350?v=3","gravatar_id":"","url":"https://api.github.com/users/pidydx","html_url":"https://github.com/pidydx","followers_url":"https://api.github.com/users/pidydx/followers","following_url":"https://api.github.com/users/pidydx/following{/other_user}","gists_url":"https://api.github.com/users/pidydx/gists{/gist_id}","starred_url":"https://api.github.com/users/pidydx/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pidydx/subscriptions","organizations_url":"https://api.github.com/users/pidydx/orgs","repos_url":"https://api.github.com/users/pidydx/repos","events_url":"https://api.github.com/users/pidydx/events{/privacy}","received_events_url":"https://api.github.com/users/pidydx/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/pidydx/grr","description":"GRR Rapid Response Framework","fork":true,"url":"https://api.github.com/repos/pidydx/grr","forks_url":"https://api.github.com/repos/pidydx/grr/forks","keys_url":"https://api.github.com/repos/pidydx/grr/keys{/key_id}","collaborators_url":"https://api.github.com/repos/pidydx/grr/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/pidydx/grr/teams","hooks_url":"https://api.github.com/repos/pidydx/grr/hooks","issue_events_url":"https://api.github.com/repos/pidydx/grr/issues/events{/number}","events_url":"https://api.github.com/repos/pidydx/grr/events","assignees_url":"https://api.github.com/repos/pidydx/grr/assignees{/user}","branches_url":"https://api.github.com/repos/pidydx/grr/branches{/branch}","tags_url":"https://api.github.com/repos/pidydx/grr/tags","blobs_url":"https://api.github.com/repos/pidydx/grr/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/pidydx/grr/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/pidydx/grr/git/refs{/sha}","trees_url":"https://api.github.com/repos/pidydx/grr/git/trees{/sha}","statuses_url":"https://api.github.com/repos/pidydx/grr/statuses/{sha}","languages_url":"https://api.github.com/repos/pidydx/grr/languages","stargazers_url":"https://api.github.com/repos/pidydx/grr/stargazers","contributors_url":"https://api.github.com/repos/pidydx/grr/contributors","subscribers_url":"https://api.github.com/repos/pidydx/grr/subscribers","subscription_url":"https://api.github.com/repos/pidydx/grr/subscription","commits_url":"https://api.github.com/repos/pidydx/grr/commits{/sha}","git_commits_url":"https://api.github.com/repos/pidydx/grr/git/commits{/sha}","comments_url":"https://api.github.com/repos/pidydx/grr/comments{/number}","issue_comment_url":"https://api.github.com/repos/pidydx/grr/issues/comments/{number}","contents_url":"https://api.github.com/repos/pidydx/grr/contents/{+path}","compare_url":"https://api.github.com/repos/pidydx/grr/compare/{base}...{head}","merges_url":"https://api.github.com/repos/pidydx/grr/merges","archive_url":"https://api.github.com/repos/pidydx/grr/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/pidydx/grr/downloads","issues_url":"https://api.github.com/repos/pidydx/grr/issues{/number}","pulls_url":"https://api.github.com/repos/pidydx/grr/pulls{/number}","milestones_url":"https://api.github.com/repos/pidydx/grr/milestones{/number}","notifications_url":"https://api.github.com/repos/pidydx/grr/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/pidydx/grr/labels{/name}","releases_url":"https://api.github.com/repos/pidydx/grr/releases{/id}","created_at":"2014-10-09T23:02:32Z","updated_at":"2015-01-22T19:11:08Z","pushed_at":"2015-01-30T18:21:51Z","git_url":"git://github.com/pidydx/grr.git","ssh_url":"git@github.com:pidydx/grr.git","clone_url":"https://github.com/pidydx/grr.git","svn_url":"https://github.com/pidydx/grr","homepage":"","size":122626,"stargazers_count":0,"watchers_count":0,"language":"Python","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":true,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"google:master","ref":"master","sha":"20ab52d47acd4da5b0d124d4bfcf52b80a0fb3db","user":{"login":"google","id":1342004,"avatar_url":"https://avatars.githubusercontent.com/u/1342004?v=3","gravatar_id":"","url":"https://api.github.com/users/google","html_url":"https://github.com/google","followers_url":"https://api.github.com/users/google/followers","following_url":"https://api.github.com/users/google/following{/other_user}","gists_url":"https://api.github.com/users/google/gists{/gist_id}","starred_url":"https://api.github.com/users/google/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/google/subscriptions","organizations_url":"https://api.github.com/users/google/orgs","repos_url":"https://api.github.com/users/google/repos","events_url":"https://api.github.com/users/google/events{/privacy}","received_events_url":"https://api.github.com/users/google/received_events","type":"Organization","site_admin":false},"repo":{"id":14909673,"name":"grr","full_name":"google/grr","owner":{"login":"google","id":1342004,"avatar_url":"https://avatars.githubusercontent.com/u/1342004?v=3","gravatar_id":"","url":"https://api.github.com/users/google","html_url":"https://github.com/google","followers_url":"https://api.github.com/users/google/followers","following_url":"https://api.github.com/users/google/following{/other_user}","gists_url":"https://api.github.com/users/google/gists{/gist_id}","starred_url":"https://api.github.com/users/google/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/google/subscriptions","organizations_url":"https://api.github.com/users/google/orgs","repos_url":"https://api.github.com/users/google/repos","events_url":"https://api.github.com/users/google/events{/privacy}","received_events_url":"https://api.github.com/users/google/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/google/grr","description":"GRR Rapid Response: remote live forensics for incident response","fork":false,"url":"https://api.github.com/repos/google/grr","forks_url":"https://api.github.com/repos/google/grr/forks","keys_url":"https://api.github.com/repos/google/grr/keys{/key_id}","collaborators_url":"https://api.github.com/repos/google/grr/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/google/grr/teams","hooks_url":"https://api.github.com/repos/google/grr/hooks","issue_events_url":"https://api.github.com/repos/google/grr/issues/events{/number}","events_url":"https://api.github.com/repos/google/grr/events","assignees_url":"https://api.github.com/repos/google/grr/assignees{/user}","branches_url":"https://api.github.com/repos/google/grr/branches{/branch}","tags_url":"https://api.github.com/repos/google/grr/tags","blobs_url":"https://api.github.com/repos/google/grr/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/google/grr/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/google/grr/git/refs{/sha}","trees_url":"https://api.github.com/repos/google/grr/git/trees{/sha}","statuses_url":"https://api.github.com/repos/google/grr/statuses/{sha}","languages_url":"https://api.github.com/repos/google/grr/languages","stargazers_url":"https://api.github.com/repos/google/grr/stargazers","contributors_url":"https://api.github.com/repos/google/grr/contributors","subscribers_url":"https://api.github.com/repos/google/grr/subscribers","subscription_url":"https://api.github.com/repos/google/grr/subscription","commits_url":"https://api.github.com/repos/google/grr/commits{/sha}","git_commits_url":"https://api.github.com/repos/google/grr/git/commits{/sha}","comments_url":"https://api.github.com/repos/google/grr/comments{/number}","issue_comment_url":"https://api.github.com/repos/google/grr/issues/comments/{number}","contents_url":"https://api.github.com/repos/google/grr/contents/{+path}","compare_url":"https://api.github.com/repos/google/grr/compare/{base}...{head}","merges_url":"https://api.github.com/repos/google/grr/merges","archive_url":"https://api.github.com/repos/google/grr/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/google/grr/downloads","issues_url":"https://api.github.com/repos/google/grr/issues{/number}","pulls_url":"https://api.github.com/repos/google/grr/pulls{/number}","milestones_url":"https://api.github.com/repos/google/grr/milestones{/number}","notifications_url":"https://api.github.com/repos/google/grr/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/google/grr/labels{/name}","releases_url":"https://api.github.com/repos/google/grr/releases{/id}","created_at":"2013-12-04T00:17:53Z","updated_at":"2015-02-11T19:46:11Z","pushed_at":"2015-02-11T19:46:11Z","git_url":"git://github.com/google/grr.git","ssh_url":"git@github.com:google/grr.git","clone_url":"https://github.com/google/grr.git","svn_url":"https://github.com/google/grr","homepage":"","size":124127,"stargazers_count":223,"watchers_count":223,"language":"Python","has_issues":true,"has_downloads":true,"has_wiki":false,"has_pages":true,"forks_count":44,"mirror_url":null,"open_issues_count":36,"forks":44,"open_issues":36,"watchers":223,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/google/grr/pulls/96"},"html":{"href":"https://github.com/google/grr/pull/96"},"issue":{"href":"https://api.github.com/repos/google/grr/issues/96"},"comments":{"href":"https://api.github.com/repos/google/grr/issues/96/comments"},"review_comments":{"href":"https://api.github.com/repos/google/grr/pulls/96/comments"},"review_comment":{"href":"https://api.github.com/repos/google/grr/pulls/comments/{number}"},"commits":{"href":"https://api.github.com/repos/google/grr/pulls/96/commits"},"statuses":{"href":"https://api.github.com/repos/google/grr/statuses/0ddcd58d361f472b88052f692b213a10d0a66b6d"}}}}