You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "wangyu096 (via GitHub)" <gi...@apache.org> on 2024/10/21 08:49:33 UTC
[I] ShardSphere Proxy Migration MySQL Index error [shardingsphere]
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] ShardSphere Proxy Migration MySQL Index error [shardingsphere]
Posted by "jasneetsingh6114 (via GitHub)" <gi...@apache.org>.
jasneetsingh6114 commented on issue #33334:
URL: https://github.com/apache/shardingsphere/issues/33334#issuecomment-2568622474
Hey@wangyu096 I was wondering if you can tell me how to setup up the configurations for a debugger for ShardingSphereProxy
--
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
Re: [I] ShardSphere Proxy Migration MySQL Index error [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #33334: ShardSphere Proxy Migration MySQL Index error
URL: https://github.com/apache/shardingsphere/issues/33334
--
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
Re: [I] ShardSphere Proxy Migration MySQL Index error [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #33334:
URL: https://github.com/apache/shardingsphere/issues/33334#issuecomment-3682147934
Duplicate with #37123
--
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