Opened 7 years ago

Closed 7 years ago

#27816 closed Bug (invalid)

Duplicate keyword '_binary' failure when using BinaryField with mysql backend

Reported by: Ace Han Owned by: nobody
Component: Database layer (models, ORM) Version: 1.10
Severity: Normal Keywords: BinaryField mysql
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Although it's bad design in 99% of the cases, my project just need to store file in db anyway

Below is my code

Code highlighting:

# models.py
class DbBasedFile(models.Model):
  filename = models.CharField(_('filename'), max_length=128)
  content = models.BinaryField(_('content'))
  size = models.PositiveIntegerField(_('size'))

  class Meta:
      unique_together = (('filename', ), )
  
  def __str__(self):
      return '{}, filename: {}'.format(self.__class__.__name__, self.filename)

  if __name__ == '__main__':
      DbBasedFile(filename='1.txt', content=b'abc', size=3).save()

will get error

ProgrammingError at /api/misc/dbfiles/file/
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_binary'abc', 3)'

The pre-generated sql is like below

Code highlighting:

str: INSERT INTO `misc_dbbasedfile` (`filename`, `content`, `size`) VALUES (%s, _binary %s, %s)

The final sql is like below

Code highlighting:

bytes: b"INSERT INTO `misc_dbbasedfile` (`filename`, `content`, `size`) VALUES ('1.txt', _binary _binary'abc', 3)"

As we can see, there are two '_binary in the final sql which will fail eventually

After a little debugging, I found these _binary in

django/django/db/backends/mysql/operations.py.DatabaseOperations.binary_placeholder_sql
django/db/models/fields/__init__.py.BinaryField.get_placeholder

, which I think is the root cause

Please kindly help to fix it, thx.

Change History (3)

comment:1 by Tim Graham, 7 years ago

Are you using the latest version of mysqlclient? There's a bug in some older versions.

in reply to:  1 comment:2 by Ace Han, 7 years ago

Replying to Tim Graham:

Are you using the latest version of mysqlclient? There's a bug in some older versions.

Hi,
I just went with PyMySQL==0.7.9, I don't know there is a mysqlclient.
However, I would prefer PyMySQL for its pure python implementation (The project goes with diff os platform).

Anyway, isn't this issue related with django itself in django/django/db/backends/mysql?

Last edited 7 years ago by Ace Han (previous) (diff)

comment:3 by Tim Graham, 7 years ago

Resolution: invalid
Status: newclosed

I think PyMySQL needs to adapt as explained in the mysqlclient issue. By the way, it's not an officially supported or tested adapter by Django.

Note: See TracTickets for help on using tickets.
Back to Top