Title: Bug in sqlite in Windows binaries
Type: Stage: resolved
Components: Build, Windows Versions: Python 2.7
Status: closed Resolution: fixed
Dependencies: Superseder:
Assigned To: steve.dower Nosy List: benjamin.peterson, brett.cannon, ghaering, ned.deily, pitrou, python-dev, schlamar, steve.dower, zach.ware
Priority: normal Keywords:

Created on 2013-10-30 11:12 by schlamar, last changed 2016-03-17 21:56 by steve.dower. This issue is now closed.

Messages (27)
msg201728 - (view) Author: Marc Schlaich (schlamar) * Date: 2013-10-30 11:12
My System:

$ python
Python 2.7.5 (default, May 15 2013, 22:43:36) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
>>> sqlite3.sqlite_version

Test Script:

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('PRAGMA foreign_keys = ON')
fk = (conn.execute("PRAGMA foreign_keys").fetchone()[0])
print 'version = %s, foreign keys = %r' % (sqlite3.sqlite_version, bool(fk))
if not fk:
  raise Exception('No foreign keys!?')

c = conn.cursor()
create table if not exists (resource_id TEXT PRIMARY KEY, data TEXT);
create table if not exists main.two (node_id INTEGER PRIMARY KEY, data TEXT);
create table if not exists main.mapping (node_id INTEGER REFERENCES two, resource_id TEXT REFERENCES one);
insert into, data) values('A', 'A one thing');
insert into main.two(node_id, data) values(1, 'A two thing');
insert into main.mapping(resource_id, node_id) values('A', 1);
insert into, data) values('B', 'Another one thing');
insert into main.two(node_id, data) values(2, 'Another two thing');
insert into main.mapping(resource_id, node_id) values('B', 2);
insert into, data) values('C', 'Yet another one thing');
for tbl in 'one', 'two', 'mapping':
  print 'TABLE main.%s:\n%s\n' % (tbl, '\n'.join(repr(r) for r in c.execute('select * from main.%s' % tbl).fetchall()))

del_cmd = """delete from where resource_id='B'"""
print 'Attempting: %s' % (del_cmd,)
except Exception, e:
  print 'Failed to delete: %s' % e

cmd = """delete from where resource_id='C'"""
print 'Attempting: %s' % (cmd,)

cmd = """delete from main.mapping where resource_id='B' AND node_id=2"""
print '\nAttempting: %s' % (cmd,)

for tbl in 'one', 'two', 'mapping':
  print 'TABLE main.%s:\n%s\n' % (tbl, '\n'.join(repr(r) for r in c.execute('select * from main.%s' % tbl).fetchall()))

print 'Attempting: %s' % (del_cmd,)

This fails with "sqlite3.IntegrityError: foreign key constraint failed".  Original report comes from SO: 

The proposed solution (to upgrade sqlite) is not possible on Windows as it comes bundled with Python. 
So please update the bundled sqlite version where this bug is solved.
msg201796 - (view) Author: Martin v. Löwis (loewis) * (Python committer) Date: 2013-10-31 10:23
It's certainly possible to update SQLite without rebuilding Python. Just replace sqlite3.dll with a newer version.
msg201820 - (view) Author: Marc Schlaich (schlamar) * Date: 2013-10-31 14:37
Ah that's great. The issue is resolved with SQLite 3.8.1.

However, would be great if a newer SQLite version comes already bundled with Python.
msg222638 - (view) Author: Mark Lawrence (BreamoreBoy) * Date: 2014-07-09 20:47
FYI 3.4.0 Windows was shipped with SQLite see #20465.
msg222652 - (view) Author: Martin v. Löwis (loewis) * (Python committer) Date: 2014-07-10 07:48
I don't maintain Python 2.7 anymore, so removing myself.
msg223118 - (view) Author: Steve Dower (steve.dower) * (Python committer) Date: 2014-07-15 16:22
I don't know enough about the SQLite API to determine whether we can safely upgrade from 3.6.21 in Python 2.7, but since this doesn't appear to be a security issue I don't see any solid justification for doing it anyway.

