Logging MySQL queries in table

Just came around a handy (i.e. Docker environment) logging feature of MariaDB.

Enable logging:

SET global general_log = 1;
SET global log_output = 'table';


View query log:

SELECT * FROM mysql.general_log
-- OR --
SELECT * FROM mysql.general_log ORDER BY event_time DESC


Disable logging:

SET global general_log = 0;

SQL to get WooCommerce product categories (terms)

For an import script I needed to get WooCommerce product categories so I ended up looking the generated query in /var/log/mysql/query.log for get_the_terms($postID, ‘taxonomy_name’)

I ended up using the following SQL query to get product categories:

SELECT t.*, tt.*
FROM wp_terms AS t 
INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id 
INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy IN ('product_cat') AND tr.object_id IN (11448)

Connect to localhost MySQL from Docker container

To test connection to MySQL/MariaDB server running on localhost from Docker container try:

docker run --rm -it --net=host mariadb mysql -u root -h
--rm  Automatically remove the container when it exits https://docs.docker.com/engine/reference/run/#clean-up-rm
-i    Keep STDIN open even if not attached https://docs.docker.com/engine/reference/run/#foreground
-t    Allocate a pseudo-TTY
--net Connect a container to a network https://docs.docker.com/engine/reference/run/#network-settings

New path for MySQL query log on OSX

Sometimes during development I need to see what SQL queries are executed and listing them in Console.app is maybe the most convenient solution on OSX.


To see queries in Console

mysqld query log OSX

I just created /etc/mysql/my.cnf with:

general_log = 1
# general_log_file = /usr/local/mysql/data/mysqld.query.log
general_log_file = /var/log/mysql/mysqld.query.log

slow_query_log = 1
long_query_time = 1 # second
# slow_query_log_file = /usr/local/mysql/data/mysqld.slow.log
slow_query_log_file = /var/log/mysql/mysqld.slow.log
log_queries_not_using_indexes = 0

MySQL server needs own permissions on the parent log dir so:

sudo mkdir /var/log/mysql
sudo chown -R _mysql:_mysql /var/log/mysql

Last step is server restart.

sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

On a Linux machine I use multitail as an alternative.

Enabling MySQL 5.7 query logging on OSX

I got used to it that sometimes I need to check the queries run by MySQL. After clean OSX upgrade I forgot to enable it.

So I added the following to /etc/mysql/my.cnf:



general_log = 1
general_log_file = /usr/local/mysql/data/mysqld.query.log

slow_query_log = 1
long_query_time = 1 # second
slow_query_log_file = /usr/local/mysql/data/mysqld.slow.log
log_queries_not_using_indexes = 0

The default one that comes with the package is at /usr/local/mysql/support-files/my-default.cnf

There’s also an option to set logging via SQL queries.

Getting CREATE SQL for single Doctrine entity in Symfony2

I needed to create a new table for a single new Doctrine entity into an existing database.

I was in hurry and couldn’t find a solution so came up with my own.

Dump the whole SQL into a file:

php app/console doctrine:schema:create --dump-sql > dump.sql

For fixing lowercase table name I found a solution at http://kurtfunai.com/2012/09/doctrine-table-name.html

 * NAMESPACE\ExampleBundle\Entity\User
 * @ORM\Entity
 * @ORM\Table(name="user")
class User

Getting ‘doesn’t have a default value’ from MySQL (MariaDB)?

Solution for me from ideas at https://stackoverflow.com/questions/15438840/mysql-error-1364-field-doesnt-have-a-default-values and https://www.farbeyondcode.com/Solution-for-MariaDB-Field–xxx–doesn-t-have-a-default-value-5-2720.html was to remove ‘STRICT_TRANS_TABLES’ from my.cnf.

So I ended up with the following in /usr/local/mysql/my.cnf: