Skip to content

Instantly share code, notes, and snippets.

Keybase proof

I hereby claim:

  • I am morgo on github.
  • I am morgo (https://keybase.io/morgo) on keybase.
  • I have a public key whose fingerprint is 97A4 2DBE 3564 9BE6 0B37 E35C C00C 8FB5 BF35 ACE4

To claim this, I am signing this object:

@morgo
morgo / keybase.md
Created September 23, 2015 15:26
keybase.md

Keybase proof

I hereby claim:

  • I am morgo on github.
  • I am morgo (https://keybase.io/morgo) on keybase.
  • I have a public key whose fingerprint is BEAF CAD6 3F51 DF9A 1915 0356 6B38 6951 7695 2538

To claim this, I am signing this object:

@morgo
morgo / SHOW PROCESSLIST in 5.7
Created September 12, 2015 01:17
SHOW PROCESSLIST in 5.7
# works in all versions
mysql> SHOW PROCESSLIST;
+----+----------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+------+---------+------+----------+------------------+
| 3 | msandbox | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+----------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
# using SYS (ships with MySQL 5.7)
@morgo
morgo / gist:779e0c9d8701a5141553
Created January 29, 2015 16:50
5.7 OR example
mysql [localhost] {msandbox} (test) > SELECT version();
+---------------+
| version() |
+---------------+
| 5.7.5-m15-log |
+---------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SHOW VARIABLES LIKE 'sql_mode';
+---------------+---------------------------------------------------------------+
@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;
@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: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: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: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)
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