The current version of MySQL wizard in the CAM Editor is covering the basic connection types only. It's not aware about specific flags and options. I would recommend to use a "Generic" connection type instead and specify all necessary parameters through JDBC connection string manually.
Regards,
Serge
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi, Serge!
I don't change params of connection, only filled out the form.
The full text with references
java.sql.SQLException:.sql.SQLException: Unable to load authentication plugin ''.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:923)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1715)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1244)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2397)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2430)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2215)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:813)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
Last edit: alex_scab 2014-07-08
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
OK. So, I would assume that you are definitely using a Generic connection and specifying the JDBC connection string, Also, make sure that you are loading proper mysql connector jar and selecting right Database Driver.
I'm not sure if this related to your case, but maybe it would help.
the issue occurs when the password set on the mysql.user table uses an older format - of the form 7f84554057dd964b (which I believe is 'badpwd') rather than something like *AAB3E285149C0135D51A520E1940DD3263DC008C which is the newer form.
Resetting the password for the user record(s) as noted in MDEV-545 does solve the issue as it upgrades the password format in the tables.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Now the connection is working with Generic JDBC driver.
I can see tables and design the structure, but when I try generate XDX, error occurs
No operations allowed after connection closed.
It is the same with SQL DB and MySQL.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
com.microsoft.sqlserver.jdbc.SQLServerException: Соединение закрыто.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:388)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:2166)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:1853)
at com.devwebsphere.jdbc.loader.NamedParameterStatement.<init>(NamedParameterStatement.java:55)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.writeDumpXML(OpenDataProcessor.java:509)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.dumpOpenDataXML(OpenDataProcessor.java:148)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.extract(OpenDataProcessor.java:130)
at uk.org.jcam.camed.ui.handlers.opendata.GenerateOpenDataHandler$2.run(GenerateOpenDataHandler.java:122)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
FOR MySQL connection
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888)
at com.mysql.jdbc.Connection.checkClosed(Connection.java:1932)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4768)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4734)
at com.devwebsphere.jdbc.loader.NamedParameterStatement.<init>(NamedParameterStatement.java:55)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.writeDumpXML(OpenDataProcessor.java:509)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.dumpOpenDataXML(OpenDataProcessor.java:148)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.extract(OpenDataProcessor.java:130)
at uk.org.jcam.camed.ui.handlers.opendata.GenerateOpenDataHandler$2.run(GenerateOpenDataHandler.java:122)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
That's interesting. It appears that connection get closed while Open-XDX is still trying to retrieve data. Is there any connection pool configured on the DB side?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Could you please try to connect to local MySQL instance and try the same routine with OpenXDX data generation. I'm suspecting that SSH tunneling may be breaking connection to DB and causing those errors.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I installed MySQL to localhost and now next Exceptions for two DataSource' types
for Generic JDBC, the same as for shared DB:
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888)
at com.mysql.jdbc.Connection.checkClosed(Connection.java:1932)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4768)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4734)
at com.devwebsphere.jdbc.loader.NamedParameterStatement.<init>(NamedParameterStatement.java:55)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.writeDumpXML(OpenDataProcessor.java:509)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.dumpOpenDataXML(OpenDataProcessor.java:148)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.extract(OpenDataProcessor.java:130)
at uk.org.jcam.camed.ui.handlers.opendata.GenerateOpenDataHandler$2.run(GenerateOpenDataHandler.java:122)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
For MySQL
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.peekChar(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)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thank you for your patience.The second error message clearly tells me that you are using non UTF-8 characters in the data that you are trying to export through OpenXDX. I can try to debug this issue if you can provide me a sample template and MySQL DB export.
Thank you!
Serge
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Serge!
I think it isn’t problem with my *.cam file.
I load the sample cam and xml from http://www.verifyxml.org/CAMV-page.html and the result was the same as with my template. Usually I use Russian Windows, can be here is problem.
I installed the Oracle Express to try connection by oracle DataSource, and Error was
"Got minus one from a read call". Full report in the file.
Now I have progress. I installed CAMed to Linux comp, connected to shared MySQL DB and created XDX file.
So, now I’ll try to create more sophisticated data structure and work with business rules. So, it is good case to understand architecture for information integration by CAM.
But I have interest to create set of Windows' components for data integration, as widely used OS.
Serge,
With direct encoding designation in Windows CAM works in one case:
connection type - MySQL; for Generic DB JDBC and for Oracle - the same Exceptions.
Last edit: alex_scab 2014-07-19
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thank you for following up on this issue. The data encoding within DB may also cause this issues. The MySQL DB uses UTF-8 by default, and I would assume if you will use UTF-8 in Oracle DB instance, you should be able to flawlessly transform your data with Open-XDX.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
It is really can be question of encoding. I have had problem with encoding by migration DB form MySQL under Windows 7 (RUS) to other MySQL instance with Windows 8 (RUS). I managed it, but non by standard MySQL methods, where standard dump file didn’t work with error in encoding. China forum discussed the same difficulties.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello,
One more question with encoding - this time not with 1251, but with IBM1252. I try to reproduce the sample of usage CAM files for data delivery by web https://github.com/VerifyXML/PMIX-Services-Demo. The problem is to read dump file – data is extracted from DB and written to the dump xml file with IBM1252 encoding. After this exception
java.io.UnsupportedEncodingException: IBM1252
Failed to load document
file: /AppData/Local/Temp/org.verifyxml.openxdx.OpenXDXDemo171316026219608853-open-xdx-dump.xml
in variable dataInputLoadXML (#175)
in variable dataRecordCount (#311)
at net.sf.saxon.instruct.GlobalVariable.getSelectValue(GlobalVariable.java:209)
at net.sf.saxon.instruct.GlobalVariable.evaluateVariable(GlobalVariable.java:233)
at net.sf.saxon.expr.VariableReference.evaluateVariable(VariableReference.java:480)
at net.sf.saxon.expr.VariableReference.iterate(VariableReference.java:433)
This type of encoding can be specified in the library open-xdx-0.1.
“Latin1” (the same as IBM1252) is clearly prescribed in the SQL file (directory DATA of this sample), where we can see “DEFAULT CHARSET=latin1”.
Simply convert the source XML document to UTF8. Like I've mentioned before, the CAM tool is expecting input in UTF8 and unfortunately due to limitation of underlying API we can't get encoding details from XML prolog.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Try to connect to MySQL DB by SSH and have the Error "Generetion open XDX' has ecountered problem
java.sql.SQLException
Unable to load authentication plugin ''.
Hello!
The current version of MySQL wizard in the CAM Editor is covering the basic connection types only. It's not aware about specific flags and options. I would recommend to use a "Generic" connection type instead and specify all necessary parameters through JDBC connection string manually.
Regards,
Serge
Hi, Serge!
I don't change params of connection, only filled out the form.
The full text with references
java.sql.SQLException:.sql.SQLException: Unable to load authentication plugin ''.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:923)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1715)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1244)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2397)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2430)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2215)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:813)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
Last edit: alex_scab 2014-07-08
OK. So, I would assume that you are definitely using a Generic connection and specifying the JDBC connection string, Also, make sure that you are loading proper mysql connector jar and selecting right Database Driver.
I did some googling and found this http://stackoverflow.com/questions/18651843/not-able-to-execute-query-in-mariadb-using-mysql-as-driver
I'm not sure if this related to your case, but maybe it would help.
Now the connection is working with Generic JDBC driver.
I can see tables and design the structure, but when I try generate XDX, error occurs
No operations allowed after connection closed.
It is the same with SQL DB and MySQL.
Please provide us a full stack trace for the error that you see.
FOR SQL connection
com.microsoft.sqlserver.jdbc.SQLServerException: Соединение закрыто.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:388)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:2166)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:1853)
at com.devwebsphere.jdbc.loader.NamedParameterStatement.<init>(NamedParameterStatement.java:55)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.writeDumpXML(OpenDataProcessor.java:509)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.dumpOpenDataXML(OpenDataProcessor.java:148)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.extract(OpenDataProcessor.java:130)
at uk.org.jcam.camed.ui.handlers.opendata.GenerateOpenDataHandler$2.run(GenerateOpenDataHandler.java:122)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
FOR MySQL connection
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888)
at com.mysql.jdbc.Connection.checkClosed(Connection.java:1932)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4768)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4734)
at com.devwebsphere.jdbc.loader.NamedParameterStatement.<init>(NamedParameterStatement.java:55)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.writeDumpXML(OpenDataProcessor.java:509)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.dumpOpenDataXML(OpenDataProcessor.java:148)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.extract(OpenDataProcessor.java:130)
at uk.org.jcam.camed.ui.handlers.opendata.GenerateOpenDataHandler$2.run(GenerateOpenDataHandler.java:122)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
That's interesting. It appears that connection get closed while Open-XDX is still trying to retrieve data. Is there any connection pool configured on the DB side?
Could you please try to connect to local MySQL instance and try the same routine with OpenXDX data generation. I'm suspecting that SSH tunneling may be breaking connection to DB and causing those errors.
I installed MySQL to localhost and now next Exceptions for two DataSource' types
for Generic JDBC, the same as for shared DB:
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888)
at com.mysql.jdbc.Connection.checkClosed(Connection.java:1932)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4768)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4734)
at com.devwebsphere.jdbc.loader.NamedParameterStatement.<init>(NamedParameterStatement.java:55)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.writeDumpXML(OpenDataProcessor.java:509)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.dumpOpenDataXML(OpenDataProcessor.java:148)
at com.oracle.openxdx.processor.impl.OpenDataProcessor.extract(OpenDataProcessor.java:130)
at uk.org.jcam.camed.ui.handlers.opendata.GenerateOpenDataHandler$2.run(GenerateOpenDataHandler.java:122)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
For MySQL
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.peekChar(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)
Alex,
Thank you for your patience.The second error message clearly tells me that you are using non UTF-8 characters in the data that you are trying to export through OpenXDX. I can try to debug this issue if you can provide me a sample template and MySQL DB export.
Thank you!
Serge
Hi Serge!
I think it isn’t problem with my *.cam file.
I load the sample cam and xml from http://www.verifyxml.org/CAMV-page.html and the result was the same as with my template. Usually I use Russian Windows, can be here is problem.
I installed the Oracle Express to try connection by oracle DataSource, and Error was
"Got minus one from a read call". Full report in the file.
Now I have progress. I installed CAMed to Linux comp, connected to shared MySQL DB and created XDX file.
So, now I’ll try to create more sophisticated data structure and work with business rules. So, it is good case to understand architecture for information integration by CAM.
But I have interest to create set of Windows' components for data integration, as widely used OS.
Alex,
The issue seems to be related to your data encoding. By default, CAM engine is expecting to get data in UTF-8, and when it sees unknown character, it fails. We had similar issues in the past, here is a workaround that you can try https://sourceforge.net/p/camprocessor/discussion/339678/thread/299ac12c/#b5e0
Basically Java runtime will set encoding based on your OS settings, but you can override it in CAMed.ini file.
Please let us know if this workaround helps.
Serge,
With direct encoding designation in Windows CAM works in one case:
connection type - MySQL; for Generic DB JDBC and for Oracle - the same Exceptions.
Last edit: alex_scab 2014-07-19
Thank you for following up on this issue. The data encoding within DB may also cause this issues. The MySQL DB uses UTF-8 by default, and I would assume if you will use UTF-8 in Oracle DB instance, you should be able to flawlessly transform your data with Open-XDX.
It is really can be question of encoding. I have had problem with encoding by migration DB form MySQL under Windows 7 (RUS) to other MySQL instance with Windows 8 (RUS). I managed it, but non by standard MySQL methods, where standard dump file didn’t work with error in encoding. China forum discussed the same difficulties.
That's basically why I'm advocating to use UTF as a primary encoding. I do remember my past struggle with CP866, KOI8-R, ISO8859-5 and Win1251 :)
Hello,
One more question with encoding - this time not with 1251, but with IBM1252. I try to reproduce the sample of usage CAM files for data delivery by web https://github.com/VerifyXML/PMIX-Services-Demo. The problem is to read dump file – data is extracted from DB and written to the dump xml file with IBM1252 encoding. After this exception
java.io.UnsupportedEncodingException: IBM1252
Failed to load document
file: /AppData/Local/Temp/org.verifyxml.openxdx.OpenXDXDemo171316026219608853-open-xdx-dump.xml
in variable dataInputLoadXML (#175)
in variable dataRecordCount (#311)
at net.sf.saxon.instruct.GlobalVariable.getSelectValue(GlobalVariable.java:209)
at net.sf.saxon.instruct.GlobalVariable.evaluateVariable(GlobalVariable.java:233)
at net.sf.saxon.expr.VariableReference.evaluateVariable(VariableReference.java:480)
at net.sf.saxon.expr.VariableReference.iterate(VariableReference.java:433)
This type of encoding can be specified in the library open-xdx-0.1.
“Latin1” (the same as IBM1252) is clearly prescribed in the SQL file (directory DATA of this sample), where we can see “DEFAULT CHARSET=latin1”.
Please modify the file's prolog from
<?xml version='1.0' encoding='IBM1252'?>to
<?xml version='1.0'?>Last edit: Serge Leontiev 2014-09-23
Hi Alex,
Simply convert the source XML document to UTF8. Like I've mentioned before, the CAM tool is expecting input in UTF8 and unfortunately due to limitation of underlying API we can't get encoding details from XML prolog.
Thanks, now works.
Package ReadWriteTextFile http://www.javapractices.com/topic/TopicAction.do?Id=42 is able to help us.