MySQL User Conference Presentations
From the MySQL User Conference in Santa Clara.
Konstantin Osipov + Peter Gulutzan’s presentation, “New Foreign Keys in 6.1″, is now available as an odp (Open Office presentation slides) file here. Nobody stomped out in a rage. One attendee said it’s a great development, he’s been waiting for years to see multi-storage-engine foreign keys. Another asked whether the feature would be in Drizzle, and Peter didn’t have the answer at the time, but later he asked a Drizzle worker and heard it’s improbable.
Peter heard a sideshow presenter suggest that the MySQL / DB2 storage engine was a surprise. The person didn’t allow time for questions or comments, but here’s a belated note that we sent out the first press release two years ago, here.
Alexander Barkov + Peter Gulutzan’s BoF, about character sets, got only a few non-Sun attendees. The main concerns were not about new features, but about how to upgrade from old versions, or how to convert to UTF8. Peter made a foolish statement about stripping accents, for which apologies are in order, but it’s okay because nobody believed him at the time.
After the conference Peter, as if to remove all doubts re his foolishness, decided to walk around the adjacent communities of Milpitas and San Jose. Subsequent checking on Google Maps reveals that was a 25-mile stroll in the sun, so if you saw him later red-faced and limping, here’s hoping you didn’t get the wrong impression.
The rest of this blog post is the sheet that Konstantin Osipov and Peter Gulutzan used for the MySQL Camp demo, “MySQL 6.1 Test Drive”. Somebody was recording it; if you find the recording on the web, please send a comment. It looks better if you see the results, so come watch next time they demo.
Peter Gulutzan and Konstantin Osipov
Sun Microsystems
"This is a live nothing-behind-the-curtain demonstration
of features that actually work, in pre-alpha or alpha or
beta versions of MySQL including MySQL 6.0, 6.1, and future."
After each demonstration, there is a chance to ask
one or two questions. We will type more statements on request.
======================================================
Feature = Foreign keys / all storage engines (WL#148)
Demonstrator = Konstantin
Version = mysql-6.1-fk
Sample Statement text =
quit
/data1/mysql-6.1-fk.sh
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1
(s1 INT UNIQUE NOT NULL)
ENGINE=FALCON; /* Falcon + foreign key! */
CREATE TABLE t2
(s1 INT DEFAULT 5 REFERENCES t1(s1)/* Legal Syntax at last! */
ON UPDATE SET DEFAULT)
ENGINE=FALCON;
CREATE TRIGGER t2_au AFTER UPDATE ON t2
FOR EACH ROW SET @a='trigger happened';
INSERT INTO t1 VALUES (1),(5);
INSERT INTO t2 VALUES (2); /* Won't work! */
INSERT INTO t2 VALUES (1);
UPDATE t1 SET s1 = 2 WHERE s1 = 1; /* Cascades! */
SELECT *,@a FROM t2; /* Shows cascading worked! */
=======================================================
Feature = Supplementary Unicode Characters (WL#1213)
Demonstrator = Peter
Version = 6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
DROP TABLE IF EXISTS t;
SET NAMES UTF8;
CREATE TABLE t
(s1 VARCHAR(5) CHARACTER SET UTF32); /* new character set! */
INSERT INTO t VALUES (0x000204d7); /* rare cjk! */
INSERT INTO t VALUES (0x000100cc); /* linear b! */
INSERT INTO t VALUES (0x00010400); /* deseret! */
SELECT * FROM t; /* Visible with this font! */
=======================================================
Feature = Retrievable OUT parameters
Demonstrator = Konstantin
Version = 6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
DROP PROCEDURE IF EXISTS p1;
DELIMITER //
CREATE PROCEDURE p1(OUT v1 INT, OUT v2 CHAR(32))
BEGIN
SELECT 'procedure p1' as result;
SET v1= 10;
SET v2= 100;
END//
DELIMITER ;
PREPARE s1 FROM 'CALL p1(?, ?)';
EXECUTE s1 USING @u1, @u2;
SELECT @u1, @u2;
=======================================================
Feature = BACKUP and RESTORE
Demonstrator = Peter
Version = 6.0
Sample Statement text =
quit
rm /data1/mysql-6.0/var/1
/data1/mysql-6.0.sh
DROP DATABASE d;
CREATE DATABASE d;
CREATE TABLE d.t (s1 INT);
INSERT INTO d.t VALUES (5);
BACKUP DATABASE d TO '1'; /* new statement! */
DROP DATABASE d;
SELECT * FROM d.t; /* now it's gone! */
RESTORE FROM '1'; /* new statement! */
SELECT * FROM d.t; /* now it's back! */
=======================================================
Feature = EXECUTE IMMEDIATE (WL#2793)
Demonstrator = Konstantin
Version = 6.0-2793
Sample Statement text =
quit
/data1/mysql-6.0-2793.sh
DROP PROCEDURE IF EXISTS p1;
SET @@max_sp_recursion_depth = 5000;
EXECUTE IMMEDIATE 'SELECT 5'; /* literal! */
DELIMITER //
CREATE PROCEDURE p1 (name CHAR(255))
BEGIN /* hara-kiri! */
EXECUTE IMMEDIATE CONCAT('DROP PROCEDURE ', name);
CALL p1(name);
END//
DELIMITER ;
CALL p1('p1');
=======================================================
Feature = Performance Schema
Demonstrator = Peter
Version = mysql-6.0-perf
Sample Statement text =
quit
/data1/mysql-6.0-perf.sh
SELECT * FROM performance_schema.EVENTS_WAITS_CURRENT\G
SELECT event_name,count_star,sum_timer_wait,max_timer_wait
FROM performance_schema.EVENTS_WAITS_SUMMARY_BY_EVENT_NAME
WHERE COUNT_STAR > 0 ORDER BY sum_timer_wait DESC LIMIT 5;
=======================================================
Feature = SIGNAL + RESIGNAL (WL#2110, WL#2265)
Demonstrator = Konstantin
Version = mysql-6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
DROP PROCEDURE IF EXISTS p;
DROP TABLE IF EXISTS t;
SIGNAL SQLSTATE '77777' SET MESSAGE_TEXT='Oops'; /* customized error! */
DROP TABLE t; /* default error! */
DELIMITER //
CREATE PROCEDURE p ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
RESIGNAL SQLSTATE '22222'; /* replaces default! */
DROP TABLE t; /* activates handler! */
END//
DELIMITER ;
CALL p();
=======================================================
Feature = SHA2 (Bug#13174)
Demonstrator = Peter
Version = mysql-6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
SELECT sha2('a',256); /* Contributor = Bill Karwin */
=======================================================
Feature = Parameters view (WL#4301)
Demonstrator = Konstantin
Version = mysql-6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
DROP FUNCTION IF EXISTS f;
CREATE FUNCTION f (parameter1 SMALLINT)
RETURNS SMALLINT
RETURN 5;
SELECT SPECIFIC_NAME AS RNAME, ORDINAL_POSITION AS POS,
PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE,
ROUTINE_TYPE
FROM INFORMATION_SCHEMA.PARAMETERS; /* 2 rows! */
=======================================================
Feature = Partition by string (WL#3352)
Demonstrator = Peter
Version = mysql-5.1-wl3352
Sample Statement text =
quit
/data1/mysql-5.1-wl3352.sh
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR, b CHAR, c CHAR)
PARTITION BY RANGE COLUMN_LIST(a,b,c)
(PARTITION p0 VALUES LESS THAN (COLUMN_LIST('a','b','c')));
=======================================================
Feature = Less VARBINARY (WL#2649)
Demonstrator = Konstantin
Version = mysql-6.0-wl2649
Sample Statement text =
quit
/data1/mysql-6.0-wl2649.sh
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 AS SELECT CONCAT(1);
SHOW CREATE TABLE t1;
=======================================================
Feature = WEIGHT_STRING function (WL#3716)
Demonstrator = Peter
Version = 6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
DROP TABLE IF EXISTS t;
SET NAMES UTF8;
CREATE TABLE t (s1 VARCHAR(5) CHARACTER SET utf8);
INSERT INTO t VALUES ('a'),('Ã');
SELECT s1,WEIGHT_STRING(s1) FROM t;
DROP TABLE IF EXISTS t;
CREATE TABLE t (s1 VARCHAR(5) CHARACTER SET utf8
COLLATE utf8_unicode_ci);
INSERT INTO t VALUES ('a'),('Ã');
SELECT s1,HEX(WEIGHT_STRING(s1)) FROM t;
