View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0011635 | MMW v4 | Synchronization | public | 2013-12-19 20:38 | 2017-03-31 15:39 |
| Reporter | lowlander | Assigned To | |||
| Priority | urgent | Severity | minor | Reproducibility | sometimes |
| Status | closed | Resolution | fixed | ||
| Product Version | 4.0.7 | ||||
| Target Version | 5.0.0 | Fixed in Version | 4.1.15 | ||
| Summary | 0011635: SQL error when working with Device Profile (overcomplex SQL issues) | ||||
| Description | Attempting to disable wifi sync for internal memory: 1) Open Device Profile, disable wifi sync, OK 2) Open Device Profile, wifi shows enabled, disable wifi sync, OK 3) On OK SQL error is shown (happened twice, AV logs just send) | ||||
| Steps To Reproduce | debug on FTP | ||||
| Tags | No tags attached. | ||||
| Attached Files | |||||
| Fixed in build | 1814 | ||||
| related to | 0011631 | resolved | Ludek | Options fails to close |
| related to | 0010922 | closed | lowlander | WiFi sync fails for very large sync lists |
| has duplicate | 0011502 | resolved | Ludek | Next track doesn't show loading... of Playlists in Classification tab |
| related to | 0013590 | closed | Ludek | WiFi sync incomplete may appear when an over-complex auto-playlist is on sync-list |
| related to | 0013070 | closed | Ludek | SQLite parser stack overflow for overcomplex SQL created by complex (nested) auto-playlists |
| related to | 0014157 | closed | Ludek | DROP TABLE _TempPlaylistContent_392_Th_2068": database schema has changed (6,6) |
|
|
Resolved as 'not fixable' as the fix is to simplify the auto-playlist as was also the issue in 0010922:0037706 |
|
|
a) Why is this SQL exectuted for opening a Device Profile? b) MM shouldn't fail on slow DB queries (be it complex SQL, large DB, slow PC, network DB). |
|
|
LowLander, look at the SQL please, it is still the same story as in case of 0010922:0037712 , your 'Radio' auto-playlist references 9 further auto-playlists, each of them references further playlists, e.g. 'Electronic' references 5 further auto-playlists, and e.g. Radio\Electronic\1970-1980\ references 'Mater Adult' auto-playlist + adds further criteria based on Genre, SongPath etc. , very very very complex resulting in the very very complex SQL above. Believe me that this auto-playlist halts database completelly for more than one minute, so if MM needs to call this SQL then it is not able to write anything to the DB for at least one minute. Please eliminate this auto-playlist, SQLite is not able to handle such a crazy SQLs. And believe me that this AP is source of many troubles you are observing, I am getting similar ELFs from you on regular basic and most of them are just consequence of the crazy AP / halted DB. The solution could be for MMW to not allow users to construct such an auto-playlists at all, but I don't want to indtroduce this limitation, because even complex SQL can work fast on smaller databases / fast computers powered by SSD. |
|
|
I think it's unreasonable to ask users to delete complex Playlists (there is no way to achieve the Playlist). Instead MediaMonkey should be able to handle the DB lock, even if it is a minute+. I personally don't mind if I'd have to wait for the transaction to complete. I do mind MM crashing or not being able to get the Playlist I need. |
|
|
As I understand, the problem is that it's really not fixable using sqlite. We would need to migrate to a full-fledged db in order to support queries of this nature. Something that could be considered for a future version (e.g. MM5) but probably not MM4. Ludek--is that what you're saying--that sqlite is simply not capable of handling this? |
|
|
This is not about SQL engine used. The issue is that we allow users to create such a over-complex auto playlists that will always cause issues despite the SQL engine used. Note that currently users have no limits in complexity. As I wrote solution would be for MMW to not allow users to construct such an auto-playlists at all, but I don't want to indtroduce this limitation, because even complex SQL can work fast on smaller databases / fast computers powered by SSD. In addition we are not able to determine this on auto-playlist creation time, because DB can grow after creation of the AP. I really don't see a solution ATM (except elimination of the auto-playlist) |
|
|
I still don't see how this isn't a simple timeout issue. Whatever causes problems when complex SQL is executed shouldn't be allowed to execute till complex SQL is executing. For example if a user opens the Device Profile and MMW needs to execute the SQL queries (I guess to calculate sync size) it should not allow users to close the Device Profile as that seems to cause problems. This situation can be solved 2 ways: 1) Specific actions can't be done with some info why not. This could be a status bar showing SQL execution progress or a warning when doing actions that they can't be done till process (SQL execution) is completed. 2) Freeze interface with message overlay showing SQL Execution progress. The latter may be more affective, but could annoy users (I don't like when I can't use Player controls). Alternatively there may be a way to terminate SQL queries, especially as the problematic SQL queries are those that only read from the DB, when the results of the query are no longer required. It seems that this may have been implemented in the Classification tab in the Properties dialog as it can be closed while Playlists are still being loaded. |
|
|
The problem with such a overcomplex SQLs like this is that it cannot be terminated, because it halts in the database engine for minutes during the parsing/execution of the query, i.e. before a single row is mined. In MM5 it won't freeze UI, because there won't be any SQL performed on UI thread, but having database inaccessible for minutes is really problem despite the fact from which thread it is accessed, generally the only solution is really to simplify the SQL/auto-playlist plus add complexity limitation as noted in 0011635:0039038 |
|
|
I still don't think limiting users is the way to go (although if many of the AutoPlaylist enhancements requested by users get added it would reduce the need of really complex auto-playlists). As for the Classifications tab, as suggested before the Playlists should be removed from there (threading it has improved things a lot, but not sufficiently) and be available in its own tab. Additionally an option could be added to only show static Playlists (as AutoPlaylists are what is slowing things down). For the Device Profile, this generally isn't a problem personally as I use wifi sync. An option to not calculate space (I presume this is the root problem) would be a solution, otherwise an interface halt (user can't do anything while calculating) would prevent SQL errors in case database is locked, unavailable. |
|
|
Fixed in 5.0.0.2037 I am going to test it on LowLander's database and if all goes well we could merge it into 4.1.15 |
|
|
Based on my tests the performance is pretty same using the temp tables solution. i.e. loading the "Car" playlist from LowLander's database takes more than 10 seconds on my PC (despite the solution used), but DB is not halted for 10 seconds using the temp tables solution (because it doesn't have to parse the crazy SQL above) Because the fix looks quite low risk I merged it into 4.1.15.1814 |
|
|
Verified 1814 |
