hi guys I have a table with teacher_image VARBINARY(1048576) NOT NULL, every time I insert a binary

hi guys I have a table with teacher_image VARBINARY(1048576) NOT NULL, every time I insert a binary into this table the whole database collapse I cant interact with the database anymore someone know why?

Ttalfiz hi guys I have a table with teacher_image VARBINARY(1048576) NOT NULL, every tim...

Matt Silverlock•11/29/23, 4:14 PM

What errors are you seeing?

talfizOP•11/29/23, 4:17 PM

after successfuly inserting into the table (I can see the values in the UI) if I try to edit / insert from the ui or with code i get the error FOREIGN KEY constraint failed (Code: 7500) , and If I try to delete the whole table from the UI Its telling me to try again later (its the same for two days)

talfizOP•11/29/23, 4:19 PM

I believe its a bug

Max (@rozenmd)•11/29/23, 4:21 PM

the dashboard doesn't support editing binary

talfizOP•11/29/23, 4:21 PM

I dont try to edit the binary I try to edit a completly diffrent table

talfizOP•11/29/23, 4:22 PM

Its the same message for all the tables

talfizOP•11/29/23, 4:22 PM

Even tables with no binary

Mmushang98 I've tried this, but got error with the "env" variable. Any suggestion ?

Rob M.•11/29/23, 4:41 PM

Can you send a snippet with your "env" usage?

Rob M.•11/29/23, 4:44 PM

By the way, you should use 'context.env.*' instead of 'env.*' in Page workers. Maybe that's the issue.

talfizOP•11/29/23, 5:56 PM

Hi guys I fixed the bug what I tried to do is to delete a user when a teacher is still refrenceing him to do that I should add ON DELETE CASCADE to the table thanks for however tried to help

MMax (@rozenmd)So far the plan is just for a single writer

rodrigo•11/30/23, 9:57 AM

You talk below about cases where people create 1db per user.. But to be more efficient, if I want to create 1 db per region (enam, wnam...) and redirect each user to the closest/fastest db, is there a way to programatically do that? with the CF-IPCountryCF-IPCountry header ? but should I build a table with states on the east/west cost ... and redirect based on it? or is there a way to get a ping to the D1 center location?

rodrigo•11/30/23, 9:59 AM

And regarding Asia-Pacific region .. that is a region too big (Australia, China , India ...). Is there (or will there) a way to allocate D1 dbs to smaller areas within the region?

Rrodrigo You talk below about cases where people create 1db per user.. But to be more eff...

Rob M.•11/30/23, 10:18 AM

I'm not sure about cloudflare, but you can use any IP database for that like this one:
https://www.maxmind.com/en/geoip-databases

MaxMind GeoIP® Databases | MaxMind

IP geolocation and network data from the creator of GeoIP. Download and host
the data locally to eliminate network latency and per-query charges.

Rrodrigo You talk below about cases where people create 1db per user.. But to be more eff...

mrwind•11/30/23, 11:03 AM

request on worker will include request.cf.continent and request.cf.country . maybe you can use it and map to d1 region.

Rrodrigo You talk below about cases where people create 1db per user.. But to be more eff...

Max (@rozenmd)•11/30/23, 12:08 PM

