[MSNoise] dtt computation problem

Sat Apr 18 13:27:08 UTC 2020

On 2020-04-17 13:39, navid.sirous wrote:

> On 2020-04-17 13:05, Thomas Lecocq wrote:
> 
> Hi,
> 
> please try : http://msnoise.org/doc/installation.html?highlight=group#mysql-mariadb-configuration
> 
> Thomas
> 
> Le 17/04/2020 à 10:32, navid.sirous a écrit : 
> 
> Dear MSNoise users,
> 
> I have just installed mMSNoise 1.6.1. When I run "msnoise compute_dtt",
> I faced the following error.
> 
> I appreciate in advance for any help.
> 
> 2020-04-17 08:13:19.415993 msnoise [pid 25050][INFO]: *** Starting:
> Compute DT/T ***
> Traceback (most recent call last):
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py",
> line 1246, in _execute_context
> cursor, statement, parameters, context
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py",
> line 588, in do_execute
> cursor.execute(statement, parameters)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/cursors.py",
> line 170, in execute
> result = self._query(query)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/cursors.py",
> line 328, in _query
> conn.query(q)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/connections.py",
> line 517, in query
> self._affected_rows = self._read_query_result(unbuffered=unbuffered)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/connections.py",
> line 732, in _read_query_result
> result.read()
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/connections.py",
> line 1075, in read
> first_packet = self.connection._read_packet()
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/connections.py",
> line 684, in _read_packet
> packet.check_error()
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/protocol.py",
> line 220, in check_error
> err.raise_mysql_exception(self._data)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/err.py", line
> 109, in raise_mysql_exception
> raise errorclass(errno, errval)
> pymysql.err.InternalError: (1055, "Expression #1 of SELECT list is not
> in GROUP BY clause and contains nonaggregated column 'p3.jobs.ref' which
> is not functionally dependent on columns in GROUP BY clause; this is
> incompatible with sql_mode=only_full_group_by")
> 
> The above exception was the direct cause of the following exception:
> 
> Traceback (most recent call last):
> File "/home/user/anaconda3/bin/msnoise", line 8, in <module>
> sys.exit(run())
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/msnoise/scripts/msnoise.py",
> line 1202, in run
> cli(obj={})
> File "/home/user/anaconda3/lib/python3.7/site-packages/click/core.py",
> line 764, in __call__
> return self.main(*args, **kwargs)
> File "/home/user/anaconda3/lib/python3.7/site-packages/click/core.py",
> line 717, in main
> rv = self.invoke(ctx)
> File "/home/user/anaconda3/lib/python3.7/site-packages/click/core.py",
> line 1137, in invoke
> return _process_result(sub_ctx.command.invoke(sub_ctx))
> File "/home/user/anaconda3/lib/python3.7/site-packages/click/core.py",
> line 956, in invoke
> return ctx.invoke(self.callback, **ctx.params)
> File "/home/user/anaconda3/lib/python3.7/site-packages/click/core.py",
> line 555, in invoke
> return callback(*args, **kwargs)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/click/decorators.py",
> line 17, in new_func
> return f(get_current_context(), *args, **kwargs)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/msnoise/scripts/msnoise.py",
> line 824, in compute_dtt
> main(loglevel=loglevel)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/msnoise/s06compute_dtt.py",
> line 165, in main
> interval=datetime.timedelta(days=interval))
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/msnoise/api.py", line
> 1569, in updated_days_for_dates
> order_by(Job.day).all()
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/query.py",
> line 3233, in all
> return list(self)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/query.py",
> line 3389, in __iter__
> return self._execute_and_instances(context)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/query.py",
> line 3414, in _execute_and_instances
> result = conn.execute(querycontext.statement, self._params)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py",
> line 982, in execute
> return meth(self, multiparams, params)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/elements.py",
> line 293, in _execute_on_connection
> return connection._execute_clauseelement(self, multiparams, params)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py",
> line 1101, in _execute_clauseelement
> distilled_params,
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py",
> line 1250, in _execute_context
> e, statement, parameters, cursor, context
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py",
> line 1476, in _handle_dbapi_exception
> util.raise_from_cause(sqlalchemy_exception, exc_info)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py",
> line 398, in raise_from_cause
> reraise(type(exception), exception, tb=exc_tb, cause=cause)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py",
> line 152, in reraise
> raise value.with_traceback(tb)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py",
> line 1246, in _execute_context
> cursor, statement, parameters, context
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py",
> line 588, in do_execute
> cursor.execute(statement, parameters)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/cursors.py",
> line 170, in execute
> result = self._query(query)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/cursors.py",
> line 328, in _query
> conn.query(q)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/connections.py",
> line 517, in query
> self._affected_rows = self._read_query_result(unbuffered=unbuffered)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/connections.py",
> line 732, in _read_query_result
> result.read()
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/connections.py",
> line 1075, in read
> first_packet = self.connection._read_packet()
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/connections.py",
> line 684, in _read_packet
> packet.check_error()
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/protocol.py",
> line 220, in check_error
> err.raise_mysql_exception(self._data)
> File
> "/home/user/anaconda3/lib/python3.7/site-packages/pymysql/err.py", line
> 109, in raise_mysql_exception
> raise errorclass(errno, errval)
> sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1055,
> "Expression #1 of SELECT list is not in GROUP BY clause and contains
> nonaggregated column 'p3.jobs.ref' which is not functionally dependent
> on columns in GROUP BY clause; this is incompatible with
> sql_mode=only_full_group_by")
> [SQL: SELECT jobs.ref AS jobs_ref, jobs.day AS jobs_day, jobs.pair AS
> jobs_pair, jobs.jobtype AS jobs_jobtype, jobs.flag AS jobs_flag,
> jobs.lastmod AS jobs_lastmod
> FROM jobs
> WHERE jobs.day >= %(day_1)s AND jobs.day <= %(day_2)s AND jobs.jobtype =
> %(jobtype_1)s AND jobs.lastmod >= %(lastmod_1)s GROUP BY jobs.day ORDER
> BY jobs.day]
> [parameters: {'day_1': datetime.date(2016, 7, 1), 'day_2':
> datetime.date(2018, 10, 31), 'jobtype_1': 'DTT', 'lastmod_1':
> datetime.datetime(2020, 4, 16, 4, 13, 19, 472342)}]
> (Background on this error at: http://sqlalche.me/e/2j85)
> 
> Regards,
> 
> Navid
> _______________________________________________
> MSNoise mailing list
> MSNoise at mailman-as.oma.be
> http://mailman-as.oma.be/mailman/listinfo/msnoise _______________________________________________
> MSNoise mailing list
> MSNoise at mailman-as.oma.be
> http://mailman-as.oma.be/mailman/listinfo/msnoise

Dear Thomas, 

Thank you for your help. I added following lines to my.cnf file, but I
still have the same problem!

[mysqld]
character-set-server=utf8
collation-server=utf8_unicode_ci
sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER"

Regards,
Navid
_______________________________________________
MSNoise mailing list
MSNoise at mailman-as.oma.be
http://mailman-as.oma.be/mailman/listinfo/msnoise 

Thank you Thomas. 

The SQL mode NO_AUTO_CREATE_USER was removed in MySQL 8.0, and it's no
longer recognized.  I changed my sql_mode to
"STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION".
I tested this on mysql 8.0.19 and it worked. 

Regards, 

Navid