How to get database size in MySql

SELECT table_schema "DB Name", sum( data_length + index_length ) / 1024 / 1024 "DB Size in MB" 
FROM information_schema.TABLES GROUP BY table_schema ;

Courtesy: http://stackoverflow.com/questions/1733507/how-to-get-size-of-mysql-database

Install mysql gem using bundler in ubuntu 11.10 64 bit

Well just tried doing a mysql gem install using bundler (Gemfile) but got an error like this

Gem::Installer::ExtensionBuildError: ERROR: Failed to build gem native extension.

        /home/praveen/.rvm/rubies/ruby-1.9.2-p290/bin/ruby extconf.rb
checking for mysql_query() in -lmysqlclient... no
checking for main() in -lm... yes
checking for mysql_query() in -lmysqlclient... no

and much more

and i forgot to install  libmysqlclient-dev :) how careless! so did
sudo apt-get install libmysqlclient-dev

and redid the bundle install and wohooo it worked!

What the hell! Can't serialize a Mysql object with Marshal in Ruby

Okey so got this strange information late in night at about 3.50am and it took more than 2 hrs for me to surrender. uff :D
and this is something that came in picture

`dump': no marshal_dump is defined for class Mysql (TypeError

and this is a small code snippet which will give you this situation

require "rubygems"
require "mysql"

class RandomClass
def initialize
@db_instance = Mysql.real_connect("hostname", "username", "password", "database_name")
end
end

random_class = RandomClass.new
a = Marshal.dump(random_class)

and there you are, so one can't serialize a object with MySql object in it and with the set of finding i can even say that serializing even with yaml is also not possible.

So work around to this that i can suggest is to do a close of live mysql object in the method itself so that at any give time when object will be passed to serialization using Marshal.dump there would be no live MySql object to hinder with serialization :)

try this piece of snippet now

require "rubygems"
require "mysql"

class RandomClass
def initialize
db_instance = Mysql.real_connect("hostname", "username", "password", "database_name")
db_instance.close
end
end

random_class = RandomClass.new
a = Marshal.dump(random_class)

And this should work :)

"Error establishing a database connection" from wordpress even when db credentials are correct

Yes i got hit by this just today!!

Let me tell you guys one thing i am not that much into Wordpress for any reason. But one of my blog is on Wordpress and obviously i am not the person who manages it but i do look into technical stuff if it goes out of hand(of my buddy)

Encountered a strange problem where in an existing install of Wordpress(which is about 1 year old) showed a message "Error establishing a database connection" in complete page, all of a sudden. wooohhhh!!!

and then i tried looking out for any changes in wp-config.php(which stores db credentials) but they all were fine. I crossed checked it by manually connecting to the db with credentials provided from console and yes i was able to connect to but Wordpress was telling me that it didn't.

I thought lets take a backup of the existing database and then try to debug it (so that in case i loose some data during this play i am in a position to recover too) but there was something waiting for me.... ufff!!!!

mysqldump -u some_user -psome_password xxxxxxxxx_blog > xxxxxxxxx_blog.dump.sql

and i got this in response

mysqldump: Got error: 145: Table './xxxxxxxxx_blog/xxxxxxxxxblog_options' is marked as crashed and should be repaired when using LOCK TABLES

so due to some activity we have blog's options table all messed up... so what to do!!

Answer is go to /var/lib/mysql/
and run myisamchk on the tables mentioned that should fix it.

something like..

sudo myisamchk -r ./xxxxxxxxx_blog/xxxxxxxxxblog_options

with output:

- recovering (with sort) MyISAM-table './xxxxxxxxx_blog/xxxxxxxxxblog_options'
Data records: 210
- Fixing index 1
- Fixing index 2
Data records: 208

and whola!!! my blog is back :D

Hope this must have helped you in anyway or the other.... will be back soon with more life like post.. these computer tech blogs.. uff uff!!

Conversion from mysql to sqlite database.. via rails

I faced this problem to have a sqlite database from an existing mysql.. and being the rails guy... i made it the rails way.. but of course there are other option available which will be fast .. this is kind of slow... but works for small stores :))

Steps are..

1. Create a source model which is connected with mysql connection/using mysql adaptor

class SourceDB < ActiveRecord::Base

end

SourceDB.establish_connection($config["database_mysql"])

2. Create a destination model which is connected with sqlite connection/using sqlite adaptor

class TargetDB < ActiveRecord::Base

end

TargetDB.establish_connection($config["database_sqlite"])

3. Create schema from source database so that can reproduce it in destination(sqlite) database.

File.open(name_of_schema_file,"w") do |file|

  ActiveRecord::SchemaDumper.dump(SourceDB.connection, file)

end

4. Alter the generated schema file to remove line which contains "add_index" as these are of no use in sqlite conversion process

5. Then load the altered schema file (change the ActiveRecord::Base.connection to point to destination database before loading)

ActiveRecord::Base.connection = TargetDB.connection

load(name_of_schema_file)

6. and now resurvely iterate with all tables in source database and transport it to derstination (sqlite) database

Source file [mysql_to_sqlite.rb]

require 'rubygems'

require 'active_record'

require 'active_support'

require 'sqlite3'

require 'active_record/schema_dumper'

require "yaml"

require "create_class.rb"

t1 = Time.now

$config   = YAML.load_file("config/config.yml")

name_of_schema_file = ($config["schema_file"])

class SourceDB < ActiveRecord::Base

end

SourceDB.establish_connection($config["database_mysql"])

class TargetDB < ActiveRecord::Base

end

TargetDB.establish_connection($config["database_sqlite"])

puts "dumping the schema"

File.open(name_of_schema_file,"w") do |file|

  ActiveRecord::SchemaDumper.dump(SourceDB.connection, file)

end

puts "discarding index(es)"

line_array = Array.new

File.open(name_of_schema_file,"r") do |file|

  file.each { |line|    line_array << line  unless line.include?("add_index")}

end

File.rename(name_of_schema_file, "original_#{name_of_schema_file}")

File.open(name_of_schema_file,"w") do |file|

  file.puts(line_array)

end

puts "loading the schema"

ActiveRecord::Base.connection = TargetDB.connection

load(name_of_schema_file)

SourceDB.connection.tables.each do |tbl|

  puts "Table_initiated: #{tbl.inspect}"

    SourceDB.set_table_name tbl

    SourceDB.set_inheritance_column ""

    

    create_class('TargetModel', TargetDB) do

      set_table_name tbl

      set_inheritance_column ""

    end

    puts "=========for table: #{tbl}=========="

    total_record_in_table = SourceDB.count_by_sql("SELECT COUNT(*) from #{tbl}")

    tub_size = 1000

    no_of_iteration = ((total_record_in_table % tub_size) == 0) ? (total_record_in_table / tub_size) : ((total_record_in_table / tub_size) + 1)

    for j in 0..(no_of_iteration-1)

      current_record_set = SourceDB.find(:all,:offset => (j*tub_size), :limit => (tub_size - 1))

      current_record_set.each_with_index do |record,ind|

        record_copy = TargetModel.new

        record.attributes.each do |key,value|

        record_copy.send("#{key}=",value)

        end

        record_copy.save

        puts "Completed: #{((j*tub_size) + ind)} of #{total_record_in_table}   with id: #{record_copy.id} ."

      end

    end

end

        

t2 = Time.now

puts "Process initiated at: #{t1}"

puts "Process completed at: #{t2}"

puts "Time elapsed        : #{t2-t1} seconds"

Source file [create_class.rb]

def create_class(class_name, superclass, &block)

  klass = Class.new superclass, &block

  Object.const_set class_name, klass

end

Configuration file [config.yml]

database_mysql:

  adapter: mysql

  database: databse_name

  username: root

  password: xxxxxxxx

  host: xxx.xx.x.xxx

  timeout: 5000

  encoding: utf8

database_sqlite:

  adapter: sqlite3

  database: sqlite_database_file_with_path.db

  

schema_file: schema.txt