the Workers platform is built in such a way that you should never need to make decisions like that - we know where your requests are coming from and should route them to the nearest replica for you, without your input (except for cases where you've opted into keeping data in certain regions)

Max (@rozenmd)•11/30/23, 12:09 PM

though I understand it's annoying to have slower requests atm while replication hasn't been shipped yet

MMax (@rozenmd)the Workers platform is built in such a way that you should never need to make d...

rodrigo•11/30/23, 2:10 PM

I was talking more about the case where you want to have several D1 dbs so that you have several writers. In this case, there is a way to redirect users to its closer db writer ? (example I want Australian users to use a master D1 db in Australia instead of having ig generating anywhere in the Asia-Pacific region)

Rrodrigo I was talking more about the case where you want to have several D1 dbs so that ...

Max (@rozenmd)•11/30/23, 2:20 PM

then yeah, request.cfrequest.cf has the data you need: https://developers.cloudflare.com/workers/runtime-apis/request/#incomingrequestcfproperties

Max (@rozenmd)•11/30/23, 2:53 PM

...yet

AAkthar What is the limit on number of databases we can create? i can see it's 50k in do...

Matt Silverlock•11/30/23, 6:54 PM

Support for more than 100,000+ databases for Workers for Platforms users — there’s no limit, but if we said “unlimited” you might not believe us — on top of the 50,000 databases per account that D1 already enables.

From https://blog.cloudflare.com/d1-open-beta-is-here/

The Cloudflare Blog

D1: open beta is here

D1 is now in open beta, and the theme is “scale”: with higher per-database storage limits and the ability to create more databases, we’re unlocking the ability for developers to build production-scale applications on D1

GuillaumeLakano•11/30/23, 8:47 PM

Hi! When D1 return an error because of a constraint, we doesn't receive the 'meta' object with the rows_read / rows_written, could you confirm we're not billed in this kind of errors please?

GGuillaumeLakano Hi! When D1 return an error because of a constraint, we doesn't receive the 'met...

Matt Silverlock•11/30/23, 11:44 PM

It can depend - this isn’t final yet and there may be cases where we should count rows read. Note that no one is actively billed just yet.

di_uthred•12/1/23, 4:09 AM

Hi. I want to upgrade the schema. I understand that ALTER is not supported? How do I go about executing a SQL query to move the data into the new altered table? Do I need to use wrangler to do that, or is there a way to do it from the UI?

kian•12/1/23, 4:18 AM

SQLite supports renaming a table, adding a new column and renaming an existing column - so that subset of ALTER TABLEALTER TABLE is supported.

Vikash•12/1/23, 5:34 AM

It seems the JSON parse function doesn't work in query - I have valuevalue column to store some JSON data, but when I query with json(value)json(value) it return the string instead the parsed JSON.

 const sqlQuery = ctx.env.DB.prepare(
    `
      SELECT key, json(value) as value, created_at, updated_at, expire_at 
      FROM Example
      Where account_id = ?
      LIMIT ?, ?`
  ).bind(authUser.account_id, offset, limit);
  const { results } = await sqlQuery.all();

 const sqlQuery = ctx.env.DB.prepare(
    `
      SELECT key, json(value) as value, created_at, updated_at, expire_at 
      FROM Example
      Where account_id = ?
      LIMIT ?, ?`
  ).bind(authUser.account_id, offset, limit);
  const { results } = await sqlQuery.all();

Result

[
    {
        "key": "rMDrWHc",
        "value": "{\"url\":\"https://agenty.com\",\"params\":{},\"key\":\"rMDrWHc\"}",

        "created_at": "2023-12-01 05:27:45",
        "updated_at": null,
        "expire_at": null
    }
]

[
    {
        "key": "rMDrWHc",
        "value": "{\"url\":\"https://agenty.com\",\"params\":{},\"key\":\"rMDrWHc\"}",

        "created_at": "2023-12-01 05:27:45",
        "updated_at": null,
        "expire_at": null
    }
]

I was expecting the JSON to be parsed using json(value) json(value) function given in docs - https://developers.cloudflare.com/d1/learning/querying-json/#supported-functions

Query JSON · Cloudflare D1 docs

D1 has built-in support for querying and parsing JSON data stored within a database. This enables you to:

kian•12/1/23, 6:12 AM

The json(X) function verifies that its argument X is a valid JSON string and returns a minified version of that JSON string

kian•12/1/23, 6:12 AM

It doesn't turn it into a JS object, you need to do that yourself.

kian•12/1/23, 6:12 AM

It's a SQLite built-in function rather than something D1 specific.

Kkian It doesn't turn it into a JS object, you need to do that yourself.

Vikash•12/1/23, 6:58 AM

you mean the returned value can't be used in query to print in output?

ken•12/1/23, 12:55 PM

Hey

Does anyone know the limit of the number of statements for Batch Statements (db.batch()db.batch()) on D1?

https://developers.cloudflare.com/d1/platform/client-api/#dbbatch|

I can see that the Maximum SQL statement length is 100,000 bytes (100 KB)100,000 bytes (100 KB) and it should apply. But is there any other limits as well?

https://developers.cloudflare.com/d1/platform/limits/

Kken Hey 👋 Does anyone know the limit of the number of statements for Batch Statemen...

Max (@rozenmd)•12/1/23, 1:02 PM

should be the same as a regular db.run/all, except the limits apply individually to the queries

Max (@rozenmd)•12/1/23, 1:02 PM

all .batch does is save you the round trips of sending your sql+params for several queries

MMax (@rozenmd)should be the same as a regular db.run/all, except the limits apply individually...

Hello, I’m Allie!•12/1/23, 1:10 PM

Isn’t that it though? If limits are applied to each query individually, how many queries can you make?

HHello, I’m Allie!Isn’t that it though? If limits are applied to each query individually, how many...

Max (@rozenmd)•12/1/23, 1:15 PM

I'm not sure we've tested it tbh, the answer is probably "however many queries it takes to crash the DO"

Max (@rozenmd)•12/1/23, 1:15 PM

each query is run sequentially though so it's probably a lot

MMax (@rozenmd)all .batch does is save you the round trips of sending your sql+params for sever...

ken•12/1/23, 1:44 PM

that makes sense, my query is throttling anyway so should be fine ,but wanted to see the upper limit. Thank you!

Original message was deleted

Max (@rozenmd)•12/1/23, 2:01 PM

that's an alpha db btw

Max (@rozenmd)•12/1/23, 2:30 PM

thats awesome

Village (Josef)•12/2/23, 4:49 PM

What's the fastest way to wipe a database and remigrate it all

Village (Josef)•12/2/23, 4:49 PM

I'd like to avoid having to manually delete 17 tables

senojsitruc•12/2/23, 4:53 PM

I think I managed to blow up my D1 database (4871ae1f-6808-465d-a8ea-8b404d0c4d42) yesterday between 8 - 9pm (ET). I created a support ticket (#3047705). The dashboard times out trying to list the database and says, "Failed to reach database. Please try again later." I had been very successfully testing it with a limited load and then a couple days ago I started trying additional load. And then it died. That could be coincidental, of course. I restored the data to a new database instance and got back up-and-running. If I've done something incorrect with D1, I'd like to know more details so that I can prevent this from happening again. Thanks! (D1 is great, btw.)

Village (Josef)•12/2/23, 5:21 PM

Just wipe and go

Village (Josef)•12/2/23, 5:21 PM

I ended up finding the --file for wrangler and just wrote a bunch of drop table statements

MMax (@rozenmd)should be the same as a regular db.run/all, except the limits apply individually...

Fun Miles•12/2/23, 6:15 PM

Is one batch query a single API call? I am hitting a maximum API call rate limit for a single worker in a CRON worker. Would batching save me?
Is it possible to batch SELECTSELECT statements?

Ssenojsitruc I think I managed to blow up my D1 database (4871ae1f-6808-465d-a8ea-8b404d0c4d4...

Matt Silverlock•12/2/23, 6:25 PM

Well take a look. There’s nothing you should be able to do that results in your database being “stuck” - we consider those cases bugs

Fun Miles•12/2/23, 6:42 PM

I can confirm I am able to have an array of 'SELECT' in there. But you cannot do DB.prepare(statment).bind(arguments...).first()DB.prepare(statment).bind(arguments...).first(). So the result will be an array.

Fun Miles•12/2/23, 6:44 PM

But how about the number of API calls it is counted as from the point of view of not getting an error of Too many API calls from a single worker ?

Fun Miles•12/2/23, 6:45 PM

I know if will count as as many row retrieval as I have SELECT statement (1 result per SELECT), but that's not an issue.

hi guys I have a table with teacher_image VARBINARY(1048576) NOT NULL, every time I insert a binary

Similar Threads