Table lock not cleared with cleartablelock

[alimajaf ALI]

Hi All,

I am new to ColumnStore world. 

I have installed MariaDB columnStore 1.0.7 in a single server.  I have InnoDB tables and ColumnStore tables in separate schema. InnoDB is a slave replicated from another MySQL instance. ColumnStore has subset of InnoDB tables which is periodically refreshed from InnoDB tables using cpimport.

Last night I have faced a issue, where I was not able to connect to the database. with error

 Internal error: IDB-2004: Cannot connect to ExeMgr.

I tried restarting columnstore, but it was not stopping

stopsystem   Fri Apr 28 18:39:52 2017

This command stops the processing of applications on all Modules within the MariaDB ColumnStore System

   Checking for active transactions

The following tables are locked:

Could not connect to PMS1: Connection refused

Could not connect to PMS1: Connection refused

Could not connect to PMS1: Connection refused

Could not connect to PMS1: Connection refused

So I bounced the server. Now the ColumnStore is up but readonly. 

MariaDB [test]> create table test (id int) Engine=ColumnStore;

ERROR 1815 (HY000): Internal error: Cannot execute the statement. DBRM is read only!

It shows a lock in the system. 

[root@ip-10-216-33-123 bin]# ./viewtablelock

 There is 1 table lock

  Table                          LockID  Process  PID     Session  Txn    CreationTime              State    DBRoots

  analytics.rrrbpmms_prop_stage  1       DMLProc  116126  4299     23162  Fri Apr 28 16:10:01 2017  LOADING  1

I am unable to clear it using cleartablelock

[root@ip-10-216-33-123 bin]# ./cleartablelock  1

BRM error: DBRM is in READ-ONLY mode

Table lock 1 is not cleared.

I am sure that I am doing things wrong. Please help me to get the columnstore back readwrite.  If you need any other info please let me know. 

Thank you,

Ali 

[David Thompson]

Hi Ali,

In some cases the system will force itself into read only mode as a preventive measure.  You can also explicitly do this. To enable writes again:

Mcsadmin resumeDatabaseWrites

 

As documented here: https://mariadb.com/kb/en/mariadb/columnstore-administrative-console/

 

You can the try clearing the lock again.

 

Unless you can’t access the server, its usually better to do a shutdownSystem then start to clear the system state.

 

As to the root cause of this, if you can run the columnStoreSupport utility that may provide some information to us to help troubleshoot: https://mariadb.com/kb/en/mariadb/system-troubleshooting-mariadb-columnstore/#mariadb-columnstore-support-tool

 

David

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.
To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/228a187f-a183-4f70-ad5d-8989b81dc0fa%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[alimajaf ALI]

Thanks David.

