search

oracle / node-oracledb

Oracle Database driver for Node.js maintained by Oracle Corp.
http://oracle.github.io/node-oracledb/
Other
2.26k stars 1.08k forks source link

Tracking: Known issues with SODA #1088

Open morgiyan opened 5 years ago

morgiyan commented 5 years ago

This is to track known SODA issues.

Client-libraries required for SODA production use: 18.5 and above. Oracle Database: 18 and above.

Known issues affecting SODA node.js are:

Enhancement Requests

cjbj commented 5 years ago

I'll start: one issue is that sodaCollection.metaData should be an Object (as documented) but is a String. This will change to an Object in node-oracledb 4.0

charleaux commented 5 years ago

Any idea when SODA will be ready for use in production?

morgiyan commented 5 years ago

Let me check with @cjbj, but I believe we should be removing the "preview" status any day now (it's just a matter of when Chris will be able to update the doc).

For known issues affecting SODA, see the comment at the top (we'll maintain a list of such issues, including status, there).

charleaux commented 5 years ago

Awesome 👍 I can't wait

morgiyan commented 5 years ago

Charles (@charleaux), the preview status has been removed. Please see the list of known issues above (in particular issue 1, the other two are minor). If you need the fix for issue 1 to go production, let me know. Thanks!

charleaux commented 5 years ago

Is there any reason SODA isn't supported with node prior to OracleDB 18.3?

I keep seeing this in the examples:

cjbj commented 5 years ago

@charleaux there are some bugs in the OCI layer not fixed until the 18.5 & 19.3 clients. In particular a big memory leak. While SODA APIs in node-oracledb work with 18.3 client, you will hit these bugs. Hence we want you to upgrade before going production. Note these bugs do not impact the SODA Java API.

morgiyan commented 5 years ago

@cjbj, I think @charleaux might be asking about the database itself (as opposed to instant client). @charleaux, is that right? If that's what's being asked, I can reply to that.

morgiyan commented 5 years ago

Basically the new SODA impls (node.js/python/ODPI-C) can be supported on 12.1 and above Oracle Database, just like the old impls (Java/REST).

But it requires an RDBMS-side fix to be backported to these releases. It's doable, and then the programmatic check that prohibits running SODA node.js on releases earlier than 18 can be removed.

The only issue is that if we do that, users that do not have this backport installed and try to create a collection, will end up with a wrong default collection shape on releases earlier than 18 (it'll only store key/content for each document, as opposed to key/content/version/last-mod timestamp/created-on timestamp, which is what the default collection is expected to store). So this can be a source of potential confusion. Thoughts?

Of course, this comment is regarding the backend (Oracle Database) version. The client needs to be 18.5 and above, as @cjbj points out.

charleaux commented 5 years ago

Thank you for clarifying, you're right I was more referring to the database server side. I was more curious than anything.

charleaux commented 5 years ago

Thanks for making this happen!

I was able to shrink my code in many of my routes by ~40% in some cases switching to the soda api.

Maybe at some point we can get more examples around SODA? I'd like to see more in the different kinds of indexing as well as pagination. It's not critical for me since I've been able to dig into the other non-node SODA documents to figure out how things work but the examples have been useful for me when learning and it's very likely there are others who would benefit from it.

cjbj commented 5 years ago

@charleaux PR's for new examples are welcome

morgiyan commented 5 years ago

Great to hear that @charleaux!

As @cjbj say PRs are welcome, and I'll also make a note for us to add examples as soon we have some free cycles.

charleaux commented 5 years ago

@morgiyan @cjbj Thanks guys, I know you're probably super busy as it is already. When things slow down on my end I'll see if if you still need any PRs for this also and come up with any I can think of.

charleaux commented 5 years ago

Just FYI in case anyone else was having these issues.

I was troubleshooting an issue where node would crash randomly and when it did crash sometimes this would show up in the logs (sometimes nothing would show up): [Error: ORA-40736: Query-By-Example (QBE) filter is not valid JSON JZN-00049: Input to JSON parser is empty] errorNum: 40736, offset: 0 } [nodemon] app crashed - waiting for file changes before starting...

The filter wasn't anything complicated:

let filter = {
    $and: [
        { measureId: { $eq: `${performanceDataItem.measureId}` } },
        { period: { $eq: `${performanceDataItem.period}` } }
    ]
};

What resolved the issue for me was upgrading the instant client from 18_5 to 19_3.

cjbj commented 5 years ago

Tagging @morgiyan for comment on the ORA-40736.

morgiyan commented 5 years ago

@charleaux somehow SODA thought the JSON that was passed in for the QBE is invalid.

Could you post a small complete reproducer so that we can check that the syntax of the QBE is correct?

The above, for example, seems to be missing backticks for the template literals, and I can't tell what values the template literals evaluate to.

Will look into it once you post a reproducer. Thank you!

cjbj commented 5 years ago

@morgiyan I edited @charleaux's post and used triple backticks around the whole block; you can see the embedded ticks now.

morgiyan commented 5 years ago

Thanks @cjbj, still would like to get the values template literals would evaluate to.

charleaux commented 5 years ago

@morgiyan The measureID was just something like this: an ID field "00476D3241D64F6EBF203AEBDE285F00" and the period i was using something simple to rule out special characters "Jan00"

I only posted for informational purposes, since my issue was resolved by upgrading the client.

morgiyan commented 5 years ago

Thanks @charleaux, we'll check (since we are not aware of any such issues with 18.5). And thanks for posting this of course!

morgiyan commented 5 years ago

@charleaux we ran this exact QBE with 18.5 IC, and cannot reproduce the issue. Runs fine for us. Maybe you could double check that you're trying this exact QBE with 18.5 IC. Thanks.

cjbj commented 4 years ago

Oracle DB 20c is now available as a preview.

By default in the preview, Oracle Database 20c SODA collections use a new JSON storage type. Since older Oracle Client libraries do not understand the new type, if you are using an older Oracle client library to access Oracle Database 20c, then use a work around:

There is no problem if you are using Oracle Client libraries 20.

cjbj commented 4 years ago

getOne() etc will fail if Oracle Client is < 19.5. This broke in node-oracledb 5.0.0. The workaround (i.e for macOS users stuck with 19.3 client libraries) is to explicitly set the fetch arraysize to 0: e.g. doc = await collection.find().fetchArraySize(0).key(key).getOne(); We'll fix this in a future node-oracledb release.

cjbj commented 3 years ago

I've updated the original post with a link to info for users moving to Oracle DB 21.

cjbj commented 3 years ago

Playing with the SODA metadata cache feature of node-oracledb 5.2.0-dev shows a huge reduction in round-trips when repeatedly opening collections. This equates to a reduced load on the DB and faster, more scalable applications. The cache also needs Oracle Client 19.11 (or later) or Oracle Client 21.3+ (which isn't available yet), so to test it out, grab Oracle Client 19.11 and set sodaMetaDataCache to true when opening a connection pool.

Node-oracledb 5.2.0-dev also avoids a round-trip needed if a pooled connection has to check the DB version - which is done when using SODA (or AQ). This optimization is for Oracle Client 19 and earlier. Oracle Client 21.1 libraries already have an equivalent optimization.

Bottom line: if you're a SODA user, grab Instant Client 19.11 (even if you have 21.1) and try the SODA metadata cache in node-oracledb 5.2.0-dev installed from the master branch.