Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

#52 "invalid query" in upgrade.php (caused by foreign keys?)

closed-fixed
nobody
8
2013-12-01
2008-01-20
Anonymous
No

SVN version 2008-01-19T16:48:15.336774Z
297

in file backup.php

- $result = db_query ("SHOW CREATE TABLE ".table_by_pos($i));
+ $result = db_query ("SHOW CREATE TABLE $tables[$i]");

- $result = db_query ("SELECT * FROM ".table_by_pos($i));
+ $result = db_query ("SELECT * FROM $tables[$i]");

- $tables = array('admin','alias','config','domain','domain_admins','log','mailbox','vacation','vacation_notification');
+ $tables = array('admin','alias','domain','domain_admins','log','mailbox','vacation','vacation_notification');

Discussion

  • Logged In: YES
    user_id=593261
    Originator: NO

    Hmm, I can't see what fails here - I just tested and the backup seems to work.

    Can you please add some details? What is the problem you want to solve?

    BTW: Your $tables does not include the "config" table. If you have updated from an earlier version of postfixadmin, please run upgrade.php to create it (and to update your other tables also).

     
  • Logged In: NO

    sorry, I run upgrade.php and problem was solved. But when I run the upgrade.php, I saw this problem:

    ALTER TABLE `vacation` CHANGE `email` `email` VARCHAR( 255 ) /*!40100 CHARACTER SET latin1 COLLATE latin1_swedish_ci */ NOT NULL

    DEBUG INFORMATION:
    Invalid query: Error on rename of './postfix/#sql-33c0_2b1' to './postfix/vacation' (errno: 150)

    if I remove table vacation and vacation_notificaion and then I create it using

    CREATE TABLE `vacation` (
    `email` varchar(255) /*!40100 CHARACTER SET latin1 COLLATE latin1_swedish_ci */ NOT NULL default '',
    `subject` varchar(255) /*!40100 CHARACTER SET latin1 COLLATE latin1_swedish_ci */ NOT NULL default '',
    `body` text NOT NULL,
    `cache` text NOT NULL,
    `domain` varchar(255) /*!40100 CHARACTER SET latin1 COLLATE latin1_swedish_ci */ NOT NULL default '',
    `created` datetime NOT NULL default '0000-00-00 00:00:00',
    `active` tinyint(4) NOT NULL default '1',
    PRIMARY KEY (`email`),
    KEY `email` (`email`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Postfix Admin - Virtual Vacation';

    everything is done, but I can`t create table vacation_notification using

    CREATE TABLE vacation_notification (
    on_vacation varchar(255) NOT NULL,
    notified varchar(255) NOT NULL,
    notified_at timestamp NOT NULL default now(),
    CONSTRAINT vacation_notification_pkey PRIMARY KEY(on_vacation, notified),
    FOREIGN KEY (on_vacation) REFERENCES vacation(email) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci TYPE=InnoDB
    COMMENT='Postfix Admin - Virtual Vacation Notifications';

    I obtain error that table /postfix/vacation_notification.frm can`t be created errono=150

     
  • Logged In: YES
    user_id=593261
    Originator: NO

    The comment from "nobody" on 2008-01-21 17:21 shows two problems:

    1) the "invalid query" shown by upgrade.php

    I'll have to check this, probably it is caused by the foreign keys, which make altering tables an interesting challenge ;-)

    2) creation of vacation_notification fails

    This part looks like a duplicate of
    http://sourceforge.net/tracker/index.php?func=detail&aid=1828857&group_id=191583&atid=937964

     
    • priority: 5 --> 8
     
    • summary: can`t create backup from menu --> "invalid query" in upgrade.php (caused by foreign keys?)
     
  • Logged In: YES
    user_id=593261
    Originator: NO

    This should be fixed in the latest SVN version (r319). Can you please test it by running upgrade.php?

    Note: I can't guarantee a clean update. If something goes wrong (you'll see an "Invalid query" message in this case), please drop the table vacation_notification (it contains a list of mail adresses who have received vacation notifications already, so the worst thing that might happen is that some of them receive another one when sending the next mail).

     
  • Logged In: YES
    user_id=593261
    Originator: NO

    Fix verified - I didn't notice any problems with upgrade.php when creating a new database and also not when updating a server from an earlier SVN revision of Postfixadmin.

     
    • status: open --> closed-fixed