FusionAuth
    • Home
    • Categories
    • Recent
    • Popular
    • Pricing
    • Contact us
    • Docs
    • Login

    Retrieving ids from FusionAuth database tables

    Scheduled Pinned Locked Moved
    Q&A
    analytics database query sql
    1
    2
    1.6k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • danD
      dan
      last edited by dan

      For analytical purposes I want to run query directly against the Fusionauth DB (I am running this locally).

      But the id fields are stored as binary blobs (probably because they are guids).

      Do you have a function (java or sql) I can use to convert this blob into a string?

      Duplicated and expanded from https://stackoverflow.com/questions/64253472/how-can-i-get-string-from-the-blob-an-id-stored-in-fusionauth-db

      --
      FusionAuth - Auth for devs, built by devs.
      https://fusionauth.io

      1 Reply Last reply Reply Quote 0
      • danD
        dan
        last edited by dan

        Hiya,

        We don't guarantee any database level compatibility (which is why we recommend doing everything through the API, which does have those guarantees).

        But here's an option. I'm assuming you are using MySQL. FusionAuth uses UUIDs for unique Ids, and in MySQL we store these as BINARY(16).

        If you want to select this value in a human readable form, you can perform a select such as SELECT HEX(id) FROM table_name.

        If you want to select this column and deserialize it into a Java UUID type, you can use code similar to the following:

        public UUID fromByteArray(byte[] ba) {
          long msb = 0;
          long lsb = 0;
          for (int i = 0; i < 8; i++) {
            msb = (msb << 8) | (ba[i] & 0xff);
          }
          for (int i = 8; i < 16; i++) {
            lsb = (lsb << 8) | (ba[i] & 0xff);
          }
          return new UUID(msb, lsb);
        }
        

        Another option, depending on your data size, would be to export all the data using the relevant APIs, and import it into your analytics database.

        --
        FusionAuth - Auth for devs, built by devs.
        https://fusionauth.io

        1 Reply Last reply Reply Quote 0
        • First post
          Last post