Not the answer you need?
Register and ask your own question!

DML command rejected when PXC mode set to Enforcing

dfreshdfresh EntrantInactive User Role Supporter
I'm working on a 3 node Percona XtraDB POC. My test app is a commercial product called Artifactory. The app successfully starts, but when testing some functions I'm running into errors when pxc mode is set to enforcing. Below appears to be the SQL in the logs being run and the error being returned. Are there any real issues with setting percona in permissive mode all the time when an app tries to use functions like this?
2017-02-02 09:25:37,451 [http-nio-8081-exec-9] [DEBUG] (o.a.s.d.u.JdbcHelper:255) - Executing SQL: 'SELECT repo_key FROM permission_target_repos WHERE perm_target_id = 436406'.
2017-02-02 09:25:37,500 [http-nio-8081-exec-9] [DEBUG] (o.a.s.d.u.JdbcHelper:113) - Query returned in 48.42 millis : 'SELECT repo_key FROM permission_target_repos WHERE perm_target_id = 436406'
2017-02-02 09:25:37,502 [http-nio-8081-exec-9] [DEBUG] (o.a.s.d.u.JdbcHelper:255) - Executing SQL: 'UPDATE permission_targets SET perm_target_name = 'Anonymous', includes = '**', excludes = NULL WHERE perm_target_id = 436406'.
2017-02-02 09:25:37,551 [http-nio-8081-exec-9] [DEBUG] (o.a.s.d.u.JdbcHelper:166) - Query returned with 1 results in 48.25 millis : 'UPDATE permission_targets SET perm_target_name = 'Anonymous', includes = '**', excludes = NULL WHERE perm_target_id = 436406'
2017-02-02 09:25:37,552 [http-nio-8081-exec-9] [DEBUG] (o.a.s.d.u.JdbcHelper:255) - Executing SQL: 'DELETE FROM permission_target_repos WHERE perm_target_id = 436406'.
2017-02-02 09:25:37,601 [http-nio-8081-exec-9] [TRACE] (o.a.s.d.s.DbTransactionManager:939) - Triggering beforeCompletion synchronization
2017-02-02 09:25:37,602 [http-nio-8081-exec-9] [DEBUG] (o.a.s.d.s.DbTransactionManager:847) - Initiating transaction rollback
2017-02-02 09:25:37,602 [http-nio-8081-exec-9] [DEBUG] (o.a.s.d.s.DbTransactionManager:284) - Rolling back JDBC transaction on Connection [ProxyConnection[PooledConnection[com.mysql.jdbc.JDBC4Connection@46bb9a77]]]
2017-02-02 09:25:37,627 [http-nio-8081-exec-9] [TRACE] (o.a.s.d.s.DbTransactionManager:968) - Triggering afterCompletion synchronization
2017-02-02 09:25:37,627 [http-nio-8081-exec-9] [TRACE] (o.a.s.d.f.s.SqlStorageSession:81) - Release all locks of lm=851775068
2017-02-02 09:25:37,628 [http-nio-8081-exec-9] [DEBUG] (o.a.s.d.s.SessionSynchronization:95) - Session completed: 'org.artifactory.storage.db.security.service.AclServiceImpl.updateAcl' in 228.28 millis
2017-02-02 09:25:37,677 [http-nio-8081-exec-9] [DEBUG] (o.a.s.d.s.DbTransactionManager:66) - Releasing JDBC Connection [ProxyConnection[PooledConnection[com.mysql.jdbc.JDBC4Connection@46bb9a77]]] after transaction
2017-02-02 09:25:37,677 [http-nio-8081-exec-9] [DEBUG] (o.a.s.d.s.DbTransactionManager:67) - Connection was successfully released
2017-02-02 09:25:37,691 [http-nio-8081-exec-9] [DEBUG] (o.a.s.f.l.a.LockingAdvice:81) - Received exception on method invocation: ReflectiveMethodInvocation: public abstract void org.artifactory.storage.security.service.AclStoreService.updateAcl(org.artifactory.security.MutableAclInfo); target is of class [org.artifactory.storage.db.security.service.AclServiceImpl]
org.artifactory.storage.StorageException: Could not update ACL org.artifactory.model.xstream.security.AclImpl@fa86944d

Caused by: java.sql.SQLException: Percona-XtraDB-Cluster prohibits use of DML command on a table (artifactory.permission_target_repos) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER

Comments

  • vadimtkvadimtk Contributor Percona Staff Role
    Enforcing mode was specifically implemented to catch issues like tables without PRIMARY KEYS. It is a very bad idea to use tables without Primary Key on Percona XtraDB Cluster. You of course can switch to the Permissive mode, but I strongly suggest to add PRIMARY KEY to the table
    artifactory.permission_target_repos
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.