[solved] RelStorage with MySQL on Plone 6

Plone Support Add-on Development
,
1

MySQL is available on System, also libmysqlclient-dev.

what i did

python3.9 -m venv ./venv
source venv/bin/activate
pip install -r https://dist.plone.org/release/6.0.0a6/requirements.txt
pip install Relstorage
pip install RelSorage[mysql]
buildout -c local.cfg

my local.cfg

[buildout]
extends =
    https://dist.plone.org/release/6.0.0a6/versions-ecosystem.cfg
    https://dist.plone.org/release/6.0.0a6/versions-extra.cfg
    https://dist.plone.org/release/6.0.0a6/versions.cfg    

find-links =
    https://dist.plone.org/release/6.0.0a6

eggs-directory = ~/.buildout/eggs

download-cache = ~/.buildout/download-cache

show-picked-versions = true

socket-timeout = 3

abi-tag-eggs = true

newest = false

parts =
    instance
    zopepy

custom-eggs =
    Plone
    Pillow

[instance]
recipe = plone.recipe.zope2instance
user = admin:admin
eggs = 
    ${buildout:custom-eggs}

rel-storage =
    type mysql
    host 127.0.0.1
    db plone
    user zopedbuser
    passwd 123456
    shared-blob-dir false
    blob-dir ${buildout:directory}/var/blobstorage
    blob-cache-size 10mb

Traceback:

bin/instance fg
2022-07-04 14:18:31,852 INFO    [chameleon.config:38][MainThread] directory cache: /home/Development/test/relstorage/var/cache.
/home/.buildout/eggs/cp39/Products.PortalTransforms-3.2.0-py3.9.egg/Products/PortalTransforms/transforms/safe_html.py:5: DeprecationWarning: IFilterSchema is deprecated. Moved to plone.base.interfaces, import from there instead (will be removed in Plone 7).
  from Products.CMFPlone.interfaces import IFilterSchema
/home/.buildout/eggs/cp39/Products.PortalTransforms-3.2.0-py3.9.egg/Products/PortalTransforms/transforms/markdown_to_html.py:8: DeprecationWarning: IMarkupSchema is deprecated. Moved to plone.base.interfaces, import from there instead (will be removed in Plone 7).
  from Products.CMFPlone.interfaces import IMarkupSchema
/home/.buildout/eggs/cp39/Products.PlonePAS-7.0.0a3-py3.9.egg/Products/PlonePAS/config.py:16: DeprecationWarning: ANTIALIAS is deprecated and will be removed in Pillow 10 (2023-07-01). Use Resampling.LANCZOS instead.
  PIL_SCALING_ALGO = Image.ANTIALIAS
/home/.buildout/eggs/cp39/Products.isurlinportal-1.2.1-py3.9.egg/Products/isurlinportal/_compat.py:30: DeprecationWarning: ILoginSchema is deprecated. Moved to plone.base.interfaces, import from there instead (will be removed in Plone 7).
  from Products.CMFPlone.interfaces import ILoginSchema
/home/.buildout/eggs/cp39/plone.dexterity-3.0.0a3-py3.9.egg/plone/dexterity/content.py:38: DeprecationWarning: IConstrainTypes is deprecated. Moved to plone.base.interfaces, import from there instead (will be removed in Plone 7).
  from Products.CMFPlone.interfaces import IConstrainTypes
