Track metadata longer than 255 characters causes database corruption during sync operation

[seth]

In Engine Desktop, when attempting to sync a collection from the computer to an external drive, if tracks contain metadata that is longer than 255 characters, the operation will fail and the external drive will be reported as corrupt.

The files below are identical except that one has artist metadata that is 255 characters long and the other has artist metadata that is 256 characters long.

metadata-255-characters-long.mp3

metadata-256-characters-long.mp3

The 255 file will sync to external drive correctly while the 256 file will cause the corruption.

Testing was performed on an clean ExFAT formatted external drive on a system using macOS Sonoma 14.2.1. Multiple version of the Engine Desktop software exhibited the bug including 3.3, 3.4, and 4.0.

[PKtheDJ]

Um, under what normal circumstances would an artist name (or general track metadata) be 256 characters long?

[Parysatis]

Collaboration?

DJ SynthwaveXperience feat. The Psychedelic Cyber-Knights, Mystical Bass Warriors, and Galactic Sound Collective (Extended Cosmic Voyage Edition) a.k.a. Intergalactic Remix Crew in Collaboration with The Infinite Chillwave Masters & Special Guest Appearance by Parysatis of the Ashfall and Hyperdub Sonic Architects of the Future Sound (Live at Electric Dream Festival 2024)

[STU-C]

As per Copilot.

“The 255-character limit in databases typically applies to the Text data type, which is common in systems like Microsoft Access. If you need to store more than 255 characters, you can use a Memo or Long Text data type instead. This allows for much larger text storage.”

Do you really need all that info in the track name? or any of its other fields? where is it being populated? by the owner?

[Parysatis]

It is not the user who has to care about data types in data bases … the software should handle any kind of input and if it exceeds a limit, handle it with grace … a bug report seems appropriate here.

It is not a matter of overly long artist names or whether someone actually wants to use them, it is about how a piece of software handles weird and malformed input. Expect the unexpected.

[djliquidice]

Thank you for this.

I’ll look into this and see if I have any tracks in my collection that may have these traits.

Can you try this:

• close engine DJ
• rename your ~/Music/Engine Library directory
• Format a USB drive in exFAT
• Copy both files to that drive
• Open Engine DJ
• from the USB exfat drive, Import that each file individually.

Do you see a corruption message?

[STU-C]

But surely you can see how this ‘issue’ can be overlooked by anyone developing or testing software. This 255 character limit is common across most platforms that deal with music files, and the vast majority of people are not going to ever get near that.

If you have sourced the music from any of the standard vendors then there won’t be data exceeding that limit, and other DJ software behaves in the same way.

Yes the ‘corrupt DB’ message without any log files to tell people why it’s corrupt can and should be improved, but that is a different discussion.

[STU-C]

Associated Reddit thread for info.

https://www.reddit.com/r/DenonPrime/comments/1fkm1vc/track_metadata_longer_than_255_characters_causes/

Edit:

Some links to another forum with similar software, useful info in there too.

Remove file path limit of 255 characters for non-FAT32 systems

255 Character Limit - Solution?

[djliquidice]

@STU-C

Some links to another forum with similar software, useful info in there too.

https://forums.pioneerdj.com/hc/en-us/community/posts/12265468863001-Remove-file-path-limit-of-255-characters-for-non-FAT32-systems

This speaks to file names, which is not the same as the metadata and the database issue we’re experiencing.

[Parysatis]

@STU-C

how this ‘issue’ can be overlooked by anyone developing or testing software.

This is why it is a bug, not a feature …

… common … most … vast majority … standard …

This means that almost everything is nice. But:

All input is evil. This is an old wisdom …

[djliquidice]

@Parysatis

the software should handle any kind of input and if it exceeds a limit, handle it with grace

Agreed, though there aren’t limitations in the database itself.

here’s the track table. there are no defined limits for the number of characters in the text columns.

