Mysql

MySQL Client Format Output to Space-Delimited

By @sskaje
Link: https://sskaje.me/2017/05/mysql-client-format-output-space-delimited/

Traditionally:

mysql> select * from INNODB_SYS_COLUMNS limit 3;
+----------+----------+-----+-------+--------+-----+
| TABLE_ID | NAME     | POS | MTYPE | PRTYPE | LEN |
+----------+----------+-----+-------+--------+-----+
|       11 | ID       |   0 |     1 | 524292 |   0 |
|       11 | FOR_NAME |   1 |     1 | 524292 |   0 |
|       11 | REF_NAME |   2 |     1 | 524292 |   0 |
+----------+----------+-----+-------+--------+-----+

mysql> select * from INNODB_SYS_COLUMNS limit 3;

+----------+----------+-----+-------+--------+-----+

+----------+----------+-----+-------+--------+-----+

| 11 | ID | 0 | 1 | 524292 | 0 |

| 11 | FOR_NAME | 1 | 1 | 524292 | 0 |

| 11 | REF_NAME | 2 | 1 | 524292 | 0 |

+----------+----------+-----+-------+--------+-----+

To save output:

mysql> pager cat > /tmp/output
PAGER set to 'cat > /tmp/output'
mysql> select * from INNODB_SYS_COLUMNS limit 3;
3 rows in set (0.00 sec)

mysql> pager cat > /tmp/output

PAGER set to 'cat > /tmp/output'

mysql> select * from INNODB_SYS_COLUMNS limit 3;

3 rows in set (0.00 sec)

# cat /tmp/output 
+----------+----------+-----+-------+--------+-----+
| TABLE_ID | NAME     | POS | MTYPE | PRTYPE | LEN |
+----------+----------+-----+-------+--------+-----+
|       11 | ID       |   0 |     1 | 524292 |   0 |
|       11 | FOR_NAME |   1 |     1 | 524292 |   0 |
|       11 | REF_NAME |   2 |     1 | 524292 |   0 |
+----------+----------+-----+-------+--------+-----+

# cat /tmp/output

+----------+----------+-----+-------+--------+-----+

+----------+----------+-----+-------+--------+-----+

| 11 | ID | 0 | 1 | 524292 | 0 |

| 11 | FOR_NAME | 1 | 1 | 524292 | 0 |

| 11 | REF_NAME | 2 | 1 | 524292 | 0 |

+----------+----------+-----+-------+--------+-----+

To format output to space delimited:

mysql> pager grep -Ev '^\+-' | column -t -s '|'                                                                                                                                                                                               PAGER set to 'grep -Ev '^\+-' | column -t -s '|''
mysql> select * from INNODB_SYS_COLUMNS limit 3;
 TABLE_ID    NAME        POS    MTYPE    PRTYPE    LEN 
       11    ID            0        1    524292      0 
       11    FOR_NAME      1        1    524292      0 
       11    REF_NAME      2        1    524292      0 
3 rows in set (0.01 sec)

mysql> pager grep -Ev '^\+-' | column -t -s '|' PAGER set to 'grep -Ev '^\+-' | column -t -s '|''

mysql> select * from INNODB_SYS_COLUMNS limit 3;

TABLE_ID NAME POS MTYPE PRTYPE LEN

11 ID 0 1 524292 0

11 FOR_NAME 1 1 524292 0

11 REF_NAME 2 1 524292 0

3 rows in set (0.01 sec)

I found CentOS 7 and Ubuntu 16.04 uses different column commands,
in CentOS 7, column is from util-linux, in Ubuntu 16.04 and OSX, BSD like.

I tried to format output as TSV, but failed.

MySQL Client Format Output to Space-Delimited by @sskaje: https://sskaje.me/2017/05/mysql-client-format-output-space-delimited/

Debian Reset MySQL Root Password

By @sskaje
Link: https://sskaje.me/2015/06/debian-reset-mysql-root-password/

Only for mysql server installed by ‘apt-get‘.

Under System root and localhost only.

# cat /etc/mysql/debian.cnf

1	# cat /etc/mysql/debian.cnf

You can find password for ‘debian-sys-maint‘, you can update root password by logging in as this user.

If you have replication, you may need to update all slaves manually.

Debian Reset MySQL Root Password by @sskaje: https://sskaje.me/2015/06/debian-reset-mysql-root-password/

Incoming search terms:

sskaje username password

MySQL/Hive/Presto/Impala Transposition

By @sskaje
Link: https://sskaje.me/2014/02/mysql-hive-presto-impala-transposition/

Rows to Columns

Rows to Comma Separated String

FROM:
+-----+-----+
| uid | tag |
+-----+-----+
|   1 |   1 |
|   1 |   2 |
|   1 |   3 |
|   2 |   1 |
|   2 |   4 |
|   3 |   5 |
+-----+-----+
TO:
+-----+----------+
| uid | tag_list |
+-----+----------+
|   1 | 1,2,3    |
|   2 | 1,4      |
|   3 | 5        |
+-----+----------+

FROM:

+-----+-----+

| uid | tag |

+-----+-----+

| 1 | 1 |

| 1 | 2 |

| 1 | 3 |

| 2 | 1 |

| 2 | 4 |

| 3 | 5 |

+-----+-----+

TO:

+-----+----------+

| uid | tag_list |

+-----+----------+

| 1 | 1,2,3 |

| 2 | 1,4 |

| 3 | 5 |

+-----+----------+

Use GROUP_CONCAT(). This function also works in Infobright.
Continue reading “MySQL/Hive/Presto/Impala Transposition” »

MySQL/Hive/Presto/Impala Transposition by @sskaje: https://sskaje.me/2014/02/mysql-hive-presto-impala-transposition/