MySQL: Kill sleeping connections

Platform: MySQL 5.x

Most of the time it would be handy to have a native MySQL script which would allow one to kill the sleeping connections which are in sleep state for more than 180 sec..  On the other hand DBA's can use  "wait_timeout" etc parameters to control this..

[code]

 # -- Make sure you are logged as MySQL 'root' user or any user who have got super privileges 



DELIMITER $$
DROP PROCEDURE IF EXISTS `uKillSleepingSessions`$$


CREATE PROCEDURE `uKillSleepingSessions`()
COMMENT 'This routne is used to kill idle sessions'
READS SQL DATA


BEGIN


DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE uID bigint(4);

DECLARE my_cur CURSOR FOR
SELECT ID
  FROM information_schema.PROCESSLIST PL
WHERE PL.COMMAND='Sleep' AND PL.TIME > 180;


DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;


OPEN my_cur;
select FOUND_ROWS() into num_rows;

the_loop: LOOP


FETCH my_cur
INTO uID;


IF no_more_rows THEN
    CLOSE my_cur;
    LEAVE the_loop;
END IF;


SET @tmp_sql= CONCAT("KILL ",uID);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;


DEALLOCATE PREPARE s1;
SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;


END$$
DELIMITER ;



# Usage from mysql prompt/gui client
# Once called , it would cleanup idle connections which are sleeping for more than 180 sec
call uKillSleepingSessions();

[/code]

Cannot create Windows service for MySQL.Error:0

Platform: Windows


People often checks MySQL's download section and when they notice that there is new version of MySQL released they just can't wait for a second & starts downloading it so that they can install it on their windows box for testing purpose. Ok fine, download is completed its time to install it. Couple of successful "NEXT" and In the last step they encounter a error message such as

"Cannot create Windows service for MySQL.Error:0"

We are so intelligent ppl that we don't want to take any help from our colleague..
And what we do is we just delete the directory in which new installation has been done & starts the installation
wizard one-more time in the hope that this time we gonna install it.

Again the result is same "Cannot create Windows service for MySQL.Error:0"

So why are we getting error that too at the last step? one common thought is "there must be something wrong
or some sort of bug in the new release"

How to resolve this error?
The primary reason for this error is that you haven't properly uninstalled the 
previously installed MySQL server & in the installation wizard  last step was
 to create  the service with the name "MySQL" and which has the same name as that 
 of the existing service (default is "MySQL").

Solution for this error is to first stop the service if it is running and then remove 
the service.

C:\>NET STOP MYSQL
The MySQL
service is stopping.
The MySQL service was stopped successfully.

Once the service is stopped, it can be removed with the SC utility:
C:\>SC DELETE MYSQL

[SC]
DeleteService SUCCESS

With the previous service stopped and removed, you can now run the MySQL Configuration Wizard again to configure and start the new MySQL service.

MySQL: Copy tables (Only structure and NO DATA) using stored procedure

Last week some one asked how to Copy the tables (Only structure and NO DATA..also no other DB objects) from one schema to another on EE. This can be easily done from command line but user wanted to do this thru stored procedure.
http://www.experts-exchange.com/Database/MySQL/Q_25024073.html
So I came with a very small MySQL procedure which was doing as needed by the user. I'm not sure whether this is the best way to do this but "There is always room for improvement."
 
DELIMITER $$

DROP PROCEDURE IF EXISTS `CopySchema`$$
CREATE PROCEDURE `CopySchema`(sourceSchema VARCHAR(64),targetSchema VARCHAR(64))


BEGIN

DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE my_table VARCHAR(64);

DECLARE my_cur CURSOR FOR
SELECT TABLE_NAME AS myTable
 FROM information_schema.TABLES
WHERE information_schema.TABLES.TABLE_SCHEMA=sourceSchema AND TABLE_TYPE = 'BASE TABLE';


DECLARE CONTINUE HANDLER FOR NOT FOUND


SET no_more_rows = TRUE;
SET @tmp_sql= CONCAT("CREATE DATABASE IF NOT EXISTS ",targetSchema);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;


OPEN my_cur;
select FOUND_ROWS() into num_rows;

the_loop: LOOP
    FETCH my_cur
      INTO my_table;

     IF no_more_rows THEN
         CLOSE my_cur;
         LEAVE the_loop;
     END IF;


     SET @tmp_sql= CONCAT("CREATE TABLE IF NOT EXISTS ",targetSchema,'.',my_table,' LIKE ',sourceSchema,'.',my_table);
      PREPARE s1 FROM @tmp_sql;
      EXECUTE s1;
     DEALLOCATE PREPARE s1;

     SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;

SELECT CONCAT('Summary: ', loop_cntr, ' tables copied from schema "',sourceSchema,'" to "',targetSchema,'"') AS "Schema copying";


END$$
DELIMITER ;

Usage:

call CopySchema('dba','dba_dummy');