Postgres query error

Nicolas Plessis, modified 13 Years ago.

Postgres query error

Youngling Posts: 10 Join Date: 3/1/11 Recent Posts
Hi,

I've installed Klaros from the war file on linux.
After the first deployment, the website seems to work but authentication failed with the default admin account, i noticed errors on postgres :

2011-03-01 17:08:39 EST ERROR: relation "kl_db_revision" does not exist at character 22
2011-03-01 17:08:39 EST STATEMENT: SELECT REVISION FROM KL_DB_REVISION WHERE ACTIVE=$1

The correct relation is kl_db_revision.

Thank you.

Nicolas
thumbnail
Torsten Stolpmann, modified 13 Years ago.

RE: Postgres query error

Jedi Council Member Posts: 755 Join Date: 2/12/09 Recent Posts
Hi Nicolas,

looks like a case sensitivity problem with table names when checking for database revisions. This will not influence initial installs, but may have an effect on previous ones. Thanks for reporting, will look into this tomorrow.

Regards,

Torsten
thumbnail
Torsten Stolpmann, modified 13 Years ago.

RE: Postgres query error

Jedi Council Member Posts: 755 Join Date: 2/12/09 Recent Posts
Actually this is rooted in an incompatibility of PostgreSQL with the SQL standard as explained in the PostgresSQL Manual:

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)


We do the latter and never quote. Thus Klaros checks for the existence of KL_DB_REVISION to check if a previous database has to be migrated, which is transparently translated to kl_db_revision in PostgreSQL.

So although the error message is misleading, all is well here.

Thanks again for spotting this.

Regards,

Torsten
Nicolas Plessis, modified 13 Years ago.

RE: Postgres query error

Youngling Posts: 10 Join Date: 3/1/11 Recent Posts
Thank you, my problem is more deeper than expected.

When i've installed the jar (the war file do the same error) for the first time with postgres, the deployment end at this point in tomcat :

2011-03-02 14:41:40: WARN, org.hibernate.util.JDBCExceptionReporter, main SQL Error: 0, SQLState: 42P01
2011-03-02 14:41:40: ERROR, org.hibernate.util.JDBCExceptionReporter, main ERROR: relation "k_cfg_prop_def" does not exist

And postgres give me this:

2011-03-02 14:41:40 EST ERROR: relation "k_cfg_prop_def" does not exist at character 386
2011-03-02 14:41:40 EST STATEMENT: select this_.KL__ID as KL1_0_0_, this_.VERSION as VERSION0_0_, this_.CONFIG as CONFIG40_0_, this_.CLASS as CLASS40_0_, this_.SORT_ORDER as SORT3_40_0_, this_.PROPERTY_DISPLAY_NAME as PROPERTY4_40_0_, this_.PROPERTY_NAME as PROPERTY5_40_0_, this_.PROPERTY_TYPE as PROPERTY6_40_0_, this_.MANDATORY as MANDATORY40_0_, this_.ENABLED as ENABLED40_0_, this_.USER_DEFINED as USER9_40_0_ from K_CFG_PROP_DEF this_


Then i restart tomcat and this time, the app seems to work, but database is empty and postgres give me this error when i reach the webpage:

2011-03-02 14:44:13 EST ERROR: relation "kl_db_revision" does not exist at character 22
2011-03-02 14:44:13 EST STATEMENT: SELECT REVISION FROM KL_DB_REVISION WHERE ACTIVE=$1

catalina.out from the 1st and 2nd, and list of relations in postgres are in attachment.

Attachments:

thumbnail
Torsten Stolpmann, modified 13 Years ago.

RE: Postgres query error

Jedi Council Member Posts: 755 Join Date: 2/12/09 Recent Posts
We distributed a wrong setting in the second level cache configuration, which is causing this problem. This leads to an uninitialized database, so users will not be able to login with the default credentials ("Authentication failed (wrong user name or password).").

This error will only affect users installing a fresh database, while exisiting installations should not be affected.

We have just published the 3.3.1 hotfix release which remedies this problem. Sorry for the foul-up.

Regards,

Torsten
Nicolas Plessis, modified 13 Years ago.

RE: Postgres query error

Youngling Posts: 10 Join Date: 3/1/11 Recent Posts
Thank you Torsten,

It works great emoticon
I still have to restart tomcat twice but the second time, webapps works.

For information the first time i still have this error :
2011-03-03 08:26:02: WARN, org.hibernate.util.JDBCExceptionReporter, main SQL Error: 0, SQLState: 42P01
2011-03-03 08:26:02: ERROR, org.hibernate.util.JDBCExceptionReporter, main ERROR: relation "k_cfg_prop_def" does not exist
Position: 386


However i noticed few things to improve the deployment,
- in the linux installer the changelog wasn't update since 3.2.3 version,
- it might be nice to display the version of the installer in its filename
- the war file wasn't update with 3.1.1 (or it has the same problem as before)


Thank you so much for you help !
thumbnail
Torsten Stolpmann, modified 13 Years ago.

RE: Postgres query error

Jedi Council Member Posts: 755 Join Date: 2/12/09 Recent Posts
Hi Nicolas,

good to hear this is working now.

- in the linux installer the changelog wasn't update since 3.2.3 version,
- the war file wasn't update with 3.1.1 (or it has the same problem as before)


Are you sure you got these archives from http://www.klaros-testmanagement.com/files/Klaros-Setup.jar and http://www.klaros-testmanagement.com/files/webapps/klaros-web.war respectivly? I rechecked these and they linked to the correct files.

- it might be nice to display the version of the installer in its filename


We currently prefer to keep the download URLfixed which helps us to distribute hotfix-releases transparently. We will look into the options we have here.

Regards,

Torsten
Nicolas Plessis, modified 13 Years ago.

RE: Postgres query error

Youngling Posts: 10 Join Date: 3/1/11 Recent Posts
Yep i've check the checksum, you're right the war file was updated but still do the same error... hum weird.
It's ok i can live with the jar emoticon

I've try again the install and yep the changelog at the step 2/10 didn't have changed since 3.2.3 and show me :
Release Notes for Klaros-Testmanagement version 3.2.3.

It's realy the 3.3.1 (cause it works now) but the changelog isn't update from what i see.

thanks.
thumbnail
Torsten Stolpmann, modified 13 Years ago.

RE: Postgres query error

Jedi Council Member Posts: 755 Join Date: 2/12/09 Recent Posts
Nicolas,

I rechecked and the jar contains the correct release notes as well as the jar is of the correct version.

I suspect that you tomcat redeployment is not working correctly. I already saw this happen in the past.

Coud you please try the following:

1. Stop Tomcat
2. Remove the webapps/klaros-web folder
3. Redeploy the war
4. Start Tomcat

Hope this helps.

Regards,

Torsten
Nicolas Plessis, modified 13 Years ago.

RE: Postgres query error

Youngling Posts: 10 Join Date: 3/1/11 Recent Posts
It helps !

Thank you.