Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Multiple processes, same database: "Exception: attempt to write a readonly database Unable to fetch row"
Forum Updated to NodeBB v4.3 + New Features

Multiple processes, same database: "Exception: attempt to write a readonly database Unable to fetch row"

Scheduled Pinned Locked Moved Solved General and Desktop
10 Posts 4 Posters 1.4k Views 2 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • F Offline
    F Offline
    FiddleWiddle
    wrote on last edited by
    #1

    Hi,

    I am new here and need some help with a database exception that I get when two processes acces the same sqlite database simultaneously. Thie first process is doing fine, but the second somehow does not get write access and throws exceptions on every attempt to write something.
    Now I wonder if this is impossible in general or what I am doing wrong in particular.

    Some more information that might be relevant:

    • Platform is Windows
    • Qt for Python
    • The second process is started from a different machine and accesses the database via network. Read and write access for the directory is given
    • I don't have any query or database objects as members, all of them exist only in their scope and I call finish() on all queries once they are not needed any longer.
    • I set db.setConnectOptions('QSQLITE_BUSY_TIMEOUT=3000') but this didn't do anything I guess.

    Any help or best practices that I missed are very much appreciated!

    Thanks in advance!

    JonBJ 1 Reply Last reply
    0
    • SGaistS Offline
      SGaistS Offline
      SGaist
      Lifetime Qt Champion
      wrote on last edited by
      #2

      Hi and welcome to devnet,

      Please read the When To Use page of sqlite's documentation.

      Your use case is one that should be avoided.

      Interested in AI ? www.idiap.ch
      Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

      1 Reply Last reply
      2
      • Kent-DorfmanK Offline
        Kent-DorfmanK Offline
        Kent-Dorfman
        wrote on last edited by
        #3

        SQLlite would not be my choice for a concurrent access networked database...just sayin. I prefer PostgreSQL.

        1 Reply Last reply
        0
        • F FiddleWiddle

          Hi,

          I am new here and need some help with a database exception that I get when two processes acces the same sqlite database simultaneously. Thie first process is doing fine, but the second somehow does not get write access and throws exceptions on every attempt to write something.
          Now I wonder if this is impossible in general or what I am doing wrong in particular.

          Some more information that might be relevant:

          • Platform is Windows
          • Qt for Python
          • The second process is started from a different machine and accesses the database via network. Read and write access for the directory is given
          • I don't have any query or database objects as members, all of them exist only in their scope and I call finish() on all queries once they are not needed any longer.
          • I set db.setConnectOptions('QSQLITE_BUSY_TIMEOUT=3000') but this didn't do anything I guess.

          Any help or best practices that I missed are very much appreciated!

          Thanks in advance!

          JonBJ Offline
          JonBJ Offline
          JonB
          wrote on last edited by JonB
          #4

          @FiddleWiddle
          Further too the above two comments. FWIW, I came across https://charlesleifer.com/blog/sqlite-small-fast-reliable-choose-any-three-/

          When would SQLite not be a good choice?
          Multiple servers communicating with the database over the network, or situations in which you plan to run your database on a separate server.
          The main consideration there, I think, is when you have multiple web-servers and need to connect to your database over the network. While you can use SQLite over an NFS mount, for instance, the SQLite documentation seems to indicate that this may be buggy.

          So cross-machine access over a network seems to be potentially "flaky"....

          1 Reply Last reply
          2
          • F Offline
            F Offline
            FiddleWiddle
            wrote on last edited by FiddleWiddle
            #5

            Thanks for all your answers!
            I read https://www.sqlite.org/faq.html#q5 and was under the assumption that this might still be doable, considering that my application very rarely writes to the database. And it appears that my second client always gets the exception mentioned above. As if the first client would lock the database forever without performing any writes. Is that possible?
            But the point @JonB mentioned seems to critical indeed.
            In that case considering that the application already exists would you recommend another database that handles this situation well or would you adjust the application to be a client/server one with only the server having the SQLite database locally? Or anything else?

            JonBJ 1 Reply Last reply
            0
            • F FiddleWiddle

              Thanks for all your answers!
              I read https://www.sqlite.org/faq.html#q5 and was under the assumption that this might still be doable, considering that my application very rarely writes to the database. And it appears that my second client always gets the exception mentioned above. As if the first client would lock the database forever without performing any writes. Is that possible?
              But the point @JonB mentioned seems to critical indeed.
              In that case considering that the application already exists would you recommend another database that handles this situation well or would you adjust the application to be a client/server one with only the server having the SQLite database locally? Or anything else?

              JonBJ Offline
              JonBJ Offline
              JonB
              wrote on last edited by
              #6

              @FiddleWiddle

              would you recommend another database that handles this situation well

              If you are saying you are willing to move off SQLite as the RDBMS, why invent your own architecture trying to talk to a server application which in turn does SQLite when, say, MySQL or MariaDB are available and already work client/server and are supported from Qt? Or, I know nothing about it other than it's "object-relational" (and multi-user), but there is @Kent-Dorfman's recommendation of PostgreSQL, and that too is supported from Qt.

              1 Reply Last reply
              0
              • F Offline
                F Offline
                FiddleWiddle
                wrote on last edited by FiddleWiddle
                #7

                I am willing to do anything that fixes my problem :)
                I have no experience with those other databases that is why I ask. Please correct me if I am wrong, but I imagine the biggest task when using the new database is converting the existing data to the new structure and maybe fix some SQL syntax. On the other hand when switching to client server, I would introduce a new layer for the database access (using maybe Pyro for RPC, but also no experience) and leave the database itself untouched.
                I am currently reading some PostgreSQL documentation to get some more insights there.

                Edit: Oh and one addtional question: All other database systems require additional software to be installed, correct?

                JonBJ 1 Reply Last reply
                0
                • F FiddleWiddle

                  I am willing to do anything that fixes my problem :)
                  I have no experience with those other databases that is why I ask. Please correct me if I am wrong, but I imagine the biggest task when using the new database is converting the existing data to the new structure and maybe fix some SQL syntax. On the other hand when switching to client server, I would introduce a new layer for the database access (using maybe Pyro for RPC, but also no experience) and leave the database itself untouched.
                  I am currently reading some PostgreSQL documentation to get some more insights there.

                  Edit: Oh and one addtional question: All other database systems require additional software to be installed, correct?

                  JonBJ Offline
                  JonBJ Offline
                  JonB
                  wrote on last edited by JonB
                  #8

                  @FiddleWiddle
                  As I said I don't know about PostgreSQL, I'm imagining that (although it does SQL) being "object-relational" the point of using it might be to change what you do a bit to take advantage or work with it.

                  OTOH, if you are looking for the least work from where you are now, my thought would be that SQLite -> MySQL (or possibly MariaDB) might be the simplest. Googling for sqlite move/migrate to mysql or similar seems a good start, hits like:

                  https://stackoverflow.com/questions/18671/quick-easy-way-to-migrate-sqlite3-to-mysql
                  https://github.com/weewx/weewx/wiki/Transfer-from-sqlite-to-MySQL

                  might be of use to you.

                  All other database systems require additional software to be installed, correct?

                  Yep, 'fraid so.

                  1 Reply Last reply
                  1
                  • SGaistS Offline
                    SGaistS Offline
                    SGaist
                    Lifetime Qt Champion
                    wrote on last edited by
                    #9

                    An SQL database system shall support the SQL standard. Some may have other features but that is another story.

                    Unless you are doing something highly SQLite specific, your Qt code will only change in the connections parameters.

                    Yes you will have to install additional software and do some administration setup stuff.

                    Interested in AI ? www.idiap.ch
                    Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                    1 Reply Last reply
                    3
                    • F Offline
                      F Offline
                      FiddleWiddle
                      wrote on last edited by
                      #10

                      Thanks a lot everyone!
                      I will close this topic as it seems my question is fully answered.

                      1 Reply Last reply
                      0

                      • Login

                      • Login or register to search.
                      • First post
                        Last post
                      0
                      • Categories
                      • Recent
                      • Tags
                      • Popular
                      • Users
                      • Groups
                      • Search
                      • Get Qt Extensions
                      • Unsolved