Discussion related to cado-nfs

[Emmanuel Thomé <Emmanuel.Thome@inria.fr>]

Hi

Thanks for your report. I'm replying to the mailing list instead of the erroneous email address that you used.

 I can reproduce your problem on debian12. We're only testing mysql backends with debian-testing distribution, and it's definitely not our daily driver. Both of these explain that this failure went unnoticed, but this definitely needs to be fixed.

I don't have a high level of familiarity with mariadb nor sql specifics in general, to be honest. I read some bits from https://mariadb.com/kb/en/foreign-keys/ ; it seems that there's a change of behavior in the mariadb engine. Until some point, I think that the REFERENCES constraint could fail silently, and now it's no longer the case. Additionally, I guess that referencing a whole table used to implicitly mean the primary key, but that could possibly fail if that was expressed right from the CREATE TABLE command itself. More recent mariadb _seems_ to be happy with this shortened construct, which would be an explanation of why I get no failure on debian-testing.

I don't expect that it does harm if we replace " workunints " by " workunits(wurowid) " in the code, which is what I'm going to push now.

E.

On Fri, Feb 21, 2025 at 5:39 AM Melanie Hendricks <mh@paradoxclub.org> wrote:

Follow-up

This is the MariaDB general query log:

/usr/sbin/mariadbd, Version: 10.11.6-MariaDB-0+deb12u1 (Debian 12). started with:

Tcp port: 3306  Unix socket: /run/mysqld/mysqld.sock

Time    Id Command Argument

250220 21:35:24    80 Quit

250220 21:35:29    81 Connect cadonfs@localhost on cadonfs using TCP/IP

   81 Query SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'

   81 Query set autocommit=0

   81 Query SET AUTOCOMMIT=0

   81 Query commit

   81 Query SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

   81 Query commit

   81 Query commit

   81 Query START TRANSACTION

   81 Query CREATE TABLE IF NOT EXISTS tasks( rowid INTEGER PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL, kkey VARCHAR(300) UNIQUE NOT NULL, type INTEGER NOT NULL, value TEXT  )

   81 Query CREATE INDEX IF NOT EXISTS tasks_dictdb_kkey_index ON tasks( kkey )

   81 Query commit

   81 Query commit

   81 Query SELECT *  FROM tasks

   81 Query commit

   81 Query commit

   81 Query START TRANSACTION

   81 Query SELECT *  FROM tasks  WHERE kkey = 'workdir'   LIMIT 1 FOR UPDATE

   81 Query UPDATE tasks SET value = '/tmp/cado.psrcnwns', type = 0  WHERE kkey = 'workdir'

   81 Query commit

   82 Connect cadonfs@localhost on cadonfs using TCP/IP

   82 Query SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'

   82 Query set autocommit=0

   82 Query SET AUTOCOMMIT=0

   82 Query commit

   82 Query SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

   82 Query commit

   82 Query commit

   82 Query commit

   82 Query commit

   82 Query START TRANSACTION

   82 Query CREATE TABLE IF NOT EXISTS workunits( wurowid INTEGER PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL, wuid VARCHAR(512) UNIQUE NOT NULL, submitter VARCHAR(512) , status INTEGER NOT NULL, wu TEXT NOT NULL, timecreated TEXT , timeassigned TEXT , assignedclient TEXT , timeresult TEXT , resultclient TEXT , errorcode INTEGER , failedcommand INTEGER , timeverified TEXT , retryof INTEGER REFERENCES workunits, priority INTEGER  )

   82 Query rollback

   81 Quit

   82 Quit

On Thu, Feb 20, 2025 at 10:37, Melanie Hendricks <mh@paradoxclub.org> wrote:

Hello cado-nfs mailing list,

When starting a new cado-nfs.py instance using mysql (MariaDB) backend, the 'tasks' table is seemingly created correctly, but the 'workunits' table fails to create with 'errno: 150 "Foreign key constraint is incorrectly formed'.  Any ideas?

Linux debian 6.1.0-31-arm64 #1 SMP Debian 6.1.128-1 (2025-02-07) aarch64 GNU/Linux

mariadb  Ver 15.1 Distrib 10.11.6-MariaDB, for debian-linux-gnu (aarch64) using  EditLine wrapper

cado-nfs cloned a couple days ago : git clone https://gitlab.inria.fr/cado-nfs/cado-nfs.git

git describe --tags: 2.3.0-5769-g0a39365a0

