Fork me on GitHub
#xtdb
<
2022-02-27
>
genekim01:02:36

Hello, all — having fun playing with XTDB, although holy cow, getting JDBC to work took a couple of hours, due to I think JVM timezone issues causing :valid-time issues. I’ll confirm that I actually fixed, and propose a doc change. (All my transactions were being put with a valid-time 8 hours in the future!) A question that I’m hoping someone will have a quick answer to: I have Lucene searches working, and am trying to get multi-field searches working. (It’s fantastic having the multi-field test cases available — I used it to learn how to do namespaced key specification.) But my queries with even the simplest multi-field query aren’t working, resulting in this error: I’ve included the special indexer, as per docs. Does anyone have any tips? Many thanks!!

(xt/start-node
      {:xtdb/tx-log              (kv-store "data/dev/tx-log")
       :xtdb/document-store      (kv-store "data/dev/doc-store")
       :xtdb/index-store         (kv-store "data/dev/index-store")
       :xtdb.lucene/lucene-store {:indexer 'xtdb.lucene.multi-field/->indexer
                                  :db-dir "data/dev/lucene-dir"}})))

(xt/q (xt/db xtdb-node)
    {:find '[?e]
     :where '[[(lucene-text-search "firstname:James OR surname:preston") [[?e]]]]})

Execution error (IllegalArgumentException) at xtdb.error/illegal-arg (error.clj:12).
Clause refers to unknown variable: lucene-text-search {:pred {:pred-fn lucene-text-search, :args ["firstname:James OR surname:preston"]}, :return [:relation [[?e]]]}

refset12:02:43

Hello! 🙂 > holy cow, getting JDBC to work took a couple of hours, due to I think JVM timezone issues causing :valid-time issues. I’ll confirm that I actually fixed, and propose a doc change. (All my transactions were being put with a valid-time 8 hours in the future!) 🙈 ah, sorry to hear that! Any and all suggestions would be welcome - no need to create a PR if you're stretched though, just a pointer or two would almost certainly help. I can't see anything obviously wrong with your snippet, and I also just tested it locally (commenting out all the persistence-related config) and it worked fine :thinking_face: Is the xtdb-node used in the query definitely the same as the one being defined as shown? The error suggests that the xtdb.lucene.multi-field/->indexer module hasn't been initialised (since one of the key things it does it register the lucene-text-search pred)

genekim20:02:27

Thank you so much — I’ll write up my path so far, and will try lucene-text-search on my simpler local storage shortly! These issues aside, it’s been a delight so far!

🙌 1
👍 1
refset22:02:29

That's always great to hear, thank you for the feedback 🙏

genekim23:02:33

It works! I had forgotten to put the new indexer in the JDBC map at 1am that night. I was able to confirm that it works using local storage, and then eventually got it to work with JDBC — it required deleting all the temporary directories. Many thanks! (A lengthier experience report coming, describing some places where I got stuck, but super happy with experiments so far!)

🙂 1
genekim21:02:29

Here’s the strange behavior on transactions using JDBC, running Graal 21 Java 17 in Rosetta mode on Apple M1. I copied this code from https://gist.github.com/alndvz/89931bb8f16867fd5f2e28d36ff85be9 Everything works as expected when running with local datastores, but strange things happened when I used JDBC on MySQL. In short, the transaction time is 8 hours in the future. So, for hours, I was trying to figure out why I couldn’t see the effects of any of my transactions. I finally noticed that when no valid-time is specified in the transaction, the :tx-time returned from the submit-tx is 8 hours in the future. This is when no timezone is specified for the JVM. PS: (I tried adding -Duser.timezone=UTC, but then await-tx never returns — presumably it will finally return in 8 hours? I see the transaction in MySQL.) See below.

(defn prepare-docs [docs]
  (mapv (fn [doc]
          ;[::xt/put doc #inst "2021-01-04T18:00:00.000-00:00"]) docs))
          ;[::xt/put doc]) docs
          [::xt/put doc (java.util.Date.)]) docs))

(defn prepare-docs-bare [docs]
  (mapv (fn [doc]
          ;[::xt/put doc #inst "2021-01-04T18:00:00.000-00:00"]) docs))
          [::xt/put doc]) docs))
          ;[::xt/put doc (java.util.Date.)]) docs)

(def tx (xt/submit-tx db/xtdb-node (prepare-docs-bare products-1)))
=> #'genek.xtdbtest/tx
tx
=> #:xtdb.api{:tx-id 6672, :tx-time #inst"2022-02-28T04:52:15.798-00:00"}
(java.util.Date.)

;;; ^^^. note the tx-time!  8 hours in future

=> #inst"2022-02-27T20:52:22.414-00:00"

;;; ^^^ current time
Note that entity-history doesn’t include :tx-id 6672:
(def history (xt/entity-history (xt/db db/xtdb-node) :towel1 :desc))
=> #'genek.xtdbtest/history
history
=>
[#:xtdb.api{:tx-time #inst"2022-02-27T20:49:46.884-00:00",
            :tx-id 6670,
            :valid-time #inst"2022-02-27T20:49:46.884-00:00",
            :content-hash #xtdb/id"bb15c9cf37c646f76e90105643e7dcec8ad86b2e"}
 #:xtdb.api{:tx-time #inst"2022-02-27T20:49:36.997-00:00",
            :tx-id 6669,
            :valid-time #inst"2022-02-27T20:49:36.997-00:00",
            :content-hash #xtdb/id"bb15c9cf37c646f76e90105643e7dcec8ad86b2e"}]

🙏 1
genekim21:02:59

My workaround was to change prepare-docs to add current time as valid time explicitly.

genekim21:02:09

(I’m using Graal Java 17 just because I had it around, when I was playing with native image creation. It only works in x86 mode…. I haven’t tried another x86 JVM.)

genekim21:02:34

Thx for asking about this @U899JBRPF, and keep up the great work!

🙂 1
refset22:02:20

Interesting...so the default valid time is inferred from the transaction time, which is generated by MySQL during submit-tx and explicitly stored as UTC, thereby losing any timezone information. This implies your JVM and MySQL are disagreeing over UTC itself (I suppose you may be running them on different hosts, but it shouldn't be that out of whack). After a little digging into to it, I think the problem may be that we should be using UTC_TIMESTAMP instead of CURRENT_TIMESTAMP here: https://github.com/xtdb/xtdb/blob/a3d6ce5b113948df0dfcf6c99d66b4c29838b782/modules/jdbc/src/xtdb/jdbc/mysql.clj#L35 (since CURRENT_TIMESTAMP is timezone-sensitive, based on https://dba.stackexchange.com/questions/62466/mysql-timestamp-timezone-handling) I've opened an issue for this here: https://github.com/xtdb/xtdb/issues/1716 Thank you for the concise write-up!

genekim23:02:58

Oh, I forgot to mention — I’m using JDBC and MySQL, connecting to a MySQL instance in Google Cloud SQL. So absolutely they are on different machines. I presume Cloud SQL is running on UTC, and I typically keep everything in local time — I had never tried to run my dev machine in UTC until today. Thx!

1
👍 1
refset14:03:02

Hi again @U6VPZS1EK - please see the team's investigation notes on the issue: https://github.com/xtdb/xtdb/issues/1716 - tl:dr; this is definitely a subtle gotcha, but we've found a solid config workaround (as I understand it, anyway). If you're happy with that as the resolution(?) I'll close the issue and add a warning in the docs for others 🙂

genekim21:03:11

This looks great! I'll try it out later today or tomorrow — and for the record, I am not an expert in either MySQL or timezones! 😂

👍 1
genekim21:03:37

Thanks to you and the team!

🙂 2
genekim17:03:48

Hi, @U899JBRPF — it works! I’m delighted, and I wrote it up here: https://github.com/xtdb/xtdb/issues/1716#issuecomment-1062040995

🙏 1
refset22:03:58

Excellent, the follow-up is much appreciated ☺️