If someone else does it, I'll build it, but I'm not taking responsibility for the change :)
msg223127 - (view) Author: Ned Deily (ned.deily) * (Python committer) Date: 2014-07-15 17:24
IMO, what third-party libraries are included with the Windows and OS X binary installers, what versions of them, and when to update them are questions without firm established policy answers, say in a PEP.  In many ways, for the installers we are performing the role of a third-party distributor / integrator.  In many other such situations, the third-party libraries would be dynamically linked from the platform OS's or distributor's version and would be independently updated, assuming ABI compatibility is maintained.  For the installers, I think the updating has been mostly at the discretion of the installer developers with occasional instigation by the release managers or other core developers in the cases of security issues.  FWIW, the most recent 2.7.x installers for OS X ship with SQLite  The current version of SQLite is 3.8.5 and that project has a very good reputation for its well-tested, regression-free releases.  So I would not see a problem in upgrading the library but I would get a pronouncement from the release manager.
msg229430 - (view) Author: Marc Schlaich (schlamar) * Date: 2014-10-15 10:20
I've now run various and partially complex applications including SQLAlchemy against SQLite 3.8.1 for months without having any issues. Right now I have run extensive database test suites against the current SQLite release 3.8.6 without any issues, too.

So I would say it's save to bundle a newer SQLite version in the next Python 2.7.x. Who would be responsible for this? Is there anything I can do to get progress on this issue?
msg229431 - (view) Author: Antoine Pitrou (pitrou) * (Python committer) Date: 2014-10-15 10:22
Steve is responsible for the Windows builds, but he always answered to your request above.
msg229433 - (view) Author: Mark Lawrence (BreamoreBoy) * Date: 2014-10-15 11:18
A work around is already given in msg201796 and is reinforced by this
msg229463 - (view) Author: Marc Schlaich (schlamar) * Date: 2014-10-15 15:42
Yes, but this is no practical solution. Telling *all* my clients to update the sqlite3.dll after *every* Python update is just not feasible and will just not work out in practice. 

What would be the required steps to update the *.dll in the build? Just update the external.bat to fetch another version or I am missing something? 

I would suggest synchronizing it with Python 3, which means SQLite Or what do you think?
msg229464 - (view) Author: Antoine Pitrou (pitrou) * (Python committer) Date: 2014-10-15 15:44
The main risk is if SQLite 3.8 changes some behaviours compared to older versions. This is why we generally choose to only apply bugfix releases of external libraries in our owbn bugfix branches.

The question is whether this issue can be worked around without upgrading SQLite.
msg229468 - (view) Author: Marc Schlaich (schlamar) * Date: 2014-10-15 15:59
Well, OSX release ships with, too:
msg229469 - (view) Author: Antoine Pitrou (pitrou) * (Python committer) Date: 2014-10-15 16:01
As I said, I think it's really Steve's choice here (as long as he is doing the Windows release builds).
msg240609 - (view) Author: Zachary Ware (zach.ware) * (Python committer) Date: 2015-04-13 14:14
I'm ambivalent on whether to do it, but Marc is right that it's just a version update in Tools/buildbot/external-common.bat and PCbuild/pyproject.vsprops.
msg247480 - (view) Author: Brett Cannon (brett.cannon) * (Python committer) Date: 2015-07-27 20:05
Apparently we are shipping an old version in 3.5b4 ( and the latest release is 3.11), so this should probably be upgraded before rc.
msg247487 - (view) Author: Steve Dower (steve.dower) * (Python committer) Date: 2015-07-27 21:24
I agree, and I know that backwards compatibility is a very high priority especially now that SQLite is shipping as part of Windows 10. While we *could* take a dependency on this binary (winsqlite3.dll, for those who have it), I don't think it's actually any different from the main release.

In short, let's update the SQLite version for 3.5 and put it through its paces.

I'm only +0 on doing it for Python 2.7, and that's assuming we'll do an RC before releasing 2.7.11. Given we just revised the entire build project, I think an RC would be appropriate.
msg247527 - (view) Author: Steve Dower (steve.dower) * (Python committer) Date: 2015-07-28 15:50
Updating to now.
msg247532 - (view) Author: Marc Schlaich (schlamar) * Date: 2015-07-28 17:04
I'm still +1 for an update in 2.7 as there are a lot of important bug fixes and a new SQLite version is 100% backwards compatible for my medium to big size (SQLAlchemy) SQLite applications.
msg247537 - (view) Author: Steve Dower (steve.dower) * (Python committer) Date: 2015-07-28 18:38
Looks like Python 2.7 is still on SQLite 3.6.21, so the RM (Benjamin) will have to make a call on whether we take the upgrade.
msg247538 - (view) Author: Roundup Robot (python-dev) Date: 2015-07-28 18:39
New changeset 1003746ee7a0 by Steve Dower in branch '3.4':
Issue #19450: Update Windows builds to use SQLite

New changeset 4b37e81fcc54 by Steve Dower in branch '3.5':
Issue #19450: Update Windows builds to use SQLite

