Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

make_superuser fails with Incorrect datetime value for last_password_change_utc #2

Closed
martinburchell opened this issue May 24, 2019 · 3 comments

Comments

@martinburchell
Copy link
Collaborator

commented May 24, 2019

  • Ubuntu 18.04
  • MySQL 5.7.26
  • camcops-server 2.2.7 from PyPI

I get the error below when trying to create a superuser. MySQL doesn't like the timezone (+0000) component in the datetime


(.ve) [14:26:08 bin]$ camcops make_superuser
2019-05-24 14:26:09.007 [p10292.t140028592371520] camcops_server.camcops:INFO: CamCOPS starting
2019-05-24 14:26:12.150 [p10292.t140028592371520] camcops_server.camcops:INFO: CamCOPS server version 2.2.7
2019-05-24 14:26:12.150 [p10292.t140028592371520] camcops_server.camcops:INFO: By Rudolf Cardinal. See http://www.camcops.org/
2019-05-24 14:26:12.150 [p10292.t140028592371520] camcops_server.camcops:INFO: Using 92 tasks
2019-05-24 14:26:12.150 [p10292.t140028592371520] camcops_server.camcops:INFO: Using configuration file: 'camcops.cfg'
2019-05-24 14:26:12.214 [p10292.t140028592371520] camcops_server.cc_modules.cc_config:INFO: Reading from camcops.cfg
2019-05-24 14:26:12.216 [p10292.t140028592371520] cardinal_pythonlib.configfiles:WARNING: Configuration variable CAMCOPS_LOGO_FILE_ABSOLUTE not found or improper; using default of /home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/camcops_server/static/logo_camcops.png
2019-05-24 14:26:12.232 [p10292.t140028592371520] cardinal_pythonlib.sqlalchemy.alembic_func:INFO: Intended database version: 0004
2019-05-24 14:26:12.239 [p10292.t140028592371520] alembic.runtime.migration:INFO: Context impl MySQLImpl.
2019-05-24 14:26:12.240 [p10292.t140028592371520] alembic.runtime.migration:INFO: Will assume non-transactional DDL.
2019-05-24 14:26:12.240 [p10292.t140028592371520] cardinal_pythonlib.sqlalchemy.alembic_func:INFO: Current database version: 0004
Username for new superuser (or to gain superuser status): admin
2019-05-24 14:26:14.096 [p10292.t140028592371520] camcops_server.camcops:INFO: Creating superuser 'admin'
New password for user admin:
New password for user admin (again):
2019-05-24 14:26:19.408 [p10292.t140028592371520] camcops_server.camcops:INFO: Success
Traceback (most recent call last):
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 508, in do_execute
    cursor.execute(statement, parameters)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/MySQLdb/cursors.py", line 412, in _query
    rowcount = self._do_query(q)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/MySQLdb/cursors.py", line 375, in _do_query
    db.query(q)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/MySQLdb/connections.py", line 276, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.OperationalError: (1292, "Incorrect datetime value: '2019-05-24T13:26:19.402719+00:00' for column 'last_password_change_utc' at row 1")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/martinb/personal/cc-server-pypi/.ve/bin/camcops", line 11, in <module>
    load_entry_point('camcops-server==2.2.7', 'console_scripts', 'camcops')()
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/camcops_server/camcops.py", line 1307, in main
    camcops_main()
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/camcops_server/camcops.py", line 1292, in camcops_main
    success = progargs.func(progargs)  # type: Optional[bool]
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/camcops_server/camcops.py", line 1048, in <lambda>
    username=args.username
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/camcops_server/camcops.py", line 549, in make_superuser
    return False
  File "/usr/lib/python3.6/contextlib.py", line 88, in __exit__
    next(self.gen)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/camcops_server/cc_modules/cc_request.py", line 1097, in command_line_request_context
    req._finish_dbsession()
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/camcops_server/cc_modules/cc_request.py", line 284, in _finish_dbsession
    session.commit()
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 943, in commit
    self.transaction.commit()
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 467, in commit
    self._prepare_impl()
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 447, in _prepare_impl
    self.session.flush()
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2254, in flush
    self._flush(objects)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2380, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2344, in _flush
    flush_context.execute()
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute
    rec.execute(self)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute
    uow
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
    mapper, table, insert)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 866, in _emit_insert_statements
    execute(statement, params)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 508, in do_execute
    cursor.execute(statement, parameters)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/MySQLdb/cursors.py", line 412, in _query
    rowcount = self._do_query(q)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/MySQLdb/cursors.py", line 375, in _do_query
    db.query(q)
  File "/home/martinb/personal/cc-server-pypi/.ve/lib/python3.6/site-packages/MySQLdb/connections.py", line 276, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1292, "Incorrect datetime value: '2019-05-24T13:26:19.402719+00:00' for column 'last_password_change_utc' at row 1") [SQL: 'INSERT INTO _security_users (username, fullname, email, hashedpw, last_login_at_utc, last_password_change_utc, superuser, must_change_password, when_agreed_terms_of_use, upload_group_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: ('admin', None, None, '$2a$06$Ehj9.WGlucJ.Xy2JxQghZOJTpekJofK4Ag8t1TP5gsYccd2GUX47q', None, DateTime(2019, 5, 24, 13, 26, 19, 402719, tzinfo=Timezone('UTC')), 1, 0, None, None)] (Background on this error at: http://sqlalche.me/e/e3q8)

@RudolfCardinal

This comment has been minimized.

Copy link
Owner

commented May 24, 2019

Thanks very much for reporting this. I think the fix will probably be to change camcops_server.cc_modules.cc_user.User.set_password so that "self.last_password_change_utc = req.now_utc" becomes "self.last_password_change_utc = req.now_utc_no_tzinfo"; will implement and test soon. I suspect the reason you've seen it and I didn't probably relates to the intermediate driver and how it handles pendulum.DateTime objects with DATETIME fields. I'm using PyMySQL==0.7.1 and a database URL like "mysql+pymysql://USERNAME:PASSWORD@127.0.0.1:3306/DATABASENAME?charset=utf8mb4"; that may be a quick workaround if you are using a different driver like mysqldb (which I see is the one in the docs!). May be a few days before I can make the change for time constraint reasons. Thanks again.

@martinburchell

This comment has been minimized.

Copy link
Collaborator Author

commented May 25, 2019

@RudolfCardinal your workaround works. Thanks!

@RudolfCardinal

This comment has been minimized.

Copy link
Owner

commented May 27, 2019

Specific fix to this and one other field made (and working with mysqlclient==1.3.13) -- but also a generic fix to the conversion system as advocated at https://pypi.org/project/pendulum/ (section headed "Limitations"), which also works. The fix will be out with CamCOPS server 2.3.3 or higher. Thanks again for the report!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.