A Little Noise

September 8, 2016

Aggregate JSON function in MySQL

Filed under: MySQL FAQ — snoyes @ 9:22 am

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:

| 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:

         @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"}] |

July 23, 2013

mysqldump privileges required

Filed under: MySQL FAQ — snoyes @ 10:12 am

“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.”

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 *.*
–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  

January 29, 2010

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

Filed under: MySQL FAQ — snoyes @ 12:12 pm

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

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">
    <field1>value a</field1>
    <field2>value b</field2>
    <field1>value c</field1>
    <field2>value d</field2>

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.

November 19, 2009

Fine-tuning FULLTEXT: Adding word characters

Filed under: MySQL FAQ — snoyes @ 8:27 am

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.

April 9, 2009

errno: 121 (Duplicate key) with CREATE TABLE

Filed under: MySQL FAQ — snoyes @ 7:02 am

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:

  constraint_type = 'FOREIGN KEY' 
  AND table_schema = DATABASE() 

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

April 7, 2009

Error 1307 creating stored procedure

Filed under: MySQL FAQ — snoyes @ 10:52 am

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:

    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 
  table_schema = 'mysql' 
  AND table_name = 'proc' 
  AND column_name LIKE 'body_utf8';

February 10, 2009


Filed under: MySQL FAQ — snoyes @ 9:31 am

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.

November 10, 2006

Drop all tables

Filed under: MySQL FAQ — snoyes @ 10:06 pm

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


August 31, 2006

Auto_increment Indexing

Filed under: MySQL FAQ — snoyes @ 5:15 pm

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 (
) ENGINE=MyISAM; -- Works for InnoDB too.

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

SELECT * FROM testTable;

| id |
|  1 |
|  1 |
|  2 |

August 21, 2006

Top N of a Group

Filed under: MySQL FAQ — snoyes @ 7:57 pm

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.

Older Posts »

Powered by WordPress