[MSNoise] MSNoise stack

Sat Aug 18 19:22:21 UTC 2018

Hi Yawar,

You have to configure your MySQL (my.cnf or elsewhere) to use the 
"traditional group by mode", see:

https://github.com/ROBelgium/MSNoise/issues/72

Cheers,

Thomas

Le 18/08/2018 à 15:01, Yawar Hussain a écrit :
> The stacking on msnoise example data is perfectly alright.
>
>
> Regards,
> Yawar Hussain
> Ph.D. Student, Geotechnical Engineering
> Dpt. Civil and Environmental Engg.
> University of Brasilia, Brazil.
>
>
> On Sat, Aug 18, 2018 at 9:55 AM Yawar Hussain <yawar.pgn at gmail.com> wrote:
>
>> Hello,
>>
>> I have problem with stacking:
>>
>> yawar at yawar-Vostro-5470:~/anaconda3/bin$ msnoise stack -r
>> Lets STACK !
>> 2018-08-18 09:52:14 [DEBUG] Starting the ref stack
>> []
>> 2018-08-18 09:52:14 [DEBUG] Processing LS_DF01:LS_DF02-ZZ-1
>> Traceback (most recent call last):
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
>> line 1139, in _execute_context
>>      context)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/default.py",
>> line 450, in do_execute
>>      cursor.execute(statement, parameters)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/cursors.py",
>> line 166, in execute
>>      result = self._query(query)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/cursors.py",
>> line 322, in _query
>>      conn.query(q)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py",
>> line 835, in query
>>      self._affected_rows = self._read_query_result(unbuffered=unbuffered)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py",
>> line 1019, in _read_query_result
>>      result.read()
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py",
>> line 1302, in read
>>      first_packet = self.connection._read_packet()
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py",
>> line 981, in _read_packet
>>      packet.check_error()
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py",
>> line 393, in check_error
>>      err.raise_mysql_exception(self._data)
>>    File "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/err.py",
>> line 107, 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 'msnoise.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/yawar/anaconda3/bin/msnoise", line 11, in <module>
>>      sys.exit(run())
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/msnoise/scripts/msnoise.py",
>> line 614, in run
>>      cli(obj={})
>>    File "/home/yawar/anaconda3/lib/python3.5/site-packages/click/core.py",
>> line 716, in __call__
>>      return self.main(*args, **kwargs)
>>    File "/home/yawar/anaconda3/lib/python3.5/site-packages/click/core.py",
>> line 696, in main
>>      rv = self.invoke(ctx)
>>    File "/home/yawar/anaconda3/lib/python3.5/site-packages/click/core.py",
>> line 1060, in invoke
>>      return _process_result(sub_ctx.command.invoke(sub_ctx))
>>    File "/home/yawar/anaconda3/lib/python3.5/site-packages/click/core.py",
>> line 889, in invoke
>>      return ctx.invoke(self.callback, **ctx.params)
>>    File "/home/yawar/anaconda3/lib/python3.5/site-packages/click/core.py",
>> line 534, in invoke
>>      return callback(*args, **kwargs)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/msnoise/scripts/msnoise.py",
>> line 301, in stack
>>      main('ref', interval)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/msnoise/s04stack.py",
>> line 203, in main
>>      updated_days = updated_days_for_dates(db, start, end,
>> pair.replace('_', '.'), jobtype='CC',
>> interval=datetime.timedelta(days=interval),returndays=True)
>>    File "/home/yawar/anaconda3/lib/python3.5/site-packages/msnoise/api.py",
>> line 1189, in updated_days_for_dates
>>      days = session.query(Job).filter(Job.pair == pair).filter(Job.day >=
>> date1).filter(Job.day <= date2).filter(Job.jobtype ==
>> jobtype).filter(Job.lastmod >=
>> lastmod).group_by(Job.day).order_by(Job.day).all()
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
>> line 2613, in all
>>      return list(self)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
>> line 2761, in __iter__
>>      return self._execute_and_instances(context)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
>> line 2776, in _execute_and_instances
>>      result = conn.execute(querycontext.statement, self._params)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
>> line 914, in execute
>>      return meth(self, multiparams, params)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/elements.py",
>> line 323, in _execute_on_connection
>>      return connection._execute_clauseelement(self, multiparams, params)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
>> line 1010, in _execute_clauseelement
>>      compiled_sql, distilled_params
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
>> line 1146, in _execute_context
>>      context)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
>> line 1341, in _handle_dbapi_exception
>>      exc_info
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py",
>> line 202, in raise_from_cause
>>      reraise(type(exception), exception, tb=exc_tb, cause=cause)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py",
>> line 185, in reraise
>>      raise value.with_traceback(tb)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
>> line 1139, in _execute_context
>>      context)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/default.py",
>> line 450, in do_execute
>>      cursor.execute(statement, parameters)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/cursors.py",
>> line 166, in execute
>>      result = self._query(query)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/cursors.py",
>> line 322, in _query
>>      conn.query(q)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py",
>> line 835, in query
>>      self._affected_rows = self._read_query_result(unbuffered=unbuffered)
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py",
>> line 1019, in _read_query_result
>>      result.read()
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py",
>> line 1302, in read
>>      first_packet = self.connection._read_packet()
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py",
>> line 981, in _read_packet
>>      packet.check_error()
>>    File
>> "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py",
>> line 393, in check_error
>>      err.raise_mysql_exception(self._data)
>>    File "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/err.py",
>> line 107, 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 'msnoise.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 \nFROM jobs \nWHERE
>> jobs.pair = %(pair_1)s AND 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: {'lastmod_1':
>> datetime.datetime(2018, 8, 17, 9, 52, 14, 839671), 'jobtype_1': 'CC',
>> 'day_2': datetime.date(2017, 12, 13), 'day_1': datetime.date(2017, 12, 9),
>> 'pair_1': 'LS.DF01:LS.DF02'}]
>>
>>
>>
>>
>> Regards,
>> Yawar Hussain
>> Ph.D. Student, Geotechnical Engineering
>> Dpt. Civil and Environmental Engg.
>> University of Brasilia, Brazil.
>>
> _______________________________________________
> MSNoise mailing list
> MSNoise at mailman-as.oma.be
> http://mailman-as.oma.be/mailman/listinfo/msnoise