[AT&T Samsung Galaxy S II SGH-I777] othermark:
STD DISCLAIMER: I am not responsible for you screwing up your phone. Make BACKUPS!

I have the ever-present problem with the media files on my phone showing up as duplicates. I see from around the forums that several other users experience this regularly on CM9/CM10/AOKP based roms. I'm not sure why this is happening, but it seems to be a side-effect of the mediascanner re-indexing media it shouldn't. In this guide I show you what is going on with your data and how to remove the duplicates.

REQUIREMENTS

Your phone must be rooted to access the sqlite3 database.

You must have debugging turned on your phone

You must have access to adb and ideally have used it in the past. This thread is not about adb/driver setup!

PROCEDURE

Connect your phone via USB and make sure your adb server is started.

[root@moby ~]$ adb start-server
* daemon not running. starting it now on port 5037 *
* daemon started successfully *

Run 'adb shell' and become superuser (run 'su')

[root@moby ~]$ adb shell
shell@android:/ $
255|shell@android:/ $ su
su
shell@android:/ #

cd to /data/data/com.android.providers.media/databases/

shell@android:/ # cd /data/data/com.android.providers.media/databases/
cd /data/data/com.android.providers.media/databases/
shell@android:/data/data/com.android.providers.media/databases # ls
ls
external.db
external.db-shm
external.db-wal
internal.db
internal.db-shm
internal.db-wal

Attach to the external.db database with sqlite3

shell@android:/data/data/com.android.providers.media/databases # sqlite3 external.db
external.db <
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

view the avaible tables (.tables). Now almost every table here, except for the 'files' table is a view based on the files table. What happens is that some data is in 'files' is blanked out and the views create duplicates of the data with seemingly good filenames. However since the primary piece of data in 'files' is missing, all the media players relying on this database freak out.

sqlite> .tables
.tables
album_art audio files
album_info audio_genres images
albums audio_genres_map search
android_metadata audio_genres_map_noid searchhelpertitle
artist_info audio_meta thumbnails
artists audio_playlists video
artists_albums_map audio_playlists_map videothumbnails
sqlite>

look at the files table sql schema and find an example (.schema files). With the duplicate problem bad data is created with the '_data' field blanked out, but a valid media_type=2, since views rely on the media_type=2 but merges other fields with the file name, etc. you get a media entry that looks good but no entry to pull off the file system. See the _data section of the single entry I queried below to see what a good record looks like. A bad record will have a different '_id' and blank '_data'

sqlite> .schema files
.schema files
CREATE TABLE files (_id INTEGER PRIMARY KEY AUTOINCREMENT,_data TEXT,_size INTEGER,format INTEGER,parent INTEGER,date_added INTEGER,date_modified INTEGER,mime_type TEXT,title TEXT,description TEXT,_display_name TEXT,picasa_id TEXT,orientation INTEGER,latitude DOUBLE,longitude DOUBLE,datetaken INTEGER,mini_thumb_magic INTEGER,bucket_id TEXT,bucket_display_name TEXT,isprivate INTEGER,title_key TEXT,artist_id INTEGER,album_id INTEGER,composer TEXT,track INTEGER,year INTEGER CHECK(year!=0),is_ringtone INTEGER,is_music INTEGER,is_alarm INTEGER,is_notification INTEGER,is_podcast INTEGER,album_artist TEXT,duration INTEGER,bookmark INTEGER,artist TEXT,album TEXT,resolution TEXT,tags TEXT,category TEXT,language TEXT,mini_thumb_data TEXT,name TEXT,media_type INTEGER,old_id INTEGER, storage_id INTEGER, is_drm INTEGER, width INTEGER, height INTEGER);
CREATE INDEX album_id_idx ON files(album_id);
CREATE INDEX artist_id_idx ON files(artist_id);
CREATE INDEX bucket_index on files(bucket_id, media_type, datetaken, _id);
CREATE INDEX bucket_name on files(bucket_id, media_type, bucket_display_name);
CREATE INDEX format_index ON files(format);
CREATE INDEX media_type_index ON files(media_type);
CREATE INDEX parent_index ON files(parent);
CREATE INDEX path_index ON files(_data);
CREATE INDEX sort_index ON files(datetaken ASC, _id ASC);
CREATE INDEX title_idx ON files(title);
CREATE INDEX titlekey_index ON files(title_key);
CREATE TRIGGER audio_meta_cleanup DELETE ON files WHEN old.media_type = 2 BEGIN DELETE FROM audio_genres_map WHERE audio_id = old._id;DELETE FROM audio_playlists_map WHERE audio_id = old._id;END;
CREATE TRIGGER audio_playlists_cleanup DELETE ON files WHEN old.media_type = 4 BEGIN DELETE FROM audio_playlists_map WHERE playlist_id = old._id;SELECT _DELETE_FILE(old._data);END;
CREATE TRIGGER files_cleanup DELETE ON files BEGIN SELECT _OBJECT_REMOVED(old._id);END;
CREATE TRIGGER images_cleanup DELETE ON files WHEN old.media_type = 1 BEGIN DELETE FROM thumbnails WHERE image_id = old._id;SELECT _DELETE_FILE(old._data);END;
CREATE TRIGGER video_cleanup DELETE ON files WHEN old.media_type = 3 BEGIN SELECT _DELETE_FILE(old._data);END;
sqlite>
sqlite> select * from files where _data like '%Unhuman%';
select * from files where _data like '%Unhuman%';
16566|/mnt/emmc/mp3/Architect - 2010 - Consume Adapt Create/Architect - 04 - Unhuman.ogg|8176842|47362|16559|1345650424|1337460350|application/ogg|Unhuman||Architect - 04 - Unhuman.ogg|||||||-1041554543|Architect - 2010 - Consume Adapt Create|| O A 5 O ? ' A |5|7||4||0|1|0|0|0||338973||||||||||2||131073|0||

