1072406 - Automatically detect and repair issues with writing to permissions.sqlite

Status: NEW

(Core :: Permission Manager, defect)

Description

[Jared Beck]

User Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:32.0) Gecko/20100101 Firefox/32.0
Build ID: 20140917194002

Steps to reproduce:

1. Under plugins, set Google Talk to "Ask to Activate"
2. Log into gmail
3. See ".. wants to run Google Talk plugin"
4. Click "Allow and Remember"
5. Quit FF
6. Reopen FF, log into gmail
7. See ".. wants to run Google Talk plugin"
8. Reproduced.


Actual results:

See steps to reproduce


Expected results:

FF should remember site-specific setting

Comment 1

[Jared Beck]

First reported here: https://bugzilla.mozilla.org/show_bug.cgi?id=1030289

Comment 2

[Benjamin Smedberg]

Paul, could you do some investigative QA here and see whether this is specific to the google talk website or whether we broke this for all websites? As before, http://benjamin.smedbergs.us/tests/ctptests/flash-solo.html is probably the simplest thing to test against.

Comment 3

[Paul Silaghi, QA [:pauly]]

I couldn't reproduce the problem on FF 32.0.2, 35.0a1 (2014-09-24) OS X 10.9.5, Win 7 x64.
'Allow and Remember' works fine on gmail, google+ (hangout). Also tested couple other flash, java, silverlight sites.

Jared, please try again on a clean profile:
http://support.mozilla.org/en-US/kb/Managing-profiles#w_starting-the-profile-manager

Comment 4

[Jared Beck]

> Jared, please try again on a clean profile

Works fine with a clean profile.  What's the next step?  It seems to me, not knowing exactly how profiles work, that no amount of bookmarks or preferences should cause 'Allow and Rembember' to fail.  What, in a profile, could do that?  Thanks.

Comment 5

[Paul Silaghi, QA [:pauly]]

