{"action":"created","issue":{"url":"https://api.github.com/repos/wallabag/wallabag/issues/2801","repository_url":"https://api.github.com/repos/wallabag/wallabag","labels_url":"https://api.github.com/repos/wallabag/wallabag/issues/2801/labels{/name}","comments_url":"https://api.github.com/repos/wallabag/wallabag/issues/2801/comments","events_url":"https://api.github.com/repos/wallabag/wallabag/issues/2801/events","html_url":"https://github.com/wallabag/wallabag/issues/2801","id":203939952,"number":2801,"title":"SQLite Upgrade errors (2.1.6 - 2.2.0)","user":{"login":"benbeton","id":1405209,"avatar_url":"https://avatars.githubusercontent.com/u/1405209?v=3","gravatar_id":"","url":"https://api.github.com/users/benbeton","html_url":"https://github.com/benbeton","followers_url":"https://api.github.com/users/benbeton/followers","following_url":"https://api.github.com/users/benbeton/following{/other_user}","gists_url":"https://api.github.com/users/benbeton/gists{/gist_id}","starred_url":"https://api.github.com/users/benbeton/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/benbeton/subscriptions","organizations_url":"https://api.github.com/users/benbeton/orgs","repos_url":"https://api.github.com/users/benbeton/repos","events_url":"https://api.github.com/users/benbeton/events{/privacy}","received_events_url":"https://api.github.com/users/benbeton/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2017-01-30T07:04:53Z","updated_at":"2017-01-31T07:51:06Z","closed_at":null,"body":"### Issue details\r\n\r\ncreated a SQLite update script:\r\n\r\n```\r\nCREATE TEMP TABLE __temp__wallabag_oauth2_clients AS SELECT id, random_id, redirect_uris, secret, allowed_grant_types, name FROM wallabag_oauth2_clients;\r\nDROP TABLE wallabag_oauth2_clients;\r\nCREATE TABLE wallabag_oauth2_clients (id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, random_id VARCHAR(255) NOT NULL COLLATE BINARY, redirect_uris CLOB NOT NULL COLLATE BINARY, secret VARCHAR(255) NOT NULL COLLATE BINARY, allowed_grant_types CLOB NOT NULL COLLATE BINARY, name CLOB DEFAULT NULL COLLATE BINARY, PRIMARY KEY(id), CONSTRAINT IDX_user_oauth_client FOREIGN KEY (user_id) REFERENCES wallabag_user (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE);\r\nINSERT INTO wallabag_oauth2_clients (id, random_id, redirect_uris, secret, allowed_grant_types, name) SELECT id, random_id, redirect_uris, secret, allowed_grant_types, name FROM __temp__wallabag_oauth2_clients;\r\nDROP TABLE __temp__wallabag_oauth2_clients;\r\nCREATE INDEX IDX_635D765EA76ED395 ON wallabag_oauth2_clients (user_id);\r\nINSERT INTO wallabag_craue_config_setting (name, value, section) VALUES ('download_images_enabled', 0, 'misc');\r\nDROP INDEX created_at_idx;\r\nDROP INDEX IDX_F4D18282A76ED395;\r\nCREATE TEMP TABLE __temp__wallabag_entry AS SELECT id, user_id, uuid, title, url, is_archived, is_starred, content, created_at, updated_at, mimetype, language, reading_time, domain_name, preview_picture, is_public FROM wallabag_entry;\r\nDROP TABLE wallabag_entry;\r\nCREATE TABLE wallabag_entry (id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, uuid CLOB DEFAULT NULL COLLATE BINARY, title CLOB DEFAULT NULL COLLATE BINARY, url CLOB DEFAULT NULL COLLATE BINARY, is_archived BOOLEAN NOT NULL, is_starred BOOLEAN NOT NULL, content CLOB DEFAULT NULL COLLATE BINARY, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, mimetype CLOB DEFAULT NULL COLLATE BINARY, language CLOB DEFAULT NULL COLLATE BINARY, reading_time INTEGER DEFAULT NULL, domain_name CLOB DEFAULT NULL COLLATE BINARY, preview_picture CLOB DEFAULT NULL COLLATE BINARY, is_public BOOLEAN DEFAULT '0', PRIMARY KEY(id));\r\nINSERT INTO wallabag_entry (id, user_id, uuid, title, url, is_archived, is_starred, content, created_at, updated_at, mimetype, language, reading_time, domain_name, preview_picture, is_public) SELECT id, user_id, uuid, title, url, is_archived, is_starred, content, created_at, updated_at, mimetype, language, reading_time, domain_name, preview_picture, is_public FROM __temp__wallabag_entry;\r\nDROP TABLE __temp__wallabag_entry;\r\nCREATE INDEX created_at_idx ON wallabag_entry (created_at);\r\nCREATE INDEX IDX_F4D18282A76ED395 ON wallabag_entry (user_id);\r\nCREATE INDEX IDX_entry_created_at ON wallabag_entry (created_at);\r\nALTER TABLE wallabag_config ADD COLUMN action_mark_as_read INTEGER DEFAULT 0;\r\nINSERT INTO wallabag_craue_config_setting (name, value, section) VALUES ('share_unmark', 0, 'entry');\r\nINSERT INTO wallabag_craue_config_setting (name, value, section) VALUES ('unmark_url', 'https://unmark.it', 'entry');\r\nALTER TABLE wallabag_entry ADD COLUMN http_status VARCHAR(3) DEFAULT NULL;\r\nINSERT INTO wallabag_craue_config_setting (name, value, section) VALUES ('restricted_access', 0, 'entry');\r\nDROP INDEX UNIQ_1D63E7E5C05FB297;\r\nDROP INDEX UNIQ_1D63E7E5A0D96FBF;\r\nDROP INDEX UNIQ_1D63E7E592FC23A8;\r\nCREATE TEMP TABLE __temp__wallabag_user AS SELECT id, username, username_canonical, email, email_canonical, enabled, salt, password, last_login, locked, expires_at, confirmation_token, password_requested_at, roles, credentials_expire_at, name, created_at, updated_at, authCode, twoFactorAuthentication, trusted FROM wallabag_user;\r\nDROP TABLE wallabag_user;\r\nCREATE TABLE wallabag_user (id INTEGER NOT NULL, username VARCHAR(180) NOT NULL COLLATE BINARY, username_canonical VARCHAR(180) NOT NULL COLLATE BINARY, email VARCHAR(180) NOT NULL COLLATE BINARY, email_canonical VARCHAR(180) NOT NULL COLLATE BINARY, enabled BOOLEAN NOT NULL, salt VARCHAR(255) NOT NULL COLLATE BINARY, password VARCHAR(255) NOT NULL COLLATE BINARY, last_login DATETIME DEFAULT NULL, locked BOOLEAN NOT NULL, expires_at DATETIME DEFAULT NULL, confirmation_token VARCHAR(180) DEFAULT NULL COLLATE BINARY, password_requested_at DATETIME DEFAULT NULL, roles CLOB NOT NULL COLLATE BINARY, credentials_expire_at DATETIME DEFAULT NULL, name CLOB DEFAULT NULL COLLATE BINARY, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, authCode INTEGER DEFAULT NULL, twoFactorAuthentication BOOLEAN NOT NULL, trusted CLOB DEFAULT NULL COLLATE BINARY, PRIMARY KEY(id));\r\nINSERT INTO wallabag_user (id, username, username_canonical, email, email_canonical, enabled, salt, password, last_login, locked, expires_at, confirmation_token, password_requested_at, roles, credentials_expire_at, name, created_at, updated_at, authCode, twoFactorAuthentication, trusted) SELECT id, username, username_canonical, email, email_canonical, enabled, salt, password, last_login, locked, expires_at, confirmation_token, password_requested_at, roles, credentials_expire_at, name, created_at, updated_at, authCode, twoFactorAuthentication, trusted FROM __temp__wallabag_user;\r\nDROP TABLE __temp__wallabag_user;\r\nCREATE UNIQUE INDEX UNIQ_1D63E7E5C05FB297 ON wallabag_user (confirmation_token);\r\nCREATE UNIQUE INDEX UNIQ_1D63E7E5A0D96FBF ON wallabag_user (email_canonical);\r\nCREATE UNIQUE INDEX UNIQ_1D63E7E592FC23A8 ON wallabag_user (username_canonical);\r\nALTER TABLE wallabag_config ADD COLUMN list_mode INTEGER DEFAULT NULL;\r\nALTER TABLE wallabag_user ADD COLUMN locked SMALLINT DEFAULT NULL;\r\nALTER TABLE wallabag_user ADD COLUMN credentials_expire_at DATETIME DEFAULT NULL;\r\nALTER TABLE wallabag_user ADD COLUMN expires_at DATETIME DEFAULT NULL;\r\nDROP INDEX IDX_F4D18282A76ED395;\r\nDROP INDEX created_at_idx;\r\nCREATE TEMP TABLE __temp__wallabag_entry AS SELECT id, user_id, uid, title, url, is_archived, is_starred, content, created_at, updated_at, mimetype, language, reading_time, domain_name, preview_picture, is_public FROM wallabag_entry;\r\nDROP TABLE wallabag_entry;\r\nCREATE TABLE wallabag_entry (id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, uid CLOB DEFAULT NULL COLLATE BINARY, title CLOB DEFAULT NULL COLLATE BINARY, url CLOB DEFAULT NULL COLLATE BINARY, is_archived BOOLEAN NOT NULL, is_starred BOOLEAN NOT NULL, content CLOB DEFAULT NULL COLLATE BINARY, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, mimetype CLOB DEFAULT NULL COLLATE BINARY, language CLOB DEFAULT NULL COLLATE BINARY, reading_time INTEGER DEFAULT NULL, domain_name CLOB DEFAULT NULL COLLATE BINARY, preview_picture CLOB DEFAULT NULL COLLATE BINARY, is_public BOOLEAN DEFAULT '0', PRIMARY KEY(id));\r\nINSERT INTO wallabag_entry (id, user_id, uid, title, url, is_archived, is_starred, content, created_at, updated_at, mimetype, language, reading_time, domain_name, preview_picture, is_public) SELECT id, user_id, uid, title, url, is_archived, is_starred, content, created_at, updated_at, mimetype, language, reading_time, domain_name, preview_picture, is_public FROM __temp__wallabag_entry;\r\nDROP TABLE __temp__wallabag_entry;\r\nCREATE INDEX IDX_F4D18282A76ED395 ON wallabag_entry (user_id);\r\nCREATE INDEX created_at_idx ON wallabag_entry (created_at);\r\nCREATE INDEX IDX_entry_uid ON wallabag_entry (uid);\r\nDROP INDEX uid;\r\nDROP INDEX created_at;\r\nDROP INDEX IDX_F4D18282A76ED395;\r\nCREATE TEMP TABLE __temp__wallabag_entry AS SELECT id, user_id, uid, title, url, is_archived, is_starred, content, created_at, updated_at, mimetype, language, reading_time, domain_name, preview_picture, is_public, http_status FROM wallabag_entry;\r\nDROP TABLE wallabag_entry;\r\nCREATE TABLE wallabag_entry (id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, uid VARCHAR(23) DEFAULT NULL COLLATE BINARY, title CLOB DEFAULT NULL COLLATE BINARY, url CLOB DEFAULT NULL COLLATE BINARY, is_archived BOOLEAN NOT NULL, is_starred BOOLEAN NOT NULL, content CLOB DEFAULT NULL COLLATE BINARY, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, mimetype CLOB DEFAULT NULL COLLATE BINARY, language CLOB DEFAULT NULL COLLATE BINARY, reading_time INTEGER DEFAULT NULL, domain_name CLOB DEFAULT NULL COLLATE BINARY, preview_picture CLOB DEFAULT NULL COLLATE BINARY, is_public BOOLEAN DEFAULT '0', http_status VARCHAR(3) DEFAULT NULL COLLATE BINARY, PRIMARY KEY(id));\r\nINSERT INTO wallabag_entry (id, user_id, uid, title, url, is_archived, is_starred, content, created_at, updated_at, mimetype, language, reading_time, domain_name, preview_picture, is_public, http_status) SELECT id, user_id, uid, title, url, is_archived, is_starred, content, created_at, updated_at, mimetype, language, reading_time, domain_name, preview_picture, is_public, http_status FROM __temp__wallabag_entry;\r\nDROP TABLE __temp__wallabag_entry;\r\nCREATE INDEX uid ON wallabag_entry (uid);\r\nCREATE INDEX created_at ON wallabag_entry (created_at);\r\nCREATE INDEX IDX_F4D18282A76ED395 ON wallabag_entry (user_id);\r\nCREATE INDEX IDX_entry_starred ON wallabag_entry (is_starred);\r\nCREATE INDEX IDX_entry_archived ON wallabag_entry (is_archived;\r\n\r\n```\r\n### Environment\r\n\r\n* wallabag version (or git revision) that exhibits the issue: standalone\r\n* How did you install wallabag? Via `git clone` or by downloading the package? download\r\n* Last wallabag version that did not exhibit the issue (if applicable): 2.1.6\r\n* php version:\r\n* OS:\r\n* type of hosting (shared or dedicated): shared\r\n* which storage system you choose at install (SQLite, MySQL/MariaDB or PostgreSQL): SQLite\r\n\r\n### Steps to reproduce/test case\r\n\r\nafter applying the script or manual steps I get the following error and a blank website:\r\n\r\n```\r\nsqlite3 bag/data/db/wallabag.sqlite < bag_upgrade.sql\r\nError: near line 8: no such index: created_at_idx\r\nError: near line 23: no such index: UNIQ_1D63E7E5C05FB297\r\nError: near line 35: duplicate column name: locked\r\nError: near line 36: duplicate column name: credentials_expire_at\r\nError: near line 37: duplicate column name: expires_at\r\nError: near line 40: no such column: uid\r\nError: near line 43: no such table: __temp__wallabag_entry\r\nError: near line 44: no such table: __temp__wallabag_entry\r\nError: near line 48: no such index: uid\r\nError: near line 49: no such index: created_at\r\nError: near line 51: no such column: http_status\r\nError: near line 54: no such table: __temp__wallabag_entry\r\nError: near line 55: no such table: __temp__wallabag_entry\r\nError: near line 60: near \";\": syntax error\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/wallabag/wallabag/issues/comments/276296686","html_url":"https://github.com/wallabag/wallabag/issues/2801#issuecomment-276296686","issue_url":"https://api.github.com/repos/wallabag/wallabag/issues/2801","id":276296686,"user":{"login":"Pablo2048","id":6281647,"avatar_url":"https://avatars.githubusercontent.com/u/6281647?v=3","gravatar_id":"","url":"https://api.github.com/users/Pablo2048","html_url":"https://github.com/Pablo2048","followers_url":"https://api.github.com/users/Pablo2048/followers","following_url":"https://api.github.com/users/Pablo2048/following{/other_user}","gists_url":"https://api.github.com/users/Pablo2048/gists{/gist_id}","starred_url":"https://api.github.com/users/Pablo2048/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Pablo2048/subscriptions","organizations_url":"https://api.github.com/users/Pablo2048/orgs","repos_url":"https://api.github.com/users/Pablo2048/repos","events_url":"https://api.github.com/users/Pablo2048/events{/privacy}","received_events_url":"https://api.github.com/users/Pablo2048/received_events","type":"User","site_admin":false},"created_at":"2017-01-31T07:51:06Z","updated_at":"2017-01-31T07:51:06Z","body":"Same problem here - unable to update 2.1.5 -> 2.2.0, Windows shared host, SQLite DB."}}
{"actor":{"display_login":"Pablo2048"}}