Description

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'

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']

Activity

1. itssme commented on Sep 28, 2018

   

   itssmemannequin

   on Sep 28, 2018

   MannequinAuthor

   More actions

   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:

   > import sqlite3
   > cx = sqlite3.connect("test.db")
   > for i in cx.iterdump():
   > print i
   > cx2 = sqlite3.connect(":memory:")
   > query = "".join(line for line in cx.iterdump())
   > cx2.executescript(query)

   Exception:
   Traceback (most recent call last):
     File "/home/test.py", line 10, in <module>
       cx2.executescript(query)
   sqlite3.OperationalError: no such table: sqlite_sequence

   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.
2. itssme

   added

   3.7 (EOL)end of lifeend of life

   3.8 (EOL)end of lifeend of life

   stdlibStandard Library Python modules in the Lib/ directoryStandard Library Python modules in the Lib/ directory

   type-bugAn unexpected behavior, bug, or errorAn unexpected behavior, bug, or error

   on Sep 28, 2018
3. berkerpeksag commented on Sep 28, 2018

   

   berkerpeksag

   on Sep 28, 2018

   Member

   More actions

   I have already forked the repository, written tests etc. and if you
   want I will create a pull request.

   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!
4. itssme commented on Sep 28, 2018

   

   itssmemannequin

   on Sep 28, 2018

   MannequinAuthor

   More actions

   I made the pull request: #9621
5. ezio-melotti

   transferred this issue fromon Apr 10, 2022
6. erlend-aasland

   added

   topic-sqlite3

   and removed

   3.8 (EOL)end of lifeend of life

   3.7 (EOL)end of lifeend of life

   on May 16, 2022
7. erlend-aasland

   added this to sqlite3 issueson May 21, 2022
8. erlend-aasland

   moved this to TODO in sqlite3 issueson May 21, 2022
9. erlend-aasland

   moved this from TODO: Docs to TODO: Bugs in sqlite3 issueson May 21, 2022
10. erlend-aasland

    moved this from TODO: Bugs to In Progress in sqlite3 issueson Jun 14, 2022
11. erlend-aasland

    self-assigned this

    on Jun 14, 2022
12. erlend-aasland

    closed this as completedin #9621on Jun 19, 2022
13. erlend-aasland

    added a commit that references this issue on Jun 19, 2022

    gh-79009: sqlite3.iterdump now correctly handles tables with autoincr…

    affa9f2
14. Repository owner moved this from In Progress to Done in sqlite3 issueson Jun 19, 2022
15. miss-islington

    added 2 commits that reference this issue on Jun 19, 2022

    pythongh-79009: sqlite3.iterdump now correctly handles tables with au…

    59b92b8

    pythongh-79009: sqlite3.iterdump now correctly handles tables with au…

    e0e2a9e
16. erlend-aasland commented on Jun 19, 2022

    

    erlend-aasland

    on Jun 19, 2022

    Contributor

    More actions

    Fixed in:

    • main: gh-79009: sqlite3.iterdump now correctly handles tables with autoincrement #9621
    • 3.11: [3.11] gh-79009: sqlite3.iterdump now correctly handles tables with autoincrement (GH-9621) #94014
    • 3.10: [3.10] gh-79009: sqlite3.iterdump now correctly handles tables with autoincrement (GH-9621) #94015
17. miss-islington

    added a commit that references this issue on Jun 19, 2022

    gh-79009: sqlite3.iterdump now correctly handles tables with autoincr…

    5abe4cb
18. erlend-aasland

    added a commit that references this issue on Jun 20, 2022

    [3.10] gh-79009: sqlite3.iterdump now correctly handles tables with a…

    8a1bade
19. serhiy-storchaka

    added a commit that references this issue on Jun 26, 2022

    Revert 30935 defer bpo45162 to 312 (#94285)

    1ffde3e

Issue actions