Skip to content

Instantly share code, notes, and snippets.

View 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 Sep 12, 2015
SHOW PROCESSLIST in 5.7
View 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)
View gist:779e0c9d8701a5141553
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';
+---------------+---------------------------------------------------------------+
View gist:46738ac0fea0f119224a
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 Oct 15, 2014
MySQL Bug #72322 - EXPLAIN in DD release
View gist:d0a12ed4479343352b6d
mysql> EXPLAIN SELECT concat('Select ''', a.TABLE_SCHEMA,',',a.TABLE_NAME,',', DATA_LENGTH, ',', INDEX_LENGTH, ',', DATA_FREE, ','', count(*),', ''','', i on
View gist:918b05f8fbc110ba8959
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 Sep 1, 2014
Alter table behavior (5.5 and 5.6 with strict_trans_tables)
View gist:a766e110eeb0381ce5cb
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 Jun 24, 2014
Wordpress wp_options VARCHAR increase.
View gist:4f23b48fd3df09fb47f6
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)
View gist:a62f98a3098beee46a95
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
View gist:5603720185f0c19de9ed
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.
You can’t perform that action at this time.