MySQL Ramp Up

MYSQL:

Add Column to Existing Table and Make it Auto Increment and Add Primary Key Constraint

Link : Alter Table and Add Column

ALTER TABLE StudentPortalData ADD StudentPortalDataID INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (StudentPortalDataID);

Query Plan and Query Profile for Executed Queries

USE SampleDatabase;

SELECT @@profiling;
SET profiling = 1;

EXPLAIN
SELECT * FROM SampleTable
where DATE_OF_BIRTH='04131998'
and CODE ='CODE';

SELECT * FROM SampleTable
where DATE_OF_BIRTH='04131998'
and CODE ='CODE';

SHOW PROFILES ;
SHOW PROFILE FOR QUERY 15;

TimeOut Error from MySQL WorkBench: Error Code: 2013. Lost connection to MySQL server during query 600.510 sec

New versions of MySQL WorkBench have an option to change specific timeouts.
For me it was under Edit → Preferences → SQL Editor → DBMS connection read time out (in seconds): 600
Changed the value to 12000.

MySQL: Currently Running Querires
ProcessList

show processlist;
show full processlist;

BulkLoad : Bulk Load CSV Into MySQL
LOAD DATA INFILE

LOAD DATA LOCAL INFILE 'G:\\TexasDI\\StudentPortal\\StudentPortalData_01(10).csv'
INTO TABLE StudentPortalDataBCP
FIELDS TERMINATED BY ','
 LINES TERMINATED BY '\n'
;

mysql> LOAD DATA
          LOCAL INFILE 'sample.csv'
          INTO TABLE sampletable
          FIELDS
            TERMINATED BY ','
            ENCLOSED BY '"'
            ESCAPED BY '"'
          LINES
            TERMINATED BY '\n'
          IGNORE 1 LINES
       ;



LOAD DATA  LOCAL INFILE 'G:/FileFolder/filename.txt'
INTO TABLE `filename`
FIELDS  TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
IGNORE 2 LINES ;

-- PASS NULL FOR EMPTY STRINGS
-- https://bugs.mysql.com/bug.php?id=23212

LOAD DATA  LOCAL INFILE 'G:/TexasDI/DataFiles/LookupFiles/testminmax.csv' INTO TABLE LookupTables.LookupSTAAR38TestCodeMinMaxCut_NULLTest
FIELDS  TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES
(`ADMIN_CODE`,
`TEST_CODE`,
`TEST_VERSION`,
`TEST_LANGUAGE`,
`STANDARD_CODE`,
`MIN_SCALE_SCORE`,
`MAX_SCALE_SCORE`,
`CUT_MIN`,
`CUT_LEVEL1`,
`CUT_LEVEL2`,
`CUT_LEVEL3`,
`MAX_POINTS`,
`CreatedBy`,
`CreatedDateTime`
)
SET CUT_MIN = nullif(`CUT_MIN`,'') ,
 CUT_LEVEL1 = nullif(`CUT_LEVEL1`,'') ,
 CUT_LEVEL2 = nullif(`CUT_LEVEL2`,'') ,
 CUT_LEVEL3 = nullif(`CUT_LEVEL3`,'') ,
 MAX_POINTS = nullif(`MAX_POINTS`,'')  ,
  CreatedBy = user() ,
 CreatedDateTime = current_timestamp()

;

BulkLoad through SQLImport : Bulk Load CSV Into MySQL
MySQLImport

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqlimport --local --compress --user=DBUser --password=DBPassword --host=auroracluster.cluster.us-east-1.rds.amazonaws.com  --fields-termin
ated-by="," DatabaseName G:\DataBCP.csv G:\BCPData_01(10).csv

Warning: Using a password on the command line interface can be insecure.
DatabaseName.DataBCP: Records: 1001  Deleted: 0  Skipped: 0  Warnings: 2014
DatabaseName.DataBCP: Records: 2794778  Deleted: 0  Skipped: 0  Warnings: 34486996

For Optionally enclosed fields : --fields-optionally-enclosed-by="'"

MySQL: List of Tables in a Database

