Mysql error with Percona XtraDB and pxc_strict_mode

Jan Bothmann shared this question ago

Answered

Im currently testing a 3 node Percona XtraDB Cluster in order to eliminate any spof in our Yellowfin setup.

But it seems like Percona is having truble with the Yellowfin Database Scheme.

I backed up our production configuration databases and migrated it to the Percona cluster, and tried to upgrade Yellowfin on a test server using this database.

During the upgrade i received the following error:

com.hof.dbtool.DBException: Error inserting data
ERR_INSERTQUERY
Query:
INSERT INTO Configuration (`IpOrg`, `ConfigTypeCode`, `ConfigCode`, `ConfigData`) VALUES (?, ?, ?, ?)
	at com.hof.dbtool.DBInterface.insertRow(DBInterface.java:1536)
	at com.hof.patch.process.job.UpdateSchemaJob.updateSchemaVersion(UpdateSchemaJob.java:295)
	at com.hof.patch.process.job.UpdateSchemaJob.run(UpdateSchemaJob.java:261)
	at com.hof.patch.PatchInstaller.runInstallJobs(PatchInstaller.java:459)
	at com.hof.patch.izpack.panels.YFPatchProcessPanel$YFProcessPanelWorker.run(YFPatchProcessPanel.java:299)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Percona-XtraDB-Cluster prohibits use of DML command on a table (yfdb01.Configuration) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
	at com.hof.dbtool.DBInterface.insertRow(DBInterface.java:1530)
	... 5 more

According Percona this is their recommendation:

"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 tables"

We would realy like to move our configuration database to a clustered setup on mysql, do you have any experience or recommendation in this matter.

Best regards

Jan Bothmann

The same question

Replies (3)

Ryan Carrie ●

Hello Jan,

Thanks for reaching out with your question. I don't have any experience in working with Yellowfin on Percona XtraDB particularly. It sounds like in order to use Yellowfin on this type of cluster you will have to switch the mode to Permissive to allow it to support our structure. Have you tried this to see how it works?

I don't see any quick work arounds that one could implement in the Configuration Database without affecting Yellowfin in unexpected ways. I think it would mainly boil down to documentation from the Percona side to determine the true implications of switching to Permissive mode.

As an alternative it may be worth looking into the native MySQL clustering options.

Does this help?

Thanks,

Ryan

Reply URL

Jan Bothmann ●

Hi Ryan,

Affecting yellowfin unexpected is my biggest consern as well. So switching to permissiv mode migt not be the best solution either.

Native MySql clustering is ofcource an option, but to be honest i was rather hoping to stay clear of Oracle :)

Thanks,

Jan

Reply URL

Ryan Carrie ●

Hi Jan,

Happy to help! If you happen to go down the permissive path with Percona feel free to update this post with your results for future inquiries.

Thanks,

Ryan

Reply URL