Crashes sometimes when trying to Execute SQL or Browse Data

[thundercatsNZ]

Details for the issue

What did you do?

Run an SQL query or use the filters in the Browse Data tab.

What did you expect to see?

For the app not to crash, or at least an error message shown.

What did you see instead?

Immediate crash with no error message given!

Useful extra information

DB4S v3.12.2 [built for x86_64-little_endian-lp64] on macOS 10.16 (darwin/21.3.0) [x86_64] using SQLCipher Version 4.4.3 community (based on SQLite 3.34.1) and Qt 5.15.2

[chrisjlocke]

So, some obvious questions first. Can you supply the query that could potentially cause the crash. Be useful to see if its a mega complex one with joins all over the place, or if its just a select from and a where. Does it do it on every database, or just one particular one? Have you run a check on the database to ensure its valid? If you click on 'Tools' and 'Integrity check', it should return OK. If it only does it on one database, can you copy the file, blat out the data so its not confidential data in it, and send it to me? If I can replicate it here, then that can go a long way to identifying what the problem is and how to solve it With just a 'it crashes', it doesn't help pinpoint the issue - its a bit like pointing vaguely to your chest to a doctor saying, "It sort of hurts around here".

You're using the SQLCipher version - is it an encrypted database? Have you tried the normal DB4S version? I see you're on a Mac. Have you tried the nightly version at all? While not vastly different from the 3.12.2 release, it is different enough that these weird crashing errors can disappear. As we've not had any other reports of 'it crashes', I don't want to be rude, but it sounds like its something local to your machine. How much free disk space do you have, and how much memory is in your Mac? Again, just some basic questions to help us understand what could be going on.

[thundercatsNZ]

Hi Chris,

Thanks for your reply. The db I am querying is the library db for Plex. It's not a complicated query - a couple of joins and some WHERE statements. The fact it is crashing on the Browse Data screen (when I add filters) makes me think that it's an underlying problem rather than just the specifc query I'm running.

Given that this db is produced and controlled by the Plex server application I don't have a lot of control over the structure etc, but running the integrity check produces a couple of messges about rows missing from the indexes. Assume that's normal for this db. There's no security or encryption on the db as far as I'm aware, and I haven't made any changes regarding SQLCipher, just using the default version from brew.

Not rude at all to suggest the problem is with my setup, when I saw that no one else had posted any similar issues I suspected as much... I apologise for my issue lacking specifics and should have asked what information someone like yourself would need to further diagnose the problem. Is there any sort of log file I can produce to send you?

Let me see if I can sanitise the db and send you a copy. Here's an example query that sometimes will crash the app:

select track.id, track.guid, track.title track_title, track.original_title track_artist, album.title album , artist.title album_artist from metadata_items track inner join (select id, parent_id,guid,title from metadata_items where metadata_type = 9) album on album.id = track.parent_id inner join (select id,guid,title from metadata_items where metadata_type = 8) artist on artist.id = album.parent_id where metadata_type = 10

Machine is an aging Macbook Pro, 16gb RAM, ~200GB free disk.

Happy to provide any other info you need, just let me know. Thanks!

[thundercatsNZ]

Hi Chris,

Using the nightly instead of the brew version seems to be much more stable. I will keep using this version and let you know if the crashes return, but looks like the issue was resolved.

I did some further testing and the crash was only occuring when displaying a resultset - i.e. the crash never occurs with 0 rows. So maybe a bug with QT that has since been resolved?

Thanks

[chrisjlocke]

My alarm tingly warning thing is tingling at the "rows missing from the indexes". There is no valid reason that a database would be corrupt. This is similar to a file system - running chkdsk should come back clean - you don't expect errors. Aah sorry - chkdsk is a Windows thing. I assume there is something similar in Mac. The nightly version might handle this anomoly better, of course.

I apologise for my issue lacking specifics

No problem. When posting an issue, its hard to know what information is needed and what isn't. We tried to add a 'template' but not many people fill that out either.

I've not seen the Plex database, but that does seem a twisty query, but certainly not one that should cause SQLite a problem. While the issue is solved, having a copy of the database would still be interesting - if only to rule out if it was due to the corruption, or different SQLite versions. The release and nightlies have different SQLite versions, albeit only a couple of revisions different. Glad the nightly has resolved the issue. Thanks for reporting it, and for your prompt replies. :)

