For some reason it doesn't know that 'region' is in another schema. I assume the database has that relationship fully defined with the referenced schema specified, right?
Can you try the beta version (http://schemaspy.sourceforge.net/schemaSpy.jar) and see if that resolves it? There were some changes related to cross-schema analysis, but I'm not sure if it'll address your issue or not.
Let me know,
John
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
MySQL is a bit of an anomaly in that there's no real database instance to go against. That means that there's no real concept of "all" for MySQL. Instead of -all and -db try using -schemas "rmx_1 rmx_2 dbWhatever" and let me know how that goes.
I'll need to update the docs to indicate this unexpected behavior.
John
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have tried -db -schemas, but for different schemas, schemaSpy still analyzes the same db specified, and generate same diagram only with different schema name. I think we need a way to let schemaSpy to analyze all the databases in mysql
danny
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
As I said, don't use -db or -all. Use -schemas instead.
> I think we need a way to let schemaSpy to analyze all the databases in mysql
As far as I know MySQL doesn't have the concept of a controller/manager that can respond to global database metadata queries like other databases provide. If one exists I have no idea how to query it through JDBC's metadata services.
John
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
thanks so much for writing this remarkably handy tool.
I'm experiencing a similar problem to Danny, and couldn't find a bug in the tracker quite describing it.
I'm using mysql 5.0.90
I've got a test case for this using 2 mysql namespaces test_parent and test_child
to recreate
CREATE database test_parent;
use test_parent;
CREATE TABLE `parent_table` (
`id` int(11) NOT NULL,
`numerak` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE database test_child;
use test_child;
CREATE TABLE `child_table` (
`id` int(11) NOT NULL,
`parent_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
FOREIGN KEY (`parent_id`) REFERENCES `test_parent`.`parent_table` (`id`)
) ENGINE=InnoDB;
so test_child.child_table has a foreign key to test_parent.parent_table(`id`)
java -jar schemaSpy_5.0.0.jar -dp <some_path_to_connector mysql-connector-java-5.1.7-bin.jar> -t mysql -o ~/schemas/test_child -host <my_host> -u <my_user> -font courier -schemas "test_child, test_parent" -loglevel finest -p <some_pass>
gets me
14:08:52.784 INFO: SchemaAnalyzer.analyze - Starting schema analysis
Analyzing schemas:
test_child test_parent
Analyzing test_child
14:08:52.995 INFO: SchemaAnalyzer.analyze - Starting schema analysis
14:08:53.055 CONFIG: ConnectionURLBuilder.<init> - connectionURL: jdbc:mysql://my_host/test_child
14:08:53.056 INFO: SchemaAnalyzer.getConnection - Using database properties:
14:08:53.056 INFO: SchemaAnalyzer.getConnection - /net/sourceforge/schemaspy/dbTypes/mysql.properties
14:08:53.214 INFO: SchemaAnalyzer.analyze - Connected to MySQL - 5.0.90
14:08:53.215 INFO: SchemaAnalyzer.analyze - Gathering schema details
14:08:53.234 FINE: Database$NameValidator.isValid - Including table child_table: matches inclusion pattern ".*"
14:08:53.236 FINE: Table.<init> - null.child_table
14:08:53.255 FINE: Database$TableCreator.createImpl - Found details of table child_table
14:08:53.263 FINER: ForeignKeyConstraint.<init> - Adding foreign key constraint 'child_table_ibfk_1' to child_table
14:08:53.263 FINE: Table.<init> - null.parent_table
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'test_child.parent_table' doesn't exist
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
at com.mysql.jdbc.DatabaseMetaData$7.forEach(DatabaseMetaData.java:3931)
at com.mysql.jdbc.IterateBlock.doForAll(IterateBlock.java:50)
at com.mysql.jdbc.DatabaseMetaData.getPrimaryKeys(DatabaseMetaData.java:3915)
at net.sourceforge.schemaspy.model.Table.initPrimaryKeys(Table.java:235)
at net.sourceforge.schemaspy.model.Table.<init>(Table.java:90)
at net.sourceforge.schemaspy.model.RemoteTable.<init>(RemoteTable.java:38)
at net.sourceforge.schemaspy.model.Database.addRemoteTable(Database.java:741)
at net.sourceforge.schemaspy.model.Table.addForeignKey(Table.java:199)
at net.sourceforge.schemaspy.model.Table.connectForeignKeys(Table.java:109)
at net.sourceforge.schemaspy.model.Database.connectTables(Database.java:984)
at net.sourceforge.schemaspy.model.Database.<init>(Database.java:83)
at net.sourceforge.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:211)
at net.sourceforge.schemaspy.Main.main(Main.java:42)
Failed to execute this process (rc 1): java -Doneofmultipleschemas=true -jar schemaSpy_5.0.0.jar -ahic -dp "my_connector_path/mysql-connector-java-5.1.7-bin.jar" -css "schemaSpy.css" -charset "ISO-8859-1" -font "courier" -fontsize "11" -t "mysql" -renderer ":gd" -p "my_pass" -u "my_user" -host "my_host" -loglevel "finest" -sqlFormatter "net.sourceforge.schemaspy.view.DefaultSqlFormatter" -i ".*" -I "" -x "" -X "" -dbthreads "2147483647" -maxdet "300" -db test_child -o /path_to_home/my_user/schemas/test_child/test_child
note int the error, "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'test_child.parent_table' doesn't exist" it will never find test_child.parent_table. parent table is located at test_partent.parent_table. Also, it's unclear why on the last line "failed to execute this process", that the -schemas option was replaced with -db.
Note that for schemaspy.4.1.1, worked. It was able to generate the schema for test_child for this problem it complained with Gathering schema details….Couldn't add FK 'child_table_ibfk_1' to table 'child_table' - Unknown Referenced Table 'parent_table'
java -jar <my_path>/schemaSpy_4.1.1.jar -dp <connector path> -t mysql -o ~/schemas/test_child -host <my_host> -u <my_user> -font courier -db test_child -p <some_pass>
am I using -schemas incorrectly? Behavior is identical if I omit the , seperator as described earlier in this post. Though the behavior is slightly different if I reorder the list of schemas.
also, oddly, the -pfp option seems not to work correctly with -schemas, I am indefinitely prompted to enter the password
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks for the details on how to reproduce the problem.
I figured out why the older version worked. Back then it completely ignored any table not in the current schema and provided very little linkage when multiple schemas were involved.
The issue appears to be that MySQL's JDBC drivers treat the table's schema as if it was a catalog. I.e. INFORMATION_SCHEMA.TABLES TABLE_CATALOG is 'def', TABLE_SCHEMA is 'test_parent' while the JDBC metadata says that catalog is 'test_parent' and schema is null. This is the first database that I've run across where the catalog was relevant. I'm trying to figure out how to deal with it without breaking lots of other databases.
John
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Please try the latest beta. I'm still working out the details of specifying catalogs. It's really bizarre how inconsistent MySQL is in dealing with schemas.
John
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
This solves the test case. However it partially fails on real data. The behavior is improved beyond where it was in 4.1.1 though. With the real data, it successfully renders the entire child schema including the referenced table from the parent schema, but fails on the the parent schema. Directories for the parent are created but empty. Still seeing the problem with using -pfp and -schemas.
The trace using the real and considerably more complicated parent/child schemas.
java -jar schemaSpy.jar -dp /path/mysql-connector-java-5.1.7-bin.jar -t mysql -o ~/schemas/child -host <host> -u <user> -font courier -schemas "child, parent" -p <pass>
Analyzing schemas:
child parent
Analyzing child
Using database properties:
/net/sourceforge/schemaspy/dbTypes/mysql.properties
Gathering schema details……………………………(0sec)
Writing/graphing summary…………(0sec)
Writing/diagramming details…………………………(3sec)
Wrote relationship details of 30 tables/views to directory '/path/schemas/child/child' in 4 seconds.
View the results by opening /path/schemas/child/child/index.html
Analyzing parent
Using database properties:
/net/sourceforge/schemaspy/dbTypes/mysql.properties
Gathering schema details……………java.lang.NullPointerException
at net.sourceforge.schemaspy.model.RemoteTable.connectForeignKeys(RemoteTable.java:60)
at net.sourceforge.schemaspy.model.Database.addRemoteTable(Database.java:761)
at net.sourceforge.schemaspy.model.Table.addForeignKey(Table.java:220)
at net.sourceforge.schemaspy.model.Table.connectForeignKeys(Table.java:124)
at net.sourceforge.schemaspy.model.Database.connectTables(Database.java:994)
at net.sourceforge.schemaspy.model.Database.<init>(Database.java:86)
at net.sourceforge.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:228)
at net.sourceforge.schemaspy.Main.main(Main.java:45)
Failed to execute this process (rc 1): java -Doneofmultipleschemas=true -jar /path/schemaSpy.jar -ahic -dp "/path/mysql-connector-java-5.1.7-bin.jar" -css "schemaSpy.css" -charset "ISO-8859-1" -font "courier" -fontsize "11" -t "mysql" -renderer ":gd" -p <pass> -u <user> -host <host> -loglevel "warning" -sqlFormatter "net.sourceforge.schemaspy.view.DefaultSqlFormatter" -i ".*" -I "" -x "" -X "" -dbthreads "2147483647" -maxdet "300" -db parent -o /path/child/parent
I'll see if I can come up with a new test case, one difference for sure is that the real child has multiple tables linking to the one table in the real parent. Thanks for the improvements, they've made the new version usable for me again.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Your test cases help are a huge help in tracking down these problems.
Can you create a bug report for the -pfp issue? I assume the expected behavior would be to prompt once and use the same password for everything that's being evaluated.
John
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
the problem with the real data here is difficult to replicate. what's happening is that the equivalent parent_table references a third schema, and in that third schema (call it test_grand_parent) there is a oddly mishapen foreign key reference in one of the tables in test_grand_parent referencing another table in test_grand_parent. if I removing the reference from parent_table to the table with the mishpaen reference in test_grand_parent, then the schema is rendered without issue despite other references from parent_table into other test_grand_parent tables. I need to figure out how that foereign key reference was created before I can recreate this. isntead of refering to the referenced table in the form "foreign key (`info_id`) references info(`id`)", this has the form "foreign key(`info_id`) test_grand_parent@002einfo(`id`).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Alright, the problem mentioned in my last post is limited to a mysql aberration, a foreign key had been mangled in some undocumented process. It can be ignored as the problem probably rendered that schema unusable.
However, I'm experiencing a new multiple schema problem. Given a triangular configuration where we have a parent, a child, and a pet. The child and pet both reference the parent, and the pet references the child. In practice, the pet's reference to the parent is failing to resolve the foreign key properly as test_parent.null.parent_table. In this parent/child/pet example pet is failing to resolve its reference to child as test_child.null.child_table.
That one came from trying to deal with the possible combinations of databases that have no schemas and/or no catalogs. From the SQL perspective MySQL has schemas and no catalogs, but from the JDBC perspective it has catalogs but no schemas. It hurts.
Can you try the latest beta? It should resolve the issue.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Note that MySQL only provides cross-schema metadata in one direction. If you want a fully cross-linked set of ER diagrams you'll have to supplement the database's metadata with XML metadata.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Cross-schema foreign keys could not be generated for my database. I have added -all parameter, but it did not work
java -Xms512m -Xmx1024m -jar ./schemaSpy.jar -cp ./mysql-connector-java-3.1.14-bin.jar -t mysql -o $dir -host $host -db rmx_1 -all -u $user -p $password
what I did wrong? please help.
Thanks,
Danny
Please elaborate on what "did not work" means. Did it give an error message or something to indicate what went wrong?
John
a bunch of error messages like
Gathering schema details...............................................Couldn't add FK 'FK736248834B8E9FFF' to table 'geo_target_region_mapping' - Unknown Referenced Table 'region'
'FK736248834B8E9FFF' is the foreign key to table 'region' in different schema.
For some reason it doesn't know that 'region' is in another schema. I assume the database has that relationship fully defined with the referenced schema specified, right?
Can you try the beta version (http://schemaspy.sourceforge.net/schemaSpy.jar) and see if that resolves it? There were some changes related to cross-schema analysis, but I'm not sure if it'll address your issue or not.
Let me know,
John
yes, relationship is fully defined.
I tried the new jar, but it does not work either.
'geo_target_region_mapping' table is in database rmx_1, here is the DDL
geo_target_region_mapping | CREATE TABLE "geo_target_region_mapping" (
"geo_target_id" bigint(20) NOT NULL,
"region_id" int(11) NOT NULL,
PRIMARY KEY ("geo_target_id","region_id"),
KEY "FK736248834B8E9FFF" ("region_id"),
KEY "FK736248839F9E57CD" ("geo_target_id"),
CONSTRAINT "FK736248834B8E9FFF" FOREIGN KEY ("region_id") REFERENCES "rmx_common"."region" ("id"),
CONSTRAINT "FK736248839F9E57CD" FOREIGN KEY ("geo_target_id") REFERENCES "geo_target" ("id")
) |
Also, it does not analyze other databases in this server, except rmx_1, which is the one specified.
I suspect the parameters I gave were not correct, can you help to double check it for me?
java -Xms512m -Xmx1024m -jar ./schemaSpy.jar -cp ./mysql-connector-java-3.1.14-bin.jar -t mysql -o $dir -host $host -db rmx_1 -all -u $user -p $password
Thanks,
danny
MySQL is a bit of an anomaly in that there's no real database instance to go against. That means that there's no real concept of "all" for MySQL. Instead of -all and -db try using -schemas "rmx_1 rmx_2 dbWhatever" and let me know how that goes.
I'll need to update the docs to indicate this unexpected behavior.
John
I have tried -db -schemas, but for different schemas, schemaSpy still analyzes the same db specified, and generate same diagram only with different schema name. I think we need a way to let schemaSpy to analyze all the databases in mysql
danny
As I said, don't use -db or -all. Use -schemas instead.
> I think we need a way to let schemaSpy to analyze all the databases in mysql
As far as I know MySQL doesn't have the concept of a controller/manager that can respond to global database metadata queries like other databases provide. If one exists I have no idea how to query it through JDBC's metadata services.
John
thanks so much for writing this remarkably handy tool.
I'm experiencing a similar problem to Danny, and couldn't find a bug in the tracker quite describing it.
I'm using mysql 5.0.90
I've got a test case for this using 2 mysql namespaces test_parent and test_child
to recreate
CREATE database test_parent;
use test_parent;
CREATE TABLE `parent_table` (
`id` int(11) NOT NULL,
`numerak` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE database test_child;
use test_child;
CREATE TABLE `child_table` (
`id` int(11) NOT NULL,
`parent_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
FOREIGN KEY (`parent_id`) REFERENCES `test_parent`.`parent_table` (`id`)
) ENGINE=InnoDB;
so test_child.child_table has a foreign key to test_parent.parent_table(`id`)
java -jar schemaSpy_5.0.0.jar -dp <some_path_to_connector mysql-connector-java-5.1.7-bin.jar> -t mysql -o ~/schemas/test_child -host <my_host> -u <my_user> -font courier -schemas "test_child, test_parent" -loglevel finest -p <some_pass>
gets me
14:08:52.784 INFO: SchemaAnalyzer.analyze - Starting schema analysis
Analyzing schemas:
test_child test_parent
Analyzing test_child
14:08:52.995 INFO: SchemaAnalyzer.analyze - Starting schema analysis
14:08:53.055 CONFIG: ConnectionURLBuilder.<init> - connectionURL: jdbc:mysql://my_host/test_child
14:08:53.056 INFO: SchemaAnalyzer.getConnection - Using database properties:
14:08:53.056 INFO: SchemaAnalyzer.getConnection - /net/sourceforge/schemaspy/dbTypes/mysql.properties
14:08:53.214 INFO: SchemaAnalyzer.analyze - Connected to MySQL - 5.0.90
14:08:53.215 INFO: SchemaAnalyzer.analyze - Gathering schema details
14:08:53.234 FINE: Database$NameValidator.isValid - Including table child_table: matches inclusion pattern ".*"
14:08:53.236 FINE: Table.<init> - null.child_table
14:08:53.255 FINE: Database$TableCreator.createImpl - Found details of table child_table
14:08:53.263 FINER: ForeignKeyConstraint.<init> - Adding foreign key constraint 'child_table_ibfk_1' to child_table
14:08:53.263 FINE: Table.<init> - null.parent_table
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'test_child.parent_table' doesn't exist
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
at com.mysql.jdbc.DatabaseMetaData$7.forEach(DatabaseMetaData.java:3931)
at com.mysql.jdbc.IterateBlock.doForAll(IterateBlock.java:50)
at com.mysql.jdbc.DatabaseMetaData.getPrimaryKeys(DatabaseMetaData.java:3915)
at net.sourceforge.schemaspy.model.Table.initPrimaryKeys(Table.java:235)
at net.sourceforge.schemaspy.model.Table.<init>(Table.java:90)
at net.sourceforge.schemaspy.model.RemoteTable.<init>(RemoteTable.java:38)
at net.sourceforge.schemaspy.model.Database.addRemoteTable(Database.java:741)
at net.sourceforge.schemaspy.model.Table.addForeignKey(Table.java:199)
at net.sourceforge.schemaspy.model.Table.connectForeignKeys(Table.java:109)
at net.sourceforge.schemaspy.model.Database.connectTables(Database.java:984)
at net.sourceforge.schemaspy.model.Database.<init>(Database.java:83)
at net.sourceforge.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:211)
at net.sourceforge.schemaspy.Main.main(Main.java:42)
Failed to execute this process (rc 1): java -Doneofmultipleschemas=true -jar schemaSpy_5.0.0.jar -ahic -dp "my_connector_path/mysql-connector-java-5.1.7-bin.jar" -css "schemaSpy.css" -charset "ISO-8859-1" -font "courier" -fontsize "11" -t "mysql" -renderer ":gd" -p "my_pass" -u "my_user" -host "my_host" -loglevel "finest" -sqlFormatter "net.sourceforge.schemaspy.view.DefaultSqlFormatter" -i ".*" -I "" -x "" -X "" -dbthreads "2147483647" -maxdet "300" -db test_child -o /path_to_home/my_user/schemas/test_child/test_child
note int the error, "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'test_child.parent_table' doesn't exist" it will never find test_child.parent_table. parent table is located at test_partent.parent_table. Also, it's unclear why on the last line "failed to execute this process", that the -schemas option was replaced with -db.
similar results for using -db test_child
same results using the latest version at http://schemaspy.sourceforge.net/schemaSpy.jar
Note that for schemaspy.4.1.1, worked. It was able to generate the schema for test_child for this problem it complained with Gathering schema details….Couldn't add FK 'child_table_ibfk_1' to table 'child_table' - Unknown Referenced Table 'parent_table'
java -jar <my_path>/schemaSpy_4.1.1.jar -dp <connector path> -t mysql -o ~/schemas/test_child -host <my_host> -u <my_user> -font courier -db test_child -p <some_pass>
am I using -schemas incorrectly? Behavior is identical if I omit the , seperator as described earlier in this post. Though the behavior is slightly different if I reorder the list of schemas.
also, oddly, the -pfp option seems not to work correctly with -schemas, I am indefinitely prompted to enter the password
Thanks for the details on how to reproduce the problem.
I figured out why the older version worked. Back then it completely ignored any table not in the current schema and provided very little linkage when multiple schemas were involved.
The issue appears to be that MySQL's JDBC drivers treat the table's schema as if it was a catalog. I.e. INFORMATION_SCHEMA.TABLES TABLE_CATALOG is 'def', TABLE_SCHEMA is 'test_parent' while the JDBC metadata says that catalog is 'test_parent' and schema is null. This is the first database that I've run across where the catalog was relevant. I'm trying to figure out how to deal with it without breaking lots of other databases.
John
Please try the latest beta. I'm still working out the details of specifying catalogs. It's really bizarre how inconsistent MySQL is in dealing with schemas.
John
This solves the test case. However it partially fails on real data. The behavior is improved beyond where it was in 4.1.1 though. With the real data, it successfully renders the entire child schema including the referenced table from the parent schema, but fails on the the parent schema. Directories for the parent are created but empty. Still seeing the problem with using -pfp and -schemas.
The trace using the real and considerably more complicated parent/child schemas.
java -jar schemaSpy.jar -dp /path/mysql-connector-java-5.1.7-bin.jar -t mysql -o ~/schemas/child -host <host> -u <user> -font courier -schemas "child, parent" -p <pass>
Analyzing schemas:
child parent
Analyzing child
Using database properties:
/net/sourceforge/schemaspy/dbTypes/mysql.properties
Gathering schema details……………………………(0sec)
Writing/graphing summary…………(0sec)
Writing/diagramming details…………………………(3sec)
Wrote relationship details of 30 tables/views to directory '/path/schemas/child/child' in 4 seconds.
View the results by opening /path/schemas/child/child/index.html
Analyzing parent
Using database properties:
/net/sourceforge/schemaspy/dbTypes/mysql.properties
Gathering schema details……………java.lang.NullPointerException
at net.sourceforge.schemaspy.model.RemoteTable.connectForeignKeys(RemoteTable.java:60)
at net.sourceforge.schemaspy.model.Database.addRemoteTable(Database.java:761)
at net.sourceforge.schemaspy.model.Table.addForeignKey(Table.java:220)
at net.sourceforge.schemaspy.model.Table.connectForeignKeys(Table.java:124)
at net.sourceforge.schemaspy.model.Database.connectTables(Database.java:994)
at net.sourceforge.schemaspy.model.Database.<init>(Database.java:86)
at net.sourceforge.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:228)
at net.sourceforge.schemaspy.Main.main(Main.java:45)
Failed to execute this process (rc 1): java -Doneofmultipleschemas=true -jar /path/schemaSpy.jar -ahic -dp "/path/mysql-connector-java-5.1.7-bin.jar" -css "schemaSpy.css" -charset "ISO-8859-1" -font "courier" -fontsize "11" -t "mysql" -renderer ":gd" -p <pass> -u <user> -host <host> -loglevel "warning" -sqlFormatter "net.sourceforge.schemaspy.view.DefaultSqlFormatter" -i ".*" -I "" -x "" -X "" -dbthreads "2147483647" -maxdet "300" -db parent -o /path/child/parent
I'll see if I can come up with a new test case, one difference for sure is that the real child has multiple tables linking to the one table in the real parent. Thanks for the improvements, they've made the new version usable for me again.
Your test cases help are a huge help in tracking down these problems.
Can you create a bug report for the -pfp issue? I assume the expected behavior would be to prompt once and use the same password for everything that's being evaluated.
John
I've opened a bug report on the -pfp issue.
John
the problem with the real data here is difficult to replicate. what's happening is that the equivalent parent_table references a third schema, and in that third schema (call it test_grand_parent) there is a oddly mishapen foreign key reference in one of the tables in test_grand_parent referencing another table in test_grand_parent. if I removing the reference from parent_table to the table with the mishpaen reference in test_grand_parent, then the schema is rendered without issue despite other references from parent_table into other test_grand_parent tables. I need to figure out how that foereign key reference was created before I can recreate this. isntead of refering to the referenced table in the form "foreign key (`info_id`) references info(`id`)", this has the form "foreign key(`info_id`) test_grand_parent@002einfo(`id`).
Alright, the problem mentioned in my last post is limited to a mysql aberration, a foreign key had been mangled in some undocumented process. It can be ignored as the problem probably rendered that schema unusable.
However, I'm experiencing a new multiple schema problem. Given a triangular configuration where we have a parent, a child, and a pet. The child and pet both reference the parent, and the pet references the child. In practice, the pet's reference to the parent is failing to resolve the foreign key properly as test_parent.null.parent_table. In this parent/child/pet example pet is failing to resolve its reference to child as test_child.null.child_table.
to recreate
CREATE DATABASE test_parent;
USE test_parent;
CREATE TABLE `parent_table` (
`id` int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE DATABASE test_child;
USE test_child;
CREATE TABLE `child_table` (
`id` int(11) NOT NULL auto_increment,
`parent_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
FOREIGN KEY (`parent_id`) REFERENCES `test_parent`.`parent_table` (`id`)
) ENGINE=InnoDB;
CREATE DATABASE test_pet;
USE test_pet;
CREATE TABLE `pet_table` (
`id` int(11) NOT NULL auto_increment,
`parent_id` int(11) NOT NULL,
`child_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
KEY `child_id` (`child_id`),
CONSTRAINT `pet_table_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `test_parent`.`parent_table` (`id`),
CONSTRAINT `pet_table_ibfk_2` FOREIGN KEY (`child_id`) REFERENCES `test_child`.`child_table` (`id`)
) ENGINE=InnoDB;
using the version at http://schemaspy.sourceforge.net/schemaSpy.jar
java -jar <path>/schemaSpy.jar -dp <path>/mysql-connector-java-5.1.7-bin.jar
-t mysql -o <path>/test -host <host> -font Courier -loglevel finest -schemas
'test_parent, test_child, test_pet' -u <user> -p <pass>
java -jar <path> -dp <path> -t mysql -o <path> -host <host> -font Courier -loglevel finest -schemas 'test_parent, test_child, test_pet' -u <user> -p <pass>
10:50:01.837 INFO: SchemaAnalyzer.analyze - Starting schema analysis
10:50:01.868 CONFIG: Dot.<init> - Version: "dot - graphviz version 2.26.3 (20100126.1600)"
10:50:01.880 INFO: Dot.supportsRenderer - Failed to validate png renderer ':cairo'. Reverting to default renderer for png.
Analyzing schemas:
test_parent test_child test_pet
Analyzing test_parent
10:50:01.911 FINE: MultipleSchemaAnalyzer.analyze - Analyzing schema with: [java, -Doneofmultipleschemas=true, -jar, <path>, -ahic, -dp, " <path>", -css, "schemaSpy.css", -charset, "ISO-8859-1", -font, "Courier", -fontsize, "11", -t, "mysql", -renderer, ":gd", -p, "<pass>", -u, "<user>", -host, "<host>", -loglevel, "finest", -sqlFormatter, "net.sourceforge.schemaspy.view.DefaultSqlForma
10:50:02.071 INFO: SchemaAnalyzer.analyze - Starting schema analysis
10:50:02.115 CONFIG: Dot.<init> - Version: "dot - graphviz version 2.26.3 (20100126.1600)"
10:50:02.128 INFO: Dot.supportsRenderer - Failed to validate png renderer ':cairo'. Reverting to default renderer for png.
10:50:02.145 CONFIG: ConnectionURLBuilder.<init> - connectionURL: jdbc:mysql: <path>
10:50:02.146 INFO: SchemaAnalyzer.getConnection - Using database properties:
10:50:02.146 INFO: SchemaAnalyzer.getConnection - [ <path>
10:50:02.305 FINE: SchemaAnalyzer.analyze - supportsSchemasInTableDefinitions: false
10:50:02.306 FINE: SchemaAnalyzer.analyze - supportsCatalogsInTableDefinitions: true
10:50:02.306 FINE: SchemaAnalyzer.analyze - catalog not specified for a database that requires one. using dbName: 'test_parent'
10:50:02.308 INFO: SchemaAnalyzer.analyze - Connected to MySQL - 5.0.90
10:50:02.308 INFO: SchemaAnalyzer.analyze - Gathering schema details
10:50:02.316 FINE: Database.prepareStatement - select table_schema as table_catalog, null as table_schema, table_name, table_comment, table_rows from information_schema.tables where table_schema=? and table_type='BASE TABLE'
10:50:02.330 FINE: Database$NameValidator.isValid - Including table parent_table: matches inclusion pattern ".*"
10:50:02.332 FINE: Table.<init> - Creating Table test_parent.parent_table
10:50:02.349 FINE: Table.initPrimaryKeys - Querying primary keys for test_parent.parent_table
10:50:02.351 FINE: Database$TableCreator.createImpl - Retrieved details of test_parent.parent_table
10:50:02.352 FINE: Database.prepareStatement - select table_schema as view_catalog, null as view_schema, table_name as view_name, view_definition, null as view_comment from information_schema.views where table_schema=?
10:50:02.357 FINE: Database.prepareStatement - select table_name, column_name, replace(column_type,"','","', '") as column_type, left(column_type, locate("(", column_type)-1) as short_column_type from information_schema.columns where table_schema=? and (column_type like 'enum(%' or column_type like 'set(%')
10:50:02.359 FINER: Table.connectForeignKeys - Connecting foreign keys to test_parent.parent_table
10:50:02.380 INFO: SchemaAnalyzer.analyze - Gathered schema details in 0 seconds
10:50:02.380 INFO: SchemaAnalyzer.analyze - Writing <path> summary
10:50:02.415 FINE: Dot.generateDiagram - dot -Tpng:gd <path> -o <path> -Tcmapx
10:50:02.460 INFO: SchemaAnalyzer.analyze - Completed summary in 0 seconds
10:50:02.460 INFO: SchemaAnalyzer.analyze - Writing <path> details
10:50:02.460 FINE: SchemaAnalyzer.analyze - Writing details of parent_table
10:50:02.484 INFO: SchemaAnalyzer.analyze - Wrote table details in 0 seconds
10:50:02.484 INFO: SchemaAnalyzer.analyze - Wrote relationship details of 1 tables <path> to directory ' <path> in 0 seconds.
10:50:02.485 INFO: SchemaAnalyzer.analyze - View the results by opening <path>
Analyzing test_child
10:50:02.494 FINE: MultipleSchemaAnalyzer.analyze - Analyzing schema with: [java, -Doneofmultipleschemas=true, -jar, <path>, -ahic, -dp, " <path>", -css, "schemaSpy.css", -charset, "ISO-8859-1", -font, "Courier", -fontsize, "11", -t, "mysql", -renderer, ":gd", -p, "<pass>", -u, "<user>", -host, "<host>", -loglevel, "finest", -sqlFormatter, "net.sourceforge.schemaspy.view.DefaultSqlForma
10:50:03.171 INFO: SchemaAnalyzer.analyze - Starting schema analysis
10:50:03.259 CONFIG: Dot.<init> - Version: "dot - graphviz version 2.26.3 (20100126.1600)"
10:50:03.388 INFO: Dot.supportsRenderer - Failed to validate png renderer ':cairo'. Reverting to default renderer for png.
10:50:03.478 CONFIG: ConnectionURLBuilder.<init> - connectionURL: jdbc:mysql: <path>
10:50:03.478 INFO: SchemaAnalyzer.getConnection - Using database properties:
10:50:03.478 INFO: SchemaAnalyzer.getConnection - [ <path>
10:50:03.636 FINE: SchemaAnalyzer.analyze - supportsSchemasInTableDefinitions: false
10:50:03.637 FINE: SchemaAnalyzer.analyze - supportsCatalogsInTableDefinitions: true
10:50:03.637 FINE: SchemaAnalyzer.analyze - catalog not specified for a database that requires one. using dbName: 'test_child'
10:50:03.646 INFO: SchemaAnalyzer.analyze - Connected to MySQL - 5.0.90
10:50:03.647 INFO: SchemaAnalyzer.analyze - Gathering schema details
10:50:03.654 FINE: Database.prepareStatement - select table_schema as table_catalog, null as table_schema, table_name, table_comment, table_rows from information_schema.tables where table_schema=? and table_type='BASE TABLE'
10:50:03.667 FINE: Database$NameValidator.isValid - Including table child_table: matches inclusion pattern ".*"
10:50:03.669 FINE: Table.<init> - Creating Table test_child.child_table
10:50:03.685 FINE: Table.initPrimaryKeys - Querying primary keys for test_child.child_table
10:50:03.687 FINE: Database$TableCreator.createImpl - Retrieved details of test_child.child_table
10:50:03.688 FINE: Database.prepareStatement - select table_schema as view_catalog, null as view_schema, table_name as view_name, view_definition, null as view_comment from information_schema.views where table_schema=?
10:50:03.693 FINE: Database.prepareStatement - select table_name, column_name, replace(column_type,"','","', '") as column_type, left(column_type, locate("(", column_type)-1) as short_column_type from information_schema.columns where table_schema=? and (column_type like 'enum(%' or column_type like 'set(%')
10:50:03.695 FINER: Table.connectForeignKeys - Connecting foreign keys to test_child.child_table
10:50:03.697 FINER: ForeignKeyConstraint.<init> - Adding foreign key constraint 'child_table_ibfk_1' to test_child.child_table
10:50:03.698 FINE: Table.addForeignKey - Adding remote table test_parent.null.parent_table
10:50:03.698 FINE: Database.addRemoteTable - Creating remote table test_parent.null.parent_table
10:50:03.698 FINE: Table.<init> - Creating RemoteTable test_parent.parent_table
10:50:03.700 FINE: Table.initPrimaryKeys - Querying primary keys for test_parent.parent_table
10:50:03.701 FINE: Database.addRemoteTable - Adding remote table test_parent.null.parent_table
10:50:03.722 INFO: SchemaAnalyzer.analyze - Gathered schema details in 0 seconds
10:50:03.722 INFO: SchemaAnalyzer.analyze - Writing <path> summary
10:50:03.849 FINE: Dot.generateDiagram - dot -Tpng:gd <path> -o <path> -Tcmapx
10:50:03.904 FINE: Dot.generateDiagram - dot -Tpng:gd <path> -o <path> -Tcmapx
10:50:03.990 INFO: SchemaAnalyzer.analyze - Completed summary in 0 seconds
10:50:03.990 INFO: SchemaAnalyzer.analyze - Writing <path> details
10:50:03.990 FINE: SchemaAnalyzer.analyze - Writing details of child_table
10:50:04.000 FINE: Dot.generateDiagram - dot -Tpng:gd <path> -o <path> -Tcmapx
10:50:04.054 INFO: SchemaAnalyzer.analyze - Wrote table details in 0 seconds
10:50:04.054 INFO: SchemaAnalyzer.analyze - Wrote relationship details of 1 tables <path> to directory ' <path> in 0 seconds.
10:50:04.054 INFO: SchemaAnalyzer.analyze - View the results by opening <path>
Analyzing test_pet
10:50:04.097 FINE: MultipleSchemaAnalyzer.analyze - Analyzing schema with: [java, -Doneofmultipleschemas=true, -jar, <path>, -ahic, -dp, " <path>", -css, "schemaSpy.css", -charset, "ISO-8859-1", -font, "Courier", -fontsize, "11", -t, "mysql", -renderer, ":gd", -p, "<pass>", -u, "<user>", -host, "<host>", -loglevel, "finest", -sqlFormatter, "net.sourceforge.schemaspy.view.DefaultSqlForma
10:50:04.284 INFO: SchemaAnalyzer.analyze - Starting schema analysis
10:50:04.329 CONFIG: Dot.<init> - Version: "dot - graphviz version 2.26.3 (20100126.1600)"
10:50:04.342 INFO: Dot.supportsRenderer - Failed to validate png renderer ':cairo'. Reverting to default renderer for png.
10:50:04.358 CONFIG: ConnectionURLBuilder.<init> - connectionURL: jdbc:mysql: <path>
10:50:04.359 INFO: SchemaAnalyzer.getConnection - Using database properties:
10:50:04.359 INFO: SchemaAnalyzer.getConnection - [ <path>
10:50:04.518 FINE: SchemaAnalyzer.analyze - supportsSchemasInTableDefinitions: false
10:50:04.518 FINE: SchemaAnalyzer.analyze - supportsCatalogsInTableDefinitions: true
10:50:04.518 FINE: SchemaAnalyzer.analyze - catalog not specified for a database that requires one. using dbName: 'test_pet'
10:50:04.520 INFO: SchemaAnalyzer.analyze - Connected to MySQL - 5.0.90
10:50:04.520 INFO: SchemaAnalyzer.analyze - Gathering schema details
10:50:04.528 FINE: Database.prepareStatement - select table_schema as table_catalog, null as table_schema, table_name, table_comment, table_rows from information_schema.tables where table_schema=? and table_type='BASE TABLE'
10:50:04.541 FINE: Database$NameValidator.isValid - Including table pet_table: matches inclusion pattern ".*"
10:50:04.543 FINE: Table.<init> - Creating Table test_pet.pet_table
10:50:04.559 FINE: Table.initPrimaryKeys - Querying primary keys for test_pet.pet_table
10:50:04.561 FINE: Database$TableCreator.createImpl - Retrieved details of test_pet.pet_table
10:50:04.562 FINE: Database.prepareStatement - select table_schema as view_catalog, null as view_schema, table_name as view_name, view_definition, null as view_comment from information_schema.views where table_schema=?
10:50:04.566 FINE: Database.prepareStatement - select table_name, column_name, replace(column_type,"','","', '") as column_type, left(column_type, locate("(", column_type)-1) as short_column_type from information_schema.columns where table_schema=? and (column_type like 'enum(%' or column_type like 'set(%')
10:50:04.569 FINER: Table.connectForeignKeys - Connecting foreign keys to test_pet.pet_table
10:50:04.571 FINER: ForeignKeyConstraint.<init> - Adding foreign key constraint 'pet_table_ibfk_1' to test_pet.pet_table
10:50:04.572 FINE: Table.addForeignKey - Adding remote table test_parent.null.parent_table
10:50:04.572 FINE: Database.addRemoteTable - Creating remote table test_parent.null.parent_table
10:50:04.572 FINE: Table.<init> - Creating RemoteTable test_parent.parent_table
10:50:04.574 FINE: Table.initPrimaryKeys - Querying primary keys for test_parent.parent_table
10:50:04.575 FINE: Database.addRemoteTable - Adding remote table test_parent.null.parent_table
10:50:04.576 FINER: ForeignKeyConstraint.<init> - Adding foreign key constraint 'pet_table_ibfk_2' to test_pet.pet_table
10:50:04.576 FINE: Table.addForeignKey - Adding remote table test_child.null.child_table
10:50:04.576 FINE: Database.addRemoteTable - Creating remote table test_child.null.child_table
10:50:04.576 FINE: Table.<init> - Creating RemoteTable test_child.child_table
10:50:04.578 FINE: Table.initPrimaryKeys - Querying primary keys for test_child.child_table
10:50:04.579 FINE: Database.addRemoteTable - Adding remote table test_child.null.child_table
java.lang.NullPointerException
at net.sourceforge.schemaspy.model.RemoteTable.connectForeignKeys(RemoteTable.java:60)
at net.sourceforge.schemaspy.model.Database.addRemoteTable(Database.java:797)
at net.sourceforge.schemaspy.model.Table.addForeignKey(Table.java:220)
at net.sourceforge.schemaspy.model.Table.connectForeignKeys(Table.java:124)
at net.sourceforge.schemaspy.model.Database.connectTables(Database.java:1029)
at net.sourceforge.schemaspy.model.Database.<init>(Database.java:87)
at net.sourceforge.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:224)
at net.sourceforge.schemaspy.Main.main(Main.java:45)
Failed to execute this process (rc 1): java -Doneofmultipleschemas=true -jar <path> -ahic -dp " <path>" -css "schemaSpy.css" -charset "ISO-8859-1" -font "Courier" -fontsize "11" -t "mysql" -renderer ":gd" -p "<pass>" -u "<user>" -host "<host>" -loglevel "finest" -sqlFormatter "net.sourceforge.schemaspy.view.DefaultSqlFormatter" -dbthreads "2147483647" -maxdet "300" -db test_pet -o <path>
That one came from trying to deal with the possible combinations of databases that have no schemas and/or no catalogs. From the SQL perspective MySQL has schemas and no catalogs, but from the JDBC perspective it has catalogs but no schemas. It hurts.
Can you try the latest beta? It should resolve the issue.
Note that MySQL only provides cross-schema metadata in one direction. If you want a fully cross-linked set of ER diagrams you'll have to supplement the database's metadata with XML metadata.
Indeed, that solved it both for the test case and the original problem, a triangular set of schema with probably 3 - 5 links between each.