This is a migration script for migrating your testrail attachments to cassandra.

migrate.py

import subprocess
import time
import mysql.connector
from mysql.connector import Error
from cassandra.cluster import Cluster
from cassandra.auth import PlainTextAuthProvider

contact_points = ['localhost']
username = 'testrail'
password = 'secret'

auth_provider = PlainTextAuthProvider(username=username, password=password)
cluster = Cluster(contact_points=contact_points, auth_provider=auth_provider)
session = cluster.connect()

session.execute("USE testrail")

connection_config_dict = {
    'user': 'testrail',
    'password': 'secret',
    'host': 'localhost',
    'database': 'testrail',
    'autocommit': True,
    'pool_size':  5
}
connection = mysql.connector.connect(**connection_config_dict)

insert_count = 0;
cursor = connection.cursor()
with open("attachments.txt", "r") as file:
    for line in file:
        cursor.execute("UPDATE `testrail`.`settings` SET `value`='0' WHERE  `name`='cassandra_migration_offset';")
        cursor.execute("DELETE FROM attachments")
        cursor.execute(f"INSERT INTO `attachments` (`id`, `name`, `filename`, `size`, `created_on`, `project_id`, `case_id`, `test_change_id`, `user_id`) VALUES {line}")
        result = subprocess.run('docker-compose exec srv php /var/www/testrail/migration_script/TestRailSqlToCassandraMigration.php -d2 -m"driver=mysql;host=db;port=3306;database=testrail;user=testrail;password=secret" -s"host=cassandra;port=9042;keyspace=testrail;user=cassandra;password=secret" -i', shell=True, cwd=".")
        insert_count += 1;
        cassandra_count = session.execute("SELECT count(*) FROM attachment_file_info").one()[0]

        print("INSERT COUNT: " + str(insert_count) + ", CASSANDRA COUNT: "+ str(cassandra_count))
        
cursor.execute("SELECT * FROM attachments")
rows = cursor.fetchall()
for row in rows:
    print(row)

cursor.close()
connection.close()
session.shutdown()
cluster.shutdown()

Since TestRail cannot be bothered to do any QA on their own software despite being a company creating QA software, this script was used to migrate attachments to Cassandra which is needed if you need to jump to version 7.4.

TestRail includes a migration script, however in version 7.4 of their docker image this is broken, and it fails to load a PHP class. In version 7.5 the script is able to execute, but I found that it would skip about 3/4 of our attachments - with the script above I was able to migrate our attachments to cassandra and we now have a working instance.

BACKUP YOUR ATTACHMENTS TABLE

Dump your testrail attachments mysql table, as this script will clear it.

Pre-requisite

Before you begin, connect to your testrail mysql database and open the jobs table and set is_done to 0 for the entry named migrate_attachments_to_cassandra. Within the settings table remove the cassandra_migration_offset entry - this enables the migration script to start over.

If you already have created a keyspace for cassandra it is advisable to remove this entirely and create a new one to start over.

Lastly, this script reads from an attachments.txt file; this file is a simple sql dump of your attachments table, with two minor changes:

• The first part of the insert statement is gone INSERT INTO attachments (id, name, filename, size, created_on, project_id, case_id, test_change_id, user_id) VALUES.
• The trailing comma is removed from every line

An example is shown below:

(2, 'image.png', '2.image.png', 2245, 1608539131, 1, NULL, NULL, 1)
(3, 'image.png', '3.image.png', 4125, 1608539749, 1, NULL, NULL, 1)
(4, 'image.png', '4.image.png', 4858, 1612862954, 1, NULL, NULL, 1)
(5, 'image.png', '5.image.png', 20655, 1612875711, 1, 12, NULL, 1)
(6, 'image.png', '6.image.png', 56211, 1612875731, 1, 12, NULL, 1)

This obviously requires a little manual labor, but should be easy with any text processing program that is able to find/replace.

Once everything is done, install the cassandra-driver and mysql-connector using pip and you are ready.

Procedure

The script reads the attachments.txt file and for every line, clears the entire attachments table. Then it seeds every attachment to the table one by one and runs the migration script.

Once everything is done, restore your mysql attachments using the backup you created to begin with.