[thundercatsNZ]

Ok cool, thanks again for your help. Can send a copy of the db to you directly, is there an easy way to do that?

[chrisjlocke]

The easiest (well, OK, not the easiest) is to use dbhub.io - you can share a sqlite database and make it public. Requires signing up, creating a certificate (if you want to use DB4S directly), etc. Worth it in the long run. Or you can go olde school and email it to db4s.issue_2988 [at] chrisjlocke.co.uk ... that'll work too! ;) I think you can .zip it up and just attach it here too (attach files by dragging and dropping). I don't think .db or .sqlite files are supported though, thus the zipping necessity. Might be wrong....

[ggoliev]

Hello, can it be useful? I have a very simple query (SELECT...FROM...WHERE...) and get this application crash. After the DBBrowser restart, the same query works fine (without a crash)

Thread 0 Crashed:: Dispatch queue: com.apple.main-thread 0 libsystem_kernel.dylib 0x7ff809b3e00e __pthread_kill + 10 1 libsystem_pthread.dylib 0x7ff809b741ff pthread_kill + 263 2 libsystem_c.dylib 0x7ff809abfd24 abort + 123 3 QtCore 0x10408e769 0x104076000 + 100201 4 QtCore 0x10408feb4 QMessageLogger::fatal(char const, ...) const + 202 5 DB Browser for SQLite 0x101b4f59c 0x10188d000 + 2893212 6 DB Browser for SQLite 0x101bc37fb 0x10188d000 + 3368955 7 DB Browser for SQLite 0x101b32d84 0x10188d000 + 2776452 8 DB Browser for SQLite 0x101b2d542 0x10188d000 + 2753858 9 DB Browser for SQLite 0x101914833 0x10188d000 + 555059 10 DB Browser for SQLite 0x101915863 0x10188d000 + 559203 11 QtCore 0x1042a1db5 0x104076000 + 2276789 12 DB Browser for SQLite 0x101a6abe6 SqliteTableModel::finishedFetch(int, int) + 70 13 DB Browser for SQLite 0x101992beb SqliteTableModel::handleFinishedFetch(int, unsigned int, unsigned int) + 283 14 DB Browser for SQLite 0x101992c4d SqliteTableModel::handleRowCountComplete(int, int) + 29 15 QtCore 0x1042999bf QObject::event(QEvent) + 943 16 QtWidgets 0x102868fca QApplicationPrivate::notify_helper(QObject, QEvent) + 266 17 QtWidgets 0x10286a3f1 QApplication::notify(QObject, QEvent) + 497 18 QtCore 0x10426e854 QCoreApplication::notifyInternal2(QObject, QEvent) + 212 19 QtCore 0x10426fb99 QCoreApplicationPrivate::sendPostedEvents(QObject, int, QThreadData) + 809 20 libqcocoa.dylib 0x103095279 0x10305b000 + 238201 21 libqcocoa.dylib 0x1030959e8 0x10305b000 + 240104 22 CoreFoundation 0x7ff809c3aaeb CFRUNLOOP_IS_CALLING_OUT_TO_A_SOURCE0_PERFORM_FUNCTION + 17 23 CoreFoundation 0x7ff809c3aa53 CFRunLoopDoSource0 + 180 24 CoreFoundation 0x7ff809c3a7cd CFRunLoopDoSources0 + 242 25 CoreFoundation 0x7ff809c391e8 __CFRunLoopRun + 892 26 CoreFoundation 0x7ff809c387ac CFRunLoopRunSpecific + 562 27 HIToolbox 0x7ff8128bfce6 RunCurrentEventLoopInMode + 292 28 HIToolbox 0x7ff8128bfa4a ReceiveNextEventCommon + 594 29 HIToolbox 0x7ff8128bf7e5 _BlockUntilNextEventMatchingListInModeWithFilter + 70 30 AppKit 0x7ff80c65f53d _DPSNextEvent + 927 31 AppKit 0x7ff80c65dbfa -[NSApplication(NSEvent) _nextEventMatchingEventMask:untilDate:inMode:dequeue:] + 1394 32 AppKit 0x7ff80c6502a9 -[NSApplication run] + 586 33 libqcocoa.dylib 0x10309464f 0x10305b000 + 235087 34 QtCore 0x10426a8ef QEventLoop::exec(QFlags) + 431 35 QtCore 0x10426ee62 QCoreApplication::exec() + 130 36 DB Browser for SQLite 0x101893def main + 319 37 dyld 0x10bbd551e start + 462

