Join of 3 datasources with a calculated datasource failing with deadlock

Question

I had this code (which worked fine) in a calculated database that joined two SQL datasources:

select m.Email, m.SkuID,m.SkuName,l.Email,l.LastLogin,l.DocsAdded
from Licenses m
inner join ActivityReport l on l.Email = m.Email

Due to a bug in the reports API that provides incorrect information on LastLogin time information, I'm using the Users.get API and writing that information a new datasource (just the email of the user and their last login time). I want to join this with my other two data sources. Using the below code:

select m.Email, m.SkuID,m.SkuName,l.Email,l.DocsAdded,x.Email,x.LastLogin as LastLogin
from Licenses m
inner join ActivityReport l on l.Email = m.Email
inner join LastLogin x on x.Email = l.Email

I get the error:

License save batch failed: JDBC backend failure. More information: Failed to execute connection with error: Deadlock found when trying to get lock; try restarting transaction

I also get two errors that looks like this: License save batch failed: Malformed SQL. More information: Error with SQL statement: Duplicate entry 'user@email.com' for key 'PRIMARY'.

What am I doing wrong?

I take it you are on Oracle database. You may want to edit the tags to attract right users to help you, just a suggestion. — junketsu, Nov 19 '19 at 20:55
I'm using the default DB in AppMaker which is Google's CloudSQL — Ian Hyzy, Nov 19 '19 at 20:57
You are having deadlock issue in your database. you can use "show engine innodb status" to better diagonise this problem. Also, I suggest you check this [question](https://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans) on stackoverflow. It's a full discussion about deadlock and how to avoid them. — Methkal Khalawi, Nov 20 '19 at 14:40

score 0 · Answer 1 · answered Nov 27 '19 at 16:34

0

I'm thinking you might need to add 'as' in your inner join unless you have already declared this in part of your code that was not posted.

from Licenses **as** m   
inner join ActivityReport **as** l on l.Email = m.Email
inner join LastLogin **as** x on x.Email = l.Email

The second error is saying that you have more than one entry that your trying to join on. So you might consider trying to join on something that has unique entries to ensure you don't have duplicates in your join.

answered Nov 27 '19 at 16:34

NewCoder04

19
8

I get a syntax error when I try to use this: `Exception: Malformed SQL. More information: Error with SQL statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from Licenses as m inner join ActivityReport as l on l.Email = m.Email inner ' at line 1.` – Ian Hyzy Dec 02 '19 at 04:30

score 0 · Answer 2 · answered Nov 28 '19 at 10:19

There is no issue in your query , query is right but this is deadlock issue ,So when many threads are running on server ,for example:

Deadlock is a situation where a set of processes are blocked because each process is holding a resource and waiting for another resource acquired by some other process. So In case of deadlock, system automatically cancelled less expensive query.

If you try after sometime there will be no issue and therefore no error prompt.

Join of 3 datasources with a calculated datasource failing with deadlock

2 Answers2