MySQL is open source available, is a fast and popular relational database. Here are points to note when using MySQL.

  • Since the order is not prepared, the order is handled in a pseudo manner using a table.
  • An error may occur when setting the Japanese column name.
  • MySQL has the constraint that the length of the primary key (together with the composite primary key) must be within 1023 bytes.If you define a model that goes beyond this constraint, the following error occurs when creating the table.
    "Java.sql.SQLException: Specified key was too long; max key length is 1024 bytes"
    If the above error occurs, review the definition of the model so that the length of the primary key is within 1023 bytes.
  • The maximum length of table name (Wagby's model name) is 64 characters.
  • The maximum length of column name (model item name of Wagby) is 64 characters.
  • In MySQL 4/5, even if UTF - 8 is used as the character encoding of the database, the characters that can be handled are limited to the range of 1 character 3 bytes.For this reason Surrogate pair character adopted in Windows VistaYou can not register.[More ...] This can be handled by specifying the character code utf8mb4 in MySQL 5.5.[reference...]
  • The total number of bytes of the items contained in one table is 65,535 bytes or less.For example integer is counted as 4 bytes, and so on.Detail isMySQL manualPlease read.The point to note here is the varchar type.For example, if 100 items of varchar (255) character type item are defined, it will be regarded as 25,500 bytes with 100 items of 255 characters. However, since UTF-8 encoding is actually adopted, three times It will be 76,500 bytes, exceeding the limit.(MySQL's UTF-8 encoding is handled as 1 character 3 bytes.) Therefore, in models (tables) with a large number of items, it is necessary to devise such as specifying the size frequently for string type items.
  • In MySQL 6, the string type standard is 1 character 4 bytes.Therefore, MySQL is subject to the constraint that the length of the primary key (together with the complex primary key) must be within 1023 bytes ".If you select "MySQL 6" as the database type, varchar (64) is the standard mapping rule only for the string primary key.(However, if the user explicitly specifies "type of table definition", it takes precedence.)

The type specified by Wagby is handled as follows in the database.

Integer type integer
String type Varchar (255) (* 1, 2)
String type (text area) Text (* 3)
Date type date
Time type time
Date Time Type datetime
File type (file name) varchar(255)
1 byte integer tinyint
2 byte integer smallint
4 byte integer integer
8 byte integer bigint
4 byte floating point number float
8 byte floating point number double precision
Fixed value (creation date / update date) datetime
Other (URL type / mail address type etc.) varchar(255)
  • For model reference, it follows the type of the primary key of the referenced model.
  • Developers can also change the type.For details, refer to "Repository> Definition of model item> Database detailsPlease read.
1 The number specified by varchar type depends on MySQL version.Before 4.1, it is in "byte" unit, but after that it is in "character" unit.Please check the MySQL manual for details.
2 If you select "MySQL 6" as the database type, we have varchar (64) as the standard mapping only for the string type primary key.(However, if the developer explicitly specifies "type of table definition", it overrides that.)
3 The MySQL text type can store up to 65,535 bytes of characters.To store more character strings, please use "mediumtext" or "longtext" type etc.

Improvement of import processing

Wagby's import process issues a large amount of update SQL.At this time, if MySQL is configured to perform log flush processing on the physical disk for each transaction, processing takes time.

Setting write as follows improves write performance.

innodb_flush_log_at_trx_commit = 0

However, if you leave this setting, there is a high possibility that data will be lost in the event of a failure in normal operation, so it is required to return to the default value ("1") as soon as the import process is completed.

Wagby's data import tool InitLoader,Execute the SQL statement that the developer independently definedcan do.

In the sql element described in the setting file to be read by InitLoader, when describing multiple SQLs with semicolons, it is necessary to enable MultiQueries specification of MySQL.

This setting can be done by adding the following specification at the end of the connection URL to MySQL in environment setting file project_env.xls.

&allowMultiQueries=true

Reference information: "Driver / Datasource class name of Connector / J, URL syntax, and configuration properties"
http://dev.mysql.com/doc/refman/5.1/ja/connector-j-reference-configuration-properties.html