Misc MySQL Doobries

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: 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: 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

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: 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: 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(): And to convert a long formatted IP to a human-readable one, use INET_NTOA():

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: 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. 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

Copy a table to a similar but not identical table

Say for instance you have one table like this:

id	foreignKey	unixtime	arbitaryFlags
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:

id	foreignKey	normalTime	activity
int(11)	int(11)	datetime	enum('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: 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:

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: 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: 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: 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

Creating Test Data on Consecutive Weekdays

Grant for all Databases with Wildcards

Use backticks in combination with SQL wildcards when using GRANT: