Michał Purzyński reported a problem with our pmGraph software, when using a PostgreSQL database:
I’d like to report a bug – pmgraph is unable to get data from postgresql database to which nfacctd is writing…
javax.servlet.ServletException: org.postgresql.util.PSQLException:
ERROR: column "src_port" does not exist
It turns out that pmacct, up to and including version 0.12.4, uses a different column name for the source port if the database is MySQL or SQLite:
if (!strcmp(config.type, "mysql") || !strcmp(config.type, "sqlite3")) {
strncat(insert_clause, "src_port", SPACELEFT(insert_clause));
strncat(where[primitive].string, "src_port=%u", SPACELEFT(where[primitive].string));
}
else {
strncat(insert_clause, "port_src", SPACELEFT(insert_clause));
strncat(where[primitive].string, "port_src=%u", SPACELEFT(where[primitive].string));
}
I really wish applications wouldn’t change their behaviour arbitrarily like this. To work around it, we would have to hard-code the database types in pmGraph as well, or add an option to switch the column names. Since pmGraph uses JDBC to access the database, it’s not even obvious which driver names are accessing an (underlying) MySQL or SQLite database. So we need to switch the column names, but if we can get pmacct fixed then we can ease the pain for new users in future.
I reported a bug to Paolo Lucente, the lead developer of pmacct, through their mailing list. Paolo agreed to change this behaviour, even though it will break backwards compatibility. We spent some time discussing how to do this in a way that would minimise any impact on existing users.
To do this, we took advantage of pmacct’s existing system of database table versioning, which means that you can still use the oldest table structures, even with the most recent version of pmacct. Paolo agreed to create a new schema version that uses the same column names for all databases, so that pmacct will remain backwards-compatible for all users unless they deliberately choose to change their database schema version.
As we chose to standardise on the PostgreSQL column names, the column names will change for MySQL users between schema versions 7 and 8, so we’ll need to add a configuration option to pmGraph to allow users to choose whether they want the old or the new column names. This is the very same switch that I wanted to avoid in pmacct, but pmGraph has fewer users so it has less impact.
