A Little Noise

1Oct/140

Ambiguous column names (not that kind)

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 ▼

Filed under: MySQL Gotchas No Comments
17Jul/130

Where’s my line?

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 ▼

Filed under: MySQL Gotchas No Comments
1Oct/122

Connection Conundrum

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?

9Nov/114

Quiz: A More Perfect UNION

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 ▼

Hint #2 ▼

Hint #3 ▼

Hint #4 ▼

Answer ▼

Filed under: MySQL Gotchas 4 Comments
9Mar/110

UNION and ORDER and LIMIT

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 ▼

Filed under: MySQL Gotchas No Comments
24Mar/102

LOAD DATA and recovery

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;

24Jul/092

Careful with those required files in Ruby

Getting Ruby to talk to MySQL was pretty easy in irb:

C:\>irb
irb(main):001:0> require 'rubygems'
=> true
irb(main):002:0> require 'mysql'
=> true
irb(main):003:0> db = Mysql.connect('localhost', 'root', 'password', 'test')
=> #<Mysql:0x2cc5270>
irb(main):004:0>

But then I tried the very same code from a file:

C:\>ruby mysql.rb
./mysql.rb:3: uninitialized constant Mysql (NameError)
        from C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
        from C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
        from mysql.rb:2

That require 'mysql' line looks in the current directory first ... and I'd used the name mysql.rb for the file I was coding ... so my code just included itself, and not the gem I wanted. Doh!

Filed under: MySQL Gotchas 2 Comments
10Oct/084

Another Parting Shot

Related to that last quiz. Here's the SHOW CREATE TABLE output - I've only cleaned up the whitespace.

CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
/*!50100 PARTITION BY RANGE (id) (
  PARTITION p0 VALUES LESS THAN (100) 
    ENGINE = MyISAM, 
  PARTITION p1 VALUES LESS THAN MAXVALUE 
    DATA DIRECTORY = '~/mysql/data/' 
    ENGINE = MyISAM
) */

Now, pay close attention to the rows inserted:

mysql> INSERT INTO t1 VALUES (100);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Ok, here's the tricky bit:

mysql> SELECT * FROM t1;
Empty set (0.00 sec)

No triggers, no BLACKHOLE, no Proxy, no events or other scheduled tasks, no other connections deleting rows, no temporary tables, and I have all the necessary privileges (including to the data directory shown, which exists and isn't full). It's all MyISAM, so surely no transactions. No errors or warnings have been reported.

When you figure it out, don't do it in your production environment. Bad things happen.

Filed under: MySQL Gotchas 4 Comments
1Oct/0812

A Parting Shot

mysql> INSERT IGNORE INTO trickyTable (id) VALUES (1);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM trickyTable;
Empty set (0.00 sec)

That's odd - what happened to the row?

I have the necessary privileges:

mysql> SHOW GRANTS;
+---------------------------------------------------+
| Grants for root@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' |
+---------------------------------------------------+
1 row in set (0.00 sec)

The table exists:

mysql> SHOW TABLES;
+------------------------+
| Tables_in_example      |
+------------------------+
| trickytable            |
+------------------------+
1 row in set (0.00 sec)

The field is there and adequately typed for the value:

mysql> DESCRIBE trickyTable;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)

Not using BLACKHOLE, and no transaction tricks:

mysql> SELECT engine FROM information_schema.tables WHERE table_name = 'trickyTable';
+--------+
| engine |
+--------+
| MyISAM |
+--------+
1 row in set (0.00 sec)

No triggers to make things tricky:

mysql> SELECT * FROM information_schema.triggers WHERE event_object_table = 'trickyTable';
Empty set (0.00 sec)

Just take my word for it that I'm not using Proxy, and this has nothing to do with replication.

Filed under: MySQL Gotchas 12 Comments
19Aug/0818

Two for the price of one

Haven't done any sneaky puzzles in a while. How would you accomplish this?

mysql> CREATE TABLE t1 (id int);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
Filed under: MySQL Gotchas 18 Comments