On 09/29/17 13:54, Phil Stracchino wrote: > On 09/29/17 02:14, Ralf Brinkmann wrote: >> Has someone a Mysql query for on an autochanger test to pickup files >> that crosses tape boundary? > > Hmmmm.... That's a good question. Let me think about that one for a > few minutes.
There isn't a practical way to do it in a single query, but you could probably write a stored procedure to do it. First we need to find out what Volumes were used for the Job and in which order: MySQL localhost> select distinct Media.MediaId, Media.VolumeName from Media join JobMedia on JobMedia.MediaId = Media.MediaId where JobMedia.JobId = 15014 order by Media.LastWritten; +---------+----------------+ | MediaId | VolumeName | +---------+----------------+ | 48 | LTO4-FULL-0013 | | 2426 | LTO4-FULL-0014 | | 51 | LTO4-FULL-0016 | +---------+----------------+ 3 rows in set (0.00 sec) Now we need to know what the first and last file indexes on each of those volumes was. We need three separate queries to do this, one for each volume, because we can't call min() and max() on values corresponding to multiple keys at once. If we do that, we'll get a single pair of results containing the first and last indexes in the entire job, which isn't what we want. We need to go one MediaId at a time: MySQL localhost> select MediaId, min(FirstIndex), max(LastIndex) from JobMedia where JobId = 15014 and MediaId = 48; +---------+-----------------+----------------+ | MediaId | min(FirstIndex) | max(LastIndex) | +---------+-----------------+----------------+ | 48 | 1 | 145851 | +---------+-----------------+----------------+ 1 row in set (0.00 sec) MySQL localhost> select MediaId, min(FirstIndex), max(LastIndex) from JobMedia where JobId = 15014 and MediaId = 2426; +---------+-----------------+----------------+ | MediaId | min(FirstIndex) | max(LastIndex) | +---------+-----------------+----------------+ | 2426 | 145851 | 146663 | +---------+-----------------+----------------+ 1 row in set (0.00 sec) MySQL localhost> select MediaId, min(FirstIndex), max(LastIndex) from JobMedia where JobId = 15014 and MediaId = 51; +---------+-----------------+----------------+ | MediaId | min(FirstIndex) | max(LastIndex) | +---------+-----------------+----------------+ | 51 | 146663 | 150149 | +---------+-----------------+----------------+ 1 row in set (0.00 sec) Uh-oh. Volume 48's last file has the same FileIndex as volume 2426's first file, and volume 2426's last file has the same FileIndex as volume 51's first file. Houston, we have a problem. Let's find out which files these are. To do that, we'll need to look them up by their FileIndexes and the JobId: MySQL localhost> select FileIndex, Path, Name from File join Filename on File.FilenameId = Filename.FilenameId join Path on File.PathId = Path.PathId where JobId = 15014 and (FileIndex = 145851 or FileIndex = 146663); +-----------+------------------------------------------------------------+-----------------------------------+ | FileIndex | Path | Name | +-----------+------------------------------------------------------------+-----------------------------------+ | 145851 | /netstore/scratch/NIN - The Slip - 96-24 High Res/ | 10 Demon Seed.wav | | 146663 | /netstore/video/TV/Criminal Minds/Criminal Minds Season 3/ | Criminal Minds S03E17 In Heat.avi | +-----------+------------------------------------------------------------+-----------------------------------+ 2 rows in set (0.31 sec) And there you go. Those two files span tape boundaries. -- Phil Stracchino Babylon Communications ph...@caerllewys.net p...@co.ordinate.org Landline: +1.603.293.8485 Mobile: +1.603.998.6958 ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
