#5 not MySQL strict compliant

closed-fixed
nobody
Database (41)
5
2016-11-01
2007-04-12
Anonymous
No

Trying to run the database script on a database with strict mode is an exercise in futility. Why dates are stored as 0000-00-00 instead of being proper nulls?

Discussion

  • GingerDog

    GingerDog - 2007-10-09

    Logged In: YES
    user_id=1761957
    Originator: NO

    Hmm... I had no idea MySQL had a 'strict' mode.

    Anyone know how to do this?

    Would making it compliant with 'strict' mode affect PostgreSQL compatibility?

    (I suspect not, but it's something to be careful about)

     
  • Christian Boltz

    Christian Boltz - 2007-10-09
    • milestone: --> SVN_(please_specify_revision!)
     
  • Christian Boltz

    Christian Boltz - 2007-10-09

    Logged In: YES
    user_id=593261
    Originator: NO

    Gingerdog, you can real all the details on http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

    The reporter unfortunately didn't mention where he found problems. According to grep, I think the problem is located in DATABASE_MYSQL.TXT which has several datetime fields with default '0000-00-00'.

    (Database design (especially encoding) and upgrading has to be discussed anyway. I'll start a thread on the -devel mailinglist.)

     
  • Christian Boltz

    Christian Boltz - 2016-04-25
    • status: open --> closed-fixed
     
  • Christian Boltz

    Christian Boltz - 2016-04-25

    I just fixed this in SVN trunk r1835 (3.0 final will have the fix) - the 'created' and 'modified' fields now have a valid default value.

    This means the oldest open bug (and the last one-digit bug) is finally fixed, even if it needed a duplicate (bug 373) as reminder ;-)

     
  • Gabor 'Morc' KORMOS

    I'm sorry to spoil the party, very late arrival too, but your fix does not work on MySQL 5.7.16-0ubuntu0.16.04.1 (Ubuntu 16.04 LTS as of this writing), upgrading from Postfixadmin 2.93. I could import the database into this MySQL version (the previous on was 5.5 on Ubuntu 12.04 LTS) because mysqldump basically sets a very permissive SQL mode. Anyhow the problem is that the ALTER TABLE commands fail on tables which have more than one field with the zero date/time value. For these tables both fields must be updates in the same query, so upgrade.php should look like this:

    foreach (array('domain_admins', 'vacation') as $table_to_change) {
        $table = table_by_key($table_to_change);
        db_query_parsed("ALTER TABLE `$table` CHANGE `created` `created` {DATETIME}");
    }
    
    foreach (array('admin', 'alias', 'alias_domain', 'domain', 'mailbox') as $table_to_change) {
        $table = table_by_key($table_to_change);
        db_query_parsed("ALTER TABLE `$table` CHANGE `created` `created` {DATETIME}, CHANGE `modified` `modified` {DATETIME}");
    }
    

    I test-drove this modification and works fine.

     
  • Christian Boltz

    Christian Boltz - 2016-11-01

    Thanks for reporting this and providing the fix!

    Commited to SVN trunk r1881, so it will be fixed in the next release (3.0.2 or 3.1, whatever number we take).

     

Log in to post a comment.