MySQL 5: unable to create InnoDB tables from within Servoy

Classic Servoy
1

I’m testing Servoy with a MySQL 5 database. Until now I have used the Sybase db included with the Servoy distribution.

Everything seems to work fine, except that I am not able to create InnoDB tables using Servoy. I understand that InnoDB tables are needed for transaction support.

I have set the default tables for MySQL to InnoDB. Nicola Gervasi, in his Mini-HowTo just published by Servoy Magazine, confirms that this should be done when configuring MySQL and Servoy.

The problem is that, if I try to create a table from within Servoy, the table type is always set to MyISAM, even if the default in MySQL is siet to InnoDB. Servoy forces the MyISAM table. This is the schema used by Servoy for a table of the repository db

CREATE TABLE `servoy_clients` (
  `client_id` varchar(50) NOT NULL,
  `user_id` int(11) default NULL,
  PRIMARY KEY  (`client_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I understand that I could create all tables in MySQL and then use them in Servoy, but this is not practical and I am not sure that the conversion between the Servoy data types and those of MySQL will be correct.

Any suggestions?

2

rioba:
I’m testing Servoy with a MySQL 5 database. Until now I have used the Sybase db included with the Servoy distribution.

Everything seems to work fine, except that I am not able to create InnoDB tables using Servoy. I understand that InnoDB tables are needed for transaction support.

I have set the default tables for MySQL to InnoDB. Nicola Gervasi, in his Mini-HowTo just published by Servoy Magazine, confirms that this should be done when configuring MySQL and Servoy.

The problem is that, if I try to create a table from within Servoy, the table type is always set to MyISAM, even if the default in MySQL is siet to InnoDB. Servoy forces the MyISAM table. This is the schema used by Servoy for a table of the repository db

CREATE TABLE `servoy_clients` (

client_id varchar(50) NOT NULL,
user_id int(11) default NULL,
PRIMARY KEY (client_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1




I understand that I could create all tables in MySQL and then use them in Servoy, but this is not practical and I am not sure that the conversion between the Servoy data types and those of MySQL will be correct.

Any suggestions?

Try to see what happens if you create a table from mysql client without specifiyng the engine, like this:

CREATE TABLE `test` (`test_id` varchar(50) NOT NULL, PRIMARY KEY (`test_id`))

After that check if the table is using InnoDB with this query: “show create table test;”, the output should be like this:

| test  | CREATE TABLE `test` (
  `test_id` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

If it’s using MyISAM there is a config problem (as I suspect), double check your /etc/my.cnf file, check if mysql is really reading this file or if it is reading some other my.cnf file (you can have a server wide config, a specific daemon config, etc. depending on the file position on the filesystem, RTM).
In the my.cnf file be sure to have added the “default-storage-engine=innodb” to the [mysqld] section, if it is added to other sections it doesn’t work and while you’re at it add the line “default-character-set=utf8” to use UTF for the default encoding (from your post I’ve seen you are using latin1). Be sure to have turned on “bin-log” otherwise InnoDB could not work (you can find istructions in my tutorial). Restart mysqld to apply all mods done to my.cnf and check the logs.
When everything works as expected drop all the tables in the servoy repository database and start servoy to recreate them and see what happens.

3

Nicola, thank you for your reply. Now everything is working as expected: I just had to restart the database after modifying the parameters.

All parameters were set correctly as per your tutorial so I did not understand what went wrong the first time. After reading your reply to this topic I reinstalled everything and edited again the my.cnf file but new tables were still MyISAM.

When I stopped the server and restarted it everything was fine: new tables were created as InnoDB.

4

Hello,

I also am trying to migrate from Sybase to MySQL. After installing MySQL using the latest installer from the MySQL website, I discovered that the default my.cnf file is empty except for the [mysqld] section header. Anyway, I copied the my-large.cnf file in the /usr/share/mysql directory and then made the following changes:

  1. Added to [mysqld]:
    default-storage-engine = innodb
    default-character-set = utf8
    default-collation = utf8_general_ci

  2. Uncommented the various lines relevant to innodb tables

  3. Changed the paths for the innodb-specific settings from /var/mysql/ to /usr/local/mysql/data/ (I tried both paths actually)

My problem is that when I restart MySQL, it takes a noticeably longer time to do so, and then I can’t connect to any databases using my admin tools. If I comment out the default-storage-engine line, MySQL starts up normally. Oddly, if I use default-storage-engine=MyISAM, MySQL also starts up normally.

I’m running MySQL 5.0.24 and Mac OS X Server 10.4.7. Any ideas? Would someone be willing to send me a copy of a my.cnf file that works on Mac OS X or Mac OS X Server?

5

OK, I think I’ve solved my problem. Some of the innodb-specific settings were a lot higher than those listed in Nicola’s very helpful article:
http://www.servoymagazine.com/home/2006 … rvoy_.html

Specifically,

  1. innodb_buffer_pool_size was set to 256M (Nicola had 16M)
  2. innodb_additional_mem_pool_size was set to 20M (Nicola had 2M)
  3. innodb_log_file_size was set to 64M (Nicola had 5M)

I don’t know enough about MySQL to know if these new settings are optimal, but at least MySQL is running now. Hope this helps someone.