query the duplicates in the files tables. Here I look at just one example... This is from a later date than the earlier section. You can see how the one media file id = 16566 has been re-indexed again. 16566 and 19522 have been blanked and new record 22822 has been indexed with the complete record.

sqlite> select _id,_data,_display_name from files WHERE _data = '' AND media_type = 2;
select _id,_data,_display_name from files WHERE _data = '' AND media_type = 2;
19518||Architect - 13 - Wachsmuth.ogg
19519||Architect - 01 - The ***** is Back.ogg
19520||Architect - 02 - The Shadow of Eve.ogg
19521||Architect - 03 - Fast Lane (Freeze Frame).ogg
19522||Architect - 04 - Unhuman.ogg
19523||Architect - 05 - For You.ogg
19524||Architect - 06 - So I Went Out.ogg
19525||Architect - 08 - Attack Ships on Fire.ogg
19526||Architect - 09 - Pure.ogg
19527||Architect - 10 - I Lost my 808 on a Rainy Day.ogg
19528||Architect - 11 - Awake (Album Version).ogg
19529||Architect - 12 - The Beauty and the Beat (Rokka).ogg
<<more bad entries>>
sqlite> select * from files where _display_name like '%Unhuman%';
select * from files where _display_name like '%Unhuman%';
16566||8176842|47362|16559|1345650424|1337460350|application/ogg|Unhuman||Architect - 04 - Unhuman.ogg|||||||-1041554543|Architect - 2010 - Consume Adapt Create|| O A 5 O ? 'A |5|7||4||0|1|0|0|0||338973||||||||||2||131073|0||
19522||8176842|47362|19515|1345728924|1337460350|application/ogg|Unhuman||Architect - 04 - Unhuman.ogg|||||||-1041554543|Architect - 2010 - Consume Adapt Create|| O A 5 O ? 'A |5|7||4||0|1|0|0|0||338973||||||||||2||131073|0||
22822|/mnt/emmc/mp3/Architect - 2010 - Consume Adapt Create/Architect - 04 - Unhuman.ogg|8176842|47362|22815|1346073675|1337460350|application/ogg|Unhuman||Architect - 04 - Unhuman.ogg|||||||-1041554543|Architect - 2010 - Consume Adapt Create|| O A 5 O ? ' A |5|7||4||0|1|0|0|0||338973||||||||||2||131073|0||

delete all the duplicates. Note we won't be able to delete until we temporarily remove any triggers referring to api functions. If you look at '.schema' again you'll note a trigger refer to _OBJECT_REMOVED, we'll need to remove them. So STEP1. List schema -- cut and paste this to notepad or something. STEP2. DROP TRIGGER on anything referencing _OBJECT_REMOVED. STEP 3. delete our duplicates. STEP 4. re-create all triggers.

sqlite> drop TRIGGER files_cleanup;
drop TRIGGER files_cleanup;
sqlite> DELETE FROM files WHERE _data = '' and media_type = 2;
DELETE FROM files WHERE _data = '' and media_type = 2;
sqlite> CREATE TRIGGER files_cleanup DELETE ON files BEGIN SELECT _OBJECT_REMOVED(old._id);END;

(optional) Add a trigger to nuke duplicates. So maybe I'd like sqlite3 to remove dupes for me anytime it re-indexes, say for a deletion? Some other events might be too expensive to have this triggering all the time, but I've tested it with a file delete and it works.

CREATE TRIGGER duplicates_cleanup DELETE ON files WHEN old.media_type = 2 BEGIN DELETE FROM files WHERE _data = '' and media_type = 2;END;

That's IT.

sqlite> .quit
.quit

Takes about 2-5 minutes to clean up manually, vs waiting 14-30 minutes to re-index a huge collection. This could also be automated if you were so inclined with a better trigger or an app.

UPDATE: 10/26/2012

One clarification: media_type = 2 is audio files -- mp3, ogg, wav and the like. If you have issues with other media you can simple use that media type, or leave the qualifier off for all media types.

I was 'trying out' this trigger, but now have gone for months without any duplicates. I think it works. Basically, you can skip doing any other sql work in the above guide -- just creating this trigger and adding a media audio file. Adding a media file should launch this trigger and clear out any duplicates (may have to reboot once).

sqlite> create trigger duplicates_cleanup AFTER UPDATE OF _data ON files BEGIN DELETE FROM files WHERE _data = '' and media_type = 2;END;
create trigger duplicates_cleanup AFTER UPDATE OF _data ON files BEGIN DELETE FROM files WHERE _data = '' and media_type = 2;END;