Cannot add new record to database on Staging

[MerielP]

If I go into the data editing interface and try to add a record to tblarticles in the database 'Uploaded database' in the project 'Staging test' on Staging, I get an error message which tries to tell me that I'm not conforming to the database schema, even though I am:

Adding exactly the same record to a duplicate database works perfectly on App, so it's not a problem with the data.

I have a hunch this may be connected to the lack-of-primary key issue I'm experiencing with the same database (see issue #767), though I don't know this for certain.

I have great difficulty working out what the error message pasted above is actually saying. But as far as I understand it, it seems to be wrong - it's perfectly possible to add a foreign key value to a record in the ORDS, and has been for a very long time. In addition to fixing the bug, would it be possible to clarify what the ORDS actually thinks is the problem here? This is one of the error messages I don't have access to, so I'm not able to edit it myself.

[thestoat]

Interesting. Two issues here. One is that the error reported is "null". The second is that the real error (duplicate key) shouldn't happen. Investigating ...

[thestoat]

Ok, I should now have fixed that. You won't know for sure until the code is on staging, of course. Similar databases don't exhibit this problem and it seems that for some reason, the commands I run against that particular database don't work - the db seems to have been been created differently to others.

Anyway, all should now be ok - with a valid error message returned if not.

[MerielP]

Now seems to be working properly - thanks.

[MerielP]

While I managed to add a new record to the table where this problem had previously arisen, I'm now hitting what looks like the same problem again in a database I've just created using the schema designer.

This is happening in the project called 'Staging test 2', in 'ORDS-created database'. It has three tables, and I can't add records to any of them.

If I try to add a record to the tables called 'colours' or 'sizes', I get an error message like this:

Trying to add one to the table called 'happy_things' (which includes two foreign key fields), however, gives me this:

So in addition to the problem of not being able to add records, we're still getting the unhelpful error messages.

[thestoat]

Does this problem only happen with your new database or others as well? Does it happen on Dev? can you send me the database to look at?

[MerielP]

The problem seems to be with any table created using the ORDS schema designer, including new tables added to databases which have been uploaded (in that case, one can add records to the uploaded tables, but not the newly created ones). It happens on both Staging and Dev.

As this affects databases created using the ORDS schema designer, I can't send you the database to look at, as the version which is causing a problem exists only in the ORDS.

Out of curiosity, I tried exporting a copy of the database mentioned above as an SQL file, and re-uploading it to the ORDS. Strangely, it then works: I can add records to the re-uploaded version, even though I still can't add them to the ORDS-created version it's based on. You can see the re-uploaded version in the project 'Staging test 2' - it's called 'Re-uploaded database'.

[thestoat]

Ok, I can see the problem. It is in the rest interface, that creates the database (through the schema designer) but doesn't set up correct ODBC permissions for the database components.

[thestoat]

I have made some changes in this area but I strongly suspect more will be required. I am thus assigning this to you to check but please test well - I expect to see it back in my queue before long :-) In particular, it would be good to set up a project with shared members, create a new db with the designer and then see if members can edit the data. I would check here but am having real problems with the whole system (SysDave has been notified).

[MerielP]

Thus far, things look promising. On Dev, I can now add new records to newly-created tables in both databases already containing uploaded tables, and databases created entirely from scratch using the schema designer. (There's still a problem on Staging, but I assume that will disappear when we do a new build.)

Now that James isn't around, I don't have a handy volunteer to add records to shared projects. Dave, if you would like to test that you can add new records, the databases I've just created are the hugely imaginatively named 'Testing adding new records' and 'Testing adding new records 2' in 'Sample project' on Dev. You're already a project admin.

Anything else specific you want me to test regarding this issue?

[thestoat]

I have just added some records and all was well - ditto update. Hmm - I'm probably being paranoid. Ok, we can close - we can always raise a new issue if any other problem comes to light

[MerielP]

OK - will close this.