A Little Noise

mysql’s --xml and some XSLT

Somebody asked in Freenode the other day how to get their data out of MySQL into a specific XML format.

Both mysqldump and the mysql client have --xml options. The output from the cli looks like this:

<resultset statement="SELECT here">
  <row>
    <field name="field1">value</field>
    <field name="field2">value</field>
  </row>
  <row>
    <field name="field1">value</field>
    <field name="field2">value</field>
  </row>
</resultset>

I wanted to change those <field name="fieldName"> into <fieldName> tags, and all it takes is a little XSLT, which I've uploaded here.

On Linux you can do something like this:

mysql -e "SELECT * FROM dbname.tablename" | xsltproc mysql.xsl -

Fine-tuning FULLTEXT: Adding word characters

Perhaps you want "." or ":" or "-" or something to be treated as a word character so that you can do a fulltext match against an IP or MAC address.

If you want to change the set of characters that are considered word characters, you can do so in two ways...
* Modify the MySQL source ... and recompile MySQL.
* Modify a character set file: This requires no recompilation.

Thus sayeth the Manual. What it doesn't say is just what to modify in that character set file. I had to ask MySQL developer Alexander Barkov for help to get it right.

In the user comments on that manual page, John Navratil has provided most of the answer, but there's an important step missing: adding a new collation.

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.

Drop all tables

Since there exists neither DROP TABLE * nor DROP TABLE WHERE name LIKE 'something', here are two ways to achieve the same:

For version 5.0+:

mysql -N -e "SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name), ';') FROM information_schema.tables WHERE table_schema = 'dbName'" | mysql dbName

Auto_increment Indexing

It's so common to set the auto_increment column to primary key, it has become common belief that it's required. Not true; an auto_increment column can use any index type. It doesn't even have to be unique:

CREATE TABLE testTable (
  id INT AUTO_INCREMENT,
  INDEX(id)
) ENGINE=MyISAM; -- Works for InnoDB too.

INSERT INTO testTable VALUES (1), (1), (NULL);

SELECT * FROM testTable;

+----+
| id |
+----+
|  1 |
|  1 |
|  2 |
+----+

Top N of a Group

No doubt everybody has solved it before, but ten seconds on Google didn't show any obvious results, so here we go: How to pick the top 5 records of each group. And we'll do it without subqueries, for the pre-4.1 folks.

Sum top N of a group

Trying to sum the top 5 entries in each group.

SELECT
    theId,
    SUM(theData) FROM (
        (SELECT
            @count := 0,
            @oldId := 0 AS theId,
            0 AS theData)
        UNION
        (SELECT
            @count := if(@oldId = id, @count+1, 0),
            @oldId := id,
            if(@count < 5, a, 0)
        FROM
            (SELECT * FROM theTable ORDER BY id, a DESC) AS theTable
        )
    ) AS theView
WHERE theId != 0
GROUP BY theId;

Table population:

INSERT INTO `thetable` VALUES
    (1,1), (1,2), (1,3), (1,4),
    (1,5), (1,6), (1,7), (2,1),
    (2,2), (2,3), (2,4), (2,5);