Hacker News Reader

Comment by philipkglass | Hacker News Reader

I had to go through a lot of comments in that thread to find a working workaround.

On OS X, Open /Applications/Spotify.app/Contents/MacOS/Spotify in a hex editor.

Search for "VACUUM;" Replace with "xxxxxx;"

On Windows, apparently the key "VACUUM;" string is in libcef.dll but I don't have a Windows system to see if editing it the same way provides a workaround like on OS X.

I used hexcurse from homebrew. After opening the file hit tab once to switch to the ASCII side, Control-F to search for VACUUM;, then type "x" six times to overwrite each character. Quit and save.

EDIT: fixes

View on HN

Hex Fiend is another hex editor for the Mac. Native and fast. http://ridiculousfish.com/hexfiend/

If you're looking for vi, but for binary, bvi does the job.

http://bvi.sourceforge.net/

Or use hexl-mode in Emacs

I'm pretty sure vi itself, or at least vim, is fine with binary files. You can run it as `vim -b` or `:set binary`.

I've always liked 0xED (http://www.suavetech.com/0xed/0xed.html)

Or do it with a one liner

perl -pi -e 's/VACUUM/xxxxxx/g' /Applications/Spotify.app/Contents/MacOS/Spotify

An explanation of what this does?

Also, I'm assuming this has to be done after every update, and since Spotify auto-updates, not so convenient...

I didn't read all of the original thread but apparently Spotify is overusing sqlite VACUUM, causing a modest amount of data to turn into a huge write volume.

https://sqlite.org/lang_vacuum.html

I'm more familiar with Postgres, but it basically instructs the table storage provider to clean up unused disk.

Think of it roughly as defragmentation. You're trading work (in the form of writes and CPU) for reclaimed space.

Unless SQLite has some semantics around it, replacing it with "xxxxxx;" will likely just cause it to run a bad command...might be more correct to replace it with something like "------;" (comment characters).

I was going to say "SELECT 1;" but it's two characters too many. Will all comment characters evaluate to whatever VACUUM does?

It likely just stops Spotify from VACUUM'ing the SQLite database it has.

Spotify stores a local SQLite database file (by default on a Mac it's at ~/Library/Application Support/Spotify/PersistentCache/mercury.db )

The file is only about ~100MB (on my computer). With databases, you issue a vacuum command to defragment a database (reclaim space from deleted/updated rows, re-sort the data, etc). SQLite's VACUUM behavior basically just recreates the entire database from scratch in a temp file, then replaces the live file with that[1]. It makes the file smaller and more efficient to run queries against. Spotify must be triggering a VACUUM statement too aggressively (possibly after every change), rather than on a periodic schedule or after a certain amount of fragmentation. By breaking the VACUUM statement with this solution, you prevent the database from getting recreated so frequently. The file itself will grow slowly (since you're not reclaiming free space like a VACUUM would) and possibly slow down queries against the database, but that sounds like a far better tradeoff than excessive wear on the drive from recreating the file so often.

[1]https://sqlite.org/lang_vacuum.html

Years ago, manually vacuuming the several SQLite databases that Firefox used (uses) was often cited as a way to speed up Firefox, meaning particularly an instance/profile that had been heavily used for some time. Not sure about these days, though the other week something had me in Bleachbit (IIRC) and I saw that doing so is still one of that application's selectable functions.

Once you apply that fix you can manually vacuum with

  sqlite3 mercury.db vacuum;

from https://sqlite.org/download.html

That sounds like an incredibly easy thing for Spotify to fix - how is this still an issue?

On the other hand it fits the picture: Their clients/apps on multiple platforms are very underwhelming. The abandoned rdio apps back from 2014 (or when did they close?) would offer a better experiment in almost any aspect.

Agreed.

  if (time_since_last_vacuum > 3600 /* seconds */) {
     go_hogwild_on_io();
  } else {
     be_nice();
  }

Yes, this is an incredibly easy fix. Perhaps it is me but sometimes it seems that nobody wants to write decent software anymore. I have seen software from giants like Cisco and BT that is abysmal.

It is disheartening to those of us who enjoy writing decent software, particularly decent native software.

I can't find the article right now, but I read a while ago that the Spotify client applications are modularized, such that different feature teams can push updates to their individual feature without breaking or being interdependent on other parts of the clients releasing in sync. I'm not sure what the mercury database is used for or if it's maintained by one of those "feature teams" or is part of the core lib, but that lack of central oversight (and responsibility) could be one reason why such an easy fix isn't so easy to get fixed.

Hmmm, could it be an issue with Chromium Embedded Framework, give that it uses SQLite and it's in libcef.dll? I uninstalled Spotify from my MBA but I'm not sure whether the damage was already done...

This fixes it, but it's likely any future update kills this temp fix. Hopefully Spotify will take care of this. I also noticed the extremely high writes less than a week ago. Wonder how long this has been going on.

Use <Control-F> to search and <Control-S> to save, it seems you mixed them up in your otherwise very helpful comment. What's the source of the problem and the fix, though?

Yep, mixed them up. See other comment about sqlite.

