Category: Technical

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!

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.

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.

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)