SELECT
    table_name, table_type, engine
FROM
    information_schema.tables
WHERE
    table_schema = 'dbname'
ORDER BY table_name DESC;

MySQL: Set Transaction Isolation Level

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM `tablename` ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

MySQL: Simple WHILE Statment

DELIMITER //

CREATE PROCEDURE `proc_WHILE` (IN param1 INT)
BEGIN
    DECLARE variable1, variable2 INT;
    SET variable1 = 0;

  WHILE variable1 < param1 DO
		SELECT variable1 ;
        SET variable1 = variable1 + 1;

    END WHILE;

END //

CALL  `proc_WHILE` (1000);

MySQL: Converting Strings into Uniquely Identifying Integers

SELECT CONV(SUBSTRING(CAST(SHA(CONCAT(IFNULL('column1 or value1',''), IFNULL('column2 or value2' , ''))) AS CHAR), 1, 16), 16, 10) AS HashValue ;

STRING Functions

MySQL: Build JSON From MySQL Select Statments

SELECT CONCAT('[',GROUP_CONCAT(CONCAT('{name:',StudentID,''),CONCAT(',email:',DATE_OF_BIRTH,'}')),']') FROM StudentInfo LIMIT 1 ;

Your options for changing the value of group_concat_max_len are:

changing the value at MySQL startup by appending this to the command:
--group_concat_max_len=your_value_here
adding this line in your MySQL configuration file (mysql.ini): group_concat_max_len=your_value_here
running this command after MySQL startup:
SET GLOBAL group_concat_max_len=your_value_here;
running this command after opening a MySQL connection:
SET SESSION group_concat_max_len=your_value_here;

http://stackoverflow.com/questions/12001919/mysql-truncates-concatenated-result-of-a-group-concat-functon

MySQL: INNODB Status

SHOW ENGINE INNODB STATUS ;

Output:

=====================================
2015-09-03 16:41:50 2b48a23c9700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 28 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 221103 srv_active, 0 srv_shutdown, 183952 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 28307731
OS WAIT ARRAY INFO: signal count 14885273
Mutex spin waits 1481261128, rounds 1689244595, OS waits 54039
RW-shared spins 4299708, rounds 119528046, OS waits 3769236
RW-excl spins 18264576, rounds 867748492, OS waits 23593873
Spin rounds per wait: 1.14 mutex, 27.80 RW-shared, 47.51 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 9249107
Purge done for trx's n:o < 9248952 undo n:o < 0 state: running but idle
History list length 219
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 5647, OS thread handle 0x2b48a23c9700, query id 3720371 192.168.63.121 eMetric init
SHOW ENGINE INNODB STATUS

MySQL:SQL Server: WAIT / SLEEP Time Options

-- SQL Server
SELECT CURRENT_TIMESTAMP() ; -- CurrentTime
WAITFOR DELAY '00:00:05'  ; -- 5 Second Delay
SELECT CURRENT_TIMESTAMP() ; -- CurrentTime

-- MySQL
SELECT CURRENT_TIMESTAMP() ; -- CurrentTime
DO SLEEP(5); --  5 Second Delay
SELECT CURRENT_TIMESTAMP() ; -- CurrentTime

MySQL : Export Data through mysqldump

mysqldump -h amazonaws.cluster.us-east-1.rds.amazonaws.com -u sampleuser -p SampleDatabase > G:\OutputFolder\SampleDatabase.sql

MySQL : Export Schema without Data through mysqldump

mysqldump -h amazonaws.cluster.us-east-1.rds.amazonaws.com -u sampleuser -p --no-data SampleDatabase > G:\OutputFolder\SampleDatabase.sql

MySQL : Thread STATES
MySQL Thread States

SHOW FULL PROCESSLIST ;

When you see different states like SYSTEM LOCK , READING FROM NET , etc.

Setup MySQL Server as Linked Server in SQL Server/span>

Download MySQL ODBC Driver.
http://dev.mysql.com/downloads/file.php?id=453005

