Categories
Continous Integration MySQL PHP Programming Project Management

Experience with BitBucket CI

I have been using bitbucket for quite sometime, and really like their service. Recently, I come to know that they have Beta of CI through pipeline, that can run any docker from Docker hub. I thought to give it a try though I have my own build setup on my local machine and really don’t need it. But why not test something new.

So, my project is regular website, which is coded in Custom PHP (no framework) and using MySQL database. I wrote some Unit Test using PHPUnit test, so all I need is to run those test when I commit to Bitbucket. It seems pretty straight forward as Bitbucket configuration example shows the configuration file for PHPUnit test. I pick it up, change path and run. My test failed, as they never get the MySQL extension for PHP. What? PHPunit test docker doesn’t have database extension installed? So, how we are suppose to test it?

Well, there are few ways, one is you use multiple docker (multi-docker environment) which I recently studied is available and I didn’t yet test it. And other is you create your own docker and use it. Install whatever you need to make it happen. Also, I realize that installing MySQL will not make any good unless I install database on it before test and change it’s certain values for testing. So, I create a new docker that has everything I need for my project, all PHP extension and database server. (You can use it from https://hub.docker.com/r/archergod/phpall/). Then I update configuration file to do following:

  1. Run MySQL service (yes by default docker do not initiate run.d etc, not sure why) and hence no service auto start.
  2. Then run `mysql` to import a Script (SQL) file to create and install database, and users needed. I put everything in that one SQL file and run it as root MySQL user. It is only a docker doing testing.
  3. then Navigate to Directory in respect to my Git folder as my default bitbucket leave your prompt at git folder.
  4. Run your test.

It does a great work, though every time you push to git, it create a CI request and download docker image, set it up based on BitBucket’s script and then run your script. So it take 2 minute extra to set the machine. If you are in habbit to push every 2 hr of work, then you end up running your test every 2 hr on every push.

But basically it is all good and is free too, which I like most. Though I have GitLab at my local machine and it works exceptionally great, but BitBucket’s CI is good and different. And in some cases is better than Gitlab’s CI.

Categories
Management MySQL Project Management Server Configuration

Installing Redmine 2.3.3 on Ubuntu 13

Okay, today I install Redmine on Azure hosted Ubuntu 13.04. The steps are easy once I get it done after 2 days of working and finding what is right to do. So, let us start on installation, but a little history first.

I see that Bitnami has put quite a few Redmine VM Images on VM depot. But unfortunately all are on older version of Ubuntu. But I still thought I can upgrade them for my use, so I use the latest [Ubuntu 12] based image and try to do ‘do-release-upgrade’ it download lot of things, but in end it fail to update the machine. So, I now opt to use core Linux machine to install what I need. So, I create a VM using Ubuntu 13.04. It was easy as usual. Once the machine is up. I once run do-release-upgrade to install all latest package and there are quite a lot of them. It took around 20-30 minute for my Very small instance of VM to install them.

Once the machine is ready, now we need to install: Apache, MySQL [so I can host not just redmine but couple of my other website as well]. And to extract the latest source of Redmine we also need SVN [package name ‘subversion’] on server as well. I usually use svn version only, but you can download zip/tarball as well.

So install is as follow:

# sudo apt-get install subversion
# sudo apt-get install apache2 libapache2-mod-passenger
# sudo apt-get install mysql-server mysql-client
# sudo apt-get install ruby ruby-dev
# sudo apt-get install imagemagick libmagickwand-dev ruby-rmagick

Above statements will install subversion, apache, MySQL, ruby and ruby-dev, imagemagick and ruby-rmagick .. they all are prerequesties and you might already gave those. Once this is done..

Download Redmine 2.3.3 from svn using this Redmine Download link http://www.redmine.org/projects/redmine/wiki/Download

#svn co http://svn.redmine.org/redmine/branches/2.5-stable redmine-2.5

Now, we need GEM Bundler to be installed. so

 # sudo gem install bundler

Now navigate to Redmine folder, oh you can download redmine in any folder, as long as you are ready to use your <redmine folder>/public as your document root. if not you can use symbolic link of public folder as well in apache. Just thought to tell this now. In next step we will create a Gemfile.local to tell the installer to use rack’ version 1.4.5 as by default it install version 1.5.2 and it doesn’t work for me and I see lot of people had problem with it, so just create a Gemfile.local with one line in it and use your bundler install to do it.

# cd redmine-2.3 
# sudo cat > Gemfile.local << "EOF"
gem "rack", "~> 1.4.5"
EOF
# sudo bundle install --without development test mysql
# rake generate_secret_token

Once you did that it install redmine or rather just built it. We now need a database to store redmine data, so create MySQL database, username and password as you want. Obviously you don’t want to use root username. Once you create that user and database. then go to redmine/config folder, you will find database.yml.example file, copy it as database.yml go to production section of MySQL db, enter your login info and change database type t0 mysql2 [it is just new library of MySQL with ruby, you can still use [mysql] but it might give error so better change it.

production:
  adapter: mysql2
  database: redmine_default
  host: localhost
  username: redmine
  password: some-secure-plain-text-password
  encoding: utf8

Now run following commant to Create Database Table, clear unwanted data and session

rake db:migrate RAILS_ENV=production 
rake tmp:cache:clear
rake tmp:sessions:clear

Now, second last step: Creating a virtual Host or defining the DocumentRoot so our apache can use redmine installation. Add following Virtual Host Tag, you can add other information as you like, but keep this as minimum you need. Please change “[” and “]” with “<” and “>” as my editor doesn’t allow me to use them below…

[Virtualhost *:80]
DocumentRoot /usr/local/share/redmine-2.3.0/public
[Directory /usr/local/share/redmine-2.3.0/public]
AllowOverride all
Options -MultiViews
[/Directory]
[/VirtualHost]

Restart Apache

# sudo service apache2 restart

Now, go to your domain, IP address base url whaterver it is, and login using “admin” as username and password. You are most probably ready to rock. If not you should enable error login using /config/environment/production.rb file and then check what error you might get from Redmine.

Categories
Article Blog: My thoughts Database MS SQL Server MySQL SQL SQlite

Database Optimization

Well, most of program use database for their web application. Eventually all web application does have database. But how many of those web application really works good. Well quite few. I am in web programming for more than 7 years now and during these years I have seen lot of developers building website using PHP or ASP.NET or ASP or PERL using SQL SERVER, MYSQL, Oracle or other database, but I always have a sense of making best application than them. But today I when I look back I realise that I was bad at writing code and making application in my initial years. Not that I don’t want to write a good code, but the fact my approach was not right. Some of the important thing that I learn over the year based on my experience are listed below. However before that I want to tell that not just me but all of us have read them in books but most of us might not have used it thinking we don’t need it as things works otherwise as well. So major stuff are:

1. Fast Database

2. Object oriented Code

3. Serialize flow of application.

In this post I try to mention Only Fast database. Well with Fast database I do not mean to pick the Fastest database on planet through bookmark testing, nor does I mean to use heavy hardware to make database run faster. These two things can really make your database appears to be faster but not really fast. I often ask a question in forum that I have few million records to parse, and can the DB engine handle it. I always get one answer if a database cannot handle few million record why we really need it.

This was very true statement, but do you really facing problem when you have only half million record and your site start taking 20-30 second to access database ? If so, then welcome to my past world. Well don’t worry you don’t need new hardware, you need new approach of programming your software and most importantly you need better database structure.

Starting with most commonly used stuff and I guess most importantly forgotten stuff.. Indexes. Yes, this is most common solution to make your site fast. Okay, so you already have define primary key in your table and it auto create index for it, but still not good enough. Well you need to index columns that you have used in your search and or in Join. Making index of one column does help, but making index of combination of 2 columns make it much better in queries that use the search on those two fields. For example, if you are searching Employee Last Name and their City from Employee table, having index on LastName and City seperately produce result slower than having index on Lastname and city name.

Now moving to next part of it. I have a sql query that has Client Code and User code in seperate column and I have a text box that allow user to enter ClientCode – UserCode in there, and I have to search it in database. I got little lazy and what I did I took the Textbox value as it is and did something like

where ClientCode + "-" + UserCode = @MyClientCode

Now, this looks good and works well for me, but this is not a good approach of writing it. In where clause never do the combination of column like that. Search easy column seperately, i.e.

where clientCode =@ClientCode and UserCode =@UserCode

the difference make your search works 10-15 times faster if you tend to have lot of records.

Third thing that comes to my mind is normalization of database and then creating views with indexes on view to join them if needed. I often see that database is not normalized and lot of Text field are used to store combination of values. For example if you want to have a product in different category then make a table Product_category, which store product ID and CategoryID, instead of storing comma seperate category IDs in product table. This seems awkward at first to change, but advantages are real. You can search product on category much faster as only Integer values are compare against the Regular expression match in comma seperated values. You can index the value in relation table, but not in text field. This approach is very common in developer in sub continent. If I am not wrong 70% of sites I see from other developer has this mistake.

Above are some error in design of database I have seen and some of them I did at my start of career, but I change my approach and today I am handling database with more than 10-20 million entries easily. Infact my site took less than 1 sec to search with a three table join. I remember the old version of this join that took 15 secs. I did great optimization there.

Anyways, making a software is your work, making it great is your choice.

Categories
Joyous MySQL Operating System

MySql 5.5 : funny about it

Just saw the release note of MySql 5.5. Looks exciting upgrade, but what looks most funny about the software is the fact that they claim to improve performance by 1500% on windows [some 372% on linux]. Looks great work by them. But I found this information funny, as my some Open source lover friends always claim that because of windows bad structure mysql perform bad on Windows machine as compare to linux. But now my argument to them is if it is bad structure of windows, then how they achieve 1500% improvement by improving their code and not windows ? Often people forgot when comparing software that run on multi platform that the optimization of core software can only be for either OS [if they share same code], and that cause performance issues.

But whatever it is, I am happy to see MySql perform better on windows as I am bound to use windows with kind of resources I can afford for my development and maintainability I can handle.

Good work Team !!!

Categories
MySQL Server Configuration

MySQL : Crash and Upgrade

Today, I face a new problem with MySQL. I got the database to crash quite a few times since morning. Event logs shows that there is a fault in mysqld.exe, but that error was not very clear on specific reason of fault. After searching for articles and forums, I got a hint that in past [about 3-4 yrs], one guy has same random crash problem and he found that crash was related to some store procedure. This give me a clue and I found that in my case it is a corrupted database file. I need to investigate as why that database got corrupted as it is the latest database created in system and was working fine just another day.

Anyways, this lead me to upgrade database server after 6-7 months. It was pretty easy though, just download the new version and run the setup. everything was done automatically. Isn’t it that very simple.

Categories
Database MySQL

MySQL WorkBench : New GUI

I was looking at MySQL.com and found a new GUI, which came as replacement of old MYSQL administrative GUI. My impression was not good to learn that they have something new, as I thought it might be just that they get their old interface opensourced or something. But when I install this application, hmm .. it looks really good, a decently good managed user interface. Meet all modern application GUI stuff, still keep it professional. Though I download a Beta version but so far so good, it does all basic work I need to perform in manner I am habitual. Looks like I can relay on this interface in future.