But still my DB is read only :(  , Please see the log.

I am not able to attach the ColumnstoteSuportReport as the size limit exceeds.  Please let me know if some specific report from the bundle will is required. 

[root@ip-10-216-33-123 columnstore]# ma shutdown

shutdownsystem   Sun Apr 30 01:11:47 2017

This command stops the processing of applications on all Modules within the MariaDB ColumnStore System

   Checking for active transactions

           Do you want to proceed: (y or n) [n]: y

   Stopping System...

   Successful stop of System

   Shutting Down System...

   Successful shutdown of System

[root@ip-10-216-33-123 columnstore]# ma startsystem

startsystem   Sun Apr 30 01:12:33 2017

startSystem command, 'columnstore' service is down, sending command to

start the 'columnstore' service on all modules

   System being started, please wait.....................

   Successful start of System

[root@ip-10-216-33-123 columnstore]# ma resumeDatabaseWrites

resumedatabasewrites   Sun Apr 30 01:13:52 2017

This command resumes the DDL/DML writes to the MariaDB ColumnStore Database

           Do you want to proceed: (y or n) [n]: y

Resume MariaDB ColumnStore Database Writes Request successfully completed

[root@ip-10-216-33-123 columnstore]# mcsmysql -p

Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 4

Server version: 10.1.21-MariaDB Columnstore 1.0.7-1

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use test;

Database changed

MariaDB [test]> create table test (id int) Engine=ColumnStore;

ERROR 1815 (HY000): Internal error: Cannot execute the statement. DBRM is read only!

MariaDB [test]>

 

[David Thompson]

Without seeing the logs it’s hard to really help understand what is going on. Is there anything in the error or critical log that is of interest? Sometimes the debug.log tells a more complete story.

 

If you were doing some very high frequency DML it’s possible you hit: https://jira.mariadb.org/browse/MCOL-529 for which there is an os config workaround or we plan to have a fix in our next maintenance release 1.0.9.

 

If this doesn’t help, could you upload the report file to some cloud storage and share the link with me as that would work around the email size limit?

 

From: mariadb-c...@googlegroups.com [mailto:mariadb-c...@googlegroups.com] On Behalf Of alimajaf ALI

Sent: Saturday, April 29, 2017 10:24 PM
To: MariaDB ColumnStore <mariadb-c...@googlegroups.com>

--

You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.
To post to this group, send email to mariadb-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/fd27afa5-9e82-4434-a631-7224204ce6fe%40googlegroups.com.

[alimajaf ALI]

Hi David,

Please have a look at the log files here, 

https://ufile.io/9x8n3

 Thank you,

ALI

[David Thompson]

I can see that the problem originated around 17:41 server time on april 24^th when it was running bulk transactional inserts. I think you are likely hitting https://jira.mariadb.org/browse/MCOL-529 in which case you can look at the os workaround there until we get 1.0.9 out.

 

You might want to consider switching to autocommit mode for your bulk insert selects as these will then get optimized internally to a stream to cpimport?

 

Finally regarding your current state, it looks like the server is not able to rollback the crashed transaction:

Apr 30 00:53:34 ip-10-216-33-123 DMLProc[41319]: 34.104062 |0|0|0| C 20 CAL0002:  problem with rollback transaction 23162and DBRM is setting to readonly and table lock is not released: lookupLocal from extent map error encountered while looking up lbid:verID 16961368:0 and error code is -1 with message UNKNOWN (-1) (WriteEngine returns error 1)

Apr 30 00:53:34 ip-10-216-33-123 DMLProc[41319]: 34.120975 |0|0|0| C 20 CAL0002:  problem with rollback transaction 23162and DBRM is setting to readonly and table lock is not released: Can't read DBRM isReadWrite [ DBRM is in READ-ONLY mode]

 

This normally might get cleaned up with a clean shutdown and  startsystem but looks like this did not happen.

 

If you run:

/usr/local/mariadb/columnstore/bin/dbrmctl status  (this should say read only)

/usr/local/mariadb/columnstore/bin/dbrmctl readwrite

/usr/local/mariadb/columnstore/bin/dbrmctl reload

 

Then try restarting the system it should clear.

 

David.

 

 

From: mariadb-c...@googlegroups.com [mailto:mariadb-c...@googlegroups.com] On Behalf Of alimajaf ALI
Sent: Monday, May 1, 2017 9:14 AM
To: MariaDB ColumnStore <mariadb-c...@googlegroups.com>
Subject: Re: Table lock not cleared with cleartablelock

 

Hi David,

--

You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.
To post to this group, send email to mariadb-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/a6ebbaa5-0e52-4ac4-9ba4-f3b7432d5aab%40googlegroups.com.

[alimajaf ALI]

David,

It works, but without a system restart.  

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

I am able to write to the ColumnStore after executing  the below commands

/usr/local/mariadb/columnstore/bin/dbrmctl status

/usr/local/mariadb/columnstore/bin/dbrmctl readwrite

/usr/local/mariadb/columnstore/bin/dbrmctl reload

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

ColumnStore is going back readonly when I do a restart of the system.

Crit.log says this:

May  2 02:55:30 ip-10-216-33-123 DMLProc[77206]: 30.869881 |0|0|0| C 20 CAL0002:  problem with rollback transaction 23162

and DBRM is setting to readonly and table lock is not released: lookupLocal from extent map error encountered while looking up lbid:verID 16961368:0 and error code is -1 with message UNKNOWN (-1) (WriteEngine returns error 1)

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

BUT transaction 23162 is already rolled back.

[root@ip-10-216-33-123 ~]#  /usr/local/mariadb/columnstore/bin/viewtablelock

 There is 1 table lock

  Table                          LockID  Process  PID     Session  Txn    CreationTime              State    DBRoots  

  analytics.rrrbpmms_prop_stage  1       DMLProc  116126  4299     23162  Fri Apr 28 16:10:01 2017  LOADING  1        

[root@ip-10-216-33-123 ~]#  /usr/local/mariadb/columnstore/bin/cleartablelock  1

Rolling back and clearing table lock for table analytics.rrrbpmms_prop_stage; table lock 1

Sending rollback request to PM1...

Successful rollback response from PM1

Sending cleanup request to PM1...

Successful cleanup response from PM1

Table lock 1 for table analytics.rrrbpmms_prop_stage is cleared.

[root@ip-10-216-33-123 ~]#  /usr/local/mariadb/columnstore/bin/viewtablelock

 No tables are locked in the database.

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

Is the transaction stuck some where else ? Are you able to figure out something ?

Thanks,

ALI

[David Thompson]

Did you run the dbrmctl steps after clearing the lock?

 

From: mariadb-c...@googlegroups.com [mailto:mariadb-c...@googlegroups.com] On Behalf Of alimajaf ALI
Sent: Tuesday, May 2, 2017 12:39 AM
To: MariaDB ColumnStore <mariadb-c...@googlegroups.com>
Cc: alimaj...@gmail.com
Subject: Re: Table lock not cleared with cleartablelock

 

David,

--

You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.
To post to this group, send email to mariadb-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/ddd6ad30-46d7-4c60-abcf-40679866ce4b%40googlegroups.com.

[alimajaf ALI]

Yes david. The tge entry in the logfile posted is after clearing the lock. I dont know where does it pick it from

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.
To post to this group, send email to mariadb-columnstore@googlegroups.com.

[David Thompson]

By restart did you issue restartSytem or shutdownsystem then startSystem? The dbrmctl commands should have the effect of clearing the failed transaction log entries..

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.
To post to this group, send email to mariadb-c...@googlegroups.com.