Interesting Things to Know about MySQL
Mike Chirico (mc******@users.sourceforge.net)
Copyright (GPU Free Documentation License) 2004
Last Updated: Mon Jun 7 10:37:28 EDT 2004
The latest version of this document can be found at:
http://prdownloads.sourceforge.net/s...l.txt?download
TIP 1:
Find out who is doing what, and kill the process if needed. This example
kills
Id 657
mysql> show processlist;
show processlist;
+-----+------+-----------+---------+---------+-------+-------+--------------
----+
| Id | User | Host | db | Command | Time | State | Info
|
+-----+------+-----------+---------+---------+-------+-------+--------------
----+
| 657 | prog | localhost | weather | Sleep | 28619 | | NULL
|
| 782 | prog | localhost | weather | Sleep | 853 | | NULL
|
| 785 | prog | localhost | NULL | Query | 0 | NULL | show
processlist |
+-----+------+-----------+---------+---------+-------+-------+--------------
----+
3 rows in set (0.00 sec)
mysql>kill 657
Or, from the command line, to kill process 782
[root@third-fl-71 mysql]# mysqladmin processlist
+-----+------+-----------+---------+---------+------+-------+---------------
---+
| Id | User | Host | db | Command | Time | State | Info
|
+-----+------+-----------+---------+---------+------+-------+---------------
---+
| 782 | prog | localhost | weather | Sleep | 2676 | |
|
| 785 | prog | localhost | | Sleep | 1823 | |
|
| 793 | root | localhost | | Query | 0 | | show
processlist |
+-----+------+-----------+---------+---------+------+-------+---------------
---+
[root@third-fl-71 mysql]#
[root@third-fl-71 mysql]# mysqladmin kill 782
Note, the following can also be helpful
mysql> show status;
or
mysql> show status\G
also
mysql> show innodb status;
TIP 2:
Clean up binary log files. For a default install they may be in
/usr/local/var/
with names ending in -bin.000001,-bin.000002,..
mysql> reset master;
reset master;
Query OK, 0 rows affected (0.02 sec)
See (TIP 24:) details working with binary log files and (TIP 25:)
explains
how to setup logging.
TIP 3:
Can the order of the columns in a create statement make a difference?
YES
create table t (
a int,
b int,
timeUpdate timestamp,
timeEnter timestamp );
The first timestamp will always be the "automatically generated" time.
So
if the record is updated, or inserted, this time gets changed. If the
order is changed, "timeEnter" is before "timeUpdate", then, "timeEnter"
would get updated. First timestamp column updates automatically.
Note, in the table above timeEnter will only get updated if passed a
null
value.
insert into t (a,b,timeEnter) values (1,2,NULL);
Hints: Need mm-dd-yyyy hh:mm:ss format?
select a,b,DATE_FORMAT(timeUpdate,'%m-%d-%Y
%T'),DATE_FORMAT(timeEnter,'%m-%d-%Y %T') from t;
+------+------+---------------------------------------+---------------------
-----------------+
| a | b | DATE_FORMAT(timeUpdate,'%m-%d-%Y %T') |
DATE_FORMAT(timeEnter,'%m-%d-%Y %T') |
+------+------+---------------------------------------+---------------------
-----------------+
| 3 | 2 | 04-15-2004 19:14:36 | 04-15-2004
19:15:07 |
| 3 | 2 | 04-15-2004 19:14:39 | 04-15-2004
19:15:07 |
| 5 | 5 | 00-00-0000 00:00:00 | 04-15-2004
19:15:53 |
| 1 | 2 | 00-00-0000 00:00:00 | 04-15-2004
19:20:15 |
+------+------+---------------------------------------+---------------------
-----------------+
4 rows in set (0.00 sec)
TIP 4:
Connect, create table and select with Perl (Linux). First the DBI module
is needed, which
can be installed from the system prompt as follows:
# perl -MCPAN -e shell
cpan> install DBI
cpan> install DBD::mysql
The following is an example program:
#! /usr/bin/perl -w
# Copyright (GPL) Mike Chirico mc******@users.sourceforge.net
#
# Program does the following:
# o connects to mysql
# o creates perlTest if it doesn't exist
# o inserts records
# o selects and displays records
#
# This program assumes DBI
#
# perl -MCPAN -e shell
# cpan> install DBI
# cpan> install DBD::mysql
#
#
#
#
#
use strict;
use DBI;
# You will need to change the following:
# o database
# o user
# o password
my $database="yourdatabase";
my $user="user1";
my $passwd="hidden";
my $count = 0;
my $tblcreate= "
CREATE TABLE IF NOT EXISTS perlTest (
pkey int(11) NOT NULL auto_increment,
a int,
b int,
c int,
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) ";
my $insert= "
insert into perlTest (a,b,c)
values (1,2,3),(4,5,6),(7,8,9)";
my $select="
select a,b,c from perlTest ";
my $dsn = "DBI:mysql:host=localhost;database=${database} ";
my $dbh = DBI->connect ($dsn, $user, $passwd)
or die "Cannot connect to server\n";
my $s = $dbh->prepare($tblcreate);
$s->execute();
$s = $dbh->prepare($insert);
$s->execute();
$s = $dbh->prepare($select);
$s->execute();
while(my @val = $s->fetchrow_array())
{
print " $val[0] $val[1] $val[2]\n";
++$count;
}
$s->finish();
$dbh->disconnect ( );
exit (0);
TIP 5:
Remove duplicate entires. Assume the following table and data.
CREATE TABLE IF NOT EXISTS dupTest (
pkey int(11) NOT NULL auto_increment,
a int,
b int,
c int,
timeEnter timestamp(14),
PRIMARY KEY (pkey)
);
insert into dupTest (a,b,c) values (1,2,3),(1,2,3),
(1,5,4),(1,6,4);
mysql> select * from dupTest;
select * from dupTest;
+------+------+------+------+---------------------+
| pkey | a | b | c | timeEnter |
+------+------+------+------+---------------------+
| 1 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
| 2 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
| 3 | 1 | 5 | 4 | 2004-04-16 10:55:35 |
| 4 | 1 | 6 | 4 | 2004-04-16 10:55:35 |
+------+------+------+------+---------------------+
4 rows in set (0.00 sec)
mysql>
Note, the first two rows contains duplicates in columns a and b. It
contains
other duplicates; but, leave the other duplicates alone.
mysql> ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);
mysql> select * from dupTest;
select * from dupTest;
+------+------+------+------+---------------------+
| pkey | a | b | c | timeEnter |
+------+------+------+------+---------------------+
| 1 | 1 | 2 | 3 | 2004-04-16 11:11:42 |
| 3 | 1 | 5 | 4 | 2004-04-16 11:11:42 |
| 4 | 1 | 6 | 4 | 2004-04-16 11:11:42 |
+------+------+------+------+---------------------+
3 rows in set (0.00 sec)
TIP 6:
Show status information on a table. Note, if the database was started
with --safe-show-database or --skip-show-database some of these
commands
may not work. Note the "\G" option may provide a nicer format.
Show the create statement:
mysql> show create table dupTest\G
show create table dupTest\G
*************************** 1. row
***************************
Table: dupTest
Create Table: CREATE TABLE `dupTest` (
`pkey` int(11) NOT NULL auto_increment,
`a` int(11) default NULL,
`b` int(11) default NULL,
`c` int(11) default NULL,
`timeEnter` timestamp NOT NULL,
PRIMARY KEY (`pkey`),
UNIQUE KEY `a` (`a`,`b`)
) TYPE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
TIP 7:
Transactions: Not all table types support transactions. BDB and INNODB
type do support transactions.
Assuming the server has NOT been started with --skip-bdb
or --skip-innodb the following should work:
mysql> create table tran_test (a int, b int) type = InnoDB;
mysql> begin;
mysql> insert into tran_test (a,b) values (1,2);
mysql> select * from tran_test;
select * from tran_test;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.00 sec)
mysql> rollback;
mysql> select * from tran_test;
select * from tran_test;
Empty set (0.00 sec)
Summary: rollback undoes everything and commit will save.
TIP 8:
MERGE: Several tables can be merged into one.
CREATE TABLE log_01 (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;
CREATE TABLE log_02 (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;
CREATE TABLE log_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;
mysql> insert into log_01 (a,b) values (1,'log1');
mysql> insert into log_02 (a,b) values (1,'log2');
mysql> select * from log_summary;
select * from log_summary;
+------+------+------+---------------------+
| pkey | a | b | timeEnter |
+------+------+------+---------------------+
| 1 | 1 | log1 | 2004-04-16 11:59:55 |
| 1 | 1 | log2 | 2004-04-16 12:00:08 |
+------+------+------+---------------------+
2 rows in set (0.00 sec)
Reference:
http://dev.mysql.com/doc/mysql/en/MERGE.html
TIP 9:
Updating foreign keys in a multiuser environment. Using
LAST_INSERT_ID().
The LAST_INSERT_ID() is unique to the login session. This allows
updating
of foreign keys.
CREATE TABLE keytest (
pkey int(11) NOT NULL auto_increment,
ptext text,
ptype int(11),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
);
CREATE TABLE foreignkeytest (
pkey int(11) NOT NULL auto_increment,
pkeykeytest int(11) NOT NULL,
ptext text,
ptype int(11),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
);
mysql> insert into keytest(ptext,ptype) values ('one',1);
mysql> select LAST_INSERT_ID() from keytest;
select LAST_INSERT_ID() from keytest;
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.03 sec)
mysql> insert into foreignkeytest (ptext,pkeykeytest) values
('one',LAST_INSERT_ID());
Note: If your session didn't update any records, LAST_INSERT_ID()
will be zero. Never
assume LAST_INSERT_ID()+1 will be the next record. If another
session inserts a record,
this value may be taken. You are assured that this value will be
unique to the "session".
TIP 10:
Monitor port 3306:
tcpdump -i eth0 -nN -vvv -xX -s 1500 port 3306 and host not
192.168.1.102
The -s is length of each packet. This monitors all traffic on port
3306 excluding
the good client 192.168.1.102. The out will be in the following
format:
[root@third-fl-71 root]# tcpdump -i eth0 -nN -vvv -xX -s 1500 port 3306
and host not 192.168.1.102
tcpdump: listening on eth0
13:05:01.857705 192.168.1.103.4131 > 192.168.1.71.mysql: S [tcp sum ok]
2542149285:2542149285(0) win 16384 <mss 1460,nop,nop,sackOK> (DF) (ttl 128,
id 2873, len 48)
0x0000 4500 0030 0b39 4000 8006 6b90 c0a8 0167 E..0.9@...k....g
0x0010 c0a8 0147 1023 0cea 9786 1ea5 0000 0000 ...G.#..........
0x0020 7002 4000 ebe7 0000 0204 05b4 0101 0402 p.@.............
13:05:04.863552 192.168.1.103.4131 > 192.168.1.71.mysql: S [tcp sum ok]
2542149285:2542149285(0) win 16384 <mss 1460,nop,nop,sackOK> (DF) (ttl 128,
id 2878, len 48)
TIP 11:
Create a C or C++ API
Download the following:
http://prdownloads.sourceforge.net/s...ar.gz?download
Also reference:
http://dev.mysql.com/downloads/other/plusplus/
TIP 12:
Connect from Java
#
# mysql-connector-java-3.0.11-stable-bin.jar or later must be
downloaded
# from:http://dev.mysql.com/downloads/connector/j/3.0.html
#
# Compile:
# javac Connect.java
#
# Run:
# java Connect
import java.sql.*;
public class Connect
{
public static void main (String[ ] args)
{
Connection conn = null;
String url = "jdbc:mysql://localhost/";
String userName = "root";
String password = "";
try
{
Class.forName
("com.mysql.jdbc.Driver").newInstance ( );
conn = DriverManager.getConnection (url,
userName, password);
System.out.println ("Connected");
}
catch (Exception e)
{
System.err.println ("Cannot connect to
server");
}
finally
{
if (conn != null)
{
try
{
conn.close ( );
System.out.println ("Disconnected");
}
catch (Exception e) { /* ignore close errors
*/ }
}
}
}
}
TIP 13:
Print defaults for the current client connection
$ /usr/local/bin/my_print_defaults client mysql
--port=3306
--socket=/tmp/mysql.sock
--no-auto-rehash
TIP 14:
Quick Status:
mysql> \s
--------------
/usr/local/bin/mysql Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686)
Connection id: 642
Current database:
Current user: prog@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 4.1.1-alpha-log
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1_swedish_ci
Server characterset: latin1_swedish_ci
UNIX socket: /tmp/mysql.sock
Uptime: 1 day 15 hours 24 min 38 sec
Threads: 1 Questions: 4896 Slow queries: 0
Opens: 14 Flush tables: 1 Open tables: 8 Queries per second avg:
0.035
--------------
TIP 15:
"error: 1016: Can't open file:" If it's from an orphaned file, not in
the database
but on disk, then, the disk file may need to be deleted.
myisamchk can help with damaged files. It's best to stop the database.
# su -
# mysqladmin shutdown
# cd /usr/local/var/database
# myisamchk *
# /etc/init.d/mysql restart
TIP 16:
Finding records that do not match between two tables.
CREATE TABLE bookreport (
b_id int(11) NOT NULL auto_increment,
s_id int(11) NOT NULL,
report varchar(50),
PRIMARY KEY (b_id)
);
CREATE TABLE student (
s_id int(11) NOT NULL auto_increment,
name varchar(15),
PRIMARY KEY (s_id)
);
insert into student (name) values ('bob');
insert into bookreport (s_id,report)
values ( last_insert_id(),'A Death in the Family');
insert into student (name) values ('sue');
insert into bookreport (s_id,report)
values ( last_insert_id(),'Go Tell It On the Mountain');
insert into student (name) values ('doug');
insert into bookreport (s_id,report)
values ( last_insert_id(),'The Red Badge of Courage');
insert into student (name) values ('tom');
To find the sudents where are missing reports:
select s.name from student s
left outer join bookreport b on s.s_id = b.s_id
where b.s_id is null;
+------+
| name |
+------+
| tom |
+------+
1 row in set (0.00 sec)
Ok, next suppose there is an orphan record in
in bookreport. First delete a matching record
in student:
delete from student where s_id in (select max(s_id) from bookreport);
Now, how to find which one is orphaned:
select * from bookreport b left outer join
student s on b.s_id=s.s_id where s.s_id is null;
+------+------+--------------------------+------+------+
| b_id | s_id | report | s_id | name |
+------+------+--------------------------+------+------+
| 4 | 4 | The Red Badge of Courage | NULL | NULL |
+------+------+--------------------------+------+------+
1 row in set (0.00 sec)
To clean things up (Note in 4.1 you can't do subquery on
same table in a delete so it has to be done in 2 steps):
select @t_sid:=b.s_id from bookreport b left outer join
student s on b.s_id=s.s_id where s.s_id is null;
delete from student where s_id=@t_sid;
But, functions do work in delete. For instance the
following is possible:
delete from student where s_id=max(s_id);
It just a problem when joining the table where the
delete will occur with another table. Another
option is two create a second temp table and
locking the first one.
TIP 17:
Getting a random roll of the dice:
CREATE TABLE dice (
d_id int(11) NOT NULL auto_increment,
roll int,
PRIMARY KEY (d_id)
);
insert into dice (roll) values (1);
insert into dice (roll) values (2);
insert into dice (roll) values (3);
insert into dice (roll) values (4);
insert into dice (roll) values (5);
insert into dice (roll) values (6);
select roll from dice order by rand() limit 1;
TIP 18:
Creating and using your own password file.
This assumes MySQL has been configured with SSL support [
../configure --with-openssl ]
CREATE TABLE password (
p_id int(11) NOT NULL auto_increment,
id varchar(20),
valid enum('y','n'),
password BLOB,
timeEnter timestamp,
PRIMARY KEY (p_id)
);
insert into password (id,password)
values ('bob',des_encrypt('secret','somekey'));
insert into password (id,password)
values ('tom',des_encrypt('password','somekey'));
insert into password (id,password)
values ('kate',des_encrypt('desmark','somekey'));
insert into password (id,password)
values ('tim',des_encrypt('tim','somekey'));
insert into password (id,password)
values ('sue',des_encrypt('SUM','somekey'));
insert into password (id,password)
values ('john',des_encrypt('dotgo86','somekey'));
insert into password (id)
values ('randal');
mysql> select id,des_decrypt(password,'somekey') from password;
+--------+---------------------------------+
| id | des_decrypt(password,'somekey') |
+--------+---------------------------------+
| bob | secret |
| tom | password |
| kate | desmark |
| tim | tim |
| sue | SUM |
| john | dotgo86 |
| randal | NULL |
+--------+---------------------------------+
7 rows in set (0.00 sec)
Note the bad passwords in the file ('secret','password', and
password is the same as the id.
The following update statement will fill in the
value for valid:
update password set valid =
COALESCE(
concat(
substring('y',1,
1-sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test '))))
),
substring('n',1,
sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test '))))
)
)
,'n');
Which gives the following:
mysql> select id,valid from password;
select id,valid from password;
+--------+-------+
| id | valid |
+--------+-------+
| bob | n |
| tom | n |
| kate | y |
| tim | n |
| sue | y |
| john | y |
| randal | n |
+--------+-------+
7 rows in set (0.00 sec)
To understand it, try taking the following select apart:
select
COALESCE(
concat(
substring('y',1,
1-sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test '))))
),
substring('n',1,
sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test '))))
)
)
,'n')
as valid
from password;
TIP 19:
Order in the count:
create table a (a varchar(10));
insert into a values ('a'),('a'),('b'),('c');
Note
select a,count(a) as count from a group by a order by count;
+------+-------+
| a | count |
+------+-------+
| b | 1 |
| c | 1 |
| a | 2 |
+------+-------+
3 rows in set (0.00 sec)
but
See above name the function ------v
select a,count(a) from a group by a order by count(a);
ERROR 1111 (HY000): Invalid use of group function
TIP 20:
Minimum configure tip:
./configure --with-openssl --enable-thread-safe-client
The --with-openssl is very helpful for creating your own
password file. Also, if doing C API, having thread safe
calls "could" come in handly...it's what I use.
TIP 21:
Getting 1 matching row from an OUTER join
CREATE TABLE parent (
pkey int(11) NOT NULL auto_increment,
pname varchar(5),
timeEnter timestamp,
PRIMARY KEY (pkey)
);
CREATE TABLE child (
ckey int(11) NOT NULL auto_increment,
pkey int(11) NOT NULL,
cname varchar(5),
timeEnter timestamp,
PRIMARY KEY (ckey)
);
insert into parent(pname) values ('A');
select @a_lid:=last_insert_id();
insert into child(pkey,cname) values (@a_lid,'a1');
insert into child(pkey,cname) values (@a_lid,'a2');
insert into child(pkey,cname) values (@a_lid,'a3');
insert into parent(pname) values ('B');
select @a_lid:=last_insert_id();
insert into child(pkey,cname) values (@a_lid,'b1');
insert into child(pkey,cname) values (@a_lid,'b2');
insert into parent(pname) values ('C');
mysql> select p.*,c.cname,count(c.pkey) as number
from parent p left outer join child c on p.pkey=c.pkey
where c.pkey is not null group by c.pkey;
+------+-------+---------------------+-------+--------+
| pkey | pname | timeEnter | cname | number |
+------+-------+---------------------+-------+--------+
| 5 | A | 2004-04-28 09:56:59 | a1 | 3 |
| 6 | B | 2004-04-28 09:56:59 | b1 | 2 |
+------+-------+---------------------+-------+--------+
2 rows in set (0.01 sec)
For comparison, here is a listing of all the matching data:
mysql> select * from parent p left outer join child c on p.pkey=c.pkey
where c.pkey is not null;
select * from parent p left outer join child c on p.pkey=c.pkey where
c.pkey is not null;
+------+-------+---------------------+------+------+-------+----------------
-----+
| pkey | pname | timeEnter | ckey | pkey | cname | timeEnter
|
+------+-------+---------------------+------+------+-------+----------------
-----+
| 5 | A | 2004-04-28 09:56:59 | 7 | 5 | a1 | 2004-04-28
09:56:59 |
| 5 | A | 2004-04-28 09:56:59 | 8 | 5 | a2 | 2004-04-28
09:56:59 |
| 5 | A | 2004-04-28 09:56:59 | 9 | 5 | a3 | 2004-04-28
09:56:59 |
| 6 | B | 2004-04-28 09:56:59 | 10 | 6 | b1 | 2004-04-28
09:56:59 |
| 6 | B | 2004-04-28 09:56:59 | 11 | 6 | b2 | 2004-04-28
09:56:59 |
+------+-------+---------------------+------+------+-------+----------------
-----+
5 rows in set (0.00 sec)
mysql>
TIP 22:
Getting a virtual row count.
Assume the following table:
CREATE TABLE student (
s_id int(11) NOT NULL auto_increment,
name varchar(15),
PRIMARY KEY (s_id)
);
Add some rows and delete (just to convice you this works in all cases)
insert into student (name) values ('tom');
insert into student (name) values ('bob');
insert into student (name) values ('sue');
insert into student (name) values ('mike');
insert into student (name) values ('joe');
insert into student (name) values ('zoe');
insert into student (name) values ('harpo');
delete from student where name = 'bob';
Now, note mc is the row count...independent of s_id;
select a.name,sum(1) as mc
from student a, student b
where a.s_id <= b.s_id
group by a.s_id, a.name order by mc;
+-------+------+
| name | mc |
+-------+------+
| harpo | 1 |
| zoe | 2 |
| joe | 3 |
| mike | 4 |
| sue | 5 |
| tom | 6 |
+-------+------+
6 rows in set (0.00 sec)
mysql>
TIP 23:
Computing running and sliding aggregates. Assume the following table
and data:
CREATE TABLE dice (
d_id int(11) NOT NULL auto_increment,
roll int,
PRIMARY KEY (d_id)
);
insert into dice (roll) values (1);
insert into dice (roll) values (2);
insert into dice (roll) values (3);
insert into dice (roll) values (4);
insert into dice (roll) values (5);
insert into dice (roll) values (6);
The following gives a running sum:
mysql> select a.roll, sum(b.roll) from dice a, dice b
where b.d_id <= a.d_id group by a.d_id, a.roll;
+------+-------------+
| roll | sum(b.roll) |
+------+-------------+
| 1 | 1 |
| 2 | 3 |
| 3 | 6 |
| 4 | 10 |
| 5 | 15 |
| 6 | 21 |
+------+-------------+
6 rows in set (0.00 sec)
Note, it's also possible to get a running average by changing the
sum to avg as follows:
mysql> select a.roll, avg(b.roll) from dice a, dice b
where b.d_id <= a.d_id group by a.d_id, a.roll;
+------+-------------+
| roll | avg(b.roll) |
+------+-------------+
| 1 | 1.0000 |
| 2 | 1.5000 |
| 3 | 2.0000 |
| 4 | 2.5000 |
| 5 | 3.0000 |
| 6 | 3.5000 |
+------+-------------+
6 rows in set (0.00 sec)
TIP 24:
What commands were executed? Reading the log files.
The following is an example of the "show binlog events" which will show
all the inserted and deleted commands. See "TIP 2:" which shows how to
clean up or reset with the "reset master".
mysql> show binlog events;
show binlog events;
+------------------------+-----+------------+-----------+--------------+----
----------------------------------------+
| Log_name | Pos | Event_type | Server_id | Orig_log_pos |
Info |
+------------------------+-----+------------+-----------+--------------+----
----------------------------------------+
| third-fl-71-bin.000001 | 4 | Start | 1 | 4 |
Server ver: 4.1.1-alpha-log, Binlog ver: 3 |
| third-fl-71-bin.000001 | 79 | Query | 1 | 79 |
use `test`; create table j2 (a int) |
| third-fl-71-bin.000001 | 137 | Query | 1 | 137 |
use `test`; insert into j2 (a) values (3) |
| third-fl-71-bin.000001 | 201 | Query | 1 | 201 |
use `test`; insert into j2 (a) values (9) |
| third-fl-71-bin.000001 | 265 | Query | 1 | 265 |
use `test`; delete from j2 where a=3 |
+------------------------+-----+------------+-----------+--------------+----
----------------------------------------+
5 rows in set (0.00 sec)
Note that the logfiles by default are located in "/usr/local/var" (see
TIP 25:) or execute "mysql> show variables" and look
for the entry under the datadir. It's possible to query specific logfiles
if they exist:
mysql> show binlog events in 'third-fl-71-bin.000001';
It's also possible to specify the start position and limit the number of
records as follows:
mysql> show binlog events from 201 limit 2;
show binlog events from 201 limit 2;
+------------------------+-----+------------+-----------+--------------+----
---------------------------------------+
| Log_name | Pos | Event_type | Server_id | Orig_log_pos |
Info |
+------------------------+-----+------------+-----------+--------------+----
---------------------------------------+
| third-fl-71-bin.000001 | 201 | Query | 1 | 201 |
use `test`; insert into j2 (a) values (9) |
| third-fl-71-bin.000001 | 265 | Query | 1 | 265 |
use `test`; delete from j2 where a=3 |
+------------------------+-----+------------+-----------+--------------+----
---------------------------------------+
2 rows in set (0.00 sec)
Another option is writing a C api. For a complete example of this
(selectbinlog.c) download the following:
http://prdownloads.sourceforge.net/s...ar.gz?download
or take a peek at
http://souptonuts.sourceforge.net/co...tbinlog.c.html
This sample selects only the "Query" events.
TIP 25:
Setting the binary log file. This can be configured in /etc/my.cnf
[mysqld]
port = 3306
socket = /tmp/mysql.sock
log-bin = /usr/local/var/mysqlLOGb.log
or for the default hostname just log-bin
[mysqld]
port = 3306
socket = /tmp/mysql.sock
log-bin
IMPORTANT: The default my.cnf has a "log-bin" entry. Make sure it is not
listed twice.
It's also possible to set the text log file
[mysqld]
port = 3306
socket = /tmp/mysql.sock
log-bin = /usr/local/var/mysqlLOGb.log
log = /usr/local/var/mysqlLOG.log
Note, (see TIP 24:) the binary log file works with the "show binlog
events command" ,however, the
text logfile "/usr/local/var/mysqlLOG.log" shows which user was executing
commands.
TIP 26:
Free articles and tutorials on normalization.
a)
http://dev.mysql.com/tech-resources/...alization.html
b) http://www.phpbuilder.com/columns/ba...31.php3?page=1
c)
http://support.microsoft.com/default...NoWebContent=1
d) http://www.gslis.utexas.edu/~l384k11w/normstep.html
f) http://www.sims.berkeley.edu/academi.../f97/Lecture8/
g)
http://ocw.mit.edu/OcwWeb/Civil-and-...otes/index.htm
TIP 27:
Adding and Compiling a User-Defined function.
Here are some examples that can be compiled and used with MySQL 4.1. See
below for how to install.
First, just a taste of what they look like:
mysql> select lookup("www.yahoo.com");
select lookup("www.yahoo.com");
+-------------------------+
| lookup("www.yahoo.com") |
+-------------------------+
| 216.109.118.71 |
+-------------------------+
1 row in set (0.02 sec)
and
mysql> select reverse_lookup("216.109.118
select reverse_lookup("216.109.118.79");
+----------------------------------+
| reverse_lookup("216.109.118.79") |
+----------------------------------+
| p16.www.dcn.yahoo.com |
+----------------------------------+
1 row in set (0.02 sec)
Also
mysql> select sequence(3);
select sequence(3);
+-------------+
| sequence(3) |
+-------------+
| 4 |
+-------------+
1 row in set (0.01 sec)
mysql> select sequence(sequence(3));
select sequence(sequence(3));
+-----------------------+
| sequence(sequence(3)) |
+-----------------------+
| 5 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select sequence(3.4);
select sequence(3.4);
+---------------+
| sequence(3.4) |
+---------------+
| 4 |
+---------------+
1 row in set (0.00 sec)
An example of an aggregate function:
mysql> create table cost (inum int, cost double);
create table cost (inum int, cost double);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into cost (inum,cost) values (3,4.5);
insert into cost (inum,cost) values (3,4.5);
Query OK, 1 row affected (0.00 sec)
mysql> insert into cost (inum,cost) values (10,21.3);
insert into cost (inum,cost) values (10,21.3);
Query OK, 1 row affected (0.00 sec)
mysql> select avgcost(inum,cost) from cost;
select avgcost(inum,cost) from cost;
+--------------------+
| avgcost(inum,cost) |
+--------------------+
| 17.4231 |
+--------------------+
1 row in set (0.00 sec)
OK, HOW TO COMPILE:
For best results mysql-4.1.2-alpha or above configured with the following
option:
--with-mysqld-ldflags=-rdynamic
The full line of my configure is as follows since I use openssl as well.
STEP A:
../configure --with-openssl --enable-thread-safe-client --with-mysqld-ldflags
=-rdynamic
make
make install
/etc/init.d/mysql restart
STEP B:
Under "mysql-4.1.2-alpha/sql" issue the following command.
# make udf_example.so
STEP C:
As root copy the file to a directory where mysql can find it.
# cp udf_example.so /usr/lib/udf_example.so
STEP D:
Load mysql. You may want to look at udf_example.cc, since it as
instructions
as well. From here issue the following commands:
mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME
"udf_example.so";
mysql> CREATE FUNCTION myfunc_double RETURNS REAL SONAME
"udf_example.so";
mysql> CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME
"udf_example.so";
mysql> CREATE FUNCTION sequence RETURNS INTEGER SONAME
"udf_example.so";
mysql> CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";
mysql> CREATE FUNCTION reverse_lookup RETURNS STRING SONAME
"udf_example.so";
mysql> CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME
"udf_example.so";
And the functions can be deleted by the following:
mysql> DROP FUNCTION metaphon;
mysql> DROP FUNCTION myfunc_double;
mysql> DROP FUNCTION myfunc_int;
mysql> DROP FUNCTION lookup;
mysql> DROP FUNCTION reverse_lookup;
mysql> DROP FUNCTION avgcost;
That's it. Just run the functions above. These can serve as
templates for writing
your own.
LONG WINDED TIPS:
LONG WINDED TIP 1: (May want to skip, since it's a long example)
SQL Characteristic Functions: Do it without "if", "case", or
"GROUP_CONCAT". There's little
use for this now; but, it's fast and provides a lot of control.
The simple secret, and it's also why they work in almost all databases,
is the
following functions:
o sign (x) returns -1,0, +1 for values x < 0, x = 0, x > 0
respectively
o abs( sign( x) ) returns 0 if x = 0 else, 1 if x > 0 or x < 0
o 1-abs( sign( x) ) complement of the above, since this returns 1
only if x = 0
Quick example: sign(-1) = -1, abs( sign(-1) ) = 1, 1-abs(
sign(-1) ) = 0
Data for full example:
CREATE TABLE exams (
pkey int(11) NOT NULL auto_increment,
name varchar(15),
exam int,
score int,
PRIMARY KEY (pkey)
);
insert into exams (name,exam,score) values ('Bob',1,75);
insert into exams (name,exam,score) values ('Bob',2,77);
insert into exams (name,exam,score) values ('Bob',3,78);
insert into exams (name,exam,score) values ('Bob',4,80);
insert into exams (name,exam,score) values ('Sue',1,90);
insert into exams (name,exam,score) values ('Sue',2,97);
insert into exams (name,exam,score) values ('Sue',3,98);
insert into exams (name,exam,score) values ('Sue',4,99);
mysql> select * from exams;
+------+------+------+-------+
| pkey | name | exam | score |
+------+------+------+-------+
| 1 | Bob | 1 | 75 |
| 2 | Bob | 2 | 77 |
| 3 | Bob | 3 | 78 |
| 4 | Bob | 4 | 80 |
| 5 | Sue | 1 | 90 |
| 6 | Sue | 2 | 97 |
| 7 | Sue | 3 | 98 |
| 8 | Sue | 4 | 99 |
+------+------+------+-------+
8 rows in set (0.00 sec)
mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from exams group by name;
+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob | 75 | 77 | 78 | 80 |
| Sue | 90 | 97 | 98 | 99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)
Note, the above pivot table was created with one select statement
You may think IF's would be clean bug WATCH OUT!
Look what the following gives (INCORRECT !!):
mysql> select name,
if(exam=1,score,null) as exam1,
if(exam=2,score,null) as exam2,
if(exam=3,score,null) as exam3,
if(exam=4,score,null) as exam4
from exams group by name;
+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob | 75 | NULL | NULL | NULL |
| Sue | 90 | NULL | NULL | NULL |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)
mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
sum(score*(1-abs(sign(exam- 2)))) -
sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
sum(score*(1-abs(sign(exam- 3)))) -
sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
sum(score*(1-abs(sign(exam- 4)))) -
sum(score*(1-abs(sign(exam- 3)))) as delta_3_4
from exams group by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 |
delta_3_4 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 |
2 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 |
1 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
2 rows in set (0.00 sec)
Above delta_1_2 shows the difference between the first and second
exams, with the numbers
being positive because both Bob and Sue improved their score with each
exam. Calculating
the deltas here shows it's possible to compare two rows, not columns
which is easily done
with the standard SQL statements but rows in the original table.
mysql>select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
sum(score*(1-abs(sign(exam- 2)))) - sum(score*(1-abs(sign(exam-
1)))) as delta_1_2,
sum(score*(1-abs(sign(exam- 3)))) - sum(score*(1-abs(sign(exam-
2)))) as delta_2_3,
sum(score*(1-abs(sign(exam- 4)))) - sum(score*(1-abs(sign(exam-
3)))) as delta_3_4,
sum(score*(1-abs(sign(exam- 2)))) - sum(score*(1-abs(sign(exam-
1)))) +
sum(score*(1-abs(sign(exam- 3)))) - sum(score*(1-abs(sign(exam-
2)))) +
sum(score*(1-abs(sign(exam- 4)))) - sum(score*(1-abs(sign(exam-
3)))) as TotalIncPoints
from exams group by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+
----------------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 |
delta_3_4 | TotalIncPoints |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
----------------+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 |
2 | 5 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 |
1 | 9 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
----------------+
2 rows in set (0.00 sec)
TotalIncPoints shows the sum of the deltas.
select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
sum(score*(1-abs(sign(exam- 2)))) - sum(score*(1-abs(sign(exam-
1)))) as delta_1_2,
sum(score*(1-abs(sign(exam- 3)))) - sum(score*(1-abs(sign(exam-
2)))) as delta_2_3,
sum(score*(1-abs(sign(exam- 4)))) - sum(score*(1-abs(sign(exam-
3)))) as delta_3_4,
sum(score*(1-abs(sign(exam- 2)))) - sum(score*(1-abs(sign(exam-
1)))) +
sum(score*(1-abs(sign(exam- 3)))) - sum(score*(1-abs(sign(exam-
2)))) +
sum(score*(1-abs(sign(exam- 4)))) - sum(score*(1-abs(sign(exam-
3)))) as TotalIncPoints,
(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG
from exams group by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+
----------------+-------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 |
delta_3_4 | TotalIncPoints | AVG |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
----------------+-------+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 |
2 | 5 | 77.50 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 |
1 | 9 | 96.00 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
----------------+-------+
2 rows in set (0.00 sec)
It's possible to combine Total Increasing Point TotalIncPoints with
AVG. In fact, it's possible to combine
all of the example cuts of the data into one SQL statement, which
provides additional options for displaying
data on your page
select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))))/2 as AVG1_2,
(sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))))/2 as AVG2_3,
(sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/2 as AVG3_4,
(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG
from exams group by name;
+------+-------+-------+-------+-------+--------+--------+--------+-------+
| name | exam1 | exam2 | exam3 | exam4 | AVG1_2 | AVG2_3 | AVG3_4 | AVG
|
+------+-------+-------+-------+-------+--------+--------+--------+-------+
| Bob | 75 | 77 | 78 | 80 | 76.00 | 77.50 | 79.00 |
77.50 |
| Sue | 90 | 97 | 98 | 99 | 93.50 | 97.50 | 98.50 |
96.00 |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
2 rows in set (0.00 sec)
Exam scores are listing along with moving averages...again it's all
with one
select statement.
REFERENCES:
Good article on "Cross tabulations" or de-normalizing data to show stats:
http://dev.mysql.com/tech-resources/...t_version.html
MySQL performance tuning tips:
http://www.linux-mag.com/cgi-bin/pri...&article=mysql
See (TIP 26:) for links on normalization.
