/kb

When exporting and importing from and to different MySQL databases with diferent version numbers this error message might show up:

#1064 – 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 ‘ENGINE=MyISAM DEFAULT CHARSET=utf8′

I got the message when exporting from a MySQL 5.x database and importing to a MySQL 4.x database. The solution came to me from the following blog post comment and is very easy. Just add the --compatible option:

mysqldump -u username -ppassword –compatible=mysql40 database_name > FILENAME.sql

GRANT ALL PRIVILEGES ON thedatabase.* TO 'theusername'@'localhost'
	IDENTIFIED BY 'thepassword' WITH GRANT OPTION;

UPDATE 20080916: Skip the last line if the user exists.

It’s very easy, but I always have to look it up.

mysqldump --opt -u USERNAME -p -h HOST.HOST.COM DATABASE_TABLE > DUMPFILE.sql

Join three tables (shows, bands, locations) in MySQL:

select * from shows 
left join (bands, locations) ON 
(bands.id = shows.band_id and locations.id = shows.location_id);

For further information see MySQL Reference manual 12.2.7.1. JOIN Syntax.