Autarchy of the Private Cave

This query performs a table-wide search-and-repalce:

UPDATE `table_name` SET `table_field` = REPLACE(`table_field`,’string to search for and replace’,'replacement string’);

If you need a database-wide search-and-replace, you could try this script (I haven’t tested/used it myself).

Beware of the following gotchas:

1. wrong query syntax may ruin the field you are performing replace on, so always backup first!
2. be sure to provide “search-for” string as specific as possible, or you will get unexpected replacements (e.g. replacing mini with little will also convert all minivans into littlevans); also, do use WHERE clause when necessary to limit the number of rows modified
3. the function in the example is case-sensitive, so replacing all minivans with vehicles won’t replace Minivans. However, I believe there exists a case-insensitive version of REPLACE function

Source: George from vbulletin team.

Two most common reasons (and fixes) for the MySQL server has gone away (error 2006) are:

1. Server timed out and closed the connection. How to fix: check that wait_timeout variable in your mysqld’s my.cnf configuration file is large enough. On Debian: sudo nano /etc/mysql/my.cnf, set wait_timeout to at least 600 seconds (you can tweak/decrease this value when error 2006 is gone), then sudo /etc/init.d/mysql restart. I didn’t check, but the default value for wait_timeout might be around 28800 seconds (8 hours).
2. Server dropped an incorrect or too large packet. If mysqld gets a packet that is too large or incorrect, it assumes that something has gone wrong with the client and closes the connection. You can increase the maximal packet size limit by increasing the value of max_allowed_packet in my.cnf file. On Debian: sudo nano /etc/mysql/my.cnf, set max_allowed_packet to to something like 64M (you can tweak/decrease this value when error 2006 is gone), then sudo /etc/init.d/mysql restart.

If you get MySQL server has gone away (error 2006) when using MySQL ODBC driver – give this hint a try.

Must have been some maintenance, as I didn’t notice any changes in PHP/MySQL versions since the 7th of March.

Update: it seems as though since that short MySQL outage everything is faster at GoDaddy shared hosting. Did they upgrade database server(s)? I have no idea, but I like the change.

Juicy presentation, even for seasoned MySQL developers.

| View | Upload your own

You can also download the PDF of this presentation: Read the rest of this entry »

Most frequent use: convert database from latin1_swedish to utf8_general_ci.
Original script found at: MySQL and UTF-8.

Update: the original script had an error, it would generate queries likes this one (note the bold part):

ALTER TABLE `links` CHANGE `link_rel` `link_rel` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT NULL;

This is clearly wrong syntax (and logic). I fixed this by making comparison to NULL strict (with three equal signs instead of two):

// Does the field default to null, a string, or nothing?
if ($row['Default'] === NULL)

Update 2: based on comment by banesto, I modified the script; now it does not require specifying the from_collation, it’s sufficient to specify to_collation (which will be used for all the fields and tables). The modified code is:

if ($row['Collation'] == ” || $row['Collation'] == $convert_to)
continue;

Here’s the script itself: (to copy-paste: first click the “Plain text” header)
Read the rest of this entry »

To start: as of the latest MySQL, syntax presented in the title is not possible. But there are several very easy ways to accomplish what is expected using existing functionality.

There are 3 possible solutions: using INSERT IGNORE, REPLACE, or INSERT ... ON DUPLICATE KEY UPDATE.

Imagine we have a table:

Now imagine that we have an automatic pipeline importing transcripts meta-data from Ensembl, and that due to various reasons the pipeline might be broken at any step of execution. Thus, we need to ensure two things: 1) repeated executions of the pipeline will not destroy our database, and 2) repeated executions will not die due to 'duplicate primary key' errors.

Method 1: using REPLACE
Read the rest of this entry »