Error during Pandora DB Schema upgrade 2.1 to 3.0

[benni]

I've tried with update.php and also with mysql command. The same error.
ERROR 1005 (HY000) at line 101: Can't create table './pandora/talert_template_modules.frm' (errno: 150)

Install Steps:
- uninstall Pandora 2.1
- install Pandora Server/Console with deb Packages
- killed all existing pandora processes
- try to upgrade db how is described in Pandora 3.0 Doc
- error 1005

System is Ubuntu Server 9.04

[Sancho Lerena]

Do you have innodb support enabled in your MySQL Server ?. Pandora 3.0 Database needed it.

Execute SHOW INNODB STATUS in the mysql client after getting this error. This will show you more information about this error.

I've tried with update.php and also with mysql command. The same error.
ERROR 1005 (HY000) at line 101: Can't create table './pandora/talert_template_modules.frm' (errno: 150)

Install Steps:
- uninstall Pandora 2.1
- install Pandora Server/Console with deb Packages
- killed all existing pandora processes
- try to upgrade db how is described in Pandora 3.0 Doc
- error 1005

System is Ubuntu Server 9.04

[benni]

=====================================
100111 13:29:15 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 40 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 31, signal count 31
Mutex spin waits 0, rounds 200, OS waits 10
RW-shared spins 36, OS waits 18; RW-excl spins 3, OS waits 3
------------------------
LATEST FOREIGN KEY ERROR
------------------------
100111 13:28:15 Error in foreign key constraint of table pandora/talert_template_modules:
FOREIGN KEY (`id_agent_module`) REFERENCES tagente_modulo(`id_agente_modulo`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`id_alert_template`) REFERENCES talert_templates(`id`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  UNIQUE (`id_agent_module`, `id_alert_template`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8:
Cannot resolve table name close to:
(`id_agente_modulo`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`id_alert_template`) REFERENCES talert_templates(`id`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  UNIQUE (`id_agent_module`, `id_alert_template`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

[benni]

Did you found a solution?

[daniels]

Take a look at: http://openideas.info/wiki/index.php?title=Pandora_3.0:Documentation_en:Anexo_Upgrade#Problems_upgrading_from_2.x_.28Database.29

This error is expected if you come from 1.x version before 2.x...

You have to do manually this, before running the upgrade script:
ALTER TABLE tagente_modulo MODIFY id_agente_modulo bigint(10) unsigned NOT NULL auto_increment;

Regards.

[Sancho Lerena]

Another "Quick" way to avoid problems is to "delete" all integral reference information for the migration SQL. This will help to avoid this problems.

Another quick way is disable innodb, but performance would be degraded as well in big enviroments ( > 2000 modules.)

[benni]

ALTER TABLE tagente_modulo MODIFY id_agente_modulo bigint(10) unsigned NOT NULL auto_increment;

This has fixed it.

Thanks.