Problem with caching in MySQL Database for some files

Locked
magiceye
User
Posts:4
Joined:Thu Jul 23, 2015 6:34 pm
Are you a spambot?:no
Location:Germany
Contact:
Problem with caching in MySQL Database for some files

Post by magiceye » Thu Jul 23, 2015 6:43 pm

I am running getID3 on a site where I want to show length and resolution of video files. Since it sometimes takes quite a wile to scan the files, I recently tried to turn on the caching feature of getID3, which works great most of the time. I get the entries for all the cached files in my MySQL database, and subsequent loads of the site are really quick. So I know (or seem to know) that caching works, and the necessary data gets written to the database.

The issue I am seeing, though, is that for some files the cached data (although something is written to the MySQL database) turns up empty. I can see the file infos I use (length and resolution) on the initial scan (when it is not read from the MySQL database), but on a reload of the page the info does not show up. Interestingly this is consistent for these files. When I delete the entry for this file from the MySQL database, on the next reload of the page the info will show up again (scanned freshly from the file), but when reloading again to access the data from the cache the info come up empty again. So it seems like the cache extension is not repopulating the file infos correctly again.

I tried it with the stable 1.9.9 release as well as with the latest 1.x beta version. Both show the same issues. Any idea what could be wrong there? Is there a way to generate some error log for the caching module?

James Heinrich
getID3() v1 developer
Posts:1477
Joined:Fri May 04, 2001 4:00 pm
Are you a spambot?:no
Location:Northern Ontario, Canada
Contact:

Re: Problem with caching in MySQL Database for some files

Post by James Heinrich » Thu Jul 23, 2015 11:27 pm

Sorry, no quick ideas come to mind. You'll have to try and trace through the hard way. Let me know what you find.

magiceye
User
Posts:4
Joined:Thu Jul 23, 2015 6:34 pm
Are you a spambot?:no
Location:Germany
Contact:

Re: Problem with caching in MySQL Database for some files

Post by magiceye » Tue Aug 11, 2015 10:55 am

Hi James,

thanks a lot for your answer. I´ve tried to understand why this is happening to some of my files, but so far have not come to a solution. Unfortunately I am not really good in PHP and MySQL debugging.
I´ve set up a 2nd test system, where the exact same problem happens with the same files. Initial scan of the files returns the expected results, and info get written to the MySQL database, but trying to read the cached info returns nothing.

Would you be willing to have a look at one of my files to see if you can reproduce the issue? I could send you a link if you would be willing to have a look.

Thanks a lot,

Florian Peters

James Heinrich
getID3() v1 developer
Posts:1477
Joined:Fri May 04, 2001 4:00 pm
Are you a spambot?:no
Location:Northern Ontario, Canada
Contact:

Re: Problem with caching in MySQL Database for some files

Post by James Heinrich » Tue Aug 11, 2015 2:05 pm

I can try and take a look, send me the link.

magiceye
User
Posts:4
Joined:Thu Jul 23, 2015 6:34 pm
Are you a spambot?:no
Location:Germany
Contact:

Re: Problem with caching in MySQL Database for some files

Post by magiceye » Tue Aug 11, 2015 6:31 pm

Okay, thanks a lot. I sent you a PM with a link to a test file.

James Heinrich
getID3() v1 developer
Posts:1477
Joined:Fri May 04, 2001 4:00 pm
Are you a spambot?:no
Location:Northern Ontario, Canada
Contact:

Re: Problem with caching in MySQL Database for some files

Post by James Heinrich » Tue Aug 11, 2015 7:02 pm

What character set is your cache database using? The cache extension does not specify so it may get created as latin1 or UTF8 (or possibly something else). Not that it should matter, since the data stored is all base64-encoded so character set should be a non-issue.

The default database structure gives a filename field length of 255 characters, is there any danger that your filenames are exceeding this length (not just the base filename, but the full filesystem filename with directory paths)? If so feel free to adjust the database structure to give more space to the filename field.

Can you please check in your cache database and see what filenames have entries and what don't? Since your example had two files of similar name where one works and one doesn't perhaps there is something going on where the data for both are trying to insert over each other so one returns the cache and the other doesn't.

From your description and example, the first time through the file doesn't exist in the cache, is analyzed fully and the correct data is returned. The second time the cache is checked and appears to match something, so the cached data is returned but what is returned is either blank or corrupt. Actually looking at that again now, perhaps this is the source of the problem: the cache table stores the analysis results in a TEXT field, which is 2[sup]16[/sup] bytes, as in 64kB maximum. It's very conceivable for a file to contain more metadata than that in the getID3 analysis.

I would suggest:
  • truncate your cache table
  • change the `value` field from TEXT to LONGTEXT

    Code: Select all

    ALTER TABLE  `getid3_cache` CHANGE `value` `value` LONGTEXT CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL ;
  • optional: change filename field length to something longer if you think you might run into a filename length limit (up to 1000 chars if supported on your system)
Then try again and let me know if it works any better.


edit: related changes put in https://github.com/JamesHeinrich/getID3 ... 351db52618

magiceye
User
Posts:4
Joined:Thu Jul 23, 2015 6:34 pm
Are you a spambot?:no
Location:Germany
Contact:

Re: Problem with caching in MySQL Database for some files

Post by magiceye » Wed Aug 12, 2015 4:19 pm

Seems like changing the 'value' field to LONGTEXT seems to have fixed my issues. I am going to do some more tests, but so far my problematic examples seem to work after deleting their entries from the MySQL database and re-caching them as LONGTEXT. So probably the metadata was more than the value field could store.

Thanks a lot for your great support!!!

James Heinrich
getID3() v1 developer
Posts:1477
Joined:Fri May 04, 2001 4:00 pm
Are you a spambot?:no
Location:Northern Ontario, Canada
Contact:

Re: Problem with caching in MySQL Database for some files

Post by James Heinrich » Wed Aug 12, 2015 4:23 pm

Good to hear, thanks for the bug report.

Locked