Filter out spammers and click bait from Google Analytics

During the last few months, a new wonderful type of spam became part of my life: the Google Analytics spam.

As this article describes, what happens is that you start seeing some blatantly bogus traffic coming from a bunch of websites like semalt.com, buttons-for-website.com, darodar.com, or ilovevitaly.com.

Google announced an Automatic Bot and Spider filtering, but as some users on hacker news reported, it doesn’t work reliably.

So far, the only solution to this problem that worked for me is setting a filter, and add spammers to it as they come. There doesn’t seem to be that many as of today, so this approach is still usable.

[Update – July 2015]: if you have a public HTTP/PHP server available, and are willing to invest half a day to install it, piwik is a nice free, open-source Google Analytics alternative. Piwik uses a community-maintained list of spammers that can also be used in Google Analytics. They wrote a blog post about it, too.

I’ve been using piwik for a few weeks now, and I’m happy with it so far. The nice thing is that updates are very easy to apply, and they include the most recent list of spammers available. The thing that could be improved is the installation process, it’s not as easy as it could be (at least if you’re using Nginx as web server). They also have a Cloud-hosted version, but I guess that if you’re using Google Analytics for free, you’re more interested in free alternatives!

To add a filter in Google Analytics:

1. go to your Administration page (last tab on your home page)
2. All filters (on the leftmost column)
3. New filter
4. Choose Filter type “Custom” > “Exclude”
5. Choose “Referral” from the Filter Field menu
6. Set this as Filter pattern:

   semalt\.com|ilovevitaly\.co|priceg\.com|forum\..*darodar\.com|blackhatworth\.com|hulfingtonpost\.com|buttons-for-website\.com

7. Select the views that you want to be filtered (I chose “All web site data”)
8. Save

The filter pattern is a regular expression, so every time you find a new source of spam, simply add another “|spammersite\.com” (remember to escape dots with a backslash, as they mean “any character”).

It’s playing catch-up with spammers, but as long as Google doesn’t find a way to reliably detect them, it’s the only way to get rid of them. I’ve collected those 7 websites in a couple of months, and I’ve seen them being reported by other users as well. Since after setting the filter I’m no longer getting any bogus traffic, it looks like the problem is still relatively small and can be patched on case-by-case basis.

Chromium crashes on Ubuntu 12.04 showing “Your profile could not be opened correctly” after suspend (and keeps crashing)

There are several similar bugs filed Chromium’s issue tracker (see, e.g., this one closed with WontFix, this one for Xubuntu, this other one for ChromeOS, and this one for Mac). I also filed a new bug, which is waiting to be approved. By the way, it’s amazing how much information they require before even considering a bug report. I wonder how much of that is filled by non-developer users… Anyway!

This post is about how to temporarily fix the issue so you can use Chromium again without restarting your PC.

This is the erratic behavior on my machine:

1. after resuming the computer from suspend, open Chromium
2. Chromium shows a number of popups all saying something along the lines of “cannot open user profile, sign in again”
3. clicking OK on all the popups (about 5 of them stacked on top of each other) takes you to the new tab page
4. no matter what you do, after about 30 seconds, Chromium crashes

This goes on and on, until you restart the PC.

Starting chromium from a shell shows what’s going wrong:

