ECS: ECSSync migration error: MySQLSyntaxErrorException: Unknown column 'source_md5' in 'field list'
Summary: ECSSync migration instantly completes without any of the Centera clips actually being migrated.
Symptoms
ECSSync migration instantly completes without any of the Centera clips to actually be migrated.
In /var/log/ecssync/ecs-sync.log file the following error appears:
2020-05-04 03:08:38 WARN [sync-pool-1-t-15] SyncTask: O--! object DNVBT3VHL1NE1e2NMV9CJDMQV5FG41BCFNF5EM0AC0A4KU05821DC failed
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'source_md5' in 'field list'
at sun.reflect.GeneratedConstructorAccessor29.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
... <output truncated> ..
Cause
Following is an example of the table created, as per the latest procedure, which doesn't include this column:
MariaDB [ecs_sync]> describe <table_name>;
+-------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+-------+
| source_id | varchar(54) | NO | PRI | NULL | |
| target_id | varchar(54) | YES | | NULL | |
| is_directory | int(11) | NO | | NULL | |
| size | bigint(20) | YES | | NULL | |
| mtime | datetime | YES | | NULL | |
| status | varchar(32) | NO | MUL | NULL | |
| transfer_start | datetime | YES | | NULL | |
| transfer_complete | datetime | YES | | NULL | |
| verify_start | datetime | YES | | NULL | |
| verify_complete | datetime | YES | | NULL | |
| retry_count | int(11) | YES | | NULL | |
| error_message | varchar(2048) | YES | | NULL | |
| is_source_deleted | int(11) | YES | | NULL | |
+-------------------+---------------+------+-----+---------+-------+
13 rows in set (0.00 sec)
ECSSync version 3.2.9 added a column to the database table to track the source MD5 checksum (this is mentioned in the release notes).
Resolution
mysql shell:
alter table <table_name> add column source_md5 VARCHAR(32);
The table should look like the following example, with the additional column:
MariaDB [ecs_sync]> describe <table_name>;
+-------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+-------+
| source_id | varchar(54) | NO | PRI | NULL | |
| target_id | varchar(54) | YES | | NULL | |
| is_directory | int(11) | NO | | NULL | |
| size | bigint(20) | YES | | NULL | |
| mtime | datetime | YES | | NULL | |
| status | varchar(32) | NO | MUL | NULL | |
| transfer_start | datetime | YES | | NULL | |
| transfer_complete | datetime | YES | | NULL | |
| verify_start | datetime | YES | | NULL | |
| verify_complete | datetime | YES | | NULL | |
| retry_count | int(11) | YES | | NULL | |
| error_message | varchar(2048) | YES | | NULL | |
| is_source_deleted | int(11) | YES | | NULL | |
| source_md5 | varchar(32) | YES | | NULL | |
+-------------------+---------------+------+-----+---------+-------+
14 rows in set (0.00 sec)
MariaDB [ecs_sync]>
(where <table_name> is the name of your existing table).