| Bug #60630 | Commit but data not in the database | ||
|---|---|---|---|
| Submitted: | 25 Mar 2011 3:44 | Modified: | 26 Apr 2012 19:09 |
| Reporter: | newbie Shai | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | Mysql 5.1.46 and mysqlconnectornet-6.2.3 | OS: | Windows (Windows XP) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Data no in the database | ||
[25 Mar 2011 6:40]
Valeriy Kravchuk
Please, give more details about the application and environment you are working with. What client do you use to execute queries? Is it some IDE, like VS, or MySQL Workbench? We clearly see the following: 110320 17:15:59 137 Connect root@175.141.14.162 on mpcms 137 Query SET autocommit=0 110320 17:16:01 137 Query SHOW VARIABLES 110320 17:16:09 137 Query SHOW COLLATION 110320 17:16:12 137 Query SET character_set_results=NULL 110320 17:16:14 137 Init DB mpcms 110320 17:16:16 137 Query KILL QUERY 136 110320 17:16:17 137 Quit Some tool (I doubt humans start session with SHOW VARIABLES) connected as root user and executed KILL statement, then quit immediately.
[25 Mar 2011 15:05]
newbie Shai
Dear Valeriy,
As you know .net connector could not support distributed transaction. So my application has a local db and central db and in the cental server a copy of the local db too. So it is C# windows application. So once I do a transaction first it will insert into local db a row and at the same insert the query in the updateCentral and updateCentralCopy tables. So then I have a window service which will go through both this table to check any thing to update central db and local copy db. If there is it will read the query run and delete from the respective local tables but again there is chance for the distributed query to fail too. Besides this program nobody else have any access to it. Hope I am clear now.
[26 Mar 2011 16:47]
newbie Shai
Dear Valeriy,
Here is another case as below.All the query are ok but except for this query is not in the db "Insert into tblTransferDetails Set transferDetailsID = 872, transferID=621, outletID = 6, stockID = 5476, productID= 5859, productType = 'Imei', stockQuantity = 1, stockSIQ = '354910048787727', costPrice = 952, transferPrice = 952
110320 16:41:26". Any idea why is this happening?
83 Query SET autocommit=0
110320 16:40:24 83 Query SHOW VARIABLES
110320 16:40:31 83 Query SHOW COLLATION
110320 16:40:36 83 Query SET character_set_results=NULL
110320 16:41:08 83 Init DB mpcms
110320 16:41:09 83 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
110320 16:41:10 83 Query BEGIN
110320 16:41:11 84 Connect root@118.101.121.96 on mpcms11
84 Query SET autocommit=0
83 Query Insert into tblReceipt Set receiptID=4126, outletID=6, employeeID=150, customerName='acip', customerPhone='0163831458', totalCostAmount=184.92, totalSellingAmount=350, totalProfitAmount=165.08, changeAmount=0, profit='y', receiptDate = '2011:03:20', receiptTime = '16:20:58'
110320 16:41:12 84 Query SHOW VARIABLES
110320 16:41:13 83 Query Insert into tblReceiptDetails Set receiptID=4126, receiptDetailsID = 7187, outletID = 6,stockID = 5668, productID= 1557, productType = 'Imei', productQuantity = 1 , productSIQ = '351872028125554', costPrice = 177.91, sellingPrice = 350, profitAmount = 172.09, profit = 'y'
110320 16:41:14 83 Query Insert into tblTransaction Set transactionID = 4126, transactionDetailsID=7187, stockID = 5668, productID= 1557, outletFromID = 6, outletToID = 6, totalAmountBefore = 177.91, totalAmountAfter = 0, totalQuantityBefore = 1, totalQuantityAfter = 0, averageCostBefore = 177.91, averageCostAfter = 177.91, quantity = 1, costPrice = 177.91, transactionPrice = 350, transactionEmployeeID = 150, transactionDate='2011:03:20', transactionTime='16:20:58', transactionType = 's'
110320 16:41:15 83 Query Update tblProduct Set tblProduct.branch6TotalStock = tblProduct.branch6TotalStock -1, tblProduct.branch6TotalAmount = tblProduct.branch6TotalAmount -177.91 Where tblProduct.productID=1557
85 Connect root@175.141.14.162 on mpcms
85 Query SET autocommit=0
110320 16:41:16 83 Query Update tblStockCentral Set tblStockCentral.stockStatus = 's' Where tblStockCentral.stockSIQ='351872028125554' And tblStockCentral.stockID=5668 And tblStockCentral.outletID=6
85 Query SHOW VARIABLES
110320 16:41:17 83 Query Insert into tblReceiptDetails Set receiptID=4126, receiptDetailsID = 7188, outletID = 6,stockID = 5012, productID= 1475, productType = 'Accessory', productQuantity = 1, productSIQ = '1', costPrice = 7.01, sellingPrice = 0, profitAmount = -7.01, profit = 'n', stockStatus ='g'
110320 16:41:18 85 Query SHOW COLLATION
83 Query Insert into tblTransaction Set transactionID = 4126, transactionDetailsID=7188, stockID = 5012, productID= 1475, outletFromID = 6, outletToID = 6, totalAmountBefore = 35.05, totalAmountAfter = 28.04, totalQuantityBefore = 5, totalQuantityAfter = 4, averageCostBefore = 7.01, averageCostAfter = 7.01, quantity = 1, costPrice = 7.01, transactionPrice = 0, transactionEmployeeID = 150, transactionDate='2011:03:20', transactionTime='16:20:58', transactionType = 'g'
110320 16:41:19 85 Query SET character_set_results=NULL
110320 16:41:22 83 Query Update tblProduct Set tblProduct.branch6TotalStock = tblProduct.branch6TotalStock -1, tblProduct.branch6TotalAmount = tblProduct.branch6TotalAmount -7.01 Where tblProduct.productID=1475
110320 16:41:23 85 Init DB mpcms
83 Query Insert into tblPaymentDetails Set paymentDetailsID=4204, outletID = 6,receiptID = 4126, paymentTypeID= 1, amount = 350
110320 16:41:24 85 Query KILL QUERY 83
110320 16:41:25 83 Query Insert into tblTransferDetails Set transferDetailsID = 872, transferID=621, outletID = 6, stockID = 5476, productID= 5859, productType = 'Imei', stockQuantity = 1, stockSIQ = '354910048787727', costPrice = 952, transferPrice = 952
110320 16:41:26 85 Quit
83 Query Update tblProduct Set tblProduct.branch6TotalStock = tblProduct.branch6TotalStock -1, tblProduct.branch6TotalAmount = tblProduct.branch6TotalAmount -952 Where tblProduct.productID=5859
110320 16:41:28 83 Query Update tblStockCentral Set tblStockCentral.stockStatus = 't' Where tblStockCentral.stockSIQ='354910048787727' And tblStockCentral.stockID=5476 And tblStockCentral.outletID=6
110320 16:41:29 83 Query Insert into tblTransaction Set transactionID = 621, transactionDetailsID=872, stockID = 5476, productID= 5859, outletFromID = 6, outletToID = 3, totalAmountBefore = 952, totalAmountAfter = 0, totalQuantityBefore = 1, totalQuantityAfter = 0, averageCostBefore = 952, averageCostAfter = 952, quantity = 1, costPrice = 952, transactionPrice = 952, transactionEmployeeID = 4, transactionDate='2011:03:20', transactionTime='16:21:51', transactionType = 'to'
110320 16:41:31 84 Query SHOW COLLATION
83 Query Insert into tblTransfer Set transferID=621, outletFromID=6, outletToID=3, employeeFromID=4, totalCostAmount=952, totalTransferAmount=952, totalProfitAmount=0, transferFromDate='2011:03:20', transferFromTime='16:21:51'
110320 16:41:33 83 Query COMMIT
110320 16:41:34 83 Quit
[26 Mar 2011 17:45]
Valeriy Kravchuk
The question is: where KILL QUERY comes from (in your application/environment). Look the these statements in the general query log: 110320 16:41:23 85 Init DB mpcms 83 Query Insert into tblPaymentDetails Set paymentDetailsID=4204, outletID = 6,receiptID = 4126, paymentTypeID= 1, amount = 350 110320 16:41:24 85 Query KILL QUERY 83 110320 16:41:25 83 Query Insert into tblTransferDetails Set transferDetailsID = 872, transferID=621, outletID = 6, stockID = 5476, productID= 5859, productType = 'Imei', stockQuantity = 1, stockSIQ = '354910048787727', costPrice = 952, transferPrice = 952 110320 16:41:26 85 Quit and read http://dev.mysql.com/doc/refman/5.1/en/kill.html: "When you use KILL, a thread-specific kill flag is set for the thread." When thread 83 get's the very next query, your problematic INSERT, it logs it to the general query log, checks kill flag, kills the query execution and continue... The following queries are executed successfully, and then committed. There is no bug here. There is only the question: where KILL QUERY 83 statement comes from. It does NOT come from server itself, it comes from your application/environment.
[27 Mar 2011 16:41]
newbie Shai
Dear Valeriy,
Attached is snippet of my full codes. The think I dont see any kill query being executed via my application so that surprises where it comes from? Is it possible from my codes below to auto generate kill query? Thank you.
[26 Apr 2012 19:09]
Sveta Smirnova
Thank you for the feedback. This is surely not full code: it does not access to mpcms database like connection which killed query, neither issues Insert like killed connection did. What you ask is free support which we are not allowed to provide here. Your problem is somewhere in your environment, not in MySQL code.
Description: Dear All, Below is the snippet from my log file. If you notice first this happened 110320 17:16:14 137 Init DB mpcms 110320 17:16:16 137 Query KILL QUERY 136? Can anyone explain to me how can it automatically kill another query when I never inniate the query is this a bug? What I notice all the queries link to 136 is in the db even the first which was killed except for this one 110320 17:17:30 136 Query Insert into tblReceiptDetails Set receiptID=5186, receiptDetailsID = 10672, outletID = 3,stockID = 9886, productID= 5387, productType = 'Imei', productQuantity = 1 , productSIQ = '356785040505824', costPrice = 641.58, sellingPrice = 700, profitAmount = 58.42, profit = 'y'. What bug is this? 110320 17:14:48 136 Connect root@175.141.14.162 on mpcms 136 Query SET autocommit=0 110320 17:14:50 136 Query SHOW VARIABLES 110320 17:14:55 136 Query SHOW COLLATION 110320 17:15:06 136 Query SET character_set_results=NULL 110320 17:15:07 136 Init DB mpcms 110320 17:15:12 136 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 110320 17:15:22 136 Query BEGIN 110320 17:15:40 136 Query Insert into tblReceipt Set receiptID=5186, outletID=3, employeeID=68, customerName='siti farahiyah', customerPhone='0194513125', totalCostAmount=641.58, totalSellingAmount=700, totalProfitAmount=58.42, changeAmount=0, profit='y', receiptDate = '2011:03:20', receiptTime = '16:52:29' 110320 17:15:59 137 Connect root@175.141.14.162 on mpcms 137 Query SET autocommit=0 110320 17:16:01 137 Query SHOW VARIABLES 110320 17:16:09 137 Query SHOW COLLATION 110320 17:16:12 137 Query SET character_set_results=NULL 110320 17:16:14 137 Init DB mpcms 110320 17:16:16 137 Query KILL QUERY 136 110320 17:16:17 137 Quit 110320 17:17:30 136 Query Insert into tblReceiptDetails Set receiptID=5186, receiptDetailsID = 10672, outletID = 3,stockID = 9886, productID= 5387, productType = 'Imei', productQuantity = 1 , productSIQ = '356785040505824', costPrice = 641.58, sellingPrice = 700, profitAmount = 58.42, profit = 'y' 136 Query Insert into tblTransaction Set transactionID = 5186, transactionDetailsID=10672, stockID = 9886, productID= 5387, outletFromID = 3, outletToID = 3, totalAmountBefore = 641.58, totalAmountAfter = 0, totalQuantityBefore = 1, totalQuantityAfter = 0, averageCostBefore = 641.59, averageCostAfter = 641.59, quantity = 1, costPrice = 641.58, transactionPrice = 700, transactionEmployeeID = 68, transactionDate='2011:03:20', transactionTime='16:52:29', transactionType = 's' 136 Query Update tblProduct Set tblProduct.branch3TotalStock = tblProduct.branch3TotalStock -1, tblProduct.branch3TotalAmount = tblProduct.branch3TotalAmount -641.58 Where tblProduct.productID=5387 136 Query Update tblStockCentral Set tblStockCentral.stockStatus = 's' Where tblStockCentral.stockSIQ='356785040505824' And tblStockCentral.stockID=9886 And tblStockCentral.outletID=3 136 Query Insert into tblPaymentDetails Set paymentDetailsID=5475, outletID = 3,receiptID = 5186, paymentTypeID= 1, amount = 700 136 Query COMMIT 110320 17:17:31 136 Quit How to repeat: Have not seen any repeats yet. Suggested fix: Why is the kill query happen?