Identifying duplicate rows in a table

select column-name from table group by column-name having count(*) > 1;

MySQL Import and Export

To import and export and entire database schema and data, follow these steps:

mysqldump -h host -u user -p --databases database-name > dump.sql # Export from a database
sed '/^\/\*\!50013 DEFINER/d' dump.sql > dump_0.sql # Fix the permission issues in case you have views
mysql -u user -p database-name < dump_0.sql         # Import to database

MySQL and Spring Transactional Tests

For years, I have been using the Transaction support provided by the Spring test framework to write transaction aware unit tests. In the early days, it was by extending AbstractTransactionalSpringContextTests, which was replaced later with the introduction of Test Contexts. Now a days, just by annotating the class with TransactionConfiguration will trigger the transaction listeners to manage the class level or method level transactions. Where @Transaction annotation is used either at the class or method level.

Recently I was doing a new project using JPA, Hibernate, Spring and MySQL and building a generic unit test framework for it. The standard way I have been configuring MySQL with Hibernate was by using org.hibernate.dialect.MySQLDialect. But this was giving strange problems when running transactional unit test cases. The @Rollback(true) annotation was not working at all. At the end of the tests case, what ever database changes are made, they were committed to the database, as if the Rollback annotation do not have any effect at all. I was creating the schema, including tables using Hibernate's schema script creation APIs.

After searching for a solution, finally I found that in the later versions of Hibernate, there are additional dialects for MySQL. There are two more dialects added, MySQLInnoDBDialect and MySQL5InnoDBDialect. So the old usage with org.hibernate.dialect.MySQLDialect will create ISAM tables, which are not transactional. When the dialect was switched to one of InnoDB dialects, the CREATE TABLE scripts emit additional ENGINE=InnoDB clause to the DDLs. And the transactions were rolled back properly at the end of the test case.

Spring Configuration

  

    
    
    
    
    
    
    
    
  

  
    

    
      
        TestModel
      
    

    
      
        ${testdb.hibernate.dialect}
      
    
  

  
    
  

Property File

jdbc.testdb.username     = user
jdbc.testdb.password     = pass
jdbc.testdb.url          = jdbc:mysql://localhost/testdb
jdbc.testdb.driver       = com.mysql.jdbc.Driver
testdb.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

Test Class

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:test-config.xml"})
@TransactionConfiguration(transactionManager="txManager", defaultRollback=false)
public class DaoTest {

    @Before
    @BeforeTransaction
    public void setup() {
        dbCreator.createSchema();
    }

    @Test
    @Transactional
    @Rollback(true)
    public void testSave() {
        TestModel model = createNewModel();

        dao.save(model);
    }

Can't connect to MySQL server error 111

Even after granting privilege to connect from a different host, still getting an error in connecting to MySQL server from a different box:

mysql -u <user-name> -p -h <mysql-server-ip> <mysql-database-name>

Can't connec to MySQL server on '<your-ip-address>' (111)

MySQL is only listening to localhost interface. To fix the problem, edit /etc/mysql/my.cnf and comment out the line:

# bind-address = 127.0.0.1

And restart the mysql service

sudo service stop mysql
sudo service start mysql

Ubuntu + Redmine + Apache2 + Passenger + Enterprise Ruby + MySQL

This post describes how to install Redmine on Ubuntu, behind Apach2 and Passenger Mode using Enterprise Ruby. I am using Amazon 64-bit Ubuntu AMI.

• Redmine
• Ruby Enterprise Edition
• Pushion Passenger
• MySQL
• Apache Web Server

Install MySQL

sudo apt-get install mysql-server
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient-dev
mysql -u root -p
mysql> create database redmine set character utf8;
mysql> grant all privileges on redmine.* to '<redmine-user>'@'localhost' identified by '<redmine-pass>' with grant option;
mysql> flush privileges;

Install Apache

sudo apt-get install apache2

Install Redmine

cd /opt
sudo wget http://rubyforge.org/frs/download.php/75097/redmine-1.2.1.tar.gz
sudo tar xvzf redmine-1.2.1.tar.gz
sudo ln -s redmine-1.2.1 redmine
sudo chown -R root:root redmine
sudo chown -R root:root redmine-1.2.1
cd redmine
sudo chmod 0755 log
cd log
sudo touch production.log
sudo chmod 0666 production.log
cd ../config
sudo cp database.yml.example database.yml
// Edit the database.yml and change the settings on production to reflect the MySQL database name, db user name and db password created in the above section.

Install Enterprise Ruby

mkdir ~/tmp
cd ~/tmp
wget http://rubyenterpriseedition.googlecode.com/files/ruby-enterprise-1.8.7-2011.03.tar.gz
tar xvzf ruby-enterprise-1.8.7-2011.03.tar.gz
cd ruby-enterprise-1.8.7-2011.03
sudo ./installer

This step will tell you what packages are missing. Follow the instructions and install all the required packages. Then re-run the installer. The installer will compile the ruby enterprise edition and will ask the directory to install. By default, it will be installed in the /opt directory.


In my case, the compiler came back with the following error:

ossl_ssl.c:104:1 - 'SSLV2_method' undeclared here (not in a function)
ossl_ssl.c:105:1 - 'SSLV2_server_method' undeclared here (not in a function)
ossl_ssl.c:106:1 - 'SSLV2_client_method' undeclared here (not in a function)

The solution was to comment out these lines from the source code ~/tmp/ruby-enterprise-1.8.7-2011.03/source/ext/openssl/ossl_ssl.c and re-run the installer.


Wait for the installer to finish. It will take a while.

Install Passenger

cd /opt/ruby-enterprise-1.8.7-2011.03/bin
sudo ./passenger-install-apache2-module

This step will tell you missing packages and how to install them.

Install some of the missing gems

cd /opt/ruby-enterprise-1.8.7-2011.03/bin
sudo ./gem install --no-rdoc --no-ri rack -v=1.1.0
sudo ./gem install  --no-rdoc --no-ri i18n -v=0.4.2
sudo ./gem install --no-rdoc --no-ri gruff
sudo ./gem install --no-rodc --no-ri fastercsv

Configure Apache

• Edit /etc/apache2/sites-available/default and add the following, assuming you are hosting the redmine at the root.
  

