?
Solved

SQLite - attempt to write a readonly database

Posted on 2011-10-17
32
Medium Priority
?
2,975 Views
Last Modified: 2012-05-12
Hi Experts,

I'm using Windows 7, opening an sqlite file in my documents folder with VS2010 and the amalgamation of sqlite (the header and the .c file compiled right in).  

I'm using the following code to open the database:
SQLiteDatabase *pDB = new SQLiteDatabase(m_strFileName, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0);  This is a good simple wrapper (Kompex) for sqlite.  I've seen this error various times and it seems to be intermittent - I can't pinpoint what causes it.

I don't see why the file is not writeable - it's in my documents folder.  No other process has it open... Here are the details:

I have about 5 "CREATE TABLE IF NOT EXISTS" happening and executing fine.  The 6th one is a new one I am adding to an existing sqlite database file.  Does SQLite not like it when you try to add tables to an existing database file?

Thanks,
Mike
0
Comment
Question by:thready
  • 21
  • 6
  • 5
32 Comments
 
LVL 1

Author Comment

by:thready
ID: 36980923
Some people are saying that this is a recurrent issue in Vista and Windows 7- that you need to run the app as admin...  Doesn't make sense to me.  I hope someone can confirm or present a workaround.  I tried to run VS2010 as admin- it didn't change anything, but I don't think it will launch the debugging process as admin as well....  How can I run my app as admin through the IDE?
0
 
LVL 86

Expert Comment

by:jkr
ID: 36981035
>>How can I run my app as admin through the IDE?

You should add a manifest to your application that requests admin privileges by setting 'requireAdministrator', see http://msdn.microsoft.com/en-us/library/bb756929.aspx ("Step 6: Create and Embed an Application Manifest (UAC)"). The manifest should look like
Executable: myapp.exe 
Manifest: myapp.exe.manifest
Sample application manifest file:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0"> 
  <assemblyIdentity version="1.0.0.0"
     processorArchitecture="X86"
     name="myapp"
     type="win32"/> 
  <description>Description of your application</description> 
  <!-- Identify the application security requirements. -->
  <trustInfo xmlns="urn:schemas-microsoft-com:asm.v2">
    <security>
      <requestedPrivileges>
        <requestedExecutionLevel
          level="requireAdministrator"
          uiAccess="false"/>
        </requestedPrivileges>
       </security>
  </trustInfo>
</assembly>

Open in new window

0
 
LVL 86

Expert Comment

by:jkr
ID: 36981048
Ooops, sorry, a copy&paste accident - this should just be
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0"> 
  <assemblyIdentity version="1.0.0.0"
     processorArchitecture="X86"
     name="myapp"
     type="win32"/> 
  <description>Description of your application</description> 
  <!-- Identify the application security requirements. -->
  <trustInfo xmlns="urn:schemas-microsoft-com:asm.v2">
    <security>
      <requestedPrivileges>
        <requestedExecutionLevel
          level="requireAdministrator"
          uiAccess="false"/>
        </requestedPrivileges>
       </security>
  </trustInfo>
</assembly>

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:thready
ID: 36981574
Thanks jkr but that didn't help.  Or did it?  (I see a little shield on the icon of my app in Windows Explorer, just noticed that little sucker.  Looks pretty nifty having a shield on my app).

So now I'm a bit confused for another reason.  Get this.  To make sure that the following is correct, I've completely deleted the manifests (maybe windows knows something I don't now, and it's storing something about my app in the registry)....

Previously I was always trying to run my app with an argument (being the name of my sqlite db file), it tries to open the file that way and fails with the "attempt to write readonly db".  The program doesn't crash, I catch the exception.  Note that this kept happening even after your manifest suggestion.

Before even asking this question on EE, I was able to open the file and run the code after the initial fail (by supplying the db name by param to my app).  Then, after that always failed, I choose "File | open", with the exact same file - and it works.  However, now there's a different - I've deleted my manifests, that nifty shield is there, and it ALSO works when the file is passed as param.  No code changes.  Must be the manifest as some hokus pokus windows is doing with my app in the registry.  Am I right?

Thanks!
Mike
0
 
LVL 1

Author Comment

by:thready
ID: 36981597
I hope that was clear- basically I was saying that I was always able to call "file | open", but that when that same file was opened because it was passed as a param to my program, it fails.  So basically, it didn't seem like a permission thing.....  it seemed more like an sqlite issue.

But with no code changes, I'm confused about this.
0
 
LVL 86

Expert Comment

