A Little Noise

8Sep/161

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"}] |
+-------------------------------------------------------------+
Filed under: MySQL FAQ 1 Comment
23Jul/133

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

Format Default Privileges Required
--add-drop-database Off  
--add-drop-table On in --opt  
--add-drop-trigger Off  
--add-locks On in --opt  
--all-databases Off SELECT, SHOW DATABASES ON *.*
--allow-keywords Off  
--apply-slave-statements Off  
--bind-address=ip_address Off  
--comments On  
--compact Off  
--compatible=name[,name,...] Off  
--complete-insert Off  
--create-options On in --opt  
--databases Off  
--debug[=debug_options] Off  
--debug-check Off  
--debug-info Off  
--default-auth=plugin Off  
--default-character-set=charset_name utf8/latin1  
--delayed-insert Off  
--delete-master-logs Off SUPER ON *.*
--disable-keys On in --opt  
--dump-date On in --comments  
--dump-slave[=value] Off SUPER or REPLICATION CLIENT ON *.*
--events Off EVENT
--extended-insert On in --opt  
--fields-enclosed-by=string '' in --tab  
--fields-escaped-by '\\' in --tab  
--fields-optionally-enclosed-by=string Off  
--fields-terminated-by=string '\t' in --tab  
--flush-logs Off RELOAD ON *.*
--flush-privileges Off  
--help Off  
--hex-blob Off  
--host localhost  
--ignore-table=db_name.tbl_name Off  
--include-master-host-port Off  
--insert-ignore Off  
--lines-terminated-by=string '\n' in --tab  
--lock-all-tables Off LOCK TABLES ON *.*
--lock-tables On in --opt LOCK TABLES
--log-error=file_name Off  
--login-path=name Off controlled at OS level
--master-data Off RELOAD ON *.*
SUPER or REPLICATION CLIENT ON *.*
--max_allowed_packet=value 24MB  
--net_buffer_length=value 1022KB  
--no-autocommit Off  
--no-create-db Off  
--no-create-info Off  
--no-data Off  
--no-set-names Off  
--no-tablespaces Off  
--opt On  
--order-by-primary Off  
--password[=password] Off  
--pipe Off  
--plugin-dir=path Off  
--port=port_num 3306  
--quick On in --opt  
--quote-names On  
--replace Off  
--result-file=file Off  
--routines Off SELECT ON mysql.proc
--set-charset On  
--set-gtid-purged=value Auto  
--single-transaction Off  
--skip-add-drop-table Off in --opt  
--skip-add-locks Off in --opt  
--skip-comments Off  
--skip-compact On  
--skip-disable-keys Off in --opt  
--skip-extended-insert Off in --opt  
--skip-opt Off  
--skip-quick Off in --opt  
--skip-quote-names Off  
--skip-set-charset Off  
--skip-triggers Off  
--skip-tz-utc Off  
--ssl-ca=file_name Off  
--ssl-capath=dir_name Off  
--ssl-cert=file_name Off  
--ssl-cipher=cipher_list Off  
--ssl-crl=file_name Off  
--ssl-crlpath=dir_name Off  
--ssl-key=file_name Off  
--ssl-verify-server-cert Off  
--tab=path Off  
--tables Off  
--triggers On TRIGGER since 5.1.23, SUPER ON *.* earlier
--tz-utc On  
--user=user_name system user on Linux, 'ODBC' on Windows  
--verbose Off  
--version Off  
--where='where_condition' Off  
--xml Off  
Filed under: MySQL FAQ 3 Comments
29Jan/100

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.

Filed under: MySQL FAQ No Comments
19Nov/0928

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.

9Apr/0914

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.

Filed under: MySQL FAQ 14 Comments
7Apr/095

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';
Filed under: MySQL FAQ 5 Comments
10Feb/090

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.

Filed under: MySQL FAQ No Comments
10Nov/063

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
31Aug/061

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 |
+----+
Filed under: MySQL FAQ 1 Comment
21Aug/067

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.