Time for another quiz to impress your friends, if you have that kind of friends.

The manual page in question

mysql --skip-enable-disable-column-names=0 -e "select 1"
mysql --skip-disable-enable-column-names=0 -e "select 1"
mysql --loose-disable-enable-skip-column-names=0 -e "select 1"

What's the result? An error about unknown options? A warning? Column headers enabled? Disabled?

Answer ▼

mysql --skip-enable-disable-column-names=0 -e "select 1"
+---+
| 1 |
+---+
| 1 |
+---+

mysql --skip-disable-enable-column-names=0 -e "select 1"
+---+
| 1 |
+---+

mysql --loose-disable-enable-skip-column-names=0 -e "select 1"
mysql: option '--skip-column-names' cannot take an argument

mysql -e "select * from test.t where d < '2013-07-17 17:00:00'"
+---------------------+
| d                   |
+---------------------+
| 2013-07-17 15:34:19 |
+---------------------+

mysqldump -t --compact test t --where="d < '2013-07-17 17:00:00'"
(no output)

Where's my line?

Hint ▼

--tz-utc

Define a user like this:

GRANT ALL ON *.* TO 'myuser'@'localhost' IDENTIFIED BY 'super$ecret';

Then try a PHP script like this:

<?php
mysqli_connect("localhost", "myuser", "super$ecret");
?>

What happens and why? How could you avoid it?

And for glory, what single line could you add that would prevent the error, without making any changes to the mysqli_connect line?

Today I saw a query like this:
SELECT d FROM t;

Performance was terrible. I ran out of patience after several minutes and killed the thread.

I changed the query to this:
(SELECT d FROM t) UNION ALL (SELECT NULL LIMIT 0);

It completed in under 3 seconds.

Can you explain how a no-op UNION so dramatically improved performance? (I couldn't have, without help from Jesper Krogh and James Day).

Hint #1 ▼

http://dev.mysql.com/doc/refman/5.5/en/cursor-restrictions.html

Hint #2 ▼

The field `d` is a varchar and is bigger than it needs to be.

Hint #3 ▼

http://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html

Hint #4 ▼

http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html

Answer ▼

The query is in a cursor. MySQL materializes …

3 ways your MySQL migration project can shake you up

Once a development or operations team gets over the hurdle of open-source, and start to feel comfortable with the way software works outside of the enterprise world, they will likely start to settle in and feel comfortable.  Best not to get too cushy though for there are more surprises hiding around the corner.  Here are a few of the biggest ones.

1. Replication Is Not Perfect

Yes, you've installed MySQL, setup a slave, and got it replicating properly.  You check the slave and it's 0 seconds behind the master.  What's more you monitor the error log file, and have a check in place to alert you if something happens there.  Job completed, good job!

Not so fast.  Unfortunately this is not the end of the story.  Many MySQL replication slaves are not consistent with their masters, but they drift apart silently.  …

From a discussion on Freenode (and don't feel bad if you don't get it; I had to ask Sinisa and Monty to explain it to me. Sinisa explained how it worked, and Monty found a bug.)--

USE test;
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
`a` int(11) DEFAULT NULL
);

INSERT INTO t VALUES (RAND() * 50);
INSERT INTO t SELECT RAND() * 50 FROM t;
Repeat that last INSERT a few times until you have about 1000 rows.

What would you expect from the following?

SELECT * FROM t WHERE false UNION SELECT * FROM t ORDER BY a LIMIT 19, 1;
(SELECT * FROM t WHERE false) UNION (SELECT * FROM t ORDER BY a LIMIT 19, 1);
(SELECT * FROM t WHERE false) UNION (SELECT * FROM t ORDER BY a) LIMIT 19, 1;

Show Answer ▼

Two things to remember:
- UNION is equivalent to UNION DISTINCT
- an …

A little two-part quiz. If you get the first one without peeking, you're worth your pay as a DBA. If you get the second one without peeking, you may tell your boss that some random guy on the Internet says you deserve a raise.

Start with a text file, 'test.txt', with these three lines:

1
1
2

Set up the test in MySQL:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id int primary key);
LOAD DATA INFILE 'test.txt' INTO TABLE t1;

This gives "ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'", which is expected.

What's in the table?

Show Answer ▼

It depends. If the engine is MyISAM, then you'll have one row: the first '1' from the file was inserted, everything else was skipped. If the engine is InnoDB, you'll have no rows, because the transaction would rollback. So either 1 row or 0 rows.

…

There are those that are very adamant about letting people know that using INFORMATION_SCHEMA can crash your database. For example, in making changes to many tables at once Baron writes:

“querying the INFORMATION_SCHEMA database on MySQL can completely lock a busy server for a long time. It can even crash it. It is very dangerous.”

Though Baron is telling the truth here, he left out one extremely important piece of information: you can actually figure out how dangerous your INFORMATION_SCHEMA query will be, ahead of time, using EXPLAIN.

In MySQL 5.1.21 and higher, not only were optimizations made to the INFORMATION_SCHEMA, but new values were added so that EXPLAIN had better visibility into what MySQL is actually doing. As per …

The other day we began to encounter weird and random errors on small and innocent queries that shouldn’t give any errors at all. It all lead to one of our most basic health checks failing for no apparent reason.

The first clue that popped into our minds was related to case-sensitivity, since the failing check was looking for the column names of the only table in the schema with UPPER CASE name. This symptom was especially weird since all of this was happening on MySQL setups running on Windows, and so we’re not sure if this was being caused by some internal code library, MySQL, or Windows itself.

Since that was the only clue we then had, it seemed obvious that we should start fiddling with the lower_case_table_names system variable. After a couple of restarts, this approach was leading us nowhere, so we finally gave up on it. …

The listing of Dynamic System Variables on the MySQL Reference Manual’s page is supposed to show those variables you can change on-the-fly. innodb_data_home_dir is listed there as a dynamic variable, but it is not one, and trying to change it on-the-fly doesn’t work:

mysql> set global innodb_data_home_dir="/data/mysql/data"; ERROR 1193 (HY000): Unknown system variable 'innodb_data_home_dir'

mysql> set session innodb_data_home_dir="/data/mysql/data"; ERROR [...]