Skip to content

error when trying to use op.bulk_insert on tables with json columns #770

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
WindowGenerator opened this issue Dec 22, 2020 · 7 comments
Closed

error when trying to use op.bulk_insert on tables with json columns #770

WindowGenerator opened this issue Dec 22, 2020 · 7 comments
Labels
external driver issues the issue involves a misbehavior on the part of the DBAPI itself, probably not SQLAlchemy or Alembic question usage and API questions

Comments

@WindowGenerator
Copy link

Describe the bug

when trying to use op.bulk_insert on tables with JSON columns, if I try to insert more than one value using bulk_insert, a mysql write error occurs: (3144, "Cannot create a JSON value from a string with CHARACTER SET 'binary'.")
It occurs even if you try to insert an empty Dict

Expected behavior
No error occurs

To Reproduce

"""
TEST
"""
import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision = 'ab959aaba2d0'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    test_table = op.create_table(
        'test',
        sa.Column('id', sa.INTEGER(), nullable=False, primary_key=True),
        sa.Column('json', sa.JSON(), nullable=False),
        mysql_charset='utf8',
    )

    op.bulk_insert(test_table, [
        {
            'id': 1,
            'json': {}
        },
        {
            'id': 2,
            'json': {}
        }
    ])


def downgrade():
    pass

Error

