Continued from MySQL tips and tricks:

General startup:

mysql -u [username] -p

mysql: USE db_name;

Note: Every SQL command needs to end with semicolon.

Config file edit: nano /etc/mysql/my.cnf to for instance turn off logging.

Remote connection one liner: mysql -h host -u username -pMyPassword dbname, note the lack of a space between -p and the password, that was not a typo.

– – – – – – –

Specifics:

Change password:

set password = password("newpassword");

Creating a user, note that if you want the credentials to apply when connecting from anywhere replace localhost with %:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

REVOKE FILE ON *.* FROM 'user'@'localhost';

FLUSH PRIVILEGES;

Getting most commented blog posts from JomSocial and ordering by the number of comments:

SELECT a.*, COUNT(c.contentid) AS count
FROM jos_content AS a LEFT JOIN jos_jomcomment AS c ON a.id = c.contentid
WHERE a.state = 1 AND a.catid = 1 $user_cond
GROUP BY a.id ORDER BY count DESC LIMIT 0, $limit

Note the GROUP BY clause, we needed it in order to be able to sort by count in a proper way. Here is another example of grabbing normal articles on the frontpage in Joomla and again counting the number of Jom Comments:

SELECT a.*, f.ordering, COUNT(c.contentid) AS comment_count
FROM jos_content_frontpage AS f
LEFT JOIN jos_content AS a ON f.content_id = a.id 
LEFT JOIN jos_jomcomment AS c ON a.id = c.contentid
WHERE a.state = 1
GROUP BY a.id ORDER BY a.created DESC LIMIT 0, $limit

Since it’s impossible to use something like SELECT DISTINCT * FROM table1, table2 WHERE table1.col1 LIKE ‘%table2.col1%’ due to the fact that the table2.col1 reference won’t expand, you could do something like this instead:

SELECT DISTINCT * FROM jos_users, jos_tag_img
WHERE LOCATE( jos_users.email, jos_tag_img.tag ) !=0

This is an unorthodox usage (at least for me) of a sub query. We want to fetch all bloggers and their last blog post, all at the same time. So we select it as c and order by created, ie date. Then we group by user id, the grouping will effectively remove all the superfluous content.

SELECT DISTINCT bu.title, bu.thumb, u.name, u.id, c.introtext, c.created, SUM( c.hits ) AS hits
FROM jos_myblog_user bu, jos_users u, (
    SELECT *
    FROM jos_content
    ORDER BY created DESC
) AS c
WHERE bu.featured =1
AND bu.user_id = u.id
AND c.created_by = u.id
AND c.sectionid =1
GROUP BY u.id
ORDER BY hits DESC

Note how we also need to add the AND c.created_by = u.id AND c.sectionid =1 in order to get the right posts, without them we would get the wrong content joined to the result. It’s a little bit contra intuitive that this is possible “after” the sub query should have been executed but I suppose it’s magic like this that the query optimizer is managing or something. I don’t really know, I’m just happy and surprised that the above query is giving me the correct records.

SELECT player.custId AS handle, SUM(activity.rakeCash) + SUM(activity.rakeStt) + SUM(activity.rakeMtt) AS rake, 
COALESCE(SUM(bonuses.paidBonus), 0) AS bonus
FROM gds_player player 
LEFT JOIN gds_player_poker_gaming_activity AS activity ON player.custId = activity.custId
LEFT JOIN gds_bonus_transactions AS bonuses ON (
  activity.date = bonuses.date 
  AND activity.custId = bonuses.custId 
  AND bonuses.bonusId != 2942
)
WHERE player.campaign = '$ref' 
AND activity.date <= '$end_date' 
AND activity.date >= '$start_date'
GROUP BY player.custId ORDER BY rake DESC

Another beast, note the multiple conditions in the JOIN ON clause there.

UPDATE `users` SET username = CONCAT(username, '_cl') 
WHERE username NOT REGEXP '^.*_cl$'

