Problems with creating an SQL view in 2.16

jason · 28 August 2014 07:43

Looks like there was an attempt to fix this in rev 14845, but still not working for me.

We are using

Version:
2.16
Build revision:
16370

Probably could be hacked rather easily, but not sure if things are still not fixed properly in the startup routines?

Regards,
Jason

Caused by: org.postgresql.util.PSQLException: ERROR: null value in column “viewid” violates not-null constraint

Detail: Failing row contains (null, test sql view, , SELECT uid, name from organisationunit LIMIT 10;, bMTwKD2Vpe0, null, 2014-08-28 07:41:46.921, 4511, 2014-08-28 07:41:46.921, 2361, rw------).

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)

at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:133)

Lars · 28 August 2014 08:10

Hi Jason,

can you execute this on your db and see if the query fails, if so provide the log?

alter table sqlview drop column viewid;

regards,

Lars

···

On Thu, Aug 28, 2014 at 9:43 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Looks like there was an attempt to fix this in rev 14845, but still not working for me.

We are using

Version:
2.16
Build revision:
16370

Probably could be hacked rather easily, but not sure if things are still not fixed properly in the startup routines?

Regards,
Jason

Caused by: org.postgresql.util.PSQLException: ERROR: null value in column “viewid” violates not-null constraint

Detail: Failing row contains (null, test sql view, , SELECT uid, name from organisationunit LIMIT 10;, bMTwKD2Vpe0, null, 2014-08-28 07:41:46.921, 4511, 2014-08-28 07:41:46.921, 2361, rw------).

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)

at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:133)

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

jason · 28 August 2014 08:13

That does not work, but this does (CAREFUL WITH THIS ONE)

alter table sqlview drop column viewid CASCADE;

There is a foreign key reference to some other table, but cannot recall which one it was. Something to do with users I think.

Regards,

Jason

···

On Thu, Aug 28, 2014 at 10:10 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Jason,

can you execute this on your db and see if the query fails, if so provide the log?

alter table sqlview drop column viewid;

regards,

Lars

On Thu, Aug 28, 2014 at 9:43 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Looks like there was an attempt to fix this in rev 14845, but still not working for me.

We are using

Version:
2.16
Build revision:
16370

Probably could be hacked rather easily, but not sure if things are still not fixed properly in the startup routines?

Regards,
Jason

Caused by: org.postgresql.util.PSQLException: ERROR: null value in column “viewid” violates not-null constraint

Detail: Failing row contains (null, test sql view, , SELECT uid, name from organisationunit LIMIT 10;, bMTwKD2Vpe0, null, 2014-08-28 07:41:46.921, 4511, 2014-08-28 07:41:46.921, 2361, rw------).

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)

at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:133)

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars · 28 August 2014 08:15

Sure, that’s the problem. We wanted to rename the “viewid” column but renaming primary keys is tricky when using hibernate.

We must include a drop constraint on that foreign key, let me know if you remember the name of it.

Lars

···

On Thu, Aug 28, 2014 at 10:13 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

That does not work, but this does (CAREFUL WITH THIS ONE)

alter table sqlview drop column viewid CASCADE;

There is a foreign key reference to some other table, but cannot recall which one it was. Something to do with users I think.

Regards,

Jason

On Thu, Aug 28, 2014 at 10:10 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Jason,

can you execute this on your db and see if the query fails, if so provide the log?

alter table sqlview drop column viewid;

regards,

Lars

On Thu, Aug 28, 2014 at 9:43 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Looks like there was an attempt to fix this in rev 14845, but still not working for me.

We are using

Version:
2.16
Build revision:
16370

Probably could be hacked rather easily, but not sure if things are still not fixed properly in the startup routines?

Regards,
Jason

Caused by: org.postgresql.util.PSQLException: ERROR: null value in column “viewid” violates not-null constraint

Detail: Failing row contains (null, test sql view, , SELECT uid, name from organisationunit LIMIT 10;, bMTwKD2Vpe0, null, 2014-08-28 07:41:46.921, 4511, 2014-08-28 07:41:46.921, 2361, rw------).

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)

