After installing CA Open Space 3.0, not able to access the On-Boarding page or the logon page. Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT="STATEMENT"

Products

• OPENINGRES

Issue/Introduction

Description:

After CA Open Space 3.0 is started, if it was started properly, a very easy rule of thumb is to check how much memory the JAVA.EXE program is taking. CA Open Space's JAVE.EXE normally consumes about 1GB RAM right after it is started. This is a very good indication that it started correctly without even checking any logs.

In this case, upon checking the LifeRay logs in <OSOP_Install>\osop\logs directory, below errors were seen:

22:01:44,205 ERROR [JDBCExceptionReporter:75] Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT andat least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.;

Normally during the install process, the CA Open Space installer accesses the MySQL database (as the database admin user specified during the install) and creates the CA Open Space database user, creates the tables and loads necessary default data into those tables. A good case install would show:

18:29:22,524 WARN  [DataSourceFactoryImpl:214] Property encrypted.password is not a valid C3PO property18:29:25,816 INFO  [DialectDetector:80] Determine dialect for MySQL 518:29:25,894 INFO  [DialectDetector:145] Found dialect org.hibernate.dialect.MySQLDialect18:29:35,020 WARN  [DataSourceFactoryImpl:214] Property encrypted.password is not a valid C3PO property18:30:08,841 ERROR [ImageLocalServiceImpl:78] Default company logo is not available18:30:23,817 ERROR [JDBCExceptionReporter:75] Table 'osop.lock_' doesn't exist18:30:25,408 WARN  [ReleaseLocalServiceImpl:140] Table 'osop.release_' doesn't exist18:30:25,424 INFO  [ReleaseLocalServiceImpl:84] Create tables and populate with default data18:32:30,427 WARN  [BaseDB:382] Specified key was too long; max key length is 767 bytes: create index IX_7020130F on SCProductVersion (directDownloadURL);18:32:30,427 ERROR [BaseDB:389] create index IX_7020130F on SCProductVersion (directDownloadURL);18:32:44,935 WARN  [BaseDB:382] Specified key was too long; max key length is 767 bytes: create index IX_89509087 on User_ (companyId, openId);18:32:44,935 ERROR [BaseDB:389] create index IX_89509087 on User_ (companyId, openId);18:32:57,104 INFO  [BaseDB:409] Database does not support case sensitive queries18:33:00,270 INFO  [VerifyProcess:76] Verifying com.liferay.portal.verify.VerifyProcessSuite18:33:00,286 INFO  [VerifyProcess:76] Verifying com.liferay.portal.verify.VerifyProperties18:33:00,302 INFO  [VerifyProcess:76] Verifying com.liferay.portal.verify.VerifyMySQL18:33:00,364 INFO  [VerifyProcess:76] Verifying com.liferay.portal.verify.VerifySQLServer18:33:00,364 INFO  [VerifyProcess:76] Verifying com.liferay.portal.verify.VerifyCounter18:33:00,411 INFO  [VerifyProcess:76] Verifying com.liferay.portal.verify.VerifyUUID18:33:00,426 INFO  [VerifyProcess:76] Verifying com.liferay.portal.verify.VerifyPermission18:33:02,876 INFO  [VerifyProcess:76] Verifying com.liferay.portal.verify.VerifyRole18:33:02,876 INFO  [VerifyProcess:76] Verifying com.liferay.portal.verify.VerifyAsset

For a bad case install, right after the Create Tables statement, may see an error like below:

Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT="STATEMENT"

For a bad case install, when you verify the CA Open Space database in MySQL, should see more than 0 records in tables like: osop.user_ , osop.portalpreferences. This document provides a way to resolve a situation where the number of records in those tables are zero (i.e. no records)

 

Resolution

Solution:

The error normally indicates that the database log format was set to something that Open Space cannot use. Therefore, it needs to be changed.

To resolve this issue:

1. Stop the CA Open Space Jetty Server and CA Open Space Server services via Windows Control Panel.
   
   
2. Login to MySQL as root user (using MySQL workbench) and run below SQL statements:
   
   FLUSH TABLES WITH READ LOCK;
   FLUSH LOGS;
   SET GLOBAL binlog_format = 'MIXED';
   FLUSH LOGS;
   UNLOCK TABLES;
   
   
3. Restart MySQL
   
   
4. Run the CA Open Space installer using the Modify Existing installation option and provide appropriate inputs to complete the reinstall. DO NOT start CA Open Space yet
   
   
5. Verify via the latest LifeRay logs to see if the log entries match what should be seen based on the Good Case scenario above. Verify if there are more than 0 records in the osop.user_ and osop.portalpreferences tables.
   
   
6. Restart CA Open Space Jetty Server and CA Open Space Server services via Windows Control Panel.
   
   
7. Login to the CA Open Space Control Panel or On-Boarding URLs.

 

Additional Information

Note: A reference about this could also be found on the MySQL documentation forums - http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html