General Query Log

The design of a data warehouse differs from relational design. Data warehouses are often designed according to the concept of the star schema.

When building a data warehouse, you usually put the cart before the horse:

• What questions should my data warehouse be able to answer?
• How do I have to design my model so that my questions can be answered easily?
• Where do I get the data to populate the model?
• How do I fill my model with the data?

For training purposes, we have investigated an issue that arises from …

The MariaDB Connection ID exists since long ago. So why bother about the Connection ID? Because it is interesting and you can do some interesting things with the Connection ID like tracking statements in your connections and find where they come from your application code.

The MariaDB Connection ID is a strictly monotonic increasing number starting with 1 at server restart:

shell> mariadb --user=root --execute='SELECT CONNECTION_ID()<br>G' | grep CONNECTION_ID
CONNECTION_ID(): 2372
shell> …

Modern Linux Systems have a mechanism called logrotate to rotate different log files.

The general configuration file is located under /etc/logrotate.conf and specific changes are under /etc/logrotate.d/

By default the logrotate job is started once a day by a O/S cron.daily job: /etc/cron.daily/logrotate

Because the default log rotation configuration does not exactly what I want I have adapted it a bit:

First I need a database user for log rotation:

CREATE USER 'logrotate'@'localhost' IDENTIFIED BY 'secret'; …

The General Query Log in MariaDB is a Log (file or table) that logs every statement sent from the Application to the MariaDB Database Server from the Connect Command to the Quit Command. A simple example you can find here:

bin/mysqld, Version: 10.4.6-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysql.sock
Time                Id Command  Argument
190709 21:27:30   3872 Connect  root@localhost as anonymous on test
                  3872 Query    INSERT INTO test …

Sometimes, MySQL tablespace file(s) might be deleted by mistake, e.g. delete the shared tablespace (ibdata1) or an individual tablespace (table_name.ibd).

In this post I will show you how to recover those files (on Linux OS) having only one condition, MySQL service should still be running. If MySQL service stopped after deleting that file, this method will not work, so it is extremely important to act as quick as possible to avoid data loss.

The following is a simple table creation (innodb_file_per_table is …

Sometimes, it is required to enable the General Query Log (which is disabled by default). If the General Query Log is enabled the server writes to this log information when clients connect or disconnect, and each SQL statement received from the client.

The question is, does enabling the General Query Log affects the MySQL performance ?
Also, it is possible to record the output of this log into either file or table in the mysql database (mysql.general_log), what is the performance impact of each one? …

I have enabled the General Query Log and the Slow Query Log in MySQL 5.6.13 in windows server. File is created but sql statement are not capturing. This is production server now its not possible to restart.

Kindly help me to solve the issue.

mysql> show global variables like 'slow%';
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| slow_launch_time    | 2 …

Dear friends, Is there is any command to check when user and password created in MySQL user table. I want command for user logins last password change date and password created date. in MSSQL we are able to take where as in MySQL unable to take. Kindly help me

We did recently a review of one of our customers systems and we found that the Query Cache was disabled even thought it had significant more read than write queries.
When we asked the customer why he has not enabled the Query Cache he mentioned a review that was done a few years ago and which stated that the Query Cache hit ratio was non optimal.
This was verified on a testing system which had the Query Cache enabled by accident.

But we all thought that the Query Cache would make sense in this situation so …

1600 - 1649 1700 - 1749

• Error: 1650 SQLSTATE: HY000 (ER_SLAVE_IGNORE_SERVER_IDS)

  Message: The requested server id %d clashes with the slave startup option –replicate-same-server-id

• Error: 1651 SQLSTATE: HY000 (ER_QUERY_CACHE_DISABLED)

  Message: Query cache is disabled; restart the server with query_cache_type=1 to enable it

• Error: 1652 SQLSTATE: HY000 (ER_SAME_NAME_PARTITION_FIELD)

  Message: Duplicate partition field name ‘%s’

• Error: 1653 SQLSTATE: HY000 ( …

MySQL Statement Syntax

Data Definition Language (DDL) Statements

Often asked but informations are spread around: The limitations of MySQL.

If you know any other MySQL limitations, please let us know.

Table of Contents

• General limitations of MySQL
• Limitations of MySQL 4.1
• Limitations of Joins
• Limitations of the MyISAM storage engine
• Limitations of MySQL 5.0
• Limitations of Joins
• Limitations of the MyISAM storage engine
• Limitations of InnoDB
• Limitations of MySQL 5.1
• Limitations of Joins
• Limitations of Partitions
• Limitations of MySQL Cluster
• Limitations in Galera Cluster …

What is a Federated Table?

A Federated Table is a table which points to a table in an other MySQL database instance (mostly on an other server). It can be seen as a view to this remote database table. Other RDBMS have similar concepts for example database links.

What can I do with a Federated Table?

To show what you can do with a federated table let us assume the following constellation: Two MySQL databases on two different servers. The first one called provider (it provides the data) the second one called …

The following tools we use sometimes for our consulting engagements…

Caution: These tools are NOT for production use! Use with care!

Tools

• allocate.c
• backslashG2table.pl
• general2bench.pl
• memuse.pl
• memwaster.c
• MySQLprofiler
• pointer_size.c
• alter_engine.pl
• commit_demo.pl
• test.sh
• vm_mon.sh
• read_process_memory.c
• read_process_memory.sh
• cluster_initial_test.pl
• mem_map.pl
• csv_converter.pl
• mem_tracker.sh
• filesystem_table.php

Sample Databases

• MySQL world: world.tgz (92 kbyte, runs with version 4.1 (tested …