at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:133)

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

jason · 28 August 2014 08:53

Not 100% sure, but think is was this

Referenced by:

TABLE “sqlviewusergroupaccesses” CONSTRAINT “fk39f92993428ceacc” FOREIGN KEY (sqlviewid) REFERENCES sqlview(viewid)

···

On Thu, Aug 28, 2014 at 10:15 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Sure, that’s the problem. We wanted to rename the “viewid” column but renaming primary keys is tricky when using hibernate.

We must include a drop constraint on that foreign key, let me know if you remember the name of it.

Lars

On Thu, Aug 28, 2014 at 10:13 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

That does not work, but this does (CAREFUL WITH THIS ONE)

alter table sqlview drop column viewid CASCADE;

There is a foreign key reference to some other table, but cannot recall which one it was. Something to do with users I think.

Regards,

Jason

On Thu, Aug 28, 2014 at 10:10 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Jason,

can you execute this on your db and see if the query fails, if so provide the log?

alter table sqlview drop column viewid;

regards,

Lars

On Thu, Aug 28, 2014 at 9:43 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Looks like there was an attempt to fix this in rev 14845, but still not working for me.

We are using

Version:
2.16
Build revision:
16370

Probably could be hacked rather easily, but not sure if things are still not fixed properly in the startup routines?

Regards,
Jason

Caused by: org.postgresql.util.PSQLException: ERROR: null value in column “viewid” violates not-null constraint

Detail: Failing row contains (null, test sql view, , SELECT uid, name from organisationunit LIMIT 10;, bMTwKD2Vpe0, null, 2014-08-28 07:41:46.921, 4511, 2014-08-28 07:41:46.921, 2361, rw------).

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)

at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:133)

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

jason · 28 August 2014 08:56

That may look a little funny actually, because in the affected system, the key was slightly different. This is what it looks like now on another 2.16 system.

Referenced by:

TABLE “sqlviewusergroupaccesses” CONSTRAINT “fk39f92993428ceacc” FOREIGN KEY (sqlviewid) REFERENCES sqlview(sqlviewid)

So, I think I should have hacked it as

Referenced by:

TABLE “sqlviewusergroupaccesses” CONSTRAINT “fk39f92993428ceacc” FOREIGN KEY (viewid) REFERENCES sqlview(viewid) ?

···

On Thu, Aug 28, 2014 at 10:53 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Not 100% sure, but think is was this

Referenced by:

TABLE “sqlviewusergroupaccesses” CONSTRAINT “fk39f92993428ceacc” FOREIGN KEY (sqlviewid) REFERENCES sqlview(viewid)

On Thu, Aug 28, 2014 at 10:15 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Sure, that’s the problem. We wanted to rename the “viewid” column but renaming primary keys is tricky when using hibernate.

We must include a drop constraint on that foreign key, let me know if you remember the name of it.

Lars

On Thu, Aug 28, 2014 at 10:13 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

That does not work, but this does (CAREFUL WITH THIS ONE)

alter table sqlview drop column viewid CASCADE;

There is a foreign key reference to some other table, but cannot recall which one it was. Something to do with users I think.

Regards,

Jason

On Thu, Aug 28, 2014 at 10:10 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Jason,

can you execute this on your db and see if the query fails, if so provide the log?

alter table sqlview drop column viewid;

regards,

Lars

On Thu, Aug 28, 2014 at 9:43 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Looks like there was an attempt to fix this in rev 14845, but still not working for me.

We are using

Version:
2.16
Build revision:
16370

Probably could be hacked rather easily, but not sure if things are still not fixed properly in the startup routines?

Regards,
Jason

Caused by: org.postgresql.util.PSQLException: ERROR: null value in column “viewid” violates not-null constraint

Detail: Failing row contains (null, test sql view, , SELECT uid, name from organisationunit LIMIT 10;, bMTwKD2Vpe0, null, 2014-08-28 07:41:46.921, 4511, 2014-08-28 07:41:46.921, 2361, rw------).

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)

at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:133)

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp