MySQL Support Blogs

please ignore

I needed to remove the spaces in filenames for many files but doing it by hand was a chore. Here is a small script that did the job for me:

#!/bin/bash
read -p "Are you sure you want to rename all files? (Y/N)" contflag
flag=$(echo $contflag| tr A-Z a-z)
if [ ! ${flag} == "y" ] ; then
 exit 1
fi
for f in *; do
     file=$(echo $f | tr A-Z a-z | tr '

There have been some request to have some reconnect possibilities in Connector/Python. I’m wondering now whether there should be some automatic reconnect on certain errors within the database driver.

My personal feeling is to have no automatic reconnect within Connector/Python and the programmer has to come up with retrying transactions herself.

For example:

	cnx.disconnect() # For testing..
	tries = 2
	while tries > 0:
		tries -= 1
		try:
			cursor.execute("INSERT INTO t1 (c1) VALUES ('ham')")
			cnx.commit()
		except mysql.connector.InterfaceError:
			if tries == 0:
				print "Failed inserting data after retrying"
				break
			else:
				print "Reconnecting.."
				cnx.reconnect()
		else:
			break

The above mimics how you would handle transactions and trying them reconnecting. I

CREATE FUNCTION MidPoint(ls LINESTRING)
RETURNS POINT
DETERMINISTIC
BEGIN
DECLARE len double;
DECLARE workLength double DEFAULT 0;
DECLARE workPoint int DEFAULT 1;
DECLARE point0 POINT;
DECLARE point1 POINT;
DECLARE distanceOver double;
DECLARE segmentLength double;

A third built-in extension point for MySQL Connector/J is the ExceptionInterceptor interface.  This is the third extension point covered in my recent JavaOne and Silicon Valley Code Camp presentations, and is very useful for diagnosing specific Exceptions encountered without modifying application-side code. This corresponds to slide #60 in my slide deck, and there are two Java files we’ll reference from my

Today I saw a query like this:
SELECT d FROM t;

Performance was terrible. I ran out of patience after several minutes and killed the thread.

I changed the query to this:
(SELECT d FROM t) UNION ALL (SELECT NULL LIMIT 0);

It completed in under 3 seconds.

Can you explain how a no-op UNION so dramatically improved performance? (I couldn't have, without help from Jesper Krogh and James Day).

Hint #1 ▼

http://dev.mysql.com/doc/refman/5.5/en/cursor-restrictions.html

Hint #2 ▼

Hint #3 ▼

http://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html

Today we registered MySQL Connector/Python with the Python Package Index (PyPI). It makes installing your favorite connector even easier (provided you first install setuptools or pip):

shell> easy_install mysql-connector
shell> pip install mysql-connector

Please report problems either using Launchpad or MySQL Bugs website.

In addition to reporting MySQL Connector/Python bugs on Launchpad, it is now also possible to enter them using http://bugs.mysql.com.

After more than 6 years doing MySQL Support for MySQL (http://www.mysql.com) AB, Sun Microsystems, and Oracle, it’s time for a change. Time to get back to development!

As of November 2011 I’ll be working full-time on MySQL Connector/Python and other goodies within the MySQL development team at Oracle. Before, this was more or less a pet project done after working hours. However, with the birth of our son Tomas more than a year ago, I’ve been slacking and family got priority.

The idea is to make MySQL Connector/Python the best choice for connecting to MySQL from within your Python code. We still got a long road ahead of us, but I’m confident that we are on the right track.

Continuing the review of MySQL Connector/J’s built-in extension points from my recent JavaOne and Silicon Valley Code Camp presentations, this blog posting will focus on the StatementInterceptor extension point.  As the name suggests, this allows you to hook into statement execution and alter behavior – without changing application-side code.  This corresponds to slide #59 in my slide deck, and there are two Java files we’ll reference:

• demo.connectorj.StatementInterceptorExample

This is the first of a handful of posts to augment the presentations I gave at Java One and Silicon Valley Code Camp earlier this month.  It seems I significantly overestimated how much content I could effectively deliver in the time allotted, and left a few of my major points untouched.  These blog posts will try to rectify that.

The first major area I failed to cover in depth was really “Extension Points”, starting from slide #56.  There are four major extension points in Connector/J:

• Lifecycle Interceptors
• Statement Interceptors
• Exception Interceptors
• Loadbalancing Strategies

We’ll look at the first

In response to http://forge.mysql.com/snippets/view.php?id=60 - adding support to count strings, not just characters

(
  character_length(<string1>)
  - character_length(REPLACE(<string1>, <searchString>, '')
) / character_length(<searchString>)

Finds the date for Easter (Western) given a year.
Sample usage: SELECT easter(YEAR(NOW()));

DELIMITER //
CREATE FUNCTION easter(inYear YEAR) RETURNS DATE DETERMINISTIC
BEGIN
    DECLARE a, b, c, d, e, k, m, n, p, q INT;

    DECLARE easter DATE;

    SET k = FLOOR(inYear / 100);
    SET a = MOD(inYear, 19);
    SET b = MOD(inYear, 4);
    SET c = MOD(inYear, 7);
    SET q = FLOOR(k / 4);
    SET p = FLOOR((13 + 8 * k) / 25);
    SET m = MOD((15-p+k-q), 30);
    SET d = MOD((19 * a + m), 30);
    SET n = MOD((4+k-q), 7);
    SET e = MOD((2*b+4*c+6*d+n), 7);

    SET easter = CASE
        WHEN d + e  10 THEN CONCAT_WS('-', inYear, '04-18')
        ELSE CONCAT_WS('-', inYear, '04', LPAD(d + e - 9, 2, 0))
    END;

    RETURN easter;
END
//
DELIMITER ;

A stored procedure to show all grants in the database.

USE mysql;

DELIMITER //
CREATE PROCEDURE showAllGrants() BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE theUser CHAR(16);
    DECLARE theHost CHAR(60);
    DECLARE cur1 CURSOR FOR SELECT user, host FROM mysql.user;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    OPEN cur1;

    REPEAT
        FETCH cur1 INTO theUser, theHost;
        IF NOT done THEN
            SET @sql := CONCAT('SHOW GRANTS FOR ', QUOTE(theUser), '@', QUOTE(theHost));
            PREPARE grantStatement FROM @sql;
            EXECUTE grantStatement;
            DROP PREPARE grantStatement;
        END IF;
    UNTIL done END REPEAT;

    CLOSE cur1;
END//
DELIMITER ;

CALL showAllGrants();

Convert an integer in the range 0 to 3999 to Roman numerals.

CREATE FUNCTION `toRoman`(inArabic int unsigned) RETURNS varchar(15) CHARSET latin1 DETERMINISTIC
BEGIN
    DECLARE numeral CHAR(7) DEFAULT 'IVXLCDM';

    DECLARE stringInUse CHAR(3);
    DECLARE position tinyint DEFAULT 1;
    DECLARE currentDigit tinyint;

    DECLARE returnValue VARCHAR(15) DEFAULT '';

    IF(inArabic > 3999) THEN RETURN 'overflow'; END IF;
    IF(inArabic = 0) THEN RETURN 'N'; END IF;

    WHILE position 

SHOW CREATE TRIGGER for 5.0 - 5.1.20

CREATE FUNCTION SHOW_CREATE_TRIGGER(in_trigger varchar(255))
RETURNS text READS SQL DATA
BEGIN
    DECLARE returnText text;
    SELECT CONCAT_WS(
        " ",
        "CREATE TRIGGER",
        TRIGGER_NAME,
        ACTION_TIMING,
        EVENT_MANIPULATION,
        "ON",
        EVENT_OBJECT_TABLE,
        "FOR EACH ROW",
        ACTION_STATEMENT) into returnText
    FROM information_schema.triggers;
RETURN returnText;
END

Translate from Roman Numerals back to decimal. Legal range is 0 to 3999 (that is, N to MMMCMXCIX) You can use IV or IIII for 4. You could even use a string of twenty I's for 20, but there's a string limit of 15, since that's the length of the biggest well-formed numeral below 3999.

CREATE FUNCTION fromRoman (inRoman varchar(15)) RETURNS integer DETERMINISTIC
BEGIN

    DECLARE numeral CHAR(7) DEFAULT 'IVXLCDM';

    DECLARE digit TINYINT;
    DECLARE previous INT DEFAULT 0;
    DECLARE current INT;
    DECLARE sum INT DEFAULT 0;

    SET inRoman = UPPER(inRoman);

    WHILE LENGTH(inRoman) > 0 DO
        SET digit := LOCATE(RIGHT(inRoman, 1), numeral) - 1;
        SET current := POW(10, FLOOR(digit / 2)) * POW(5, MOD(digit, 2));
        SET sum := sum + POW(-1, current  

Given a source string and a two-character range string, extracts all characters from the source that fall within the range. Sort of a simple version of the [A-Z] syntax from regular expressions.

Examples:
extractRange('123-ABC-456', 'AZ') returns 'ABC'
extractRange('123-ABC-456', '09') returns '123456'

CREATE FUNCTION extractRange(inString TEXT, inRange char(2))
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE returnString TEXT DEFAULT '';
DECLARE pointer INT DEFAULT 1;
WHILE pointer  

Two functions that work just like FIND_IN_SET, but support the % wildcard.
SELECT FIND_IN_WILD_SET('true', 'this,returns,%true%');
SELECT FIND_IN_WILD_SET('true', 'this,returns,%false%');
SELECT FIND_WILD_IN_SET('tr%e', 'this,returns,true');
SELECT FIND_WILD_IN_SET('tr%e', 'this,returns,false');

DROP FUNCTION IF EXISTS FIND_IN_WILD_SET;
DROP FUNCTION IF EXISTS FIND_WILD_IN_SET;

DELIMITER //

CREATE FUNCTION FIND_IN_WILD_SET(theString varchar(65535), theSet varchar(65535))
RETURNS boolean
DETERMINISTIC
BEGIN

DECLARE delimiterCount int;
DECLARE pos int DEFAULT 0;
DECLARE setElement varchar(65535);
DECLARE returnValue boolean DEFAULT FALSE;

SET delimiterCount := CHARACTER_LENGTH(theSet) - CHARACTER_LENGTH(REPLACE(theSet, ',', ''));

WHILE (pos 

A stored function to guess which line terminator ('\r' or '\r\n') to use with LOAD DATA INFILE

CREATE FUNCTION whichLineTerminator(fileName varchar(255))
RETURNS varchar(20)
NOT DETERMINISTIC
BEGIN
  DECLARE cr_count int;
  DECLARE lf_Count int;
  DECLARE f text;

  SET f := LOAD_FILE(fileName);

  IF f IS NULL THEN RETURN 'Cannot read file.'; END IF;

  SET cr_count := CHARACTER_LENGTH(f) - CHARACTER_LENGTH(REPLACE(f, '\r', ''));
  SET lf_count := CHARACTER_LENGTH(f) - CHARACTER_LENGTH(REPLACE(f, '\n', ''));

  IF cr_count >= lf_count
  THEN RETURN '\\r\\n';
  ELSE RETURN '\\n';
  END IF;
END