Hi, I worked through the torque tutorial for MySQL and had some problems. I solved the problems and now I want to describe these for others. Maybe one could add the things outlined below to the tutorial?:-)
I'm working with MySQL 3.23.52, Java 1.4.1 and torque-3.0-b4.
When working through the tutorial everything seems to work still no
errors occur. Afterwards I took a look into MySQL and found out the no
FOREIGN KEYS have been created.
The solution to this is that FORQIGN KEYS are only support by the
table-type InnoDB. MySQL 3.23.52 uses MyISAM as the default table-type
so if you want FOREIGN KEYS you have to enable InnoDB in your
my.cnf-File at first (see 1 or simply copy one of the template my.cnf to
/etc/my.cnf) and afterwards edit "templates/sql/base/mysql/db.props". In
the last line you can specify the mysql-Tabletype. The file should look
like this after having been edited:
--------------------cut--------------------
# For example, if you wanted to use the transactional type InnoDB,
# then specify "tableType = InnoDB"
#tableType = MyISAM
tableType = InnoDB
--------------------cut--------------------
If you now recreate the database by invoking:
ant -f build-torque.xml
ant -f build-torque.xml create-db
ant -f build-torque.xml id-table-init-sql
ant -f build-torque.xml insert-sql
You should get an error message from insert-sql
--------------------cut--------------------
Buildfile: build-torque.xml
....
[torque-sql-exec] Failed to execute: CREATE TABLE book ( book_id INTEGER
NOT NULL, title VARCHAR (255) NOT NULL, isbn VARCHAR (24) NOT NULL,
publisher_id INTEGER NOT NULL, author_id INTEGER NOT NULL, PRIMARY
KEY(book_id), FOREIGN KEY (publisher_id) REFERENCES publisher
(publisher_id), FOREIGN KEY (author_id) REFERENCES author (author_id) )
Type=InnoDB
[torque-sql-exec] java.sql.SQLException: General error: Can't create
table './bookstore/book.frm' (errno: 150)
[torque-sql-exec] Executing file:
/home/tom/downloads/java/torque-3.0-b4/src/sql/project-schema-idtable-init.sql
[torque-sql-exec] 10 of 11 SQL statements executed successfully
--------------------cut--------------------
This error message is caused by two different problems arising with
MySQL and foreign-keys. The first is that you have to create an INDEX
for every column you are declaring as FOREIGN KEY. The second problem
is: When creating the book-table the tables publisher and author you are
referencing have not been created at this time. For more informations
please take a look at (2).
To resolve this problem you've to edit "project-schema.xml". Adding
indexes and moving the book-table to the end of the file.
--------------------cut--------------------
....
<table name="book" description="Book Table">
....
<index
name="fk_author_id">
<index-column name="author_id"/>
</index>
<index
name="fk_publisher_id">
<index-column name="publisher_id"/>
</index>
<foreign-key foreignTable="publisher">
<reference
local="publisher_id"
foreign="publisher_id"/>
</foreign-key>
<foreign-key foreignTable="author">
<reference
local="author_id"
foreign="author_id"/>
</foreign-key>
</table>
</database>
--------------------cut--------------------
Now you can recreate the database as shown above and everything works
fine.
LINKS:
(1)http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#InnoDB_start
(2)http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#SEC447
tom schindl
--
b e s t s o l u t i o n . a t EDV Systemhaus GmbH
------------------------------------------------------------------------
Thomas Schindl Project Management mobile ++43/664/314 59 58
------------------------------------------------------------------------
Anton-Rauch-Str.6a A-6020 Innsbruck fax ++43/512/935834
http://www.bestsolution.at phone ++43/512/935834
signature.asc
Description: Dies ist ein digital signierter Nachrichtenteil
