A weirdly named site with lots of boring content!

Misc MySQL Doobries

Cool stuff in MySQL includes some conditional functions. These can be very useful and all that. Here's a quick run down of usage.
compton, 28 September 07
Updated 8 April 13

Diagnosing MySQL Startup Failure


If MySQL is set to start up automatically on boot up, but doesn't appear to be running, first try to restart the MySQL daemon from the command line. On Arch linux, do this:
sudo rc.d restart mysqld
If it's not already running, you'll see a FAIL when it tries to shutdown, and if there's a permanent issue preventing it starting, you'll see a FAIL when it tries to start up again. Check the error log for more info about what happened:
sudo tail -25 /var/lib/mysql/hostname.err
Obviously replace hostname above with the name of the MySQL server.

InnoDB is notoriously fussy about its log files, and will prevent MySQL starting up if it cannot open the next expected logfile in the sequence. Check the error log for any suggestion that there was a problem opening the InnoDB log. This can happen if the file has become corrupted, or something prevented the file being created at the right time. Delete the /var/lib/mysql/mysql.index file and restart MySQL, and the index and log sequence should be restarted.

Extract Domain from a referrer Field

SELECT COUNT( * ) AS num, LEFT( referrer, LOCATE( '/', referrer, 9 ) ) AS ref FROM `participants` GROUP BY ref ORDER BY num DESC

Copy/Rename an Entire MySQL Database


The best way to do this is to first create the target database. If you use the MySQL client, issue the command CREATE DATABASE new_database;. Then run the following on the command line:
mysqldump -u root -p old_database | mysql -u root -p new_database
Once done, you can drop the old database if it's no longer needed.

Store IP Addresses in Native Format


In use, IP addresses are unsigned long integers, between 0 and 4294967295. They have a commonly recognised human-readable format of four dot-separated integers between 0 and 255 e.g. 201.55.11.9. For various reasons, including performance and compactness, when storing IP addresses in a database, it is preferable to store them as unsigned integers rather than character strings.

The PHP functions ip2long() and long2ip() are able to convert between the human-readable and the native formats, but on 32 bit systems they must be used in conjunction with sprintf() in order to get them as unsigned integers. For instance, say you have a human readable IP address in a variable called $ip, you could convert it to the unsigned long format with the following:
$nativeip = long2ip(sprintf('%u', $row['ip']));
This is a bit messy really, so you might be glad to know MySQL has had functions to perform the conversion since version 3.23.15. To convert a string format to the long format, use INET_ATON():
INSERT INTO ipAddresses SET ip=INET_ATON('201.55.11.9');
And to convert a long formatted IP to a human-readable one, use INET_NTOA():
SELECT INET_NTOA(ip) AS humanIp FROM ipAddresses;

Increase Maximum Size of a MySQL Database Table


By default, MyISAM tables are created with a four-byte datafile pointer. This creates an upper limit for the table of 4GB.

If you need to work with very large tables, you will want to change this. A five-byte datafile pointer will allow tables up to 1 terabyte (TB) in size. There is no apparent way to change the size of the datafile pointer directly, instead you need to tell MySQL the maximum number of rows you want the table to hold, and the average size of each row:
ALTER TABLE myMySQLTable max_rows=20000000 avg_row_length=250;
I don't believe these figures are used in any other context, but if you wish to make them better reflect your actual data, you can change the avg_row_length to what you calculate to be the average size of a row, and then alter the max_rows value to match. So if you halve avg_row_length, you would double max_rows and so on.

Version count by most recent


MySQL variables let you do a range of things. If a variable has not been defined already, then its value will be NULL. As NULL + number = NULL, you do need to initialise any variables before use. Sometimes you can do this in a single statement, other times you need a separate statement just for the initialisation. As long as the initialisation statement is in the same transaction as following statements which use those variables, they will work as expected.
$query = "SELECT IF( t.critical = 'Y', @version := @version +1, @version ) AS version, IF( t.critical = 'N', @subversion := @subversion +1, @subversion :=0) AS subversion, t.termsId, t.critical, CONCAT( DATE_FORMAT( t.lastUpdateDate, '%d/%m/%Y ' ) , DATE_FORMAT( t.lastUpdateTime, '%H:%i' ) ) AS updated, DATE_FORMAT( t.lastUpdateDate, '%D %b %Y' ) AS updatedLong, s.username FROM terms t INNER JOIN security_user s ON t.lastUpdatedUser=s.id ORDER BY version DESC, subversion DESC";   mysql_query("START TRANSACTION"); // A transaction is one way to initialise variables mysql_query("SET @version :=0, @subversion :=0"); $archive = mysql_query($query); mysql_query("COMMIT");
REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL that either inserts or updates see Section 12.2.4.3, INSERT ... ON DUPLICATE KEY UPDATE Syntax.

Get total rows of an item, the number of rows of that item meeting a particular criterion, and the percentage of the total that represents

SELECT COUNT(d.packageId) AS Total, SUM(IF(d.renewalMethod='auto',1,0)) AS `Auto Renew`, SUM(IF(d.renewalMethod='auto',1,0))/COUNT(d.packageId)*100 AS `Auto Renew %` FROM packages d WHERE d.purchaseDate>'2007-08-19' AND d.domainProductId>0;

