Install MySQL - Tips and Errors with Resolution

MySQL - bit datatype
a) This type will be used to store true / false information. (false will be stored as value 1 and true value stored as 2)

b) eg: to update bit value (true / false) : UPDATE SET column = TRUE;

c) To view the bit datatype information in command prompt type column_name + 1

eg query : SELECT columnName + 1, tableName.* from tableName; (columnName is a bit data type column).

INSERT statement from SELECT statement
eg : INSERT INTO operation_processor (processor_id , operation_id)
VALUES(1, (SELECT operation_id FROM operation WHERE production_system_template_id = 3 AND name = 'AreaCodePull'));

MySQL Workbench : 
If you want to do ER Diagram or Querying database use MySQL WorkBench. Its very nice. You can do "Reverse Engineering" also under database menu. This will give you ER diagram for the existing database or tables.

MySQLImport - Import file directly into table.
-------------------------------------------------
mysqlimport --compress --fields-optionally-enclosed-by="\"" --fields-terminated-by=, --ignore-lines=1 --lines-terminated-by="\n" --local -uusername -ppwd -hhost db_name /path/fileName.csv

You can use REPLACE for import based on unique key for update.

Source.csv is tableName.csv

ERROR 1449 (HY000) at line 13718: The user specified as a definer ('username'@'%') does not exist

CREATE USER 'user'@'localhost' IDENTIFIED BY 'user';

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' WITH GRANT OPTION;

FLUSH PRIVILEGES; 

GRANT SELECT, SHOW VIEW ON dbame.`table` to 'user_name'@`%`;

To Revoke permission
REVOKE REFERENCES ON  `jabord_dev_db`.* FROM 'jabord_user'@'%'

If you upgrade your mysql from lower version to higher try "mysql_upgrade" to avoid corrupted table issue.

http://forge.mysql.com/wiki/Top10SQLPerformanceTips

MySQL Install 5.5

sudo apt-get purge mysql-server

sudo apt-get purge mysql-client

sudo apt-get purge mysql-common

sudo apt-get autoremove

sudo apt-get autoclean

sudo rm -fr /var/lib/mysql

sudo wget http://mirrors.ukfast.co.uk/sites/ftp.mysql.com/Downloads/MySQL-5.5/MySQL-server-5.5.14-1.linux2.6.x86_64.rpm

sudo wget http://mirrors.ukfast.co.uk/sites/ftp.mysql.com/Downloads/MySQL-5.5/MySQL-client-5.5.14-1.linux2.6.x86_64.rpm

sudo apt-get install alien

sudo alien -i MySQL-server-5.5.14-1.linux2.6.x86_64.rpm --scripts

sudo alien -i MySQL-client-5.5.14-1.linux2.6.x86_64.rpm --scripts

sudo apt-get install libaio1 libaio-dev

sudo /usr/sbin/mysqld --skip-grant &

sudo /etc/init.d/mysql start

Before command failure :  

ALTER TABLE tableName ADD column columnName dataType primary key auto_increment BEFORE secondColumnName; 

Above command may fail. Because you cannot add first column with 'before' command. Use FIRST replacing BEFORE keyword and you should be good

MySQL - Views

MySQL View is very useful to see data in one view place. IF we have multiple tables and data's and if you specifically interested to view specific data you can create view below is example.

CREATE VIEW test.v AS SELECT * FROM t;

DROP VIEW IF EXISTS im_job_info;
CREATE VIEW im_job_info AS
SELECT DISTINCT a.req_id , a.source_req_id , a.source, a.sub_source,
b.job_id, b.external_target_id, e.operation_type, b.status AS 'jobstatus', a.status AS 'requeststatus',
c.send_status, b.last_status, b.priority, b.production_system_id,
a.received_at,b.queued_at, c.sent_at

FROM
request_message a, job b, response_message c, external_target d,
operation_type e

WHERE
a.req_id = b.req_id AND c.im_req_id = b.req_id AND
d.external_target_id = b.external_target_id AND
d.operation_type_id = e.operation_type_id;

Query to List all the views from the db
Syntax : 
     SHOW FULL TABLES IN `db_name` WHERE TABLE_TYPE LIKE 'VIEW';

MySQL - Duplicates Handling with Insert Ignore / Replace

CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
UNIQUE (last_name, first_name)
);

INSERT IGNORE INTO person_tbl VALUES( 'Thomas', 'Jay', 'Male'); - This query will try to insert and if the record already exist it ignores to insert. It checks unique / primary key

REPLACE INTO person_tbl (last_name, first_name, sex) VALUES( 'Jay', 'Thomas', 'FeMale'); - This query will try to replace if the same unique / primary key record already exist. If record not exist it will insert.

MySQL - Event Scheduler - Example

event table exist in mysql database for more info. to get about event. It will be in MYsql VERSION > 5.1.16

CREATE TABLE a (
c1 INT(1));

SET GLOBAL event_scheduler = 1; -- Turn on event scheduler variable
-- DROP EVENT IF EXISTS insert_record;

DELIMITER //
CREATE EVENT IF NOT EXISTS insert_record
ON SCHEDULE AT now() + INTERVAL 1 MINUTE
[ON COMPLETION [NOT] PRESERVE]
DO
BEGIN
INSERT INTO a (c1) values (2);
INSERT INTO a (c1) values (5);
END//

select * from mysql.event;