MySQL error

[Gachara Irura]

Hi, at ends!! I get an error whenever I connect FrontlineSMS to mysql 5. Database tables are created at first; but after closing, the application will not reopen. Changing 'value' to a smaller number yields no results. See error below:

[AWT-EventQueue-0] ERROR [10/06/09 09:29:16] net.frontlinesms.data.db.DbController.createTable(DbController.java:712) - Unable to create table [frontline_form_field_results]
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Column length too big for column 'value' (max = 21845); use BLOB or TEXT instead
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Unknown Source)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Constructor.class:0)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:741)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:587)
at net.frontlinesms.data.db.DbController.createTable(DbController.java:712)
at net.frontlinesms.data.db.DbController.init(DbController.java:712)
at net.frontlinesms.ui.UiGeneratorController.saveDatabaseDetails(UiGeneratorController.java:172)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at thinlet.Thinlet.invokeImpl(Thinlet.java:158)
at thinlet.Thinlet.invoke(Thinlet.java:158)
at thinlet.Thinlet.handleMouseEvent(Thinlet.java:158)
at thinlet.Thinlet.processEvent(Thinlet.java:158)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

Hi Gachara

Have you checked the Forums for possible solutions to this? There have been a few posts about MySql issues. We're a developer down over the next few days, so if you're still having problems we'll get back to you as soon as we can.

Ken

Hi Ken,

None so far on working around the table below even after changing the size of the column 'value'. There's been no way around this for sometime. I think the error is somewhat at the DB level and not application:

Unable to create table [frontline_form_field_results]
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Column length too big for column 'value' (max = 21845); use BLOB or TEXT instead


Gachara

Can you use FrontlineSMS with the default database for now? And maybe write a script to update any separate database which is triggered using the External Commands feature? If you try this you may need to reinstall FrontlineSMS (in case you've corrupted or damaged the default database).

Ken

Hi Gachara,

What version of MySQL are you using? It looks like it might be a difference between version 4 and version 5.

Hi Alex, I'm using mysql version 5.0.62 on windows. In earlier forums you mentioned upgrading to mysql 5 so strangely that doesn't seem to be my issue presently.

But I just installed in a PC with 5.1.x and the error is gone and I can close/reopen FrontlineSMS. Everything is posted to the DB ok. Something to document.

Gachara.

Hi Gachara,

Thanks for the detail - looks like MySQL 5.1 is required! Hopefully we can get these issues ironed out in the next release!

Cheers,

Alex