Hi,
I’m trying to install scadabr with latest release of mysql as storage database. When i restart the webapp to let it recreate the tables, I receive this error in the log file:
GRAVE: Exception sending context initialized event to listener instance of class com.serotonin.mango.MangoContextListener
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ create table systemSettings ( settingName varchar(32) not null, settingValue longtext, primary key (settingName) ) 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 ‘type=InnoDB’ at line 1
By researching a little i found that the problem is the syntax used by scadabr to create the table, now it’s incompatible.
Deprecated:
CREATE TABLE t1(id int) TYPE=InnoDB;
Fully today accepted:
CREATE TABLE t1(id int) ENGINE=InnoDB;
Try with sed command line with -e flag in order to replace TYPE for ENGINE.
Can anyone suggest to me how to solve this problem??
Best regards, Andrea.
Hello Andrea,
TYPE has been deprecated since MySQL 5.0… So by now, they may have dropped “TYPE” as a synonym.
But as I´ve checked in the .sql that creates the database structure, all created tables use “ENGINE”. At least for the version that I checked, do not assure you that all the previous ScadaBR versions use this syntax.
Anyway, thanks for the report with enough information to understand your problem. Could you please inform us what ScadaBR version you are using?
[]´s
Hello Diego,
are you sure TYPE was deprecated from 5.0 and not from 5.1? I’m using 5.1.63 or 69 on linux without problem for ScadaBR, everything is working fine. If i try mysql 5.5 on windows xp machine or linux ubuntu machine, i receive that error! I’m using ScadaBr 0.9.1!
Hello,
It was deprecated in version 5.0, but mantained as a synonym for backward compatibility
http://dev.mysql.com/doc/refman/5.0/en/using-innodb-tables.html
“The older term TYPE is supported as a synonym for ENGINE for backward compatibility, but ENGINE is the preferred term and TYPE is deprecated.”
But I couldn´t find the “t1” creation in the main .sql files. I will check for it again, get back to you soon…
Hi Diego!
sorry, “t1” table was just an example from google to try to explain the syntax problem! I don’t even know…unluckly…where are the query statement to create table on ScadaBR!
Ok, so, if could find in the mango.log file the creation statement, maybe we could find this “type” manace =]
Give some time to launch my Linux and search it properly…
Hi Diego, I think I found the SQL statement to modify.
…\ScadaBR\webapps\ScadaBR\WEB-INF\db\createTables-mysql.sql, and inside you’ll see “TYPE=INNODB” statement. I didn’t try yet but i think we can solve the problem by modifing it, maybe for next release. Is possible to have the EventList search working again, also without calendar like in mango??? mine is non working…I think it can be very useful. Thanks, Andrea.
Hello Andrea,
Nice! That’s a start =]
I’ll check with our team if the event search will be fully functional for 1.0 version. And, deal with this TYPE correction.
Thanks again!