WbProfiles.xml max size?

[Wes Howard]

Created a little python script to generate my WbProfiles.xml file. After some troubleshooting I think I may be exceeding some file size or record count limit. Is there a cap on either of these with this file before I spend too much more time stepping through 5k plus connections?

As fare as troubleshooting goes, I have a high degree of confidence in the format since this same script has worked on smaller files of 5-12 connection profiles routinely for some time. It is possible that something in one of the string fields I am grabbing  is causing a problem, but before i go through 5-6k of those I want to eliminate this first.

[Thomas Kellerer]

Hi,

the file is written and read using standard Java classes (XMLEncoder and XMLDecoder).

The result is a standard Java ArrayList which should be able to handle 6k of entries just fine.

What kind of problems did you run to exactly? Can you be more specific?
(You can also send me the generated file to the support email address, and I have a look)

I wouldn't be surprised if loading the file takes a lot of time, due to the overhead of the XML parser.

Regards
Thomas

[Wes Howard]

When starting SqlWB with the file in place it is not even parsed (empty connections profiles just like a new install). Take it into a text editor and delete all but the top dozen or so and try again and it reads them just fine. I know its possible some characters in some of the group or connection names is breaking the file, but going for the broader possibilities before I step through the whole thing.

And unfortunately the file contains a lot of info private to our business.

[Thomas Kellerer]

Hi Wes,

> When starting SqlWB with the file in place it is not even parsed
> (empty connections profiles just like a new install).

Is there any error in the workbench.log file?
Are you sure SQL Workbench picks up the right file?

Can you change the log level to DEBUG (e.g. by passing -loglevel=DEBUG on the commandline) and then send me the logfile?

The location of the logfile is shown in the About dialog.

> And unfortunately the file contains a lot of info private to our
> business.

You could just do a search and replace on the URL, username and passwords.
The resulting file will then not contain anything confidential then.

Regards
Thomas

[Wes Howard]

Did just find that an ampersand (&) in a connection name will break it when removing all but one random entry. (connection names are the business names of clients so x & y, LLC.....)

I am sure I am getting the same file (editing in place down to one good connection vs freshly generated, also modifying entries in text editor to verify we are all on the same file)

And no errors at all in the UI, but will check the log. With the flawed file in place it literally starts up like a fresh install wanting to make a new file.

from the log file (after removing 311 ampersands):

2012-11-04 05:01 ERROR Error reading file C:\Users\Administrator\SQL Workbench Old\WbProfiles.xml Invalid byte 1 of 1-byte UTF-8 sequence.

com.sun.org.apache.xerces.internal.impl.io.MalformedByteSequenceException: Invalid byte 1 of 1-byte UTF-8 sequence.

at com.sun.org.apache.xerces.internal.impl.io.UTF8Reader.invalidByte(Unknown Source)

at com.sun.org.apache.xerces.internal.impl.io.UTF8Reader.read(Unknown Source)

at com.sun.org.apache.xerces.internal.impl.XMLEntityScanner.load(Unknown Source)

at com.sun.org.apache.xerces.internal.impl.XMLEntityScanner.skipChar(Unknown Source)

at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(Unknown Source)

at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(Unknown Source)

at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)

at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)

at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)

at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(Unknown Source)

at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(Unknown Source)

at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)

at javax.xml.parsers.SAXParser.parse(Unknown Source)

at javax.xml.parsers.SAXParser.parse(Unknown Source)

at java.beans.XMLDecoder.getHandler(Unknown Source)

at java.beans.XMLDecoder.readObject(Unknown Source)

at workbench.util.WbPersistence.readObject(WbPersistence.java:87)

at workbench.util.WbPersistence.readObject(WbPersistence.java:78)

at workbench.db.ConnectionMgr.readProfiles(ConnectionMgr.java:652)

at workbench.WbManager$10.run(WbManager.java:832)

2012-11-04 05:01 ERROR Error when reading connection profiles 0

java.lang.ArrayIndexOutOfBoundsException: 0

at com.sun.beans.ObjectHandler.dequeueResult(Unknown Source)

at java.beans.XMLDecoder.readObject(Unknown Source)

at workbench.util.WbPersistence.readObject(WbPersistence.java:87)

at workbench.util.WbPersistence.readObject(WbPersistence.java:78)

at workbench.db.ConnectionMgr.readProfiles(ConnectionMgr.java:652)

at workbench.WbManager$10.run(WbManager.java:832)

2012-11-04 05:01 INFO  Stopping SQL Workbench/J, Build 110 

2012-11-04 05:01 INFO  =================== Log stopped =================== 

[Thomas Kellerer]

> Did just find that an ampersand (&) in a connection name will break
> it when removing all but one random entry. (connection names are the
> business names of clients so x & y, LLC.....)

As that is XML the ampersand must be encoded as an XML entity:

<void property="name">
<string>One &amp; The other</string>
</void>

You also need to encode other special characters this way:

& becomes &amp;
< becomes &lt;
> becomes &gt;
" becomes &quot;

> from the log file (after removing 311 ampersands): 2012-11-04 05:01
> ERROR Error reading file C:\Users\Administrator\SQL Workbench
> Old\WbProfiles.xml Invalid byte 1 of 1-byte UTF-8 sequence.
> com.sun.org.apache.xerces.internal.impl.io.MalformedByteSequenceException:
> Invalid byte 1 of 1-byte UTF-8 sequence.

Apparently the encoding of your generated XML file is not correct.
It *must* be UTF-8 without a BOM (byte order mark) at the beginning.

Regards
Thomas

[Wes Howard]

On Sunday, November 4, 2012 5:16:04 AM UTC-6, Thomas Kellerer wrote:

As that is XML the ampersand must be encoded as an XML entity: 

     <void property="name">
      <string>One &amp; The other</string>
     </void>

You also need to encode other special characters this way:

& becomes &amp;
< becomes &lt;
> becomes &gt;
" becomes &quot;

Pulling names and connection strings out of a db. Literally concatenating strings to make this. I will have to extert more control and transform the characters.

Apparently the encoding of your generated XML file is not correct.
It *must* be UTF-8 without a BOM (byte order mark) at the beginning.

Verified this early on, but I did just now find non-breaking unicode spaces in one entry (/xA0) and that was certainly an issue. Also a pilcrow (/xB6)... wth is that doing in there....

[Wes Howard]

A quick update that this is resolved. In the end it was multibyte characters and other non-xml-safe characters. As large as this was I wanted to rule size out before going through the file line by line. used some handy libs someone made available for cleaning this sort of thing up and making things ansi safe and resolved the issue.