CREATE TABLE Track ( 
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  playOrder INTEGER, 
  length INTEGER, 
  bpm INTEGER, 
  year INTEGER, 
  path TEXT, 
  filename TEXT, 
  bitrate INTEGER, 
  bpmAnalyzed REAL, 
  albumArtId INTEGER, 
  fileBytes INTEGER, 
  title TEXT, 
  artist TEXT, 
  album TEXT, 
  genre TEXT, 
  comment TEXT, 
  label TEXT, 
  composer TEXT, 
  remixer TEXT, 
  key INTEGER, 
  rating INTEGER, 
  albumArt TEXT, 
  timeLastPlayed DATETIME, 
  isPlayed BOOLEAN, 
  fileType TEXT, 
  isAnalyzed BOOLEAN, 
  dateCreated DATETIME, 
  dateAdded DATETIME, 
  isAvailable BOOLEAN, 
  isMetadataOfPackedTrackChanged BOOLEAN, 
  isPerfomanceDataOfPackedTrackChanged BOOLEAN, 
  playedIndicator INTEGER, 
  isMetadataImported BOOLEAN, 
  pdbImportKey INTEGER, 
  streamingSource TEXT, 
  uri TEXT, 
  isBeatGridLocked BOOLEAN, 
  originDatabaseUuid TEXT, 
  originTrackId INTEGER, 
  trackData BLOB, 
  overviewWaveFormData BLOB, 
  beatData BLOB, 
  quickCues BLOB, 
  loops BLOB, 
  thirdPartySourceId INTEGER, 
  streamingFlags INTEGER, 
  explicitLyrics BOOLEAN, 
  activeOnLoadLoops INTEGER, 
  lastEditTime DATETIME, 
  CONSTRAINT C_originDatabaseUuid_originTrackId UNIQUE (originDatabaseUuid, originTrackId), 
  CONSTRAINT C_path UNIQUE (path), 
  FOREIGN KEY (albumArtId) REFERENCES AlbumArt (id) ON DELETE RESTRICT 
);

[Parysatis]

I don’t know what the underlying cause of the problem is. This could be anything …

@seth has encountered a situation where the database was corrupted as a result and obviously done quite some work to isolate the problem, and reported it here. Good work!

[djliquidice]

Flagging i can replicate this issue with Engine in a clean DB, but not with simple SQLite3 queries. Thank you @seth. I’ve shared this thread with others who are trying to get this resolved.

[PKtheDJ]

Reading up on it, for ID3v2 there’s a limit for the size of (total) tag data. It’s 256MB which seems huge, so I guess having a 256 character artist name shouldn’t be an issue.

For reference, the text of the entire bible takes up less than 5MB.

[STU-C]

Reading the Pioneer thread it seems FAT32 and the ability to support it is the underlying reasoning for the limit.

What the actual issue here is, the message being posted after corruption. In other software we have log files to examine to see what has and hasn’t imported and any errors.

It should warn that a character limit has been exceeded and then name the files causing it.

[seth]

Thank you for the reply. I have tried this process on a completely clean system. Uninstalled all copies of Engine Desktop and deleted the Engine music library folder. Testing with the two files, same error occurs.

For those who question why this would be a problem in the first place (like, who has tracks with artist names that long?), please understand that I am not out here advocating for arbitrarily large metadata. I was happy to go through the process of trimming down the long artist names so that they were more usable in practice (for reference, this occurs typically when there are a large number of co-contributors or producers who claim credit on a track).

The point of this bug report is to report an undocumented way that your library can become corrupt. It was challenging to diagnose because there is no indication of what went wrong. Only through through systematic testing was I able to figure out the actual cause. Trusting user input in a way that corrupts the underlying database is a basic software development error. This bug should be corrected as soon as possible, especially because of the esoteric nature of how it presents itself.

[Johan]

It’s indeed the lack of error handling that makes this a bug. If it would be cleanly signalled to the user it would just be expected behavior, for whatever relevant reason.

Nice find Seth!

[mufasa]

@djliquidice whats the update on your investigation? is this the cause of the corruption we have been dealing with?

how does one find files with 256 character names in a library?

Never mind…found a terminal script

find /Volumes/whatever -print | sort | perl -e ‘while(<>) { print if(length > 257) }’ > results.txt

shows me one result

//Volumes/T7_MUFASA/EXTRAS/Occasional Music_ Bathroom Breaks_MISCELLANOUS/Traditional vs. V.I.C. vs. Los Del Rio vs. Marcia Griffiths vs. Blackstreet vs. DJ Casper vs. Big Mucci vs. Cupid vs. Silento - Annoying Line Dances (Pointless Adam Dutch Mix) Clean - Fm 130-70.mp3

[mufasa]

@djliquidice

not the solution

I removed the file from my library

still corrupts on export

[PKtheDJ]

If I had that on my system I’d have called it Adam Dutch - Annoying Line Dances.

The BPM and key would be in the database, and (possibly) I’d list the artists, without the vs. inbetween, in a comment.

I’m assuming that none of my files have large amounts of tag data, as I’ve never had a corrupt Engine database.

After reading about the 256MB max tag data, I had a look for the largest file on my system. It’s a two hour long DJ mix. I can’t imagine the amount of tag info that’d be required to take up as much space as 2hrs of audio. That’s around 50 bibles worth of text!