>running today's Easel branch build of IDE.
is it 7.2 or 7.3?

>a "New Connection Wizard" pops up
it's ok if there is not enouth information for this connection and it't not yet registered in nb, as nb is not container it can't use datasource directly and use corresponding connections within the ide in my understanding.

>have no idea why it is there and where it came from
it should be important where it come from, but I'll try the tutorial to see if it's from here. if you know it's not from tutorial, it's required to know how this ds was created and what properties are set om server side, including case for symbols (see http://netbeans.org/bugzilla/show_bug.cgi?id=209073 for example, this one issue may be a duplicate).

http://netbeans.org/kb/docs/javaee/ecommerce/connect-db.html#createConnPoolDataSource

David, have you complete steps above to create datasource?
Also anyway, can you check datasource/connection pool properties from gf admin?

(In reply to comment #1)
> is it 7.2 or 7.3?

7.3 (from Easel branch)

(In reply to comment #2)
> http://netbeans.org/kb/docs/javaee/ecommerce/connect-db.html#createConnPoolDataSource
> 
> David, have you complete steps above to create datasource?

No, I have not because I just downloaded the whole project which already contains datasource definition. And in Services->Database I have connection to affablebeans which I can connect to and expand tables etc.

> Also anyway, can you check datasource/connection pool properties from gf admin?

Sorry, one thing I have not said is that the demo app itself runs fine. It is only when I decided to create a new fresh Web App and generate new entity classes from the affablebean db that I bumped into the problems I'm describing in this issue.

> Also anyway, can you check datasource/connection pool properties from gf admin?

It looks all OK.

it may mean gf just have more information or/and use another properties.
will try to download this project.
it's anyway may be useful to get what properties exists on gf side for the datasource.

There are several snapshots for this tutorial, which one was used prior to fail in entities from db?

It's valid to create not valid pool.
also it's valid to create pools/datasources different ways, 
so either configuration isn't valid or all properties from these connections are required in order to evaluate the issue, but also steps to create pool/datasource may be required to get root cause of creation corrupted ds.

can't reproduce the issue, tried next:
setup mysql
create mysql connection to fresh affablebean db
dowload snapshot4 for the tutorial
deploy (but have to change persistence.xml to 'Create' mode) 
all works fine

create new web project and select jdbc/affablebean 
and have connection established successfully and table list is filled.

there is no way to evaluate it without requested details.

Sorry for delay in my answer. I tried few things and it does not work so here are my exact steps.
- start today's trunk build with fresh userdir

Product Version: NetBeans IDE Dev (Build 20120905-f70331372981)
Java: 1.7.0_07; Java HotSpot(TM) 64-Bit Server VM 23.3-b01
System: Linux version 3.0.0-24-generic running on amd64; UTF-8; en_NZ (nb)
User directory: /home/david/.netbeans/_UD4
Cache directory: /home/david/.netbeans/_UD4/var/cache

- open Services
- right Database -> New Connection
- MySQL Driver
- set databse to "affablebean" and password to my password and tick remember pwd
- Next

Error #1:

I got "Cannot establish a connection to jdbc:mysql://localhost:3306/mysql?zeroDateTimeBehavior=convertToNull using com.mysql.jdbc.Driver (Access denied for user 'root'@'localhost' (using password: NO))". See attached screenhost. Which is weird - notice that url contains "mysql" as database name despite JDBC URL containing "affablebean".

- I try "Test Connection" instead.

I got the same "cannot establish..." but at least this time the URL is correct one.

- Cancel wizard
- right click "MYSQL Server (disconnected)" and do Connect
- got some big dialog about "connection failed and maybe your settings are not correct"
- Yes update my settings
- set password and ticked save password again

Now it looks like connection was established. Let's try to make new connection again:

- right Database -> New Connection
- MySQL Driver
- set databse to "affablebean" and password (AGAIN empty!) to my password and tick remember pwd
- Next

The same Error #1 with wrong URL

- Let's try Test COnnection instead
- Connection Succeeded!!!!
- Next, Next, Finish

OK, now I have connection. Let's open the AffableBeans project and 

Error #2 - it still has "Resolve Data Source Problem".

The fact is that most likely I can deploy and run AffableBean project without problems but why the hell is the project marked as datasource is missing??!

I'm trying to create a new Web Project instead and after it is created I:

- right click project -> New -> "Entity Ccasses from DB"
- datasource jdbc/affablebean (!!!!!!! so the data source is known!)
- dialog "New Connectyion Wizard" pops up
- I'm steady and patient and enter "affablebean" db and password it remember this time
- Next, Finish

Error #3 - I got dialog "Unable to add connection. Connection already exists".

I think the root cause for my problems is that datasource content cannot be enumerated or something and IDE interpret it as missing connection and therefore:
- marks one project as "missing datasource"
- in fresh new project it forces me to re-create a connection which already exists

#1 may need to be filed against db module

it may be the root cause of later problems also, and I'm not sure it should be P1.

#2
>The fact is that most likely I can deploy and run AffableBean project without
problems but why the hell is the project marked as datasource is missing??!

not an issue in my opinion, gf know about datasource and can work with it and nb do not know proper connection binding and complain.

still there are no requested information which one tutorial project was downloaded(snapshot?link? there are a lot of snapshots) and how ds was created.

I can reproduce #1 with "mysql" name but it's another issue.
Steps are not complete as I can't " right click "MYSQL Server (disconnected)" and do Connect" without registration of mysql if fresh userdir is used but it may be minor.
no steps regarding server registration etc, to process with #2 it should be registred and datasource should be somehow created, still the same infirmation is missed as before.


also "resolve datasource" is web project feature and I can't resolve missed datasource neither with already created connection nor with connection created with resolve datasource dialog.

the issue may be a duplicate of 204722 but can't be evaluated without more details

summary(required for evaluation):
- please provide a link to project distribution
- please provide a way to create datasource on server side to reproduce the issue, may be all datasource properties will be enough if you don't remember how it was created (with what snapshot/application deployment etc)

will try to evaluate, will try o find why matching do not work properly as now I can reproduce, may be it's related with easel integration

workaround is to remove zeroDateTimeBehavior=convertToNull from connection url (when connection is created).
other question if url should be considered as equal with and without this parameter.

if consider equal:
org.netbeans.modules.j2ee.common.ui.BrokenDatasourceSupport  matchURL need to be updated for broken datasource report

org.netbeans.modules.j2ee.persistence.wizard.fromdb.DatabaseTablesPanel findDatabaseConnections to have proper connection from persistence wizards

org.netbeans.api.db.explorer.ConnectionManager may be affected, at least showAddConnectionDialogFromEventThread have url parameter and persistence wizard pass url without any "zeroDateTimeBehavior=convertToNull" and it may be an issue to add it by default in this case.

and may be few more places.

We need to discuss if the only require fix is in org.netbeans.api.db.explorer.ConnectionManager.

one more point - if fix only connection dialog from showAddConnectionDialogFromEventThread  but keep "zeroDateTimeBehavior=convertToNull" by default in "new connection" action on services tab it will affect tutorial (including completed projects)

my opinion: urls with and without parametes shouldn't be considered as equal neither by broken datasource support nor by persistence as it affect table-classes matching.
the issue itsef is tutorial (ds in sun-resources.xml should be updated to new default url or some other fix) or/and db|code issue (showAddConnectionDialogFromEventThread  should respect passed url and do not modify it and new default additional parameter may be roll backed)

(In reply to comment #9)
> #1 may need to be filed against db module

could you file the issue please? 

> it may be the root cause of later problems also, and I'm not sure it should be
> P1.

I marked this as P1 as so far I was not able to use affablebean MySQL DB in IDE. But I do not care much if it is P1 or P2 as long as it gets fixed.

> #2
> >The fact is that most likely I can deploy and run AffableBean project without
> problems but why the hell is the project marked as datasource is missing??!
> 
> not an issue in my opinion, gf know about datasource and can work with it and
> nb do not know proper connection binding and complain.

I agree that this is not an issue but it is frustrating that project keeps complaining about something what is actually not a problem. I think we've had this discussion in the past once. The "Datasource missing" warning should be only shown if:

* project uses some datasource; and
* that datasource is missing in target server; and
* project does not contains server specific deployment file to initialize datasource on server upon project deployment


Would you agree that make sense? Because it is weird that project complains about something yet deployment works. 

> still there are no requested information which one tutorial project was
> downloaded(snapshot?link? there are a lot of snapshots)

I though there was only one version of the project: one from the Conclusion page of the tutorial: http://netbeans.org/projects/samples/downloads/download/Samples%252FJavaEE%252Fecommerce%252FAffableBean_complete.zip


>  and how ds was created.
> 

I do not know what you mean by that - I listed for you all the steps which I did. The datasoruce is created upon application deployment because of sun-resources.xml which defines it.

> I can reproduce #1 with "mysql" name but it's another issue.

Could you file it please.

> Steps are not complete as I can't " right click "MYSQL Server (disconnected)"
> and do Connect" without registration of mysql if fresh userdir is used

I guess that on Ubuntu this happens automatically as I just have the MYSQL in the IDE.

> but it
> may be minor.
> no steps regarding server registration etc, to process with #2 it should be
> registred and datasource should be somehow created, still the same infirmation
> is missed as before.
> 
> 
> also "resolve datasource" is web project feature and I can't resolve missed
> datasource neither with already created connection nor with connection created
> with resolve datasource dialog.

Could you just fix this please in Web Project. Or file an issue for it.

(In reply to comment #14)
> my opinion: urls with and without parametes shouldn't be considered as equal
> neither by broken datasource support nor by persistence as it affect
> table-classes matching.

I do not know how the mapping from jndi-name to db-connection is done but we could ask user "which connection do you want to use for jdbc/affablebean" and they could pick one connection. Just brainstorming here. I'm not sure what's the best approach. I thought this all has been working fine until now. :-)

Re. matching - if we want to do exact matching then we should somehow indicate it to user so that they can create right connection. If in this case I was told "You need to create connection 'jdbc:mysql://localhost:3306/affablebean'. None of the existing connections are matching that one, especially not the one 'jdbc:mysql://localhost:3306/affablebean?zeroDateTimeBehavior=convertToNull'" then it would be clear to me.

On the other hand this "?zeroDateTimeBehavior=convertToNull" sounds like something what we should not generate at all. It looks like a workaround for some particular case which users can setup in runtime GF if they need it but IDE should not make are configurations changes like that. IMO.


> the issue itsef is tutorial (ds in sun-resources.xml should be updated to new
> default url or some other fix) or/and db|code issue
> (showAddConnectionDialogFromEventThread  should respect passed url and do not
> modify it and new default additional parameter may be roll backed)

But any users with existing app is affected by this and most of them would answer this request like this: "Why? Everything has been working fine. Why should I change it just to make NetBeans IDE usable?"

>Because it is weird that project complains
about something yet deployment works. 

most warnings in code cause no execution problems but keep user attention, in this case project complain as it may be the problem in persistence wizards at least but may be more ideas why it was implemented

> that datasource is missing in target server; and
it's not a problem as if it's missed on server but exist in a project it will e created, it may be problem if it exists in server and in a project but content do not match

>I do not know how the mapping from jndi-name to db-connection
I would like to thing gf will not add anything extra to connection url as two different jndi may use different connection urls with different parameters expected by user, the same should be in nb if connection without paramters is missed you need to add connection without parameters to use in persistence wizards.

>"Why? Everything has been working fine.
id db funtionality is changed tutorial have to be changed

reassign to db for evaluation of:
-existence of new parameter
-not respecting url in showAddConnectionDialogFromEventThread (if I miss something in usage I need new api which will respect url and will not add anything on its own)

>then it would be clear to me.
absence of this hint was not a problem before because url was shown initially in new connecton dialog and in my opinion it should be reverted or new wayto use db api should be added.
there is also mysql db is shown and it may be the problem similar with wrong connectio usage on "Next" if used before "Test connection".

217969 for new connection dialog wrong db in connection issue

> reassign to db for evaluation of:
> - existence of new parameter
I think that the new parameter is useful, because it make MySQL JDBC driver handle date values as expected, see bug 183440.

I also think that URLs with and without the parameter should not be considered equal, as they can produce different data.

> - not respecting url in showAddConnectionDialogFromEventThread (if I miss
>   something in usage I need new api which will respect url and will not add
>   anything on its own)
Fixed in http://hg.netbeans.org/core-main/rev/3243088ca641
I tested "Resolve Data Source Problem..." and it worked fine for me now.
Please verify.

Integrated into 'main-golden', will be available in build *201209080001* on http://bits.netbeans.org/dev/nightly/ (upload may still be in progress)
Changeset: http://hg.netbeans.org/main-golden/rev/3243088ca641
User: Jaroslav Havlin <jhavlin@netbeans.org>
Log: #217432:  Use predefined MySQL database URL in New Connection dialog

(In reply to comment #20)
> > reassign to db for evaluation of:
> > - existence of new parameter
> I think that the new parameter is useful, because it make MySQL JDBC driver
> handle date values as expected, see bug 183440.

I think the fix for issue 183440 is not right. The fix would make sense if the connection was private and had only one client who wants to handle Dates in a certain way. But the connection from the Services panel is a global IDE one and should be usable in many different contexts, like for example Web Application context where we need a connection without "zeroDateTimeBehavior" so that it behaves the same way as a connection in the runtime application server. If you disagree that suggest how to fix the current problem where Web Application needs connection with zeroDateTimeBehavior.

I'm reopening as only first part of the problem was fixed (thanks for that, the diff make sense and the issue should be fixed). The second part, that is matching of URLs, is the root cause of the problems described here and which make Web/EE functionality unusable.

What's the problem with url matching?
Different urls shouldn't be considered as equal.

Connection created to MySQL should match one expected by Web/EE projects. That's how it used to work before zeroDateTimeBehavior was automatically added to the connection URL. But if you have other idea how to resolve this issue then just fix it. All I care about is that this works. And so far it does not.

there is no predefined " expected by Web/EE projects" if connection was created with a parameter before it will be expected later to have a parameter but if it was created without any parameters it will be expected to have it without parameters later, only defaults are changed in 7.3.
And if there is enough reasons(it make MySQL JDBC driver
handle date values as expected) to have new defaults it require to say tutorial suitable for 6.8/6.9 should be updated for 7.3 and it's the only problem.

> I think the fix for issue 183440 is not right. The fix would make sense if the
> connection was private and had only one client who wants to handle Dates in a
> certain way.
I know the fix is not optimal, it caused some controversy. The current solution was recommended by experienced database expert and respected NetBeans contributor Matthias42.

> If you  disagree that suggest how to fix the current problem where Web
> Application  needs connection with zeroDateTimeBehavior.
What about adding some method for soft-comparing of JDBC URLs?
I think it wold be enough to have a private implementation in the JPA module.
What's your opinion?

> The second part, that is matching of URLs, is the root cause of the problems 
> described here and which  make Web/EE functionality unusable.
At worst the existing connection is not found and another one (without the parameter) is created. It is not perfect, but usable, I think.

Some other thoughts:

Adding zeroDateTimeBehavior parameter by default can be quite educational. Users will probably notice the parameter, and they can research what it is good for and possibly use it in their own applications.

Probably the behavior with zeroDateTimeBehavior=convertToNull is more semantically correct. Date 0000-00-00 should be returned as null in Java, as it means undefined date. Throwing an exception is not probably expected.

Shouldn't server-side data resources use this parameter too? It would be more safe if some zero-date values (0000-00-00) may appear in the database.

In some cases you cannot have the same connection URLs on the server and on the developer's machine (real database is not accessible directly, but you a local database with the same structure). Comparing of URLs will not work and "Missing data source problem" waring will be shown forever. Wouldn't be some "Mark as resolved" action useful?

(In reply to comment #25)
> And if there is enough reasons(it make MySQL JDBC driver
> handle date values as expected) to have new defaults it require to say tutorial
> suitable for 6.8/6.9 should be updated for 7.3 and it's the only problem.
I agree, updating the tutorial would be a clean and correct solution.

(In reply to comment #26)

> Some other thoughts:
> 
> Adding zeroDateTimeBehavior parameter by default can be quite educational.
> Users will probably notice the parameter, and they can research what it is good
> for and possibly use it in their own applications.
> 

So if a user creates a connection to a MySQL db, will the name of the db connection always contain ?zeroDateTimeBehavior=convertToNull from now on?
Does this only apply when creating a connection to an existing db?

Eg.
jdbc:mysql://localhost:3306/affablebean?zeroDateTimeBehavior=convertToNull [ken on Default schema]

If I create a MySQL db in the Services window using the Create Database dialog the db connection is also created, eg jdbc:mysql://localhost:3306/mytestdb [ken on Default schema] but there is no zeroDateTimeBehavior=convertToNull in the URL. Should it be there also?

(In reply to comment #26)
> What about adding some method for soft-comparing of JDBC URLs?
> I think it wold be enough to have a private implementation in the JPA module.
> What's your opinion?

This make sense to me. We know the semantic of zeroDateTimeBehavior and we know that wizards like "JPA entities from DB" are not impacted by it because the wizard does not care about data but only about table structure.

> > The second part, that is matching of URLs, is the root cause of the
> > problems 
> > described here and which  make Web/EE functionality unusable.
> At worst the existing connection is not found and another one (without the
> parameter) is created. It is not perfect, but usable, I think.

Well, if that was the case I would be OK with it. But it is not. In comment #8 when I was trying to do this I had no idea that I have to remove "zeroDateTimeBehavior" param from the URL - I was just following the wizard and tried to add a new connection but the final outcome was the connection with the zeroDateTimeBehavior again.

> 
> Some other thoughts:
> 
> Adding zeroDateTimeBehavior parameter by default can be quite educational.
> Users will probably notice the parameter, and they can research what it is
> good for and possibly use it in their own applications.
> 
> Probably the behavior with zeroDateTimeBehavior=convertToNull is more
> semantically correct. Date 0000-00-00 should be returned as null in Java, as
> it means undefined date. Throwing an exception is not probably expected.

I agree. I read more about zeroDateTimeBehavior and I must say it does make sense to suggest to users to use it by default.

> In some cases you cannot have the same connection URLs on the server and on 
> developer's machine (real database is not accessible directly, but you a local
> database with the same structure). Comparing of URLs will not work and 
> "Missing data source problem" waring will be shown forever. Wouldn't be some 
> "Mark as resolved" action useful?

I agree, this make sense too. But that's not how it is currently implemented.

(In reply to comment #27)
> (In reply to comment #25)
> > And if there is enough reasons(it make MySQL JDBC driver
> > handle date values as expected) to have new defaults it require to say 
> > tutorial suitable for 6.8/6.9 should be updated for 7.3 and it's the only
> > problem.
> I agree, updating the tutorial would be a clean and correct solution.

Generally speaking, I always disagree with answer "user should update his source code files to make NetBeans IDE happy". That's just not OK IMO.

In the instance of this database user can say that date/time columns in his DB schema are all defined as "TIMESTAMP NOT NULL" and therefore there is no need for zeroDateTimeBehavior - the problem will simply never happen.

Other issues with this approach is that this problem is not limited to this tutorial. The same problem will happen with any other MySQL DB connection and user's existing application.

>wizard does not care about data but only about table structure.

even if this one have no impact on structure but some parameters may affect structure also, may be YEAR type mapping to Date or int(not sure it's real life example) or some other

in real mysql YEAR is either Short or Date but not sure yearIsDateType can be set as part of jdbc url.

> I had no idea that I have to remove
"zeroDateTimeBehavior" param from the URL - I was just following the wizard and
tried to add a new connection but the final outcome was the connection with the
zeroDateTimeBehavior again.

I thought it was fixed and resolve datasource should create proper matching connection now.

Also world is not limited to mysql, for other databases parameters affect authorization and may be other options and just choose another similar may not be good. We are not speaking about "netbeans should be happy" we are about moving forward as at some point default new jpa provider was changed to jpa 2.0 and eclipselink and old tutorials was required to be updated, and it was considered as improvement, default source level was updated to 1.6 and user have to make some extra clicks to support old projects. But I can add soft comparing to jpa wizard and check strict match as with priority and fall into main part of url matching if no strict match is found, just worry it may have some side effect hard to track.
Currently both places 'broken datasource' and 'japa wizard' each use own implementation to get db connection from datasource data as I know and do not use any db api.

after a bit of reading in mysql doc there is also  tinyInt1isBit  property and both with year property can be set with jdbc url.

(In reply to comment #33)
> I thought it was fixed and resolve datasource should create proper matching
> connection now.

What make you think so? The only commit I've seen changes the wrong db name in the URL.

> Also world is not limited to mysql, for other databases parameters affect
> authorization and may be other options and just choose another similar may not
> be good. We are not speaking about "netbeans should be happy" we are about
> moving forward as at some point default new jpa provider was changed to jpa 2.0
> and eclipselink and old tutorials was required to be updated, and it was
> considered as improvement, default source level was updated to 1.6 and user
> have to make some extra clicks to support old projects. But I can add soft
> comparing to jpa wizard and check strict match as with priority and fall into
> main part of url matching if no strict match is found, just worry it may have
> some side effect hard to track.

I would implement the check to ignore only "zeroDateTimeBehavior=convertToNull" param in the URL. Everything else should match.

(In reply to comment #28)
> If I create a MySQL db in the Services window using the Create Database
> dialog the db connection is also created, eg
> jdbc:mysql://localhost:3306/mytestdb [ken
> on Default schema] but there is no zeroDateTimeBehavior=convertToNull in
> the URL. Should it be there also?
This is a bug. I will fix it. Thank you for catching it.

(In reply to comment #29)
> Well, if that was the case I would be OK with it. But it is not.
> In comment #8 when I was trying to do this I had no idea that I have to
> remove "zeroDateTimeBehavior" param from the URL - I was just following the
> wizard and tried to add a new connection but the final outcome was
> the connection with the zeroDateTimeBehavior again.
(and in reply to comment #35)
> > I thought it was fixed and resolve datasource should create proper
> > matching connection now.
> What make you think so? The only commit I've seen changes the wrong db
> name in the URL.
Not only the database name, but the whole URL is changed. It should work fine now. It worked so when I tested it, but I would appreciate some verification.

> I would implement the check to ignore only 
> "zeroDateTimeBehavior=convertToNull"
> param in the URL. Everything else should match.
I would prefer some more generic solution, or maybe rather nothing. It may be beneficial to force creating connection with exactly the same parameters.

Example situation: NetBeans have connection with the extra parameter, web application have connection URL without the parameter. Exploring the database from the IDE works fine, and using the same data from the web application throws some exception. User does not notice that the URLs are different and spend quite a lot of time debugging the web application (the exception message will not be probably very helpful), assuming the data are correct.
It the exception is thrown form the IDE, incorrect data are detected earlier and diagnosing the problem may be simpler.

Having two versions of the connection may be better than hiding the fact that IDE uses connection URL different to the one used in the web application.
I'm not sure about this, but maybe it's worth considering.

(In reply to comment #36)
> > If I create a MySQL db in the Services window using the Create Database
> > dialog the db connection is also created, eg
> > jdbc:mysql://localhost:3306/mytestdb [ken on Default schema] but there
> > is no zeroDateTimeBehavior=convertToNull in
> > the URL. Should it be there also?
> This is a bug. I will fix it. Thank you for catching it.
Fixed: http://hg.netbeans.org/core-main/rev/bffa77583dc0
Thanks again.

I can verify that now everything works just as expected. Thanks!

*** Bug 221076 has been marked as a duplicate of this bug. ***

I could reproduce the same issue in Netbeans ide 8.0.2.

I created Web Project, and started with "New Restful Webservices from Database" wizard and data sources combo lists all the existing data source, I selected "jdbc/mysqldatasource" which I created in GF admin console, despite it being an existing connection suddenly a "New Connection Wizard" pops up without host and database name value.Why it is not using the datasource value which I entered in GF admin console?. So I enter the DB and host and click next and in the choose database wizard I don't see the schema selected.Why? when I click next 
I see url - "jdbc:mysql://localhost:3306/mydb [root on Default schema]"
[root on Default schema] is the new parameter added to the url.
I deleted the new parameter and clicked finish.



I'm back in "New Restful Webservices from database" wizard and available tables list is empty. There is a warning which says "Select the table source" .I have no idea why it behaves like this? If I go through the process of add connection again I get "connection already exists" error.

pbuser@netbeans.org please file a new bug for this - you high jacked a bug that was closed three years ago and was verified at that time. Your problem might be similar, but even so, please just reference this bug in the text.

Thank you.

As a lead for you: only datasource based on java.sql.Driver are supported (if I remember correctly), so if your datasource a javax.sql.Datasource of even a XADatasource, this won't work.