Postgresql 9.4: ERROR: type "datetime" does not exist

[Anders Lantz]

I´m setting up Webadm Version 1.4.1 (64bit) built September 8 2015 using postgresql 9.4 as the SQL-server, during the database setup (in the webadm webinterface) I get the following error:

Creating table 'Admin'... Failed (Error while executing the query (non-fatal); ERROR: type "datetime" does not exist at character 54)
Query was: CREATE TABLE "Admin" ("ID" BIGSERIAL NOT NULL,"Time" DATETIME,"DN" VARCHAR(255),"Source" VARCHAR(64),"Session" VARCHAR(64),"Text" TEXT, PRIMARY KEY ("ID"))

Creating table 'Manag'... Failed (Error while executing the query (non-fatal); ERROR: type "datetime" does not exist at character 54)
Query was: CREATE TABLE "Manag" ("ID" BIGSERIAL NOT NULL,"Time" DATETIME,"Application" VARCHAR(64),"DN" VARCHAR(255),"Source" VARCHAR(64),"Session" VARCHAR(64),"Text" TEXT, PRIMARY KEY ("ID"))

Creating table 'WebApp'... Failed (Error while executing the query (non-fatal); ERROR: type "datetime" does not exist at character 55)
Query was: CREATE TABLE "WebApp" ("ID" BIGSERIAL NOT NULL,"Time" DATETIME,"Application" VARCHAR(64),"DN" VARCHAR(255),"Source" VARCHAR(64),"Session" VARCHAR(64),"Text" TEXT, PRIMARY KEY ("ID"))

Creating table 'WebSrv'... Failed (Error while executing the query (non-fatal); ERROR: type "datetime" does not exist at character 55)
Query was: CREATE TABLE "WebSrv" ("ID" BIGSERIAL NOT NULL,"Time" DATETIME,"Application" VARCHAR(64),"Client" VARCHAR(64),"DN" VARCHAR(255),"Source" VARCHAR(64),"Session" VARCHAR(64),"Text" TEXT, PRIMARY KEY ("ID"))

Creating table 'Alert'... Failed (Error while executing the query (non-fatal); ERROR: type "datetime" does not exist at character 54)
Query was: CREATE TABLE "Alert" ("ID" BIGSERIAL NOT NULL,"Time" DATETIME,"Application" VARCHAR(64),"Server" VARCHAR(64),"Text" TEXT, PRIMARY KEY ("ID"))

The installation overview in the we interface looks like this:

Checking SQL database
Checking database connection... Ok
Reading database tables... Ok
Checking table Admin... Missing
Checking table Manag... Missing
Checking table WebApp... Missing
Checking table WebSrv... Missing
Checking table Alert... Missing
Checking table Message... Ok
    Checking field Message.Application... Ok
    Checking field Message.Reference... Ok
    Checking field Message.Language... Ok
    Checking field Message.Text... Ok
Checking table Inventory... Ok
    Checking field Inventory.Type... Ok
    Checking field Inventory.Reference... Ok
    Checking field Inventory.Description... Ok
    Checking field Inventory.DN... Ok
    Checking field Inventory.Data... Ok
    Checking field Inventory.Status... Ok

My servers.xml has the following related info:

<SqlServer name="SQL-MFA-01"

        type="PostgreSQL"

        host="-SQL-MFA-01"

        user="user"

        password="password"

        port="5432"

        database="webadm" />

[Anders Lantz]

Not sure if it is relevant but i also get this message in the the postgres log:

2015-09-24 13:59:41 CEST [12181-1] webadm@webadm ERROR:  unrecognized configuration parameter "ksqo"

2015-09-24 13:59:41 CEST [12181-2] webadm@webadm STATEMENT:  set ksqo to 'ON'

[Anders Lantz]

In order to continue my installation I changed the querys used and ran them manually, now webadm is happy with the database but I am unsure if this will create problems down the road.

CREATE TABLE "Admin" ("ID" BIGSERIAL NOT NULL,"Time" TIMESTAMP,"DN" VARCHAR(255),"Source" VARCHAR(64),"Session" VARCHAR(64),"Text" TEXT, PRIMARY KEY ("ID"))

CREATE TABLE "Manag" ("ID" BIGSERIAL NOT NULL,"Time" TIMESTAMP,"Application" VARCHAR(64),"DN" VARCHAR(255),"Source" VARCHAR(64),"Session" VARCHAR(64),"Text" TEXT, PRIMARY KEY ("ID"))

