Skip to content

Instantly share code, notes, and snippets.

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
morgo / gist:7880479
Last active December 30, 2015 20:28
Testing performance schema - recreating show_profiles.
/*
* 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@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
morgo / gist:5603720185f0c19de9ed
Created June 17, 2014 18:46
Zero date testcase
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.
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
morgo / gist:4f23b48fd3df09fb47f6
Created June 24, 2014 17:59
Wordpress wp_options VARCHAR increase.
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
morgo / gist:a766e110eeb0381ce5cb
Created September 1, 2014 19:16
Alter table behavior (5.5 and 5.6 with strict_trans_tables)
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
morgo / gist:918b05f8fbc110ba8959
Created September 5, 2014 00:44
Function on index
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
morgo / gist:d0a12ed4479343352b6d
Created October 15, 2014 15:52
MySQL Bug #72322 - EXPLAIN in DD release
mysql> EXPLAIN SELECT concat('Select ''', a.TABLE_SCHEMA,',',a.TABLE_NAME,',', DATA_LENGTH, ',', INDEX_LENGTH, ',', DATA_FREE, ','', count(*),', ''','', i on
@morgo
morgo / gist:46738ac0fea0f119224a
Created January 27, 2015 13:36
MySQL 5.7 index scan
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;