A Little Noise

Category: MySQL

Things related to http://www.mysql.com

  • Workbench loses its crutches

    When MySQL Workbench was still in alpha and beta stages, some dark murmurs in the community suggested that the OSS (free) version was crippleware, part of an evil plot to make you pony up $99 if you wanted to get any real benefit. Closed source and profits and bears, oh my!

    The new GA release of Workbench gets some of those “crippled” features back. In particular, the OSS version now allows connections to a live server, so you can reverse and forward engineer without having to go through a dump file. I also have it on good authority that the OSS version will be able to print diagrams directly, instead of you having to export an image.

    If you see any merit in a visual diagram of your database, but you wrote off Workbench based on earlier experiences, give it another try now.

  • errno: 121 (Duplicate key) with CREATE TABLE

    Trying to create a table, and getting something like this?

    ERROR 1005 (HY000): Can't create table '<db>.<table>' (errno: 121)

    Discovered that perror 121 says this?

    MySQL error code 121: Duplicate key on write or update

    Really confused how you might get a duplicate key error while creating a table?

    If the table you’re trying to create includes a foreign key constraint, and you’ve provided your own name for that constraint, remember that it must be unique within the database. Run this query to see if that name is in use somewhere:

    SELECT 
  constraint_name,
  table_name
FROM 
  information_schema.table_constraints 
WHERE 
  constraint_type = 'FOREIGN KEY' 
  AND table_schema = DATABASE() 
ORDER BY 
  constraint_name;

    (If you’re still on 4.1 or earlier, 

    mysqldump --no-data yourDbName | grep CONSTRAINT

    to get a similar list)

    Thanks to [raymond] on Freenode.

  • Error 1307 creating stored procedure

    Trying to create a stored procedure, and getting a cryptic error like this?

    ERROR 1307 (HY000): Failed to CREATE PROCEDURE <procName>

    Try this simple diagnostic query first:

    SELECT 
  IF(
    COUNT(*) XOR (CAST(VERSION() AS decimal(2, 1)) >= 5.1), 
    'Wrong mysql.proc table version. Did you forget to run mysql_upgrade?',
    'The mysql.proc table looks ok. Keep hunting.'
  ) AS troubleshooting 
FROM 
  information_schema.columns 
WHERE 
  table_schema = 'mysql' 
  AND table_name = 'proc' 
  AND column_name LIKE 'body_utf8';

  • Misconceptions

    Others (including the manual) have said it, and most who bother to read MySQL related blogs know it anyway, but:

    * autoincrement does *not* have to be primary, or even unique. Just indexed.
    * int(1) and int(100) hold the same range of values. The difference is how many zeros are added if zerofill is turned on.

  • 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.

  • 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.

  • 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)

  • Proxy Projects

    When I first heard of MySQL Proxy, I thought, “That’s way cool.”

    Then I thought, “Neat toy, but I can’t think of anything I’d actually want it for. I can spell ‘SELECT’ without the client correcting me, and I’d just as soon use the built in logs. ”

    Now I’m starting to gather ideas that might be practical:
    (more…)

  • Complete Set

    Occasionally in #mysql the question pops up, “How can I get all the values from a table that satisfy all of multiple conditions?”

    Then we beat the questioner with a large stick, until they give an example of what they really mean.

    SELECT * FROM quizAnswers;
+-------------+----------+
| studentName | question |
+-------------+----------+
| seekwill    | A        |
| seekwill    | B        |
| seekwill    | C        |
| roxlu       | A        |
| fury        | B        |
| fury        | B        |
+-------------+----------+

    Find all the students who have answered both questions ‘A’ and ‘B’.
    (more…)

  • Views and Social Engineering

    CREATE TABLE secretData (
    secretValue int COMMENT 'If this goes over 5, WWIII will start'
);

CREATE SQL SECURITY DEFINER VIEW censoredData AS 
SELECT * FROM secretData WHERE secretValue < 5 WITH CHECK OPTION;

GRANT SELECT, INSERT ON test.censoredData TO 'evilFiend'@'%';

    <telephone> ring ring

    <sysadmin> "Hello?"

    <evilFiend> "I'd like to create an insertable view on some tables I already have rights to. I don't know just yet what I'll use for my select statement."

    <sysadmin> "Ok. I'll set it up so you can do what you'd like."

    CREATE SQL SECURITY INVOKER VIEW evilFiendsView AS SELECT 1;
GRANT SELECT, INSERT, ALTER ON test.evilFiendsView TO 'evilFiend'@'%';

    Evil Fiend

    evilFiend connects to the server, while twiddling the end of his handlebar mustache.

    ALTER VIEW evilFiendsView AS 
SELECT * FROM censoredData WITH LOCAL CHECK OPTION;

INSERT INTO evilFiendsView VALUES (42);

    Muhahaha!