Is it locking because it has to check the uniqueness of that col before update?

|
Amit 2021-08-19 07:18:38
https://youtu.be/J7gxXMKrOCo

Connecting PHP with MYSQL Database | PHP TutorialsYouTube
In this video you will learn connecting php with mysql database. i will explain procedural method to connecting with mysql database. In php there are 2 methods to connect to the mysql database.

Connect mysql database in php in hindi – MUST watch PHP tutorial for beginners

1. Mysqli extension
2. PDO (PHP Data object)

#phptutorial #phpprogramming #php

Please subscribe “Babaji Technical” for more videos.

Follow me on Instagram:

https://www.instagram.com/tech_amit27/

#PHP #PHPTutorial
#BabajiTechnical

MasterZiv 2021-08-19 08:15:00
madno 2021-08-19 05:38:10
Hi found out the culprit, in that whole transaction , following queries are executed
1. Begin
2. Insert into wallet table(eg- id 123)
3. Insert into table2
4. Update wallet where pk=123
5. Commit

So I assume update is locking the rows. Is it locking because it has to check the uniqueness of that col before update?

No update just has to lock rows it processes. This is normal.

Tell me please how locks are relates to your problem?

Yes update locks, insert locks, but this is normal and not a problem.
The problem is as I can see that your slave is far behind the master.

Let it work for a longer time and see the tendency what it is:is it going to catch up the master or the opposite.
Look at the situation in a long run.
If the slave stays out of sync even when the master doesn’t do transactions intensively, then this is a problem.

madno 2021-08-19 08:24:47
MasterZiv 2021-08-19 08:15:00
No update just has to lock rows it processes. This is normal.

Tell me please how locks are relates to your problem?

Yes update locks, insert locks, but this is normal and not a problem.
The problem is as I can see that your slave is far behind the master.

Let it work for a longer time and see the tendency what it is:is it going to catch up the master or the opposite.
Look at the situation in a long run.
If the slave stays out of sync even when the master doesn’t do transactions intensively, then this is a problem.

I failed to understand why there is locks for all rows.
1. Begin
2. Insert into wallet without populating UK column.
3. Insert into table2
4. Update UK column in wallet table
5. Commit.
What i did see that at some point slave is executing updates but it is slow because of locks. So inturn slave lags keeps on increasing

madno 2021-08-19 08:26:21
MasterZiv 2021-08-19 08:15:00
No update just has to lock rows it processes. This is normal.

Tell me please how locks are relates to your problem?

Yes update locks, insert locks, but this is normal and not a problem.
The problem is as I can see that your slave is far behind the master.

Let it work for a longer time and see the tendency what it is:is it going to catch up the master or the opposite.
Look at the situation in a long run.
If the slave stays out of sync even when the master doesn’t do transactions intensively, then this is a problem.

mysql_en-12164.jpg
Straight lag decline is when I removed partition from the table.

MasterZiv 2021-08-19 08:55:01
madno 2021-08-19 08:24:47
I failed to understand why there is locks for all rows.
1. Begin
2. Insert into wallet without populating UK column.
3. Insert into table2
4. Update UK column in wallet table
5. Commit.
What i did see that at some point slave is executing updates but it is slow because of locks. So inturn slave lags keeps on increasing

Probably you see several transactions…
Locks are not problem by itself.

MasterZiv 2021-08-19 08:56:44
madno 2021-08-19 08:26:21
Straight lag decline is when I removed partition from the table.

well , send exact partition definition, it was wrong in the latest info from you…

Deb_575 2021-08-19 10:24:20
Ishu Gupta 2021-08-19 05:15:25
Hi when I fired a select query on a table it shows table is marked as crashed and should be repaired table storage engine is archive and repair table command also not worked

If you have backup then you can restore the table.

Ishu Gupta 2021-08-20 05:09:32
Deb_575 2021-08-19 10:24:20
If you have backup then you can restore the table.

That I already done but my question is why my table got crashed I need RCA of it

