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 ;
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.
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'@'%' ;
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;