i don't recall us changing `run()`'s behaviour tbh

J

johnjoz•11/7/23, 6:24 PM

D1 workers paid noob possible: trying to upload a roughly 1.5 gb database with wrangler fails with mysterious node errors, and I’ve followed the sundry blog examples. I can rclone to r2 with impunity. Is there any facility to load a d1 sqlite db from r2?

MMax (@rozenmd)i don't recall us changing `run()`'s behaviour tbh

K

kian•11/7/23, 6:28 PM

I've tried it in local & remote development and

run()

run()

returns

results

results

results

results.

K

kian•11/7/23, 6:28 PM

In Wrangler 3.1.1, you'd get an error thrown saying "Execute returned results" or something similar

K

kian•11/7/23, 6:29 PM

d1-database

K

kian•11/7/23, 6:29 PM

K

kian•11/7/23, 6:33 PM

Miniflare used to check on it's own and throw (hence why I had the issue on an old Wrangler version) but now the API seems to send results rather than an error.

Jjohnjoz D1 workers paid noob possible: trying to upload a roughly 1.5 gb database with w...

M

Max (@rozenmd)OP•11/7/23, 8:16 PM

you might be overloading the db - what exactly are the errors?

J

johnjoz•11/7/23, 8:57 PM

% wrangler d1 execute ipbistrosql --file ./searchtree.sql
FATAL ERROR: v8::ToLocalChecked Empty MaybeLocal

J

johnjoz•11/7/23, 8:57 PM

Plus many more nondescript lines from v8 and node

J

johnjoz•11/7/23, 8:59 PM

If i copy the sql file and truncate at a hundred lines that works as in blogs. But of course is useless. The sql file (generated by dump from sqlite3 file tested locally) is about 21 million lines. The db is about 1.5 gb.

J

johnjoz•11/7/23, 9:06 PM

Perhaps I should rclone the sql file to r2 (i can do that no problem) then write a worker which loads from r2 into d1, but it seems i hit some unintended unchecked limit inside a node dependency or v8 itself (on which i was long ago a contributor via a qualcomm google joint effort)

Jjohnjoz % wrangler d1 execute ipbistrosql --file ./searchtree.sql FATAL ERROR: v8::ToLoc...

M

Matt Silverlock•11/7/23, 9:12 PM

What is your account ID & database ID?

J

johnjoz•11/7/23, 9:23 PM

J

johnjoz•11/7/23, 9:24 PM

1b4eb314bfee79ccebd94f33e9bde8ee

J

johnjoz•11/7/23, 9:24 PM

d6ac38a9-20be-4fb4-9839-02a379d944da

J

johnjoz•11/7/23, 9:25 PM

That says uuid on the wrangler output for d1 list

J

johnjoz•11/7/23, 9:29 PM

I did upload the sqlite3 file to r2 but not the sql dump script

Jjohnjoz Click to see attachment

M

Matt Silverlock•11/7/23, 9:38 PM

This is wrangler failing to parse 1.5GB locally. As an interim fix, you'll need to break up the upload into smaller chunks.

J

johnjoz•11/7/23, 9:40 PM

Thank you

V

Victor•11/8/23, 12:25 AM

Does the sqlite engine inside D1 not support

unhex()

unhex()

unhex()

unhex()?

V

Victor•11/8/23, 12:26 AM

Since it's listed here as a core function https://www.sqlite.org/lang_corefunc.html#unhex

V

Victor•11/8/23, 12:28 AM

Which I find weird because

hex()

hex()

hex()

hex() works just fine

K

kian•11/8/23, 12:32 AM

do you get not authorized or an error about it not existing

V

Victor•11/8/23, 12:32 AM

If I have a uuid stored as binary

BLOB

BLOB

(initially generated with

(randomblob(16))

(randomblob(16))

), this works:

(lower(format('%s-%s-%s-%s-%s', substr(hex(t_id),1,8), substr(hex(t_id),9,4), substr(hex(t_id),13,4), substr(hex(t_id),17,4), substr(hex(t_id),21))))

(lower(format('%s-%s-%s-%s-%s', substr(hex(t_id),1,8), substr(hex(t_id),9,4), substr(hex(t_id),13,4), substr(hex(t_id),17,4), substr(hex(t_id),21))))

(lower(format('%s-%s-%s-%s-%s', substr(hex(t_id),1,8), substr(hex(t_id),9,4), substr(hex(t_id),13,4), substr(hex(t_id),17,4), substr(hex(t_id),21))))

(lower(format('%s-%s-%s-%s-%s', substr(hex(t_id),1,8), substr(hex(t_id),9,4), substr(hex(t_id),13,4), substr(hex(t_id),17,4), substr(hex(t_id),21))))

to get the normal looking utf8 uuid

Kkian do you get not authorized or an error about it not existing

V

Victor•11/8/23, 12:32 AM

{
  result: [],
  success: false,
  errors: [ { code: 7500, message: 'no such function: unhex' } ],
  messages: []
}

{
  result: [],
  success: false,
  errors: [ { code: 7500, message: 'no such function: unhex' } ],
  messages: []
}

{
  result: [],
  success: false,
  errors: [ { code: 7500, message: 'no such function: unhex' } ],
  messages: []
}

{
  result: [],
  success: false,
  errors: [ { code: 7500, message: 'no such function: unhex' } ],
  messages: []
}

V

Victor•11/8/23, 12:35 AM

I make it

import { randomUUID } from 'node:crypto';

export class Helpers {
    public static get generateUuid() {
        const uuid = randomUUID();
        const uuidHex = uuid.replaceAll('-', '');

        return {
            utf8: uuid,
            forBlob: uuidHex,
        };
    }
}

import { randomUUID } from 'node:crypto';

export class Helpers {
    public static get generateUuid() {
        const uuid = randomUUID();
        const uuidHex = uuid.replaceAll('-', '');

        return {
            utf8: uuid,
            forBlob: uuidHex,
        };
    }
}

import { randomUUID } from 'node:crypto';

export class Helpers {
    public static get generateUuid() {
        const uuid = randomUUID();
        const uuidHex = uuid.replaceAll('-', '');

        return {
            utf8: uuid,
            forBlob: uuidHex,
        };
    }
}

import { randomUUID } from 'node:crypto';

export class Helpers {
    public static get generateUuid() {
        const uuid = randomUUID();
        const uuidHex = uuid.replaceAll('-', '');

        return {
            utf8: uuid,
            forBlob: uuidHex,
        };
    }
}

Then I try to insert it with

query: {
    params: [Helpers.generateUuid.forBlob],
    sql: `INSERT INTO some_table (uuid) VALUES (unhex(?))`,
}

query: {
    params: [Helpers.generateUuid.forBlob],
    sql: `INSERT INTO some_table (uuid) VALUES (unhex(?))`,
}

query: {
    params: [Helpers.generateUuid.forBlob],
    sql: `INSERT INTO some_table (uuid) VALUES (unhex(?))`,
}

query: {
    params: [Helpers.generateUuid.forBlob],
    sql: `INSERT INTO some_table (uuid) VALUES (unhex(?))`,
}

K

kian•11/8/23, 12:37 AM

unhex isn’t in the version of SQLite that D1 currently uses

Kkian unhex isn’t in the version of SQLite that D1 currently uses

V

Victor•11/8/23, 12:37 AM

what is the version of sqlite that d1 uses?

not authorized to use function: sqlite_version

not authorized to use function: sqlite_version

K

kian•11/8/23, 12:42 AM

3.40.1

V

Victor•11/8/23, 12:43 AM

nooooo, the version that added it is literally the next one

3.41.0

3.41.0

V

Victor•11/8/23, 12:43 AM

Woah it even adds

base64()

