MySQL cache Confusion

Locked
barryjarvis
User
Posts:16
Joined:Tue May 07, 2013 6:21 pm
Are you a spambot?:no
MySQL cache Confusion

Post by barryjarvis » Mon Jan 06, 2014 9:40 pm

I'm looking to cache the ID3 info of some MP3 files to a MySQL database. I spotted the cache.mysql extension and thought i may be able to use this.
So looking in the demo file i created a small simple test file for just a single track.

first i created a script that just grabs the ID3 tags and echo's them out to the browser.
Then following the mysql cache demo code i attempted to cache to the DB.

My script seems to work as it has created a new table 'getid3_cache' in my DB.
Looking at the fields within the table it doesn't appear that it actually inserts the id3 info in to the table??

Perhaps i'm thinking this does something else?

I'm looking to have 'artist', 'title', 'bitrate' etc all populated in to the DB for each track - is this possible using this extension?

Thanks.

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: MySQL cache Confusion

Post by James Heinrich » Tue Jan 07, 2014 7:43 am

The cache extensions cache the result of the entire file analysis, as a serialized array. When retrieving file info through the cache extension(s) the database is checked to see if that file has already been analyzed in which case the cached data is returned, otherwise the file is analyzed and the full results are stored in the database cache for next time.

You won't find any discrete fields in the database for "artist", "title", etc, but if you unserialize the data that is stored for the file in question you should find all the data you need in the usual getID3 structure.

This extension is designed to cache results where analyzing the source file is expensive (for example: the source file is on a remote HTTP/FTP host and the file data would need to be transferred across the network/internet, or the source file is on a removable medium like a DVD). You can use it as-is and obtain your values from the unserialized array, or if you want to have a database with artist/title/etc stored discretely it would probably be best to roll your own solution.

barryjarvis
User
Posts:16
Joined:Tue May 07, 2013 6:21 pm
Are you a spambot?:no

Re: MySQL cache Confusion

Post by barryjarvis » Tue Jan 07, 2014 10:33 am

Thanks for the reply.

Makes sense that it's stored in a serialized array, but it doesn't seem that i'm getting that in my db?

I've attached a screengrab of my db table after trying it out on 1x track.
Capture.PNG
Capture.PNG (17.55KiB)Viewed 22605 times
Does that look right??

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: MySQL cache Confusion

Post by James Heinrich » Tue Jan 07, 2014 12:27 pm

It is normal to have that as the first row in the table (see function clear_cache in extension.cache.mysql.php around line 125). This establishes which version of getID3 was used to generate the cached data so that all the cached data is known to be in a consistent format.

However you should also have additional rows, one for each file that has been analyzed through the cache extension. Those rows will have the filename in the filename field, valid values for filesize (bytes) and filetime (unix-timestamp) and the full output array serialized into the `value` column.

barryjarvis
User
Posts:16
Joined:Tue May 07, 2013 6:21 pm
Are you a spambot?:no

Re: MySQL cache Confusion

Post by barryjarvis » Tue Jan 07, 2014 1:03 pm

ok, thanks James - it's nice to have find a dev who offers decent support too!

So, based on the above screen shot it looks like the info being read isn't being inserted in to the database?

I'll scan try a few more files and see if anything gets inserted.

I know the file i scanned first has ID3 info that it's been reading because before i tried the MySQL integration i just did a simple getID3->analyze and then did print_r to display the array to screen.
I then went on to change the new GetID3 to the MySQL version.

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: MySQL cache Confusion

Post by James Heinrich » Tue Jan 07, 2014 1:12 pm

Sorry I can't really test anything for you right now (I'm on vacation with only a netbook and slow wifi, I don't have access to my usual getID3 test installation to try things out).

Whether or not there's any ID3 tag (or tags of any kind) in the file won't matter - every file analysis will include at the very least either file format and/or error messages. Entering nothing in the cache for a file would kind of defeat the purpose of the cache (to not re-analyze the file next time you see it).

Can you please post the snippet of code you're using to analyze the file through the caching extension?

You may also want to update your getID3 installation to the latest development version from Github:
https://github.com/JamesHeinrich/getID3 ... master.zip
It should be just as stable as v1.9.7 and with some bugs fixed. Offhand I don't recall what, if any, changes were made to the caching extensions since 1.9.7 but it can't hurt to try the current version.

barryjarvis
User
Posts:16
Joined:Tue May 07, 2013 6:21 pm
Are you a spambot?:no

Re: MySQL cache Confusion

Post by barryjarvis » Tue Jan 07, 2014 2:04 pm