(In reply to Jared Beck from comment #4)
> What, in a profile, could do that?  Thanks.
One of your extensions. Disable them one by one until you find the culprit.

Comment 6

[:Gijs (he/him)]

(In reply to Paul Silaghi, QA [:pauly] from comment #5)
> (In reply to Jared Beck from comment #4)
> > What, in a profile, could do that?  Thanks.
> One of your extensions. Disable them one by one until you find the culprit.

Well, either that, or the permissions DB is corrupt. That's permissions.sqlite in your profile (you can find the folder from about:support and open it in Finder from there). You could try moving it out of the way (copy to a different file) to see if that fixes it.

Comment 7

[Dave Schmidt]

I confirm this behavior:
- "Allow and remember" only remembers until FF is closed.
- Works correctly with a clean profile, but not with my default profile.
- Still fails to remember in my default profile even with all the extensions disabled.

Comment 8

[Dave Schmidt]

(In reply to Dave Schmidt from comment #7)
> I confirm this behavior:
> - "Allow and remember" only remembers until FF is closed.
> - Works correctly with a clean profile, but not with my default profile.
> - Still fails to remember in my default profile even with all the extensions
> disabled.

Also happens with Silverlight on Netflix.

I am using Firefox 34.  This problem started recently for me, so I'm wondering if it's related to a recent version of Firefox.

Comment 9

[:Gijs (he/him)]

(In reply to Dave Schmidt from comment #8)
> (In reply to Dave Schmidt from comment #7)
> > I confirm this behavior:
> > - "Allow and remember" only remembers until FF is closed.
> > - Works correctly with a clean profile, but not with my default profile.
> > - Still fails to remember in my default profile even with all the extensions
> > disabled.
> 
> Also happens with Silverlight on Netflix.
> 
> I am using Firefox 34.  This problem started recently for me, so I'm
> wondering if it's related to a recent version of Firefox.

Can you try moving the permissions.sqlite file in your profile out of the way as suggested in comment #6 and telling us if that fixed this for you?

Comment 10

[Dave Schmidt]

(In reply to :Gijs Kruitbosch from comment #9)
> (In reply to Dave Schmidt from comment #8)
> > (In reply to Dave Schmidt from comment #7)
> > > I confirm this behavior:
> > > - "Allow and remember" only remembers until FF is closed.
> > > - Works correctly with a clean profile, but not with my default profile.
> > > - Still fails to remember in my default profile even with all the extensions
> > > disabled.
> > 
> > Also happens with Silverlight on Netflix.
> > 
> > I am using Firefox 34.  This problem started recently for me, so I'm
> > wondering if it's related to a recent version of Firefox.
> 
> Can you try moving the permissions.sqlite file in your profile out of the
> way as suggested in comment #6 and telling us if that fixed this for you?

Yep, that seemed to solve the problem.  Gmail/Talk and Netflix/Silverlight allowances were both remembered after restarting FF.

Comment 11

[:Gijs (he/him)]

(In reply to Dave Schmidt from comment #10)
> (In reply to :Gijs Kruitbosch from comment #9)
> > Can you try moving the permissions.sqlite file in your profile out of the
> > way as suggested in comment #6 and telling us if that fixed this for you?
> 
> Yep, that seemed to solve the problem.  Gmail/Talk and Netflix/Silverlight
> allowances were both remembered after restarting FF.

Great!

Any chance you would be willing to share the broken file so we can figure out what was broken? To check the most basic thing, did your user have write permissions to the file, and did you reboot your system at least once after the problem started (to be sure there wasn't a stray process blocking new processes from accessing it or keeping a lock on the db or something) ?

NB: if you share the file, the site-specific permissions details could in principle give away what pages you were visiting, so it's a little privacy-sensitive... depending on where you enable plugins / change site permissions (geolocation, webrtc, ...). :-(

Marco/Felipe, could either of you help determining what is going wrong, assuming we can get our hands on a broken file - if not, could you suggest someone else?

Comment 12

[Dave Schmidt]

(In reply to :Gijs Kruitbosch from comment #11)
> (In reply to Dave Schmidt from comment #10)
> > (In reply to :Gijs Kruitbosch from comment #9)
> > > Can you try moving the permissions.sqlite file in your profile out of the
> > > way as suggested in comment #6 and telling us if that fixed this for you?
> > 
> > Yep, that seemed to solve the problem.  Gmail/Talk and Netflix/Silverlight
> > allowances were both remembered after restarting FF.
> 
> Great!
> 
> Any chance you would be willing to share the broken file so we can figure
> out what was broken? To check the most basic thing, did your user have write
> permissions to the file, and did you reboot your system at least once after
> the problem started (to be sure there wasn't a stray process blocking new
> processes from accessing it or keeping a lock on the db or something) ?
> 
> NB: if you share the file, the site-specific permissions details could in
> principle give away what pages you were visiting, so it's a little
> privacy-sensitive... depending on where you enable plugins / change site
> permissions (geolocation, webrtc, ...). :-(
> 
> Marco/Felipe, could either of you help determining what is going wrong,
> assuming we can get our hands on a broken file - if not, could you suggest
> someone else?

I'm pretty sure it's not a permission issue.  I downloaded an sqlite viewer and when I open the newly created permissions.sqlite, I can see data in the moz_hosts table.  However, in the bad file, the table appears empty.  I ran "strings" on the bad file, and I see plenty of URLs in there.  I didn't see any URLs that I was too worried about sharing, so I can provide the bad file; however, I'd still prefer to email it to one or two individuals rather than post to this bug for the world.

Comment 13

[:Gijs (he/him)]

(In reply to Dave Schmidt from comment #12)
> I'm pretty sure it's not a permission issue.  I downloaded an sqlite viewer
> and when I open the newly created permissions.sqlite, I can see data in the
> moz_hosts table.  However, in the bad file, the table appears empty.  I ran
> "strings" on the bad file, and I see plenty of URLs in there. 

This is already helpful. I'm not a sqlite expert, but that basically sounds like the DB got corrupted somehow (maybe if/when Firefox crashed or the OS was unexpectedly shutdown, maybe because sqlite/firefox made a mistake in writing to it). Thanks for diving in.


> I didn't see
> any URLs that I was too worried about sharing, so I can provide the bad
> file; however, I'd still prefer to email it to one or two individuals rather
> than post to this bug for the world.

Oh, for sure! I'm still waiting to hear from Marco and/or Felipe... once we have someone who (unlike me) knows a bit more about our sqlite implementation, it may be helpful to share the DB with them. We can needinfo you again then, or you can email it to me and I can forward it privately. Either works for me/us, I expect, but I'd totally understand if you prefer limiting the number of people who have it as much as possible. :-)

Comment 14

[Dave Schmidt]

(In reply to :Gijs Kruitbosch from comment #13)
 
> > I didn't see
> > any URLs that I was too worried about sharing, so I can provide the bad
> > file; however, I'd still prefer to email it to one or two individuals rather
> > than post to this bug for the world.
> 
> Oh, for sure! I'm still waiting to hear from Marco and/or Felipe... once we
> have someone who (unlike me) knows a bit more about our sqlite
> implementation, it may be helpful to share the DB with them. We can needinfo
> you again then, or you can email it to me and I can forward it privately.
> Either works for me/us, I expect, but I'd totally understand if you prefer
> limiting the number of people who have it as much as possible. :-)

I emailed it to you.  Hopefully it yields some clues and leads to a fix.  Thanks.

Comment 15

[Marco Bonardo [:mak] (Away Apr 25 - May 5)]

I'm not very surprised that this file can corrupt easily since the connection is setup like:
mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING("PRAGMA synchronous = OFF"));
that means we never fsync, that is, we never wait for the data to be written safely on disk.

On crash/powerloss it's very easy to break both data coherency and the database file format.
This was done for performance, it would be interesting to measure if we'd have any interesting perf hit moving to WAL journaling, that would at least ensure transactions atomicity, fsyncing only when the journal gets merged. It depends on the writes traffic we get here, if it's low it may not affect performances, if not for some ms on shutdown.

We also don't seem to do any special action to ensure the database is sane, apart from deleting it (or trying to delete it) if openDatabase fails, that happens very rarely even in case of real corruption.

FWIW, looks like we do the same for cookies.sqlite, but there we use WAL (with synchronous off is not really safer, I guess we do that mostly for the concurrency win). I expect cookies traffic to be higher to there it might make more sense.

Moreover see also bug 975996, this component should be rewritten to use async Storage API.

The first thing I'd suggest is to run a PRAGMA integrity_check on the database, to establish if this is a file corruption or a data coherency corruption.

Comment 16

[:Gijs (he/him)]

(In reply to Marco Bonardo [::mak] (needinfo? me) from comment #15)
> The first thing I'd suggest is to run a PRAGMA integrity_check on the
> database, to establish if this is a file corruption or a data coherency
> corruption.


*** in database main ***
On tree page 43 cell 13: Rowid 58527 out of order (max larger than parent max of 58506)
On tree page 58 cell 14: Rowid 58674 out of order (max larger than parent max of 58664)
On page 2 at right child: 2nd reference to page 34

Comment 17

[Marco Bonardo [:mak] (Away Apr 25 - May 5)]

yeah, that's file corruption.
probably fixable by integrity_check (that now should return OK).

I wonder if now it would work or if there's more...

btw, no easy solution here, apart from investigating using WAL+synchronous=NORMAL or rewriting the whole component to be more robust.

Comment 18

[:Gavin Sharp [email: [e-mail]]]

Sounds like we'd rather invest in removing the use of SQLite (or at least main-thread sqlite) rather than patching the current error handling.

Comment 19

[WBT]

I and at least a couple others are experiencing this bug still:
https://twitter.com/dev_masrani/status/694021157800939525

Comment 20

[Samuel]

I have changed the Plugin settings of the following to Always Activate, and the issue seems to be fixed. 

Google Talk Plugin 
Google Talk Plugin Video Accelerator