Mysql errror : alter event table not working

[Praitheesh]

alfio.log.tar.gz
I tried to run the application with latest changes.

Mysql Version : 5.7.14
Got the error when try to alter event table saying Invalid default value for 'end_ts'. It might be related to #102. Attached log for the reference.

`SQL State : 42000
Error Code : 1067
Message : Invalid default value for 'end_ts'
Location : alfio/db/MYSQL/V15_1.8.8__ADD_FIRST_LAST_NAME.sql (/home/praitheesh/github/alf.io/build/resources/main/alfio/db/MYSQL/V15_1.8.8__ADD_FIRST_LAST_NAME.sql)
Line : 19
Statement : alter table event add column version varchar(50)

at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:749) ~[spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:185) ~[spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1143) ~[spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1046) ~[spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:510) ~[spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482) ~[spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) ~[spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) ~[spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) ~[spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) ~[spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1192) ~[spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1116) ~[spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1014) ~[spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:813) ~[spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:741) ~[spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
... 17 more`

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'migrator' defined in alfio.config.DataSourceConfiguration: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.flywaydb.core.Flyway]: Factory method 'migrator' threw exception; nested exception is org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:

Tried to alter the table manually. Got below message.

mysql> alter table event add column version varchar(50); ERROR 1067 (42000): Invalid default value for 'end_ts'

mysql> show create table event;

| event | CREATE TABLE `event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `short_name` varchar(128) COLLATE utf8_bin NOT NULL,
  `location` varchar(2048) COLLATE utf8_bin NOT NULL,
  `latitude` varchar(255) COLLATE utf8_bin NOT NULL,
  `longitude` varchar(255) COLLATE utf8_bin NOT NULL,
  `start_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `end_ts` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `time_zone` varchar(255) COLLATE utf8_bin NOT NULL,
  `regular_price_cts` int(11) NOT NULL,
  `currency` varchar(3) COLLATE utf8_bin DEFAULT NULL,
  `available_seats` int(11) NOT NULL,
  `vat_included` tinyint(1) NOT NULL,
  `vat` decimal(5,2) NOT NULL,
  `allowed_payment_proxies` varchar(2048) COLLATE utf8_bin NOT NULL,
  `private_key` varchar(2048) COLLATE utf8_bin NOT NULL,
  `org_id` int(11) NOT NULL,
  `website_url` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
  `website_t_c_url` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
  `image_url` varchar(2048) COLLATE utf8_bin DEFAULT NULL,
  `file_blob_id` char(64) COLLATE utf8_bin DEFAULT NULL,
  `display_name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `locales` int(11) NOT NULL DEFAULT '7',
  `type` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT 'INTERNAL',
  `external_url` varchar(1024) COLLATE utf8_bin DEFAULT NULL,
  `src_price_cts` int(11) NOT NULL DEFAULT '0',
  `vat_status` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT 'NONE',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_event_name` (`short_name`),
  KEY `event_org_id_idx` (`org_id`),
  KEY `file_blob_id` (`file_blob_id`),
  CONSTRAINT `event_ibfk_1` FOREIGN KEY (`org_id`) REFERENCES `organization` (`id`),
  CONSTRAINT `event_ibfk_2` FOREIGN KEY (`file_blob_id`) REFERENCES `file_blob` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

[syjer]

@Praitheesh good catch! Thank you, I'll fix the script :)

[syjer]

@Praitheesh I've pushed a fix, could you try it?

[Praitheesh]

Still it is throwing below error.

[ERROR] 2016-08-23 10:00:15.247 [main] SpringApplication - Application startup failed org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'customMessageManager' defined in file [/home/praitheesh/github/alf.io/build/classes/main/alfio/manager/support/CustomMessageManager.class]: Unsatisfied dependency expressed through constructor argument with index 1 of type [alfio.manager.EventManager]: Error creating bean with name 'eventManager' defined in file [/home/praitheesh/github/alf.io/build/classes/main/alfio/manager/EventManager.class]: Unsatisfied dependency expressed through constructor argument with index 17 of type [org.flywaydb.core.Flyway]: Error creating bean with name 'migrator' defined in alfio.config.DataSourceConfiguration: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.flywaydb.core.Flyway]: Factory method 'migrator' threw exception; nested exception is org.flywaydb.core.api.FlywayException: Schemaalfiocontains a failed migration to version 15.1.8.8 !; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'migrator' defined in alfio.config.DataSourceConfiguration: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.flywaydb.core.Flyway]: Factory method 'migrator' threw exception; nested exception is org.flywaydb.core.api.FlywayException: Schemaalfiocontains a failed migration to version 15.1.8.8 !; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'eventManager' defined in file [/home/praitheesh/github/alf.io/build/classes/main/alfio/manager/EventManager.class]: Unsatisfied dependency expressed through constructor argument with index 17 of type [org.flywaydb.core.Flyway]: Error creating bean with name 'migrator' defined in alfio.config.DataSourceConfiguration: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.flywaydb.core.Flyway]: Factory method 'migrator' threw exception; nested exception is org.flywaydb.core.api.FlywayException: Schemaalfiocontains a failed migration to version 15.1.8.8 !; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'migrator' defined in alfio.config.DataSourceConfiguration: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.flywaydb.core.Flyway]: Factory method 'migrator' threw exception; nested exception is org.flywaydb.core.api.FlywayException: Schemaalfiocontains a failed migration to version 15.1.8.8 !

More Logs here : alfio.log.tar.gz

[Praitheesh]

I think above error because of previous flyway migration filer.

I got it work by deleting entry from schema_version table.

version_rank: 52
installed_rank: 52
       version: 15.1.8.8
   description: ADD FIRST LAST NAME
          type: SQL
        script: V15_1.8.8__ADD_FIRST_LAST_NAME.sql
      checksum: 1569500787
  installed_by: root
  installed_on: 2016-08-22 05:14:34
execution_time: 120
       success: 0

delete schema_version where version_rank = 52;

@syjer is any way to reset programmability via flyway ?

[syjer]

@Praitheesh I don't know, and I'm not sure it's useful for our case to call programmatically the repair functionality of flyway: if a migration fail we could have the database in an inconsistent state (this is the case for mysql which don't have transactional DDL), so we need to fix it manually anyway.

[Praitheesh]

@syjer agree with you.