MySQL Tips

Friday, January 02, 2009

PHPBB Convert poster_ip to real IP address in phpbb_posts table

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;

Friday, May 13, 2005

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

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;

Wednesday, April 27, 2005

How to show warning messages for LOAD DATA INFILE

Sunday, April 17, 2005

MySQL difference between dates in number of days

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

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

Alter table auto_increment examples

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

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

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

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

MySQL find duplicate repords example

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

MySQL benchmark (ran 2005-08-17)

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

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

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()

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 ;

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

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

[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

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

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