by:jkr
ID: 36981638
Can you check if when you do that "file | open" thing, the full path is being used? Or, IOW, what happens if you specify the full path on the command line?
0
 
LVL 1

Author Comment

by:thready
ID: 36981646
I was curious to see what would happen if I delete the exe for the app.  I lost the little shield that was superimposed on it when it got rebuilt (it's not very noticeable, but it's not there anymore).  It still works every time, with no manifests and no code changes.....

I've had this same intermittent bug with sqlite before - different code (different language even), different format of the file - and it just seemed to go away on its own as well.  I could never pinpoint the issue with it, shrugged my shoulders and since it kept working without problems with months in between, let it go.  Now I have to get to the bottom of this....
0
 
LVL 1

Author Comment

by:thready
ID: 36981667
I just double checked and it's always exactly the same path in both cases.
0
 
LVL 1

Author Comment

by:thready
ID: 36981669
(full path)
0
 
LVL 1

Author Comment

by:thready
ID: 36981733
Nothing with my app's name of interest in the registry....
0
 
LVL 86

Expert Comment

by:jkr
ID: 36981764
Are you by any chance using a different working directory in each case?
0
 
LVL 1

Author Comment

by:thready
ID: 36981788
No - same instance of the application.  It's launched from the IDE (with command line being full path to the file).  Then when that used to fail, full path to the file as well from browse dlg.  working dir could not have changed.
0
 
LVL 86

Expert Comment

by:jkr
ID: 36982421
Well, the IDE *does* use a different working directory than when you launch it from the Explorer - check that with 'GetCurrentDirectory()'.
0
 
LVL 1

Author Comment

by:thready
ID: 36983300
I'm only launching it from the IDE (with a debug parameter which is the same full path to the file).  The same launched session first fails, then it works when I use my (launched app from the IDE's) File | Open function.  Same debugging launched session (first fails with debug param, then works from file open - same instance of the app).  Then, worked for both with no code changes....  Sorry this wasn't so clear...

Mike
0
 
LVL 35

Expert Comment

by:sarabande
ID: 36984190
can you post details to the (three?) start and open alternatives you tried?

- post the commandline and default directory from debug settings in the IDE
- post the code you were using when you pass the file by "debug param".
- post the code you were using when getting and opening the file via "File - Open"  

note, when starting the program from ide it is always the project root directory which you have as current directory beside you make different launch settings. if you start the program from explorer (browser?) the default directory is where you executable resides. in any case when you were able to open the database file you have passed a correct path to the file. but you may check that you only have one single file with the same name at disk.

Sara
0
 
LVL 1

Author Comment

by:thready
ID: 36986286
Hi Sara,

There are not three ways I'm doing this - there's only 1:

- I run my program ONCE from the IDE (with a command line).
- It calls a function to open the file passed as argument
- it fails
- The same program is STILL running.
- I then call File | Open (with the same file)
- the same function as previously described gets called, with the same path (same working dir of course)
- it then works.

That was before doing what jkr said to do, without any code changes, everything just started working.  No different threads.  I'm baffled.  And still, It all works, the error is not happening any more.

The code called in the adaptor (command line or from file open - same function is called):

Within that function:
SQLiteDatabase *pDB = new SQLiteDatabase(m_strFileName, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0);
SQLiteStatement *pStmt = new Kompex::SQLiteStatement(pDB);
pStmt->SqlStatement("CREATE TABLE IF NOT EXISTS mydata (id INTEGER, fid INTEGER, dval REAL, ival INTEGER, sval TEXT, type INTEGER, cid INTEGER);");

nothing interesting at all......

0
 
LVL 1

Author Comment

by:thready
ID: 36986294
Seems like sqlite has a bug in it.  I wouldn't be the first to suspect this, after googling a lot yesterday- this is a very common error and there's some confusion about it.
0
 
LVL 35

Expert Comment

by:sarabande
ID: 36986525
it seems to be rather unlikely that you really have two or more times an identical m_strFileName when calling 'new SQLiteDatabase' .

would you post the code where you fill m_strFileName from commandline argument, from debug param and from Open - File ?

also would you please set a breakpoint at statement with new SQLiteDatabase, such that you could go sure, that the function was called only once and the contents of m_strFileName is all the same.

Sara
0
 
LVL 35

Accepted Solution

by:
sarabande earned 2000 total points
ID: 36986632
if there is a bug in sqllite it could be that it tries to open the file exclusively for write.

could it be that you didn't close the file properly at end of your program such that sqllite has problems with the very first open? that would explain that the second opens were working!

