KB294389767: MediaBeacon - How to increase available SQL Indexed fields

Summary

MediaBeacon standard configuration is limited to "SQL Indexing" a given number of fields. This article explains how to adjust this.

Symptoms

When setting the "Index with SQL" checkbox for a given field (AKA "indexing" a field) in the Fields Workspace of the Admin Core interface, an error message may be shown:

No more available indexes for search fields. The system allows to have 29 string, 9 integer and 10 date search fields.

Solution

System Properties

In order to change these limits, additional properties will need to be added to mediabeacon.properties. In the example below, the properties are set to the system defaults.

mb.fields.integer=9
mb.fields.date=10
mb.fields.string=29

MySQL

MySQL is limited to indexing 64 fields per table. After accounting for non-XMP-indexing columns, reserved for MediaBeacon's own use, there are 48 columns left for XMP field indexing use.

In order to index more fields of a given type using this database type, the system properties will have to swap the number of allowed fields.

mb.fields.integer=8
mb.fields.date=9
mb.fields.string=31

The SQL database will then need to be repaired or the database tables will need to be manually updated. See the example below.

MS SQL Server

MS SQL Server has the capacity to index up to 999 fields per SQL table. If a customer would like to add additional indexed field capacity, for example 20 additional String fields to be indexed, for a total of 49 String field indexes altogether.

mb.fields.string=49

The SQL database will then need to be repaired, see the example below.

Indexing an Additional Field in MySQL & MS SQL Server

If a customer would like to index an additional String field in MySQL, they will in turn have to remove the index associated with an Integer or Date field not currently in use. In MS SQL Server, the additional String fields can simply be added, but in both instances the Java system properties will need to be reconfigured, and the table structures updated via a SQL client.

To override the default apportionment in MySQL, or to add the additional String field indexes in MS SQL Server, the customer will need to perform the following operations:

1. Stop MediaBeacon and perform the next three steps, or stop and restart MediaBeacon in order for the changes to take effect.
2. Make the necessary adjustments to the Core MediaBeacon R3Search mediabeacon.properties file
3. Make the same adjustments to the MediaBeacon Preview Server's mediabeacon.properties file (If Windows is running as a service, this will also need to be adjusted in the service definition).
4. If a webhead is being used, Tomcat's catalina.properties file will also need to be adjusted.

Examples

Environments using MySQL

Task: Adding 2 String-Type SQL Indexes at the Expense of 1 Integer Index and 1 Date Index

As an example, if a customer would like to index two additional String fields, the mediabeacon.properties file would need to be adjusted to reflect that:

mb.fields.integer=8
mb.fields.date=9
mb.fields.string=31

Repairing the Database

After making the necessary adjustments to the mediabeacon.properties file, the database will need to be repaired.

• Restart MediaBeacon and all of its components and perform a SQL Repair Operation (click the "Repair Database" button on the Admin tab of the MediaBeacon Core server-side GUI app).

Environments using MS SQL

Task: Adding 20 String-Type SQL Indexes

As an example, if a customer would like to index 20 additional String fields, the mediabeacon.properties file would need to be adjusted to reflect that:

mb.fields.string=49

Repairing the Database

After making the necessary adjustments to the mediabeacon.properties file, the database will need to be repaired.

• Restart MediaBeacon and all of its components and perform a SQL Repair Operation (click the "Repair Database" button on the Admin tab of the MediaBeacon Core server-side GUI app).