Installation problem with db table setup

Bruce Lin
2011-07-19
2013-01-23
  • Bruce Lin
    Bruce Lin
    2011-07-19

    Hi all,
    I'm installing postfixadmin on a vm.
    When I ran setup.php, it shows:
    DEBUG INFORMATION:
    Invalid query: Invalid default value for 'created'
    After searching this forum, I removed all default value of created and shows :
    :…..for the right syntax to use near ' `active` tinyint() NOT NULL , PRIMARY KEY (`address`) ) ENGINE' at line 6"

    Thanks for help~

     
  • Run setup.php?debug=1 - it will show the queries it executes. Then post the last shown query and the last "updating to version …" line.

    BTW: Which version of PostfixAdmin are you using? Which database (MySQL or PostgreSQL)?

     
  • Bruce Lin
    Bruce Lin
    2011-07-20

    Here is the message:

    Everything seems fine… attempting to create/update database structure

    CREATE TABLE IF NOT EXISTS config ( `id` int(11) not null auto_increment primary key, `name` VARCHAR(20) /*!40100 CHARACTER SET latin1 */ NOT NULL DEFAULT '', `value` VARCHAR(20) /*!40100 CHARACTER SET latin1 */ NOT NULL DEFAULT '', UNIQUE name ( `name` ) ) ENGINE = MYISAM COMMENT = 'PostfixAdmin settings'

    INSERT INTO config (name, value) VALUES ('version', '0')

    Updating database:

    - old version: 0; target version: 740

    updating to version 1 (MySQL)…

    CREATE TABLE IF NOT EXISTS admin ( `username` varchar(255) NOT NULL default '', `password` varchar(255) NOT NULL default '', `created` datetime NOT NULL default '0000-00-00 00:00:00', `modified` datetime NOT NULL default '0000-00-00 00:00:00', `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`username`) ) ENGINE=MyISAM COMMENT='Postfix Admin - Virtual Admins';

    DEBUG INFORMATION:
    Invalid query: Invalid default value for 'created'

    Environment:
    Ubuntu 10.04.2LTS
    Apache 2.2.14
    PHP/5.3.2-1ubuntu4.9
    postfixadmin 2.3
    MariaDB 5.1.49

     
  • Bruce Lin
    Bruce Lin
    2011-07-20

    Uh…the version of postfix is 2.3.3 not 2.3.

     
  • You/MariaDB seems to have enabled SQL strict mode - and the "default 0000-00-00" is known to be incompatible with strict mode.

    Workaround: replace all "0000-00-00" in upgrade.php with "2000-01-01".

     
  • Bruce Lin
    Bruce Lin
    2011-07-21

    Thanks, It works!
    But here is a new problem when I'm going to add a manager.
    It shows:

    DEBUG INFORMATION:
    Invalid query: Field 'description' doesn't have a default value

    And nothing is added to the db nor can I login into postfixadmin.

     
  • Bruce Lin
    Bruce Lin
    2011-07-21

    After turning off strict mode, all problem is solved.
    Thanks for your help.