We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
MySQL error handling
I have created several honeypots for other protocols (Android Debug Bridge, Citrix Netscaler, Elasticsearch, Internet Printing Protocol). In all of them I use Cowrie's idea of output plugins. However, the attack traffic for these protocols is much much lower than for Telnet/SSH. Probably for this reason, the MySQL plugin often fails with the dreaded "(2006, 'MySQL server has gone away')" error. (Yes, sometimes more than 8 hours can pass between attacks.)

What bothered me is that the plugin failed with a stack trace. This isn't supposed to happen, because it intercepts this error just like Cowrie's MySQL plugin and tries to re-establish the connection. Then I noticed that the error that is being reported is not `MySQLdb.OperationalError`, as the code intercepts. Instead, it is `MySQLdb._exceptions.OperationalError`.

And, indeed, linting the code complains that the `MySLdb` module does not have a member named `OperationalError`. Similarly, there is no `MySQLdb.Error` - but there seems to be `MySQLdb._exceptons.Error` instead.

Shouldn't these be changed in Cowrie's MySQL module?

Also, from the [documentation of MySQLdb](https://mysqlclient.readthedocs.io/FAQ.html#my-data-disappeared-or-won-t-go-away):

> Starting with 1.2.0, MySQLdb disables autocommit by default, as required by the DB-API standard (PEP-249). If you are using InnoDB tables or some other type of transactional table type, you’ll need to do connection.commit() before closing the connection, or else none of your changes will be written to the database.

Shouldn't the `stop()` method of Cowrie's MySQL plugin issue a `self.db.commit()` before closing the connection? Or maybe set `self.db.autocommit(True)` when opening it?

--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `src/cowrie/output/mysql.py`
Content:
```
1 """
2 MySQL output connector. Writes audit logs to MySQL database
3 """
4 
5 from __future__ import absolute_import, division
6 
7 import MySQLdb
8 
9 from twisted.enterprise import adbapi
10 from twisted.internet import defer
11 from twisted.python import log
12 
13 import cowrie.core.output
14 from cowrie.core.config import CowrieConfig
15 
16 
17 class ReconnectingConnectionPool(adbapi.ConnectionPool):
18     """
19     Reconnecting adbapi connection pool for MySQL.
20 
21     This class improves on the solution posted at
22     http://www.gelens.org/2008/09/12/reinitializing-twisted-connectionpool/
23     by checking exceptions by error code and only disconnecting the current
24     connection instead of all of them.
25 
26     Also see:
27     http://twistedmatrix.com/pipermail/twisted-python/2009-July/020007.html
28     """
29 
30     def _runInteraction(self, interaction, *args, **kw):
31         try:
32             return adbapi.ConnectionPool._runInteraction(
33                 self, interaction, *args, **kw)
34         except MySQLdb.OperationalError as e:
35             if e.args[0] not in (2003, 2006, 2013):
36                 raise e
37             log.msg("RCP: got error {0}, retrying operation".format(e))
38             conn = self.connections.get(self.threadID())
39             self.disconnect(conn)
40             # Try the interaction again
41             return adbapi.ConnectionPool._runInteraction(
42                 self, interaction, *args, **kw)
43 
44 
45 class Output(cowrie.core.output.Output):
46     """
47     mysql output
48     """
49     db = None
50 
51     def start(self):
52         self.debug = CowrieConfig().getboolean('output_mysql', 'debug', fallback=False)
53         port = CowrieConfig().getint('output_mysql', 'port', fallback=3306)
54         try:
55             self.db = ReconnectingConnectionPool(
56                 'MySQLdb',
57                 host=CowrieConfig().get('output_mysql', 'host'),
58                 db=CowrieConfig().get('output_mysql', 'database'),
59                 user=CowrieConfig().get('output_mysql', 'username'),
60                 passwd=CowrieConfig().get('output_mysql', 'password', raw=True),
61                 port=port,
62                 cp_min=1,
63                 cp_max=1,
64                 charset='utf8mb4',
65                 cp_reconnect=True,
66                 use_unicode=True
67             )
68         except MySQLdb.Error as e:
69             log.msg("output_mysql: Error %d: %s" % (e.args[0], e.args[1]))
70 
71     def stop(self):
72         self.db.close()
73 
74     def sqlerror(self, error):
75         """
76         1146, "Table '...' doesn't exist"
77         1406, "Data too long for column '...' at row ..."
78         """
79         if error.value[0] in (1146, 1406):
80             log.msg("output_mysql: MySQL Error: {}".format(error.value))
81             log.msg("MySQL schema maybe misconfigured, doublecheck database!")
82         else:
83             log.err("output_mysql: MySQL Error: {}".format(error.value))
84 
85     def simpleQuery(self, sql, args):
86         """
87         Just run a deferred sql query, only care about errors
88         """
89         if self.debug:
90             log.msg("output_mysql: MySQL query: {} {}".format(sql, repr(args)))
91         d = self.db.runQuery(sql, args)
92         d.addErrback(self.sqlerror)
93 
94     @defer.inlineCallbacks
95     def write(self, entry):
96         if entry["eventid"] == 'cowrie.session.connect':
97             r = yield self.db.runQuery(
98                 "SELECT `id`"
99                 "FROM `sensors`"
100                 "WHERE `ip` = %s",
101                 (self.sensor,))
102 
103             if r:
104                 sensorid = r[0][0]
105             else:
106                 yield self.db.runQuery(
107                     'INSERT INTO `sensors` (`ip`) '
108                     'VALUES (%s)',
109                     (self.sensor,))
110 
111                 r = yield self.db.runQuery('SELECT LAST_INSERT_ID()')
112                 sensorid = int(r[0][0])
113             self.simpleQuery(
114                 "INSERT INTO `sessions` (`id`, `starttime`, `sensor`, `ip`) "
115                 "VALUES (%s, FROM_UNIXTIME(%s), %s, %s)",
116                 (entry["session"], entry["time"], sensorid, entry["src_ip"]))
117 
118         elif entry["eventid"] == 'cowrie.login.success':
119             self.simpleQuery('INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) '
120                              'VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))',
121                              (entry["session"], 1, entry['username'], entry['password'], entry["time"]))
122 
123         elif entry["eventid"] == 'cowrie.login.failed':
124             self.simpleQuery('INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) '
125                              'VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))',
126                              (entry["session"], 0, entry['username'], entry['password'], entry["time"]))
127 
128         elif entry["eventid"] == 'cowrie.session.params':
129             self.simpleQuery('INSERT INTO `params` (`session`, `arch`) '
130                              'VALUES (%s, %s)',
131                              (entry["session"], entry["arch"]))
132 
133         elif entry["eventid"] == 'cowrie.command.input':
134             self.simpleQuery('INSERT INTO `input` (`session`, `timestamp`, `success`, `input`) '
135                              'VALUES (%s, FROM_UNIXTIME(%s), %s , %s)',
136                              (entry["session"], entry["time"], 1, entry["input"]))
137 
138         elif entry["eventid"] == 'cowrie.command.failed':
139             self.simpleQuery('INSERT INTO `input` (`session`, `timestamp`, `success`, `input`) '
140                              'VALUES (%s, FROM_UNIXTIME(%s), %s , %s)',
141                              (entry["session"], entry["time"], 0, entry["input"]))
142 
143         elif entry["eventid"] == 'cowrie.session.file_download':
144             self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '
145                              'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',
146                              (entry["session"], entry["time"], entry['url'], entry['outfile'], entry['shasum']))
147 
148         elif entry["eventid"] == 'cowrie.session.file_download.failed':
149             self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '
150                              'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',
151                              (entry["session"], entry["time"], entry['url'], 'NULL', 'NULL'))
152 
153         elif entry["eventid"] == 'cowrie.session.file_upload':
154             self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '
155                              'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',
156                              (entry["session"], entry["time"], '', entry['outfile'], entry['shasum']))
157 
158         elif entry["eventid"] == 'cowrie.session.input':
159             self.simpleQuery('INSERT INTO `input` (`session`, `timestamp`, `realm`, `input`) '
160                              'VALUES (%s, FROM_UNIXTIME(%s), %s , %s)',
161                              (entry["session"], entry["time"], entry["realm"], entry["input"]))
162 
163         elif entry["eventid"] == 'cowrie.client.version':
164             r = yield self.db.runQuery(
165                 'SELECT `id` FROM `clients` '
166                 'WHERE `version` = %s',
167                 (entry['version'],))
168 
169             if r:
170                 id = int(r[0][0])
171             else:
172                 yield self.db.runQuery(
173                     'INSERT INTO `clients` (`version`) '
174                     'VALUES (%s)',
175                     (entry['version'],))
176 
177                 r = yield self.db.runQuery('SELECT LAST_INSERT_ID()')
178                 id = int(r[0][0])
179             self.simpleQuery(
180                 'UPDATE `sessions` '
181                 'SET `client` = %s '
182                 'WHERE `id` = %s',
183                 (id, entry["session"]))
184 
185         elif entry["eventid"] == 'cowrie.client.size':
186             self.simpleQuery(
187                 'UPDATE `sessions` '
188                 'SET `termsize` = %s '
189                 'WHERE `id` = %s',
190                 ('%sx%s' % (entry['width'], entry['height']), entry["session"]))
191 
192         elif entry["eventid"] == 'cowrie.session.closed':
193             self.simpleQuery(
194                 'UPDATE `sessions` '
195                 'SET `endtime` = FROM_UNIXTIME(%s) '
196                 'WHERE `id` = %s',
197                 (entry["time"], entry["session"]))
198 
199         elif entry["eventid"] == 'cowrie.log.closed':
200             self.simpleQuery(
201                 'INSERT INTO `ttylog` (`session`, `ttylog`, `size`) '
202                 'VALUES (%s, %s, %s)',
203                 (entry["session"], entry["ttylog"], entry["size"]))
204 
205         elif entry["eventid"] == 'cowrie.client.fingerprint':
206             self.simpleQuery(
207                 'INSERT INTO `keyfingerprints` (`session`, `username`, `fingerprint`) '
208                 'VALUES (%s, %s, %s)',
209                 (entry["session"], entry["username"], entry["fingerprint"]))
210 
211         elif entry["eventid"] == 'cowrie.direct-tcpip.request':
212             self.simpleQuery(
213                 'INSERT INTO `ipforwards` (`session`, `timestamp`, `dst_ip`, `dst_port`) '
214                 'VALUES (%s, FROM_UNIXTIME(%s), %s, %s)',
215                 (entry["session"], entry["time"], entry["dst_ip"], entry["dst_port"]))
216 
217         elif entry["eventid"] == 'cowrie.direct-tcpip.data':
218             self.simpleQuery(
219                 'INSERT INTO `ipforwardsdata` (`session`, `timestamp`, `dst_ip`, `dst_port`, `data`) '
220                 'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',
221                 (entry["session"], entry["time"], entry["dst_ip"], entry["dst_port"], entry["data"]))
222
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/src/cowrie/output/mysql.py b/src/cowrie/output/mysql.py
--- a/src/cowrie/output/mysql.py
+++ b/src/cowrie/output/mysql.py
@@ -31,7 +31,7 @@
         try:
             return adbapi.ConnectionPool._runInteraction(
                 self, interaction, *args, **kw)
-        except MySQLdb.OperationalError as e:
+        except (MySQLdb.OperationalError, MySQLdb._exceptions.OperationalError) as e:
             if e.args[0] not in (2003, 2006, 2013):
                 raise e
             log.msg("RCP: got error {0}, retrying operation".format(e))
@@ -65,10 +65,11 @@
                 cp_reconnect=True,
                 use_unicode=True
             )
-        except MySQLdb.Error as e:
+        except (MySQLdb.Error, MySQLdb._exceptons.Error) as e:
             log.msg("output_mysql: Error %d: %s" % (e.args[0], e.args[1]))
 
     def stop(self):
+        self.db.commit()
         self.db.close()
 
     def sqlerror(self, error):

{"golden_diff": "diff --git a/src/cowrie/output/mysql.py b/src/cowrie/output/mysql.py\n--- a/src/cowrie/output/mysql.py\n+++ b/src/cowrie/output/mysql.py\n@@ -31,7 +31,7 @@\n         try:\n             return adbapi.ConnectionPool._runInteraction(\n                 self, interaction, *args, **kw)\n-        except MySQLdb.OperationalError as e:\n+        except (MySQLdb.OperationalError, MySQLdb._exceptions.OperationalError) as e:\n             if e.args[0] not in (2003, 2006, 2013):\n                 raise e\n             log.msg(\"RCP: got error {0}, retrying operation\".format(e))\n@@ -65,10 +65,11 @@\n                 cp_reconnect=True,\n                 use_unicode=True\n             )\n-        except MySQLdb.Error as e:\n+        except (MySQLdb.Error, MySQLdb._exceptons.Error) as e:\n             log.msg(\"output_mysql: Error %d: %s\" % (e.args[0], e.args[1]))\n \n     def stop(self):\n+        self.db.commit()\n         self.db.close()\n \n     def sqlerror(self, error):\n", "issue": "MySQL error handling\nI have created several honeypots for other protocols (Android Debug Bridge, Citrix Netscaler, Elasticsearch, Internet Printing Protocol). In all of them I use Cowrie's idea of output plugins. However, the attack traffic for these protocols is much much lower than for Telnet/SSH. Probably for this reason, the MySQL plugin often fails with the dreaded \"(2006, 'MySQL server has gone away')\" error. (Yes, sometimes more than 8 hours can pass between attacks.)\r\n\r\nWhat bothered me is that the plugin failed with a stack trace. This isn't supposed to happen, because it intercepts this error just like Cowrie's MySQL plugin and tries to re-establish the connection. Then I noticed that the error that is being reported is not `MySQLdb.OperationalError`, as the code intercepts. Instead, it is `MySQLdb._exceptions.OperationalError`.\r\n\r\nAnd, indeed, linting the code complains that the `MySLdb` module does not have a member named `OperationalError`. Similarly, there is no `MySQLdb.Error` - but there seems to be `MySQLdb._exceptons.Error` instead.\r\n\r\nShouldn't these be changed in Cowrie's MySQL module?\r\n\r\nAlso, from the [documentation of MySQLdb](https://mysqlclient.readthedocs.io/FAQ.html#my-data-disappeared-or-won-t-go-away):\r\n\r\n> Starting with 1.2.0, MySQLdb disables autocommit by default, as required by the DB-API standard (PEP-249). If you are using InnoDB tables or some other type of transactional table type, you\u2019ll need to do connection.commit() before closing the connection, or else none of your changes will be written to the database.\r\n\r\nShouldn't the `stop()` method of Cowrie's MySQL plugin issue a `self.db.commit()` before closing the connection? Or maybe set `self.db.autocommit(True)` when opening it?\n", "before_files": [{"content": "\"\"\"\nMySQL output connector. Writes audit logs to MySQL database\n\"\"\"\n\nfrom __future__ import absolute_import, division\n\nimport MySQLdb\n\nfrom twisted.enterprise import adbapi\nfrom twisted.internet import defer\nfrom twisted.python import log\n\nimport cowrie.core.output\nfrom cowrie.core.config import CowrieConfig\n\n\nclass ReconnectingConnectionPool(adbapi.ConnectionPool):\n    \"\"\"\n    Reconnecting adbapi connection pool for MySQL.\n\n    This class improves on the solution posted at\n    http://www.gelens.org/2008/09/12/reinitializing-twisted-connectionpool/\n    by checking exceptions by error code and only disconnecting the current\n    connection instead of all of them.\n\n    Also see:\n    http://twistedmatrix.com/pipermail/twisted-python/2009-July/020007.html\n    \"\"\"\n\n    def _runInteraction(self, interaction, *args, **kw):\n        try:\n            return adbapi.ConnectionPool._runInteraction(\n                self, interaction, *args, **kw)\n        except MySQLdb.OperationalError as e:\n            if e.args[0] not in (2003, 2006, 2013):\n                raise e\n            log.msg(\"RCP: got error {0}, retrying operation\".format(e))\n            conn = self.connections.get(self.threadID())\n            self.disconnect(conn)\n            # Try the interaction again\n            return adbapi.ConnectionPool._runInteraction(\n                self, interaction, *args, **kw)\n\n\nclass Output(cowrie.core.output.Output):\n    \"\"\"\n    mysql output\n    \"\"\"\n    db = None\n\n    def start(self):\n        self.debug = CowrieConfig().getboolean('output_mysql', 'debug', fallback=False)\n        port = CowrieConfig().getint('output_mysql', 'port', fallback=3306)\n        try:\n            self.db = ReconnectingConnectionPool(\n                'MySQLdb',\n                host=CowrieConfig().get('output_mysql', 'host'),\n                db=CowrieConfig().get('output_mysql', 'database'),\n                user=CowrieConfig().get('output_mysql', 'username'),\n                passwd=CowrieConfig().get('output_mysql', 'password', raw=True),\n                port=port,\n                cp_min=1,\n                cp_max=1,\n                charset='utf8mb4',\n                cp_reconnect=True,\n                use_unicode=True\n            )\n        except MySQLdb.Error as e:\n            log.msg(\"output_mysql: Error %d: %s\" % (e.args[0], e.args[1]))\n\n    def stop(self):\n        self.db.close()\n\n    def sqlerror(self, error):\n        \"\"\"\n        1146, \"Table '...' doesn't exist\"\n        1406, \"Data too long for column '...' at row ...\"\n        \"\"\"\n        if error.value[0] in (1146, 1406):\n            log.msg(\"output_mysql: MySQL Error: {}\".format(error.value))\n            log.msg(\"MySQL schema maybe misconfigured, doublecheck database!\")\n        else:\n            log.err(\"output_mysql: MySQL Error: {}\".format(error.value))\n\n    def simpleQuery(self, sql, args):\n        \"\"\"\n        Just run a deferred sql query, only care about errors\n        \"\"\"\n        if self.debug:\n            log.msg(\"output_mysql: MySQL query: {} {}\".format(sql, repr(args)))\n        d = self.db.runQuery(sql, args)\n        d.addErrback(self.sqlerror)\n\n    @defer.inlineCallbacks\n    def write(self, entry):\n        if entry[\"eventid\"] == 'cowrie.session.connect':\n            r = yield self.db.runQuery(\n                \"SELECT `id`\"\n                \"FROM `sensors`\"\n                \"WHERE `ip` = %s\",\n                (self.sensor,))\n\n            if r:\n                sensorid = r[0][0]\n            else:\n                yield self.db.runQuery(\n                    'INSERT INTO `sensors` (`ip`) '\n                    'VALUES (%s)',\n                    (self.sensor,))\n\n                r = yield self.db.runQuery('SELECT LAST_INSERT_ID()')\n                sensorid = int(r[0][0])\n            self.simpleQuery(\n                \"INSERT INTO `sessions` (`id`, `starttime`, `sensor`, `ip`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s, %s)\",\n                (entry[\"session\"], entry[\"time\"], sensorid, entry[\"src_ip\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.login.success':\n            self.simpleQuery('INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) '\n                             'VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))',\n                             (entry[\"session\"], 1, entry['username'], entry['password'], entry[\"time\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.login.failed':\n            self.simpleQuery('INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) '\n                             'VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))',\n                             (entry[\"session\"], 0, entry['username'], entry['password'], entry[\"time\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.session.params':\n            self.simpleQuery('INSERT INTO `params` (`session`, `arch`) '\n                             'VALUES (%s, %s)',\n                             (entry[\"session\"], entry[\"arch\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.command.input':\n            self.simpleQuery('INSERT INTO `input` (`session`, `timestamp`, `success`, `input`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s , %s)',\n                             (entry[\"session\"], entry[\"time\"], 1, entry[\"input\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.command.failed':\n            self.simpleQuery('INSERT INTO `input` (`session`, `timestamp`, `success`, `input`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s , %s)',\n                             (entry[\"session\"], entry[\"time\"], 0, entry[\"input\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.session.file_download':\n            self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',\n                             (entry[\"session\"], entry[\"time\"], entry['url'], entry['outfile'], entry['shasum']))\n\n        elif entry[\"eventid\"] == 'cowrie.session.file_download.failed':\n            self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',\n                             (entry[\"session\"], entry[\"time\"], entry['url'], 'NULL', 'NULL'))\n\n        elif entry[\"eventid\"] == 'cowrie.session.file_upload':\n            self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',\n                             (entry[\"session\"], entry[\"time\"], '', entry['outfile'], entry['shasum']))\n\n        elif entry[\"eventid\"] == 'cowrie.session.input':\n            self.simpleQuery('INSERT INTO `input` (`session`, `timestamp`, `realm`, `input`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s , %s)',\n                             (entry[\"session\"], entry[\"time\"], entry[\"realm\"], entry[\"input\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.client.version':\n            r = yield self.db.runQuery(\n                'SELECT `id` FROM `clients` '\n                'WHERE `version` = %s',\n                (entry['version'],))\n\n            if r:\n                id = int(r[0][0])\n            else:\n                yield self.db.runQuery(\n                    'INSERT INTO `clients` (`version`) '\n                    'VALUES (%s)',\n                    (entry['version'],))\n\n                r = yield self.db.runQuery('SELECT LAST_INSERT_ID()')\n                id = int(r[0][0])\n            self.simpleQuery(\n                'UPDATE `sessions` '\n                'SET `client` = %s '\n                'WHERE `id` = %s',\n                (id, entry[\"session\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.client.size':\n            self.simpleQuery(\n                'UPDATE `sessions` '\n                'SET `termsize` = %s '\n                'WHERE `id` = %s',\n                ('%sx%s' % (entry['width'], entry['height']), entry[\"session\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.session.closed':\n            self.simpleQuery(\n                'UPDATE `sessions` '\n                'SET `endtime` = FROM_UNIXTIME(%s) '\n                'WHERE `id` = %s',\n                (entry[\"time\"], entry[\"session\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.log.closed':\n            self.simpleQuery(\n                'INSERT INTO `ttylog` (`session`, `ttylog`, `size`) '\n                'VALUES (%s, %s, %s)',\n                (entry[\"session\"], entry[\"ttylog\"], entry[\"size\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.client.fingerprint':\n            self.simpleQuery(\n                'INSERT INTO `keyfingerprints` (`session`, `username`, `fingerprint`) '\n                'VALUES (%s, %s, %s)',\n                (entry[\"session\"], entry[\"username\"], entry[\"fingerprint\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.direct-tcpip.request':\n            self.simpleQuery(\n                'INSERT INTO `ipforwards` (`session`, `timestamp`, `dst_ip`, `dst_port`) '\n                'VALUES (%s, FROM_UNIXTIME(%s), %s, %s)',\n                (entry[\"session\"], entry[\"time\"], entry[\"dst_ip\"], entry[\"dst_port\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.direct-tcpip.data':\n            self.simpleQuery(\n                'INSERT INTO `ipforwardsdata` (`session`, `timestamp`, `dst_ip`, `dst_port`, `data`) '\n                'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',\n                (entry[\"session\"], entry[\"time\"], entry[\"dst_ip\"], entry[\"dst_port\"], entry[\"data\"]))\n", "path": "src/cowrie/output/mysql.py"}], "after_files": [{"content": "\"\"\"\nMySQL output connector. Writes audit logs to MySQL database\n\"\"\"\n\nfrom __future__ import absolute_import, division\n\nimport MySQLdb\n\nfrom twisted.enterprise import adbapi\nfrom twisted.internet import defer\nfrom twisted.python import log\n\nimport cowrie.core.output\nfrom cowrie.core.config import CowrieConfig\n\n\nclass ReconnectingConnectionPool(adbapi.ConnectionPool):\n    \"\"\"\n    Reconnecting adbapi connection pool for MySQL.\n\n    This class improves on the solution posted at\n    http://www.gelens.org/2008/09/12/reinitializing-twisted-connectionpool/\n    by checking exceptions by error code and only disconnecting the current\n    connection instead of all of them.\n\n    Also see:\n    http://twistedmatrix.com/pipermail/twisted-python/2009-July/020007.html\n    \"\"\"\n\n    def _runInteraction(self, interaction, *args, **kw):\n        try:\n            return adbapi.ConnectionPool._runInteraction(\n                self, interaction, *args, **kw)\n        except (MySQLdb.OperationalError, MySQLdb._exceptions.OperationalError) as e:\n            if e.args[0] not in (2003, 2006, 2013):\n                raise e\n            log.msg(\"RCP: got error {0}, retrying operation\".format(e))\n            conn = self.connections.get(self.threadID())\n            self.disconnect(conn)\n            # Try the interaction again\n            return adbapi.ConnectionPool._runInteraction(\n                self, interaction, *args, **kw)\n\n\nclass Output(cowrie.core.output.Output):\n    \"\"\"\n    mysql output\n    \"\"\"\n    db = None\n\n    def start(self):\n        self.debug = CowrieConfig().getboolean('output_mysql', 'debug', fallback=False)\n        port = CowrieConfig().getint('output_mysql', 'port', fallback=3306)\n        try:\n            self.db = ReconnectingConnectionPool(\n                'MySQLdb',\n                host=CowrieConfig().get('output_mysql', 'host'),\n                db=CowrieConfig().get('output_mysql', 'database'),\n                user=CowrieConfig().get('output_mysql', 'username'),\n                passwd=CowrieConfig().get('output_mysql', 'password', raw=True),\n                port=port,\n                cp_min=1,\n                cp_max=1,\n                charset='utf8mb4',\n                cp_reconnect=True,\n                use_unicode=True\n            )\n        except (MySQLdb.Error, MySQLdb._exceptons.Error) as e:\n            log.msg(\"output_mysql: Error %d: %s\" % (e.args[0], e.args[1]))\n\n    def stop(self):\n        self.db.commit()\n        self.db.close()\n\n    def sqlerror(self, error):\n        \"\"\"\n        1146, \"Table '...' doesn't exist\"\n        1406, \"Data too long for column '...' at row ...\"\n        \"\"\"\n        if error.value[0] in (1146, 1406):\n            log.msg(\"output_mysql: MySQL Error: {}\".format(error.value))\n            log.msg(\"MySQL schema maybe misconfigured, doublecheck database!\")\n        else:\n            log.err(\"output_mysql: MySQL Error: {}\".format(error.value))\n\n    def simpleQuery(self, sql, args):\n        \"\"\"\n        Just run a deferred sql query, only care about errors\n        \"\"\"\n        if self.debug:\n            log.msg(\"output_mysql: MySQL query: {} {}\".format(sql, repr(args)))\n        d = self.db.runQuery(sql, args)\n        d.addErrback(self.sqlerror)\n\n    @defer.inlineCallbacks\n    def write(self, entry):\n        if entry[\"eventid\"] == 'cowrie.session.connect':\n            r = yield self.db.runQuery(\n                \"SELECT `id`\"\n                \"FROM `sensors`\"\n                \"WHERE `ip` = %s\",\n                (self.sensor,))\n\n            if r:\n                sensorid = r[0][0]\n            else:\n                yield self.db.runQuery(\n                    'INSERT INTO `sensors` (`ip`) '\n                    'VALUES (%s)',\n                    (self.sensor,))\n\n                r = yield self.db.runQuery('SELECT LAST_INSERT_ID()')\n                sensorid = int(r[0][0])\n            self.simpleQuery(\n                \"INSERT INTO `sessions` (`id`, `starttime`, `sensor`, `ip`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s, %s)\",\n                (entry[\"session\"], entry[\"time\"], sensorid, entry[\"src_ip\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.login.success':\n            self.simpleQuery('INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) '\n                             'VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))',\n                             (entry[\"session\"], 1, entry['username'], entry['password'], entry[\"time\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.login.failed':\n            self.simpleQuery('INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) '\n                             'VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))',\n                             (entry[\"session\"], 0, entry['username'], entry['password'], entry[\"time\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.session.params':\n            self.simpleQuery('INSERT INTO `params` (`session`, `arch`) '\n                             'VALUES (%s, %s)',\n                             (entry[\"session\"], entry[\"arch\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.command.input':\n            self.simpleQuery('INSERT INTO `input` (`session`, `timestamp`, `success`, `input`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s , %s)',\n                             (entry[\"session\"], entry[\"time\"], 1, entry[\"input\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.command.failed':\n            self.simpleQuery('INSERT INTO `input` (`session`, `timestamp`, `success`, `input`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s , %s)',\n                             (entry[\"session\"], entry[\"time\"], 0, entry[\"input\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.session.file_download':\n            self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',\n                             (entry[\"session\"], entry[\"time\"], entry['url'], entry['outfile'], entry['shasum']))\n\n        elif entry[\"eventid\"] == 'cowrie.session.file_download.failed':\n            self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',\n                             (entry[\"session\"], entry[\"time\"], entry['url'], 'NULL', 'NULL'))\n\n        elif entry[\"eventid\"] == 'cowrie.session.file_upload':\n            self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',\n                             (entry[\"session\"], entry[\"time\"], '', entry['outfile'], entry['shasum']))\n\n        elif entry[\"eventid\"] == 'cowrie.session.input':\n            self.simpleQuery('INSERT INTO `input` (`session`, `timestamp`, `realm`, `input`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s , %s)',\n                             (entry[\"session\"], entry[\"time\"], entry[\"realm\"], entry[\"input\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.client.version':\n            r = yield self.db.runQuery(\n                'SELECT `id` FROM `clients` '\n                'WHERE `version` = %s',\n                (entry['version'],))\n\n            if r:\n                id = int(r[0][0])\n            else:\n                yield self.db.runQuery(\n                    'INSERT INTO `clients` (`version`) '\n                    'VALUES (%s)',\n                    (entry['version'],))\n\n                r = yield self.db.runQuery('SELECT LAST_INSERT_ID()')\n                id = int(r[0][0])\n            self.simpleQuery(\n                'UPDATE `sessions` '\n                'SET `client` = %s '\n                'WHERE `id` = %s',\n                (id, entry[\"session\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.client.size':\n            self.simpleQuery(\n                'UPDATE `sessions` '\n                'SET `termsize` = %s '\n                'WHERE `id` = %s',\n                ('%sx%s' % (entry['width'], entry['height']), entry[\"session\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.session.closed':\n            self.simpleQuery(\n                'UPDATE `sessions` '\n                'SET `endtime` = FROM_UNIXTIME(%s) '\n                'WHERE `id` = %s',\n                (entry[\"time\"], entry[\"session\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.log.closed':\n            self.simpleQuery(\n                'INSERT INTO `ttylog` (`session`, `ttylog`, `size`) '\n                'VALUES (%s, %s, %s)',\n                (entry[\"session\"], entry[\"ttylog\"], entry[\"size\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.client.fingerprint':\n            self.simpleQuery(\n                'INSERT INTO `keyfingerprints` (`session`, `username`, `fingerprint`) '\n                'VALUES (%s, %s, %s)',\n                (entry[\"session\"], entry[\"username\"], entry[\"fingerprint\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.direct-tcpip.request':\n            self.simpleQuery(\n                'INSERT INTO `ipforwards` (`session`, `timestamp`, `dst_ip`, `dst_port`) '\n                'VALUES (%s, FROM_UNIXTIME(%s), %s, %s)',\n                (entry[\"session\"], entry[\"time\"], entry[\"dst_ip\"], entry[\"dst_port\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.direct-tcpip.data':\n            self.simpleQuery(\n                'INSERT INTO `ipforwardsdata` (`session`, `timestamp`, `dst_ip`, `dst_port`, `data`) '\n                'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',\n                (entry[\"session\"], entry[\"time\"], entry[\"dst_ip\"], entry[\"dst_port\"], entry[\"data\"]))\n", "path": "src/cowrie/output/mysql.py"}]}