Closed Bug 1202398 Opened 4 years ago Closed 3 years ago

PlacesUtils.keywords.insert creates "UNIQUE constraint failed" sqlite error

Categories

(Toolkit :: Places, defect, P2)

40 Branch
defect

Tracking

()

RESOLVED DUPLICATE of bug 1150678

People

(Reporter: panuworld.net, Unassigned)

Details

Attachments

(1 file)

User Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:40.0) Gecko/20100101 Firefox/40.0
Build ID: 20150826023504

Steps to reproduce:

Executed the following in an add-on:

Components.utils.import("resource://gre/modules/XPCOMUtils.jsm")
XPCOMUtils.defineLazyModuleGetter(this, "PlacesUtils", "resource://gre/modules/PlacesUtils.jsm")

PlacesUtils.keywords.insert({ keyword: "g", url: "http://www.google.com/" }).catch( e => { console.log(e) } )



Actual results:

The new keyword works well for a while but after a few start-ups the keyword "g" stops to work and the following message is logged by the catch above:

Error: Error(s) encountered during statement execution: UNIQUE constraint failed: moz_keywords.keyword
Stack trace:
ConnectionData.prototype<._executeStatement/pending<.handleCompletion@resource://gre/modules/Sqlite.jsm:685:25
1 xxxxxxxx.js:20:51

Also, when trying to add a keyword "g" for a bookmark manually will also be ignored. No error message is displayed, the keyword just does not get saved in the bookmark. Other keywords than "g" continue work normally and can be assigned to e.g. bookmarks.

Deleting places.sqlite temporarily fixes the problem but it always reappears after some (tens) of start-ups.


Expected results:

PlacesUtils.keywords.insert() should continue working without errors, like it has done many times every time before the problem pops up. 

Reusing the same keyword should not be the problem because keywords.insert() should redefine the existing keyword in such case, and also trying to keywords.remove() the keyword just before keywords.insert() does not help.
Component: Untriaged → Places
Product: Firefox → Toolkit
using the same keyword for different urls is not supported, you need to change the keyword or to remove the previous one. We don't automatically change the url for a keyword cause that could easily hide development mistakes.
Note these APIs return promises, you must wait for the insert promise to be complete before you can remove (and so on)
hm, looks like I was wrong about the automatic update, we do update the uri.

Can you please give me a test case,even runnable in scratchpad, that exhibits the misbehavior you are pointing out?

The error is pointing out that some code is trying to insert a duplicate keyword, are you trying to insert a keyword to an uri that already has one?
Flags: needinfo?(panuworld.net)
Keywords: testcase-wanted
I wonder how to make a testcase because the issue may need several days to appear, and I do not know what actually triggers the problem. (After deleting place.sqlite the code works well, no matter how many restarts I do. Then, usually a few days later, the keywords stop working.)

The original idea of the code is to insert the same keywords and uris on every start-up, to ensure that the user has the intranet service keywords defined as intended. As I have understood from "Using the Places keywords API", re-inserting an existing keyword(+uri) should not do any harm, instead it should simply override possible uri change made by user.

I try to make the issue more easily reproducible, and then build a testcase.
Thanks, any information can be useful.

yes, reinserting the same keyword+url couple through PlacesUtils.keywords.insert() should be a no-op.
After working fine for this week, today the keywords stopped to work, and it look like that the keywords.insert() are failing the same exception as earlier.

Currently trying to execute the following in scratchpad throws the error, however, I am not able to describe how to corrupt the Places database in order to get into this state:
PlacesUtils.keywords.insert( { keyword: "a", url: "http://www.google.com/" } ).catch( e => { console.log(e)} )

