SQL Power Software Forum

Can you please post some direction on how to connect to MySQL?

I've downloaded the driver "mysql-connector-java-5.0.5-bin.jar" and added it in the user preferences. Then when I add the connection source, in the JDBC URL field I use "jdbc:mysql://localhost:port/db" where "port" is substituted with port number and "db" is substituted with database name. Database type is set to "OTHER", PL_Schema Owner is blank.

I get the "Error during initial database probe" and looking into the exception details it says "... no JDBC driver class specified".

Hello,

You did all but one thing that had to be done to connect to MySQL. Since the current version of the Architect doesn't support MySQL the driver doesn't show up in the dropdown list in our GUI so you have to specify it manually.

To do this, open up your pl.ini file. If you do not know where it is saved just check out the user preferences. In the pl.ini file find the section that has the connection you created. For the JDBC Driver Class line, change it to read "JDBC Driver Class=com.mysql.jdbc.Driver". Save the file and reopen the Architect. You should be able to connect to the database now.

You may be happy to learn that in the upcoming release of the Architect MySQL will be fully supported. We plan to have the release out very soon, so be sure to check back!

- Ryan

Works great, thanks!

Now ... as I understand it ... the intended purpose of the database connections is to be able to move tables into the playpen. Then make changes in the playpen, and forward engineer them back into the database?

I dragged and dropped the tables, they seem to move over ok, however, the relationships are not retained. Is there any way I can bring the relationships over as well?

Some export capabilities would be nice as well. Maybe PDF format to share the data model with others.

You seem to have a good idea of how the Architect works. Any changes you make in the playpen are never applied to the source database unless you forward engineer. Also, you can forward engineer the playpen to any database, it doesn't have to be the one that the one the original tables came from. The connection to the database can be used for other things as well such as profiling and comparing two databases.

As for the relationships not being retained, I'm not sure what the problem is. What version of MySQL are you using? I hope that information helps us shed light on what the problem may be.

Your suggestion is a great one. As of now you can only print out the playpen but we can surely see the use of exporting it to a PDF. Again, thanks for the suggestion. We'll try to implement it in the next release.

Thanks for your input, and hopefully we can figure out this problem with relationships for you.

- Ryan

Thanks for the clarification Ryan.

I'm running MySQL 5.0.19.

It would be great if we could get the relationships working. And any chance that the next release that's coming out "very soon" is going to have the export capabilities?

I'd hate to have to rebuild all the relationships and then piece together screenshots to send the model to people who aren't using Power*Architect. ;)

Ok!

We've done some testing with various versions of MySQL, and we have been able to reproduce your problem. The good news is, the Architect does support reverse engineering foreign keys in MySQL.

But there is bad news: According to the MySQL 5.0.x documentation, you can only have foreign key relationships on InnoDB tables (not MyISAM).

MyISAM is the default storage engine on 5.0.19, so you are probably dealing with MyISAM tables. All of the tables in our existing MySQL 5.0.18 database were MyISAM. The 5.0.19 we set up for testing yesterday was also defaulting everything to MyISAM.

The ALTER TABLE .. ADD FOREIGN KEY .. statement executes with no errors on MyISAM tables, but doesn't create a foreign key constraint! It creates a "key" on the child table, which shows up as an index (not a constraint) in JDBC DatabaseMetaData. We havebn't been able to determine what this key does, but we know it doesn't enforce referential integrity.

Here is an example:

So the things to notice there are:

1. They're MyISAM tables by default
2. The "show create table" output doesn't say anything about constraints or foreign keys
3. There is no referential integrity check.. we were able to insert an orphan record into child_table.

Now, with InnoDB:

mysql> drop table parent_table;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table child_table;
Query OK, 0 rows affected (0.00 sec)

mysql> create table parent_table (parent_id integer primary key) engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> create table child_table (child_id integer primary key, parent_id integer) engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> alter table child_table add constraint my_fk foreign key (parent_id) references parent_table (parent_id);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table child_table;
(snip)
 CREATE TABLE `child_table` (
  `child_id` int(11) NOT NULL,
  `parent_id` int(11) default NULL,
  PRIMARY KEY  (`child_id`),
  KEY `my_fk` (`parent_id`),
  CONSTRAINT `my_fk` FOREIGN KEY (`parent_id`) REFERENCES `parent_table` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into child_table values (1, 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`test/child_table`, CONSTRAINT `my_fk` FOREIGN KEY (`parent_id`) REFERENCES `parent_table` (`parent_id`))

This time, the "show create table" shows us the FK constraint, and the referential integrity was enforced.

In the second case, the foreign key reverse engineering works in the Architect.

If converting your existing tables to InnoDB is not an option, I'm not sure if we'll be able to reliably detect the existence of foreign key relationships between them.