Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Module `db_sqlite` doesn't finalize statements with db_sqlite.rows after breaking the iterator's loop #7241

Open
trialism opened this issue Feb 21, 2018 · 7 comments

Comments

Projects
None yet
4 participants
@trialism
Copy link

commented Feb 21, 2018

Hi,

I'm using nim 0.17.2 and every time I try to close a database connection with close() I get this error:

unable to close due to unfinalized statements or unfinished backups

It's probably related to this SO problem.

@Araq

This comment has been minimized.

Copy link
Member

commented Feb 21, 2018

Please provide an example program demonstrating the problem. We've been using this wrapper in production for years.

@trialism

This comment has been minimized.

Copy link
Author

commented Feb 21, 2018

When I tried to create a minimal example I've realized it only happens with db.rows. My problem was that I called break in the for loops and it seems like db_sqlite doesn't call the finalize because it doesn't fetch the whole data first(?).
The docs says this about fastRows():

Breaking the fastRows() iterator during a loop will cause the next database query to raise a DbError exception unable to close due to ....

but for rows() it says it's safe:

same as FastRows, but slower and safe.

but it isn't safe.

@trialism trialism changed the title Module `db_sqlite` doesn't finalize statements Module `db_sqlite` doesn't finalize statements with db_sqlite.rows Feb 21, 2018

@trialism trialism changed the title Module `db_sqlite` doesn't finalize statements with db_sqlite.rows Module `db_sqlite` doesn't finalize statements with db_sqlite.rows after breaking the iterator's loop Feb 21, 2018

@trialism

This comment has been minimized.

Copy link
Author

commented Feb 22, 2018

So, minimal example:

import db_sqlite

const table1 = ("""
create table if not exists table1(
  id integer primary key,
  num integer not null
);""")

proc a(q: string, i: int): void =
  let db = open("d.db", nil, nil, nil)
  if i > 0:
    for row in db.rows(sql(q), i):
      echo(row)
      break
  else:
    db.exec(sql(q))
  db.close()

a(table1, 0)
a("insert into table1(num) values(1);", 0)
a("insert into table1(num) values(2);", 0)
a("select * from table1 where num >= ?;", 1)

Output:

@[1, 1]
Traceback (most recent call last)
main.nim(22)             main
main.nim(17)             a
db_sqlite.nim(297)       close
db_sqlite.nim(104)       dbError
Error: unhandled exception: unable to close due to unfinalized statements or unfinished backups [DbError]
****
@define-private-public

This comment has been minimized.

Copy link
Contributor

commented Jan 13, 2019

I'd like to confirm that I'm also hitting this error on 0.19.2. This was in the context though of exec() first throwing a DbError, handling it, then outside of that block trying to close the connection of the database.

I can provide some sample code if requested, but it might be a bit.

@Araq

This comment has been minimized.

Copy link
Member

commented Jan 13, 2019

Easy to fix, needs more try finally statements. :-)

@Araq Araq closed this in 7f0559b Jan 13, 2019

@define-private-public

This comment has been minimized.

Copy link
Contributor

commented Jan 13, 2019

I used choosenim to update to #head to see if the fix worked; It didn't for me. Here is a snippet of the code I was using to trip the error:

import db_sqlite


# Open the database
let db = open("asdf.db", "", "", "")

# Add a table
db.exec(sql"CREATE TABLE Asdf(value TEXT NOT NULL UNIQUE);")

# Insert some good values
db.exec(sql"""
INSERT INTO Asdf (value)
VALUES           ('abc'),
                 ('123');
""")

# Do a bad update
try:
  db.exec(sql"""
  UPDATE Asdf
  SET value = 'abc'
  WHERE value = '123';
  """)
except DbError:
  # This should be hit because the colum `value` must be unique
  echo("Tried to do an invalid update")

# close, throws an error here
db.close()

And here is the exact output:

Tried to do an invalid update
sqlite_fail.nim(29)      sqlite_fail
db_sqlite.nim(305)       close
db_sqlite.nim(103)       dbError
Error: unhandled exception: unable to close due to unfinalized statements or unfinished backups [DbError]
Error: execution of an external program failed: '/home/ben/Desktop/tmp/sqlite_fail '

@Araq Araq reopened this Jan 14, 2019

@Araq

This comment has been minimized.

Copy link
Member

commented Jan 14, 2019

Well the example worked for me, now you use a different snippet, make up your mind. ;-)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.