Bug 146940 - New Entity Classes from Database bug
New Entity Classes from Database bug
Status: RESOLVED FIXED
Product: db
Classification: Unclassified
Component: MySQL
6.x
PC All
: P3 with 2 votes (vote)
: 7.0.1
Assigned To: Jiri Rechtacek
issues@db
:
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2008-09-11 13:29 UTC by leifinge
Modified: 2011-07-14 06:18 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
:


Attachments
Test script (1.32 KB, text/plain)
2008-09-15 08:39 UTC, leifinge
Details

Note You need to log in before you can comment on or make changes to this bug.
Description leifinge 2008-09-11 13:29:36 UTC
If MySQL is configured with case sensitive table names, NetBeans doesn't find the primary key in all tables and thus
says "<table> cannot be added because it does not have a primary key". If I revert to MySQL's default with lowercase
table names, all tables are available for selection.

I haven't found any particular property of the "no primary key" tables that causes the problem. In my case I have 40
tables, 17 of which are ok for NetBeans. All 40 tables are of InnoDB type and have one or more integer fields as the
primary key. I've tried using versions 5 and 6 of mysql-connector-java-5.1.X-bin.jar; same result.
Comment 1 leifinge 2008-09-12 12:38:34 UTC
Some investigation reveals that the problem occurs for tables having a foreign key (which means pretty much all
important tables...). Also, if I create a database schema, it seems to work. But when using that schema to create entity
classes, the return values from functions are of type short instead of the entity type.

One workaround is to set MySQL to work with case insensitive table names (i.e. the default configuration). Then after
creating entity classes you can correct the case manually.
Comment 2 Dongmei Cao 2008-09-12 18:47:05 UTC
Can you attach the SQL script for me to reproduce the problem? Thanks.
Comment 3 leifinge 2008-09-15 08:39:19 UTC
Created attachment 69843 [details]
Test script
Comment 4 leifinge 2008-09-15 08:46:18 UTC
Here are the detailed steps I performed

1. In MySQL command line:
  create database nbtest;
  source <path>\nbtest.sql

2. In NetBeans, create a datasource to the nbtest database

3. In NetBeans, create New Entity Classes from Database. When I select the 'nbtest' db, the Job table is listed in grey
like "Job (no primary key)", while JobType is listed in black and is available for selection.

This happens when I've put the following line in my.ini under section [mysqld]:
  lower_case_table_names=0

Without this line (which would default to lower_case_table_names=1, I think) both tables are listed.
Comment 5 leifinge 2008-09-15 08:49:00 UTC
Sorry, I couldn't correct the error in my previous post. Of course I did "source <path>\nberror.sql" in step 1.
Comment 6 Dongmei Cao 2008-09-15 20:01:58 UTC
Thanks for the information.I tried to reproduce it on my Windows XP. It worked just fine. Wondering if it is Vista related. 
Comment 7 Dongmei Cao 2008-09-15 22:30:22 UTC
Honza, can you try to reproduce this on Vista? Thanks.
Comment 8 leifinge 2008-09-29 08:05:51 UTC
In case it helps, I just found a strange thing. First, the problem seems not to be connected to the database itself, or
at least it happens both when I in NetBeans connect to database 1 and 2 below:

1. DB resides on a server running Windows Server 2003 R2 Standard Edition (English language). MySQL 5.0.45. Pentium 2.4
GHz with 1GB RAM.

2. DB resides on a local PC running Windows Vista Business Service Pack 1 (Norwegian Language). MySQL 6.0.4 alpha. Intel
Core 2 Quad CPU 2.4GHz, 4GB RAM.

In NetBeans under the Services tab I connect to the DB and verify each table looks right with the primary key column
marked with a red bar instead of a blue bar. The foreign keys are also listed as expected.

A strange thing, though; when I expand the Foreign keys folder for the Job table and expand the fkJobTypeID, it says
"TypeID -> jobtype.ID". I.e. NetBeans uses lowercase for the table name in the foreign key definition, even if MySQL is
configured to be case sensitive.

Can this be the source of the error?
Comment 9 leifinge 2008-09-29 09:48:52 UTC
There is an old MySQL bug on this (http://bugs.mysql.com/bug.php?id=6555). I guess this causes NetBeans to fail
importing the tables. However, NetBeans should still find the primary key and let me import the Job table as well, no
matter if there is an error in the foreign key definition?
Comment 10 Dongmei Cao 2008-10-08 00:03:02 UTC
Thanks for all the info! 
Reassign to the db module for an evaluation on the case sensitive issue. Looks like that is the cause of the Entity
Classes from Database wizard not working properly.
Comment 11 David Vancouvering 2008-10-08 01:27:21 UTC
Hi, leifinge.  How acceptable is the workaround to work with case insensitive table names?  Are you able to work with
that, or are you stuck?  I am trying to get a sense of the priority of this issue.

Thanks!

David
Comment 12 leifinge 2008-10-09 09:13:32 UTC
The workaround works and is acceptable for now. The bug is a bit cumbersome, causing some manual work, but no
show-stopper :-) We also have some alternatives that I'm working on (e.g. use PostgreSQL instead of MySQL; put the
database on Linux instead of Windows). I'd rather not change our naming standard from mixed case to lowercase table names.

Migrating the tables from MySQL to PostgreSQL reveals the problem as well; all foreign key constraints are "not found"
because they are given as lowercase from MySQL while they in reality have mixed case. The PostgreSQL migration tool does
import the tables, though, only without the foreign keys, which I have to add manually after migration.
Comment 13 Jiri Rechtacek 2009-02-10 15:39:06 UTC
Reassigned to new owner.
Comment 14 Jan Lahoda 2010-07-01 13:02:25 UTC
Reverting recent changes to bug metadata, which no reason was given.
Comment 15 __geo__ 2011-01-05 15:25:33 UTC
I also had the same problem while connecting directly to the MySQL but it went through without any problem when I generated a DB schema first and generated the entity beans from the schema
Comment 16 Jiri Rechtacek 2011-04-11 09:09:38 UTC
These changesets could fix this problem:
core-main/rev/db9e81980ae7
core-main/rev/3e701ed2b767
Comment 17 Quality Engineering 2011-04-12 08:42:55 UTC
Integrated into 'main-golden', will be available in build *201104120401* on http://bits.netbeans.org/dev/nightly/ (upload may still be in progress)
Changeset: http://hg.netbeans.org/main/rev/3e701ed2b767
User: Jiri Rechtacek <jrechtacek@netbeans.org>
Log: #146940: New Entity Classes from Database bug
Comment 18 impe 2011-07-13 23:45:41 UTC
I'm having the same issue on Mac, Neteans 7.0 and Mysql 5.1.44
The case sensitive/case insensitive does not work.
I got an additional (no primary key) issue when I added some foreign keys to a table.
Before it worked fine. After it was not able to see the primary key on the table

D.
Comment 19 Jiri Rechtacek 2011-07-14 06:18:07 UTC
(In reply to comment #18)
> I'm having the same issue on Mac, Neteans 7.0 and Mysql 5.1.44
> The case sensitive/case insensitive does not work.
> I got an additional (no primary key) issue when I added some foreign keys to a
> table.
> Before it worked fine. After it was not able to see the primary key on the
> table
> 
> D.

impe, don't reopen this bug which was fixed _after 7.0_ due to you found it in 7.0. Also don't change Version, it's important to hold it to know in which release the bug appear at first.


By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2014, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo