[ic] PostgreSQL 7.0.x + IC 4.6.x: construct config error: ERROR: Bad timestamp external representation ''

Dan db@list.dnsalias.net
Fri, 1 Dec 2000 20:52:23 -0800


In recent versions of IC and PG, I have been having trouble getting even the
'construct' demo built.  During the start of the IC server, it goes through
all the create table ... process, and often litters the following error
several times:
ds15 config error: ERROR:  Bad timestamp external representation ''
And I get errors like: "We're sorry, the Interchange server is
unavailable...
We are out of service or may be experiencing high system demand, please try
again soon." when trying to access cgi-bin/ds15/index.html, but sometimes it
gives me "Undefined catalog: /cgi-bin/ds15".

What is really weird is that the databases seem to be created correctly.  I
can browse them in psql and SELECT * them just fine.

I tracked down a few conversations of people who experienced the same error
on the pgsql lists (of course not related to Interchanage).  Namely...
http://www.postgresql.org/mhonarc/pgsql-bugs/2000-11/msg00088.html
and
http://www.postgresql.org/mhonarc/pgsql-bugs/2000-11/msg00096.html
Basically I think it is because of some implicent TIMESTAMP --> DATE type
conversion that we are doing, maybe during data import?

If anyone has an idea about which code to peruse through, or how to do some
better testing, or whatever, I would love to do it.  (My life goal is to get
into the CREDITS file of every project).  I'll even take orders from Mike
"Lazy" Heins. (for the humor impared: I'm only quoting recent post, this is
not a flame)

Thanks.
-Dan Browning


Here is what I am attaching to help solve the problem:
1. Platform
2. Admin -> Administration excerpt
3. Related excerpts from interchange/error.log
4. Related excerpts from $CATALOG/error.log
5. Related excerpts from interchange.cfg
6. psql \dt
7. psql SELECT * FROM affiliate

1. Platform: RedHat 7.0 (actually, 7.0.1 from valinux, but it's basically
the same), Perl 5.005, IC 4.6.1(cvs 12-1-00).  PostgreSQL 7.0.[2|3]
And Yes, I'm positive that my DBD::Pg is setup fine (admin -> administration
says so).

2. Admin->Administration excerpt:
Perl Information
Perl Version      Perl 5.006 (called with: perl)
Optional Module Information      LWP::Simple not found. External UPS lookup
and other internet-related functions will not work.
MD5 found (v2.01).
MIME::Base64 found (v2.11).
SQL::Statement found (v0.1016).
Safe::Hole found (v0.06).
Storable found (v1.000).
Tie::Watch found (v1.0).
URI::URL found (v5.02).
Safe operations untrapped      ftfile sort rand
Database Information
Database Interfaces      GDBM available (v1.03)
Berkeley DB_File available (v1.72)
DBI enabled (v1.14), available drivers:
ADO
ExampleP
Multiplex
Pg
Proxy

3. interchange/error.log

- - - [01/December/2000:11:32:30 -0800] - - Config 'ds15' at server startup
- - - [01/December/2000:11:32:30 -0800] - - Using PostgreSQL,
DSN=dbi:Pg:dbname=ds15.
- - - [01/December/2000:11:32:32 -0800] - - ds15 config error: No database
object for table: affiliate
>
> Probable mismatch of Database directive to database type,
> for example calling DBI without proper modules or database
> access.
- - - [01/December/2000:11:32:32 -0800] - - Error reconfiguring catalog
/cgi-bin/ds15 from running server (4084)
>
- - - [01/December/2000:11:32:32 -0800] - - Config 'ds15' from running
server (4084)
- - - [01/December/2000:11:32:32 -0800] - - Using PostgreSQL,
DSN=dbi:Pg:dbname=ds15.
- - - [01/December/2000:11:32:33 -0800] - - ds15 config error: Couldn't move
'/var/ic/ds15/products/new_userdb.sql' to
'/var/ic/ds15/products/userdb.sql': No such file or directory
- - - [01/December/2000:11:32:33 -0800] - - ds15: config error. Skipping.
- - - [01/December/2000:11:32:33 -0800] - - Config 'test1' at server startup
- - - [01/December/2000:11:32:33 -0800] - - Using default DBM database.
- - - [01/December/2000:11:32:33 -0800] - - START server (4132) (INET and
UNIX)
- - - [01/December/2000:11:32:43 -0800] - - ds15 config error: ERROR:  Bad
timestamp external representation ''
- - - [01/December/2000:11:32:43 -0800] - - Error reconfiguring catalog
/cgi-bin/ds15 from running server (4084)


4. $CATALOG/error.log
>
- - - [01/December/2000:11:32:32 -0800] ds15 - bad table creation statement:
> create table affiliate

> affiliate char(16) NOT NULL,
> name text,
> campaigns text,
> coupon_amount text,
> join_date text,
> url text,
> timeout text,
> active text,
> password text,
> image text
> )
>
>
> Error: ERROR:  Cannot insert a duplicate key into unique index
pg_type_typname_index

5. interchange.cfg (passwords changed to protect the innocent)

#Variable    SOMESQL         1
#Variable    MYSQL
#Variable    PGSQL           1
#Variable    ORACLE
#Variable    SQLDSN          dbi:Pg:dbname=ds15
#Variable    SQLDB           ds15
#Variable    SQLUSER         XXXX
#Variable    SQLPASS         YYYY

6. psql \dt
ds15=# \dt
           List of relations
       Name       | Type  |    Owner
------------------+-------+-------------
 affiliate        | table | interchange
 area             | table | interchange
 cat              | table | interchange
 country          | table | interchange
 gift_certs       | table | interchange
 merchandising    | table | interchange
 orderline        | table | interchange
 pricing          | table | interchange
 recurring_items  | table | interchange
 recurring_orders | table | interchange
 userdb           | table | interchange
(11 rows)

7. psql SELECT * FROM affiliate
ds15=# SELECT * FROM affiliate;
    affiliate     |           name           | campaigns | coupon_amount |
join_date |                  url                  | timeout | active |
password |                       image
------------------+--------------------------+-----------+---------------+--
---------+---------------------------------------+---------+--------+-------
---+---------------------------------------------------
 hardhat          | Hardhat Construction     | coupon    | 5             |
20000827  | http://demo.akopia.com/~hardhat       |         | 1      |
akopia   | http://demo.akopia.com/~hardhat/images/logo.gif
 consolidated     | Consolidated Diversified | coupon    | 5             |
20000910  | http://www.minivend.com/consolidated/ |         | 1      |
akopia   | http://www.minivend.com/consolidated/conslogo.gif
(2 rows)