Openfire 3.10.0 on Windows, using external Oracle database and REST API plugin.
If a list of IP Addresses is added to the REST API’s AllowedIPs and then saved, a new property ‘plugin.restapi.allowedIPs’ is created in the database. Later, through the Admin Console, if the same field is cleared it appears as though all IP addresses are allowed. However, after restart of the Openfire service, the original list of IPs will return.
It seems the Admin Console tries to set the ‘plugin.restapi.allowedIPs’ to ‘null’. However, the database schema prevents a null value for this column. So the value is left unchanged. Once the service restarts the original non-null value is used.
java.sql.SQLException: ORA-01407: cannot update (“OPENFIRE”.“OFPROPERTY”.“PROPVALUE”) to NULL
Delete the ‘plugin.restapi.allowedIPs’ property via the Admin Console’s System Properties page.
That, more or less, confirms what I see. If I clear the allowedIPs field and press SAVE the exception appears in the error log and the value in the database remains unchanged. I have to guess an “in-memory” or “cached” value must be used until the service restarts. As the plugin will function as if the value were empty until service restart occurs.
Frankly, this might an issue for any number of plugins/properties when an Oracle database is used. Let me run a quick test.
As I thought, this is a larger issue than just with the REST API.
Any time a property’s value is set to an empty string and an Oracle database is used, the value will not be updated in the database. As such, the original value will be used after a service restart (or whatever else causes Openfire to re-read its property values from the database). This same issue may affect other tables in Openfire in which an empty string is placed into a non-nullable column and an Oracle database is used.
Install Openfire and configure it to use an external Oracle database
Goto the Admin Console’s ‘System Properties’ page
Add a property ‘aaa.test.property’ with some value ‘sample value goes here’ and press ‘Save Property’
The OFPROPERTY table’s entry for ‘aaa.test.property’ will show the above value
In the Admin Console, edit the same property and clear the entire value field and press “Save Property”
The Admin Console will now show an “empty” value for the ‘aaa.test.property’
An exception will appear in the error.log at the time Save Property is pressed in Step 5
“java.sql.SQLException: ORA-01407: cannot update (“OPENFIRE”.“OFPROPERTY”.“PROPVALUE”) to NULL”
The OFPROPERTY table’s entry for ‘aaa.test.property’ will show the value entered in Step 3
Restart the Openfire service
Open the Admin Console’s System Properties page, note the value for ‘aaa.test.property’ is now the value entered in Step 3.
Cry because you chose to use Orcale as a backing store for Openfire.