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 »