Category Archives: MySQL

Connect to a remote MySQL server with MySQLBrowser using SSH

Posted on by
Reply

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

mysql2 gem error with bundle install in Rails 3.0.x

Posted on by
Reply

Problem
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

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

gem 'mysql2', "~> 0.2.7"

converting MySQL sysdate to local timezone

Posted on by
Reply

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

Posted on by
Reply

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

Posted on by
Reply

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])

Using Flexigrid with MySQL field carriage returns

Posted on by
2

Problem
You want to use flexigrid with a MySQL fields that contains carriage returns. As the flexigrid uses json it doesn’t work with carriage returns, and displays an empty page, instead of an error page.

Solution
In the page_name.json.erb file in the fields that has carriage returns make sure that you use to_json method as in the example below:

‘<%=fg_escape event.comments.to_json -%>‘,

Mandriva – Rails 2.3.2 – mysql gem – ‘ERROR: Error installing mysql’

Posted on by
Reply

Problem
You are trying to install the mysql gem in Mandriva, but it fails with error messages:

ERROR:  Error installing mysql:
ERROR: Failed to build gem native extension...
checking for mysql_query() in -lmysclient... no ....

Solution
After searching in google, with solutions about providing different options (– –with-mysql-config, ..etc), even trying different combinations for providing the client library path, the configuration file, or the header file path, was still faced with the same error installing the mysql gem.
As the Mandriva installation was quite new, it turns out to be a couple of missing packages.
So try:

urpmi gcc
urpmi make

and run:

gem install mysql

again.

It should work out ok install the gem and output:

Building native extensions. This could take a while...
Successfully installed mysql-2.7
1 gem installed

Comparing data from two big MySQL tables

Posted on by
Reply

Problem
You have two big tables in MySQL (>640K records), that maybe differ in the number of fields, but you want to make sure that the data in the common fields in both tables are the same.

Solution

  1. Use mysql to export the data from the first table in a csv file, selecting only the common fields.
    We use the /tmp folder on the server to make sure we have the right permissions to create the file:

    mysql>SELECT common_field1, common_field2, ... 
INTO OUTFILE '/tmp/first_table.txt'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table1;
  2. If the tables are in different databases remember to switch db: 
    use seconddb;

    Export the second table in the second file:

    mysql>SELECT common_field1, common_field2, ... 
INTO OUTFILE '/tmp/second_table.txt'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table2;
  3. now use the diff. You can use any of the following options:
    • diff -q first_table.txt second_table.txt
    • diff first_table.txt second_table.txt > diff.txt