morgo

Percona Server with XtraDB

Introduction

• 2010(2009?) InnoDB 1.1 announced, will first ship with MySQL 5.5.
• Historically InnoDB development lags while MySQL is trying to GA
• lots of things fixed in InnoDB since MySQL 5.0
• important note -- plugin version of InnoDB is not the default in 5.1

morgo

/* 
 * Procedure: show_profiles()
 *
 * Versions: 5.6.x
 *
 * Emulates previous behaviour of SHOW PROFILES feature which has been
 * officially deprecated from MySQL 5.7 onwards.
*/

morgo

morgo@rbook:~/sandboxes/msb_5_6_16$ cat /tmp/my.cnf 
[mysql]
user=root
password=testtttt
------------------------------------------------------------------------------------------------------------------------------------- 15:16:39
morgo@rbook:~/sandboxes/msb_5_6_16$ ~/Documents/mysql-installs/5.6.16/bin/mysql --defaults-extra-file=/tmp/my.cnf --defaults-file=/Users/morgo/sandboxes/msb_5_6_16/my.sandbox.cnf
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.6.16-log MySQL Community Server (GPL)

morgo

Example showing zero date:

mysql [localhost] {msandbox} (test) > CREATE TABLE a (a INT NOT NULL primary key auto_increment, b date);
Query OK, 0 rows affected (0.05 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO a VALUES (NULL, '2014-06-00');
Query OK, 1 row affected (0.01 sec)

# MySQL 5.7 proposal is to merge the SQL mode NO_ZERO_IN_DATE into the definition of STRICT_ALL_TABLES
# Which is on by default.

morgo

mysql [localhost] {msandbox} (test) > set sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > CREATE TABLE string_test (a int);
Query OK, 0 rows affected (0.12 sec)

mysql [localhost] {msandbox} (test) > insert into string_test VALUES ('hello');
ERROR 1366 (HY000): Incorrect integer value: 'hello' for column 'a' at row 1

morgo

mysql> CREATE TABLE `wp_options` (
    ->   `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   `option_name` varchar(64) NOT NULL DEFAULT '',
    ->   `option_value` longtext NOT NULL,
    ->   `autoload` varchar(20) NOT NULL DEFAULT 'yes',
    ->   PRIMARY KEY (`option_id`),
    ->   UNIQUE KEY `option_name` (`option_name`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=43361 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

morgo

MySQL 5.5 and below default behaviour (can be changed with sql_mode):
————————————

mysql [localhost] {msandbox} (test) > select * from test_table;
+-----+------------+
| id  | some_value |
+-----+------------+
| 100 |          2 |
| 101 |       NULL |
| 102 |          2 |

morgo

mysql [localhost] {msandbox} (test) > CREATE TABLE cities (id int not null primary key auto_increment, name VARCHAR(50) NOT NULL, INDEX(name));
Query OK, 0 rows affected (0.05 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO cities (name) VALUES ('Toronto'), ('Montreal'), ('Vancouver'), ('Calgary');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > update cities set name = AES_ENCRYPT(name, 'MYKEY');
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

morgo

mysql> EXPLAIN SELECT concat('Select ''', a.TABLE_SCHEMA,',',a.TABLE_NAME,',', DATA_LENGTH, ',', INDEX_LENGTH, ',', DATA_FREE, ','', count(*),', ''','',                                                                                                                                                                                                          i                                                                                                                                                                               on                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

morgo

mysql [localhost] {msandbox} (test) > Create table test (id int primary key auto_increment, name char(20));
Query OK, 0 rows affected (0.03 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO test VALUES (NULL, REPEAT('a', 20));
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO test VALUES (NULL, REPEAT('a', 20));
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;