BUG REPORT: MySql column protocol.protocol_id must have the AUTO_INCREMENT flag set

I tried running relax compiled with MPI and MYSQL.

MySQL was configured properly. A test run without MPI successfully created tables and wrote data to MySQL.

The protocol file @relax.flags was created with the following options:

-list pdblist.txt

-relax:script relax.script

-relax:bb_move false

-score:output_residue_energies

-score:weights res2015

-out:file:output_pose_energies_table

-out:use_database

-out:database_protocol_id 1     <

-inout:dbms:mode mysql

-inout:dbms:database_name run1

-inout:dbms:host localhost

-inout:dbms:user ********

-inout:dbms:password *********

-inout:dbms:port  3306

-run:random_delay 5

-jd2:delete_old_poses true

PROBLEM

The process would stop with the following error message:

[FILE]: src/protocols/features/util.cc

[LINE]: 184

[START_MESSAGE]

[ ERROR ] UtilityExitException

ERROR: Failed to set the protocol id for batch 'db_job_outputter'

Error Message:

cppdb::mysql::Duplicate entry '0' for key 'PRIMARY'

We all know what this means… the ID exists in the protocol table and some competing processes are trying to do an insert with duplicate values in a primary key. I assumed the id 0 existed because I did a test run before without MPI, so I truncated all tables and retried… but this wouldn’t even work out of the box with a fresh database. (This only happened using MPI).

SOLUTION:

I figured this error and other assertion problems were a response from the DB… so the problem was MySQL, not Rosetta. I havent studied how rosetta crafts insert queries to the protocol table, but I imagine the ID column is being sent blank or with ID 0… in either case, as with any other DB code, primary keys are often self incremental.

So  I did an ALTER on my “protocol” table and added the “AUTO_INCREMENT” flag on the primary key. In order to do this I had to temporarily delete the “batches_ibfk_1” foreign key from the batches table, and then recreate it after altering the protocol table.

This fixed the issue.

TODO:

Please include the AUTO_INCREMENT flag on the CREATE statement of the table protocol. The primary key must have this flag if you expect rosetta to behave well with MySQL + MPI. 

I don’t know if the rest of the supported DBs have this issue… mysql for sure does.

Here is my 2 cents LOL… I hope it helps others

Pedro