base64()

too...plzzzzzz update to 3.41.0+

V

Victor•11/8/23, 12:45 AM

In that case, how can I format buffer related data through the REST api to output back as a BLOB binary buffer in D1?

VVictor 😭 nooooo, the version that added it is literally the next one `3.41.0`

K

kian•11/8/23, 12:50 AM

There’s an open PR to update it to 3.44.0 so if/when that’s merged and released it’ll be available.

Kkian There’s an open PR to update it to 3.44.0 so if/when that’s merged and released ...

V

Victor•11/8/23, 12:53 AM

Can you send me a link to that PR? I'm not seeing one on

cloudflare/workers-sdk

cloudflare/workers-sdk

K

kian•11/8/23, 12:54 AM

The runtime is at cloudflare/workerd

K

kian•11/8/23, 12:55 AM

https://github.com/cloudflare/workerd/pull/1374

V

Victor•11/8/23, 12:55 AM

lol I just found it literally same second as you sent it

R

Rick•11/8/23, 7:00 AM

Does the sqllite FTS works on D1? Also, are there any options for text search, if my data is distributed amongst all the D1 dbs in my account?

M

MrXO•11/8/23, 7:02 AM

hi

M

MrXO•11/8/23, 7:02 AM

how I can add more column for table?

MMrXO how I can add more column for table?

M

Matthijs•11/8/23, 8:13 AM

you can use migrations to alter existing tables: https://developers.cloudflare.com/d1/platform/migrations/

Migrations · Cloudflare D1 docs

Database migrations are a way of versioning your database. Each migration is stored as an .sql file in your migrations folder. The migrations folder …

R

Rick•11/8/23, 8:58 AM

Thanks! I guess I will have to add an orchestrator in my api which will take the search query, propagate it over the dbs and then merge the results

L

Lila Rest•11/8/23, 10:16 AM

Hey

Any ETA for D1 going out of beta?

LLila Rest Hey 👋 Any ETA for D1 going out of beta?

G

GuillaumeLakano•11/8/23, 10:54 AM

https://blog.cloudflare.com/d1-open-beta-is-here/
« We’re setting a clear goal: we want to make D1 “generally available” (GA) for production use-cases by early next year (Q1 2024). Although you can already use D1 without a waitlist or approval process, we understand that the GA label is an important one for many when it comes to a database (and as do we). »

RRick Does the sqllite FTS works on D1? Also, are there any options for text search, i...

M

Matt Silverlock•11/8/23, 11:21 AM

FTS5 works in D1.

GGuillaumeLakano https://blog.cloudflare.com/d1-open-beta-is-here/ « We’re setting a clear goal: ...

L

Lila Rest•11/8/23, 11:22 AM

Thanks Guillaume for your answer!
In fact I'm relying on Cloudflare Pages so it'd be awesome to be able to use D1 as an edge database.
The main point is not the GA, but the 2GB limit per database. Is that something that can be increased at that point?

K

kian•11/8/23, 11:24 AM

This is a beta-only limitation. The maximum storage per-database, storage per-account and number of databases will automatically increase for paid plans during the course of D1’s public beta.

K

kian•11/8/23, 11:24 AM

All limits tagged with beta on https://developers.cloudflare.com/d1/platform/limits/ will be raised over time.

Kkian All limits tagged with beta on <https://developers.cloudflare.com/d1/platform/li...

L

Lila Rest•11/8/23, 11:26 AM

Thanks, any ETA on raising the storage per-database limit?
We cannot take the risk to develop on D1 if the limit is not raised once we go in production (late 2023)

K

kian•11/8/23, 11:27 AM

I’m not aware of a specific timeline, although Matt may be able to share one. It’s currently a limit that can’t be raised by exception (which is to say it applies to everyone and isn’t a case-by-case basis limit).

i don't recall us changing `run()`'s behaviour tbh

Similar Threads

Similar Threads

Similar Threads