Overview

In some cases, MySQL creates internal temporary tables while processing queries.
On the base of the dimensions of the resultset MySQL will use the MEMORY engine AND/OR the MyISAM engine.
The difference is that MEMORY engine will handle the table in memory, while MyISAM will write it on disk.
A table created using the MEMORY engine can be automatically converted by the MySQL server if exceed the define threshold.

Then there are others circumstances which could create temporary tables using MEMORY but that can go to MyISAM (so disk) if too large:

• If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue;
• DISTINCT combined with ORDER BY may require a temporary table;
• In the case of the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.

There are some conditions which will force the temporary table to use MyISAM :

• Presence of a BLOB or TEXT column in the table;
• Presence of any column in a GROUP BY or DISTINCT clause larger than 512 bytes;
• Presence of any column larger than 512 bytes in the SELECT list, if UNION or UNION ALL;

The tables explicitly created with CREATE TABLE ENGINE MEMORY use ONLY the max_heap_table_size system variable to determines how large the table is permitted to grow and there is no conversion to on-disk format.

Why still doing a post on this issue?

Because i still see on MySQL configuration that DBA seems use it as separate assignments.Let us take this from the start, the manual.
In the manual section (http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_tmp_table_size) we can find the tmp_table_size definition.

Command-Line Format --tmp_table_size=#
Config-File Format tmp_table_size
Option Sets Variable Yes,tmp_table_size
Variable Name tmp_table_size
Variable Scope Both
Dynamic Variable Yes
Permitted Values
Type numeric
Default system dependent
Range 1024-4294967295

What we need to take in account, and in mind, are these two element:

Default system dependent
Range 1024-4294967295

Now let us check for the max_heap_table_size

Command-Line Format --max_heap_table_size=#
Config-File Format max_heap_table_size
Option Sets Variable Yes, max_heap_table_size
Variable Name max_heap_table_size
Variable Scope Both
Dynamic Variable Yes
Permitted Values
Type numeric
Default 16777216
Range 16384-4294967295

We can easily identify that both can be dynamically modified (Dynamic Variable Yes), that both the variable could be assign at global scope or per session (Variable Scope Both).

Finally that max_heap_size is by default 16M while tmp_table_size is system dependent.

Let us to try to clarify this digging in the code doing:

<mysql_source_dir>/sql ->
[root@tusacentral07 sql]# grep tmp_table_size *.*

and we found this files.

set_var.cc:static sys_var_thd_ulonglong sys_tmp_table_size(&vars, "tmp_table_size",
set_var.cc: &SV::tmp_table_size);

From that my understanding is that both starts with a value of 16MB full-stop.

In set_var.cc the statement is:

"static sys_var_thd_ulonglong sys_tmp_table_size(&vars, "tmp_table_size",&SV::tmp_table_size);"

we have no explicit reference to allocation.

The place were we have it is in mysqld.cc :

{"tmp_table_size", OPT_TMP_TABLE_SIZE,
"If an internal in-memory temporary table exceeds this size, MySQL will"
" automatically convert it to an on-disk MyISAM table.",
&global_system_variables.tmp_table_size,
&max_system_variables.tmp_table_size, 0, GET_ULL,
REQUIRED_ARG, 16*1024*1024L, 1024, MAX_MEM_TABLE_SIZE, 0, 1, 0},

{"max_heap_table_size", OPT_MAX_HEP_TABLE_SIZE,
"Don't allow creation of heap tables bigger than this.",
&global_system_variables.max_heap_table_size,
&max_system_variables.max_heap_table_size, 0, GET_ULL,
REQUIRED_ARG, 16*1024*1024L, 16384, MAX_MEM_TABLE_SIZE,
MALLOC_OVERHEAD, 1024, 0},

In the MySQL 5.5 and above, we have a clearer place and assignment:

in sys_vars.cc:

static Sys_var_ulonglong Sys_tmp_table_size(
"tmp_table_size",
"If an internal in-memory temporary table exceeds this size, MySQL "
"will automatically convert it to an on-disk MyISAM table",
SESSION_VAR(tmp_table_size), CMD_LINE(REQUIRED_ARG),
VALID_RANGE(1024, (ulonglong)~(intptr)0), DEFAULT(16*1024*1024),
BLOCK_SIZE(1));

