MySQL metadata backend

[mennozon]

I'm trying to get MariaDB working as a meta backend but I'm running into some issues.

Using 2.9.17 configured with MySQL meta backend running backy2 initdb results in an error:

sqlalchemy.exc.CompileError: (in table 'stats', column 'version_name'): VARCHAR requires a length on dialect mysql

This was resolved by defining the length of strings in sql.py for the affected entries, see attached patch below.

Next issue I ran into when running initdb is:

sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key [SQL: '\nCREATE TABLE blocks (\n\tuid VARCHAR(32), \n\tversion_uid VARCHAR(36) NOT NULL, \n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tdate DATETIME NOT NULL, \n\tchecksum VARCHAR(128), \n\tsize BIGINT, \n\tvalid INTEGER NOT NULL, \n\tPRIMARY KEY (version_uid, id), \n\tFOREIGN KEY(version_uid) REFERENCES versions (uid)\n)\n\n']

I’m not sure how to fix this, if I only define one with primary_key=True I can run initdb successful but then I run into errors with duplicated entries when running a backup, example output:

ERROR: (_mysql_exceptions.IntegrityError) (1062, "Duplicate entry 'c2e53e00-1352-11e9-9772-b499baadddb2' for key 'PRIMARY'") [SQL: 'INSERT INTO blocks (uid, version_uid, id, date, checksum, size, valid) VALUES (%s, %s, %s, now(), %s, %s, %s)'] [parameters: ((None, 'c2e53e00-1352-11e9-9772-b499baadddb2', 0, None, 4194304, 1), (None, 'c2e53e00-1352-11e9-9772-b499baadddb2', 1, None, 4194304, 1), (None, 'c2e53e00-1352-11e9-9772-b499baadddb2', 2, None, 4194304, 1), (None, 'c2e53e00-1352-11e9-9772-b499baadddb2', 3, None, 4194304, 1), (None, 'c2e53e00-1352-11e9-9772-b499baadddb2', 4, None, 4194304, 1), (None, 'c2e53e00-1352-11e9-9772-b499baadddb2', 5, None, 4194304, 1), (None, 'c2e53e00-1352-11e9-9772-b499baadddb2', 6, None, 4194304, 1), (None, 'c2e53e00-1352-11e9-9772-b499baadddb2', 7, None, 4194304, 1)  ... displaying 10 of 1000 total bound parameter sets ...  (None, 'c2e53e00-1352-11e9-9772-b499baadddb2', 998, None, 4194304, 1), (None, 'c2e53e00-1352-11e9-9772-b499baadddb2', 999, None, 4194304, 1))]

Any idea what I’m doing wrong? This is the patch with changes I've made.

--- /usr/lib/python3/dist-packages/backy2/meta_backends/sql.py.orig	2019-01-08 16:30:12.033365334 +0100
+++ /usr/lib/python3/dist-packages/backy2/meta_backends/sql.py	2019-01-09 12:17:23.255117738 +0100
@@ -30,7 +30,7 @@
     __tablename__ = 'stats'
     date = Column("date", DateTime , default=func.now(), nullable=False)
     version_uid = Column(String(36), primary_key=True)
-    version_name = Column(String, nullable=False)
+    version_name = Column(String(128), nullable=False)
     version_size_bytes = Column(BigInteger, nullable=False)
     version_size_blocks = Column(BigInteger, nullable=False)
     bytes_read = Column(BigInteger, nullable=False)
@@ -48,8 +48,8 @@
     __tablename__ = 'versions'
     uid = Column(String(36), primary_key=True)
     date = Column("date", DateTime , default=func.now(), nullable=False)
-    name = Column(String, nullable=False, default='')
-    snapshot_name = Column(String, nullable=False, server_default='', default='')
+    name = Column(String(128), nullable=False, default='')
+    snapshot_name = Column(String(128), nullable=False, server_default='', default='')
     size = Column(BigInteger, nullable=False)
     size_bytes = Column(BigInteger, nullable=False)
     valid = Column(Integer, nullable=False)
@@ -68,7 +68,7 @@
 class Tag(Base):
     __tablename__ = 'tags'
     version_uid = Column(String(36), ForeignKey('versions.uid'), primary_key=True, nullable=False)
-    name = Column(String, nullable=False, primary_key=True)
+    name = Column(String(128), nullable=False, primary_key=True)
 
     def __repr__(self):
        return "<Tag(version_uid='%s', name='%s')>" % (
@@ -80,7 +80,7 @@
     __tablename__ = 'blocks'
     uid = Column(String(32), nullable=True, index=True)
     version_uid = Column(String(36), ForeignKey('versions.uid'), primary_key=True, nullable=False)
-    id = Column(Integer, primary_key=True, nullable=False)
+    id = Column(Integer, nullable=False)
     date = Column("date", DateTime , default=func.now(), nullable=False)
     checksum = Column(String(128), index=True, nullable=True)
     size = Column(BigInteger, nullable=True)

[elemental-lf]

@mennozon I've been working on a fork of backy2 for some months now. I'd be interested in adding MySQL/MariaDB as an additional supported database dialect if someone would volunteer as a tester... Please see https://github.com/elemental-lf/benji.

[mennozon]

Thanks @elemental-lf, with the help of a colleague I was able to get it up and running for almost a month now.

I'll clean up the changes and submit a pull request so this issue can be closed, feel free to also add it to benji if you like.

[elemental-lf]

@mennozon glad to hear that you're up and running. I'll have a look at your PR when it's ready. I've already made some changes based on your original patch. Benji wasn't affected by the auto column issue as the schema has changed a bit in relation to Backy2.

[mennozon]

I just submitted the pull request, I have this running for about a month now without any issues and I just build a new cluster to test backy initdb and a new backup from my Ceph cluster.