A good example of regular expressions in MySQL, the above will change all usernames that doesn’t end with “_cl” already to end with it.

GRANT ALL PRIVILEGES ON `database` . * TO 'user'@'localhost' WITH GRANT OPTION ;

Granting access to a database to a user.

ALTER TABLE users AUTO_INCREMENT = 2000000001;

Explicitly sets the auto increment value on a table.

UPDATE table SET value=(REPLACE (value, 'string to replace', 'replace with'));

Will do a string replace on the value column in table.

SELECT * FROM `table` WHERE FIND_IN_SET( 150, col )

The above works great if your table column is a text/varchar and contains comma separated values, in this case numbers, for instance 98,150,65,23 and so on.

This is PHP but is more of a MySQL problem so I put it here:

function nextAutoId($table){
	$status = $this->loadAssoc("SHOW TABLE STATUS LIKE '$table'");
	return $status['Auto_increment'];
}

The above will grab the next auto increment number from the result of the show table status command. This is the correct way of doing it, using MAX(id) might grab a different number than the actual id that the next inserted row will get.

ALTER TABLE table DROP FOREIGN KEY table_ibfk_1;

Some stupid programmers/DBAs like to use InnoDB with foreign constraints. To remove them you can simply do like above. Just switch table for your real table name. If there are two constraints on the table in question simply run the above query with the _1 at the end switched to _2 to get rid of both. To determine the names of the constraints you can run SHOW CREATE TABLE table.

SELECT table . * , count( col ) AS count FROM table GROUP BY col HAVING count( col ) >1 ORDER BY `count` DESC

The above will add the count of col to the result, as well as only selecting duplicate or more rows with regards to the value of col. Since you know that you will only get back duplicates you also know that the group by clause will have filtered one or more rows from each result.

UPDATE `table` SET col = LOWER( col );

The above makes all characters in a certain column lower case.

SELECT id
FROM wp_articles
WHERE created_at > '2011-06-30'
AND md5_content
IN (
  SELECT md5_content
  FROM wp_articles
  WHERE created_at < '2011-07-01'
)

The above will select all articles that are newer than June 30 if their hashed content exists in articles added before June 1.

This is a good and quick one for spotting duplicates based on COUNT(), note that HAVING clause, this one is crucial:

SELECT *, COUNT( * ) AS cnt
FROM wp_articles
GROUP BY md5_content
HAVING cnt > 1

Inner joining can also be a great way of finding at least duplicates:

SELECT t1 . *
FROM table t1
INNER JOIN table AS t2 ON t2.col1 = t1.col1 AND t2.col2 = t1.col2 AND t2.col3 != 0
WHERE t1.col3 = 0

Above we find all rows who have the same col1 and col2 with the third column different, eg:

col1 col2 col3
 1     1     0
 1     1     1

Enable remote hosts to connect (useful when for instance setting up replication):

GRANT ALL ON *.* to username@'1.1.1.1' IDENTIFIED BY 'a password';

Get all rows where the column in question does not contain any numbers and is longer than two characters:

SELECT DISTINCT * FROM table WHERE column NOT RLIKE '[0-9]' AND CHAR_LENGTH(column) > 2

A trick to avoid getting duplicate when joining is to do an explicit select in the join and group by the offending column (note that it can be considerably slower though):

SELECT DISTINCT * FROM table1 t1 
LEFT JOIN (SELECT * FROM table2 GROUP BY col1) AS t2 ON t1.ref1 = t2.col1

Setting and checking the maximum amount of connections:

SET GLOBAL max_connections = 2000;
SELECT @@global.max_connections;

If replication MySQL breaks and you want to skip the query that breaks replication:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

Creating a dababase with ut8 collation:

CREATE DATABASE `my-db` CHARACTER SET utf8 COLLATE utf8_general_ci;

Changing a password:

SET PASSWORD FOR 'user'@'%' = PASSWORD('abc');