iamitshri/db-notes

Mysql Code snippets

https://dba.stackexchange.com/questions/195018/mysql-safe-to-kill-transaction-that-is-stuck-in-waiting-for-table-metadata-loc https://stackoverflow.com/questions/13148630/how-do-i-find-which-transaction-is-causing-a-waiting-for-table-metadata-lock-s

SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;
select @@foreign_key_checks;
select @@global.foreign_key_checks;
SET GLOBAL FOREIGN_KEY_CHECKS=0;
select @@foreign_key_checks

https://stackoverflow.com/questions/13148630/how-do-i-find-which-transaction-is-causing-a-waiting-for-table-metadata-lock-s

Adding indexes on the existing giant table taking too long?

• https://stackoverflow.com/questions/25191113/alter-table-query-taking-too-long-for-adding-constraint

• https://stackoverflow.com/questions/15501673/how-can-i-temporarily-disable-a-foreign-key-constraint-in-mysql

 -- add primary key and auto increment
ALTER TABLE test_table modify id bigint NOT NULL AUTO_INCREMENT;

-- add unique constraint
alter table test_table add UNIQUE `assessment_code` (`assessment_code`);
ALTER TABLE test_table ADD UNIQUE (columnName);
ALTER IGNORE TABLE test_table ADD UNIQUE (columnName);

-- add index constraint
ALTER TABLE test_table ADD INDEX (Email,Email2);
ALTER TABLE test_table ADD INDEX (c);
ALTER TABLE test_table drop index name;

-- add foreign key
ALTER TABLE test_table ADD CONSTRAINT referral_ibfk_2 FOREIGN KEY (evaluation_id) REFERENCES `evaluation` (`evaluation_id`);

-- adding composite primary key
ALTER TABLE test_table ADD PRIMARY KEY(person,place,thing);

-- show  table definition
SHOW CREATE TABLE test_table

Find hanged queries

select * From information_schema.processlist;
Select concat('KILL ',id,';') from information_schema.processlist where user='migration_user';
show processlist;
kill 1916;

 USE INFORMATION_SCHEMA;
 SELECT * FROM information_schema.INNODB_LOCK_WAITS;
Select concat('KILL ',id,';') from information_schema.processlist where user='migration_user';

select * From information_schema.processlist;
Select concat('KILL ',id,';') from information_schema.processlist where user='migration_user';

  SHOW VARIABLES LIKE 'max_heap_table_size'; -- 16777216
  SHOW VARIABLES LIKE 'tmp_table_size';-- 16777216

References

• https://tableplus.com/blog/2018/08/mysql-how-to-temporarily-disable-foreign-key-constraints.html