2022-07-04 14:18:32,625 WARNING [relstorage.adapters.scriptrunner:83][MainThread] script statement failed: 'CREATE TABLE object_state (\nzoid        BIGINT NOT NULL,\ntid         BIGINT NOT NULL\nREFERENCES "transaction",\nprev_tid    BIGINT NOT NULL\nREFERENCES "transaction",\nmd5         CHAR(32) CHARACTER SET ascii,\nstate_size  BIGINT NOT NULL,\nstate       LONGBLOB,\nCONSTRAINT object_state_pk\nPRIMARY KEY (zoid, tid),\nCHECK (tid > 0),\nCHECK (state_size >= 0)\n) ENGINE = InnoDB'; parameters: ()
Traceback (most recent call last):
  File "/home/Development/test/relstorage/parts/instance/bin/interpreter", line 263, in <module>
    exec(compile(__file__f.read(), __file__, "exec"))
  File "/home/.buildout/eggs/cp39/Zope-5.5.1-py3.9.egg/Zope2/Startup/serve.py", line 255, in <module>
    sys.exit(main() or 0)
  File "/home/.buildout/eggs/cp39/Zope-5.5.1-py3.9.egg/Zope2/Startup/serve.py", line 251, in main
    return command.run()
  File "/home/.buildout/eggs/cp39/Zope-5.5.1-py3.9.egg/Zope2/Startup/serve.py", line 189, in run
    app = self.loadapp(app_spec, name=app_name, relative_to=base,
  File "/home/.buildout/eggs/cp39/Zope-5.5.1-py3.9.egg/Zope2/Startup/serve.py", line 220, in loadapp
    return loadapp(app_spec, name=name, relative_to=relative_to, **kw)
  File "/home/.buildout/eggs/cp39/PasteDeploy-2.1.1-py3.9.egg/paste/deploy/loadwsgi.py", line 253, in loadapp
    return loadobj(APP, uri, name=name, **kw)
  File "/home/.buildout/eggs/cp39/PasteDeploy-2.1.1-py3.9.egg/paste/deploy/loadwsgi.py", line 278, in loadobj
    return context.create()
  File "/home/.buildout/eggs/cp39/PasteDeploy-2.1.1-py3.9.egg/paste/deploy/loadwsgi.py", line 715, in create
    return self.object_type.invoke(self)
  File "/home/.buildout/eggs/cp39/PasteDeploy-2.1.1-py3.9.egg/paste/deploy/loadwsgi.py", line 209, in invoke
    app = context.app_context.create()
  File "/home/.buildout/eggs/cp39/PasteDeploy-2.1.1-py3.9.egg/paste/deploy/loadwsgi.py", line 715, in create
    return self.object_type.invoke(self)
  File "/home/.buildout/eggs/cp39/PasteDeploy-2.1.1-py3.9.egg/paste/deploy/loadwsgi.py", line 152, in invoke
    return fix_call(context.object, context.global_conf, **context.local_conf)
  File "/home/.buildout/eggs/cp39/PasteDeploy-2.1.1-py3.9.egg/paste/deploy/util.py", line 55, in fix_call
    val = callable(*args, **kw)
  File "/home/.buildout/eggs/cp39/Zope-5.5.1-py3.9.egg/Zope2/Startup/run.py", line 61, in make_wsgi_app
    starter.prepare()
  File "/home/.buildout/eggs/cp39/Zope-5.5.1-py3.9.egg/Zope2/Startup/starter.py", line 38, in prepare
    self.startZope()
  File "/home/.buildout/eggs/cp39/Zope-5.5.1-py3.9.egg/Zope2/Startup/starter.py", line 94, in startZope
    Zope2.startup_wsgi()
  File "/home/.buildout/eggs/cp39/Zope-5.5.1-py3.9.egg/Zope2/__init__.py", line 36, in startup_wsgi
    _startup()
  File "/home/.buildout/eggs/cp39/Zope-5.5.1-py3.9.egg/Zope2/App/startup.py", line 100, in startup
    DB = dbtab.getDatabase('/', is_root=1)
  File "/home/.buildout/eggs/cp39/Zope-5.5.1-py3.9.egg/Zope2/Startup/datatypes.py", line 245, in getDatabase
    db = factory.open(name, self.databases)
  File "/home/.buildout/eggs/cp39/Zope-5.5.1-py3.9.egg/Zope2/Startup/datatypes.py", line 141, in open
    DB = self.createDB(database_name, databases)
  File "/home/.buildout/eggs/cp39/Zope-5.5.1-py3.9.egg/Zope2/Startup/datatypes.py", line 138, in createDB
    return ZODBDatabase.open(self, databases)
  File "/home/Development/test/relstorage/venv/lib/python3.9/site-packages/ZODB/config.py", line 143, in open
    storage = section.storage.open()
  File "/home/Development/test/relstorage/venv/lib/python3.9/site-packages/relstorage/config.py", line 43, in open
    return RelStorage(adapter, name=config.name, options=options)
  File "/home/Development/test/relstorage/venv/lib/python3.9/site-packages/relstorage/storage/__init__.py", line 174, in __init__
    self._adapter.schema.prepare()
  File "/home/Development/test/relstorage/venv/lib/python3.9/site-packages/relstorage/adapters/schema.py", line 738, in prepare
    self.connmanager.open_and_call(self._prepare_with_connection)
  File "/home/Development/test/relstorage/venv/lib/python3.9/site-packages/relstorage/adapters/connmanager.py", line 293, in open_and_call
    res = callback(conn, cursor)
  File "/home/Development/test/relstorage/venv/lib/python3.9/site-packages/relstorage/adapters/mysql/schema.py", line 201, in _prepare_with_connection
    super(MySQLSchemaInstaller, self)._prepare_with_connection(conn, cursor)
  File "/home/Development/test/relstorage/venv/lib/python3.9/site-packages/relstorage/adapters/schema.py", line 727, in _prepare_with_connection
    all_tables = self.create_tables(cursor, existing_tables)
  File "/home/Development/test/relstorage/venv/lib/python3.9/site-packages/relstorage/adapters/schema.py", line 693, in create_tables
    _, existing_tables = self._create_schema_objects(cursor, self.all_tables, existing_tables)
  File "/home/Development/test/relstorage/venv/lib/python3.9/site-packages/relstorage/adapters/schema.py", line 688, in _create_schema_objects
    meth(cursor)
  File "/home/Development/test/relstorage/venv/lib/python3.9/site-packages/relstorage/adapters/schema.py", line 430, in _create_object_state
    self.runner.run_script(cursor, self.CREATE_OBJECT_STATE_TMPL)
  File "/home/Development/test/relstorage/venv/lib/python3.9/site-packages/relstorage/adapters/scriptrunner.py", line 105, in run_script
    self.run_script_stmt(cursor, stmt, params)
  File "/home/Development/test/relstorage/venv/lib/python3.9/site-packages/relstorage/adapters/scriptrunner.py", line 81, in run_script_stmt
    cursor.execute(stmt, generic_params)
  File "/home/Development/test/relstorage/venv/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/Development/test/relstorage/venv/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/Development/test/relstorage/venv/lib/python3.9/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1005, 'Can\'t create table `plone`.`object_state` (errno: 150 "Foreign key constraint is incorrectly formed")')

Looks like a incorrect SQL Statement. Is MySQL Version not supported or should i use PostgreSQL?

2

While I usually recommend Postgresql, MySQL should work too.
Did you read Setting Up MySQL — RelStorage 3.5.0a7.dev0 documentation ?
Which RelStorage version do you use?

3

Btw, you use first pip and then buildout to create the instance? This combination is new to me (and will not produce the result you expect).
You may want to use GitHub - plone/cookiecutter-zope-instance: It bakes configuration for Zope 5 instead of buildout

4

Yes i do this normally so. The import in a Postgres DB run without Problems, only MySQL fails. I will check the cookiecutter way.

5

Yes i read the manual. I will check the Version of RelStorage.
Update: RelStorage 3.4.5

6

The coredev buildout do the same way. venv -> pip -> buildout. or mean you that the install of packages, here RelStorage, should happen via buildout and not via pip? I have try it, but i run in a buildout error.

7

Coredev installs only buildout via pip, everything else is pulled in inside buildout.

Pure pip is fine, just drop buildout and use the cookiecutter to create the instance.
see Install Plone backend from its Packages – Step by Step – Install Plone 6 — Plone Documentation v6.0-dev

8

@jensens Thanks for hint. Installation via the Plone 6 Training with cookiecutter is the right way. My Testinstallation runs. Only one thing i must write with hand in the zope.conf - the connection for the Database. Cookiecutter generate only this:

<relstorage>
  <mysql>
    driver MySQLdb
  </mysql>
</relstorage>

After filling out the connection options, all was fine.

<relstorage>
  <mysql>
    driver MySQLdb
    db zodb
    user zodbuser
    passwd secret
    host 127.0.0.1
  </mysql>
</relstorage>
9

Update

I use a config.yaml file and now, the relstorage options with the database connection options are in the zope.conf available:

cookiecutter -f --no-input --config-file config.yaml https://github.com/plone/cookiecutter-zope-instance

default_context:
  initial_user_name: "admin"
  initial_user_password: "admin"

  target: "./instance"

  load_zcml:
    package_includes: []

  db_storage: relstorage
  db_relstorage: mysql
  db_blobs_mode: "cache"
  db_relstorage_keep_history: false
  db_relstorage_mysql_parameters:
    host: localhost
    user: zodbuser
    passwd: secret
    db: zodb

  debug_mode: true

Amazing!

1 Like