MySQL Binding - Wrong Table creation for String

[TimWeyand]

Hi, i wanted to log every item with the mysql persistent, but it seems that there is a Problem with the Datatype "String" Interpretation. The Problem seems to be UTF8 and the limitation of Varchar to 65.535 Bytes (See: http://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html)

21845 * 3 is 65.535 and matches the max-row-size.

The Solution might be, to reduce the varchar size (or even make it configurable?). An alternate solution would be to set the character set of the field (Value varchar(65500) CHARACTER SET latin1 DEFAULT NULL).

Also the Table Index counter is increased on every error. Instead of my 160 Variables, the Code tried to initialize Table503.

item definition: String strSunset "Sunset" (gWeather) { http="<[http://api.wunderground.com/api/XXX/astronomy/q/zmw:XXX.xml:21600000:XSLT(wunderground_sunset.xsl)]" }

persistent definition: Items { * : strategy = everyChange, restoreOnStartup }

logfile: 22:34:00.046 ERROR o.o.p.m.i.MysqlPersistenceService[:212]- mySQL: Could not create table for item 'strSunrise' with statement 'CREATE TABLE Item503 (Time DATETIME, Value VARCHAR(65500), PRIMARY KEY(Time));': Column length too big for column 'Value' (max = 21845); use BLOB or TEXT instead 22:34:00.047 ERROR o.o.p.m.i.MysqlPersistenceService[:227]- mySQL: Item 'strSunrise' was not added to the table - removing index 22:34:00.048 ERROR o.o.p.m.i.MysqlPersistenceService[:313]- mySQL: Could not store item 'strSunrise' in database with statement 'INSERT INTO Item503 (TIME, VALUE) VALUES(NOW(),'8:35:00');': Table 'openhab.Item503' doesn't exist

Best Regards Tim

[cdjackson]

Hi, You can change the definition of the field. If I remember correctly, the following line in openhab.cfg should do it -: mysql:sqltype.string=VARCHAR(20000)

You can do the same with all item types by changing the “string” to the openhab item type (color, contact…).

Chris

On 6 Jan 2014, at 22:01, TimWeyand notifications@github.com wrote:

Hi, i wanted to log every item with the mysql persistent, but it seems that there is a Problem with the Datatype "String" Interpretation. The Problem seems to be UTF8 and the limitation of Varchar to 65.535 Bytes (See: http://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html)

21845 * 3 is 65.535 and matches the max-row-size.

The Solution might be, to reduce the varchar size (or even make it configurable?). An alternate solution would be to set the character set of the field (email varchar(65500) CHARACTER SET latin1 DEFAULT NULL).

Also the Table Index counter is increased on every error. Instead of my 160 Variables, the Code tried to initialize Table503.

item definition: String strSunset "Sunset" (gWeather) { http="<[http://api.wunderground.com/api/XXX/astronomy/q/zmw:XXX.xml:21600000:XSLT(wunderground_sunset.xsl)]" }

persistent definition: Items {

• : strategy = everyChange, restoreOnStartup }

logfile: 22:34:00.046 ERROR o.o.p.m.i.MysqlPersistenceService[:212]- mySQL: Could not create table for item 'strSunrise' with statement 'CREATE TABLE Item503 (Time DATETIME, Value VARCHAR(65500), PRIMARY KEY(Time));': Column length too big for column 'Value' (max = 21845); use BLOB or TEXT instead 22:34:00.047 ERROR o.o.p.m.i.MysqlPersistenceService[:227]- mySQL: Item 'strSunrise' was not added to the table - removing index 22:34:00.048 ERROR o.o.p.m.i.MysqlPersistenceService[:313]- mySQL: Could not store item 'strSunrise' in database with statement 'INSERT INTO Item503 (TIME, VALUE) VALUES(NOW(),'8:35:00');': Table 'openhab.Item503' doesn't exist

Best Regards Tim

— Reply to this email directly or view it on GitHub.

[TimWeyand]

Hi Chris, thank you for the quick help. The Strings are now stored in the Database. I have added the Information to the Wiki, since i only found it in https://github.com/openhab/openhab/issues/441.

The Question is - as this is an international Project (?) - should the binding work with unicode out of the box?

Best Regards Tim

[cdjackson]

Yes, you're right. I'm happy to set the default down to (say) 21800.

Also. I'm not sure I understand the problem with the counter? The column ref gets updated every time it tries to create a item table, so if this is failing then it will increment. However, in theory (!) the 'Items' table should remove any indexes for failed items. So the result is you increment the counter, but it's still 'correct' in that the index and table names relate correctly to the item. If that isn't happening, then let me know and I'll take a look at it.

Cheers Chris

[TimWeyand]

Hi Chris, thanks.

Just figured out the counter problem, seems to be an mysql auto index "problem". If the creation of a table fails, the item in items is deleted (?) but the auto index of the table has already been increased. This seems to be the reason of the big gap. The references to the tables seem to be fine. So it's just a cosmetic one.

Visualisation

[cdjackson]

Exactly. I know this looks a bit “funny”, but it should be correct, and in general a user shouldn’t be looking at the index - you’d use software to just query based on the item name and the index number becomes irrelevant.

On 6 Jan 2014, at 23:07, TimWeyand notifications@github.com wrote:

Hi Chris, thanks.

Just figured out the counter problem, seems to be an mysql auto index "problem". If the creation of a table fails, the item in items is deleted (?) but the auto index of the table has already been increased. This seems to be the reason of the big gap. The references to the tables seem to be fine. So it's just a cosmetic one.

Visualisation

— Reply to this email directly or view it on GitHub.

[teichsta]

has been fixed meanwhile …