| Bug #8496 | Database url format problem & solution | ||
|---|---|---|---|
| Submitted: | 14 Feb 2005 13:03 | Modified: | 17 Feb 2005 14:48 |
| Reporter: | Richard Tykwinski | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | Alexander Soklakov | CPU Architecture: | Any |
[14 Feb 2005 13:48]
Aleksey Kishkin
I think it must be a 'feature request', not a bug
[14 Feb 2005 15:16]
Mark Matthews
The question mark '?' is not a special character under XML, however the ampersand is. However, you just need to represent it as '&', and everything works okay. We will not change the URL format to support ';'.
[15 Feb 2005 8:51]
Richard Tykwinski
Below are the results of your concept ...and this still not working, url's with '&;' included works only directly form tools like MysqlFront not from Java.
Examples
1. xml with "mysql format" url
url="jdbc:mysql://localhost:3306/baza1250?autoReconnect=true&characterEncoding=cp1250"
result = 2005-02-14 21:04:39 org.apache.tomcat.util.digester.Digester fatalError
SEVERE: Parse Fatal Error at line 48 column 94: The reference to entity "characterEncoding" must end with the ';' delimiter.
org.xml.sax.SAXParseException: The reference to entity "characterEncoding" must end with the ';' delimiter.
2. xml with "informix etc. standard" url
url="jdbc:mysql://localhost:3306/baza1250?autoReconnect=true;characterEncoding=cp1250"/>
result= 2005-02-14 21:16:34 org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet Controller threw exception
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (The connection property 'autoReconnect' only accepts values of the form: 'true', 'false', 'yes' or 'no'. The value 'true;characterEncoding' is not in this set.)
3. xml with '&' included
url="jdbc:mysql://localhost:3306/baza1250?autoReconnect=true;&characterEncoding=cp1250"/>
result= 2005-02-14 21:20:15 org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet Controller threw exception
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (The connection property 'autoReconnect' only accepts values of the form: 'true', 'false', 'yes' or 'no'. The value 'true;' is not in this set.)
4. second xml test with '&' included
url="jdbc:mysql://localhost:3306/baza1250?autoReconnect=true&;characterEncoding=cp1250"/>
result=
For url above everything looks OK until StringTokenizer which get a string :
jdbc:mysql://localhost:3306/baza1250?autoReconnect=true&;characterEncoding=cp1250 ...at this moment everything looks fine but ...
'
NonRegisteringDriver.parseURL(
...
StringTokenizer queryParams = new StringTokenizer(paramString, "&"); //$NON-NLS-1$
while (queryParams.hasMoreTokens()) {
...
}
...
'
After this parser with 'while' urlProps are OK but few steps next and at the end of parseURL() ... urlProps give bad values, and the finish result of Connection.configureClientCharacterSet() is completely stupid ...ISO_8859_1 codepage. Finally effect is collation error latin1 to cp1250 problem.
My enviroment is :
database server - MySQL 4.1 with default-charset=cp1250
database - created with default server charset
operating system - Windows XP with default charset cp1250
Tomcat 5.5.4 on java with default charset cp1250 and -Dfile.encoding="cp1250".
For above format solution patch with 'paramString = paramString.replaceAll(";","");'
give good result and everything works well ... but url format with '&;' is nonsens.
If you don't want to add suport just remove ';' from paramString before Tokenizer
'
paramString = paramString.replaceAll(";","");
StringTokenizer queryParams = new StringTokenizer(paramString, "&"); //$NON-NLS-1$
'
What do you suggest?
[15 Feb 2005 15:07]
Mark Matthews
Please read the instructions I posted _very_ carefully, you're still mixing concepts here. Replace _only_ the & with & ... Please _do_not_ add extra semicolons at the end of configuration values or names. I assure you this works, we have thousands of people using MySQL with Tomcat, and using this exact format. Please refer to how I fix your examples: > 3. xml with '&' included > url="jdbc:mysql://localhost:3306/baza1250?autoReconnect=true;&characterEncoding=cp1250"/> > result= 2005-02-14 21:20:15 > org.apache.catalina.core.StandardWrapperValve invoke > SEVERE: Servlet.service() for servlet Controller threw exception > org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create > PoolableConnectionFactory (The connection property 'autoReconnect' only > accepts values of the form: 'true', 'false', 'yes' or 'no'. The value > 'true;' is not in this set.) jdbc:mysql://localhost:3306/baza1250?autoReconnect=true&characterEncoding=cp1250 > 4. second xml test with '&' included > url="jdbc:mysql://localhost:3306/baza1250?autoReconnect=true&;characterEncoding=cp1250"/> > result= > For url above everything looks OK until StringTokenizer which get a > string : > jdbc:mysql://localhost:3306/baza1250?autoReconnect=true&;characterEncoding=cp1250 jdbc:mysql://localhost:3306/baza1250?autoReconnect=true&characterEncoding=cp1250 Notice I've removed spurious semicolons.
[16 Feb 2005 10:24]
Richard Tykwinski
OK, your last solution is good, my mistake (later I found that mysqlfront allow
for format with &;)
Please update MySQL documentation 'doc/connector/j/en/cj-driver-classname.html' to avoid this XML problem for the future.
PS.
My sugessted fix should be great feature to avoid this problem, as you see
this fix not change default behavior of this driver.
//patch
String token;
if (paramString.indexOf("&")>0)
token = "&";
else
token = ";";
StringTokenizer queryParams = new StringTokenizer(paramString, token);
// end of patch
[16 Feb 2005 13:36]
Mark Matthews
Yes, it does change the default behavior of the driver, as now ';' becomes a special character which can not be used in other parameters (such as passwords for example).
[17 Feb 2005 9:57]
Richard Tykwinski
I agree ';' is special parameter and nobody using it in parameters and
everybody probably try to use as separator with no results.
As you wish, patch below support url's in format $1..5 and allow to omit strange & notation
$1. jdbc:mysql://localhost:3306/baza1250?autoReconnect=true
$2. jdbc:mysql://localhost:3306/baza1250?autoReconnect=true&characterEncoding=cp1250
$3. jdbc:mysql://localhost:3306/baza1250?autoReconnect=true&characterEncoding=cp1250
$6. jdbc:mysql://localhost:3306/baza1250?autoReconnect=true&characterEncoding=cp1250&user=root&password=
$4. jdbc:mysql://localhost:3306/baza1250?autoReconnect=true;characterEncoding=cp1250
$5. jdbc:mysql://localhost:3306/baza1250?autoReconnect=true;characterEncoding=cp1250;user=root;password=;
File NonRegisteringDriver.java
public Properties parseURL(String url, Properties defaults)
(
...
//line with old solution
//StringTokenizer queryParams = new StringTokenizer(paramString, "&"); //$NON-NLS-1$
//patch
String token="&"; // MySQL default separator
if (paramString.indexOf("&")>=0){ // then execute url with mySQL default '&' separators
} else if (paramString.indexOf(";")>=0) // then url is without '&' but with ';' separator
token = ";";
StringTokenizer queryParams = new StringTokenizer(paramString, token); //$NON-NLS-1$
// end of patch
...
)
[17 Feb 2005 14:48]
Mark Matthews
Richard, The words 'everybody' and 'nobody' are very strong. Your proposed change is not backwards-compatible, and has the possibility of causing issues for many, many people, and thus we can not make such a change to such core functionality. The '&' character has been the parameter separator for at least 4 years, and is 'normal' to people who do web development, as it's the same character used in HTTP URLs to separate parameters. I suggest that if you're going to be using Tomcat, then you _really_ need to get comfortable with XML, which includes entity representations such as & and " since their entire configuration system relies on XML.
[18 Feb 2005 10:23]
Richard Tykwinski
OK, if someone commit and accept "Bug 8537" there also will be no problem with '&' for Windows(Cp1250) users, beacuse using charset parameters will be not neccesery. By the end ... I don't know what is problem with my " feature with ';' " ... what is "not backward-compatible" in below lines, if '&' and ';' are reserved chars and not allowed in XML (and probably not allowed in other url declaration) to use in user or passwd parameter's ? String token="&"; // MySQL default separator if (paramString.indexOf("&")>=0){// then execute url with mySQL default '&' separators } else if (paramString.indexOf(";")>=0)// then url is without '&' but with ';' separator token = ";"; StringTokenizer queryParams = new StringTokenizer(paramString, token);
[22 Mar 2007 15:33]
Kyle Cunningham
Does anyone know if this fix (inserting & instead of &) should work for jBoss as well? I've tried it on jBoss-3.2.7 and jBoss doesn't seem to like it. I get an "incomletely deployed package" message when trying to use this url: jdbc:mysql://localhost:3306/analysis_db?zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8 Thanks
[17 Mar 2011 17:52]
anTON anTON
Продается комплект абсолютно новой летней резины на дисках,однократная бортовка, балансировка. Диски - Borbet LV5 7*16 5/110ET40 http://s50.radikal.ru/i128/1004/1d/4f9fa878222f.jpg Резина - Continental ContiPremiumContact 2 195/55 R16 87 H Цена за 4диска с резиной 35тыс.руб. Осмотр М.О.г.Красногорск Антон 89055785133 Елена 89067873140
[8 Jul 2014 11:54]
Petr Hajič
Yes, I confirm that behavior and I evaluate it as a bug. When semicolon is missing in pool, the deployment to Glassfish issues: "The reference to entity "characterEncoding" must end with the ';' delimiter." and stack trace is printed. But deployment is successful and national characters are properly saved in database. When semicolon is present, the deployment fails. When option characterEncoding=UTF-8 is missing, any national characters are saved as question marks. Testing string in sun-resources.xml can be or not can be with semicolon: "jdbc:mysql://localhost:3306/affablebean?useUnicode=yes&characterEncoding=UTF-8;&zeroDateTimeBehavior=convertToNull"
[8 Jul 2014 12:02]
Petr Hajič
Tested on Glassfish 4 and NetBeans 8.0
[8 Jul 2014 12:02]
Alexander Soklakov
Hi Petr, This is a very old report, which versions of Connector/J and Glassfish did you try?
Description: By default MySQL connector support configuration url's with '?' and '&' characters for example in format - ' url="jdbc:mysql://localhost:3306/baza1250?autoReconnect=true&characterEncoding=cp1250&characterSetResults=cp1250" ' This is OK but for XML parsers this is problem because they expect standard ';' separator and url format like - ' url="jdbc:mysql://localhost:3306/baza1250?autoReconnect=true;characterEncoding=cp1250;characterSetResults=cp1250" ' This situation is standard in Tomcat server.xml <Resource> section and give errors; How to repeat: Add in Tomcat/ server.xml file url with parameters. <GlobalNamingResources> <Resource name="jdbc/mysql_cp1250" auth="Container" type="javax.sql.DataSource" maxActive="10" maxIdle="30" maxWait="1000" username="root" password="" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/baza1250?autoReconnect=true;characterEncoding=cp1250;connectionCollation=cp1250_general_ci;characterSetResults=cp1250"/> </GlobalNamingResources> or url="jdbc:mysql://localhost:3306/baza1250?autoReconnect=true&characterEncoding=cp1250&connectionCollation=cp1250_general_ci&characterSetResults=cp1250" Both get error. Suggested fix: I found that good solution is to correct file NonRegisteringDriver.java and enhance by replace this old line below with patch //old line StringTokenizer queryParams = new StringTokenizer(paramString, "&"); //patch String token; if (paramString.indexOf("&")>0) token = "&"; else token = ";"; StringTokenizer queryParams = new StringTokenizer(paramString, token); // end of patch