2

I'm completely stumped on an intermittent error I am getting in sqlite3. The problem is that the exact same script will occasionally run to completion, or it will fail on a seemingly random SELECT statement. Since it's not properly repeatable, I don't know if it's me doing something wrong or if there's a bug. I've seen a very similar issue on this mailing list but Guido van Rossum just referred them elsewhere and I can't find the follow-up.

The boiled-down code:

import sqlite3

conn = sqlite3.connect('c2c_orders.db')
c = conn.cursor()

tracking_nos = [u'1615146623203', u'1614117623187', u'1614174623176', 
                u'1614141623103', u'1614141623101', u'1613102623033', 
                u'1612192622864', u'1612104622842', u'1612109622787', 
                u'1612137622586', u'1612137622583', u'1611191622448', 
                u'1611166622426', u'1610118621895']

for num in tracking_nos:
    print num
    c.execute("SELECT * FROM mw_orders WHERE id=(?)", (num,)) 
    conn.commit()
    db_result = c.fetchall()

I can run this once and from the print statement I will get:

1615146623203
1614117623187
1614174623176
1614141623103
1614141623101
1613102623033
...

Fine. The tracking numbers don't exist in the table so it is returning an empty list. Reset everything and run again:

1615146623203
1614117623187
---------------------------------------------------------------------------
InterfaceError                            Traceback (most recent call last)
C:\...path... in <module>()
    113 
    114     orders = check_orders()
--> 115     orderInfo = get_detailed_info(orders)
    116 
    117     end = datetime.datetime.now()

C:\C:\...path... in get_detailed_info(tracking_no)
     63         data_list = get_data.json()
     64 
---> 65         c.execute("SELECT * FROM mw_orders WHERE id=(?)", (num,))
     66         conn.commit()
     67         db_result = c.fetchall()

InterfaceError: Error binding parameter 0 - probably unsupported type.

Errors relate to the main script so lines don't match up. But I don't understand. This can happen on any number or none at all. As far as I can tell I set my query up correctly.

This is with sqlite3 version 2.6.0 running on Python 2.7 in Enthought Canopy. Has anyone seen this before and know how to get around this? Thanks in advance.

EDIT The db lock mentioned in my comments persists over a Windows reboot. Using the software described here here I get the following, showing that the last modified date is before the reboot

enter image description here

The sequence of events is even stranger. The errors currently alternate as follows. First attempt to read database will print the following to console (prints tracking number and type immediately prior to the query that throws the error)

%run "C:\Users\Joshua\Canopy\PCscripts\full_vehicle_routing\dbSyncer2.py"
1608123637974
<type 'unicode'>
1608188637849
<type 'unicode'>
1607105637842
<type 'unicode'>
1607133637841
<type 'unicode'>
---------------------------------------------------------------------------
InterfaceError                            Traceback (most recent call last)
C:\Users\Joshua\Canopy\PCscripts\full_vehicle_routing\dbSyncer2.py in <module>()
    312     removeChecks = remove_all_checks()
    313     orders = check_orders()
--> 314     orderInfo = get_detailed_info(orders)
    315     checkOldOrders = check_old_orders()
    316 

C:\Users\Joshua\Canopy\PCscripts\full_vehicle_routing\dbSyncer2.py in get_detailed_info(tracking_no)
     90         data_list = get_data.json()
     91 
---> 92         c.execute("SELECT * FROM mw_orders WHERE id=(?)", (num,))
     93         conn.commit()
     94         db_result = c.fetchall()

InterfaceError: Error binding parameter 0 - probably unsupported type.

Try running it again and get:

C:\Users\Joshua\Canopy\PCscripts\full_vehicle_routing\dbSyncer2.py in remove_all_checks()
     65 
     66 def remove_all_checks():
---> 67     c.execute("UPDATE mw_orders SET is_checked = '0'")
     68     conn.commit()
     69 

OperationalError: database is locked