Hi James,

Don't worry about testing etc, i know how it is sometimes (i'm currently in a similar situation).

My code is below, you'll see i have a few lines commented out from testing etc but it should be what you're looking for.
(obviously i have the real database info in the working version ;)

Code: Select all

<?php 
echo '<h1>Testing GetID3 to MySQL</h1>';

$filename = 'test-track.mp3';

// Include the GetID3 lib's
require_once('getid3/getid3.php');
require_once('getid3/extension.cache.mysql.php');

// Initialise GetID3
// $getID3 = new getID3;
$getID3 = new getID3_cached_mysql('host', 'db', 'user', 'pass', 'tablename');

// Fetch ID3 info
$ThisFileInfo = $getID3->analyze($filename);

/* 
getid3_lib::CopyTagsToComments($ThisFileInfo);

echo 'File name: '.$ThisFileInfo['filenamepath'].'<br>';
echo 'Artist: '.(!empty($ThisFileInfo['comments_html']['artist']) ? implode('<BR>', $ThisFileInfo['comments_html']['artist']) : '&nbsp;').'<br>';
echo 'Title: '.(!empty($ThisFileInfo['comments_html']['title']) ? implode('<BR>', $ThisFileInfo['comments_html']['title'])  : '&nbsp;').'<br>';
echo 'Bitrate: '.(!empty($ThisFileInfo['audio']['bitrate']) ? round($ThisFileInfo['audio']['bitrate'] / 1000).' kbps'   : '&nbsp;').'<br>';
echo 'Play time: '.(!empty($ThisFileInfo['playtime_string']) ? $ThisFileInfo['playtime_string'] : '&nbsp;').'<br>';
*/
 ?>

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: MySQL cache Confusion

Post by James Heinrich » Tue Jan 07, 2014 2:27 pm

At quick glance it would seem that you're doing it correctly. With the above code (using getID3_cached_mysql) does it print out file information as expected if you uncomment the echo statements?

I would suggest playing with extension.cache.mysql.php a little bit to see if any MySQL errors are generated after the insert statements (e.g. "echo mysql_error();" after the call to INSERT). Sorry I can't help more with the debugging. I'll be interested in what you find out.

barryjarvis
User
Posts:16
Joined:Tue May 07, 2013 6:21 pm
Are you a spambot?:no

Re: MySQL cache Confusion

Post by barryjarvis » Tue Jan 07, 2014 3:20 pm

hmm, i've not tried the echo statements using getID3_cached_mysql (only the standard getid3), so i'll try that later tonight.

At least i know my code is correct so i have a starting point.
Will try out some things tonight when i'm back at home and keep you updated.

barryjarvis
User
Posts:16
Joined:Tue May 07, 2013 6:21 pm
Are you a spambot?:no

Re: MySQL cache Confusion

Post by barryjarvis » Tue Jan 07, 2014 7:52 pm

James,

It seems i've made some progress.
I took your advice and moved over to the extension.cache.mysql.php from v2 - had to change a couple of bits to get it to match up with the code i was using (capitalisation of some letter has changed - 'getid3' instead of 'getID3' for example. Also had a MySQL error which was fixed by using 'ENGINE=MyISAM' instead of 'TYPE=MyISAM').

Anyway, after that i've now got it reading to the DB and inserting the info for each track.

I did run in to a stumbling block though. One track i'm trying to analyze doesn't seem to work - it doesn't insert anything at all in to the DB.
I've got a 'Print_r()' to show you the array it creates here (http://themixtapesite.com/getID3/).
I notice it seems to be full of junk(?) - almost looks corrupt?

Was wondering if you'd come across this before?

barryjarvis
User
Posts:16
Joined:Tue May 07, 2013 6:21 pm
Are you a spambot?:no

Re: MySQL cache Confusion

Post by barryjarvis » Tue Jan 07, 2014 8:06 pm

Sorry, too late to edit last post....

On looking at the array of that 'corrupt' file and also another one i tried, they both seem to start having issues when getting to the picture data... not sure if that's of any significance?

Is it possible to not read the entire ID3 info - i.e. selectively analyze data so we can ignore the picture data?

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: MySQL cache Confusion

Post by James Heinrich » Wed Jan 08, 2014 6:07 am