New changeset efb8132a7a22 by Steve Dower in branch 'default':
Issue #19450: Update Windows builds to use SQLite
msg247560 - (view) Author: Roundup Robot (python-dev) Date: 2015-07-29 06:30
New changeset 33dbcde76b3f by Ned Deily in branch '3.4':
Issue #19450: Update OS X installer builds to use SQLite 3.8.11.

New changeset ebe72984c1b9 by Ned Deily in branch '3.5':
Issue #19450: merge from 3.4

New changeset 1f4ef305b658 by Ned Deily in branch 'default':
Issue #19450: merge from 3.5
msg261153 - (view) Author: Marc Schlaich (schlamar) * Date: 2016-03-03 09:18
This wasn't decided yet for 2.7 so I'm reopening it until a decision is made.

I'm still +1 for an update to on Windows so that this is on par with the version on OSX and the version in Python 3.
msg261658 - (view) Author: Steve Dower (steve.dower) * (Python committer) Date: 2016-03-12 16:10
Assigning to Benjamin to make the call for 2.7. Either close or assign it back if you want the update.
msg261800 - (view) Author: Benjamin Peterson (benjamin.peterson) * (Python committer) Date: 2016-03-15 05:38
Let's make Windows use the same version as the Mac installer.
msg261935 - (view) Author: Roundup Robot (python-dev) Date: 2016-03-17 21:42
New changeset fa68df1d5e65 by Steve Dower in branch '2.7':
Issue #19450: Update Windows builds to use SQLite
msg261936 - (view) Author: Steve Dower (steve.dower) * (Python committer) Date: 2016-03-17 21:56
Done. The test_sqlite tests were fine, but that's all I checked.
Date User Action Args
2016-03-17 21:56:39steve.dowersetstatus: open -> closed
resolution: fixed
messages: + msg261936
2016-03-17 21:42:17python-devsetmessages: + msg261935
2016-03-15 05:38:18benjamin.petersonsetassignee: benjamin.peterson -> steve.dower
messages: + msg261800
2016-03-13 00:41:39BreamoreBoysetnosy: - BreamoreBoy
2016-03-12 16:10:46steve.dowersetassignee: steve.dower -> benjamin.peterson
messages: + msg261658
versions: - Python 3.5, Python 3.6
2016-03-03 09:18:30schlamarsetstatus: closed -> open
resolution: fixed -> (no value)
messages: + msg261153
2015-08-08 16:07:27steve.dowersetstatus: open -> closed
resolution: fixed
stage: resolved
2015-07-29 06:30:24python-devsetmessages: + msg247560
2015-07-28 18:39:08python-devsetnosy: + python-dev
messages: + msg247538
2015-07-28 18:38:26steve.dowersetmessages: + msg247537
2015-07-28 17:04:05schlamarsetmessages: + msg247532
2015-07-28 15:50:13steve.dowersetmessages: + msg247527
2015-07-27 21:24:45steve.dowersetmessages: + msg247487
versions: + Python 3.6
2015-07-27 20:05:51brett.cannonsetnosy: + brett.cannon

messages: + msg247480
versions: + Python 3.5
2015-04-13 14:14:51zach.waresetassignee: steve.dower
messages: + msg240609
2014-10-15 16:01:28pitrousetmessages: + msg229469
2014-10-15 15:59:58schlamarsetmessages: + msg229468
2014-10-15 15:44:07pitrousetmessages: + msg229464
2014-10-15 15:42:34schlamarsetmessages: + msg229463
2014-10-15 11:18:57BreamoreBoysetnosy: + zach.ware
messages: + msg229433
2014-10-15 10:22:40pitrousetnosy: + pitrou
messages: + msg229431
2014-10-15 10:20:26schlamarsetmessages: + msg229430
2014-07-15 17:37:31steve.dowersetnosy: + benjamin.peterson
2014-07-15 17:24:44ned.deilysetnosy: + ned.deily
messages: + msg223127
2014-07-15 16:22:24steve.dowersetnosy: + ghaering
messages: + msg223118
2014-07-10 11:35:49berker.peksagsetnosy: + steve.dower
2014-07-10 07:48:19loewissetnosy: - loewis
2014-07-10 07:48:14loewissetmessages: + msg222652
2014-07-09 20:47:07BreamoreBoysetnosy: + BreamoreBoy
messages: + msg222638
2013-10-31 14:37:23schlamarsetmessages: + msg201820
2013-10-31 10:23:55loewissetmessages: + msg201796
2013-10-30 22:33:47ned.deilysetnosy: + loewis
components: + Build, Windows, - Extension Modules
2013-10-30 11:12:38schlamarcreate