Tried connecting to a plain MySQL DB. These are the commands executed during the connection.
1. duration: 2 ms, connection-id: 1233, statement-id: 1, resultset-id: 1, message: /* mysql-connector-java-5.1.34 ( Revision: [log in to unmask] ) */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 = 'license' OR Variable_name = 'init_connect'
2. duration: 0 ms, connection-id: 1233, statement-id: 1, resultset-id: 2, message: /* mysql-connector-java-5.1.34 ( Revision: [log in to unmask] ) */SELECT @@session.auto_increment_increment
3. duration: 0 ms, connection-id: 1233, statement-id: 999, resultset-id: 0, message: SET NAMES latin1
4. duration: 0 ms, connection-id: 1233, statement-id: 999, resultset-id: 0, message: SET character_set_results = NULL
5. duration: 0 ms, connection-id: 1233, statement-id: 999, resultset-id: 0, message: SET autocommit=1
6. duration: 0 ms, connection-id: 1233, statement-id: 999, resultset-id: 0, message: SET sql_mode='STRICT_TRANS_TABLES'
On Nov 17, 2014, at 12:53 PM, Jacek Becla <[log in to unmask]> wrote:
> 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
