[MCOL-718]  Unable to perform the batch insert operation because DMLProc batchinsert with PID 140826 is currently holding the table lock for session 1548 Created: 2017-05-12  Updated: 2017-08-09  Resolved: 2017-08-09

Status: Closed
Project: MariaDB ColumnStore
Component/s: DDLProc, DMLProc
Affects Version/s: 1.0.8
Fix Version/s: 1.0.9

Type: Bug Priority: Major
Reporter: rambo Assignee: Unassigned
Resolution: Fixed Votes: 1
Labels: community
Environment:

Linux 3.10.0-514.10.2.el7.x86_64 #1 SMP Fri Mar 3 00:04:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux


 Description   

Sometime the table lock session has't auto released, Here is the error output:

SQLSTATE[HY000]: General error: 1815 Internal error: CAL0001: Insert Failed:  IDB-2009: Unable to perform the batch insert operation because DMLProc batchinsert
 
with PID 140826 is currently holding the table lock for session 1548.  2017-05-12 9:02:03^M
 
Raw SQL : INSERT INTO vstats_servers (iid,ip, ccons, cobj, CPU, cused, hit, loads, mem, reqs, rpc, rx, time, tpr, tx, uptime) VALUES (1301, '1.82.208.123', 0, 12
 
7, 0.71, 0, 0, '4.42,3.83,3.58', 11.27, 0, 1, 0, 1494550800, 0.11, 0, 3019065),(837, '111.11.4.237', 0, 2855610, 0.79, 100, 0, '1.49,0.68,0.47', 7.25, 0, 1, 0, 1
 
494550800, 0.13, 0, 2664775),(1143, '111.20.248.168', 0, 19108, 0.87, 0.68, 0, '1.34,1.62,1.80', 7.21, 0, 1, 0, 1494550800, 0.25, 0, 3019043),(1747, '113.207.31.
 
121', 0, 52, 0.88, 0, 0, '0.00,0.01,0.00', 11.98, 0, 1, 0, 1494550800, 0.14, 0, 3019065),(1101, '117.159.48.7', 0, 15782204, 0.87, 23.68, 0, '2.52,2.58,2.65', 19
 
.3, 0, 1, 0, 1494550800, 0.13, 0, 3019042),(1720, '117.169.88.45', 0, 28673, 1.35, 0.13, 0, '3.08,3.10,2.90', 17.68, 0, 1, 0, 1494550800, 0.14, 0, 3019066),(1454
 
, '117.25.158.139', 0, 4, 0.74, 0, 0, '1.45,1.71,1.82', 7.25, 0, 1, 0, 1494550800, 0.2, 0, 3019056),(1515, '121.14.157.49', 0, 342, 0.96, 0.01, 0, '0.80,1.06,1.1
 
0', 5.64, 0, 1, 0, 1494550800, 0.25, 0, 3019053),(1243, '150.138.166.248', 0, 7, 0.64, 0, 0, '2.24,2.73,2.66', 7.25, 0, 1, 0, 1494550800, 0.25, 0, 3019066),(933,
 
 '171.107.82.114', 0, 6, 0.8, 0, 0, '1.35,1.09,1.01', 7.26, 0, 1, 0, 1494550800, 0.33, 0, 3019052),(1152, '180.88.63.21', 0, 0, 0.87, 0, 0, '2.46,3.09,3.26', 7.4
 
9, 0, 1, 0, 1494550800, 0.17, 0, 1985885),(143, '183.6.204.16', 0, 60, 0.71, 0, 0, '2.98,3.12,3.17', 5.46, 0, 1, 0, 1494550800, 0.25, 0, 1968219),(1207, '211.91.
 
140.152', 0, 223, 0.79, 0.01, 0, '3.35,2.89,2.84', 7.25, 0, 1, 0, 1494550800, 0.25, 0, 3019066),(833, '218.6.154.171', 0, 3, 0.87, 0, 0, '3.04,2.40,2.60', 7.7, 0
 
, 1, 0, 1494550800, 0.25, 0, 3019061),(1278, '219.138.39.147', 0, 894, 1.03, 0.01, 0, '1.49,1.41,1.31', 9.64, 0, 1, 0, 1494550800, 0.11, 0, 2554177),(1372, '221.
 
12.140.196', 0, 0, 0.59, 0, 0, '0.94,1.54,1.74', 5.14, 0, 1, 0, 1494550800, 0.33, 0, 2139812),(1576, '221.204.173.200', 0, 1831, 0.79, 0.01, 0, '0.04,0.02,0.00',
 
 6.53, 0, 1, 0, 1494550800, 0.03, 0, 3019050),(826, '221.204.173.212', 0, 1508, 1.19, 0.01, 0, '4.23,4.65,4.79', 11.63, 0, 1, 0, 1494550800, 0.1, 0, 3019063),(20
 
20, '221.228.69.52', 12, 17513503, 3.25, 55.67, 0, '1.70,1.76,1.69', 59.89, 200, 2.02, 20640, 1494550800, 13.4, 33440, 43663),(838, '222.42.5.8', 0, 107, 0.71, 0
 
, 0, '0.89,1.22,1.14', 5.54, 0, 1, 0, 1494550800, 0.33, 0, 3019050),(1816, '58.222.48.20', 0, 3568, 0.87, 0.02, 0, '0.02,0.02,0.00', 12.67, 0, 1, 0, 1494550500,
 
0.13, 0, 3018852),(1541, '59.49.43.56', 0, 1947, 1.03, 0.02, 0, '4.22,4.18,3.92', 9.65, 0, 1, 0, 1494550800, 0.17, 0, 3019052),(834, '59.49.43.62', 0, 2807, 1.11
 
, 0.03, 0, '4.86,4.41,4.20', 21.08, 0, 1, 0, 1494550800, 0.14, 0, 3019053),(1359, '60.217.25.205', 0, 774, 0.81, 0.03, 0, '2.28,1.79,1.59', 7.25, 0, 1, 0, 149455
 
0800, 0.11, 0, 3019066);

