I have enough disk space. I just limit the database file size artificially
for testing purpose as you can see.
There is no problem of privilege and there is nothing else than the code I
sent. No other access to the DB.
I'm using sqlite 3.16.2
On Fri, Apr 5, 2019 at 3:59 PM Chris Locke <sql...@chrisjlocke.co.uk> wrote:
> Arthur - are you running SQLite in parallel runs?
> If you access the database file using the sqlite3 command-line tool, and
> try to execute the same SQL commands, do you get the same error ?
>
> SQLite makes a temporary 'journal' file while it's working. I think that,
> on your platform, by default it will be in the same directory as the
> database file. Does your application have enough privileges to create new
> files in that directory ?
> What version of SQLite are you using? It might be an old version.
> Also, removing rows doesn't necessarily remove space in the database file.
> If you're running out of disk space, it could be you need to vacuum your
> database file. How large is the database? How much disk space do you have
> left?
>
>
> Thanks,
> Chris
>
> On Fri, Apr 5, 2019 at 1:46 PM Arthur Blondel <arthur5blon...@gmail.com>
> wrote:
>
> > OK, I wasn't clear.
> > I'm limited in space so when the DB is full (when sqlite3_exec() returns
> > SQLITE_FULL when I try to insert a new row), I remove the oldest row and
> > retry to insert the new one.
> > The data is always the same. That's why removing one row should be enough
> > to insert a new one.
> > My problem is that some times I need to remove many rows to add one new
> > one.
> > This is basically my code:
> >
> > main()
> > {
> > sqlite3* db;
> > int rc;
> > char *err_msg = 0;
> > int counter;
> > bool full = false;
> > int id;
> >
> > /* --- Create DB --- */
> > rc = sqlite3_open("db_file.db", &db);
> > printf("1. rc = %d\n", rc);
> >
> > rc = sqlite3_exec(db,
> > "CREATE TABLE IF NOT EXISTS data_table"
> > "(id INTEGER PRIMARY KEY AUTOINCREMENT, col1 INTEGER,
> col2
> > INTEGER, col3 INTEGER)",
> > 0, 0, &err_msg);
> > printf("2. rc = %d\n", rc);
> >
> > /* --- Limit database size to 50 K --- */
> > rc = sqlite3_exec(db, "PRAGMA page_size=1024", 0, 0, &err_msg);
> > printf("3. rc = %d\n", rc);
> > rc = sqlite3_exec(db, "PRAGMA max_page_count=50", 0, 0, &err_msg);
> > printf("4. rc = %d\n", rc);
> > rc = sqlite3_exec(db, "VACUUM", 0, 0, &err_msg); // resize file
> > printf("5. rc = %d\n", rc);
> >
> >
> > /* --- Fill DB --- */
> > for (int i = 0 ; i < 5000 ; i++) {
> > counter = 0;
> > do {
> > rc = sqlite3_exec(db,
> > "INSERT INTO data_table"
> > "(col1, col2, col3) VALUES(1, 2, 3)",
> > 0, 0, &err_msg);
> >
> > if (rc == SQLITE_FULL) {
> > if (!full) {
> > printf("%d - DB full\n", id);
> > full = true;
> > }
> > counter++;
> > // delete oldest row
> > int stat = sqlite3_exec(db,
> > "DELETE FROM data_table WHERE id IN "
> > "(SELECT id FROM data_table ORDER BY id
> LIMIT
> > 1)",
> > 0, 0, &err_msg);
> > if (stat != SQLITE_OK) {
> > printf("Delete error %d\n", stat);
> > }
> > } else if (rc == SQLITE_OK) {
> > id = sqlite3_last_insert_rowid(db);
> > } else /*if (rc != SQLITE_OK)*/ {
> > printf("Insert error %d\n", rc);
> > }
> > } while (rc == SQLITE_FULL);
> >
> > if (counter > 2) {
> > printf("%d - %d rows was removed\n", id, counter);
> > }
> > }
> >
> > printf("close -> %d\n", sqlite3_close(db));
> > }
> >
> >
> > Following the output:
> >
> > 1. rc = 0
> > 2. rc = 0
> > 3. rc = 0
> > 4. rc = 0
> > 5. rc = 0
> > 3959 - DB full
> > 3960 - 109 rows was removed
> > 4044 - 92 rows was removed
> > 4128 - 86 rows was removed
> > 4212 - 85 rows was removed
> > 4296 - 85 rows was removed
> > 4380 - 84 rows was removed
> > 4464 - 84 rows was removed
> > 4548 - 84 rows was removed
> > 4632 - 84 rows was removed
> > 4716 - 84 rows was removed
> > 4800 - 84 rows was removed
> > 4884 - 84 rows was removed
> > 4968 - 84 rows was removed
> > close -> 0
> >
> > Thanks
> >
> >
> > On Thu, Apr 4, 2019 at 9:07 AM Arthur Blondel <arthur5blon...@gmail.com>
> > wrote:
> >
> > >
> > > Hello
> > >
> > > When I try to insert new data to a full SQLite database, I need to
> remove
> > > much more than really needed. I'm doing the following:
> > >
> > > while(1) {
> > > do {
> > > status = insert_1_row_to_db();
> > > if (status == full) {
> > > remove_one_row_from_db();
> > > }
> > > } while (status == full);}
> > >
> > > The inserted data has always the same size. When the database is full,
> > > removing only one row is enough to insert the new one. But after a
> > while, I
> > > need to remove 30, 40 and even more the 100 rows to be able to insert
> one
> > > new row. Is it the correct behavior of SQLite? Is there a way to remove
> > > only what is needed and no more? Thanks
> > >
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
