MySQL Tips

PHPBB Convert poster_ip to real IP address in phpbb_posts table

2009-01-02T11:31:00.000-08:00

Let's say you see an offending user on your website and you have their IP and you'd like to see if they're a registered poster on your PHPBB forum. The problem is that the poster_ip field in the phpbb_posts table is encoded. Here's an example to convert it back and search for an offending IP, let's say in this case it's 255.255.255.255:

SELECT * from nuke_phpbb_posts where inet_ntoa(conv(poster_ip, 16, 10)) = '255.255.255.255';

SELECT u.username from nuke_phpbb_posts p, nuke_phpbb_users u where inet_ntoa(conv(p.poster_ip, 16, 10)) = '207.200.116.10' and p.poster_id = u.user_id limit 1;

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

2005-05-13T17:06:00.000-07:00

If in Mysql get the message "ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key" when trying to drop a primary key, then do the following:

(let's say the table is 'your_table' and the primary key column w/ auto_increment is 'the_column'):

alter table your_table change the_column the_column int unsigned;

to remove the auto_increment, then do a:

alter table your_table drop primary key;

How to show warning messages for LOAD DATA INFILE

2005-04-27T17:13:00.000-07:00

MySQL difference between dates in number of days

2005-04-17T04:09:00.000-07:00

To get a date difference in days in Mysql version before 4.1 (where you can use the datediff() function instead), do the following to calculate date difference:

select (TO_DAYS(date1)-TO_DAYS(date2))

MySQL get last 24 hours example SQL

2005-04-17T04:08:00.001-07:00

select count(*) as cnt from log where date >= DATE_SUB(CURDATE(),INTERVAL 1 DAY);

Alter table auto_increment examples

2005-04-17T04:08:00.000-07:00

ALTER TABLE tbl_name AUTO_INCREMENT = 100
will start your records at 100

ALTER TABLE tbl_name AUTO_INCREMENT = 1000
will start your records at 1000

Resolving ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key

2005-04-17T03:51:00.000-07:00

mysql> alter table test add orig_order int unsigned auto_increment;
ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key
mysql> alter table test add orig_order int unsigned auto_increment, add key(orig_order);
Query OK, 1221 rows affected (0.10 sec)
Records: 1221 Duplicates: 0 Warnings: 0

Export a table in mysql to a file in csv format example

2005-04-17T03:50:00.000-07:00

select * into outfile '/tmp/zips.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from zips;

MySQL find last digit = number example SQL

2005-04-17T03:49:00.001-07:00

select address from $db.$table
where ascii(right(address, 1)) >= 48 and ascii(right(address, 1)) <= 57

MySQL find duplicate repords example

2005-04-17T03:49:00.000-07:00

select address, count(address) as cnt
from mailing_list
group by address
having cnt > 1
order by cnt;

MySQL benchmark (ran 2005-08-17)

2005-04-17T03:47:00.000-07:00

MySQL Benchmark
Hardware: AMD Athlon64 3000+ 512MB software raid-0 on 2 WDD 120 gig HDD (partition RAID)
my.cnf as follows:

[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
set-variable = key_buffer_size=32M
set-variable = join_buffer_size=32M
set-variable = max_allowed_packet=2M
set-variable = table_cache=256K
set-variable = sort_buffer_size=24M
set-variable = net_buffer_length=256K
set-variable = myisam_sort_buffer_size=8M
set-variable = read_buffer_size=4M
set-variable = long_query_time=5
set-variable = thread_cache_size=1M
set-variable = read_rnd_buffer_size=8M
set-variable = tmp_table_size=256M
set-variable = record_buffer=1M
set-variable = log-slow-queries=/var/log/mysql_slow_queries.log
set-variable = long_query_time=3
set-variable = query_cache_type=1
set-variable = query_cache_size=32M
set-variable = query_cache_limit=4M
set-variable = max_connections=200
skip-innodb

[mysql]
prompt="\\d\\R\\m>"

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
#err-log=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

[root@localhost sql-bench]# perl run-all-tests
Benchmark DBD suite: 2.15
Date of test: 2004-08-17 14:04:30
Running tests on: Linux 2.6.7-1.478 x86_64
Arguments:
Comments:
Limits from:
Server version: MySQL 4.1.3 beta standard log
Optimization: None
Hardware:

alter-table:
Total time: 20 wallclock secs ( 0.04 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.06 CPU)
ATIS: Total time: 6 wallclock secs ( 4.16 usr 0.83 sys + 0.00 cusr 0.00 csys = 4.99 CPU)
big-tables: Total time: 7 wallclock secs ( 1.89 usr 1.58 sys + 0.00 cusr 0.00 csys = 3.47 CPU)
connect: Total time: 80 wallclock secs (23.86 usr 10.12 sys + 0.00 cusr 0.00 csys = 33.98 CPU)
create: Total time: 108 wallclock secs ( 1.21 usr 0.41 sys + 0.00 cusr 0.00 csys = 1.62 CPU)
insert:

Total time: 1277 wallclock secs (279.28 usr 49.60 sys + 0.00 cusr 0.00 csys = 328.88 CPU)
select: Total time: 56 wallclock secs (21.93 usr 3.56 sys + 0.00 cusr 0.00 csys = 25.49 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 4 wallclock secs ( 1.63 usr 0.43 sys + 0.00 cusr 0.00 csys = 2.06 CPU)

All 9 test executed successfully

Totals per operation:
Operation seconds usr sys cpu tests
alter_table_add 8.00 0.01 0.00 0.01 100
alter_table_drop 9.00 0.02 0.00 0.02 91
connect 5.00 2.63 0.52 3.15 10000
connect+select_1_row 8.00 3.69 0.75 4.44 10000
connect+select_simple 6.00 3.19 0.66 3.85 10000
count 7.00 0.03 0.01 0.04 100
count_distinct 0.00 0.08 0.01 0.09 1000
count_distinct_2 0.00 0.09 0.01 0.10 1000
count_distinct_big 5.00 3.75 0.75 4.50 120
count_distinct_group 1.00 0.48 0.08 0.56 1000
count_distinct_group_on_key 1.00 0.11 0.02 0.13 1000
count_distinct_group_on_key_parts 0.00 0.46 0.07 0.53 1000
count_distinct_key_prefix 0.00 0.09 0.02 0.11 1000
count_group_on_key_parts 0.00 0.45 0.08 0.53 1000
count_on_key 12.00 3.95 0.54 4.49 50100
create+drop 29.00 0.15 0.03 0.18 10000
create_MANY_tables 21.00 0.03 0.00 0.03 10000
create_index 2.00 0.00 0.00 0.00 8
create_key+drop 31.00 0.18 0.11 0.29 10000
create_table 0.00 0.00 0.00 0.00 31
delete_all_many_keys 32.00 0.01 0.00 0.01 1
delete_big 0.00 0.00 0.00 0.00 1
delete_big_many_keys 32.00 0.01 0.00 0.01 128
delete_key 1.00 0.15 0.09 0.24 10000
delete_range 3.00 0.00 0.00 0.00 12
drop_index 1.00 0.00 0.00 0.00 8
drop_table 0.00 0.00 0.00 0.00 28
drop_table_when_MANY_tables 3.00 0.16 0.09 0.25 10000
insert 31.00 5.14 2.84 7.98 350768
insert_duplicates 8.00 1.58 0.84 2.42 100000
insert_key 39.00 3.00 1.16 4.16 100000
insert_many_fields 3.00 0.21 0.06 0.27 2000
insert_select_1_key 2.00 0.00 0.00 0.00 1
insert_select_2_keys 3.00 0.00 0.00 0.00 1
min_max 2.00 0.02 0.00 0.02 60
min_max_on_key 17.00 7.10 0.99 8.09 85000
multiple_value_insert 2.00 0.20 0.03 0.23 100000
once_prepared_select 16.00 3.43 1.38 4.81 100000
order_by_big 14.00 10.84 2.38 13.22 10
order_by_big_key 16.00 11.15 2.41 13.56 10
order_by_big_key2 13.00 10.78 2.39 13.17 10
order_by_big_key_desc 16.00 11.22 2.44 13.66 10
order_by_big_key_diff 14.00 10.95 2.35 13.30 10
order_by_big_key_prefix 14.00 10.86 2.26 13.12 10
order_by_key2_diff 2.00 0.97 0.18 1.15 500
order_by_key_prefix 1.00 0.53 0.10 0.63 500
order_by_range 2.00 0.61 0.09 0.70 500
outer_join 1.00 0.00 0.00 0.00 10
outer_join_found 1.00 0.00 0.00 0.00 10
outer_join_not_found 1.00 0.00 0.00 0.00 500
outer_join_on_key 1.00 0.00 0.00 0.00 10
prepared_select 24.00 9.12 1.49 10.61 100000
select_1_row 10.00 1.78 1.34 3.12 100000
select_1_row_cache 4.00 1.36 1.23 2.59 100000
select_2_rows 12.00 1.81 1.46 3.27 100000
select_big 14.00 10.88 2.34 13.22 80
select_big_str 13.00 4.72 0.76 5.48 10000
select_cache 1.00 0.76 0.10 0.86 10000
select_cache2 27.00 1.85 0.16 2.01 10000
select_column+column 12.00 1.76 1.17 2.93 100000
select_diff_key 35.00 0.15 0.01 0.16 500
select_distinct 1.00 0.80 0.14 0.94 800
select_group 2.00 0.52 0.09 0.61 2911
select_group_when_MANY_tables 24.00 0.69 0.18 0.87 10000
select_join 0.00 0.24 0.04 0.28 100
select_key 146.00 27.12 3.51 30.63 200000
select_key2 164.00 28.05 3.45 31.50 200000
select_key2_return_key 160.00 27.43 3.08 30.51 200000
select_key2_return_prim 162.00 27.87 3.18 31.05 200000
select_key_prefix 80.00 23.80 3.15 26.95 200000
select_key_prefix_join 3.00 2.13 0.42 2.55 100
select_key_return_key 154.00 27.36 3.04 30.40 200000
select_many_fields 4.00 1.68 1.52 3.20 2000
select_range 7.00 4.22 0.85 5.07 410
select_range_key2 2.00 1.76 0.30 2.06 25010
select_range_prefix 2.00 1.78 0.33 2.11 25010
select_simple 5.00 1.53 1.11 2.64 100000
select_simple_cache 5.00 1.38 1.12 2.50 100000
select_simple_join 0.00 0.28 0.06 0.34 500
update_big 11.00 0.00 0.00 0.00 10
update_of_key 6.00 0.72 0.43 1.15 50000
update_of_key_big 5.00 0.01 0.01 0.02 501
update_of_primary_key_many_keys 18.00 0.01 0.01 0.02 256
update_with_key 24.00 4.00 2.59 6.59 300000
update_with_key_prefix 9.00 2.52 0.95 3.47 100000
wisc_benchmark 1.00 1.17 0.17 1.34 114
TOTALS 1583.00 329.57 65.53 395.10 3425950

Created_tmp_tables

2005-04-17T03:46:00.000-07:00

The number of in-memory temporary tables created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.

set-variable = tmp_table_size=128M

Chang the Mysql client prompt

2005-04-17T03:45:00.000-07:00

Changing the Mysql client prompt: (Mysql 4.0.2 and higher)

(from http://dev.mysql.com/doc/mysql/en/mysql_Commands.html)

e.g. "prompt \d\R\m>" will show the current database + millitary hour + minutes

Put in my.cnf

[mysql]
prompt="\\d\\R\\m>"

Faster way of getting random rows in mysql instead of using order by rand()

2005-04-17T03:44:00.000-07:00

1. create a new indexed column (= column_x) that's integer incremented for each table
2. select count(*) to get # of rows
3. use the app to generate X # of integers between 1 and last row
4. select * from table where column_x in (rand1, rand2, rand3, ...)

End Query with \G instead of ;

2005-04-17T03:42:00.000-07:00

You can use \G instead of ; for tables with lots of fields.

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_queries_in_cache | 6990 |
| Qcache_inserts | 6990 |
| Qcache_hits | 3987 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 621 |
| Qcache_free_memory | 176329432 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 13998 |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Qcache%' \G
*************************** 1. row ***************************
Variable_name: Qcache_queries_in_cache
Value: 7902
*************************** 2. row ***************************
Variable_name: Qcache_inserts
Value: 7902
*************************** 3. row ***************************
Variable_name: Qcache_hits
Value: 6456
*************************** 4. row ***************************
Variable_name: Qcache_lowmem_prunes
Value: 0
*************************** 5. row ***************************
Variable_name: Qcache_not_cached
Value: 3887
*************************** 6. row ***************************
Variable_name: Qcache_free_memory
Value: 172986416
*************************** 7. row ***************************
Variable_name: Qcache_free_blocks
Value: 1
*************************** 8. row ***************************
Variable_name: Qcache_total_blocks
Value: 15828
8 rows in set (0.00 sec)

Gauging MySQL performance- examples

2005-04-17T03:41:00.000-07:00

mysql> show variables like '%query_cache%';
+-------------------+-----------+
| Variable_name | Value |
+-------------------+-----------+
| have_query_cache | YES |
| query_cache_limit | 4194304 |
| query_cache_size | 167772160 |
| query_cache_type | ON |
+-------------------+-----------+
4 rows in set (0.01 sec)

mysql> show variables like '%query_cache%';
+-------------------+-----------+
| Variable_name | Value |
+-------------------+-----------+
| have_query_cache | YES |
| query_cache_limit | 4194304 |
| query_cache_size | 167772160 |
| query_cache_type | ON |
+-------------------+-----------+
4 rows in set (0.01 sec)

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_queries_in_cache | 2046 |
| Qcache_inserts | 2046 |
| Qcache_hits | 2385 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1619 |
| Qcache_free_memory | 161661632 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 4111 |
+-------------------------+-----------+
8 rows in set (0.00 sec)

Example my.cnf for 4.0.X

2005-04-17T03:40:00.000-07:00

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = key_buffer_size=160M
set-variable = join_buffer_size=64M
set-variable = max_allowed_packet=8M
set-variable = table_cache=256K
set-variable = sort_buffer_size=128M
set-variable = net_buffer_length=256K
set-variable = myisam_sort_buffer_size=128M
set-variable = read_buffer_size=8M
set-variable = long_query_time=5
set-variable = thread_cache_size=1M
set-variable = query_cache_size=8M
set-variable = read_rnd_buffer_size=32M
set-variable = tmp_table_size=64M
set-variable = record_buffer=4M
set-variable = log-slow-queries=/var/log/mysql_slow_queries.log
set-variable = long_query_time=3
set-variable = query_cache_type=1
set-variable = query_cache_size=256M
set-variable = query_cache_limit=4M
skip-innodb

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

MySQL date calculations

2005-04-17T03:38:00.000-07:00

e.g. 2 days ago till today:

select domain, count(*) as cnt from referrers where date >= current_date()-2 group by domain order by cnt;

Simple Date Calculations
Date calculations are relatively easy. The first function we're going to look at is the YEAR() function, which returns a year from a given date. For example:

mysql> SELECT YEAR('2003-03-31');
+--------------------+
| YEAR('2003-03-31') |
+--------------------+
| 2003 |
+--------------------+

We can perform simple arithmetic on a date using the '+' and '-' operators. For example, to find out which year is five years ahead of a given date, you can use:
mysql> SELECT YEAR('2003-03-31')+5;
+----------------------+
| YEAR('2003-03-31')+5 |
+----------------------+
| 2008 |
+----------------------+

And to find out which year was five years in the past:
mysql> SELECT YEAR('2003-03-31')-5;
+----------------------+
| YEAR('2003-03-31')-5 |
+----------------------+
| 1998 |
+----------------------+

Of course you don't have to hard-code the date. MySQL is quite capable of telling the date and time, using the NOW() function:
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2003-03-31 00:32:21 |
+---------------------+

or just the date with the CURRENT_DATE() function:
mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2003-03-31 |
+----------------+

There are also functions for the other date and time intervals; MONTH(), DAYOFMONTH(), HOUR(), MINUTE() and SECOND(). For example:
mysql> SELECT MONTH(NOW()) AS m,
DAYOFMONTH(NOW()) AS d,
HOUR(NOW()) AS h,
MINUTE(NOW()) AS m,
SECOND(NOW()) AS s;
+------+------+------+------+------+
| m | d | h | m | s |
+------+------+------+------+------+
| 3 | 31 | 1 | 53 | 38 |
+------+------+------+------+------+

The DAYOFMONTH() function is an exception to the naming conventions because there are a number of other ways to return the day. DAYOFMONTH() returns the day as a numeric from 1 to 31, but there is also DAYNAME() which returns the actual name of the day, DAYOFWEEK() which returns a number from 1 (Sunday) to 7(Saturday) and DAYOFYEAR() returning a number from 1 to 366. Some examples:

mysql> SELECT DAYNAME('2000-01-01');
+-----------------------+
| DAYNAME('2000-01-01') |
+-----------------------+
| Saturday |
+-----------------------+

mysql> SELECT DAYOFWEEK('2000-01-01');
+-------------------------+
| DAYOFWEEK('2000-01-01') |
+-------------------------+
| 7 |
+-------------------------+

mysql> SELECT DAYOFYEAR('2000-12-31');
+-------------------------+
| DAYOFYEAR('2000-12-31') |
+-------------------------+
| 366 |
+-------------------------+

One of the most commonly performed date calculations is one which calculates age. Unfortunately there isn't a function to do it, so you need to do a bit of thinking. Let's begin with doing some simple arithmetic - you can see when I'm running these queries from the earlier results (31 March 2003). First, we'll simply subtract the year of birth from the current year. If you're reading this article well after I wrote it, of course your results may be different:
mysql> SELECT YEAR(CURRENT_DATE)-YEAR('1971-01-01');
+---------------------------------------+
| YEAR(CURRENT_DATE)-YEAR('1971-01-01') |
+---------------------------------------+
| 32 |
+---------------------------------------+

If you were using this to return the age, the result would be correct. Someone born on the 1st of January 1971 would be 32 years old on the 31st of March 2003. But let's test for someone born on the 31st of December:
mysql> SELECT YEAR(CURRENT_DATE)-YEAR('1971-12-31');
+---------------------------------------+
| YEAR(CURRENT_DATE)-YEAR('1971-12-31') |
+---------------------------------------+
| 32 |
+---------------------------------------+

Here the age is wrong. This person would not have turned 32 yet. The problem is that the calculation above only checks the year part of the date. It comes down to subtracting 1971 from 2003, regardless of the month and day. The best way to do this is to compare whether the current month and day are larger than the birth month and day. If it is, a full year has passed, and the year portion of the calculation can be left. If it isn't, a full year hasn't passed, and you need to subtract one from the year portion. It sounds tricky, but it isn't really. Let's break it down slowly. It helps us greatly that MySQL evaluates a true expression to 1, and a false expression to 0. For example:
mysql> SELECT 23>19;
+-------+
| 23>19 |
+-------+
| 1 |
+-------+

mysql> SELECT 23<19;> SELECT RIGHT('abcdef',2);
+-------------------+
| RIGHT('abcdef',2) |
+-------------------+
| ef |
+-------------------+

This returns 2 characters, starting from the right of the string, thus 'ef'. If you ask for more characters than the string is long, MySQL simply returns the whole string:
mysql> SELECT RIGHT('abcdef',9);
+-------------------+
| RIGHT('abcdef',9) |
+-------------------+
| abcdef |
+-------------------+

So, to return the 'MM-DD' portion of a date, you need to return the five rightmost characters, for example:
mysql> SELECT RIGHT(CURRENT_DATE(),5);
+-------------------------+
| RIGHT(CURRENT_DATE(),5) |
+-------------------------+
| 03-31 |
+-------------------------+

Now we have everything we need to calculate an age. The query will have a portion that calculates the difference in years, and then we'll subtract either 0 or 1, depending on the month-day portion. Here is the full query:
mysql> SELECT YEAR(CURRENT_DATE()) - YEAR('1971-12-31')
- (RIGHT(CURRENT_DATE(),5)<'12-31') AS age;
+------+
| age |
+------+
| 31 |
+------+

MySQL Explain example

2005-04-17T03:37:00.000-07:00

EXPLAIN SELECT employee_number,firstname,surname FROM employee WHERE employee_number= '10875';

+----------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+------+---------------+------+---------+------+------+------------+
| employee | ALL | NULL | NULL | NULL | NULL | 2 | where used |
+----------+------+---------------+------+---------+------+------+------------+

So what are all these things?

-table shows us which table the output is about (for when you join many tables in the query)
-type is an important one - it tells us which type of join is being used. From best to worst the types are: system, const, eq_ref, ref, range, index, all
-possible_keys Shows which possible indexes apply to this table
-key And which one is actually used
-key_len give us the length of the key used. The shorter that better.
-ref Tells us which column, or a constant, is used
-rows Number of rows mysql believes it must examine to get the data
extra Extra info - the bad ones to see here are "using temporary" and "using filesort"

Some built-in MySQL functions

2005-04-17T03:34:00.000-07:00

Problem in Mysql with equality comparison with float

2005-04-17T03:33:00.000-07:00

There is a general problem when testing a floating point field for equality. The test (field=14.40) doesn't work because field is not exactly 14.40, but it could be 13.9999 or 14.0001. Use this test instead: (field between 13.9999 and 14.0001). Moreover, as a general rule, if you want precise results, don't use float, but decimal, and test by ranges (between y and z), instead of exact values (= x).

Do a join on the same table in MySQL

2005-04-17T03:31:00.000-07:00

You can join the same table in mysql!

mysql> select mv2.name, mv1.quantity
-> from dw.MAILVRS mv1, dw.MAILVRS mv2
-> where mv1.version_recnum = 124475 and mv1.package_recnum = mv2.dataflex_recnum_one;

+---------+----------+
| name | quantity |
+---------+----------+
| Kit A | 25000 |
| Control | 50000 |
| Kit E | 25000 |
+---------+----------+

MySQL outer join SQL tutorial/examples

2005-04-17T03:29:00.000-07:00

First of all, outer joins are for instances where info is being looked up on another table and you want rows returned even though nothing was matched for a particular id, e.g.:

create table food (
id int unsigned primary key auto_increment,
food char(16),
yummy char(1),
animal char(16)
);

create table animals (
name char(16) primary key,
weight int unsigned
);

insert into food values
(NULL, 'apple', 'Y', 'human'),
(NULL, 'oranges', 'Y', 'human'),
(NULL, 'apple', 'N', 'dog'),
(NULL, 'oranges', 'N', 'dog'),
(NULL, 'apple', 'N', 'cat'),
(NULL, 'oranges', 'N', 'cat');

insert into animals values
('cat', 12),
('dog', 25);

table 'food'

id food yummy animal
--- ---- ----- ------
1 apple Y human
2 oranges Y human
3 apple N dog
4 oranges N dog
5 apple N cat
6 oranges N cat

table 'animals'

name weight
---- ------
Cat 12
Dog 25

Let's say you want a list of all the foods and the weight of the animals.

If you do:

select f.*, a.weight
from food f, animals a
where f.animal = a.name

In this case the result would be:

+----+---------+-------+--------+--------+
| id | food | yummy | animal | weight |
+----+---------+-------+--------+--------+
| 5 | apple | N | cat | 12 |
| 6 | oranges | N | cat | 12 |
| 3 | apple | N | dog | 25 |
| 4 | oranges | N | dog | 25 |
+----+---------+-------+--------+--------+

Notice that humans is missing, but if you want the humans to show up even
though there's no human entry in the 'animals' table you have to do an
outer join (aka LEFT [OUTER] JOIN in Mysql):

select f.*, a.weight
from food f
LEFT JOIN animals a on (f.animal=a.name)

In this case the result would be:

+----+---------+-------+--------+--------+
| id | food | yummy | animal | weight |
+----+---------+-------+--------+--------+
| 1 | apple | Y | human | NULL |
| 2 | oranges | Y | human | NULL |
| 3 | apple | N | dog | 25 |
| 4 | oranges | N | dog | 25 |
| 5 | apple | N | cat | 12 |
| 6 | oranges | N | cat | 12 |
+----+---------+-------+--------+--------+

Here are some more examples:

SELECT T.name, V.value, T.unit, T.id, A.Product_id
FROM attribute_type AS T
LEFT JOIN attribute AS A ON ( T.id = A.type_id AND A.Product_id = 21 )
LEFT JOIN attribute_value AS V ON ( A.value_id = V.id );

select j.id, j.job_id, d.NAMES, e.DETAILS1, j.file_name, j.job_owner, j.import_date
from db.summary_jobs j
left join db.EST e on (j.job_id=e.JOB_NUMBER)
left join db.DEB d on (e.DEBTOR=d.AC_NO)
order by j.job_id desc limit

How to alter mysql table column to AUTO_INCREMENT

2005-04-17T03:28:00.000-07:00

mysql> set insert_id=7;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table fixtures modify column id int auto_increment;
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings

MySQL INSERT SELECT example

2005-04-17T03:27:00.000-07:00

insert into user (id, name, email, passwd, ref, joindate) select idm, user_name, email, pass, refferal, sysdate() from db.members;

Export from mysql into fixed length file

2005-04-17T03:25:00.000-07:00

SELECT * INTO OUTFILE file_name
FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY "\n"
FROM my_table;

Mysql Search and Replace example

2005-04-17T03:24:00.000-07:00

Doing search and replace in mysql, e.g.:

update tablename set field = replace(field,'search_for_this','replace_with_this');

Show Processlist

2005-04-17T03:22:00.000-07:00

In mysql do a "show processlist" to see what queries are running.

MySQL slow queries log

2005-04-17T03:21:00.000-07:00

start safe_mysqld like this to catch slow queries:

./bin/safe_mysqld --log-slow-queries &

OR

To use it, you need to add --log-slow-queries to your mysqld startup.

I added it to my /etc/rc.d/init.d/mysql file like so:

$bindir/safe_mysqld --user=$mysql_daemon_user --datadir=$datadir --pid-file=$pid_file --log=$log_file --log-slow-queries &

It does appear to put stuff into a different place, namely:

/var/lib/mysql/hostname-slow.log

MySQL replication instructions

2005-04-17T03:20:00.000-07:00

As of version 3.23.15 (try to use 3.23.29 or later), MySQL supports one-way replication. Since most web applications usually have more reads than writes, an architecture which distributes reads across multiple servers can be very beneficial.

In typical MySQL fashion, setting up replication is trivial. On your master server add this to your my.cnf file:

[mysqld]
log-bin
server-id=1

And add a replication user id for slaves to log in as:

GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY 'foobar';

Then on your slave servers:

[mysqld]
set-variable = max_connections=200
log-bin
master-host=192.168.0.1
master-user=repl
master-password=foobar
master-port=3306
server-id=2

Make sure each slave has its own unique server-id. And since these will be read-only slaves, you can start them with these options to speed them up a bit:

--skip-bdb --low-priority-updates --delay-key-write-for-all-tables

Stop your master server. Copy the table files to each of your slave servers. Restart the master, then start all the slaves. And you are done. Combining MySQL replication with a Squid reverse cache and redirector and you might have an architecture like this:

You would then write your application to send all database writes to the master server and all reads to the local slave. It is also possible to set up two-way replication, but you would need to supply your own application-level logic to maintain atomicity of distributed writes. And you lose a lot of the advantages of this architecture if you do this as the writes would have to go to all the slaves anyway.

MySQL SELECT CASE example

2005-04-17T03:19:00.000-07:00

select
CASE month when "01" then "January"
when "02" then "February"
when "03" then "March"
when "04" then "April"
when "05" then "May"
when "06" then "June"
when "07" then "July"
when "08" then "August"
when "09" then "September"
when "10" then "October"
when "11" then "November"
when "12" then "December"
END
from calendar where year = "2005" order by month

Transactions example

2005-04-17T03:17:00.000-07:00

Transactions are used to make ensure that a series of statements either all get processed or don't get processed at all -- basically all or nothing. Why is this important? Let's consider a simple banking example. You transfer $500 from one bank account into another:

1. - $500 from account A
2. + $500 to account B

Step 1 processes successfully, but something bad happens between step 1 and 2. Where is your money now -- you just lost $500!! This is clearly not acceptable, we need to be able to confidently say that steps 1 and 2 must all process successfully, or none of it can process at all.

Enter the concept of transactions. With transactions, we would be able to do this:

1. BEGIN TRANSACTION
2. - $500 from account A
3. + $500 to account B
4. END TRANSACTION

Now steps 1 to 4 are treated as one atomic operation. If anything fails between steps 1 to 4, the entire operation is aborted and we revert back to the way things were before we started. The worst that can happen here is that the money doesn't get transferred, but at least you will not lose $500.

Setup New Users in MySQL

2005-04-17T03:15:00.000-07:00

The examples below show how to use the mysql client to set up new users. These examples assume that privileges are set up according to the defaults described in the previous section. This means that to make changes, you must be on the same machine where mysqld is running, you must connect as the MySQL root user, and the root user must have the insert privilege for the mysql database and the reload administrative privilege. Also, if you have changed the root user password, you must specify it for the mysql commands below.

You can add new users by issuing GRANT statements:

shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;

These GRANT statements set up three new users:

monty
A full superuser who can connect to the server from anywhere, but who must use a password 'some_pass' to do so. Note that we must issue GRANT statements for both monty@localhost and monty@"%". If we don't add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence when we connect from the local host, because it has a more specific Host field value and thus comes earlier in the user table sort order.
admin
A user who can connect from localhost without a password and who is granted the reload and process administrative privileges. This allows the user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin processlist . No database-related privileges are granted. (They can be granted later by issuing additional GRANT statements.)
dummy
A user who can connect without a password, but only from the local host. The global privileges are all set to 'N' -- the USAGE privilege type allows you to create a user with no privileges. It is assumed that you will grant database-specific privileges later.
You can also add the same user access information directly by issuing INSERT statements and then telling the server to reload the grant tables:

shell> mysql --user=root mysql
mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user VALUES('%','monty',PASSWORD('some_pass'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin',
Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;

Depending on your MySQL version, you may have to use a different number of 'Y' values above (versions prior to Version 3.22.11 had fewer privilege columns). For the admin user, the more readable extended INSERT syntax that is available starting with Version 3.22.11 is used.

Note that to set up a superuser, you need only create a user table entry with the privilege fields set to 'Y'. No db or host table entries are necessary.

The privilege columns in the user table were not set explicitly in the last INSERT statement (for the dummy user), so those columns are assigned the default value of 'N'. This is the same thing that GRANT USAGE does.

The following example adds a user custom who can connect from hosts localhost, server.domain, and whitehouse.gov. He wants to access the bankaccount database only from localhost, the expenses database only from whitehouse.gov, and the customer database from all three hosts. He wants to use the password stupid from all three hosts.

To set up this user's privileges using GRANT statements, run these commands:

shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON bankaccount.*
TO custom@localhost
IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON expenses.*
TO custom@whitehouse.gov
IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON customer.*
TO custom@'%'
IDENTIFIED BY 'stupid';

The reason that we do to grant statements for the user 'custom' is that we want the give the user access to MySQL both from the local machine with Unix sockets and from the remote machine 'whitehouse.gov' over TCP/IP.

To set up the user's privileges by modifying the grant tables directly, run these commands (note the FLUSH PRIVILEGES at the end):

shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
VALUES('localhost','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
VALUES('server.domain','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES
('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES
('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;

The first three INSERT statements add user table entries that allow user custom to connect from the various hosts with the given password, but grant no permissions to him (all privileges are set to the default value of 'N'). The next three INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, and customer databases, but only when accessed from the proper hosts. As usual, when the grant tables are modified directly, the server must be told to reload them (with FLUSH PRIVILEGES) so that the privilege changes take effect.

If you want to give a specific user access from any machine in a given domain, you can issue a GRANT statement like the following:

mysql> GRANT ...
ON *.*
TO myusername@"%.mydomainname.com"
IDENTIFIED BY 'mypassword';

To do the same thing by modifying the grant tables directly, do this:

mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername',
PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;

You can also use xmysqladmin, mysql_webadmin, and even xmysql to insert, change, and update values in the grant tables. You can find these utilities in the Contrib directory of the MySQL Website.

LOAD DATA INFILE example - importing data from csv into mysql

2005-04-17T03:12:00.000-07:00

LOAD DATA INFILE "/home/mysql/data/my_table.csv" INTO TABLE my_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Installing MySQL

2005-04-17T03:11:00.000-07:00

The basic commands you must execute to install a *MySQL* source
distribution are:

shell> groupadd mysql
shell> useradd -g mysql mysql
shell> gunzip < mysql-VERSION.tar.gz | tar -xvf -
shell> cd mysql-VERSION
shell> ./configure --prefix=/usr/local/mysql
shell> make
shell> make install
shell> scripts/mysql_install_db
shell> chown -R root /usr/local/mysql
shell> chown -R mysql /usr/local/mysql/var
shell> chgrp -R mysql /usr/local/mysql
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> /usr/local/mysql/bin/safe_mysqld --user=mysql &

To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
This is done with:
./bin/mysqladmin -u root -p password 'new-password' (note that new-password *IS* the new root password you wanna assign, and initially there is *NO* password for mysql so when it asks for password: just hit ENTER)
./bin/mysqladmin -u root -h server-name -p password 'new-password'