A Little Noise

Aggregate JSON function in MySQL

There is not yet an equivalent to GROUP_CONCAT that produces a JSON array. (There is in MySQL 8, but that's not GA yet.) Until then, you can hack it together with string functions:

SELECT * FROM t;
+------+--------+
| id   | data   |
+------+--------+
|    1 | First  |
|    2 | Second |
+------+--------+

SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', id, 'value', data) SEPARATOR ', '), ']') AS j FROM t;
+-------------------------------------------------------------+
| j                                                           |
+-------------------------------------------------------------+
| [{"id": 1, "value": "First"}, {"id": 2, "value": "Second"}] |
+-------------------------------------------------------------+

Or you can use all JSON functions but hack the grouping:

SELECT j FROM (
       SELECT
         @c := @c + 1 AS c,
         @j := JSON_MERGE(@j, JSON_OBJECT('id', id, 'value', data)) AS j
       FROM t
       JOIN (SELECT @c := 0,  @j := JSON_ARRAY()) dt1
     ) dt2 ORDER BY c DESC LIMIT 1;
+-------------------------------------------------------------+
| j                                                           |
+-------------------------------------------------------------+
| [{"id": 1, "value": "First"}, {"id": 2, "value": "Second"}] |
+-------------------------------------------------------------+

mysqldump privileges required

"mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions."
- http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

mysql’s --xml and some XSLT (now with JSON!)

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 a</field>
    <field name="field2">value b</field>
  </row>
  <row>
    <field name="field1">value c</field>
    <field name="field2">value d</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.

An XSLT processor lets you do something like this:

mysql --xml -e "SELECT here" | xsltproc mysql.xsl -

The result will be something like:

<resultset statement="SELECT here">
  <row>
    <field1>value a</field1>
    <field2>value b</field2>
  </row>
  <row>
    <field1>value c</field1>
    <field2>value d</field2>
  </row>
</resultset>

If JSON is your thing, you can use xml2json to take that output a step farther:

mysql --xml -e "SELECT here" | xsltproc mysql.xsl - | xsltproc xml2json.xsl -

Result (whitespace added):

{"resultset" : 
  {
    "statement" : "SELECT here", 
    "row" : [
      {"field1" : "value a", "field2" : "value b"},
      {"field1" : "value c", "field2" : "value d"}
    ]
  }
}

That will suffice until the mysql client has a --json parameter.

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. (Edit: Now it does.) 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.