Support PostGre/mysql backend

We should finish the work I started in git branch mikeperry/postgre-bwscanner to rid us of the possibly memleaky and poorly performing sqlite backend. The problem is that for some reason the postgre backend produces incorrect results, and mysql doesn't support the queries that sqlalchemy generates for us. Perhaps this will improve in 0.6.x?

added component::core tor/torflow owner::mikeperry priority::medium resolution::fixed status::closed type::enhancement labels

Trac:
Points: N/A to N/A
Cc: N/A to aagbsn@extc.org
Actualpoints: N/A to N/A

This fix lived here https://github.com/aagbsn/torflow/tree/ticket2392. It has since been merged into master. The changes consist of:

1. rewriting queries so that UPDATE target and source tables are not the same (this is achieved by using nested SELECT which cause MySQL to generate temporary tables)

2. rewriting queries to avoid divide-by-zeros.

3. adding a new configuration parameter, db_url, to bwauthority.cfg which specifies the database type:

if db_url is unset bwauthority will default to sqlite

#db_url = #db_url = mysql+mysqldb://bwscanner:password@127.0.0.1/BwScan1 db_url = postgresql://bwscanner:password@127.0.0.1/BwScan1

Trac:
Status: new to needs_review

Roger, how is your postgres scanner working?

Trac:
Cc: aagbsn@extc.org to aagbsn@extc.org, arma

note: Roger ran into a table error with older (sqlalchemy 0.5.x, elixir 0.6.x) using the Postgres backend. Upgrading to (latest at time of this comment) sqlalchemy 0.7.2 and elixir 0.7.1 resolved this issue.

I ran it for a while. The bwauth parents are still going. I mailed the bw.log files to Mike. They have some tracebacks in them. They each stop at different times:

$ date
Thu Aug 25 00:46:25 EDT 2011
$ ls -la scanner.?/bw.log
-rw-r--r-- 1 bwauth bwauth  47047146 2011-08-21 17:18 scanner.1/bw.log
-rw-r--r-- 1 bwauth bwauth   2007759 2011-08-19 22:15 scanner.2/bw.log
-rw-r--r-- 1 bwauth bwauth 175908437 2011-08-24 09:37 scanner.3/bw.log
-rw-r--r-- 1 bwauth bwauth 192325325 2011-08-24 15:03 scanner.4/bw.log

Scanner number 2 looks particularly sad.

resolved in #3834 (moved)

Trac:
Resolution: N/A to fixed
Status: needs_review to closed

closed

mentioned in issue #2393 (moved)

moved to tpo/network-health/torflow#2392 (closed)

mentioned in issue tpo/network-health/torflow#2393 (closed)