Re: [MSNoise] MSNoise Digest, Vol 49, Issue 6

Send MSNoise mailing list submissions to msnoise(a)mailman-as.oma.be To subscribe or unsubscribe via the World Wide Web, visit http://mailman-as.oma.be/mailman/listinfo/msnoise or, via email, send a message with subject or body 'help' to msnoise-request(a)mailman-as.oma.be You can reach the person managing the list at msnoise-owner(a)mailman-as.oma.be When replying, please edit your Subject line so it is more specific than "Re: Contents of MSNoise digest..." Today's Topics: 1. MSNoise stack (Yawar Hussain) 2. Re: MSNoise stack (Yawar Hussain) 3. Re: MSNoise stack (Thomas Lecocq) ---------------------------------------------------------------------- Message: 1 Date: Sat, 18 Aug 2018 09:55:12 -0300 From: Yawar Hussain &lt;yawar.pgn(a)gmail.com&gt; To: msnoise(a)mailman-as.oma.be Subject: [MSNoise] MSNoise stack Message-ID: < CAP7arHRVjawZnP_a-iBU8Wkwx6FbWXQSSFPsNE01Aqc78fQt1Q(a)mail.gmail.com&gt; Content-Type: text/plain; charset="UTF-8" Hello, I have problem with stacking: yawar@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. ------------------------------ Message: 2 Date: Sat, 18 Aug 2018 10:01:01 -0300 From: Yawar Hussain &lt;yawar.pgn(a)gmail.com&gt; To: msnoise(a)mailman-as.oma.be Subject: Re: [MSNoise] MSNoise stack Message-ID: <CAP7arHSFQndNYOUjAKwscc8gRpTfhkH8io= eURG3yUBMkg5_5g(a)mail.gmail.com&gt; Content-Type: text/plain; charset="UTF-8" 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 &lt;yawar.pgn(a)gmail.com&gt; wrote: "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/default.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/err.py", which "/home/yawar/anaconda3/lib/python3.5/site-packages/msnoise/scripts/msnoise.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/msnoise/scripts/msnoise.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/msnoise/api.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/orm/query.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/orm/query.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/orm/query.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/default.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/err.py", dependent jobs.day %(day_2)s BY 9), ------------------------------ Message: 3 Date: Sat, 18 Aug 2018 21:22:21 +0200 From: Thomas Lecocq &lt;Thomas.Lecocq(a)seismology.be&gt; To: msnoise(a)mailman-as.oma.be Subject: Re: [MSNoise] MSNoise stack Message-ID: &lt;0f6f23ea-65b4-5fa8-2d04-b8c8965cbeaa(a)seismology.be&gt; Content-Type: text/plain; charset=utf-8; format=flowed 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?: wrote: "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/default.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", self._read_query_result(unbuffered=unbuffered) "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", >> line 1019, in _read_query_result >> result.read() "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", >> line 1302, in read >> first_packet = self.connection._read_packet() "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", >> line 981, in _read_packet >> packet.check_error() "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/err.py", in 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()) "/home/yawar/anaconda3/lib/python3.5/site-packages/msnoise/scripts/msnoise.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/click/core.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/click/core.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/click/core.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/click/core.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/click/core.py", >> line 534, in invoke >> return callback(*args, **kwargs) "/home/yawar/anaconda3/lib/python3.5/site-packages/msnoise/scripts/msnoise.py", "/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() "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/orm/query.py", >> line 2613, in all >> return list(self) "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/orm/query.py", >> line 2761, in __iter__ >> return self._execute_and_instances(context) "/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) "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py", >> line 914, in execute >> return meth(self, multiparams, params) "/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) "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py", >> line 1010, in _execute_clauseelement >> compiled_sql, distilled_params "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py", >> line 1146, in _execute_context >> context) "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py", >> line 1341, in _handle_dbapi_exception >> exc_info "/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) "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py", >> line 185, in reraise >> raise value.with_traceback(tb) "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/default.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", self._read_query_result(unbuffered=unbuffered) "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", >> line 1019, in _read_query_result >> result.read() "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", >> line 1302, in read >> first_packet = self.connection._read_packet() "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", >> line 981, in _read_packet >> packet.check_error() "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/connections.py", "/home/yawar/anaconda3/lib/python3.5/site-packages/pymysql/err.py", dependent jobs.day %(day_2)s GROUP BY 9), ------------------------------ _______________________________________________ MSNoise mailing list MSNoise(a)mailman-as.oma.be http://mailman-as.oma.be/mailman/listinfo/msnoise End of MSNoise Digest, Vol 49, Issue 6 **************************************