Using tables with dashes in MySQL queries

Problem

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:

ERROR 1064 (42000): 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 '-db.* to 'username'@'%'' at line 1

Solution

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

GRANT ALL on `databasename-db`.* to 'username'@'%'

Exporting query results from MySQL

Problem

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

Solution

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:

SELECT one,max(two) max_two,min(three) min_three 
FROM table 
WHERE one=1 
GROUP BY one 
INTO OUTFILE '/tmp/my_query.csv';

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

Problem

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:

ERROR 2003 (HY000): Can't connect to MySQL server

Solution

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

bind_address 127.0.0.1

to

bind_address AMAZON_PRIVATE_IP

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.

Connect to a remote MySQL server with MySQLBrowser using SSH

Problem

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.

 

Solution

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:

ssh -L 3307:localhost:3306 user_name@remote_host -N

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

 

Hostname: 127.0.0.1
username: db_user_name_on_remote
password: db_user_password_on_remote
port: 3307

converting MySQL sysdate to local timezone

Problem
You are hosting your MySQL server in a shared host which is in a different country from your application’s local timezone, and you cannot change the MySQL system timezone, but you would like to use sysdate for date comparisons.

Solution
In your SQL query that you use for comparing with the system date/time, use the convert_tz function in MySQL, using the appropriate local time zone (ie Europe/London):
convert_tz(sysdate(),'SYSTEM','Europe/London')

Rails – Mysql::Error: Got error 28 from storage engine

Problem
Trying to access your rails application in your development pc, you get the error:

Mysql::Error: Got error 28 from storage engine

in your console.

Solution
It turns out that this specific error is a MySQL error, indicating that you have run out of space in the partition that the MySQL server stores its files (/var/lib/mysql).
Looking at the folder there, you can see a very large file called ibdata1.
This file holds all the information about your InnoDB tables and transactions in your MySQL. According to other posts after a Google search, it seems that this file cannot be reduced in size either by removing/truncating your tables or deleting databases that you don’t need. There are quite a few solutions out there if you really need to keep a backup of your databases, but if you only using your database for development and you DON’T NEED (!!) your data or tables, you can move this big ibdata1 to another partition for backup and restart your mysql, that will create a new file.

Rails find case sensitive search

Problem
You want to use find in Rails for a case sensitive search. For example in your authentication logic you have something to find the user to login as in:
u=User.find_by_login(‘username’)
If you don’t want to limit the available logins with case insensitive validation in the model, then the above code will not work if you have ‘Username’ and ‘username’ as available logins, as the find will only return one of them.

Solution
Change the find method to use the BINARY keyword in your database (only used with MySQL), as in the following:
u=User.find(:first, :conditions => [“BINARY login = ?”, login])