Posts Tagged ‘MySQL’

Dump MySQL Database without Drop Table Syntax

No Comments
Posted 05 Feb 2010 — by admin
Category Databases

Output .sql file for MySQL but without the drop table syntax before table name use the –skip-add-drop-table flag

mysqldump -u root -p database_name --skip-add-drop-table --skip-lock-tables > database_name.sql
Tags: , , , ,

Generate MySQL Datetime Type Using PHP Date() Function

No Comments
Posted 05 Feb 2010 — by admin
Category php

If you want to insert a datetime that matches the default mysql datetime type format use this 

date('Y-m-d H:i:s');
Tags: , , , , ,

Managing Timestamps in MySQL with a Trigger

1 Comment
Posted 10 Dec 2009 — by admin
Category Databases

MySQL doesn’t support having two columns with time stamping on both initialization and/or on updating at the same time. It would be nice to be able to do *this* where the created_at column gets the current_timestamp on initialization and the updated_at gets changed on updating the row.

# like so doesn't work...
create table entries(
  body blob,
  created_at datetime default current_timestamp,
  updated_at timestamp default current_timestamp on update current_timestamp
);

Seems like a feature a lot of folks would like. There are two work-arounds. The first is baking it into your application code with something like

create table entries(
  body blob,
  created_at datetime default null,
  updated_at timestamp default current_timestamp on update current_timestamp
);
insert into entries (body, created_at) values ('hello world', now());

The second way is to create a trigger and call the trigger on your insert action on a row. 

create table entries (
  body  blob,
  created_at datetime default null,
  updated_at timestamp default null on update current_timestamp
);
create trigger init_created_at before insert on entries for each row set new.created_at = now();

Now whenever a new row is created the trigger will be executed and set the time to the current timestamp. You can forget about the created_at column in your code because it’s not meant to be changed.

Tags: , , , ,

Install do_mysql Ruby Gem on Mac OS X

No Comments
Posted 19 Aug 2009 — by admin
Category Databases

I ran into the same problem when installing mysql gem for Rails development. This fix worked for me http://seanbehan.com/programming/fixing-mysql-for-rails-2-2-development-on-mac-os-x/

The same thing works with the data objects gem. Just specify the path the mysql config that it’s using and the gem should install just fine. 

gem install do_mysql -- --with-mysql-config=/opt/local/lib/mysql5/bin/mysql_config
Tags: , , , , ,

Fixing MySQL for Rails 2.2 Development on Mac OS X

No Comments
Posted 03 Jul 2009 — by admin
Category Programming

Oh what trouble Rails 2.2 and MySQL (on Mac OS X) can be. Rails, as of version >= 2.2, no longer comes bundled with the MySQL adapter. This means you’ll need to install it yourself, but it appears that the gem for installing it is also broken.

This will fail

gem install mysql

What you need to do is tell the gem which MySQL to use. I installed MySQL with mac ports, http://macports.org , so I need to specify this when I run the installation command. 

gem install mysql -- --with-mysql-config=/opt/local/lib/mysql5/bin/mysql_config

Check out an earlier post explaining how to install MySQL with mac ports http://seanbehan.com/programming/installing-sphinx-search-engine-on-mac-os-x-or-ld-library-not-found-for-lmysqlclient/

You’re not out of the woods yet. Rails needs to know the location of the socket it uses to connect to the MySQL server. I created a symbolic link to the location Rails normally looks for this socket. You could specify the location of the socket in the config/database.yml file, however, then you’ll need to do this with each of your applications. 

ln -s /private/tmp/mysql.sock /opt/local/var/run/mysql5/mysqld.sock

If there is an easier way to get MySQL back for Rails on Mac OS X please let me know. I’d love a quick fix to this problem.

Tags: , , , , ,

Sample Rails Database Config for MySQL

No Comments
Posted 01 May 2009 — by admin
Category Ruby on Rails

Sample Ruby on Rails database config file for connecting to mysql.

production:
  adapter: mysql
  encoding: utf8
  reconnect: false
  database: db_production
  pool: 5
  username: db_user
  password: db_password
  #socket: /tmp/mysql.sock #this may vary
Tags: , , , ,

mysql on rails 2.3.2

No Comments
Posted 19 Mar 2009 — by admin
Category Programming, Ruby on Rails

mysql driver is no longer bundled w/ rails. you’ll need to install it yourself w/

sudo gem install mysql

however, on ubuntu (heron) this won’t work. issue these commands first

sudo apt-get install libmysql-ruby libmysqlclient-dev

if libmysqlclient-dev fails… try libmysqlclient15-dev

then run 

sudo gem install mysql
Tags: , , ,

Setting up a new ubuntu server with apache2, php, ruby on rails, rubygems, mysql, and git

4 Comments
Posted 14 Dec 2008 — by admin
Category Linux, Ruby on Rails

