[Koha-bugs] [Bug 1857] New: updatedatabase fails w/ constraint

[bugzilla-daemon at pippin.metavore.com]

http://bugs.koha.org/cgi-bin/bugzilla/show_bug.cgi?id=1857

           Summary: updatedatabase fails w/ constraint
           Product: Koha
           Version: HEAD
          Platform: All
        OS/Version: All
            Status: NEW
          Severity: major
          Priority: P3
         Component: Installation (web-based installer)
        AssignedTo: henridamien at koha-fr.org
        ReportedBy: joe.atzberger at liblime.com
         QAContact: koha-bugs at nongnu.org


After git rebase, during web update:

ERROR 1005 (HY000): Can't create table './atz_koha/printers_profile.frm'
(errno: 150)

Error at updatedatabase.pl line 993. 

The command is:

    $dbh->do("CREATE TABLE `printers_profile` (
            `prof_id` int(4) NOT NULL auto_increment,
            `printername` varchar(40) NOT NULL,
            `tmpl_id` int(4) NOT NULL,
            `paper_bin` varchar(20) NOT NULL,
            `offset_horz` float default NULL,
            `offset_vert` float default NULL,
            `creep_horz` float default NULL,
            `creep_vert` float default NULL,
            `unit` char(20) NOT NULL default 'POINT',
            PRIMARY KEY  (`prof_id`),
            UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
            CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`)
REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");


The same error is returned from mysql client when attempting that command.  The
problem is the CONSTRAINT line.   When I remove the CONSTRAINT, the command
completes OK.  If I then try to add the constraint I get the original error:

mysql> alter table `printers_profile` add CONSTRAINT `printers_profile_pnfk_1`
FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE
CASCADE ON UPDATE CASCADE;
ERROR 1005 (HY000): Can't create table './atz_koha/#sql-6733_3d9eb.frm' (errno:
150)

Possible cause: labels_templates.tmpl_id defaults to NULL (even though it is
set NOT NULL), so this violates the foreign key constraint since
printers_profile.tmpl_id is NOT NULL.  

| Field                    | Type  | Collation | Null | Key | Default | Extra   
+--------------------------+-------+-----------+------+-----+---------+------------
| labels_templates.tmpl_id | int(4)| NULL      | NO   | PRI | NULL    |
auto_increment
| printers_profile.tmpl_id | int(4)| NULL      | NO   |     |         |

It might be enough to change the former's Default to 1 (or 0).




------- You are receiving this mail because: -------
You are the QA contact for the bug, or are watching the QA contact.