barryjarvis wrote:Also had a MySQL error which was fixed by using 'ENGINE=MyISAM' instead of 'TYPE=MyISAM').
That is one of the things that was fixed between 1.9.7 and the current development version, so I assume you're still using the old version. My suggestion would be to update to the current version to minimize the chance of running into already-fixed bugs (and makes debugging easier if we're looking at the same file versions).
barryjarvis wrote:Anyway, after that i've now got it reading to the DB and inserting the info for each track.
Was there an error in extension.cache.mysql.php that prevented it from working before, or what was the issue?
barryjarvis wrote:One track i'm trying to analyze doesn't seem to work - it doesn't insert anything at all in to the DB.
The image data from the tag looks normal at quick glance. Do you get any error reported from echo mysql_error(); after the insert call? If it's not inserting then that should give you an error message. One possibility is that you're getting a MySQL error (packet too large or something to that effect) when trying to insert a very large statement (e.g. large embedded image in the tag).

Side note: you'll get a better + safer output if you use this instead of a straight print_r

Code: Select all

//print_r($variable);
echo '<pre>'.htmlespecialchars(print_r($variable, true)).'</pre>';
If you have no interest in large embedded data like cover art, you can tell getID3 to not return it as part of the array by setting

Code: Select all

$getID3->option_save_attachments = false;
after instantiating it and before calling analyze.

barryjarvis
User
Posts:16
Joined:Tue May 07, 2013 6:21 pm
Are you a spambot?:no

Re: MySQL cache Confusion

Post by barryjarvis » Wed Jan 08, 2014 3:02 pm

James Heinrich wrote:
barryjarvis wrote:Also had a MySQL error which was fixed by using 'ENGINE=MyISAM' instead of 'TYPE=MyISAM').
That is one of the things that was fixed between 1.9.7 and the current development version, so I assume you're still using the old version. My suggestion would be to update to the current version to minimize the chance of running into already-fixed bugs (and makes debugging easier if we're looking at the same file versions).
I got the file from the sourceforge 'getid3-2.0.0b6-20101125.zip' which is linked to from the main site??
Line 169 of that file still uses 'TYPE=MyISAM'
James Heinrich wrote: If you have no interest in large embedded data like cover art, you can tell getID3 to not return it as part of the array by setting

Code: Select all

$getID3->option_save_attachments = false;
after instantiating it and before calling analyze.
Excellent, just what i was looking for... it all works now and the 2x tracks that weren't making it to the DB previous now are :)

Thank you so much for the support James!

Pernod
getID3() contributor
Posts:103
Joined:Sat Mar 21, 2009 5:30 pm
Are you a spambot?:no
Location:London, UK
Contact:

Re: MySQL cache Confusion

Post by Pernod » Wed Jan 08, 2014 3:36 pm

barryjarvis wrote:I got the file from the sourceforge 'getid3-2.0.0b6-20101125.zip' which is linked to from the main site??
Line 169 of that file still uses 'TYPE=MyISAM'
The version 2.0.0b6 is an old unsupported branch from 2010. You should download the lastest code using the github link that James posted yesterday.

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: MySQL cache Confusion

Post by James Heinrich » Wed Jan 08, 2014 3:55 pm

barryjarvis wrote:I got the file from the sourceforge 'getid3-2.0.0b6-20101125.zip' which is linked to from the main site??
The that says "THIS IS NOT IN ACTIVE DEVELOPMENT. NOT RECOMMENDED FOR PRODUCTION USE"? :)
Please don't use that version. I have removed that link from the main page.
The v2.0.x branch was an attempted fork from about 5 years ago and hasn't been updated in a very long time. Possibly in the future a new attempt will be made at a similar rewrite, but for the conceivable future please use the 1.x branch (currently v1.10.x development version available on Github).

There are known issues in v2.0.x that won't be fixed, so I strongly suggest you switch to v1.10:
https://github.com/JamesHeinrich/getID3 ... master.zip

barryjarvis
User
Posts:16
Joined:Tue May 07, 2013 6:21 pm
Are you a spambot?:no

Re: MySQL cache Confusion

Post by barryjarvis » Fri Jan 17, 2014 9:12 pm

James,

I've just got back to this and tried out a couple of things... And now i'm even more confused.

