Mysql find duplicate records in a table for particular column — June 20, 2017

Mysql find duplicate records in a table for particular column

SELECT *, COUNT(*) c FROM table GROUP BY column HAVING c > 1;
Advertisements
Lock wait timeout exceeded try restarting transaction in mysql — May 30, 2017

Lock wait timeout exceeded try restarting transaction in mysql

You can set variable innodb_lock_wait_timeout=100 for lock time to 100 sec.

mysql> set innodb_lock_wait_timeout=100

Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 100   |
+--------------------------+-------+

The transaction which is timeout, try to lock table which is hold by another process. and your timeout variable set with little number of second. so it shows error. You can see more status by the command.

SHOW ENGINE INNODB STATUS\G 

You can see list of locked tables by-

 show open tables where in_use>0;

Now see the thread which is using this table

  show full processlist;

now you can kill that thread or wait to complete it.

To get a list of the threads you want to kill off.

SELECT CONCAT('KILL ',id,';') AS run_this FROM information_schema.processlist

You now have a list of the threads you want to kill off.

Add foreign key to existing mysql table — February 27, 2017

Add foreign key to existing mysql table

Example to add multiple foreign keys in one go:–

ALTER TABLE users_role_map

ADD CONSTRAINT FK_users_role_map1
FOREIGN KEY (email) REFERENCES usert(email)
ON UPDATE CASCADE
ON DELETE CASCADE,

ADD CONSTRAINT FK_users_role_map2
FOREIGN KEY (id) REFERENCES rolet(id)
ON UPDATE CASCADE
ON DELETE CASCADE;

Example to add one foreign key:–

ALTER TABLE users_role_map

ADD CONSTRAINT FK_users_role_map1
FOREIGN KEY (email) REFERENCES usert(email)
ON UPDATE CASCADE
ON DELETE CASCADE

You can skip ‘ADD CONSTRAINT FK_users_role_map1’ than mysql will do that for you :).