  DocumentRoot /opt/redmine/public
  PassengerMaxPoolSize 10
  PassengerMinInstances 3
  PassengerHighPerformance on
  PassengerRoot /opt/ruby-enterprise-1.8.7-2011.03/lib/ruby/gems/1.8/gems/passenger-3.0.8
  PassengerRuby /opt/ruby-enterprise-1.8.7-2011.03/bin/ruby
  <Directory /opt/redmine/>
      Options FollowSymLinks -MultiViews
      AllowOverride none
      Order allow, deny
      allow from all
      PassengerAppRoot /opt/redmine
  </Directory>
  

• To load the passenger module, create a new file /etc/apache2/mods-available/passenger.load and add the following line:
  

  LoadModule passenger_module /opt/ruby-enterprise-1.8.7-2011.03/lib/ruby/gems/1.8/gems/passenger-3.0.8/ext/apache2/mod_passenger.so
  

• Enable the module
  

  cd /etc/apache2/modes-enabled
  sudo ln -s ../modes-available/passenger.load passenger.load 
  

Start the apache

sudo /etc/init.d/apach2 start

Point the browser http://localhost and see if any libraries or gems are missing. Also inspect the /var/log/apache2/error.log and /opt/redmine/log/production.log for any issues.

Confluence + Tomcat + Ubuntu + MySQL

To install the EAR/WAR version of Confluence follow these steps. This assumes basic knowledge on Ubuntu, MySQL, Tomcat etc.

1. Download the latest EAR/WAR version
2. Unpack the distribution
   

       % sudo tar xvf confluence-3.4.6.tar -C /opt
   

3. Create symlink wiki for the confluence distribution
   

       % cd /opt
       % sudo ln -s confluence-3.4.6 wiki
       % sudo chown -R tomcat6:tomcat6 wiki
     

4. Create the data directory
   

       % sudo mkdir /var/data/wiki
       % sudo chown -R tomcat6:tomcat6 /var/data/wiki
     

5. Edit the /opt/wiki/confluence/WEB-INF/classes/confluence-init.properties and set the value of variable confluence.home to /opt/wiki/confluence
6. Edit /etc/default/tomcat6 and adjust the JVM settings
   

   JAVA_OPTS="-Djava.awt.headless=true -Xmx640m -XX:+UseConcMarkSweepGC -XX:MaxPermSize=256m"
   

7. Modify /opt/wiki/confluence/WEB-INF/classes/log4j.properties so that the log files will be produced under /var/log/tomcat6
   
   • Comment the line for ConfluenceHomeLogAppender
   • Uncomment the line for RollingFileAppender
   • Set the log location to /var/log/tomcat6/atlassian-confluence.log
   • Set the debug threshold to WARN
8. Create a confluence database
   

       % mysqladmin -u root -p create wiki
       % mysql -u root -p
       % mysql> grant all privileges on wiki.* to 'wikiuser'@'localhost' identified by 'wikipass' with grant option;
       % mysql> commit;
       % mysql> flush all privileges;
     

9. Setup the confluence context: In the /var/lib/tomcat6/conf/Catalina/localhost directory, create a file called confluence.xml with the following contents:
   

   <Context path="/wiki" docBase="/opt/wiki/confluence" debug="0" reloadable="0"/>
   

10. Restart tomcat
    

        % sudo /etc/init.d/tomcat6 restart
    



11. Point the browser to localhost:8080/wiki and follow the instructions