I am trying to take backup of my D1 database but getting following issue Please guide me if anyone h

RRavi Kumar Choudhary I am trying to take backup of my D1 database but getting following issue Please ...

C

Cyb3r-Jak3•10/26/23, 1:13 PM

Backups aren’t supported in the beta system.

Original message was deleted

M

Matt Silverlock•10/26/23, 1:17 PM

This isn’t true.

RRavi Kumar Choudhary I am trying to take backup of my D1 database but getting following issue Please ...

M

Matt Silverlock•10/26/23, 1:17 PM

https://developers.cloudflare.com/d1/learning/time-travel/

Time Travel and backups · Cloudflare D1 docs

Time Travel is D1’s approach to backups and point-in-time-recovery, and allows you to restore a database to any minute within the last 30 days.

S

Steved•10/26/23, 3:27 PM

D1 feature request: Some option for Unicode collation (specifically non-ASCII case insensitivity)

Could we have the ICU extension enabled?, see https://sqlite.org/src/dir/ext/icu
Sadly, this could affect existing indexes (as discussed at the bottom of the link).
So, another potential option is to implement a custom collation sequence (via create_collation - https://www.sqlite.org/c3ref/create_collation.html ). For example, add a new 'UNICODE_NOCASE' collation that could then be used on columns or specific where clauses with

... COLLATE UNICODE_NOCASE

... COLLATE UNICODE_NOCASE

... COLLATE UNICODE_NOCASE

... COLLATE UNICODE_NOCASE.

I'm currently adding duplicate columns with the data passed through Javascript's toLower(), but that's a PITA.
I understand it is ultimately an Sqlite quirk. However, Sqlite provides the above two options, but consumers of D1 have no control of those extensions/APIs which is why some assistance from D1 would be helpful.

Stanislav Trofimchuk wrote a really good background piece, with lots of discussion and example showing use of custom collation (from Python): https://shallowdepth.online/posts/2022/01/5-ways-to-implement-case-insensitive-search-in-sqlite-with-full-unicode-support/ and a great follow up post specifically on performance: https://shallowdepth.online/posts/2022/01/performance-of-application-defined-functions-and-collations-in-sqlite/

SSteved D1 feature request: Some option for Unicode collation (specifically non-ASCII ca...

D

DaniFoldi•10/26/23, 3:42 PM

I second this, internationalized case insensitivity would be useful, instead of keeping a second column where all characters are ascii-mapped in advance so case doesn't matter

S

switz•10/26/23, 7:55 PM

is there a max file size for import/execute?

S

switz•10/26/23, 7:55 PM

getting

  Cannot create a string longer than 0x1fffffe8 characters

  Cannot create a string longer than 0x1fffffe8 characters

  Cannot create a string longer than 0x1fffffe8 characters

  Cannot create a string longer than 0x1fffffe8 characters

on a 1gb database

U

Unsmart•10/26/23, 8:01 PM

The limits are documented here: https://developers.cloudflare.com/d1/platform/limits/

Limits · Cloudflare D1 docs

Many of these limits will increase during D1’s public beta. Join the #d1-beta channel in the Cloudflare Developer Discord or subscribe to D1’s public …

S

switz•10/26/23, 8:22 PM

ah so I'm hitting

> Maximum SQL statement length

> Maximum SQL statement length

> Maximum SQL statement length

> Maximum SQL statement length?

S

switz•10/26/23, 8:23 PM

anyone have a nice cli command to split the requests into 100kb chunks?

Sswitz ah so I'm hitting `> Maximum SQL statement length`?

O

omar4289•10/26/23, 9:49 PM

Just wondering what request do you make that goes beyond 100kb

S

switz•10/26/23, 9:49 PM

I'm trying to import a sqlite db that has 1gb of data into d1

S

switz•10/26/23, 9:50 PM

each sql statement is just a few bytes, but the whole file is 1gb

D

Dahoom•10/26/23, 9:50 PM

ask gippity to write a python script that batches the sql file into ~99kb chunks

S

switz•10/26/23, 9:52 PM

oh just realized free dbs are limited to 500mb, so may have to come up with another solution unless I want to pay

S

switz•10/26/23, 9:52 PM

this is not mission critical, will have to re-think

Sswitz oh just realized free dbs are limited to 500mb, so may have to come up with anot...

O

omar4289•10/26/23, 10:12 PM

Yeah, also if you have indexes, that might take some space too, and now we are only limited to 2gb per db, so not even sure you could migrate it all

C

ChrisBrocklesby•10/27/23, 1:49 AM

Its really important to be able to download a D1 database, with this new TimeTravel feature the ability has seemed to disappear... This seems like vendor lock-in... Any tricks or workarounds to download a large D1 database ?

DDahoom nora's project should be linked in the docs

D

Dahoom•10/27/23, 1:50 AM

^

CChrisBrocklesby Its really important to be able to download a D1 database, with this new TimeTra...

C

Cyb3r-Jak3•10/27/23, 1:50 AM

You can use https://github.com/nora-soderlund/cloudflare-d1-backups to backup your database and then download that

D

Dahoom•10/27/23, 1:51 AM

and he beat me to it

CCyb3r-Jak3 You can use https://github.com/nora-soderlund/cloudflare-d1-backups to backup yo...

C

ChrisBrocklesby•10/27/23, 1:57 AM

Hi, Does that work with databases on the new storage subsystem in D1 ?

D

Dahoom•10/27/23, 1:59 AM

queries the DB with select and writes it to an sql file to put it simply

C

ChrisBrocklesby•10/27/23, 2:01 AM

@Cyb3r-Jok3 @Dahoom Excellent solution for now, Thank you both... But hopefully this becomes native option in the Cloudflare Dashboard (or Wrangler CLI) in the future.

CChrisBrocklesby @Cyb3r-Jok3 @Dahoom Excellent solution for now, Thank you both... But hopefully ...

C

Cyb3r-Jak3•10/27/23, 2:03 AM

I believe it is planned to be added

C

captain•10/27/23, 4:52 AM

odd thing occuring...that seems new and I can't explain it

C

captain•10/27/23, 4:53 AM

i'm inserting data but on the primary key, if i insert a number like: 9440344, the database saves it as 9440344.0

C

captain•10/27/23, 4:54 AM

await env.MYDATABASE_DB.prepare(`insert into quotes (order_id, location_name, location_id, status, type, vendor, date, date_exp, vendor_order_id, vendor_order_body) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`).bind(parseInt(key), raw_data_post.orders[0].restaurant_name, customer_id, 'pending', type, best_auto_vendor, JSON.stringify(timestamp1), JSON.stringify(timestamp1), 'notsure', results).run()

await env.MYDATABASE_DB.prepare(`insert into quotes (order_id, location_name, location_id, status, type, vendor, date, date_exp, vendor_order_id, vendor_order_body) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`).bind(parseInt(key), raw_data_post.orders[0].restaurant_name, customer_id, 'pending', type, best_auto_vendor, JSON.stringify(timestamp1), JSON.stringify(timestamp1), 'notsure', results).run()

await env.MYDATABASE_DB.prepare(`insert into quotes (order_id, location_name, location_id, status, type, vendor, date, date_exp, vendor_order_id, vendor_order_body) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`).bind(parseInt(key), raw_data_post.orders[0].restaurant_name, customer_id, 'pending', type, best_auto_vendor, JSON.stringify(timestamp1), JSON.stringify(timestamp1), 'notsure', results).run()

await env.MYDATABASE_DB.prepare(`insert into quotes (order_id, location_name, location_id, status, type, vendor, date, date_exp, vendor_order_id, vendor_order_body) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`).bind(parseInt(key), raw_data_post.orders[0].restaurant_name, customer_id, 'pending', type, best_auto_vendor, JSON.stringify(timestamp1), JSON.stringify(timestamp1), 'notsure', results).run()

D

Dahoom•10/27/23, 4:54 AM

probably an sqlite thing, might be saving int as double for some reason?

D

Dahoom•10/27/23, 4:55 AM

check the PRAGMA config

DDahoom probably an sqlite thing, might be saving int as double for some reason?

C

captain•10/27/23, 4:55 AM

whats weird is that i didn't notice

C

captain•10/27/23, 4:55 AM

CREATE TABLE quotes (order_id text PRIMARY KEY,location_id text,status text,type text,vendor text,date integer,date_exp integer,vendor_order_id text,vendor_order_body text);

CREATE TABLE quotes (order_id text PRIMARY KEY,location_id text,status text,type text,vendor text,date integer,date_exp integer,vendor_order_id text,vendor_order_body text);

CREATE TABLE quotes (order_id text PRIMARY KEY,location_id text,status text,type text,vendor text,date integer,date_exp integer,vendor_order_id text,vendor_order_body text);

CREATE TABLE quotes (order_id text PRIMARY KEY,location_id text,status text,type text,vendor text,date integer,date_exp integer,vendor_order_id text,vendor_order_body text);

D

Dahoom•10/27/23, 4:57 AM

which column do you get the number from?

D

Dahoom•10/27/23, 4:59 AM

since a lot of it is being saved as text it might be the way js is parsing the values before being inserted into the db

C

captain•10/27/23, 4:59 AM

i'm checking the number in console before it gets entered and not seeing the .0

C

captain•10/27/23, 5:00 AM

and i'm also doing parseInt(key)

DDahoom which column do you get the number from?

C

captain•10/27/23, 5:03 AM

any other ideas?

C

captain•10/27/23, 5:03 AM

i'm dumbfounded on this

C

captain•10/27/23, 5:04 AM

wondering if something changed on the cloudflare side

D

Dahoom•10/27/23, 5:04 AM

order_id is the primary key using text

C

captain•10/27/23, 5:04 AM

yes

C

captain•10/27/23, 5:07 AM

https://stackoverflow.com/questions/23157411/using-text-as-a-primary-key-in-sqlite-table-bad discussion says there shouldn't be any performance issues

Stack Overflow

Using text as a primary key in SQLite table bad?

Is it bad to have text as a primary key in an SQLite database? I heard that it's bad for performance reasons, is this true? And will the rowid be used as the actual primary key in such a case?

D

Dahoom•10/27/23, 5:07 AM

I'm not saying that I'm just wondering if THATS whats causing the issue

D

Dahoom•10/27/23, 5:08 AM

and dont trust stackoverflow

D

Dahoom•10/27/23, 5:08 AM

its like citing Wikipedia

C

captain•10/27/23, 5:08 AM

ha maybe so, but this probably is a bug? maybe? Should I his cloudflare staff on this?

D

Dahoom•10/27/23, 5:11 AM

most likely a PRAGMA issue to do with how the value is being represented in sqlite

D

Dahoom•10/27/23, 5:14 AM

try to convert the value to a string before inserting it, at least thats what gippity says might work https://decide.gg/s/pGbt

D

Dahoom•10/27/23, 5:15 AM

A more pressing question is why you would use the text format when saving only integers?

DDahoom try to convert the value to a string before inserting it, at least thats what gi...

C

captain•10/27/23, 5:21 AM

that worked..i feel foolish. Probably still should be considered a bug with D1, but the workaround worked!

I am trying to take backup of my D1 database but getting following issue Please guide me if anyone h

Similar Threads

Similar Threads

Similar Threads