Chris Tate-Davies

An archive of helpful tit bits of information for development, and probably some stuff that is incomplete, wrong or boring…

MySQL Error in create table syntax ‘USING BTREE’

We had an odd thing today, whilst performing a apply-db-changes we received the error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘USING BTREE, KEY `index` (`fields`)’ at line 1 It worked fine before, so why won’t ant [...]

Resetting forgotten MySQL root password

If you have forgotten your root password for MySQL, then don’t panic. Although, if you don’t have root shell access, then do panic! Anyway, to reset a password, you need to stop MySQL and restart it with a special setting that allows you to login without passwords. Obviously this is a dangerous switch and you [...]

Changing your MySQL root password

When you installed MySQL, you would have been asked for a root password. If you were testing or something similar, you may have used an empty password. This is all well and good until you need to put your server into production. So, how do you create one? > mysqladmin -u root password NEWPASSWORD This [...]

MySQL Query Profiling

This inbuilt part of MySQL allows you to look closely at queries run – per session. And you can use this information to find bottlenecks. To enable profiling, run this command in the MySQL client: SET profiling = 1; This will turn on the profiling. Then for each query you run, MySQL will log all the [...]

How to use mysqldump

Using mysqldump to back up a database If you want to make a super quick backup of a MySQL database, the best tool for the job is the native mysqldump. Its also a command line utility, so you can use it from a telnet or SSH connection, etc… The basic command structure of mysqldump is: [...]

Zend_Db – Performing a LIKE search

When performing a simple SQL search using Zend_Db_Select, $select->where(‘name = ?’, ‘chris’); But what if you want to do a LIKE search? $select->where(‘name LIKE ?’, ‘chris%’); Simples.

Reading the MySQL binary log

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 [...]

MySQL – Order by certain value first

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 [...]

MySQL Incorrect String value “x/80″

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 [...]

MySQL REPLACE

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…

keep looking »
  • Chris Tate-Davies

    Hello there. This is my little "repository" on the world wide web. Its for nothing more than documenting things that I might need again in the future. You could describe it as an extension to my memory.

    Also an online collaboration of my thoughts through the day. I'll try to keep the real random stuff out and keep the blog on course.

    Thanks for stopping by... Hope you find what you're looking for...

  • Tags