You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/12/30 02:01:19 UTC

[GitHub] [shardingsphere] sandynz opened a new issue #14429: binlog_row_image option should not be required for all MySQL versions in scaling, verify it and improve it if necessary

sandynz opened a new issue #14429:
URL: https://github.com/apache/shardingsphere/issues/14429


   ## Bug Report
   
   ### Which version of ShardingSphere did you use?
   master branch
   
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   ShardingSphere-Proxy, scaling
   
   ### Expected behavior
   No exception thrown
   
   ### Actual behavior
   Not tested for now.
   
   ### Reason analyze (If you can)
   
   From `MySQLDataSourceChecker.java` source code, seems `binlog_row_image` is required, so it might throw exception in MySQL lower version, e.g. `5.5`.
   
   Code snippets:
   ```
       static {
           REQUIRED_VARIABLES.put("LOG_BIN", "ON");
           REQUIRED_VARIABLES.put("BINLOG_FORMAT", "ROW");
           REQUIRED_VARIABLES.put("BINLOG_ROW_IMAGE", "FULL");
       }
       
       private void checkPrivilege(final DataSource dataSource) {
           try (Connection connection = dataSource.getConnection();
                PreparedStatement preparedStatement = connection.prepareStatement(SHOW_GRANTS_SQL);
                ResultSet resultSet = preparedStatement.executeQuery()) {
               while (resultSet.next()) {
                   String privilege = resultSet.getString(1).toUpperCase();
                   if (matchPrivileges(privilege)) {
                       return;
                   }
               }
           } catch (final SQLException ex) {
               throw new PipelineJobPrepareFailedException("Source data source check privileges failed.", ex);
           }
           throw new PipelineJobPrepareFailedException("Source data source is lack of REPLICATION SLAVE, REPLICATION CLIENT ON *.* privileges.");
       }
       
       private boolean matchPrivileges(final String privilege) {
           return Arrays.stream(REQUIRED_PRIVILEGES).anyMatch(each -> Arrays.stream(each).allMatch(privilege::contains));
       }
   ```
   
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   
   ### Example codes for reproduce this issue (such as a github link).
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] ReyYang edited a comment on issue #14429: binlog_row_image option should not be required for all MySQL versions in scaling, verify it and improve it if necessary

Posted by GitBox <gi...@apache.org>.
ReyYang edited a comment on issue #14429:
URL: https://github.com/apache/shardingsphere/issues/14429#issuecomment-1003322881


   The problems found after testing are as follows:
   - Exception stack
   ``` java
   org.apache.shardingsphere.data.pipeline.core.exception.PipelineJobPrepareFailedException: Source data source check variables failed.
   	at org.apache.shardingsphere.data.pipeline.mysql.check.datasource.MySQLDataSourceChecker.checkVariable(MySQLDataSourceChecker.java:96)
   	at org.apache.shardingsphere.data.pipeline.mysql.check.datasource.MySQLDataSourceChecker.checkVariable(MySQLDataSourceChecker.java:86)
   	at org.apache.shardingsphere.data.pipeline.scenario.rulealtered.RuleAlteredJobPreparer.checkSourceDataSources(RuleAlteredJobPreparer.java:104)
   	at org.apache.shardingsphere.data.pipeline.scenario.rulealtered.RuleAlteredJobPreparer.checkDataSource(RuleAlteredJobPreparer.java:93)
   	at org.apache.shardingsphere.data.pipeline.scenario.rulealtered.RuleAlteredJobPreparer.prepare(RuleAlteredJobPreparer.java:63)
   	at org.apache.shardingsphere.data.pipeline.scenario.rulealtered.RuleAlteredJob.execute(RuleAlteredJob.java:48)
   	at org.apache.shardingsphere.elasticjob.simple.executor.SimpleJobExecutor.process(SimpleJobExecutor.java:33)
   	at org.apache.shardingsphere.elasticjob.simple.executor.SimpleJobExecutor.process(SimpleJobExecutor.java:29)
   	at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.process(ElasticJobExecutor.java:172)
   	at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.lambda$process$0(ElasticJobExecutor.java:153)
   	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
   	at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125)
   	at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:69)
   	at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:78)
   	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
   	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
   	at java.lang.Thread.run(Thread.java:748)
   Caused by: java.sql.SQLException: Illegal operation on empty result set.
   	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
   	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
   	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
   	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
   	at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:790)
   	at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5213)
   	at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5136)
   	at com.zaxxer.hikari.pool.HikariProxyResultSet.getString(HikariProxyResultSet.java)
   	at org.apache.shardingsphere.data.pipeline.mysql.check.datasource.MySQLDataSourceChecker.checkVariable(MySQLDataSourceChecker.java:105)
   	at org.apache.shardingsphere.data.pipeline.mysql.check.datasource.MySQLDataSourceChecker.checkVariable(MySQLDataSourceChecker.java:93)
   	... 16 common frames omitted
   ```
   - Cause Analysis
   In MySQL5.5, the "BINLOG_ROW_IMAGE" variable does not exist, so an exception is thrown in `resultSet.next()`
   “BINLOG_ROW_IMAGE” default value is full. In MySQL 5.5 and earlier, full row images are always used for both before images and after images. 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] sandynz commented on issue #14429: binlog_row_image option should not be required for all MySQL versions in scaling, verify it and improve it if necessary

Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #14429:
URL: https://github.com/apache/shardingsphere/issues/14429#issuecomment-1002845384


   From MySQL doc:
   ```
   Important Variables in MySQL 5.6.  Features have been added to MySQL 5.6 which need to be disabled when replicating to earlier MySQL versions. To avoid incompatibilities, set the following variables on the MySQL 5.6 source:
       binlog_checksum=NONE
       binlog_row_image=FULL
       binlog_rows_query_log_events=OFF
       log_bin_use_v1_row_events=1 (NDB Cluster only)
       gtid_mode=OFF 
   ```
   
   Please refer to these docs for more details:
   - [Replication Compatibility Between MySQL Versions](https://docs.oracle.com/cd/E17952_01/mysql-5.6-en/replication-compatibility.html ) , [binlog_row_image](https://docs.oracle.com/cd/E17952_01/mysql-5.6-en/replication-options-binary-log.html#sysvar_binlog_row_image )
   - [Merge binlog_row_image from MySQL 5.6](https://jira.mariadb.org/browse/MDEV-6877 )
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] sandynz closed issue #14429: binlog_row_image option should not be required for all MySQL versions in scaling, verify it and improve it if necessary

Posted by GitBox <gi...@apache.org>.
sandynz closed issue #14429:
URL: https://github.com/apache/shardingsphere/issues/14429


   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] ReyYang commented on issue #14429: binlog_row_image option should not be required for all MySQL versions in scaling, verify it and improve it if necessary