2020-12-22T16:16:21 ERROR: (MySQLdb._exceptions.OperationalError) (3144, "Cannot create a JSON value from a string with CHARACTER SET 'binary'.")
[SQL: INSERT INTO test (id, json) VALUES (%s, %s)]
[parameters: ((1, '{}'), (2, '{}'))]
(Background on this error at: http://sqlalche.me/e/13/e3q8)
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1256, in _execute_context
    self.dialect.do_executemany(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 148, in do_executemany
    rowcount = cursor.executemany(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 230, in executemany
    return self._do_execute_many(
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 266, in _do_execute_many
    rows += self.execute(sql + postfix)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (3144, "Cannot create a JSON value from a string with CHARACTER SET 'binary'.")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/app/share/db/migrations/utils.py", line 29, in run_db_migrations
    command.upgrade(alembic_cfg, "head")
  File "/usr/local/lib/python3.8/site-packages/alembic/command.py", line 298, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.8/site-packages/alembic/script/base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.8/site-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 783, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "alerting/db/migrations/env.py", line 4, in <module>
    run_migrations(Base.metadata)
  File "/app/share/db/migrations/alembic_env.py", line 100, in run_migrations
    run_migrations_online(target_metadata)
  File "/app/share/db/migrations/alembic_env.py", line 93, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.8/site-packages/alembic/runtime/environment.py", line 846, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.8/site-packages/alembic/runtime/migration.py", line 522, in run_migrations
    step.migration_fn(**kw)
  File "/app/alerting/db/migrations/versions/test.py", line 22, in upgrade
    op.bulk_insert(test_table, [
  File "<string>", line 8, in bulk_insert
  File "<string>", line 3, in bulk_insert
  File "/usr/local/lib/python3.8/site-packages/alembic/operations/ops.py", line 2165, in bulk_insert
    operations.invoke(op)
  File "/usr/local/lib/python3.8/site-packages/alembic/operations/base.py", line 373, in invoke
    return fn(self, operation)
  File "/usr/local/lib/python3.8/site-packages/alembic/operations/toimpl.py", line 170, in bulk_insert
    operations.impl.bulk_insert(
  File "/usr/local/lib/python3.8/site-packages/alembic/ddl/impl.py", line 327, in bulk_insert
    self._exec(table.insert(inline=True), multiparams=rows)
  File "/usr/local/lib/python3.8/site-packages/alembic/ddl/impl.py", line 141, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1256, in _execute_context
    self.dialect.do_executemany(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 148, in do_executemany
    rowcount = cursor.executemany(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 230, in executemany
    return self._do_execute_many(
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 266, in _do_execute_many
    rows += self.execute(sql + postfix)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (3144, "Cannot create a JSON value from a string with CHARACTER SET 'binary'.")

Versions.

  • OS: debian 9
  • Python: 3.8.6
  • Alembic: 1.4.3
  • SQLAlchemy: 1.3.20 (and sqlalchemy_utils: 0.36.8, mysqlclient: 2.0.1)
  • Database: mysql 8.0.18
  • DBAPI: sqlalchemy

Additional context

If you insert only one value, then the error does not occur
@WindowGenerator WindowGenerator added the requires triage New issue that requires categorization label Dec 22, 2020
@zzzeek zzzeek added external driver issues the issue involves a misbehavior on the part of the DBAPI itself, probably not SQLAlchemy or Alembic question usage and API questions and removed requires triage New issue that requires categorization labels Dec 22, 2020
@zzzeek
Copy link
Member

zzzeek commented Dec 22, 2020

hey there -

your script doesn't fail for me. Running against MySQL 8.0.21 with mysqlclient 2.0.2 (2.0.1 also worked fine), the SQL log output is as follows:

INFO  [sqlalchemy.engine.base.Engine] 
CREATE TABLE test (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	json JSON NOT NULL, 
	PRIMARY KEY (id)
)CHARSET=utf8


INFO  [sqlalchemy.engine.base.Engine] ()
INFO  [sqlalchemy.engine.base.Engine] COMMIT
INFO  [sqlalchemy.engine.base.Engine] INSERT INTO test (id, json) VALUES (%s, %s)
INFO  [sqlalchemy.engine.base.Engine] ((1, '{}'), (2, '{}'))
INFO  [sqlalchemy.engine.base.Engine] COMMIT
INFO  [sqlalchemy.engine.base.Engine] INSERT INTO alembic_version (version_num) VALUES ('811b4ec787c0')
INFO  [sqlalchemy.engine.base.Engine] ()
INFO  [sqlalchemy.engine.base.Engine] COMMIT

so we would say this has something to do with how the database was created, e.g. CHARACTER SET options perhaps. There's lots and lots of google hits for this error but they seem specific to some kind of bulk importer tool, but then it looks like ultimatetely you want to make sure the database itself is created with the utf8mb4 character set and not BINARY: https://stackoverflow.com/questions/36178312/mysqlimport-issues-set-character-set-database-binary-which-prevents-loading

note also I'm using a URL with charset=utf8mb4:

mysql+mysqldb://scott:tiger@mysql80/test?charset=utf8mb4

@zzzeek
Copy link
Member

zzzeek commented Dec 22, 2020

note also that the mysqlclient driver formats SQL values differently when using "executemany" vs. "execute", it rewrites INSERT statements internally, so that would likely be why you get different results with more than one value vs. a single value.

@zzzeek
Copy link
Member

zzzeek commented Dec 22, 2020 •
edited by CaselIT
Loading

the CREATE DATABASE I'm working with is:

CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

@WindowGenerator
Copy link
Author

Thanks for the detailed answer)

@mikhailantoshkin
Copy link

mikhailantoshkin commented Apr 7, 2021 •
edited
Loading

Removing binary_prefix=true from the URL fixed a similar issue for me.

As mentioned in the documentation, it instructs BDAPI driver to provide special character set introducers, which, apparently, break bulk inserts of rows with JSON values

Still, I'm not sure if having binary_prefix=true should interfere with bulk inserts of JSON values in the first place...

@zzzeek
Copy link
Member

zzzeek commented Apr 27, 2021

thanks for that hint, can reproduce and confirmed this is a mysqlclient bug. Use PyMySQL or dont use the binary_prefix flag. Issue created at PyMySQL/mysqlclient#494

@zzzeek zzzeek closed this as completed Apr 27, 2021
@cemrehancavdar
Copy link

I am having exact error with aiomysql and also asyncmy while trying to insert json to mysql 8. So same error?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
external driver issues the issue involves a misbehavior on the part of the DBAPI itself, probably not SQLAlchemy or Alembic question usage and API questions
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@zzzeek @WindowGenerator @cemrehancavdar @mikhailantoshkin