mysql

Turn email addresses into fake addresses for testing databases

Snippet Yeah, kitt finished writing this at 16:42 on 12 October 2009

When you have live data in a testing database, you don't want to trigger emails that go to your users. In the case where you need the rest of the data, and need valid email addresses, append .example.com to turn current valid email addresses into syntactically valid email addresses, but not emailable addresses.

UPDATE `orders` SET email = CONCAT(email, '.example.com') WHERE email NOT LIKE '%example.com';
 
-- alternately, to undo this change, use replace
 
UPDATE `orders` SET email = REPLACE(email, '.example.com', '');

Show MySQL warnings after query

Snippet Written with a loving hand by kitt some time around 14:35 on 12 October 2009

When a query has run, a number of warnings may have occurred that didn't stop the query from running, but may be of concern. Use "SHOW WARNINGS [LIMIT n]" to display the warnings and see what might have gone wrong in the query.

mysql> SELECT name, addr1, addr2, city, state, FROM_UNIXTIME(created_on) AS created FROM `orders` WHERE ...;
Empty set, 20180 warnings (0.02 sec)
 
mysql> show warnings limit 10;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2009-08-21 00:01:01' | 
| Warning | 1292 | Truncated incorrect DOUBLE value: '2009-08-26 00:01:01' | 
| Warning | 1292 | Truncated incorrect DOUBLE value: '2009-08-21 00:01:01' | 
| Warning | 1292 | Truncated incorrect DOUBLE value: '2009-08-26 00:01:01' |

Various team event queries for rosters

Snippet kitt decided around 17:42 on 18 September 2009 to publish this:

Find teams that are at a UPA event in the rostering system

-- find all the teams at the club sectionals
 
select r.teamid, r.name, t.tournamentid, t.name from roster_tournament t, roster_team r where t.name like '2009C%CLUB%SECTION%' and t.tournamentid = r.tournamentid; 
 
-- teams with > 27 players on roster
 
select r.teamid, r.name, t.tournamentid, t.name, p.lastname, p.status from roster_tournament t, roster_team r, roster_player p  where t.name like '2009C%CLUB%SECTION%' and t.tournamentid = r.tournamentid and p.teamid = r.teamid and p.status != 'D' group by teamid having count(r.teamid) > 27;

Revoke Mysql privileges

Snippet Posted by kitt at 17:19 on 20 August 2009 
REVOKE GRANT OPTION, CREATE, ALTER, DROP, RELOAD, SHUTDOWN on db.* FROM dbuser@'%';

MySQL output query to file

Book page Written with a loving hand by kitt some time around 16:17 on 1 May 2008

Ways to output the results of a MySQL query to a file:

  1. Use the command line switch option -e to input the query and > to redirect the output to a file:
    % mysql -uuser -ppass -e "SELECT id, name FROM person WHERE name like '%smith%'" database > smiths.txt
  2. Use the SQL query construct INTO OUTFILE 'filename' to write the results to file from inside the MySQL command line interface:
    % mysql -uuser -ppass database
mysql> SELECT id, name INTO OUTFILE '/tmp/smiths.txt' FROM person WHERE name like '%smith%';
Query OK, 10 rows affected (0.01 sec)

    Be sure where you have write permissions where the output file will be written to. Specify the full path if in doubt.

MySQL clear query cache

Book page Posted by kitt at 23:30 on 11 February 2008

MySQL clears the query cache after any changes to the tables used in a cache query. Any of INSERT, UPDATE, DELETE, TRUNCATE, ALTER, DROP TABLE or DROP DATABASE potentially remove queries from the cache.

You can manually clear the query cache with RESET QUERY CACHE.

Return random records in MySQL

Interesting kitt decided around 16:11 on 29 December 2007 to publish this:

Works only for relatively small tables.


SELECT field1, field2, field3 FROM mytable WHERE status = 1 AND otherfield = 'somevalue' ORDER BY RAND();

One of many sources:
http://davidwalsh.name/return-random-records-mysql

Converting to MySQL TIMESTAMPs from int(11)

Update: Imported on 6 Oct 08 from http://kitt.hodsden.com/

For MySQL 5+

mysql> SELECT FROM_UNIXTIME(created) FROM node WHERE nid = 10; 
+------------------------+
| from_unixtime(created) |
+------------------------+
| 2005-05-22 10:22:04    | 
+------------------------+

For MySQL 4.1 and lower:

Having converted MySQL TIMESTAMPs to Drupal's int(11) style timestamps, which records the number of seconds since epoch (midnight, January 1st 1970), I also had cause to convert the other direction: from an int(11) to a MySQL TIMESTAMP.

MySQL provides a utility function to convert from unix timestamps (the int(11) format): FROM_UNIXTIME.

From the MySQL documentation:

FROM_UNIXTIME(unix_timestamp) , FROM_UNIXTIME(unix_timestamp,format)

Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. 

mysql> SELECT FROM_UNIXTIME(875996580);
        -> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
        -> 19971004222300

If format is given, the result is formatted according to the format string. format may contain the same specifiers as those listed in the entry for the DATE_FORMAT() function. 

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
    ->                      '%Y %D %M %h:%i:%s %x');
        -> '2003 6th August 06:22:58 2003'

Okay, so FROM_UNIXTIME can format the time/date in pretty much any way you'd like it, with the optional format argument.

Since we want the TIMESTAMP format, force MySQL to treat the date as an integer by adding 0 to it: 

mysql> SELECT FROM_UNIXTIME(creation_date) + 0 FROM paid_support LIMIT 1;
+----------------------------------+
| FROM_UNIXTIME(creation_date) + 0 |
+----------------------------------+
|                   20031102084514 |
+----------------------------------+
1 row in set (0.00 sec)

Pages