Copy a table to a similar but not identical table


Say for instance you have one table like this:

idforeignKeyunixtimearbitaryFlags
int(11)int(11)int(14)int(4)

As you might guess, the unixtime column holds the time in Unix format (seconds since the first screening of Star Wars or something like that). The arbitaryFlags field has integer flags representing different situations, let's say 1 means 'Denied', 4 means 'Expired', and 8 means 'Success'. For various reasons, you decide to change the table format to the following:

idforeignKeynormalTimeactivity
int(11)int(11)datetimeenum('Success','Denied','Expired')


The obvious way might be to create a quick script file to read in the rows from the old table, and insert them one by one into the new table. However, we can use a single SQL statement instead:
INSERT INTO newTable (foreignKey, normalTime, activity) SELECT foreignKey, FROM_UNIXTIME(unixtime) as normalTime, CASE arbitaryFlags WHEN 1 THEN 2 WHEN 4 THEN 3 WHEN 8 THEN 1 END AS activity FROM oldTable;
Note the use of integer values in the CASE statement. This works becuase ENUM column values can be specified using their numerical index as given in the column definition - but be aware: the index values start at 1, not 0. Alternatively, it would be perfectly fine to use the string values in place of the numbers, which would work just as well and avoid the possibility of using the wrong numbers.

Insert a row only if a similar row does not already exist


You can't use WHERE clauses in INSERT statements, but you can combine an INSERT with a SELECT that has a WHERE clause. Combined with the special purpose dummy table DUAL, we can construct an INSERT IF NOT EXISTS type statement, that is not restricted to checking for duplicate keys and indexes:
INSERT INTO pollResponses (pollId, responseId, memberId, respondedOn) SELECT ".intval($pollId).", ".intval($responseId).", ".$_SESSION['memberId'].", NOW() FROM DUAL WHERE NOT EXISTS (SELECT pollResponseId FROM pollResponses WHERE pollId=".intval($pollId)." AND memberId=".$_SESSION['memberId']

Using the GROUP_CONCAT() MySQL Function



When grouping rows, different values are 'squashed' into one arbitary selected value from all in each group. For instance if you group a list of products on for example a category, you would get just one arbitrarily selected description returned. The GROUP_CONCAT() function is an extension to MySQL which allows you to get all the different values of any such 'squashed' column.

By default, it will get you all different values, each separated by a comma. You can use DISTINCT to remove duplicates from its result, and you can use ORDER BY to order the list ascending or descending. Additionally, there is a special keyword, SEPARATOR, which lets you change or remove the comma between values.

When grouping returned rows with a GROUP BY clause, this MySQL extension will give you a string containing all the values for certain columns concatenated into one. Any columns in expr can be preceded by DISTINCT in order to eliminate duplicate values. Each value is by default separated by a comma, but you can specify a separator of your choosing with the SEPARATOR keyword, eg SEPARATOR ' ' to separate each value by a space. You can also specify the order in which the different values should be listed with an ORDER BY clause. Here's an example:
SELECT COUNT(studentId), GROUP_CONCAT(studentName ORDER BY studentName SEPARATOR ':') FROM students GROUP BY courseCode
The maximum length of the concatenated values is by default limited to 1024 characters, although it can be changed by adjusting the group_concat_max_len system variable.

Create a CSV file from the results of a query



Simply append the following to the end of your query:
INTO OUTFILE '/tmp/report.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Creating the file in tmp is often simplest as that location is usually set to allow files to be created by just about any user.

Creating Random Password Strings



If you want to create a user with a random password, you can do it in MySQL:
INSERT INTO users SET password=CONCAT(CHAR(RAND()*26+65), CHAR(RAND()*26+65), CHAR(RAND()*26+65), CHAR(RAND()*26+65), CHAR(RAND()*26+65));
The above would create a random 5 letter password (consisting of random capital letters e.g. IHJDZ).

Deleting a Row and Shifting auto_index column

BEGIN; SET @delId := 1742205; DELETE FROM invoices WHERE invoiceId=@delId; DELETE FROM invoiceItems WHERE invoiceId=@delId; SELECT @moveId:=max(invoiceId) FROM invoices; UPDATE invoices SET invoiceId=@delId WHERE invoiceId=@moveId; UPDATE invoiceItems SET invoiceId=@delId WHERE invoiceId=@moveId; ALTER TABLE invoices AUTO_INCREMENT =@moveId; COMMIT;

Creating Test Data on Consecutive Weekdays

SET @days_ago = 80; INSERT INTO timeLog SELECT NULL, 191, NOW(), 191, DATE_SUB(CURRENT_DATE, INTERVAL (IF(DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL @days_ago DAY), '%w')=5, @days_ago := @days_ago-3, @days_ago := @days_ago-1)) DAY), 7, 30, newProjectId, 0, description, 0, 0, 'y' FROM timeLog WHERE userId=191 AND hours>=3 AND timeLogId<3575 LIMIT 60;

Grant for all Databases with Wildcards


Use backticks in combination with SQL wildcards when using GRANT:
GRANT ALL ON `%_tests`.* TO 'phpunit'@'localhost';