Closed
Labels
stdlibStandard Library Python modules in the Lib/ directoryStandard Library Python modules in the Lib/ directorytopic-sqlite3type-bugAn unexpected behavior, bug, or errorAn unexpected behavior, bug, or error
Description
| BPO | 34828 |
|---|---|
| Nosy | @berkerpeksag, @itssme, @erlend-aasland |
| PRs |
Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.
Show more details
GitHub fields:
assignee = None
closed_at = None
created_at = <Date 2018-09-28.06:51:55.269>
labels = ['3.7', '3.8', 'type-bug', 'library']
title = 'sqlite.iterdump does not work for (most) databases with autoincrement'
updated_at = <Date 2021-07-15.21:18:56.228>
user = 'https://github.com/itssme'bugs.python.org fields:
activity = <Date 2021-07-15.21:18:56.228>
actor = 'erlendaasland'
assignee = 'none'
closed = False
closed_date = None
closer = None
components = ['Library (Lib)']
creation = <Date 2018-09-28.06:51:55.269>
creator = 'itssme'
dependencies = []
files = []
hgrepos = []
issue_num = 34828
keywords = ['patch']
message_count = 3.0
messages = ['326610', '326611', '326657']
nosy_count = 3.0
nosy_names = ['berker.peksag', 'itssme', 'erlendaasland']
pr_nums = ['9621']
priority = 'normal'
resolution = None
stage = 'patch review'
status = 'open'
superseder = None
type = 'behavior'
url = 'https://bugs.python.org/issue34828'
versions = ['Python 3.6', 'Python 3.7', 'Python 3.8']Metadata
Metadata
Assignees
Labels
stdlibStandard Library Python modules in the Lib/ directoryStandard Library Python modules in the Lib/ directorytopic-sqlite3type-bugAn unexpected behavior, bug, or errorAn unexpected behavior, bug, or error
Projects
- StatusShow more project fieldsDone
Activity
itssme commentedon Sep 28, 2018
There is a bug in sqlite3/dump.py when wanting to dump databases that use autoincrement in one or more tables.
The problem is that the iterdump command assumes that the table "sqlite_sequence" is present in the new database in which the old one is dumped into.
From the sqlite3 documentation:
"SQLite keeps track of the largest ROWID using an internal table named "sqlite_sequence". The sqlite_sequence table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. The content of the sqlite_sequence table can be modified using ordinary UPDATE, INSERT, and DELETE statements."
Source: https://sqlite.org/autoinc.html#the_autoincrement_keyword
Example:
BEGIN TRANSACTION;
CREATE TABLE "posts" (
id int primary key
);
INSERT INTO "posts" VALUES(0);
CREATE TABLE "tags" (
id integer primary key autoincrement,
tag varchar(256) unique,
post int references posts
);
INSERT INTO "tags" VALUES(NULL, "test", 0);
COMMIT;
The following code should work but because of the assumption that "sqlite_sequence" exists it will fail:
Here is the ouput of cx.iterdrump()
BEGIN TRANSACTION;
CREATE TABLE "posts" (
id int primary key
);
INSERT INTO "posts" VALUES(0);
DELETE FROM "sqlite_sequence";
INSERT INTO "sqlite_sequence" VALUES('tags',1);
CREATE TABLE "tags" (
id integer primary key autoincrement,
tag varchar(256) unique,
post int references posts
);
INSERT INTO "tags" VALUES(1,'test',0);
COMMIT;
As you can see the problem is that "DELETE FROM "sqlite_sequence";" and "INSERT INTO "sqlite_sequence" VALUES('tags',1);" are put into the dump before that table even exists. They should be put at the end of the transaction. (Like the sqlite3 command ".dump" does.)
Note that some databases that use autoincrement will work as it could be that a table with autoincrement is created before the sqlite_sequence commands are put into the dump.
File: https://github.com/python/cpython/blob/master/Lib/sqlite3/dump.py
I have already forked the repository, written tests etc. and if you want I will create a pull request.
berkerpeksag commentedon Sep 28, 2018
Please do!
Note that 3.4 and 3.5 are in security-fix-only mode and let's decide whether fixing 2.7 is worth the trouble when you submit your PR. Thank you!
itssme commentedon Sep 28, 2018
I made the pull request: #9621
gh-79009: sqlite3.iterdump now correctly handles tables with autoincr…
pythongh-79009: sqlite3.iterdump now correctly handles tables with au…
pythongh-79009: sqlite3.iterdump now correctly handles tables with au…
erlend-aasland commentedon Jun 19, 2022
Fixed in:
gh-79009: sqlite3.iterdump now correctly handles tables with autoincr…
[3.10] gh-79009: sqlite3.iterdump now correctly handles tables with a…
Revert 30935 defer bpo45162 to 312 (#94285)