Here are a list of commands to get up and running with Apache2 with Phussion Passenger for Rails, PHP5, MySQL5, Ruby on Rails with Gems, and the source control software Git. I think that this is a pretty ideal environment for a development box and even production if you need to run apps that are written in both PHP and Ruby.

I’m using Ubuntu 8, Hardy Heron in this example.

Login to your server as root, sudo su, if you’re on the box already as another user. You can optionally enter the sudo command before each of the following commands but I don’t in this example. If you get a permission denied error, try the same command again with sudo in front of it.

The following will update where Ubuntu looks for packages, upgrade to the latest stable build and also install build tools like gcc and make.

apt-get update
apt-get upgrade
apt-get install build-essential

Now that the server is up to date and has the necessary tools to build and compile the software we’re about to install, let’s intstall our basic LAMP (Linux, Apache2, MySQL and PHP) stack. You can list different packages to install sequentialy if you separate them with a space.

apt-get install apache2 apache2-prefork-dev
apt-get install mysql-client mysql-server
apt-get install php5 php5-mysql

Lets get Ruby installed next. We will want to use Ruby Gems to manage our Ruby packages rather than apt-get. The reason is because apt-get doesn’t keep up with the latest stable for all Ruby packages and Gems is really a great way to manage Ruby libraries. Think of Ruby Gems as apt-get but for Ruby. Rails for example is a Ruby library that we will download and install using Gems. However, to install Gems we need Ruby and we get Ruby using apt-get.

apt-get install ruby ruby1.8-dev libopenssl-ruby ri rdoc

This should download and install Ruby with a couple of other packages for documentation and SSL support. To get Gems we need to download either a zip or tgz. The best way and easiest way is to navigate to http://rubyforge.org and search for rubygems(one word).

Here is a link to the exact package that I have downloaded and used for this information. 


http://rubyforge.org/frs/download.php/45905/rubygems-1.3.1.tgz

However, this could change in the future or when Gems gets an upgrade.

You need to get it on your server. You can either download it to your local machine and ftp or scp it to your remote server or you can use a tool like wget to download directly to your server.

wget http://rubyforge.org/frs/download.php/45905/rubygems-1.3.1.tgz

If you don’t have wget installed, which would be odd, just type apt-get install wget

After it has finished downloading unpack it and cd into it *the name below will be different .

tar xzvf <rubygems-dowloaded-file.tgz>

After you cd into the folder type

ruby setup.rb

You’ll install new Gems with the command gem install package-name. However, now you need to link the program you just downloaded to /usr/bin/gem. The reason is that Gems was installed but with a version number suffix like /usr/bin/gem1.8 or whatever version it’s at. The reason for this is so that you can switch between different versions of Gems without having to uninstall anything. Just override the old link and you can use the new Gem. So to link the file you’ll need to find the path that it was installed under, most likely /usr/bin/gem1.8. Once you have the path type in the terminal

ln -s /usr/bin/<your gem version> /usr/bin/gem

Where <your gem version> is something like gem1.8

Type gem –version to verify that it works. You’ll get a readout of the version of the Gem you are using. Next let’s get it up to date and install the Rails framework. That is two dashes for the system flag below.

gem update --system
gem install rails

Once that is done we’ll want to install Phusin Passenger which will let us deploy our Rails application in a similar fashion to any PHP based application. Just point a VirtualHost DocumentRoot to the location of your Rails_Root/public directory and your’re good to go!

gem install passenger

Next we’ll build and compile the module for Apache. This is why we needed to run apt-get install build-essential, installing passenger requires make and gcc development tools. When you execute the next command it will spit a lot of stuff to the screen. This is normal, it is building and linking the module for you. When it is done it will tell you what to copy and paste. I will too, but your configuration could be slightly different. Just follow the prompts that follow.

passenger-install-apache2-module

Passenger will tell you where to put the configuration options. It will look something like this

LoadModule passenger_module /usr/lib/ruby/gems/1.8/gems/passenger-2.0.5/ext/apa$
PassengerRoot /usr/lib/ruby/gems/1.8/gems/passenger-2.0.5
PassengerRuby /usr/bin/ruby1.8

I placed the code above into the /etc/apache2/mods-available/passenger.conf then I linked them to the mods available directory like so ln -s /etc/apache2/mods-available/passenger.conf /etc/apache2/sites-enables/passenger.conf

Next create a VirtualHost

<VirtualHost *>
  ServerName awesome.iam.com
  DocumentRoot /var/www/awesome.iam.com/public
  RailsEnv production
</VirtualHost>

Remember to restart your server with

apache2ctl restart

Also, you restart your Rails application by issuing this command from the Rails_Root dir

touch tmp/restart.txt

And finally, to install Git

apt-get install git git-core
Tags: , , , ,