When I debug PlacesUtils.jsm, I see unexpected behavior starting from line 2141 (let cache = yield gKeywordsCachePromise;):
- 2144: let oldEntry = cache.get(keyword); --> leaves oldEntry undefined
- Therefore the "no-op" 2147: return is not executed, instead execution continues to
- 2167: yield db.executeCached( `INSERT OR IGNORE INTO moz_places (url, ... --> which executes ok
- 2172: yield db.executeCached( `INSERT INTO moz_keywords (keyword, ... --> throws the exception

It looks like that the keyword "a" is still present in table moz_keywords, although the cache.get(keyword) returned undefined i.e. the oldEntry was not detected.

If I manually force execution to line 2157: yield db.executeCached( `UPDATE moz_keywords SET place_id = ... no exceptions are thrown and the keywords starts to work again.
Flags: needinfo?(panuworld.net)
Additional info: The "cache" on line 2141 seems to contain only those keywords that are explicitly defined in my bookmarks (the default bookmarks "wp", "dict", "google", "quote"). None of those keywords that my code has inserted earlier can be found there, although they seem to still be in moz_keywords table. Why?

Note that I am inserting keywords for uris that are not bookmarked. I understood that having a bookmark for the uri is not required in order to make a keyword to work.
(In reply to Panu Tuominen from comment #5)
> - 2172: yield db.executeCached( `INSERT INTO moz_keywords (keyword, ... -->
> throws the exception

yes this is compatible with the error, the insert fails because there is already such an entry, so the insert violates a schema constraint.

> It looks like that the keyword "a" is still present in table moz_keywords,
> although the cache.get(keyword) returned undefined i.e. the oldEntry was not
> detected.

So the problem appears to be here: for some reason the cache doesn't reflect the actual status of the underlying table. There must be actions that causes the cache to go out of sync.

First question: do you use Sync? if it's enabled, it will use the old keywords API, we may restrict the investigation to some weird interaction between the old and the new APIs.

Since you look skilled with development, maybe you could add dumps everywhere we update gKeywordsCache, check that they keywords are properly added, and check where they are wrongly removed?

(In reply to Panu Tuominen from comment #6)
> None of those keywords that my code has
> inserted earlier can be found there, although they seem to still be in
> moz_keywords table. Why?

Tthe cache is populated from a:
SELECT keyword, url, post_data
  FROM moz_keywords k
  JOIN moz_places h ON h.id = k.place_id
there's nothing referring bookmarks.
Something breaks the cache at a later time.

> Note that I am inserting keywords for uris that are not bookmarked. I
> understood that having a bookmark for the uri is not required in order to
> make a keyword to work.

Correct. the only downside for now is that those keywords will be unaccessible from the UI (we need a new UI to manage these)
Status: UNCONFIRMED → NEW
Ever confirmed: true
I do not use Sync, so it can be ruled out.

I scratched my head for a while but I did not yet find a way to add dumps for the gKeywordsCache during browser start-up. When I later add breakpoints from browser console and trigger the insert from scratchpad, then the gKeywordsCache already seem to contain only those keywords I have as bookmarks.

I do not have access to the particular PC anymore this week, so I'll continue testing earliest next week.
Priority: -- → P2
The problem disappeared for a week or so (the keyword insert in the add-on started to work without errors), without any changes made by me. But now it again started to fail. I have not figured out what is changing in my installation so that on some weeks it works and on some others it won't.
Since comment 5 and comment 6 provided a reduced test case that helped Marco confirmed this bug, I will remove the "testcase-wanted" keyword.
Keywords: testcase-wanted
NEW DETAILED INFO: The problem still appears randomly, staying there for weeks and then disappears. However, I analyzed the places.sqlite manually and noted a difference there when the problem occurs vs. when it does not. (See attachment)

When the problem occurs the keyword is defined in table moz_keywords but the corresponding place_id is not found in moz_places.

If I delete the keyword, the problem disappears, and the keyword can be successfully added from the add-on. After that I see the new entry appended to moz_keywords and _also_ to moz_places with corresponding id. The problem seem not to re-appear as long as the corresponding entry is in moz_places. However, the entry in moz_places is lost, the corresponding keyword is again "locked".

No idea what actually deletes the entry from moz_places. Someone cleaning moz_places every now and then? (Because these are pure keyword-only entries, they are not referring to any bookmark for history place.)

However, I think that the Firefox should be able to recover cleanly from situation where a keyword entry is present in moz_keywords without corresponding entry in moz_places.
Status: NEW → RESOLVED
Closed: 3 years ago
Resolution: --- → DUPLICATE
Duplicate of bug: 1150678
You need to log in before you can comment on or make changes to this bug.