Header Shadow Image


User cannot run DDL statements on the specified database. Attempt to create and drop a table failed.

In case you receive the following:


cloudera-scm-server.log:2019-06-21 01:16:57,763 ERROR CommandPusher:com.cloudera.cmf.model.DbCommand: Command 609(HiveTestDatabaseConnection) has completedFINISHED, success:false, msg:User cannot run DDL statements on the specified database. Attempt to create and drop a table failed.

check the logs or test creating a temporary table:

cloudera-scm-server.log:Caused by: org.postgresql.util.PSQLException: ERROR: cannot execute UPDATE in a read-only transaction
cloudera-scm-server.log:Caused by: java.sql.BatchUpdateException: Batch entry 0 update COMMANDS_DETAIL set OPTIMISTIC_LOCK_VERSION=4, INTERNAL_STATE=? where COMMAND_ID=618 and OPTIMISTIC_LOCK_VERSION=3 was aborted: ERROR: cannot execute UPDATE in a read-only transaction  Call getNextException to see other errors in the batch.
cloudera-scm-server.log:Caused by: org.postgresql.util.PSQLException: ERROR: cannot execute UPDATE in a read-only transaction
cloudera-scm-server.log:Caused by: java.sql.BatchUpdateException: Batch entry 0 update COMMANDS_DETAIL set OPTIMISTIC_LOCK_VERSION=6, INTERNAL_STATE=? where COMMAND_ID=618 and OPTIMISTIC_LOCK_VERSION=5 was aborted: ERROR: cannot execute UPDATE in a read-only transaction  Call getNextException to see other errors in the batch.
cloudera-scm-server.log:Caused by: org.postgresql.util.PSQLException: ERROR: cannot execute UPDATE in a read-only transaction
[root@cm-r01nn01 cloudera-scm-server]#

Try to test create a table:

hue_mws01=>
hue_mws01=> CREATE TABLE account(
   user_id serial PRIMARY KEY,
   username VARCHAR (50) UNIQUE NOT NULL,
   password VARCHAR (50) NOT NULL,
   email VARCHAR (355) UNIQUE NOT NULL,
   created_on TIMESTAMP NOT NULL,
   last_login TIMESTAMP
);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
hue_mws01=>

In our case the above was resolved by restarting haproxy, keepalived and Patroni on psql02 (Node 2) of our PostgreSQL cluster.  We then tested and received:

[root@psql01 ~]# sudo su – postgres
Last login: Sat Jun 22 00:16:39 EDT 2019 on pts/0
-bash-4.2$ psql -h psql-c01.nix.mds.xyz -p 5432 -W -U hue_mws01 -d hue_mws01
Password for user hue_mws01:
psql (10.5)
Type "help" for help.

hue_mws01=> CREATE TABLE account(
   user_id serial PRIMARY KEY,
   username VARCHAR (50) UNIQUE NOT NULL,
   password VARCHAR (50) NOT NULL,
   email VARCHAR (355) UNIQUE NOT NULL,
   created_on TIMESTAMP NOT NULL,
   last_login TIMESTAMP
);
CREATE TABLE
hue_mws01=>

Tell us if this solved your issue.

Cheers,
TK

 

Leave a Reply

You must be logged in to post a comment.


     
  Copyright © 2003 - 2013 Tom Kacperski (microdevsys.com). All rights reserved.

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 Unported License