Skip navigation

Monthly Archives: June 2014

Tasked with archiving large groups of tables, moving from one database to another. There are a few caveats involved depending on the architecture of the tables being moved, specifically bugs in MySQL 5.1, 5.5, 5.6 and maybe a few more versions documented here. this procedure handles the AUTO_INCREMENT issue, but will not alter tables containing various types of indexes. There are parameters for origin and destination databases in case you make a mistake and need to move one back. The archiveTable parameter is a boolean value 1/0, the rest is fairly straight forward.

DROP PROCEDURE IF EXISTS archive_tables;
-- set a temp delimiter for procedure construction
DELIMITER $$
-- create the procedure
CREATE PROCEDURE archive_tables(tablePrefix VARCHAR(32), originTable VARCHAR(32), destinationTable VARCHAR(32), archiveTable BOOLEAN)
BEGIN
    -- declare the variables
    DECLARE done INT DEFAULT FALSE;
    DECLARE tableName VARCHAR(50);
    DECLARE newTableName VARCHAR(70);
    DECLARE mv_query VARCHAR(1000);
    DECLARE alt_query VARCHAR(1000);
	
	-- create the cursor with the selected tables
    DECLARE cur1 CURSOR FOR SELECT TABLE_NAME 
	    FROM information_schema.TABLES 
		WHERE TABLE_NAME LIKE CONCAT(tablePrefix,'%') 
		AND TABLE_SCHEMA=originTable;
	-- this turns 'done' TRUE when there are no more tables
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

	-- begin
    OPEN cur1;
    read_loop: LOOP
	    -- push the current cursor element into the tableName var
        FETCH cur1 INTO tableName;
		-- if we are done, stop
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET newTableName = CONCAT(destinationTable,'.',tableName);

		-- create the rename query
        SET mv_query = CONCAT('RENAME TABLE ', tableName, ' TO ', newTableName);
        SET @mvQuery = mv_query;

		-- exec rename
        PREPARE stmt FROM @mvQuery;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- are we archiving the relocated tables?
		-- Note: This engine will not work with all tables, there is also a bug related to AI columns
		--       documented here: http://bugs.mysql.com/bug.php?id=37871 (Dev is running 5.1.73) The
		--       temp workaround is setting AUTO_INCREMENT to 0, but even this is not sufficient for
		--       all tables. I suggest not trying to use this feature even though the benefits are many.
		IF archiveTable THEN
		    
			-- create engine conversion query
		    SET alt_query = CONCAT('ALTER TABLE ', newTableName, ' AUTO_INCREMENT=0 ENGINE=archive');
			SET @altQuery = alt_query;
			
			-- set the engine attribute
			PREPARE stmt FROM @altQuery;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
		END IF;
    END LOOP;
END;

This is a simple bash script you can use to quickly convert a folder of music in AAC or FLAC into MP3 for use on a mobile device or small storage. To accommodate different input formats simple change the file suffix in the first line and the name variable regular expression from ‘mp4’ to ‘m4a’ or ‘flac’, etc. To use, copy/paste the script into a file within the folder to be converted and save it, then $chmod +x the_file_name and run the script ./the_file_name
The Script:

#!/bin/bash
for f in *.mp4
do
    name=`echo "$f" | sed -e "s/.mp4$//g"`
    ffmpeg -i "$f" -vn -ar 44100 -ac 2 -ab 320k -f mp3 "$name.mp3"
done

This script relies upon ffmpeg compiled with mp3 support (lamemp3).

I’ve recently gotten back into high end audio equipment with the purchase of a pair of Sennheiser HD 800 headphones. I’ve had a quality set of home shelf speakers for many years now but they aren’t something I can take into work or anywhere for that matter. For anyone who’s still with me on this, you know the difference a pair of high end speakers or headphones can make in the sound and clarity of music compared to even expensive ear buds or car stereo speakers. There really is no comparison.

The downside to this is that any of your music that has been compressed into MP3, M4A, etc. sounds thin, hollow and lifeless if recorded well to begin with. This has inspired me to begin to rebuild much of my music library that isn’t already in FLAC and along the way I thought I’d run a spectrum analysis of a song to document various levels of compression and the inherent data loss they incite.

Audio Sprectrum

The top graph represents the FLAC 44.1kHz sample, the middle represents 256kbs Mp3, and the bottom represents ~174kbps VBR Mp3. The frequency cutoff, banding and definition loss are obvious just looking at the graph and if you know the song well even more so upon listening. It is true you won’t be able to notice much of a difference withoput high quality audio components but given today’s high availability of cheap storage there is no good reason not to store your catalog in lossless or at least low-loss formats (>256kbs). Need to fit alot of music onto a smaller mobile listening device? Encode the music from the lossless sources when you need to, keeping the original as a backup (example).

The errors encountered during configure:
checking for sysvipc shared memory support... no
checking for mmap() using MAP_ANON shared memory support... no
checking for mmap() using /dev/zero shared memory support... no
checking for mmap() using shm_open() shared memory support... no
checking for mmap() using regular file shared memory support... no
checking "whether flock struct is linux ordered"... "no"
checking "whether flock struct is BSD ordered"... "no"
configure: error: Don't know how to define struct flock on this system, set --enable-opcache=no

I encountered these on a local system recently, a system where 5.5.12 had successfully compiled WITH opcache several weeks prior. One of the major performance advantages of the 5.5 generation is the opcache extension so disabling it was not an option. Long story short flock (file lock) structuring is passed to make by libtdl which is a part of the GNU libtool family and this info is required by the opcache extension to establish part of the memory mapping strategy during compilation. How these became lost or corrupted since the last install is a mystery, but I’m not Angela Lansbury and I need this to work because there is a particular LDAP related bugfix that may impact us so this is what needs to be done:

$sudo yum reinstall libtool libtool-ltdl libtool-ltdl-devel