Attachments are log files FYI.

columnstoreSupportReport3.tgz

 Comments   
Comment by David Thompson (Inactive) [ 2017-05-12 ]

This likely is the start / cause of the lock /problem:

May 10 12:11:06 utn-cz-1-1-s16h1 writeengineserver[140719]: 06.701869 |0|0|0| E 32 CAL0017: DML Starting HWMs do not match for OID1-3563; DBRoot-1; partition-0; segment-3; hwm-4378; width-4:#012 and OID2-0; DBRoot-1; partition-0; segment-0; hwm-0; width-4
 
May 10 12:11:06 utn-cz-1-1-s16h1 dmlpackageproc[140826]: 06.727460 |0|0|0| E 21 CAL0001: Insert Failed:   HWMs for same width columns not equal.  Check err.log for detailed information.
 
May 10 13:41:04 utn-cz-1-1-s16h1 writeengineserver[140719]: 04.046524 |0|0|0| E 32 CAL0017: DML Starting HWMs do not match for OID1-3563; DBRoot-1; partition-0; segment-3; hwm-4437; width-4:#012 and OID2-0; DBRoot-1; partition-0; segment-0; hwm-0; width-4
 
May 10 13:41:04 utn-cz-1-1-s16h1 dmlpackageproc[140826]: 04.072445 |0|0|0| E 21 CAL0001: Insert Failed:   HWMs for same width columns not equal.  Check err.log for detailed information.
 
May 10 20:46:04 utn-cz-1-1-s16h1 writeengineserver[140719]: 04.061548 |0|0|0| E 32 CAL0017: DML Starting HWMs do not match for OID1-3563; DBRoot-1; partition-0; segment-3; hwm-4760; width-4:#012 and OID2-0; DBRoot-1; partition-0; segment-0; hwm-0; width-4
 
May 10 20:46:04 utn-cz-1-1-s16h1 dmlpackageproc[140826]: 04.090672 |0|0|0| E 21 CAL0001: Insert Failed:   HWMs for same width columns not equal.  Check err.log for detailed information.

Comment by rambo [ 2017-05-12 ]

When i restart service via mcsadmin, this error does not appear。

Comment by Andrew Hutchings (Inactive) [ 2017-05-12 ]

Hi rambo,

As theorised in MCOL-676 these appear to be caused by your continuous mysqld crashes in MCOL-679. We have just released MariaDB ColumnStore 1.0.9 that contains a fix for MCOL-679 so can you please try that version?

Comment by rambo [ 2017-05-13 ]

ok, i will try later, tks

Comment by David Thompson (Inactive) [ 2017-06-05 ]

Hi, were you able to retest with 1.0.9 to see if this resolves the issue?

Comment by Kleyson Rios [ 2017-06-09 ]

I'm trying to use kettle 'Output table' step in Batch insert mode and not working.

If I set batch size = 100 or 100 no record is stored on the table. If the batch size = 10 records as stored in a rate of 1 r/s.

is there any debug log where I could grab some information to help on this ?

Comment by David Thompson (Inactive) [ 2017-06-09 ]

kleysonr - yes you should check the logs under /var/log/mariadb/columnstore. Also check /usr/local/mariadb/columnstore/db/<system>.err in case it is a sql parsing issues. I assume there is nothing useful in the kettle logs?

You could also provide us the complete logs and system info via the columnstoreSupport utility, this link covers logs and that:
https://mariadb.com/kb/en/mariadb/system-troubleshooting-mariadb-columnstore/

It would also be better creating a new jira in case these are distinct issues (we can always close as a dupe later).

If you are trying to use an ETL tool it may be better setting it up to output csv files and then use cpimport as this will be a lot faster for bulk inserts.

Comment by David Thompson (Inactive) [ 2017-08-09 ]

Please re-open if this is still an issue with 1.0.9 or later

Generated at Thu Feb 08 02:23:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.