Posted by GitBox <gi...@apache.org>.
ReyYang commented on issue #14429:
URL: https://github.com/apache/shardingsphere/issues/14429#issuecomment-1003322881


   Hi,Can I take this?
   The problems found after testing are as follows:
   - Exception stack
   ``` java
   org.apache.shardingsphere.data.pipeline.core.exception.PipelineJobPrepareFailedException: Source data source check variables failed.
   	at org.apache.shardingsphere.data.pipeline.mysql.check.datasource.MySQLDataSourceChecker.checkVariable(MySQLDataSourceChecker.java:96)
   	at org.apache.shardingsphere.data.pipeline.mysql.check.datasource.MySQLDataSourceChecker.checkVariable(MySQLDataSourceChecker.java:86)
   	at org.apache.shardingsphere.data.pipeline.scenario.rulealtered.RuleAlteredJobPreparer.checkSourceDataSources(RuleAlteredJobPreparer.java:104)
   	at org.apache.shardingsphere.data.pipeline.scenario.rulealtered.RuleAlteredJobPreparer.checkDataSource(RuleAlteredJobPreparer.java:93)
   	at org.apache.shardingsphere.data.pipeline.scenario.rulealtered.RuleAlteredJobPreparer.prepare(RuleAlteredJobPreparer.java:63)
   	at org.apache.shardingsphere.data.pipeline.scenario.rulealtered.RuleAlteredJob.execute(RuleAlteredJob.java:48)
   	at org.apache.shardingsphere.elasticjob.simple.executor.SimpleJobExecutor.process(SimpleJobExecutor.java:33)
   	at org.apache.shardingsphere.elasticjob.simple.executor.SimpleJobExecutor.process(SimpleJobExecutor.java:29)
   	at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.process(ElasticJobExecutor.java:172)
   	at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.lambda$process$0(ElasticJobExecutor.java:153)
   	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
   	at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125)
   	at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:69)
   	at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:78)
   	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
   	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
   	at java.lang.Thread.run(Thread.java:748)
   Caused by: java.sql.SQLException: Illegal operation on empty result set.
   	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
   	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
   	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
   	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
   	at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:790)
   	at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5213)
   	at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5136)
   	at com.zaxxer.hikari.pool.HikariProxyResultSet.getString(HikariProxyResultSet.java)
   	at org.apache.shardingsphere.data.pipeline.mysql.check.datasource.MySQLDataSourceChecker.checkVariable(MySQLDataSourceChecker.java:105)
   	at org.apache.shardingsphere.data.pipeline.mysql.check.datasource.MySQLDataSourceChecker.checkVariable(MySQLDataSourceChecker.java:93)
   	... 16 common frames omitted
   ```
   - Cause Analysis
   In MySQL5.5, the "BINLOG_ROW_IMAGE" variable does not exist, so an exception is thrown in `resultSet.next()`


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org