MySQL version 5.7 issue with ClientJobsDAO.py DATETIME DEFAULT 0

[AS-Mackay]

Just installed nupic on OSX 10.9.
Found MySQL version 5.7 (latest) didn't like like the DATETIME DEFAULT 0 usage in
python/site-packages/nupic/database/ClientJobsDAO.py
Solution used: changed all instances of
DATETIME DEFAULT 0
to
DATETIME DEFAULT CURRENT_TIMESTAMP
... and MySQL was happy again.
Hope this is useful.
Andy

[rhyolight]

@vitaly-krugl @oxtopus Does this concern you at all? Outlier or should we update docs?

@Ww36 Thank you for the note about this, it is helpful.

[vitaly-krugl]

Looking into it to make sure the 0 doesn't have special meaning in the logic. Also, trying it out on my own system.

[vitaly-krugl]

This is related to mysql’s wishy-washy position on the NO_ZERO_DATE flag that has gone and come again starting with 5.7.4. See "SQL Mode Changes in MySQL 5.7" http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes. Apparently, as of mysql 5.7.8, NO_ZERO_DATE is back in the default mode set. Apparently, you can get rid of the mentioned error by removing NO_ZERO_DATE from mysql’s config file (my.ini?). According to this note, "The long term plan is still to have the three affected modes be included in strict SQL mode and to remove them as explicit modes in a future MySQL release. ", but they keep going back and forth to give affected apps additional time to modify their schema.

I think that we need to modify the affected schema to use datetime column attributes that are compatible with mysql 5.6 and 5.7.8+ and don’t break the application logic (update affected logic, if needed), in case something in the logic relies on the original default value.

[rhyolight]

Thanks a lot, @vitaly-krugl. I will create an internal ticket for this and link it to this issue.

[vitaly-krugl]

A temporary workaround is to suppress NO_ZERO_DATE mode when starting mysql (assuming you don't need NO_ZERO_DATE for anything else). Note that according to http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes, this option will eventually go away and the behavior will become part of the "strict" mode.

This may be done either via my.cnf file (my.ini on Windows) or via the --sql-mode command-line option to mysql server. This is explained in http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html. You will probably first want to find out which sql modes are turned on by default, and then pass all sans NO_ZERO_DATE. SELECT @@sql_mode tells the current sql modes that are in effect

I installed mysql 5.7.10 via homebrew, and this is how I start mysql without NO_ZERO_DATE:

# exclude NO_ZERO_DATE
mysql_modes="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
mysql.server start --sql-mode=${mysql_modes}

[rhyolight]

For search reference, this is the error I get when I run into this problem:

Worker completion message: E10003: Exception occurred in Hypersearch Worker: InternalError(1292, u"Incorrect datetime value: '0000-00-00 00:00:00' for column 'end_time' at row 1")
Traceback (most recent call last):
  File "/Users/mtaylor/Library/Python/2.7/lib/python/site-packages/nupic/swarming/HypersearchWorker.py", line 545, in main
    jobID = hst.run()
  File "/Users/mtaylor/Library/Python/2.7/lib/python/site-packages/nupic/swarming/HypersearchWorker.py", line 353, in run
    jsonModelParams, modelParamsHash, particleHash)
  File "/Users/mtaylor/Library/Python/2.7/lib/python/site-packages/nupic/support/decorators.py", line 56, in exceptionLoggingWrap
    return func(*args, **kwargs)
  File "/Users/mtaylor/Library/Python/2.7/lib/python/site-packages/nupic/database/ClientJobsDAO.py", line 2411, in modelInsertAndStart
    return insertModelWithRetries()
  File "/Users/mtaylor/Library/Python/2.7/lib/python/site-packages/nupic/support/decorators.py", line 208, in retryWrap
    result = func(*args, **kwargs)
  File "/Users/mtaylor/Library/Python/2.7/lib/python/site-packages/nupic/database/ClientJobsDAO.py", line 2344, in insertModelWithRetries
    numRowsAffected = conn.cursor.execute(query, sqlParams)
  File "/Users/mtaylor/Library/Python/2.7/lib/python/site-packages/DBUtils-1.1-py2.7.egg/DBUtils/SteadyDB.py", line 631, in tough_method
    raise error # reraise the original error again
InternalError: (1292, u"Incorrect datetime value: '0000-00-00 00:00:00' for column 'end_time' at row 1")

[vitaly-krugl]

Note that datetime default current_timestamp was not supported prior to mysql 5.6.5; reference: http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

[vitaly-krugl]

CURRENT_TIMESTAMP may be inappropriate as default, since it's inconsistent with the semantics of at least two of the columns:

        'start_time              DATETIME DEFAULT 0',
            # When this model started being evaluated
        'end_time                DATETIME DEFAULT 0',
            # When this model completed

[rhyolight]

Thanks to @vitaly-krugl for fixing. I'll need to schedule a release before this fix gets into the binaries. Will try to release soon.