I recently encountered an issue with OpenHAB’s MYSQL persistence where new String items couldn’t be stored. After some research, I found that the problem arises because the default VARCHAR length in MYSQL is too small for certain String items. Here’s what I discovered and how I resolved it:
-
Problem Identification: When adding new String items, OpenHAB attempted to create tables with VARCHAR(65500), but MYSQL rejected this due to the column length being too large (max allowed is 21844). This resulted in errors like ‘Column length too big for column ‘Value’ (max = 21844); use BLOB or TEXT instead’.
-
Solution Attempt: I found a suggestion online where someone manually created the database entries using TEXT instead of VARCHAR. While this seemed promising, I wasn’t confident in my MYSQL administration skills to implement it correctly.
-
Configuration Adjustment: I checked the MYSQL configuration in OpenHAB and noticed that the STRING object size was set to 65500 in the jdbc.cfg file. However, this didn’t resolve the issue, likely because MYSQL doesn’t support such a large VARCHAR length by default.
-
Final Fix: To bypass the issue, I decided to manually adjust the problematic items to use TEXT instead of VARCHAR. This involved editing the MYSQL database directly to change the column type for the affected items. While this was a bit daunting, it worked perfectly and allowed the String items to be stored without errors.
-
Conclusion: This experience highlighted the importance of understanding database limitations when working with IoT platforms like OpenHAB. By adjusting the column type to TEXT, I was able to resolve the persistence issue and ensure smooth operation of my smart home setup.
If anyone else encounters similar issues, I recommend checking your MYSQL configuration, considering TEXT for larger String items, and always backing up your database before making manual changes!