[chrisjlocke]

What version of DB4S are you using? If you're using a really old version (eg, v0.009 beta alpha sub version 7) then you might need to upgrade, as that version was written while upside down, so it has some bugs. v0.009 beta alpha sub version 8 was written while someone drove a bus to work, so that had even newer bugs. I can't tell from your log what version you're using, but can guess from '33 libqcocoa.dylib 0x10309464f 0x10305b000 + 235087' that you might be using version 235087? Thats a cool version.
OK, I have no idea. This is why we rely on people telling us. If you click 'Help' and 'About', there is a useful screen showing the version number. The text can actually be selected and copied too, so you don't even have to memorise it. Simply copy and paste. Not pasty as that's a pie. Don't pasty here. I'm guessing by the helpful log that you're not on Windows either, as Windows doesn't do helpful logs. It just reports 'Unknown error 0 - completed successfully' which isn't much good. Unless you're Bill Gates of course. I'm going to use my wacky wild powers and guess its a fruit derivative ... possibly less citrus based and more pre-cider variety ... a small Granny Smith or Gala? It's an Apple, isn't it.
If I mention 'nightlies' would that mean anything? Does this happen on every database you throw at it, or just this one? Have you verified to check if this database is OK? Is it clean and valid, or is it full of holes, missing indexes and tables with odd names like 'Barry' (not that Barry is odd, but he has an uncomfortable number of ducks in his garden). Is the database top secret and someone would encase you in concrete if you shared it, or can you splat out any sensitive data and share the database? A database is like a beautifully crafted cat (but without the fur-balls) and when we get errors like this, its useful to stand in the middle of the room and stroke the cat. If you can share the cat, we can experience the same pain and delve into the glory that is the cat. You can either use dbhub.io or email - but don't email us a real cat. Obviously. To everyone else that got notified of this email that isn't @ggoliev - I apologise. Twice.

[marcardar]

I've also been experiencing (3.12.2) crashes (MacOS 12.3.1, Macbook M1 Pro) for simple queries in a non-deterministic way. Perhaps happens about 1 in 10 queries on average. The app crashes, I restart the app and then the query works again. I'm often using a newly created database for each query. It happens definitely with encrypted (sqlcipher) and (IIRC) non-encrypted too.

[NateRobinson]

I got the same crash with you @marcardar

[gcstang]

I've also been experiencing (3.12.2) crashes (MacOS 12.3.1, Macbook M1 Pro) for simple queries in a non-deterministic way. Perhaps happens about 1 in 10 queries on average. The app crashes, I restart the app and then the query works again. I'm often using a newly created database for each query. It happens definitely with encrypted (sqlcipher) and (IIRC) non-encrypted too.

Same, originally I used the x86 and noticed this issue updated to the Arm version for my M1 and still the same; gave up and went with command line.

After reading this thread I ran the Integrity Check and it's good. Selects happen randomly same as you describe.

[lucydodo]

@marcardar @NateRobinson @gcstang I apologize for the inconvenience. Whenever you have a moment, could you please test if the issue is resolved in the latest nightly build?

As of today, here are the links to the latest nightly builds.

• DB4S for Apple Silicon Processors
• DB4S for Intel Processors
• DB4S with SQLCipher for Apple Silicon Processors
• DB4S with SQLCipher for Intel Processors

[gcstang]

@marcardar @NateRobinson @gcstang I apologize for the inconvenience. Whenever you have a moment, could you please test if the issue is resolved in the latest nightly build?

As of today, here are the links to the latest nightly builds.

• DB4S for Apple Silicon Processors
• DB4S for Intel Processors
• DB4S with SQLCipher for Apple Silicon Processors
• DB4S with SQLCipher for Intel Processors

@lucydodo so far so good, will keep using this for now, thank you for quick turn around.