Saari Development

Ali Rizvi's Technical Blog as a Professional Software Development Engineer

Archive for the ‘mysql’ Category

MySQL: Restarting MySQL on Ubuntu

leave a comment »

/etc/init.d/mysql start

OR

/etc/init.d/mysql restart

Source:
Mysql Database Server Installation and Configuration in Ubuntu

Written by imsaar

April 7, 2008 at 3:16 am

Posted in mysql

Tagged with , ,

MySQL : Saving a ton of space on old tables

with one comment

Here is a nice article about using MySQL (> 5) archive engine.
The MySQL 5.0 Archive Storage Engine

If you have some tables that has mostly read-only for data retention purposes then you can do something like this to drastically reduce the disk spaced used by that table:

alter table MY_OLD_TABLE drop primary key, engine=archive;

note the archive table does not have primary key and dropping the primary key in this way prevents from rebuilding the table twice (compared to the case where you issue two alter table, one to drop key and second to switch engine.

Written by imsaar

January 10, 2008 at 1:15 am

Posted in mysql

Tagged with , , , ,

MySQL : Information about tables

leave a comment »

desc information_schema.tables;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512) | YES  |     | NULL    |       |
| TABLE_SCHEMA    | varchar(64)  | NO   |     |         |       |
| TABLE_NAME      | varchar(64)  | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)  | NO   |     |         |       |
| ENGINE          | varchar(64)  | YES  |     | NULL    |       |
| VERSION         | bigint(21)   | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)  | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21)   | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21)   | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21)   | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21)   | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21)   | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21)   | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21)   | YES  |     | NULL    |       |
| CREATE_TIME     | datetime     | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime     | YES  |     | NULL    |       |
| CHECK_TIME      | datetime     | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(64)  | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21)   | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255) | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(80)  | NO   |     |         |       |
+-----------------+--------------+------+-----+---------+-------+
-- now you can do this or some such
select table_name, engine from information_schema.tables where table_name like "MY_TABLE%";

Written by imsaar

January 10, 2008 at 12:14 am

Posted in mysql

MySQL : Find out and change charset of a table

with one comment

show create table TABLE_IN_QUESTION;
-- show create table can tell you more than 
-- describe TABLE_IN_QUESTION including database engine
-- look towards the end for CHARSET=XXX
alter table TABLE_IN_QUESTION CONVERT TO CHARACTER SET latin1;

Written by imsaar

January 10, 2008 at 12:07 am

Posted in mysql

MySQL and Rails: mysql.sock not found

leave a comment »

I started doing my webapp on my brand new server (ubuntu linux 7.04) and when trying to run my first migration I got the following error message:


rake db:migrate
(in /home/rizvi/projects/somewebapp)
rake aborted!
No such file or directory - /tmp/mysql.sock

Here is a useful mysql command to find out mysql defaults:


mysqladmin --print-defaults
mysqladmin would have been started with the following arguments:
--port=3306 --socket=/var/run/mysqld/mysqld.sock

Here is what solved the problem:


sudo apt-get install libmysql-ruby

Note: The above is an alternative to :

sudo gem install mysql

Now I get:


rake db:migrate
(in /home/rizvi/projects/somewebapp)
rake aborted!
Unknown database 'somewebapp_development'
(See full trace by running task with --trace)

So I need to do the following:


mysqladmin -u root create somewebapp_development -p
mysqladmin -u root create somewebapp_test -p
mysqladmin -u root create somewebapp_production -p

Reference: RailsOnUbuntu

Written by imsaar

August 6, 2007 at 6:37 am

Posted in mysql, rails