Deb_575 2021-08-20 22:15:24
Have you checked logs?
I think there might be some data corruption
MasterZiv 2021-08-20 23:30:08
Ishu Gupta 2021-08-20 05:09:32
That I already done but my question is why my table got crashed I need RCA of it

A table may be corrupted by many reasons…

lynevevl 2021-08-22 15:38:51
mysql_en-12209.jpg
hi does anyone know how to get the difference in number of days between the ‘start_date’ and ‘end_date’ ?
lynevevl 2021-08-22 15:39:35
I’ve tried to use DATEDIFF but it didn’t work
MasterZiv 2021-08-22 15:53:04
lynevevl 2021-08-22 15:39:35
I’ve tried to use DATEDIFF but it didn’t work

It should work!

lynevevl 2021-08-22 15:54:56
mysql_en-12212.jpg
it didn’t work. It shown me this error code 1582, incorrect parameter
smlkw 2021-08-22 15:56:26
lynevevl 2021-08-22 15:54:56
it didn’t work. It shown me this error code 1582, incorrect parameter

look into the docs on this function, it seems like you are calling it in a wrong way

lynevevl 2021-08-22 15:57:27
smlkw 2021-08-22 15:56:26
look into the docs on this function, it seems like you are calling it in a wrong way

Ooo ok you mean the DATEDIFF(day, start_date, end_date) is incorrect?

smlkw 2021-08-22 15:58:01
lynevevl 2021-08-22 15:57:27
Ooo ok you mean the DATEDIFF(day, start_date, end_date) is incorrect?

yes

MasterZiv 2021-08-22 15:58:09
lynevevl 2021-08-22 15:54:56
it didn’t work. It shown me this error code 1582, incorrect parameter

If you don’t show the code you can complain for long long time spirit any help…

smlkw 2021-08-22 15:58:26
MasterZiv 2021-08-22 15:58:09
If you don’t show the code you can complain for long long time spirit any help…

the code is shown above on on the screenshot

lynevevl 2021-08-22 15:59:01
lynevevl 2021-08-22 15:54:56
it didn’t work. It shown me this error code 1582, incorrect parameter

The code is located above

MasterZiv 2021-08-22 15:59:40
lynevevl 2021-08-22 15:59:01
The code is located above

Why didn’t you show it FIRST?

lynevevl 2021-08-22 16:00:16
mysql_en-12220.jpg

smlkw 2021-08-22 16:00:19
MasterZiv 2021-08-22 15:59:40
Why didn’t you show it FIRST?

Ilia, ‘mon 🙂

smlkw 2021-08-22 16:00:49
lynevevl 2021-08-22 16:00:16

remove the first argument, DATEDIFF takes only TWO args

MasterZiv 2021-08-22 16:01:14
lynevevl 2021-08-22 15:57:27
Ooo ok you mean the DATEDIFF(day, start_date, end_date) is incorrect?

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

lynevevl 2021-08-22 16:01:26
smlkw 2021-08-22 16:00:49
remove the first argument, DATEDIFF takes only TWO args

Ahh ok

MasterZiv 2021-08-22 16:02:20
smlkw 2021-08-22 16:00:49
remove the first argument, DATEDIFF takes only TWO args

No, three, but “day” goes last

lynevevl 2021-08-22 16:02:35
mysql_en-12226.jpg
thanks @MasterZiv i think it shown the total count of days now
lynevevl 2021-08-22 16:03:18
thanks @smlkw your tips helped
lynevevl 2021-08-22 16:03:33
i’ve removed the first argument and it worked for DATEDIFF
smlkw 2021-08-22 16:04:19
MasterZiv 2021-08-22 16:02:20
No, three, but “day” goes last

mysql_en-12229.jpg

smlkw 2021-08-22 16:04:23
it seems only two
smlkw 2021-08-22 16:04:33
lynevevl 2021-08-22 16:03:18
thanks @smlkw your tips helped

you’re welcome

lynevevl 2021-08-22 16:04:39
smlkw 2021-08-22 16:04:23
it seems only two

Yes only 2

Manjunath Dharmatti 2021-08-24 14:13:55
Pls suggest training videos for learning SQL. I am a beginner.
|