static Sys_var_ulonglong Sys_max_heap_table_size(
"max_heap_table_size",
"Don't allow creation of heap tables bigger than this",
SESSION_VAR(max_heap_table_size), CMD_LINE(REQUIRED_ARG),
VALID_RANGE(16384, (ulonglong)~(intptr)0), DEFAULT(16*1024*1024),
BLOCK_SIZE(1024));

But the result is the same, MySQL start from 16MB, no matter what system you have, or if it does, is very well hide from search.

Now let see what the manual say about how this two variables and their relation:

The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.)

If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table.

Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.

This variable does not apply to user-created MEMORY tables.

Well this one seems clear enough for everyone but to avoid confusion, check the code:

so again

[root@tusacentral07 sql]# grep tmp_table_size *.*

but this time we open the file

sql_select.cc

if (thd->variables.tmp_table_size == ~ (ulonglong) 0) // No limit
share->max_rows= ~(ha_rows) 0;
else
share->max_rows= (ha_rows) (((share->db_type() == heap_hton) ?
min(thd->variables.tmp_table_size,
thd->variables.max_heap_table_size) :
thd->variables.tmp_table_size) /
share->reclength);

Here is were we find the min() functions used from MySQL.

min(thd->variables.tmp_table_size,thd->variables.max_heap_table_size)

So as the manual state MySQL will take the LOWER value assign to the two variables, as the good value for tmp_table_size (only).

To be more clear, if we have in the my.cnf:

tmp_table_size      = 32M
max_heap_table_size = 16M

MySQL will assign to tmp_table_size the value of 16M and to max_heap_table_size 16M.

if we have:

tmp_table_size      = 16M
max_heap_table_size = 32M

MySQL will assign to tmp_table_size the value of 16M and to max_heap_table_size 32M.

This needs to take in account because I still see at client site a lot of :

tmp_table_size = 32M
max_heap_table_size = 16M

Which makes no sense.

Finally, this variable has huge possible impact on memory consumption.

Just consider that having:

max_connctions = 600
tmp_table_size = 32M
max_heap_table_size = 32M

These settings could potentially generate a memory usage on the server of (600 x 16MB) = 9.3 GB.

Given that the variables are dynamic, and that could be assign at session level, it is good practics to increase the value of tmp_table_size only when is really needed, in the session before performing the operation which will require it.

Seems all to me but If I have miss something here, corrections are welcome.

{joscommentenable}

===============================================================

MySQL is really poor in is support for AUDITING.

There will be some new feature in 5.5 or later, in which we will see the AUDIT interface finally implemented.

But ... who knows what will really happen, and who know if it will work or not.

So in the meantime, if you want to have some information printed out (at least), you can use this simple pathc.

What you need to do is modifying the file mysqld.cc in the sql directory.

in:> sql/mysqld.cc
Look for the function : int my_message_sql(uint error, const char *str, myf MyFlags)

change the code in the function with the one here, but also check that it is not inserting new bugs ;-).

