wangyu096 opened a new issue, #33334:
URL: https://github.com/apache/shardingsphere/issues/33334
## 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:
<img width="1871" alt="image"
src="https://github.com/user-attachments/assets/33f72e67-3b7e-4196-af7c-416b0b6379a0";>
### Reason analyze (If you can)
The source code for creating the table is as follows:
<img width="1182" alt="image"
src="https://github.com/user-attachments/assets/2136d16a-18a9-4863-9d4c-dade76a9ea70";>
```
"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.
--
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:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
