Problema con la actualizacion de la B.D.

Help
2007-08-27
2013-05-02
  • Rafael Cano

    Rafael Cano - 2007-08-27

    He instalado por primera vez la aplciación en un sistema Ubuntu 7.04, Tomcat 6.0.14, MySQL 5.0.38 server 5.0.26 Client, navegador Firefox 2.0.0.6, version 0.6 de tntconcept (tntconcept-0.6-installer.tar.gz)

    Cree la base de datos en el esquema tntconcept.
    el usuario de la aplicación se llama 'programa', tiene permiso para accerder a todo el esquema tntconcept.

    Cuando por primera vez me dice que la base de datos es antigua, diciéndome que me actualice, me da un error.

    El log del fichero de migación es:

    27 ago 2007 13:53:40,767 upgradeDatabase - >>>> STARTING MIGRATION FROM 0.1.0 TO 0.6.0 <<<<
    27 ago 2007 13:53:40,768 upgradeDatabase - disabling auto commit
    27 ago 2007 13:53:40,768 upgradeDatabase - 0.1.0
    27 ago 2007 13:53:40,768 upgradeDatabase - loading script com/autentia/intra/sql/mysql/0.1/upgrade.sql
    27 ago 2007 13:53:40,769     insert into Frequency(name) values ('Bimensual')
    27 ago 2007 13:53:40,769    ALTER TABLE `Bill` MODIFY COLUMN `amount` decimal(10,2) default 0
    27 ago 2007 13:53:40,769  ALTER TABLE `Bill` ADD COLUMN `startBillDate` date not null default '1980-01-01'
    27 ago 2007 13:53:40,769  ALTER TABLE `Bill` ADD COLUMN `endBillDate` date not null default '1980-01-01'
    27 ago 2007 13:53:40,769   ALTER TABLE `Bill` ADD COLUMN `billType` varchar(16) not null default 'ISSUED'
    27 ago 2007 13:53:40,770  ALTER TABLE `Bill` ADD COLUMN `orderNumber` varchar(64)
    27 ago 2007 13:53:40,770  ALTER TABLE `Bill` ADD COLUMN `contactId` int
    27 ago 2007 13:53:40,770  ALTER TABLE `Bill` ADD index `ndx_bill_contactId` (`contactId`)
    27 ago 2007 13:53:40,770  ALTER TABLE `Bill` ADD constraint `fk_bill_contactId` foreign key (`contactId`) references `Contact` (`id`)
    27 ago 2007 13:53:40,770    CREATE TABLE IF NOT EXISTS `BillBreakDown` ( `id` int unsigned NOT NULL auto_increment, `billId` int NOT NULL, `concept` varchar(1024) collate utf8_spanish_ci NOT NULL, `units` decimal(10,2) not null default 1, `amount` decimal(10,2) not null, `iva` decimal(4,2) not null default 16, PRIMARY KEY  (`id`), index `ndx_billBreakDown_bill` (`billId`), CONSTRAINT `fk_billBreakDown_bill` FOREIGN KEY (`billId`) REFERENCES `Bill` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci
    27 ago 2007 13:53:40,770     DROP PROCEDURE IF EXISTS migrate_bill
    27 ago 2007 13:53:40,771  CREATE PROCEDURE migrate_bill() BEGIN DECLARE endOfTable INT DEFAULT 0; DECLARE var_id int; DECLARE var_name varchar(128); DECLARE var_amount decimal(10,2); DECLARE var_amount_final decimal(10,2); DECLARE var_rappel decimal(4,2); DECLARE var_rappel_final decimal(10,2); DECLARE var_creationDate date; DECLARE message varchar(2048); DECLARE cur1 CURSOR FOR SELECT id, name, amount, rappel, creationDate FROM Bill; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET endOfTable = 1;  OPEN cur1;  billTable: loop FETCH cur1 INTO var_id, var_name, var_amount, var_rappel, var_creationDate; IF endOfTable THEN leave billTable; END IF; select var_amount / 1.16 into var_amount_final; INSERT INTO BillBreakDown (billId,concept,units,amount,iva) values (var_id, var_name, 1, var_amount_final,16); IF var_rappel >= 0 THEN select -1 * (var_amount_final * (var_rappel / 100)) into var_rappel_final; INSERT INTO BillBreakDown (billId,concept,units,amount,iva) values (var_id, 'rappel', 1, var_rappel_final,0); END IF; UPDATE Bill set startBillDate = var_creationDate, endBillDate = var_creationDate  where id = var_id;  end loop;   CLOSE cur1; END
    27 ago 2007 13:53:40,771  call migrate_bill()
    27 ago 2007 13:53:40,771  drop procedure if exists migrate_bill
    27 ago 2007 13:53:40,771   UPDATE Bill set state = 'EMITTED' where state IN ('PENDING','ACCEPTED')
    27 ago 2007 13:53:40,771   ALTER TABLE `Bill` DROP COLUMN `rappel`
    27 ago 2007 13:53:40,771  ALTER TABLE `Bill` DROP COLUMN `amount`
    27 ago 2007 13:53:40,771    CREATE TABLE IF NOT EXISTS `RequestHoliday` ( `id` int(10) unsigned NOT NULL auto_increment, `beginDate` datetime NOT NULL, `finalDate` datetime NOT NULL, `state` varchar(16) collate utf8_spanish_ci NOT NULL, `userId` int(11) NOT NULL, `observations` varchar(1024) collate utf8_spanish_ci default NULL, PRIMARY KEY  (`id`), index `ndx_requestHoliday_userId` (`userId`), CONSTRAINT `fk_requestHoliday_userId` FOREIGN KEY (`userId`) REFERENCES `User` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci
    27 ago 2007 13:53:40,771    ALTER TABLE AccountEntry DROP COLUMN  `review`
    27 ago 2007 13:53:40,772    ALTER TABLE Organization ADD COLUMN notes VARCHAR(1024) AFTER ftpsite
    27 ago 2007 13:53:40,772    ALTER TABLE `AccountEntryType` ADD COLUMN `accountEntryTypeId` INTEGER AFTER `observations`, ADD CONSTRAINT `FK_accountentrytype_accountEntryTypeId` FOREIGN KEY `FK_accountentrytype_accountEntryTypeId` (`accountEntryTypeId`) REFERENCES `AccountEntryType` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
    27 ago 2007 13:53:40,772     CREATE TABLE IF NOT EXISTS `Department` ( `id` int unsigned NOT NULL auto_increment, `parentId` int unsigned, `name` varchar(128) not null, `description` varchar(2048) collate utf8_spanish_ci NOT NULL, PRIMARY KEY  (`id`), index `ndx_department_department` (`parentId`), CONSTRAINT `fk_department_department` FOREIGN KEY (`parentId`) REFERENCES `Department` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci
    27 ago 2007 13:53:40,772   INSERT INTO Department (id,name,description) values (1,'Dirección','Departamento de dirección.')
    27 ago 2007 13:53:40,772  INSERT INTO Department (parentId,name,description) values (1,'I+D+I','Departamento de I+D+I.')
    27 ago 2007 13:53:40,772  INSERT INTO Department (parentId,name,description) values (1,'Consultoría','Departamento de Consultoría.')
    27 ago 2007 13:53:40,772     CREATE TABLE IF NOT EXISTS `ContractType` ( `id` int unsigned NOT NULL auto_increment, `name` varchar(128) not null, `description` varchar(2048) collate utf8_spanish_ci NOT NULL, PRIMARY KEY  (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci
    27 ago 2007 13:53:40,772   INSERT INTO ContractType (name,description) values ('Prácticas','Departamento de dirección.')
    27 ago 2007 13:53:40,773  INSERT INTO ContractType (name,description) values ('Duración determinada','Departamento de dirección.')
    27 ago 2007 13:53:40,773  INSERT INTO ContractType (name,description) values ('Indefinido','Departamento de dirección.')
    27 ago 2007 13:53:40,773      ALTER TABLE `User` ADD COLUMN notes VARCHAR(1024) AFTER healthInsurance
    27 ago 2007 13:53:40,773  ALTER TABLE `User` ADD COLUMN photo varchar(255) AFTER notes
    27 ago 2007 13:53:40,773  ALTER TABLE `User` ADD COLUMN endTestPeriodDate date AFTER photo
    27 ago 2007 13:53:40,773  ALTER TABLE `User` ADD COLUMN endContractDate date AFTER endTestPeriodDate
    27 ago 2007 13:53:40,773  ALTER TABLE `User` ADD COLUMN departmentId int unsigned not null default 1 AFTER endContractDate
    27 ago 2007 13:53:40,773  ALTER TABLE `User` ADD index `ndx_user_departmentId` (`departmentId`)
    27 ago 2007 13:53:40,773  ALTER TABLE `User` ADD constraint `fk_user_departmentId` foreign key (`departmentId`) references `Department` (`id`)
    27 ago 2007 13:53:40,773  ALTER TABLE `User` ADD COLUMN contractTypeId int unsigned AFTER departmentId
    27 ago 2007 13:53:40,774  ALTER TABLE `User` ADD index `ndx_user_contractTypeId` (`contractTypeId`)
    27 ago 2007 13:53:40,774  ALTER TABLE `User` ADD constraint `fk_user_contractTypeId` foreign key (`contractTypeId`) references `ContractType` (`id`)
    27 ago 2007 13:53:40,774  ALTER TABLE `User` ADD COLUMN contractObservations varchar(2048) AFTER contractTypeId
    27 ago 2007 13:53:40,774   ALTER TABLE `User` MODIFY COLUMN nif varchar(16)
    27 ago 2007 13:53:40,774     update Version set version='0.2'
    27 ago 2007 13:53:40,774 upgradeDatabase - executing batch of commands
    27 ago 2007 13:53:40,846 upgradeDatabase - >>>> MIGRATION FAILED: WILL BE ROLLED BACK <<<<
    java.sql.BatchUpdateException: Duplicate column name 'contractObservations'
        at com.mysql.jdbc.Statement.executeBatch(Statement.java:919)
        at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
        at com.autentia.intra.manager.data.MigrationManager.upgradeDatabase(MigrationManager.java:136)
        at com.autentia.intra.bean.single.ConsoleBean.migrateDB(ConsoleBean.java:37)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.apache.myfaces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:129)
        at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:63)
        at javax.faces.component.UICommand.broadcast(UICommand.java:106)
        at javax.faces.component.UIViewRoot._broadcastForPhase(UIViewRoot.java:94)
        at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:168)
        at org.apache.myfaces.lifecycle.LifecycleImpl.invokeApplication(LifecycleImpl.java:343)
        at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:86)
        at javax.faces.webapp.FacesServlet.service(FacesServlet.java:137)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:264)
        at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)
        at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
        at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
        at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
        at org.acegisecurity.ui.rememberme.RememberMeProcessingFilter.doFilter(RememberMeProcessingFilter.java:142)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
        at org.acegisecurity.wrapper.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:81)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
        at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
        at org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:106)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
        at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:229)
        at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)
        at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)
        at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.myfaces.webapp.filter.ExtensionsFilter.doFilter(ExtensionsFilter.java:144)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.autentia.intra.filter.UTF8Filter.doFilter(UTF8Filter.java:74)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:263)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:584)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
        at java.lang.Thread.run(Thread.java:595)

     
    • Ivan Zaera Avellon

      Buenos días Rafael:

      El error que te da es muy extraño. Te está dando al convertir la BD de 0.1 (que es como la deja el script createTables.sql) a 0.2. Sin embargo, en el script createTables.sql no aparece la palabra "contractObservations", y en el de migración de 0.1 a 0.2 sólo aparece una vez, en la sentencia:

        ALTER TABLE `User` ADD COLUMN contractObservations varchar(2048) AFTER contractTypeId

      Por lo tanto, no entiendo como se puede quejar el MySQL de que está duplicada la columna "contractObservations"

      ¿Como has creado la base de datos? ¿Con el createDatabase.sql o le has pasado algún script más? Comprueba después de crear la BD y antes de migrar que no exista la columna "contractObservations" en la tabla "User". Comprueba también que no tengas otros esquemas de tntconcept en la base de datos.

      Si sigues teniendo problemas dilo y vemos como podemos obtener más información (a lo mejor mandándonos un backup de la base de datos o algo así).

      Un saludo,
      Ivan

       
      • Rafael Cano

        Rafael Cano - 2007-08-28

        Corregido. He vuelto a crear la base de datos con el script. He ejecutado la aplicación ahora que estaba todo correctamente configurado, y se me ha actualizado la base de datos correctamente.

        Igual la primera vez que se ejecutó el script me falló, porque el usuario programa que uso para el acceso al aplicativo, no tenía todos los permisos necesarios, y claro al ser una actualizacion de estructuras no se puede hacer un rollback, para deshacer los cambios.

        Gracias de nuevo, Ivan.

        Salu2 Rafael Cano
        Jaén - España
        tntconcept(arroba)rafacano.es

         

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks