java - liquibase.异常.DatabaseException : Duplicate entry

Question

我在运行生产环境时遇到以下异常。但在本地工作正常。

ing bean with name 'jobLauncher' defined in class path resource [config/spring/batch/lms-draw-result-batch-jobs-context.xml]: Cannot resolve reference to bean 'jobRepository' while setting bean property 'jobRepository'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jobRepository' defined in class path resource [config/spring/batch/lms-draw-result-batch-jobs-context.xml]: Cannot resolve reference to bean 'transactionManager' while setting bean property 'transactionManager'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'transactionManager' defined in class path resource [config/spring/lms-txnconfig-context.xml]: Cannot resolve reference to bean 'sessionFactory' while setting bean property 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [config/spring/lms-dbconfig-context.xml]: Invocation of init method failed; nested exception is `liquibase.exception.MigrationFailedException: Migration failed for change set db/changelog/db.changelog-1.7.xml:`:update-draw-id-to-winning_result::Mohamed Musni:
     Reason: liquibase.exception.DatabaseException: Duplicate entry '1340' for key 'draw_id' [Failed SQL: ALTER TABLE lms_staging.winning_result ADD UNIQUE (draw_id)]
2017-08-01 15:06:35 [localhost-startStop-1] INFO  SchedulerFactoryBean:765 - Shutting down Quartz Scheduler
2017-08-01 15:06:35 [localhost-startStop-1] INFO  QuartzScheduler:694 - Scheduler org.springframework.scheduling.quartz.SchedulerFactoryBean#0_$_NON_CLUSTERED shutting down.
2017-08-01 15:06:35 [localhost-startStop-1] INFO  QuartzScheduler:613 - Scheduler org.springframework.scheduling.quartz.SchedulerFactoryBean#0_$_NON_CLUSTERED paused.
2017-08-01 15:06:35 [localhost-startStop-1] INFO  QuartzScheduler:771 - Scheduler org.springframework.scheduling.quartz.SchedulerFactoryBean#0_$_NON_CLUSTERED shutdown complete.
2017-08-01 15:06:35 [localhost-startStop-1] INFO  ThreadPoolTaskScheduler:203 - Shutting down ExecutorService 'messageBrokerTaskScheduler'
2017-08-01 15:06:35 [localhost-startStop-1] INFO  ThreadPoolTaskExecutor:203 - Shutting down ExecutorService 'brokerChannelExecutor'
2017-08-01 15:06:35 [localhost-startStop-1] INFO  ThreadPoolTaskExecutor:203 - Shutting down ExecutorService 'clientOutboundChannelExecutor'
2017-08-01 15:06:35 [localhost-startStop-1] INFO  ThreadPoolTaskExecutor:203 - Shutting down ExecutorService 'clientInboundChannelExecutor'
2017-08-01 15:06:35 [localhost-startStop-1] INFO  ThreadPoolTaskExecutor:203 - Shutting down ExecutorService 'taskExecutor'
2017-08-01 15:06:35 [localhost-startStop-1] ERROR ContextLoader:353 - Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'schedulerController': Injection of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire field: private com.auxenta.lms.service.DrawManagementService com.auxenta.lms.controller.web.v1.SchedulerController.drawManagementService; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'drawManagementServiceImpl': Injection of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire field: org.springframework.batch.core.launch.JobLauncher com.auxenta.lms.service.Impl.DrawManagementServiceImpl.jobLauncher; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jobLauncher' defined in class path resource [config/spring/batch/lms-draw-result-batch-jobs-context.xml]: Cannot resolve reference to bean 'jobRepository' while setting bean property 'jobRepository'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jobRepository' defined in class path resource [config/spring/batch/lms-draw-result-batch-jobs-context.xml]: Cannot resolve reference to bean 'transactionManager' while setting bean property 'transactionManager'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'transactionManager' defined in class path resource [config/spring/lms-txnconfig-context.xml]: Cannot resolve reference to bean 'sessionFactory' while setting bean property 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [config/spring/lms-dbconfig-context.xml]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set db/changelog/db.changelog-1.7.xml::update-draw-id-to-winning_result::Mohamed Musni:
     Reason: liquibase.exception.DatabaseException: Duplicate entry '1340' for key 'draw_id' [Failed SQL: ALTER TABLE lms_staging.winning_result ADD UNIQUE (draw_id)]
        at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:334)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1214)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:543)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:772)
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:839)
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:538)
        at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:446)
        at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:328)
        at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:107)
        at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4961)
        at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5455)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
        at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
        at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
        at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:634)
        at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:1074)
        at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1858)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
        at java.util.concurrent.FutureTask.run(FutureTask.java:262)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:744)

这是我的 createTable xml 文件

<changeSet author="zzzzzzzzzz" id="1-create-winning_result">
        <createTable tableName="winning_result">
            <column autoIncrement="true" name="id" type="INT">
                <constraints primaryKey="true"/>
            </column>
            <column name="draw_id" type="INT"/>
            <column name="winning_numbers" type="VARCHAR(45)"/>
            <column name="bonus_numbers" type="VARCHAR(45)"/>
            <column name="special_numbers" type="VARCHAR(45)"/>
            <column name="approved" type="BIT(1)"/>
            <column name="timestamp" type="datetime"/>
        </createTable>
    </changeSet>

这也是ForeignKeyConstraint，

 <changeSet author="zzzzzzzzzz" id="2-add-foreign-constraint">
        <addForeignKeyConstraint baseColumnNames="draw_id"
                                 baseTableName="winning_result"
                                 constraintName="fk_draw_winning_result"
                                 deferrable="false"
                                 initiallyDeferred="false" onDelete="NO ACTION"
                                 onUpdate="NO ACTION" referencedColumnNames="id"
                                 referencedTableName="draw"/>
    </changeSet>

我需要将draw_id设置为unique.my (db/changelog/db.changelog-1.7.xml)文件 --->

            <addUniqueConstraint
                                 columnNames="draw_id"
                                 deferrable="true"
                                 disabled="true"
                                 initiallyDeferred="true"
                                 tableName="winning_result"/>
        </changeSet>

是这个数据库版本问题还是其他问题。任何人都可以帮助我。

Answer 1

向包含预先存在的数据的表添加限制是一个危险的游戏。

您的 Winning_result 表可能已经包含一些数据，并且您尝试设置唯一的列 (draw_id) 多次包含值 1340。

您必须在添加约束之前修复数据(删除重复的数据(第 1340 章可能有其他值))，或者放弃添加约束。