CREATE TABLE "WebApp" ("ID" BIGSERIAL NOT NULL,"Time" TIMESTAMP,"Application" VARCHAR(64),"DN" VARCHAR(255),"Source" VARCHAR(64),"Session" VARCHAR(64),"Text" TEXT, PRIMARY KEY ("ID"))

CREATE TABLE "WebSrv" ("ID" BIGSERIAL NOT NULL,"Time" TIMESTAMP,"Application" VARCHAR(64),"Client" VARCHAR(64),"DN" VARCHAR(255),"Source" VARCHAR(64),"Session" VARCHAR(64),"Text" TEXT, PRIMARY KEY ("ID"))

CREATE TABLE "Alert" ("ID" BIGSERIAL NOT NULL,"Time" TIMESTAMP,"Application" VARCHAR(64),"Server" VARCHAR(64),"Text" TEXT, PRIMARY KEY ("ID"))

[Spyridon Gouliarmis (RCDevs)]

Might have to do with the recent changes we did to support SQL Server. I'll pass this as a bug report to the devs.

[Anders Lantz]

From what I have found googeling postgresql has moved away from "datetime" but in order to still support it the recommended ODBC driver has some mapping done from datetime to timestamp, however the ODBC driver packaged with the webadm VMtemplate is outdated and should be replaced by psqlodbca according to some forum posts I have seen.

I am no DBA or even a Linux server admin so take what I say in regards to this with a grain of salt.

Any info from the devs on the correct/supported solution to this problem for me would be much appreciated. 

[Spyridon Gouliarmis (RCDevs)]

Can you try this version? https://www.rcdevs.com/downloads/download.php?type=1&id=Testing%2Fwebadm_all_in_one-1.4.1-1-x64.sh.gz

[Anders Lantz]

How do I go about retriggering the DB setup?

Also if you have a link to documentation on how to update that would be much appreciated. 

[Spyridon Gouliarmis (RCDevs)]

Delete the tables, that should do the trick.

No documentation, just run the setup. There are guides to upgrade between major versions, but I'm assuming that does not apply here.

[Anders Lantz]

Computer says no:

Version 1.4.1-1 (64bit) built September 8 2015

Creating table 'Admin'... Failed (Error while executing the query (non-fatal); ERROR: type "datetime" does not exist at character 54)
Query was: CREATE TABLE "Admin" ("ID" BIGSERIAL NOT NULL,"Time" DATETIME,"DN" VARCHAR(255),"Source" VARCHAR(64),"Session" VARCHAR(64),"Text" TEXT, PRIMARY KEY ("ID"))

Creating table 'Manag'... Failed (Error while executing the query (non-fatal); ERROR: type "datetime" does not exist at character 54)
Query was: CREATE TABLE "Manag" ("ID" BIGSERIAL NOT NULL,"Time" DATETIME,"Application" VARCHAR(64),"DN" VARCHAR(255),"Source" VARCHAR(64),"Session" VARCHAR(64),"Text" TEXT, PRIMARY KEY ("ID"))

Creating table 'WebApp'... Failed (Error while executing the query (non-fatal); ERROR: type "datetime" does not exist at character 55)
Query was: CREATE TABLE "WebApp" ("ID" BIGSERIAL NOT NULL,"Time" DATETIME,"Application" VARCHAR(64),"DN" VARCHAR(255),"Source" VARCHAR(64),"Session" VARCHAR(64),"Text" TEXT, PRIMARY KEY ("ID"))

Creating table 'WebSrv'... Failed (Error while executing the query (non-fatal); ERROR: type "datetime" does not exist at character 55)
Query was: CREATE TABLE "WebSrv" ("ID" BIGSERIAL NOT NULL,"Time" DATETIME,"Application" VARCHAR(64),"Client" VARCHAR(64),"DN" VARCHAR(255),"Source" VARCHAR(64),"Session" VARCHAR(64),"Text" TEXT, PRIMARY KEY ("ID"))

Creating table 'Alert'... Failed (Error while executing the query (non-fatal); ERROR: type "datetime" does not exist at character 54)
Query was: CREATE TABLE "Alert" ("ID" BIGSERIAL NOT NULL,"Time" DATETIME,"Application" VARCHAR(64),"Server" VARCHAR(64),"Text" TEXT, PRIMARY KEY ("ID"))

[Administrators]

Sorry we didn't upload the file - You can retry...

[Anders Lantz]

Much better, what did you change if I may ask?

[Administrators]

Used TIMESTAMP instead of DATETIME as recommended.

Disabled obsolet PG-ODBC features.