Then running the same script again gives the first error. It either ping-pongs between the two or creates a persistent lock (which I assume is a fully-corrupted db. No other process uses this script, I'm developing it as a test in Canopy and only this script uses the db

Community
  • 1
  • 1
roganjosh
  • 12,594
  • 4
  • 29
  • 46
  • Show what the value and type of `num` is in this situation. – CL. Mar 02 '16 at 18:09
  • @CL. It's the one printed above the error: 1614117623187 (printed before the SELECT query fails). I am correct in thinking that `parameter 0` refers to `num` and not something in the table (in `*`)? Now I can't get it on a `while true` loop so I think I have done something to the DB without knowing.. meaning I can't check the type. I'm going to add an edit now for my new thoughts because it might be an issue with autocommit failures. – roganjosh Mar 02 '16 at 18:14
  • @CL. I've made the edit at the bottom and would be grateful if you could have a look. I'm about to start building a test case myself but I might be off the mark? – roganjosh Mar 02 '16 at 18:26
  • @CL. confirmed that the numbers are coming through as unicode strings even when it fails. `id` is stored as a string too. Moving on a bit from where I was when I asked this; the first run of the script now successfully writes all entries from the JSON string to the db. Running the script again, which should return entries from the table, is failing at random numbers. Not only that, but it now locks the db and even a reboot won't unlock it (Windows 7 showing that it is my own script keeping it locked even over a reboot) – roganjosh Mar 07 '16 at 08:16
  • It is not possible for any process to survive rebooting. This sounds as if your script is run when you do not expect it, probably multiple times. – CL. Mar 07 '16 at 08:22
  • @CL. I've updated the edit in response. I might have to abandon this, I will try running the script using just Python rather than through Enthought Canopy, which behaves a little differently by maintaining everything in memory even once a script ends. – roganjosh Mar 07 '16 at 08:37
  • It is possible to lock a file without yet modifying it. – CL. Mar 07 '16 at 08:41
  • @CL. I understand, but what the pic does show is that I know it is that script that is locking the db. Since there is nothing like a cronjob, the script is not imported into any other script and absolutely nothing makes it run other than my input, I can't figure it out. That, combined with the ping-pong between two errors (the first of which should create the second error that persists on all subsequent runs) is only an additional layer of strange behaviour on top of the initial question :( – roganjosh Mar 07 '16 at 08:50
  • Something looks weird: are you opening the connection to the sqlite database multiple times in a single script run, and do you consistently close it? – Serge Ballesta Mar 07 '16 at 08:59
  • @SergeBallesta The db is opened once at the start of the script and closed at the end. On failure, the `close()` will never be run. Hence, when I rerun the code, the only command should be to open a connection and I would anticipate the second error in my edit on every subsequent run (locked) but I don't always get that. In the part above my EDIT, everything is inside a single `for` loop so I'm still without any understanding on what causes the initial problem on random tracking numbers. – roganjosh Mar 07 '16 at 09:04

2 Answers2

1

Your commit between a select and its fetch should not exist.

To be sure, you could try to control what happens with a try catch after removing the commit:

for num in tracking_nos:
    print num
    try:
        c.execute("SELECT * FROM mw_orders WHERE id=(?)", (num,)) 
        db_result = c.fetchall()
    except Exception as e:
        print "*** ERROR *** ", e, "  reading >", num, "<", type(num)
        # con.close()  # optionally depending on your higher level logic
        raise e
Serge Ballesta
  • 143,923
  • 11
  • 122
  • 252
  • It's going to take me a while to test this sorry as now I can't get it to fail again! I assume the suggestion is in case the print queue isn't flushing to display the most recent `num` and `type` on the failing query? I'm wondering now if there's some type issue on the server I am querying because pinning this issue down to investigate properly is so difficult. – roganjosh Mar 07 '16 at 10:15
  • I'm very sorry to say that after hundreds of cycles now I cannot repeat the error yet I've changed nothing fundamental. The reason that this question stood for 4 days with no activity is that this also happened before. For peace of mind I would like to dive into this further, perhaps it will come back. Thanks for the suggestion of removing commit, I did get rid of that. From your experience, might that have been a cause of unstable behaviour? – roganjosh Mar 07 '16 at 18:08
  • @roganjosh I'm not really a SQLite expert, and I only use it for simple operations. The commit was really bad there, but I cannot say whether it was enough to cause the instability. If you removed it and it has worked since then, maybe... Anyway, I've reworded my post to make it look like an answser – Serge Ballesta Mar 07 '16 at 21:42
  • Ok thanks. Intuitively it doesn't feel like that would be the root cause but it remains unexplained. I will accept and close this for now as clearly there's insufficient info available to me for any of us to *know* what is wrong. I'll re-open another question if I get something closer and your `try/except/` remains in place in that event :) – roganjosh Mar 08 '16 at 07:32
0

Try checking the connection isolation levels: https://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.isolation_level

'Get or set the current isolation level. None for autocommit mode or one of “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”. See section Controlling Transactions for a more detailed explanation.'

--> Try "EXCLUSIVE"

Nicole Abi-Esber
  • 1
  • 1