[MSNoise] dtt computation problem

Fri Apr 17 09:09:05 UTC 2020

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