Using tables with dashes in MySQL queries


You have some databases in MySQL that contain dashes in their names, and when you try to use them (ie grant access to them), you get the following error:


To be able to use them, enclose the database name in backticks (`) like:

Find out the tables storage engine in your MySQL database


You would like to check the storage engine in a MySQL database.


Use the following query after logging in to the server:

Taken from here

rails `require’: cannot load such file — mysql2/mysql2 (LoadError)


You want to use a different ruby version from the one you have initially installed and build your application with, but when you change it in your rvm installation you get the following error:


To make it work again you will have to uninstall the mysql2 gem and install it again in the new ruby version with the option –platform=ruby.

Exporting query results from MySQL


You would like to export some query results from MySQL into a format that can be imported in LibreOffice, OpenOffice or Excel.


Try first with the default export options, as by doing a google search, there are a lot of different options for specifying field delimeters, lines terminated, etc.
So by not specifying any additional options the export is a tab delimited, linefeed-terminated file which should work in most of the programs.
So in order to do that you would need a folder in the MySQL server that should have write access. Usually /tmp should do. Then you can build your query and at the end add the INTO OUTFILE ‘/path’, like:

You can use any file ending but usually by specifying .csv the program that you will try to open will understand it.

Enabling MySQL server to be remotely accessible in Amazon EC2


You want to make your instance’s mysql server in Amazon available remotely, and you have set the firewall setting in your security group to allow access to port 3306, but you get the following error:


You would need to edit your /etc/mysql/my.cnf file to allow access to the mysql server by changing the line:


Make sure that the IP used is the private ip and not the Elastic IP.
You should be able to find that in your instance control panel.

Adding timezone information in MySQL

You would like to convert times in MySQL with the CONVERT_TZ function and to be able to use timezone names (ie CE).


  1. Find out if your MySQL includes timezone information as it is not included by default. Log in and run : select count(*) from mysql.time_zone_name;
  2. If the result is 0 then the timezone information is not included
  3. Log out from your MySQL server on the command prompt and upgrade your server with: mysql_upgrade -p
  4. On the command prompt again load up the table with the data (linux server): mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql -u root -p mysql/li>
  5. Login to your MySQL server again and run the first query: select count(*) from mysql.time_zone_name;

Connect to a remote MySQL server with MySQLBrowser using SSH


You would like to connect to a remote MySQL server using the Mysql-query-browser GUI tool, and you only have localhost access to the remote server, but also have ssh access to the remote server.



You can use the following command to start a port forwarding to your localhost and then use that to connect to the remote database server:

You could then use the following to the Mysql-query-broser connection:


mysql2 gem error with bundle install in Rails 3.0.x

When you try to create a new Rails 3.0.x application, and try to use the ‘mysql2’ gem in your Gemfile without specifying a version you get the following warnings:

WARNING: This version of mysql2 (0.3.2) doesn't ship with the ActiveRecord adapter bundled anymore as it's now part of Rails 3.1
WARNING: Please use the 0.2.x releases if you plan on using it in Rails <= 3.0.x

Specify the latest version of mysql2 that is compatible with the Rails 3.0.x versions as in:

gem 'mysql2', "~> 0.2.7"