DateTimeFields are broken

[Nixarn]

Hey, so this is weird and annoying. I wanted to deploy a django app on the app engine using the cloud sql db. But I kept having problems getting DateTimeFields to work. So I ended up creating a new project, by cloning this one, and oddly enough same problem happens here.

To replicate what I did was:

1. Follow tutorial and deploy app
2. Create a Question (it only has a date)
3. Accessing the site on localhost shows "Date published" just fine, but it's always blank when accessing the remote url.

Edit: So I made a Question, then created a new view:

def index(request):
    q = Question.objects.all()[0]
    print q.pub_date

Locally that outputs the date (connecting to the same CloudSQL db) but accessing the remote site prints None in the log for pub_date.

[Nixarn]

So I started digging to find out where it turned to None. In db/backends/mysql/base.py, line 133 I did a "print "Mysql cursor result", self.cursor.fetchall()", the log shows:

Mysql cursor result ((1L, u'test', None),)

But on my computer it shows:

Mysql cursor result ((1L, u'test', datetime.datetime(2016, 2, 8, 15, 24, 44)),)

When I used the mysql cli to check the db, it does look correct, so seems the problem might be in mysql-python?

[Nixarn]

Okay, so here we go, MySQLdb on the AppEngine, connected to CloudSQL is broken.

Here's my database:

mysql> select * from polls_question;
+----+---------------+----------------------------+
| id | question_text | pub_date                   |
+----+---------------+----------------------------+
|  1 | test          | 2016-02-08 15:24:44.000000 |
+----+---------------+----------------------------+
1 row in set (0.16 sec)

And here's the simplified example I did

import MySQLdb
import os
import webapp2

class IndexPage(webapp2.RequestHandler):
  def get(self):
    self.response.headers['Content-Type'] = 'text/plain'
    env = os.getenv('SERVER_SOFTWARE')
    if (env and env.startswith('Google App Engine/')):
    # Connecting from App Engine
      db = MySQLdb.connect(
      unix_socket='/cloudsql/<removed>:<removed>',
      user='root',db='gaetest')
    else:
      # Connecting from an external network.
      # Make sure your network is whitelisted
      db = MySQLdb.connect(
      host='<removed>',
      port=3306,
      user='root', passwd='<removed>',db='<removed>')

    cursor = db.cursor()
    cursor.execute('SELECT * FROM polls_question')
    for r in cursor.fetchall():
      self.response.write('%s\n' % str(r))

    db.close()

app = webapp2.WSGIApplication([
    ('/', IndexPage),
    ])

On my computer it gives me:

(1L, 'test', datetime.datetime(2016, 2, 8, 15, 24, 44))

and when I access the remote url I get:

(1L, 'test', None)

Damn, this pretty much makes it impossible to use GAE + CloudSQL.

[Nixarn]

Hey, so I found the problem!

Django created datetime(6) and those don't work when accessing the site remotely (maybe and older mysql-python than locally).

Apparently the problem was me using a newer mysqldb-python locally (1.2.5 instead of 1.2.4), so it had nothing with this project to do, but it's an easy and annoying thing ppl might end up doing when trying this.

[waprin]

@Nixarn thank you for the extremely detailed bug report. I reproduced your issue yesterday, with Django and without Django, and so far it's looking like some sort of bug - datetime should definitely work. I am working on filing a bug with the CloudSQL team and investigating some workarounds.

[waprin]

@Nixarn looks like it was a bug with App Engine library MySQLdb 1.2.4b4, which at time of writing is also "latest". Switching the app.yaml dependency for MySQLdb from "latest" or "1.2.4b4" to "1.2.5" or "1.2.4" should fix the issue.

I just switched the MySQL library to 1.2.5 for this repo so the issue should be fixed.

Sorry about the problem, thanks for the bug report.

[Nixarn]

Hey Bill!

Oh, it was possible to get 1.2.5 in use by changing it to "latest", good to
know! I hacked the Django mysql-adapter to disable the precise datetimes to
get it working, but this solution is much cleaner!

Thanks a lot for looking into it!

Best,
Niklas

On Tue, Feb 9, 2016 at 8:16 PM, Bill Prin notifications@github.com wrote:

@Nixarn https://github.com/Nixarn looks like it was a bug with App
Engine library MySQLdb 1.2.4b4, which at time of writing is also "latest".
Switching the app.yaml dependency for MySQLdb from "latest" or "1.2.4b4" to
"1.2.5" or "1.2.4" should fix the issue.

I just switched the MySQL library to 1.2.5 for this repo so the issue
should be fixed.

Sorry about the problem, thanks for the bug report.

—
Reply to this email directly or view it on GitHub
#7 (comment)
.

Follow me on Twitter - twitter.com/nixarn

[waprin]

Glad to know you got something working! I think you slightly misunderstood a small part of what I said. "latest" is currently pointing to 1.24b4 which is the broken version, so "latest" is broken too. It will be changed to 1.2.5 soon, but for now, specify 1.2.5 in the app.yaml is best. In general, I don't recommend "latest" since you don't know which version it points to, and it can change behind the scenes which might break code that was otherwise working.

[Nixarn]

Ah sorry, I got what you said, I just wrote a very clumsy answer. You can get a later version by setting it to 1.2.5 instead of setting it to "latest" which gives 1.2.4b - got it!

The core of the problem seems to be with the mysql-python adapter not supporting high precision datetimes before 1.2.5.

This is from django/db/backend/mysql/features.py line 50 - 54:

    def supports_microsecond_precision(self):
        # See https://github.com/farcepest/MySQLdb1/issues/24 for the reason
        # about requiring MySQLdb 1.2.5
        return self.connection.mysql_version >= (5, 6, 4) and Database.version_info >= (1, 2, 5)

So if I had used 1.2.4 locally when creating the datbase, it would have used datetime, instead of datetime(6) and it would have worked. But I couldn't get 1.2.4 working on OSX while 1.2.5 worked just fine. My ugly fix was to just return False for this features function and recreate the database. But I'll revert my django hack now and change it to use 1.2.5.

Thanks again!

[waprin]

Ok, awesome, that's good information to know, thanks for the pointers.