Posts tagged MySQL

Reading the MySQL binary log

Jul 27th

Posted by admin in IT

No comments

Having MySQL dumping out binary logs is a very good failsafe for backup redundancy. But, how do you get any of the information out of them?

If you know a time span that you need to investigate, then you can easily use the mysqlbinlog utility:

mysqlbinlog --start-datetime="2010-07-27 14:30:00" --stop-datetime="2010-07-27 15:00:00" > backup.txt

This will export all the recorded SQL Statements performed between 2:30pm and 3pm on the 27th July 2010 to the text file “backup.txt”. I can then browse at my convenience.

If you need to re-create any of the statements, you can copy and paste into your MySQL client of choice.

, , , ,

MySQL – Order by certain value first

Mar 23rd

Posted by admin in MySQL

3 comments

Ever wanted to sort a resultset of data, by value, but I wanted a couple of exceptions to appear at the top?

SELECT country, population,
CASE country
WHEN 'United Kingdom' THEN 0
WHEN 'United States' THEN 1
WHEN 'New Zealand" THEN 2
ELSE country
END AS countrySort
FROM countryList 
ORDER BY countrySort ASC;

This will sort the results by country, but with UK, US, NZ as the top three.

,

MySQL Incorrect String value “x/80″

Mar 18th

Posted by admin in MySQL

1 comment

MySQL Incorrect String value replication error.

We use ANT for releasing versions of our PHP applications. Its very smart and takes a lot of the problems related to releases out of the equation.

However, everytime we performed a release, our replication server would break, and I’d have to skip a load of database delta error messages with this command:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1

The error we received is:

Error 'Incorrect string value: 'x9CxF37x12[k...' for
column 'applied_by' at row 1' on query. Default database:
'<databasename>'.
Query: 'INSERT INTO changelog (change_number, delta_set, complete_dt,
applied_by, description)
VALUES (35, 'Main', CURRENT_TIMESTAMP, USER(),
'<database_delta_filename>')'.

I’d been getting these errors for a while.

Basically, the MySQL server was replicating the changelog table down to the slave, but for some reason it just would not accept the replicated query.

After a bit of scrummaging around, and changing field values/character sets, I discovered that in the string was “\x80″ which is a padding character as set out in http://en.wikipedia.org/wiki/ISO_8859-1 and shouldn’t be used in a string. I’m not really sure how it got there, or why, but I basically changed the “applied_by” field to be a BLOB – which is generically a VARCHAR with no character set and there fore ignores this.

,

MySQL REPLACE

Mar 18th

Posted by admin in MySQL

No comments

I’d not come across REPLACE as a MySQL function before. I’d always used a combination of concatination of LEFTs and RIGHTs and the occasional MID.

Unbelievable that I’d not found this function before…

SELECT REPLACE(fieldname, "look for", "replace with") FROM tablename;

So simple…

MySQL Group Concat

Mar 18th

Posted by admin in MySQL

No comments

What is MySQL’s GROUP_CONCAT function for?

How often have you wanted a comma separated list of values in a table of results. Say you had a list of trainers in makes, and sizes? And you want to display this list as a grid:

Make, Size

Adidas : 6, 7, 8, 10

Nike: 7, 8

Puma: 5, 7, 10, 12

Now, if these records are in one table, how are you going to get the sizes for each trainer?

You could loop through each group of trainers, and output a string, size by size. Or, you could use the GROUP_CONCAT function in MySQL, which will give you a list of the sizes as one field, even though they are multiple records.

SELECT make, 
GROUP_CONCAT(DISTINCT size ORDER BY size ASC SEPARATOR \", \" ) AS trainer_size
FROM trainers GROUP BY make ASC;

Then all you need is to loop through the 3 records, of each will contain 2 fields: make and trainer_size. Its a lot easier than writing code to loop through the DISTINCT makes and get the available sizes.

,
12»