ER_LOCK_WAIT_TIMEOUT issue resolved , dead lock transaction mysql

 Hey guys,

Today, i faced an issue with transaction getting dead locked in mysql. i was unable to perform any action on the locked table. 

I created a transaction with autocommit : false and forgot to either rollbock or commit the transaction.

At the same time another transaction been created and tried to access the same table and final result is 

ER_LOCK_WAIT_TIMEOUT: Lock wait timeout exceeded; try restarting transaction

Solution:

--------------

SHOW PROCESSLIST 

See if your query is present in the processes and kill the <id> which causing the table lock.

KILL <processid>

Above command did not help me as process or query id is not present to overcome the problem. 

Dig in more to check the transactions list and try to kill the one that got dead locked.

First  , let's get some Engine status. i m using INNODB engine for Mysql.

SHOW ENGINE INNODB STATUS

This will result us with below sample output. 

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5367
OS WAIT ARRAY INFO: signal count 4706
RW-shared spins 0, rounds 2666, OS waits 728
RW-excl spins 0, rounds 1878, OS waits 44
RW-sx spins 34, rounds 217, OS waits 4
Spin rounds per wait: 2666.00 RW-shared, 1878.00 RW-excl, 6.38 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 418476
Purge done for trx's n:o < 418416 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421985361275640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421985361274720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------

In the list of transactions , if you any lock struct(s) more than 0 then you will get it with query ID and thread id 

ex: MySQL thread id 197, query id 771 localhost

Now we need to kill the thread thats causing the dead lock situation with KILL command. 

KILL 197  

If you want to kill the query then KILL QUERY 771  followed by KILL 197 

Thus, we can overcome to transaction dead lock and Er_lock_wait_time_timeout error. 

if you really want to Dig in more on this then below link will help you :

https://severalnines.com/database-blog/how-fix-lock-wait-timeout-exceeded-error-mysql

MYSQL: ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Hey guys,

Recently when we run one of our query with group by . we encountered an MYSQL error as like below:

ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column <column name> which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

When we dig in deeper, we came to know that we have enabled only_full_group_by  which is by default in MySQL 5.7.5 and later. 

For example. Query

select name , phone_number, count(*)  from contact 

group by phone_number ;

contact table :

----------------------

  name     phone_number

   Raj       123

   Ram     123 

   Karan    456

See above table has 2 names pointing to same number 123 . so when we group by the data with phone_number it tries to count no . of records based on phone_number.

Now when we keep the name in the selection list . MYSQL has to pick only one name to show to user . 

System will confuse which name to pick Raj / Ram . 

so , when we enable only_full_group_by . we are letting mysql to not accept queries with functional independence in the selection list.  if they are functionally dependent or primary key then it will work. 

To overcome above problem :  2 solutions

Solution 1: 

   Disable only_full_group_by . follow below link 

     https://stackoverflow.com/questions/23921117/disable-only-full-group-by

Solution 2:

    ANY_VALUE()  function for non-agggregated columns in the selection list. By using this, system picks any one name out of multiple values. 

     select any_value(name) , phone_number, count(*)  from contact 

group by phone_number ; 

Please don't keep the non-aggregated columns in selection list at any cost. If you think its necessary then follow above steps to overcome your problem 

More on only_full_group_by

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Angular service Re-initialised / Angular service instantiated many times

 Hey Guys,

Recently i came cross a problem with Angular 2 + service  constructor got called multiple times. 

Generally , Angular services are singleton meaning it will be called / instantiated only once across the app if the scope of injection is root. 

If we want to re-create / call constructor mutliple times then we have to keep the service in the specific module PROVIDERS. When we specify in providers then the service will be re-created for that particular module you specified. 

The issue i faced:

 > we have the service with  root injectable.  it clearly states that the service available across the root. 

@Injectable({
  providedIn: 'root'
})

export class TestService

But we kept the service in Providers for particular module say  TestModule

providers: [
    TestService
  ]
This causing the service to be called many times and all the data inside service is re-freshed. 

so, when u keep the service at injectable root then dont put it in providers until its needed or necessary.