Sara
0
 
LVL 1

Author Comment

by:thready
ID: 36986665
Did that.  Definitely only once.  I totally agree with you that this sounds extremely weird.  But like I said- I've seen this before in a completely different program/language/ database with SQLite.

And again- I didn't even change any of the code and it just started working again- by creating a manifest and deleting it after?  I don't know about that.  I suspect something like an antivirus program getting a handle on the file as I was trying to read it or something (but 50 times in a row and after restarting my machine)?  Doubt it.  Baffled.

Mike
0
 
LVL 1

Author Comment

by:thready
ID: 36986669
Now you're talking - your last comment is a very good one!
0
 
LVL 1

Author Closing Comment

by:thready
ID: 36986681
You got it!  THANK YOU!     :o)
0
 
LVL 35

Expert Comment

by:sarabande
ID: 36986850
if a file is not closed properly a file handle still was unclosed and could prevent from opening exclusively next time. i assume sqllite opens the file read-only when it failed to open exclusively.

but, with that next open windows closes the old and no longer used file handle from previous session such that any following call for opening in exclusive write mode succeeds.

i don't know that for sure, but it sounds not bad, right?

thanks.

Sara
0
 
LVL 1

Author Comment

by:thready
ID: 36986880
I actually read about that - yes SQLite opens in readonly mode if something else has a handle on it... But I thought it was an anti-virus program causing that.  It could very well be both myself and another program causing it....  Because it even happened after a restart...
0
 
LVL 35

Expert Comment

by:sarabande
ID: 36986951
yes, if it happens after boot, it definitively had another reason. you probably could help yourself by copying the database file to a network drive. or if the database is small you could write a little script where you first make a copy of the file and open the copy rather than the original. after closing the program the script would copy the file back. that way you additionally had a backup.

Sara

0
 
LVL 1

Author Comment

by:thready
ID: 37117564
Nope- I'm starting to think there's something seriously wrong with SQLite.  I'm getting this error again and intermittent behavior again- I get through a series of "CREATE TABLE IF NOT EXISTS" calls, it gets through them all just fine, and when I inspect the file, the tables don't all exist!
0
 
LVL 35

Expert Comment

by:sarabande
ID: 37118866
SQLite is file-based and the "lite" means some limitations - especially for multi-user operability. but i don't think there is a fundamental problem as there are many many developers using SQLite.

when do you inspect the file? while the program is still running? did you check all exits of your program such that the file really was closed after  the create statements? are you doing some kind of transactions? are you doing some asynchronous operations with the SQLite? some callbacks? can you make sure that the database never was opened twice?

Sara
0
 
LVL 1

Author Comment

by:thready
ID: 37119762
I thought you were right previously about the opened twice, but I looked deeply into it, it was not.  I always close the DB and only ever connect to it on one thread (the main thread).  This morning it works again... no code changes.  I don't understand what it could be.  There's nothing that I know of that is accessing (all my sqlite files)...   It either happens to all of them or none of them.  It's very bizarre.  I think you should reclose this question- I don't think there's much anyone can do to help me here...  It's going to be something quite unique- because yes, a heck of a lot of people use sqlite!
0
 
LVL 1

Author Comment

by:thready
ID: 37119772
quite unique and intermittent.......  makes a great combination!
0
 
LVL 1

Author Comment

by:thready
ID: 37157467
This is happening again- this is truely my worst nightmare.  It does seem to be a problem with trying to create a new table when data already exists in the database.

When I test on a new file that doesn't contain some of the tables that my setup function creates with "CREATE TABLE IF NOT EXISTS", I get the error, "Attempt to write to readonly database"....  I will continue to document what I find on this in this question until it's solved...
0
 
LVL 1

Author Comment

by:thready
ID: 37157524
ok wow...  I think I'm finally on the right track.  After diving into the Winapp code for the serialize function (which gets called from ProcessShellCommand), There's a call to load the file passed as param to the app.  That serialize function, I noticed, creates a read-only handle on the file...  I'm looking into another way of launching my program and grabbing what's on the command line.....
0
 
LVL 1

Author Comment

by:thready
ID: 37157542
EUREKA!  This was from some very unexpected behavior as I stated in my last post.  I'm not sure why I had placed my code in the serialize, but that was done a very long time ago- before I changed my file format from my own to SQLite.  This was only happening to files that weren't already getting created in another program.  One of them tough bugs to fix- one less to deal with in my life!  FEWF!!  Thanks again you two...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question