Tatiana, John
It'd still be very useful if you'd run it again vanilla mysql,
*with no qserv in the loop*
Jacek
On 11/17/2014 12:14 PM, Tatiana Goldina wrote:I tried setting dumpQueriesOnException=true. Here is the query that produces the mysql.general_log in the previous email:
Connection to LSST DB1 failed
java.sql.SQLException: Qserv error: 'ParseException:ANTLR parse error:unexpected token: /:'
Query being executed when exception was thrown:
/* mysql-connector-java-5.1.6 ( Revision: ${svn.Revision} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
On Nov 17, 2014, at 11:53 AM, Tatiana Goldina <[log in to unmask]> wrote:select * from mysql.general_log
| 2014-11-17 11:39:54 | qsmaster[qsmaster] @ localhost [] | 26 | 0 | Connect | qsmaster@localhost on qservResult |
| 2014-11-17 11:39:54 | qsmaster[qsmaster] @ localhost [] | 26 | 0 | Query | set autocommit=0 |
| 2014-11-17 11:39:54 | qsmaster[qsmaster] @ localhost [] | 26 | 0 | Query | CREATE TABLE IF NOT EXISTS qservResult.message_28666954 (chunkId INT, code SMALLINT, message CHAR(255), timeStamp FLOAT) ENGINE=MEMORY;LOCK TABLES qservResult.message_28666954 WRITE |
| 2014-11-17 11:39:54 | qsmaster[qsmaster] @ localhost [] | 26 | 0 | Query | UNLOCK TABLES |
| 2014-11-17 11:39:54 | qsmaster[qsmaster] @ localhost [] | 26 | 0 | Quit
On Nov 14, 2014, at 11:29 PM, Jacek Becla <[log in to unmask]> wrote:Tatiana, John
Can you do the following:
a) turn on general query log for mysqld by adding
general_log=1
log-output=TABLE
to /etc/my.cnf under [mysqld]
and restarting mysql
b) re-run whatever you are typically running that
is having problems via qserv (have it talk to
the mysqld with general log enabled)
c) send us the output from
select * from mysql.general_log
I will try to re-play it on my local qserv instance
to reproduce things.
Thanks,
Jacek
On 11/14/2014 09:01 PM, Jacek Becla wrote:Nope! It is not the "set" query. I run it by hand,
and I got:
SET @@session.autocommit = 1;
+------------------------------------------+
| no-field-name |
+------------------------------------------+
| Ignoring meaningless command (in Qserv). |
+------------------------------------------+
and after that Qserv was happily running.
---
I don't know how deeply you depend on the query
that Tatiana mentioned earlier:
show columns from DeepSource from LSST;
Because that query will consistently fail right now
with something like:
ERROR 4110 (Proxy): Qserv error: 'ParseException:ANTLR parse
error:unexpected token: show:'
we are not supporting "show columns" commands.
We are working on code that will make implementing this
command easier. Adding support for this is in a quick
and dirty way right now would be non-trivial.
So, Tatiana / Trey, let us know if you can get away
without "show columns" queries in the near term.
Jacek
On 11/12/2014 02:55 PM, Daniel L. Wang wrote:Hi John,
I'm betting that the problem occurs here:
-> 1291 self._execute_query("SET @@session.autocommit =
{0}".format(switch))
"SET..." is not supported right now.
Qserv doesn't really handle much other than SELECT statements. For the
longest time, we were focused on handling those well. Though they aren't
perfect, we think qserv handles them well enough to support some
practical usage now. One area that has gotten very little attention is
in supporting things other than SQL SELECT queries. There is some
short-circuit code that handles the few extra commands that the
command-line "mysql" binary sends without user intervention, because
that's what we test with.
I think in this case, the best idea might be to save the proxy logs from
connecting with the JDBC driver and with the mysql-connector. None of us
(AFAIK) have tested with those, but we could see putting in some more
short-circuit code to triage the "extracurricular" queries they send (we
can put in some semi-reasonable canned responses), and that could very
well get things working. We haven't set aside any space in our Nov
sprint to work on this, but we might be able to squeeze it in, deferring
existing tickets (Jacek will have to decide).
In the meantime, you might be able to workaround this by calling the
mysql binary in a shell process and then processing the output. In
python, this wouldn't be too bad, and I might claim that the code is
only incrementally more complex than using the API, and the performance
(in practice) is mostly indistinguishable for, say, up to 10k or 100k
result rows.
FYI, qserv internally uses mysqldb when connecting to mysqld through
Python, and the raw mysql C-API when connecting with C++.
Hope this helps,
-Daniel
On 11/11/2014 08:27 PM, John Rector wrote:Hi Daniel,
This is a follow-up on my earlier note about trouble accessing Qserv
with JDBC. I tried a python module as well. (Results are not in the
logs I sent you, but I can send them if it's helpful.)
~> pip list | grep mysql
*mysql-connector-python (2.0.2)*
It worked when I tried to connect to a standard MySQL db, but not when
I tried to connect to qserv. Immediately following the failure, I cut
and pasted the qserv connection values I had used into a /mysql/
command in the same terminal session. That connect to the LSST db.
Below is the output from the Python connection attempt.
BTW: is there a preferred Python module to use with qserv? I have a
few, but tried only one tonight—the one from the MySQL site. One more
question, has anyone tried to fill a Pandas data frame directly from a
qserv query?
My ipython session:
In [1]: *import mysql.connector*
In [2]:*cnx = mysql.connector.connect(user='qsmaster',
host='lsst-db1.ipac.caltech.edu <http://lsst-db1.ipac.caltech.edu>',
database='LSST', port=4040)*
---------------------------------------------------------------------------
InternalError Traceback (most recent call
last)
<ipython-input-2-15e0754e8f3a> in <module>()
----> 1 cnx = mysql.connector.connect(user='qsmaster',
host='lsst-db1.ipac.caltech.edu <http://lsst-db1.ipac.caltech.edu>',
database='LSST', port=4040)
/Users/johnrector/anaconda/lib/python2.7/site-packages/mysql/connector/__init__.pyc
in connect(*args, **kwargs)
157
158 # Regular connection
--> 159 return MySQLConnection(*args, **kwargs)
160 Connect = connect # pylint: disable=C0103
161
/Users/johnrector/anaconda/lib/python2.7/site-packages/mysql/connector/connection.pyc
in __init__(self, *args, **kwargs)
127
128 if len(kwargs) > 0:
--> 129 self.connect(**kwargs)
130
131 def _get_self(self):
/Users/johnrector/anaconda/lib/python2.7/site-packages/mysql/connector/connection.pyc
in connect(self, **kwargs)
453 self.disconnect()
454 self._open_connection()
--> 455 self._post_connection()
456
457 def shutdown(self):
/Users/johnrector/anaconda/lib/python2.7/site-packages/mysql/connector/connection.pyc
in _post_connection(self)
433 """
434 self.set_charset_collation(self._charset_id)
--> 435 self.autocommit = self._autocommit
436 if self._time_zone:
437 self.time_zone = self._time_zone
/Users/johnrector/anaconda/lib/python2.7/site-packages/mysql/connector/connection.pyc
in set_autocommit(self, value)
1289 """Toggle autocommit"""
1290 switch = 'ON' if value else 'OFF'
-> 1291 self._execute_query("SET @@session.autocommit =
{0}".format(switch))
1292 self._autocommit = value
1293
/Users/johnrector/anaconda/lib/python2.7/site-packages/mysql/connector/connection.pyc
in _execute_query(self, query)
1495 """
1496 if self._unread_result is True:
-> 1497 raise errors.InternalError("Unread result found.")
1498
1499 self.cmd_query(query)
InternalError: Unread result found.
______________________________
John Rector
IPAC, California Institute of Technology
MS 100-22
770 S. Wilson Ave.
Pasadena, CA 91125
USA
Tel: (626) 395—8577
------------------------------------------------------------------------
Use REPLY-ALL to reply to list
To unsubscribe from the QSERV-L list, click the following link:
https://listserv.slac.stanford.edu/cgi-bin/wa?SUBED1=QSERV-L&A=1
--
--
BEGIN-ANTISPAM-VOTING-LINKS
------------------------------------------------------
Teach CanIt if this mail (ID 04NgISe9c) is spam:
Spam: https://canit.ipac.caltech.edu/canit/b.php?i=04NgISe9c&m=593e092d3ae1&c=s
Not spam: https://canit.ipac.caltech.edu/canit/b.php?i=04NgISe9c&m=593e092d3ae1&c=n
Forget vote: https://canit.ipac.caltech.edu/canit/b.php?i=04NgISe9c&m=593e092d3ae1&c=f
------------------------------------------------------
END-ANTISPAM-VOTING-LINKS
Use REPLY-ALL to reply to list
To unsubscribe from the QSERV-L list, click the following link:
https://listserv.slac.stanford.edu/cgi-bin/wa?SUBED1=QSERV-L&A=1