Good Article on Setting up MySQL Linked Server
SELECT * FROM OPENQUERY(LINKEDSERVERNAME, ‘SELECT * FROM MySQLSchemaName.MySQLTableName LIMIT 10’)

MySQL : HostName with Processes Count

SELECT SUBSTRING_INDEX(host, ':', 1) AS HostName,
       GROUP_CONCAT(DISTINCT USER)   AS UserName,
       COUNT(*) as Nbr_Processes
FROM   information_schema.processlist
GROUP  BY HostName
ORDER  BY COUNT(*),
          HostName;

MySQL : Open Tables and UNLOCK Tables

 show open tables where in_use>0;
  UNLOCK TABLES;

MySQL : Troubleshoot Lock wait timeout Issues

ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
show variables like '%isol%';
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation
AWS RDS Create and Change Parameter Groups

Creates a DB parameter group.
A DB parameter group is initially created with the default parameters for the database engine used by the DB instance. To provide custom values for any of the parameters, you must modify the group after creating it using rds-modify-db-parameter-group. Once you’ve created a DB parameter group, you need to associate it with your DB instance using rds-modify-db-instance. When you associate a new DB parameter group with a running DB instance, you need to reboot the DB instance for the new DB parameter group and associated settings to take effect.

http://docs.aws.amazon.com/AmazonRDS/latest/CommandLineReference/CLIReference-cmd-CreateDBParameterGroup.html

MySQL : Running SQL File from Command Line/span>
https://dev.mysql.com/doc/refman/5.7/en/mysql-batch-commands.html

mysql> \. ~/Kaizen_Sep_20_2015.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec

  503  mysqldump --help
  505  mysqldump -h xx.xxx.x.xx -u xxxxxx -p xxxxxx > ~/Kaizen_Sep_20_2015.sql
  517  mysqldump -h xx.xxx.x.xx  -u xxxxx -p xxxxx < Kaizen_Sep_20_2015.sql
  522  history | grep mysqldump

MySQL : Alter Table Column Name and Default Value

ALTER TABLE `TableName`
CHANGE COLUMN `OldColumnName` `NewColumnName` INT NOT NULL DEFAULT 0 COMMENT 'Processed Flag for Updating the BusinessRules'

MySQL : EXPLAIN with and without UnIndexed Column

explain SELECT MIN(ID) FROM  TableName WHERE Processed = 0

	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
	1	SIMPLE	StudentTestData_TAKS	ALL					80000695	Using where

 explain SELECT MIN(ID) FROM  TableName ;

	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
	1	SIMPLE								Select tables optimized away

MySQL : Quickly Change Transaction Isolation Level
http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html

/*
	@@GLOBAL.tx_isolation	@@tx_isolation
	READ-COMMITTED	READ-UNCOMMITTED
*/

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
SET GLOBAL tx_isolation='READ-COMMITTED';
SET SESSION tx_isolation='READ-COMMITTED';

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

/*
	@@GLOBAL.tx_isolation	@@tx_isolation
	READ-COMMITTED	READ-COMMITTED
*/

MySQL :Query General Query Log and Configs

 SET GLOBAL slow_query_log = 'ON';
 SET GLOBAL log_queries_not_using_indexes = 'ON';
 SET SESSION long_query_time = 1;
 SET SESSION min_examined_row_limit = 100;
 SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
 SHOW SESSION VARIABLES LIKE 'long_query_time';

select * from mysql.general_log
where event_time > DATE_ADD(current_timestamp(),INTERVAL -30 SECOND)
ORDER BY event_time desc;

MySQL : Number of Rows in Table

select * from information_schema.tables
where table_schema = 'ETLStage'
order by table_rows desc ;

MySQL Create Login and Grant Permissions

-- Create MySQL User
CREATE USER 'weblogin'@'%' IDENTIFIED BY 'SuperDuperPassword';

SHOW GRANTS FOR 'weblogin'@'%';

GRANT  EXECUTE ON Jira TO 'weblogin'@'%' ;

GRANT  SELECT, EXECUTE  ON Jira TO 'weblogin'@'%' ;