That's a pretty cool fix. I wonder how they found that. This also highlights another issue with closed source software (and a few open source apps too): complete opaqueness. Yeah, hide the logging and status by default but at least give me a way to see what's actually going on. The Spotify Android app is worse. I wonder if it also suffers from this issue (I swapped to Apple Music after losing all of my offline tracks in Spotify again)

They probably just monitored what files the spotify client had open, or on Windows uses something like Process Monitor and monitored the program's activities. It would have seen a file open command pretty soon.

And once they saw it open the sqlite.db, all they needed to do was run sqlite3 on the command line against the DB file and see if there was history in the file.

It is possible that the Android client does the same as sqlite3 is the underlying database format for apps and is very easy to use.

Great fix. Only 1.7MB written in about 45 minutes.

Does anyone know why "xxxxxx;"? Seems like that would make an invalid call, so wouldn't it make more sense to replace it with some sort of no-op? Or is xxxxxx sql's way of doing a no-op?

Presumably it results in an error, but if the return value isn't checked then that's fine.

DO 0

SET @noop=0

NULL; -- works in pg at least

seem like good possibilities.

Because that breaks the statement, Spotify doesn't care if this particular statement succeeds or fails, and because it has the same length as "VACUUM;". Since this is being modified in a binary, you can't change the length of the string, or else you'll overwrite some other part of the data.

How does this not break the signature on the binary and stop execution?

is Spotify signed at all? Anyway, worked for me (used '------;' with the python script above).

It's signed on my laptop.

Mine is signed. Maybe the HN crowd tends to have less restrictive GateKeeper settings since running unsigned stuff is kind of a pain in the ass.

6 or 7 x's? In your example, you used 6.

7 x's, one to overwrite all of VACUUM; including the terminating semicolon.

I didn't count the x's in the quoted text that I copied from the thread.

If you overwrite the ; is there not a risk you might cause the next legitimate sqlite operation to fail?

They might get lucky and overwrite the last SQL statement in the method. Seems likely with a statement like VACUUM.

6, it wont start otherwise. I left the ; in.

Hmm, I used 7 and my patched client is running fine. Maybe it's enough to garble VACUUM so it doesn't do anything valid.

If you replaced the ; also, you also didn't change the total length of the strings, I think that's what the problem is.

This patch also works fine on Spotify for Linux if you patch with ghex.

iHex is an alternative (native) hex editor and it's in the Mac App Store https://itunes.apple.com/us/app/ihex-hex-editor/id909566003?...

Spotify updates itself fairly regularly, so you may need to reapply this fix from time to time.

Brute-force solution: save this as spotify-fixer.py, run it as cron job:

  #!/usr/bin/python

  target = "/Applications/Spotify.app/Contents/MacOS/Spotify"
  with open(target) as infile:
      bytes = infile.read()
  with open("./Spotify", "wb") as backup:
      backup.write(bytes)
  fixed = bytes.replace("VACUUM;", "xxxxxx;")
  with open (target, "wb") as outfile:
      outfile.write(fixed)

[retracted]

Thanks logicallee! I definitely need to be more judicious about time management, so I appreciated your comment!

dude. stop. the reason your username is "toomuchtodo" is shit like this. Just do it RIGHT NOW, or don't do it. It's not that important. Delete that stupid bookmark.

Just because something could be fun to do if you didn't have anythign else to do and were bored out of your mind, doesn't mean it's worth doing, ever.

Is the vitrol really necessary? How does what a complete stranger decides to do in their spare time affect you in any way shape or form?

I think it's more tough love than it is vitriol.

> I think it's more tough love

That's how I took it.

yeah

If you're on a Mac you have python already. Just save it somewhere and curl pipe it to python.

No No No, please do not encourage people to do this. Curl pipe is a horrible anti security pattern and you are encouraging people to be irresponsible with patching binaries from the internet.

The weird thing is talking about "curl pipe"ing into Python when you have a local copy? Wha?

The now-retracted comment talked about rewriting the Python into a shell script that you could toss on a web server and curl-pipe to run. I was pointing out that you can curl-pipe the original Python without rewriting it as shell.

If you do that, go the extra mile and schedule a (weekly or so) job to vacuum that database.

Also, since you are on a Mac, I would use launchd jobs and make it less brute-force by scheduling the job changing the file on writes to the Applications/Spotify.app/Contents/MacOS/ directory.

It's beyond me, why doesn't Spotify do exactly the same thing instead of overusing the VACUUM command.

On Ubuntu/Debian, it might make sense to run it after each apt-get:

    $ cat /usr/local/bin/spotify-turn-off-vacuum 
    #!/usr/bin/env python2
    target = "/usr/bin/spotify"
    with open(target) as infile:
        bytes = infile.read()
    with open(target+".bak", "wb") as backup:
        backup.write(bytes)
    fixed = bytes.replace("VACUUM;", "xxxxxx;")
    with open (target, "wb") as outfile:
        outfile.write(fixed)
    
    $ cat /etc/apt/apt.conf.d/99spotify-turn-off-vacuum
    DPkg::Post-Invoke {"/usr/local/bin/spotify-turn-off-vacuum";};

for Linux, it is at /usr/share/spotify/spotify.

Reply on news.ycombinator.com