MySQL database problem

This message was edited 2 times. Last update was at 07/12/2009 03:33:43

When I try to convert the derby database into MySQL database. I got the following exception:

Exception sending context initialized event to listener instance of class com.serotonin.mango.MangoContextListener
com.serotonin.ShouldNeverHappenException: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select ds.id, ds.name, ds.dataSourceType, ds.enabled, vrt.updateSeconds, mbs.updateSeconds, mbs.commPortId, mbs.baudRate, mbs.flowControlIn, mbs.flowControlOut, mbs.dataBits, mbs.stopBits, mbs.parity, mbs.encoding, mbs.echo, mbi.updateSeconds, mbi.transportType, mbi.host, mbi.port from dataSources ds left join dataSourceVrt vrt on vrt.dataSourceId = ds.id left join dataSourceMbs mbs on mbs.dataSourceId = ds.id left join dataSourceMbi mbi on mbi.dataSourceId = ds.id]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'mango.datasourcevrt' doesn't exist
at com.serotonin.mango.db.upgrade.DBUpgrade.checkUpgrade(DBUpgrade.java:87)
at com.serotonin.mango.db.DatabaseAccess.initialize(DatabaseAccess.java:133)
at com.serotonin.mango.MangoContextListener.databaseInitialize(MangoContextListener.java:24

at com.serotonin.mango.MangoContextListener.contextInitialized(MangoContextListener.java:85)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3934)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4429)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:526)
at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:987)
at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:909)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:495)
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1206)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:314)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
at org.apache.catalina.core.StandardHost.start(StandardHost.java:722)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
at org.apache.catalina.core.StandardService.start(StandardService.java:516)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
at org.apache.catalina.startup.Catalina.start(Catalina.java:583)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:28

at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413)

Can anyone tell me what is the problem?

Thanks

What version of Mango are you starting from?

I am using mango 1.7.0.

I downloaded it from this link http://mango.serotoninsoftware.com/downloads/mango-1.7.0.zip

That error is mentioning a table that hasn't been in Mango since version 0.7.0. This means that the environment that you are converting from is somehow corrupted. When you remove the "convert" properties and stuff from the env.properties file, can you get back to your old install?

Hi,

I get the error "Table/View 'DATASOURCEVRT' does not exist" according to com.seroronin.ShouldNeverHappenException when it checkUpgrades(DBUpgrade.java:8

.Its version is 1.8.0.

Did you create the tables before running Mango, or let Mango create the tables itself? Mango automatically checks if the users table is missing and if so creates all tables. Otherwise it checks what version the database is and upgrades if necessary.

If you ran the create script yourself, the database will not have been properly set up, which would result in this error.

I'm having problems upgrading to mySQL. The tables get created in mySQL during start up, but something else during the execution fails. There is no data in the mySQL DB.

env.properties set up as per instruction. All Data from from derby was gone when I restored the env.properties to default settings. I'm using version 1.8.

Some of the exceptions I get.

SEVERE: A web application registered the JBDC driver [org.apache.derby.jdbc.AutoloadedDriver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.
15-Feb-2010 2:45:44 PM org.apache.catalina.loader.WebappClassLoader clearReferencesJdbc
SEVERE: A web application registered the JBDC driver [com.mysql.jdbc.Driver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.

EVERE: Exception sending context initialized event to listener instance of class com.serotonin.mango.MangoContextListener
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ create table events ( id int not null auto_increment, typeId int not null, typeRef1 int not null, typeRef2 int not null, activeTs bigint not null, rtnApplicable char(1) not null, rtnTs bigint, rtnCause int, alarmLevel int not null, message longtext, ackTs bigint, ackUserId int primary key (id)

type=InnoDB; ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(id)

type=InnoDB' at line 1
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.translate(SQLExceptionSubclassTranslator.java:7

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:237)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:406)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:42

at com.serotonin.mango.db.BasePooledAccess.runScript(BasePooledAccess.java:89)
at com.serotonin.mango.db.BasePooledAccess.createSchema(BasePooledAccess.java:106)
at com.serotonin.mango.db.MySQLAccess.newDatabaseCheck(MySQLAccess.java:66)
at com.serotonin.mango.db.DatabaseAccess.initialize(DatabaseAccess.java:86)
at com.serotonin.mango.MangoContextListener.databaseInitialize(MangoContextListener.java:24

at com.serotonin.mango.MangoContextListener.contextInitialized(MangoContextListener.java:85)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3972)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4467)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:526)
at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1041)
at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:964)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:502)
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1277)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:321)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
at org.apache.catalina.core.StandardHost.start(StandardHost.java:722)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
at org.apache.catalina.core.StandardService.start(StandardService.java:516)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
at org.apache.catalina.startup.Catalina.start(Catalina.java:593)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(id)

type=InnoDB' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
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:254

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 org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:266)
at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:421)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395)
... 31 more

There is a bug in the MySQL create script (found in the "db" directory). Add a comma at the end of line 264 and try again.

Made the correction, dropped the mysql tables., restared app. Now works. Thanks.

I had some changes in env.properties,then mango 1.12.3 version created tables in mysql ,but when i am running the application it is getting this error,i don't know why..

28 Jan, 2013 8:04:14 PM org.apache.catalina.core.StandardContext listenerStart

SEVERE: Exception sending context initialized event to listener instance of class com.serotonin.mango.MangoContextListener

com.serotonin.ShouldNeverHappenException: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select ds.id, ds.name, ds.dataSourceType, ds.enabled, vrt.updateSeconds, mbs.updateSeconds, mbs.commPortId, mbs.baudRate, mbs.flowControlIn, mbs.flowControlOut, mbs.dataBits, mbs.stopBits, mbs.parity, mbs.encoding, mbs.echo, mbi.updateSeconds, mbi.transportType, mbi.host, mbi.port from dataSources ds left join dataSourceVrt vrt on vrt.dataSourceId = ds.id left join dataSourceMbs mbs on mbs.dataSourceId = ds.id left join dataSourceMbi mbi on mbi.dataSourceId = ds.id]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'mango.dataSourceVrt' doesn't exist

at com.serotonin.mango.db.upgrade.DBUpgrade.checkUpgrade(DBUpgrade.java:90)
at com.serotonin.mango.db.DatabaseAccess.initialize(DatabaseAccess.java:150)
at com.serotonin.mango.MangoContextListener.databaseInitialize(MangoContextListener.java:241)
at com.serotonin.mango.MangoContextListener.contextInitialized(MangoContextListener.java:92)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3934)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4429)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:526)
at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:630)
at org.apache.catalina.startup.HostConfig.deployDescriptors(HostConfig.java:556)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:491)
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1206)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:314)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
at org.apache.catalina.core.StandardHost.start(StandardHost.java:722)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
at org.apache.catalina.core.StandardService.start(StandardService.java:516)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
at org.apache.catalina.startup.Catalina.start(Catalina.java:583)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:616)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:28

at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413)

The tables dataSourceVrt, dataSourceMbi, etc are from a very old version of Mango; probably pre-1.0.0. Your system didn't upgrade properly if the code is still looking for them.

I am using mango 1.12.3, if i use 1.12.5 version it is giving errors ,so i had installed 1.12.3 version,what should i do to get rid from this problem....