Problem with datetime formatting

[udi]

The problem might not be specific to datetime formatting, but that's where I'm stuck.

Consider the following query that executes fine directly on mysql:

mysql> SELECT STR_TO_DATE('01:35 PM', '%h:%i %p') as t;
+----------+
| t |
+----------+
| 13:35:00 |
+----------+

However, when I run the exact same query in torndb:

row = conn.get("SELECT STR_TO_DATE('01:35 PM', '%%h:%%i %%p') as t")
print ">>", row.t
I get the following warning (conn is correctly constructed using torndb.Connection):

/Library/Python/2.7/site-packages/torndb.py:226: Warning: Incorrect datetime value: '01:35 PM' for function str_to_date
return cursor.execute(query, kwparameters or parameters)

None

I looked at the mysqld logs, and it seems that both queries are identical in the server, only using torndb I see the following:
290 Query SELECT STR_TO_DATE('01:35 PM', '%h:%i %p') as t
290 Query SHOW WARNINGS

whereas when I run it from mysql, I only see the first line:
46 Query SELECT STR_TO_DATE('01:35 PM', '%h:%i %p') as t

What is going on here?

[oczkers]

I've checked it on latest torndb and 5.5.32-MariaDB but no warning received:

>>> db.query("SELECT STR_TO_DATE('01:35 PM', '%%h:%%i %%p') as t")
[{'t': datetime.timedelta(0, 48900)}]
>>> db.query('SHOW WARNINGS')
[]

What's your mysql version?

[udi]

Using mysql 5.6.13 on a Mac OS, and MySQLdb 1.2.4.
It seems to be a mysqldb-related problem, or somehow the way that torndb parses the parameters. When I skip torndb:

conn = db.connect("localhost", 'xxx')
c = conn.cursor()
c.execute("SELECT STR_TO_DATE('01:35 PM', '%%h:%%i %%p') as t")
main:1: Warning: Incorrect datetime value: '01:35 PM' for function str_to_date
1L

However, when I remove the double %%:

c.execute("SELECT STR_TO_DATE('01:35 PM', '%h:%i %p') as t")
1L
c.fetchone()
(datetime.timedelta(0, 48900),)

thoughts?

[udi]

Found the bug...(well, it's not really a bug, or at least I cannot explain why this happens). In your db connection (line 73 in torndb.py) you have:
sql_mode="TRADITIONAL"

Removing this parameter solves the problem!

[bdarnell]

SQL mode "TRADITIONAL" enables a number of options on the mysql server. Among them are NO_ZERO_DATE and NO_ZERO_IN_DATE, which make the server stricter about invalid DATEs and DATETIMEs. In this case STR_TO_DATE should be returning a TIME, not a DATETIME, so it is not supposed to be affected, but it looks like internally it's triggering some sort of check on the absent date portion of the timestamp. This looks like a bug in mysql. The simplest workaround is to not use TRADITIONAL mode (or at least to unset the NO_ZERO_IN_DATE flag). I think a parameter should be added to the Connection constructor to let the caller choose the SQL mode.

[udi]

So what will happen? should we add a flag to the constructor or nothing is planned? are we using any of the options that "traditional" enables?

[bdarnell]

As the readme says, this project is not actively maintained. I'd merge a pull request that added a sql_mode option to the constructor. I can't tell you whether you are relying on any of the other options implied by "traditional" mode; you'll have to look at your own code to determine that.