View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0012372 | MMA | Synchronization | public | 2014-11-20 14:34 | 2015-02-05 15:09 |
| Reporter | Ludek | Assigned To | |||
| Priority | urgent | Severity | minor | Reproducibility | always |
| Status | resolved | Resolution | fixed | ||
| Product Version | 1.1.0 | ||||
| Target Version | 1.1.0 | Fixed in Version | 1.1.0 | ||
| Summary | 0012372: Failed REINDEX on mmstore.db | ||||
| Description | Both me and user from ticket #RET-223-65190 can reproduce that after upgrade to MMA 1.0 beta there is a problem during USB sync, because if MMW tries to call REINDEX (on mmstore.db suplied by MMA) it fails with DB constraints. It seems that there are some index duplicities for some entries (for some reason). I tried the REINDEX call with various SQL editors (SQLite Studio, SQLite analzer, SQLite Spy, ...) and it always fails on mmstore.db In course of 0012347 I modified MMW USB sync workflow so that it does not consider failed REINDEX as critical issue and continues the sync (MMW 4.1.5 considers such a mmstore.db as corrupted and synces like if MMA was not installed) | ||||
| Additional Information | The user form ticket #RET-223-65190 uses MMA build 330 (MMA.info.app_version = 330) | ||||
| Tags | No tags attached. | ||||
| Fixed in build | 392 | ||||
|
|
It seems that it is related to actual content on device, if MMA scanned only half on the songs then the REINDEX succeeded, but once MMA scanned all my songs then the indexes are always corrupted and REINDEX fails. I can share my phone with you to reproduce the issue. Or I can upload my songs set somewhere and share. |
|
|
One idea of possible cause: I have the same song on both memories (Phone and Card) with same path, maybe it could have an impact? |
|
|
Related to http://www.mediamonkey.com/forum/viewtopic.php?f=21&t=78469&start=15 |
|
|
It seems that issue is not on MMA side. I have same issue (PRAGMA INTEGRITY_CHECK fails - missing several indexes) with my MMA database in SQLiteStudio, but in MMA everything seems to be ok. Tested my database from MMA: 1) integrity check is ok before publish to external storage 2) integrity check in SQLiteStudio fails on missing several indexes 3) clean internal MMA database to use copy of database from external storage 3) immediately on open database integrity check is ok (If I used Ludek's db, then integrity fails on few missing indexes, but Reindex; command fix it) Moreover in SQLiteStudio REINDEX; fails ( Error while executing query: indexed columns are not unique), but I) if I try REINDEX each table separately then command fails only on table genre. REINDEX genres - fails; suspicious rows are: _id genre type 11 Rock 0 32 rock 0 I chaged rock to rockX and then try again REINDEX genres; -> succeeds. I tried again just REINDEX; - it fails again. II) So I tried keep just one table "genre" in database with two rows: _id genre type 1 Pop 0 2 Gothic Rock 0 but REINDEX; fails (indexed columns are not unique), but there is only one index: CREATE UNIQUE INDEX genres_genre_idx ON genres ( type, genre ); on table CREATE TABLE genres ( _id INTEGER PRIMARY KEY, genre TEXT NOT NULL COLLATE UNICODE, type INTEGER, number_of_tracks INTEGER, number_of_albums INTEGER ); so I tried change type to "1" for "Gothic rock" then: _id genre type 1 Pop 0 2 Gothic Rock 1 and REINDEX; succeeds. Conclusion SQLiteStudio has several issue: I) creates indexes case insensitive for UNICODE II) REINDEX; fails on unique indexes from two columns Ludek's db: REINDEX; fails ( Error while executing query: indexed columns are not unique) REINDEX genres - fails;(rows "Rock",0 and "rock",0) It seems that issue isn't on MMA side, so I mark this issue as resolved for now. Fixed in build 1.1.0.383 |
|
|
Wouldn't be using CREATE INDEX instead of CREATE UNIQUE INDEX a solution/workaround? |
|
|
Because REINDEX; is important for sync with MMW, we have to remove all unique constraints combined from two or more columns. Uniqueness is checking programmatically, so it shouldn't cause another issues. |
|
|
I have found that issue causes UNICODE with combination of UNIQUE index. It means that Reindex; fails also on single column _data from table Artists, which is UNIQUE and UNICODE. SQLite has just three built-in collating functions: BINARY, NOCASE, and RTRIM. https://www.sqlite.org/datatype3.html#collation so it looks like that UNICODE in SQLiteStudio is incorrect. I postpone this issue to monday, it needs more testing with MMW. |
|
|
Tested on my database right after sync with Android media store. Problematic rows in genres id genre type 5 KLasic 0 6 Klasic 0 3 Rock 0 9 rock 0 14 Rock 1 SQLiteStudio 2.0.27 PRAGMA collation_list; NOCASE RTRIM BINARY if UNICODE is not found then it uses NOCASE collation. reindex; Error while executing query: indexed columns are not unique rename KLasic -> KLasicX rock -> rockX still Error while executing query: indexed columns are not unique In this configuration it fails on each unique/unicode column 2) select load_extension("SQLite3MMExt.dll", "sqlite3_extension_init") PRAGMA collation_list; NUMERICSTRING USERLOCALE UNICODE IUNICODE NOCASE RTRIM BINARY reindex after renaming: unsuccessful Tested on database with only one table genres and with only two records: 1 Pop 0 2 Gothic Rock 0 It fails due tue UNIQUE ( type, genre ) Conclusion: SQLiteStudio v2.0.27 doesn't work properly SQLiteSutdio 3.0.2 PRAGMA collation_list; UNICODE NOCASE RTRIM BINARY reindex; Error while executing SQL query on database 'mmstore': UNIQUE constraint failed: genres.type, genres.genre rename KLasic -> KLasicX rock -> rockX reindex after renaming: successful 2) select load_extension("SQLite3MMExt.dll", "sqlite3_extension_init") PRAGMA collation_list; NUMERICSTRING USERLOCALE IUNICODE UNICODE NOCASE RTRIM BINARY reindex after renaming: successful Conclusion: No difference between default configuration, which contains UNICODE collation, and loaded UNICODE from extension. It seems that issue is that MMA creates unique indexes in case sensitive unlike others. MMA allows Klasic/KLasic or rock/Rock as different genres. When I tested it in MMW I have renamed genre "Blues" to "blues". It keeps track in genre "Blues", only this track has genre "blues" in properties, so I guess that media in MMW has own column "genre" unlike MMA. Current MMA UNICODE comparator: private static final Collator mCollator = Collator.getInstance(); int result = mCollator.compare(string1, string2); http://developer.android.com/reference/java/text/Collator.html#compare(java.lang.String, java.lang.String) Should be comparation case insensitive? |
|
|
Assigned back to Marin to make MMA UNICODE collator case insensitive (to be compatible with MMW UNICODE collator). This is most probably root of all the troubles, after that the REINDEX shouldn't be needed at all. |
|
|
Is this proposed fix consistent with our long term direction as proposed in 0001412 ? |
|
|
Yes, the UNICODE collator is just about sorting and I think it can be case insensitive (like in MMW) Martin, is it a simple fix on MMA side to change the collator? If it is not an easy fix then I would suggest to remove the UNIQUE as temporary workaround, but for the future we should unify the MMA/MMW unicode collators. |
|
|
MMA is already fixed in build 1.1.0.392. |
|
|
Note that the very original reason for the REINDEX was this issue: 0011624 Martin changed the MMA's unicode collator so the REINDEX shouldn't be needed anymore at all, but I left it there for sure (and for the older MMA databases), there isn't any significant performance impact during sync. |