user@debian:~/cado/cado-nfs$ ./cado-nfs.py 11710260397957750949872918681684284672703233285112420883497435934195126280484746485372615873734910227107717968753275983907530163260845346252572237205277052566466383 server.port=45001 server.whitelist=0.0.0.0/0  database=db:mysql://cadonfs:cadonfs@localhost/cadonfs --mysql --server  

Info:root: Using default parameter file ./parameters/factor/params.c165

Info:Database: Database URI is db:mysql://USERNAME:PASSWORD@localhost/cadonfs

Info:root: Attempting database access for URI db:mysql://USERNAME:PASSWORD@localhost/cadonfs

Info:Database: database: cadonfs, connect args: {'user': 'cadonfs', 'password': 'cadonfs', 'host': 'localhost'}

Info:root: Found database, with stored workdir path /tmp/cado.psrcnwns

Info:root: Set tasks.linalg.bwc.threads=16 based on detected physical cores

Info:root: Set tasks.threads=16 based on detected logical cpus

Info:root: tasks.threads = 16 [via tasks.threads]

Info:root: tasks.polyselect.threads = 2 [via tasks.polyselect.threads]

Info:root: tasks.sieve.las.threads = 2 [via tasks.sieve.las.threads]

Info:root: tasks.linalg.bwc.threads = 16 [via tasks.linalg.bwc.threads]

Info:root: tasks.sqrt.threads = 8 [via tasks.sqrt.threads]

Info:root: Command line parameters: ./cado-nfs.py 11710260397957750949872918681684284672703233285112420883497435934195126280484746485372615873734910227107717968753275983907530163260845346252572237205277052566466383 server.port=45001 server.whitelist=0.0.0.0/0 database=db:mysql://USERNAME:PASSWORD@localhost/cadonfs --mysql --server

Info:root: If this computation gets interrupted, it can be resumed with ./cado-nfs.py /tmp/cado.psrcnwns/c165.parameters_snapshot.3

Info:Database: database: cadonfs, connect args: {'user': 'cadonfs', 'password': 'cadonfs', 'host': 'localhost'}

Traceback (most recent call last):

  File "/home/user/.local/lib/python3.11/site-packages/mysql/connector/connection_cext.py", line 755, in cmd_query

    self._cmysql.query(

_mysql_connector.MySQLInterfaceError: Can't create table `cadonfs`.`workunits` (errno: 150 "Foreign key constraint is incorrectly formed")

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

Traceback (most recent call last):

  File "/home/user/cado/cado-nfs/./cado-nfs.py", line 201, in <module>

    factorjob = cadotask.CompleteFactorization(db=db,

                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/home/user/cado/cado-nfs/./scripts/cadofactor/cadotask.py", line 6851, in __init__

    self.wuar.create_tables()

  File "/home/user/cado/cado-nfs/./scripts/cadofactor/wudb.py", line 375, in create_tables

    self.conn.harness_transaction(EXCLUSIVE, self.mapper.create)

  File "/home/user/cado/cado-nfs/./scripts/cadofactor/database/base.py", line 109, in harness_transaction

    return transaction(cursor, *args, **kwargs)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/home/user/cado/cado-nfs/./scripts/cadofactor/wudb.py", line 163, in create

    self.table.create(cursor)

  File "/home/user/cado/cado-nfs/./scripts/cadofactor/database/table.py", line 40, in create

    cursor.create_table(self.tablename, fields)

  File "/home/user/cado/cado-nfs/./scripts/cadofactor/database/base.py", line 298, in create_table

    self._exec(command)

  File "/home/user/cado/cado-nfs/./scripts/cadofactor/database/base.py", line 262, in _exec

    self.try_catch_execute(command, values)

  File "/home/user/cado/cado-nfs/./scripts/cadofactor/database/mysql.py", line 57, in try_catch_execute

    super().try_catch_execute(command, values)

  File "/home/user/cado/cado-nfs/./scripts/cadofactor/database/base.py", line 272, in try_catch_execute

    self.cursor.execute(command)

  File "/home/user/.local/lib/python3.11/site-packages/mysql/connector/cursor_cext.py", line 351, in execute

    self._connection.cmd_query(

  File "/home/user/.local/lib/python3.11/site-packages/mysql/connector/opentelemetry/context_propagation.py", line 97, in wrapper

    return method(cnx, *args, **kwargs)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/home/user/.local/lib/python3.11/site-packages/mysql/connector/connection_cext.py", line 763, in cmd_query

    raise get_mysql_exception(

mysql.connector.errors.DatabaseError: 1005 (HY000): Can't create table `cadonfs`.`workunits` (errno: 150 "Foreign key constraint is incorrectly formed")

Thank you,

Melanie Hendricks