MySql query log to table or file

For debugging it’s often useful to see all queries and since you usually don’t want to save all this data, it’s nice to be able to turn it on and off efficiently.

You can log to file but also to a table in the mysql database.

While you can turn this on and off by updating the my.cnf file, you can also do it via mysql commands:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

One nice thing about logging to a table is that you can search for specific things, see examples below.

USE mysql;
SELECT * FROM general_log;
SELECT * FROM general_log WHERE argument LIKE "DELETE%";

You probably want to clear the log periodically and can do so with:

TRUNCATE general_log;

Then simply turn OFF when finished.

SET GLOBAL general_log = 'OFF';

If you would rather log to file (the default), use:

SET GLOBAL log_output = "FILE";
SET GLOBAL general_log_file = "/path/to/your/logfile.log";
SET GLOBAL general_log = 'ON';

Docker swarm errors: check storage driver

After adding a new worker node to a swarm, it had errors including problems communicating with other services/containers on the overlay network (previously added nodes, both manager and worker, worked fine). It turned out that this newest node used a new installer that defaulted the storage driver to overlay2 — where the previously installed nodes were using aufs. All nodes are running on Ubuntu 16.04.

You can check for support with the grep aufs /proc/filesystems command. If you don’t see any results, you may need to install sudo apt-get install linux-image-extra-`uname -r` (or other updates). If you do have aufs support and Docker defaults to overlay2, you can spec the storage driver in /etc/docker/daemon.json via:

  "storage-driver": "aufs"

and restart Docker (via systemctl restart docker.service or similar). Note that any images or containers created with the old storage driver will not be accessible to the new one so you should delete/prune/etc what you won’t need and stop & remove all running containers prior to restarting.

Docker: fix terminal width issue

Sometimes the terminal size reverts to the 80×24 default size, particularly when using Docker-in-Docker. To fix, pass the terminal specs when calling `docker run` or `docker exec`:

docker run --rm -it -e COLUMNS=$COLUMNS -e LINES=$LINES -e TERM=$TERM -it ubuntu:16.04

(see issue here)

Simple Made Easy (link)

An oldie-but-good I was reminded of recently:

Rich Hickey emphasizes simplicity’s virtues over easiness’, showing that while many choose easiness they may end up with complexity, and the better way is to choose easiness along the simplicity path.

Sample takeaways:

  • We should aim for simplicity because simplicity is a prerequisite for reliability.
  • Simple is often erroneously mistaken for easy. “Easy” means “to be at hand”, “to be approachable”. “Simple” is the opposite of “complex” which means “being intertwined”, “being tied together”. Simple != easy.

Choose simple constructs over complexity-generating constructs; It's the artifacts, not the authoring; Create abstractions with simplicity as a basis; Simplify the problem space before you start; Simplicity often means making more things, not fewer; Reap the benefits!

He even predicts (to some extent) microservice architecture by declaring a set of separate strings hanging down independently can show a byproduct of simplicity can be more things, but things that are easier to understand and manage than if the strings were intertwined — and easier to update individually.

Swap column values in MySql (via temp variables)

If you want to swap values in one query in MySql without a temporary table, you can use variables to hold values that otherwise would be changed before you can use them.

E.g., swap start_date and end_date via:

UPDATE mydates SET start_date=@tmp:=start_date, start_date=end_date, end_date=@tmp WHERE start_date > end_date

« Previous Entries