Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

ShardSphere Proxy Migration MySQL Index error #33334

Open
wangyu096 opened this issue Oct 21, 2024 · 0 comments
Open

ShardSphere Proxy Migration MySQL Index error #33334

wangyu096 opened this issue Oct 21, 2024 · 0 comments
Labels
feature: pipeline

Comments

@wangyu096
Copy link

Bug Report

Which version of ShardingSphere did you use?

5.4.1

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy?

Expected behavior

When migrating data, retain the original index names, do not add extra 'actualTableName' suffix to avoid exceeding MySQL's index name limit. MySQL allows the creation of indexes with the same name on different tables, so the suffix may not be necessary？

Actual behavior

When using the ShardingProxy data migration tool, tables on the target database will be automatically created. However, when creating table indexes, the tool automatically appends the name of the target table to the original index name, triggering MySQL's limitation that index names cannot exceed 64 characters.

The original table is as follows:

CREATE TABLE `gse_script_execute_obj_task` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `task_instance_id` bigint(20) NOT NULL,
  `step_instance_id` bigint(20) NOT NULL,
  `execute_count` smallint(6) NOT NULL DEFAULT '0',
  `actual_execute_count` smallint(6) DEFAULT NULL,
  `batch` smallint(6) NOT NULL DEFAULT '0',
  `execute_obj_type` tinyint(4) NOT NULL,
  `execute_obj_id` varchar(24) NOT NULL,
  `gse_task_id` bigint(20) NOT NULL DEFAULT '0',
  `status` int(11) DEFAULT '1',
  `start_time` bigint(20) DEFAULT NULL,
  `end_time` bigint(20) DEFAULT NULL,
  `total_time` bigint(20) DEFAULT NULL,
  `error_code` int(11) DEFAULT '0',
  `exit_code` int(11) DEFAULT NULL,
  `tag` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '',
  `log_offset` int(11) NOT NULL DEFAULT '0',
  `row_create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `row_update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_step_id_execute_count_batch_execute_obj_id` (`step_instance_id`,`execute_count`,`batch`,`execute_obj_id`),
  KEY `idx_task_instance_id` (`task_instance_id`),
  KEY `idx_step_id_execute_obj_id` (`step_instance_id`,`execute_obj_id`),
  KEY `idx_gse_task_id` (`gse_task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Through DEBUG, it was found that the actual SQL executed by ShardingSphere Proxy for data migration to create the table is as follows:

CREATE TABLE IF NOT EXISTS `gse_script_execute_obj_task` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `task_instance_id` bigint(20) NOT NULL,
  `step_instance_id` bigint(20) NOT NULL,
  `execute_count` smallint(6) NOT NULL DEFAULT '0',
  `actual_execute_count` smallint(6) DEFAULT NULL,
  `batch` smallint(6) NOT NULL DEFAULT '0',
  `execute_obj_type` tinyint(4) NOT NULL,
  `execute_obj_id` varchar(24) NOT NULL,
  `gse_task_id` bigint(20) NOT NULL DEFAULT '0',
  `status` int(11) DEFAULT '1',
  `start_time` bigint(20) DEFAULT NULL,
  `end_time` bigint(20) DEFAULT NULL,
  `total_time` bigint(20) DEFAULT NULL,
  `error_code` int(11) DEFAULT '0',
  `exit_code` int(11) DEFAULT NULL,
  `tag` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '',
  `log_offset` int(11) NOT NULL DEFAULT '0',
  `row_create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `row_update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_step_id_execute_count_batch_execute_obj_id_gse_script_execute_obj_task_0` (`step_instance_id`,`execute_count`,`batch`,`execute_obj_id`),
  KEY `idx_task_instance_id_gse_script_execute_obj_task_0` (`task_instance_id`),
  KEY `idx_step_id_execute_obj_id_gse_script_execute_obj_task_0` (`step_instance_id`,`execute_obj_id`),
  KEY `idx_gse_task_id_gse_script_execute_obj_task_0` (`gse_task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;