So, here's the situation.
If i use V2 of the extension.cache.mysql.php (the unsupported one) i can get it caching to the DB and reading from the DB. However, it seems to clear the DB and re-cache everything (can't seem to pin down a pattern to this yet).

If i revert to the latest V1 version of the file i can't get it to cache to the DB at all (it creates the DB table and inserts the first row with version number etc).
In this case it seems to be the following line causing the trouble:

Code: Select all

$getID3 = new getID3_cached_mysql('localhost', 'database', 'username', 'password', 'tablename');
(obviously i have my DB info in the real script).

I say thats the problem line because anything in my script after that line doesn't work. Even a simple

Code: Select all

echo 'test';
doesn't work after that line but works before it.

I tried adding a

Code: Select all

echo mysql_error();
after the insert call as you previously suggested but that didn't do anything so now sure if i put that in the right place?

Any suggestions?

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: MySQL cache Confusion

Post by James Heinrich » Sat Jan 18, 2014 7:15 am

I'm still unable to do any debugging of my own since I'm still on the road.

If the table is being created and the version-number row inserted then it would seem the getID3_cached_mysql() constructor is working, at least up to the last line where it calls parent:getID3();

First thing to check of course would be if your PHP (or Apache) error logs show anything of interest, they may or may not.

If you have PHP error_reporting turned off or limited, you may want to enable it for debugging, put this at the top of your code (or at least before the call to getID3_cached_mysql):

Code: Select all

error_reporting(E_ALL);
 ini_set("display_errors", 1);
If the above doesn't reveal any more clues, my suggestion would be to trace through the code execution by sprinkling the expected code path with this line

Code: Select all

echo basename(__FILE__).':'.__LINE__."<br>\n";
because if you say an echo-test line after calling new getID3_cached_mysql doesn't work then it seems to be dying during the object initialization somehow. With the above line you can see how far the execution is getting and in which file, and narrow down where the code stops running.

barryjarvis
User
Posts:16
Joined:Tue May 07, 2013 6:21 pm
Are you a spambot?:no

Re: MySQL cache Confusion

Post by barryjarvis » Sat Jan 18, 2014 9:26 am

ah, so it turned out i have error reporting off...

So, with that little code snippet you gave me i now get the following error:

Fatal error: Call to undefined method getID3::getID3() in /var/www/vhosts/i-16852366/getID3/getid3/extension.cache.mysql.php on line 114

Line 114 is:
parent::getID3();

I'm afraid my PHP doesn't go as far as objects and classes (it's doesn't go much further than 'echo' ;)) so i'm now lost :(

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: MySQL cache Confusion

Post by James Heinrich » Sat Jan 18, 2014 1:32 pm

Per the sample code at the top (lines 33-41) of extension.cache.mysql.php are you including (preferably via require_once) getid3.php as well as extension.cache.mysql.php? You are obviously including the latter, but from your error it sounds like you may have forgotten the former.

barryjarvis
User
Posts:16
Joined:Tue May 07, 2013 6:21 pm
Are you a spambot?:no

Re: MySQL cache Confusion

Post by barryjarvis » Sat Jan 18, 2014 2:45 pm

Yup, Still got that line in there...

For reference my code as it stands:

Code: Select all

<?php 
error_reporting(E_ALL);
ini_set("display_errors", 1);
 
echo '<h1>Testing GetID3 to MySQL</h1>';

$filename = 'test-track2.mp3';

// Include the GetID3 lib's
require_once('getid3/getid3.php');
require_once('getid3/extension.cache.mysql.php');

// Initialise GetID3
$getID3 = new getID3_cached_mysql('blah', 'blah', 'blah', 'blah');
$getID3->encoding = 'UTF-8';
$getID3->option_save_attachments = false;

// Fetch ID3 info
$ThisFileInfo = $getID3->analyze($filename);

// print_r($ThisFileInfo);

getID3_lib::CopyTagsToComments($ThisFileInfo);

echo 'File name: '.$ThisFileInfo['filenamepath'].'<br>';
echo 'Artist: '.(!empty($ThisFileInfo['comments_html']['artist']) ? implode('<BR>', $ThisFileInfo['comments_html']['artist']) : '&nbsp;').'<br>';
echo 'Title: '.(!empty($ThisFileInfo['comments_html']['title']) ? implode('<BR>', $ThisFileInfo['comments_html']['title'])  : '&nbsp;').'<br>';
echo 'Bitrate: '.(!empty($ThisFileInfo['audio']['bitrate']) ? round($ThisFileInfo['audio']['bitrate'] / 1000).' kbps'   : '&nbsp;').'<br>';
echo 'Play time: '.(!empty($ThisFileInfo['playtime_string']) ? $ThisFileInfo['playtime_string'] : '&nbsp;').'<br>';

 ?>

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: MySQL cache Confusion

Post by James Heinrich » Sat Jan 18, 2014 5:42 pm

Oh, of course, the getID3 constructor is now called __construct() rather than getID3() (getid3.php line 118).

Please change

Code: Select all

//parent::getID3();
parent::__construct();
and let me know if it works any better.

Locked