ERROR:connection.cc(1060)] Web sqlite error 5, errno 0: database is locked, sql: PRAGMA journal_mode = PERSIST
ERROR:desktop_window_tree_host_x11.cc(1478)] Not implemented reached in void views::DesktopWindowTreeHostX11::MapWindow(ui::WindowShowState)
ERROR:connection.cc(1060)] Web sqlite error 5, errno 0: database is locked, sql: PRAGMA cache_size=32
ERROR:connection.cc(1060)] Web sqlite error 5, errno 0: database is locked, sql: CREATE TABLE meta(key LONGVARCHAR NOT NULL UNIQUE PRIMARY KEY, value LONGVARCHAR)
ERROR:web_data_service_backend.cc(54)] Cannot initialize the web database: 1
ERROR:desktop_window_tree_host_x11.cc(766)] Not implemented reached in virtual void views::DesktopWindowTreeHostX11::InitModalType(ui::ModalType)
ERROR:desktop_window_tree_host_x11.cc(766)] Not implemented reached in virtual void views::DesktopWindowTreeHostX11::InitModalType(ui::ModalType)
ERROR:desktop_window_tree_host_x11.cc(766)] Not implemented reached in virtual void views::DesktopWindowTreeHostX11::InitModalType(ui::ModalType)
ERROR:desktop_window_tree_host_x11.cc(766)] Not implemented reached in virtual void views::DesktopWindowTreeHostX11::InitModalType(ui::ModalType)
ERROR:database.cc(499)] Database error: IO error: /home/myUserName/.config/chromium/Performance Monitor Databases/Recent Metrics/LOCK: No further details. (ChromeMethodPFE: 15::LockFile::1). Trying again.
ERROR:desktop_window_tree_host_x11.cc(766)] Not implemented reached in virtual void views::DesktopWindowTreeHostX11::InitModalType(ui::ModalType)
ERROR:desktop_window_tree_host_x11.cc(766)] Not implemented reached in virtual void views::DesktopWindowTreeHostX11::InitModalType(ui::ModalType)
ERROR:connection.cc(1060)] Quota sqlite error 5, errno 0: database is locked, sql: UPDATE OriginInfoTable SET used_count = ?, last_access_time = ? WHERE origin = ? AND type = ?
ERROR:database.cc(518)] Failed to delete database. IO error: /home/myUserName/.config/chromium/Performance Monitor Databases/Recent Metrics/LOCK: No further details. (ChromeMethodPFE: 15::LockFile::1)
ERROR:connection.cc(1060)] Quota sqlite error 5, errno 0: database is locked, sql: UPDATE OriginInfoTable SET last_modified_time = ? WHERE origin = ? AND type = ?
ERROR:database.cc(499)] Database error: IO error: /home/myUserName/.config/chromium/Performance Monitor Databases/Max Value Metrics/LOCK: No further details. (ChromeMethodPFE: 15::LockFile::1). Trying again.
ERROR:database.cc(518)] Failed to delete database. IO error: /home/myUserName/.config/chromium/Performance Monitor Databases/Max Value Metrics/LOCK: No further details. (ChromeMethodPFE: 15::LockFile::1)
ERROR:database.cc(499)] Database error: IO error: /home/myUserName/.config/chromium/Performance Monitor Databases/Configuration/LOCK: No further details. (ChromeMethodPFE: 15::LockFile::1). Trying again.
ERROR:connection.cc(1060)] Quota sqlite error 5, errno 0: database is locked, sql: UPDATE OriginInfoTable SET used_count = ?, last_access_time = ? WHERE origin = ? AND type = ?
ERROR:connection.cc(1060)] Quota sqlite error 5, errno 0: database is locked, sql: UPDATE OriginInfoTable SET last_modified_time = ? WHERE origin = ? AND type = ?
ERROR:connection.cc(1060)] Quota sqlite error 5, errno 0: database is locked, sql: UPDATE OriginInfoTable SET last_modified_time = ? WHERE origin = ? AND type = ?
ERROR:CONSOLE(68)] "Uncaught Database not available", source: https://www.google.it/xjs/_/js/editedBecauseImACoward (68)
ERROR:database.cc(518)] Failed to delete database. IO error: /home/myUserName/.config/chromium/Performance Monitor Databases/Configuration/LOCK: No further details. (ChromeMethodPFE: 15::LockFile::1)
ERROR:database.cc(499)] Database error: IO error: /home/myUserName/.config/chromium/Performance Monitor Databases/Active Interval/LOCK: No further details. (ChromeMethodPFE: 15::LockFile::1). Trying again.
ERROR:connection.cc(1060)] Quota sqlite error 5, errno 0: database is locked, sql: UPDATE OriginInfoTable SET last_modified_time = ? WHERE origin = ? AND type = ?
ERROR:database.cc(518)] Failed to delete database. IO error: /home/myUserName/.config/chromium/Performance Monitor Databases/Active Interval/LOCK: No further details. (ChromeMethodPFE: 15::LockFile::1)
ERROR:database.cc(499)] Database error: IO error: /home/myUserName/.config/chromium/Performance Monitor Databases/Metrics/LOCK: No further details. (ChromeMethodPFE: 15::LockFile::1). Trying again.
ERROR:connection.cc(1060)] Quota sqlite error 5, errno 0: database is locked, sql: UPDATE OriginInfoTable SET last_modified_time = ? WHERE origin = ? AND type = ?
ERROR:connection.cc(1060)] Quota sqlite error 5, errno 0: database is locked, sql: UPDATE OriginInfoTable SET last_modified_time = ? WHERE origin = ? AND type = ?
ERROR:database.cc(518)] Failed to delete database. IO error: /home/myUserName/.config/chromium/Performance Monitor Databases/Metrics/LOCK: No further details. (ChromeMethodPFE: 15::LockFile::1)
ERROR:connection.cc(1060)] Quota sqlite error 5, errno 0: database is locked, sql: UPDATE OriginInfoTable SET last_modified_time = ? WHERE origin = ? AND type = ?
ERROR:connection.cc(1060)] Quota sqlite error 5, errno 0: database is locked, sql: UPDATE OriginInfoTable SET last_modified_time = ? WHERE origin = ? AND type = ?
ERROR:connection.cc(1060)] Quota sqlite error 5, errno 0: database is locked, sql: UPDATE OriginInfoTable SET last_modified_time = ? WHERE origin = ? AND type = ?
ERROR:database.cc(499)] Database error: IO error: /home/myUserName/.config/chromium/Performance Monitor Databases/Events/LOCK: No further details. (ChromeMethodPFE: 15::LockFile::1). Trying again.
ERROR:connection.cc(1060)] Quota sqlite error 5, errno 0: database is locked, sql: UPDATE OriginInfoTable SET last_modified_time = ? WHERE origin = ? AND type = ?
ERROR:connection.cc(1060)] Quota sqlite error 5, errno 0: database is locked, sql: UPDATE OriginInfoTable SET last_modified_time = ? WHERE origin = ? AND type = ?
ERROR:database.cc(518)] Failed to delete database. IO error: /home/myUserName/.config/chromium/Performance Monitor Databases/Events/LOCK: No further details. (ChromeMethodPFE: 15::LockFile::1)
ERROR:performance_monitor.cc(177)] Could not initialize database; aborting initialization.

(timestamps omitted for “readability”)

What seems to be going on is that chromium didn’t clean up its LOCK files before suspend happened, and something is preventing it from deleting them.

Deleting those files manually fixed the problem for me. Since I am not a chromium developer, follow this advice at your own risk!

Everything seems to be working fine after I do this (I can sign in to chrome again, and everything works), but I can’t guarantee you that it’s 100% safe.

Ok, now that I’ve chickened out enough, here’s what to do:

cd ~/.config/chromium/Performance\ Monitor\ Databases
rm */LOCK

and you can start chromium again.

Problem solved (?)

If chromium keeps complaining about your profile being unavailable, I found this comment to be extremely helpful. It looks like it’s the @gnome-keyring-daemon@ process that makes chromium hang, and fail.

If that is the case, try to kill @gnome-keyring-daemon@, kill all chromium processes, and restart chromium:

ps -ef | grep gnome-keyring-daemon
# kill it if there with kill 
ps -ef | grep chromium
# kill them all