All indexes were suffixed with '_gse_script_execute_obj_task_0' by the ShardingSphere Proxy migration tool !!! It cause migration fail:
image

Reason analyze (If you can)

The source code for creating the table is as follows:
image

"j0102p00006a2e736b9cfca23eaddc58029580aded_Worker-1@15762" prio=5 tid=0x2328 nid=NA runnable
  java.lang.Thread.State: RUNNABLE
	  at org.apache.shardingsphere.infra.metadata.database.schema.util.IndexMetaDataUtils.getActualIndexName(IndexMetaDataUtils.java:65)
	  at org.apache.shardingsphere.sharding.rewrite.token.pojo.IndexToken.getIndexValue(IndexToken.java:75)
	  at org.apache.shardingsphere.sharding.rewrite.token.pojo.IndexToken.toString(IndexToken.java:63)
	  at org.apache.shardingsphere.infra.rewrite.sql.impl.RouteSQLBuilder.getSQLTokenText(RouteSQLBuilder.java:40)
	  at org.apache.shardingsphere.infra.rewrite.sql.impl.AbstractSQLBuilder.toSQL(AbstractSQLBuilder.java:55)
	  at org.apache.shardingsphere.infra.rewrite.engine.RouteSQLRewriteEngine.addSQLRewriteUnits(RouteSQLRewriteEngine.java:97)
	  at org.apache.shardingsphere.infra.rewrite.engine.RouteSQLRewriteEngine.rewrite(RouteSQLRewriteEngine.java:73)
	  at org.apache.shardingsphere.infra.rewrite.SQLRewriteEntry.rewrite(SQLRewriteEntry.java:83)
	  at org.apache.shardingsphere.infra.connection.kernel.KernelProcessor.rewrite(KernelProcessor.java:66)
	  at org.apache.shardingsphere.infra.connection.kernel.KernelProcessor.generateExecutionContext(KernelProcessor.java:52)
	  at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.createExecutionContext(ShardingSphereStatement.java:504)
	  at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute0(ShardingSphereStatement.java:444)
	  at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute(ShardingSphereStatement.java:378)
	  at org.apache.shardingsphere.data.pipeline.core.preparer.datasource.AbstractDataSourcePreparer.executeTargetTableSQL(AbstractDataSourcePreparer.java:99)
	  at org.apache.shardingsphere.data.pipeline.mysql.prepare.datasource.MySQLDataSourcePreparer.prepareTargetTables(MySQLDataSourcePreparer.java:39)
	  at org.apache.shardingsphere.data.pipeline.core.preparer.PipelineJobPreparerUtils.prepareTargetTables(PipelineJobPreparerUtils.java:113)
	  at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepareTarget(MigrationJobPreparer.java:165)
	  at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepareAndCheckTarget(MigrationJobPreparer.java:147)
	  at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepareAndCheckTargetWithLock(MigrationJobPreparer.java:133)
	  at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepare(MigrationJobPreparer.java:98)
	  at org.apache.shardingsphere.data.pipeline.scenario.migration.MigrationJob.doPrepare(MigrationJob.java:75)
	  at org.apache.shardingsphere.data.pipeline.core.job.AbstractPipelineJob.prepare(AbstractPipelineJob.java:91)
	  at org.apache.shardingsphere.data.pipeline.core.job.AbstractSimplePipelineJob.execute(AbstractSimplePipelineJob.java:61)
	  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:173)
	  at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.process(ElasticJobExecutor.java:142)
	  at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.execute(ElasticJobExecutor.java:124)
	  at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.execute(ElasticJobExecutor.java:100)
	  at org.apache.shardingsphere.elasticjob.lite.internal.schedule.LiteJob.execute(LiteJob.java:35)
	  at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
	  at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
	  - locked <0x3e1a> (a java.lang.Object)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Using ShardingProxy Migration Tool to migrate a table, when source table length(index_name)+length(table_name) is greater than 64 chars.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
feature: pipeline
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@terrymanu @wangyu096