Snapshots plugin not compatible with postgres

[davisj <jake@…>]

Using bcfg2-server 1.0 rc1 with postgresql 8.3 - initializing the snapshots DB succeeds without error but I get tracebacks on the server when a client connects or when I attempt to run snapshot reports. The same process works without error if I use the sqlite driver. Here is the output on the server when the client connects.

# bcfg2-server -d
Initialized git plugin with git directory = /var/lib/bcfg2/.git
Loading experimental plugin(s): Snapshots GroupPatterns
NOTE: Interfaces subject to change
service available at https://chinstrap:6789
serving bcfg2-server at https://chinstrap:6789
serve_forever() [start]
Handled 23 events in 0.011s
Handled 9 events in 0.001s
Handled 24 events in 0.022s
Handled 10 events in 0.002s
Asserting client chinstrap.company.com profile to common
Creating new client: chinstrap.company.com, profile common
Generated config for chinstrap.company.com in 0.002s
Client chinstrap.company.com reported state dirty
Exception in thread Thread-1:
Traceback (most recent call last):
  File "/usr/lib/python2.6/threading.py", line 525, in __bootstrap_inner
    self.run()
  File "/usr/lib/python2.6/threading.py", line 477, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/usr/lib/python2.6/dist-packages/Bcfg2/Server/Plugins/Snapshots.py", line 70, in load_snapshot
    self.statistics_from_old_stats(metadata, data)
  File "/usr/lib/python2.6/dist-packages/Bcfg2/Server/Plugins/Snapshots.py", line 116, in statistics_from_old_stats
    metadata, entries, extra)
  File "/usr/lib/python2.6/dist-packages/Bcfg2/Server/Snapshots/model.py", line 260, in from_data
    getattr(snap, dest).append(ecls.from_record(session, edata))
  File "/usr/lib/python2.6/dist-packages/Bcfg2/Server/Snapshots/model.py", line 24, in from_record
    return cls.by_value(session, **data)
  File "/usr/lib/python2.6/dist-packages/Bcfg2/Server/Snapshots/model.py", line 16, in by_value
    return session.query(cls).filter_by(**kwargs).one()
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py", line 1321, in one
    ret = list(self[0:2])
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py", line 1221, in __getitem__
    return list(res)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py", line 1361, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py", line 1364, in _execute_and_instances
    result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none())
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/session.py", line 755, in execute
    clause, params or {})
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py", line 824, in execute
    return Connection.executors[c](self, object, multiparams, params)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py", line 874, in _execute_clauseelement
    return self.__execute_context(context)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py", line 896, in __execute_context
    self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py", line 950, in _cursor_execute
    self._handle_dbapi_exception(e, statement, parameters, cursor, context)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception
    raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect)
DataError: (DataError) invalid input syntax for type boolean: "None"
 'SELECT service.id AS service_id, service.name AS service_name, service.type AS service_type, service.status AS service_status \nFROM service \nWHERE service.status = %(status_1)s AND service.type = %(type_1)s AND service.name = %(name_1)s \n LIMIT 2 OFFSET 0' {'type_1': 'deb', 'name_1': 'ondemand', 'status_1': u'None'}

And here's the output on the client during the same operation.

# bcfg2 -qvn
No ca is specified. Cannot authenticate the server with SSL.
No ca is specified. Cannot authenticate the server with SSL.
Loaded tool drivers:
 APT      Action   DebInit  POSIX
Cannot verify incomplete ConfigFile /etc/resolv.conf

Phase: initial
Correct entries:        0
Incorrect entries:      5
Total managed entries:  5
Unmanaged entries:      1565

In dryrun mode: suppressing entry installation for:
 ConfigFile:/etc/bcfg2.conf   ConfigFile:/etc/issue.net    ConfigFile:/etc/resolv.conf
 ConfigFile:/etc/issue        ConfigFile:/etc/motd

Phase: final
Correct entries:        0
Incorrect entries:      5
 ConfigFile:/etc/bcfg2.conf   ConfigFile:/etc/issue.net    ConfigFile:/etc/resolv.conf
 ConfigFile:/etc/issue        ConfigFile:/etc/motd
Total managed entries:  5
Unmanaged entries:      1565

No ca is specified. Cannot authenticate the server with SSL.

And finally, output from the server when trying to run a report.

# cfg2-admin snapshots reports -a
Traceback (most recent call last):
  File "/usr/sbin/bcfg2-admin", line 78, in <module>
    main()
  File "/usr/sbin/bcfg2-admin", line 70, in main
    mode(args[1:])
  File "/usr/lib/python2.6/dist-packages/Bcfg2/Server/Admin/Snapshots.py", line 93, in __call__
    for item in q.all():
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py", line 1267, in all
    return list(self)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py", line 1361, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py", line 1364, in _execute_and_instances
    result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none())
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/session.py", line 755, in execute
    clause, params or {})
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py", line 824, in execute
    return Connection.executors[c](self, object, multiparams, params)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py", line 874, in _execute_clauseelement
    return self.__execute_context(context)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py", line 896, in __execute_context
    self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py", line 950, in _cursor_execute
    self._handle_dbapi_exception(e, statement, parameters, cursor, context)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception
    raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "client.name" must appear in the GROUP BY clause or be used in an aggregate function
 'SELECT client.name AS client_name, snapshot.correct AS snapshot_correct, snapshot.revision AS snapshot_revision, snapshot.timestamp AS snapshot_timestamp \nFROM client, snapshot \nWHERE client.id = snapshot.client_id GROUP BY client.id' {}