int my_message_sql(uint error, const char *str, myf MyFlags)
{
THD *thd;
DBUG_ENTER("my_message_sql");
DBUG_PRINT("error", ("error: %u  message: '%s'", error, str));

DBUG_ASSERT(str != NULL);
/*
Code added for writing access denied
*/
if ((global_system_variables.log_warnings > 1) &&
(error == ER_DBACCESS_DENIED_ERROR ||
error == ER_ACCESS_DENIED_ERROR ||
error == ER_TABLEACCESS_DENIED_ERROR ||
error == ER_COLUMNACCESS_DENIED_ERROR ||
error == ER_SPECIFIC_ACCESS_DENIED_ERROR ||
error == ER_PROCACCESS_DENIED_ERROR))
{ // then this is an access-denied error, log it
sql_print_warning(str);
}

Recompile and test it just trying to accessing something with a user that DO NOT have th permission to do it.

You will see the Alert in the log!

So simple so coool

It is possible to trap all the query sent to MySQL, using tcpdump.

This will be done capturing the network traffic. Since this uses the libpcap library, we need to have root privileges.

Because tcpdump will not capture queries sent to socket file, be sure that the request you want to track will not use it.

So having clarified this obvious limitation, let us assume that all the application running locally are accessing MySQL using 127.0.0.1 on standard port 3306.

Capturing the queries to a file named all_query_tcpdump.log executing:

tcpdump -i lo port 3306 -s 2000 -w all_query_tcpdump.log

Please note that we will capture 2000 (-s 2000) headers bytes, which should be fine in the most cases.

Having done the data collection, we have now to analyze it.
For that we will use  thsark (wireshark) :

tshark -r all_query_tcpdump.log -d tcp.port==3306,mysql -T fields -e mysql.query > mysql_query.log

The generated log, will contains a lot of empty lines which could be removed as follow:
cat mysql_query.logt | grep -v "^$" > mysql_clean_query.log

That's all folks!!!

Very often people not expert ask me some simple detailed directions on how to install MySQL from fresh.

The following is a simple one that if you will follow step by step should give you a MySQL working installation.

this articule assume that you have the possibility to attach different storage for:

System and binaries

MySQLData

MySQLLogs

Prerequisite:

1) You need to have access to the client area for download the MySQL software from somwere possible points are: www.mysql.com www.askmonty.org www.percona.com

2) Perl DBI and DBD::mysql installed on the machine (See How to Install MySQL Perl module guide)

3) Root privileges

Steps:

1) Install the Perl modules

2) Create a directory layout as follow:

/mysql_data/
|-- data
|   |-- mysql
|   `-- test
`-- lost+found 

/mysql_logs/
|-- exports
| 
`-- logs
    |-- Q_MySQL

    |-- binlog

    |-- general

    |-- innodb
  `-- relay

3) Create a directory for the MySQL binary in:

/usr/local/mysqldistributions

4) Expand the downloaded file (MySQL server binary installation) into the mysqldistributions directory

5) Create the symbolic link to /usr/local/mysql

6) Copy the file from /usr/local/mysql/support-files/mysql.server to /etc/init.d/ as mysql

7) Register it chkconfig –-add mysql

8) Create in /etc/ the file my.cnf and modify the server-id with a unique value

9) Create the group and user mysql

groupadd mysql
seradd -g mysql –s /bin/bash –p mysql 

10) Change the permissions on all the relevant directories:

chown –R mysql:mysql /mysql_logs
chown –R mysql:mysql /mysql_data
chown –R mysql:mysql /usr/local/mysql*

11) Impersonate the user mysql su –l mysql

12) Run the script to initialize the mysql database

./scripts/./mysql_install_db --basedir=/usr/local/mysql --datadir=/mysql_data/data

13) Check that the directories are created and the permissions correctly set:

ll /mysql_data/data

14) Open another terminal window to check the log

15) Become root again

16) Start the MySQL server with

/etc/init.d/mysql start

17) On the opened terminal type:

tail -fn200 /mysql_data/data/.err to check what is going on

18) On the original terminal, go inside the mysql server using the local

mysql console: /usr/local/mysql/bin/mysql –uroot

19)Check for anonymous user to remove

root@localhost:mysql.sock [(none)]> select user,host from mysql.user;

+------+------------+
| user | host |
+------+------------+
| root | 127.0.0.1 |
| | localhost |
| root | localhost |
| | |
| root | |
+------+------------+

5 rows in set (0.00 sec)
and remove them:

root@localhost:mysql.sock [(none)]> drop user ''@localhost;
root@localhost:mysql.sock [(none)]> drop user ''@’machinename’;

    Add the Password for the user root for *ANY* hosts:

set password for root@localhost=Password('mysql');
set password for root@’machinename’=Password('mysql');

set password for root@’127.0.0.1’=Password('mysql');

20) Exit and try if all was done correctly:

root@localhost:mysql.sock [(none)]> exit

Bye

[root@xx mysql]# /usr/local/mysql/bin/mysql -uroot -pmysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.1.31sp1-enterprise-gpl-pro-log MySQL Enterprise Server - Pro Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

root@localhost:mysql.sock [(none)]>

21)Now add al the other user as you like

{joscommentenable}