Announcement

Collapse
No announcement yet.

how to find out what's wrong with jdbc exporter?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • how to find out what's wrong with jdbc exporter?

    I want to export the data from voltdb to mssql, and I have configured the exporter.The configuration is below:

    <export>
    <configuration enabled="true" target="archive" type="jdbc">
    <property name="type">jdbc</property>
    <property name="jdbcurl">jdbc:sqlserver://10.201.0.113:1433;databaseName=testdata</property>
    <property name="jdbcuser">sa</property>
    <property name="jdbcpassword">Cowcowcow2631</property>
    <property name="jdbcdriver">com.microsoft.sqlserver.jdbc.SQL ServerDriver</property>
    <property name="schema">testdata</property>
    </configuration>
    </export>

    After I create table "company" and stream "export_company" export to archive, the voltdb write the stream to export_overflow rather than to remote databases.
    Did I miss some necessary steps?

    CREATE TABLE COMPANY ( NAME varchar(2048), ID integer );
    CREATE STREAM EXPORT_COMPANY EXPORT TO TARGET ARCHIVE ( NAME varchar(2048), ID integer );
    Last edited by richard; 06-15-2017, 05:33 AM.

  • #2
    Hi Richard,

    VoltDB won't try to connect to the JDBC target until you have inserted a record into a stream that is configured for that target. It looks like you have done that correctly, and if you are seeing some files output in export_overflow, that is a sign that you did in fact insert into the stream.

    The export connector, once it detects some data in the stream will do the following:
    1. Connect to the target database. If it fails, you should see errors in volt.log. This used to be fatal in older versions, but in the latest version, it will just keep trying.
    2. Run a CREATE TABLE statement to create the target table. The statement it generates gets logged to volt.log so you can see what it was using in case of any failure. If the user does not have permission to create tables, or if there is any problem with the statement, you should see an error message in volt.log.
    3. Then it begins to insert into the table from the buffered records that have been inserted into the stream. For databases that support batch inserts, it will do a batch insert. There will be a separate batch for each table for each partition, which will contain up to some size limit or up to 5 seconds of data. If it fails, it will keep on trying and will show errors periodically in volt.log.

    Two small details that sometimes get missed is that if you don't override the "skipinternals" property to true it will include 6 additional metadata columns in the target table, and if you don't override ignoregenerations to true it will create a table named {stream-name}_{generation-id}, and you probably just want it to have the same name as the stream.

    I expect you're probably getting some error in the volt.log at one of these points in the process, so hopefully this will help.

    Best regards,
    Ben

    Comment


    • #3
      Thanks, Ben
      I try to find some error in the volt.log,but it seems nothing special and it still not work. I paste all the log related to stream below:

      2017-06-16 05:21:44,940 INFO [Ad Hoc Planner - 0] COMPILER: Applying the following DDL to cluster:
      2017-06-16 05:21:44,940 INFO [Ad Hoc Planner - 0] COMPILER: CREATE STREAM EXPORT_COMPANY EXPORT TO TARGET ARCHIVE ( NAME varchar(2048), ID integer )
      2017-06-16 05:21:44,977 INFO [Ad Hoc Planner - 0] HOST: Export target archive is configured and enabled with type=JDBC
      2017-06-16 05:21:44,977 INFO [Ad Hoc Planner - 0] HOST: Export target archive configuration properties are:
      2017-06-16 05:21:44,977 INFO [Ad Hoc Planner - 0] HOST: Export Configuration Property NAME=type VALUE=jdbc
      2017-06-16 05:21:44,978 INFO [Ad Hoc Planner - 0] HOST: Export Configuration Property NAME=jdbcurl VALUE=jdbc:sqlserver://10.201.0.113:1433;databaseName=testdata
      2017-06-16 05:21:44,978 INFO [Ad Hoc Planner - 0] HOST: Export Configuration Property NAME=jdbcuser VALUE=sa
      2017-06-16 05:21:44,978 INFO [Ad Hoc Planner - 0] HOST: Export Configuration Property NAME=jdbcdriver VALUE=com.microsoft.sqlserver.jdbc.SQLServerDriver
      2017-06-16 05:21:44,978 INFO [Ad Hoc Planner - 0] HOST: Export Configuration Property NAME=schema VALUE=testdata
      2017-06-16 05:21:44,978 INFO [Ad Hoc Planner - 0] HOST: Export Configuration Property NAME=ignoregenerations VALUE=true
      2017-06-16 05:21:44,978 INFO [Ad Hoc Planner - 0] HOST: Export Configuration Property NAME=skipinternals VALUE=true
      2017-06-16 05:21:44,985 INFO [MP Site - 0:8] HOST: New catalog update from: Catalog: TXN ID 3619631923249151, catalog hash 1D026D7258, deployment hash FC8BA3D9A5


      2017-06-16 05:21:45,061 INFO [SP 5 Site - 0:5] EXPORT: Creating ad for EXPORT_COMPANY|vi_0
      2017-06-16 05:21:45,063 INFO [SP 5 Site - 0:5] EXPORT: Creating ExportDataSource for table EXPORT_COMPANY signature EXPORT_COMPANY|vi partition id 0
      2017-06-16 05:21:45,064 INFO [SP 5 Site - 0:5] EXPORT: Creating ad for EXPORT_COMPANY|vi_1
      2017-06-16 05:21:45,066 INFO [SP 5 Site - 0:5] EXPORT: Creating ExportDataSource for table EXPORT_COMPANY signature EXPORT_COMPANY|vi partition id 1
      2017-06-16 05:21:45,066 INFO [SP 5 Site - 0:5] EXPORT: Creating ad for EXPORT_COMPANY|vi_2
      2017-06-16 05:21:45,066 INFO [SP 5 Site - 0:5] EXPORT: Creating ExportDataSource for table EXPORT_COMPANY signature EXPORT_COMPANY|vi partition id 2
      2017-06-16 05:21:45,066 INFO [SP 5 Site - 0:5] EXPORT: Creating ad for EXPORT_COMPANY|vi_3
      2017-06-16 05:21:45,067 INFO [SP 5 Site - 0:5] EXPORT: Creating ExportDataSource for table EXPORT_COMPANY signature EXPORT_COMPANY|vi partition id 3
      2017-06-16 05:21:45,067 INFO [SP 5 Site - 0:5] EXPORT: Creating ad for EXPORT_COMPANY|vi_4
      2017-06-16 05:21:45,068 INFO [SP 5 Site - 0:5] EXPORT: Creating ExportDataSource for table EXPORT_COMPANY signature EXPORT_COMPANY|vi partition id 4
      2017-06-16 05:21:45,069 INFO [SP 5 Site - 0:5] EXPORT: Creating ad for EXPORT_COMPANY|vi_5
      2017-06-16 05:21:45,069 INFO [SP 5 Site - 0:5] EXPORT: Creating ExportDataSource for table EXPORT_COMPANY signature EXPORT_COMPANY|vi partition id 5
      2017-06-16 05:21:45,070 INFO [SP 5 Site - 0:5] EXPORT: Creating ad for EXPORT_COMPANY|vi_6
      2017-06-16 05:21:45,074 INFO [SP 5 Site - 0:5] EXPORT: Creating ExportDataSource for table EXPORT_COMPANY signature EXPORT_COMPANY|vi partition id 6
      2017-06-16 05:21:45,074 INFO [SP 5 Site - 0:5] EXPORT: Creating ad for EXPORT_COMPANY|vi_7
      2017-06-16 05:21:45,076 INFO [SP 5 Site - 0:5] EXPORT: Creating ExportDataSource for table EXPORT_COMPANY signature EXPORT_COMPANY|vi partition id 7


      2017-06-16 05:21:45,136 INFO [SP 5 Site - 0:5] EXPORT: First stream created processor will be initialized: org.voltdb.export.processors.GuestProcessor
      2017-06-16 05:21:45,136 INFO [SP 5 Site - 0:5] EXPORT: Creating connector org.voltdb.export.processors.GuestProcessor
      2017-06-16 05:21:45,140 INFO [SP 5 Site - 0:5] EXPORT: Setting master for partition: 0 Table EXPORT_COMPANY Replica running false
      2017-06-16 05:21:45,142 INFO [SP 5 Site - 0:5] EXPORT: Accepting mastership for export generation 2503537317508038655 Table EXPORT_COMPANY partition 0
      2017-06-16 05:21:45,142 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 0] EXPORT: Export generation 2503537317508038655 Table EXPORT_COMPANY accepting mastership for partition 0
      2017-06-16 05:21:45,142 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 0] EXPORT: Beginning export processing for export source EXPORT_COMPANY partition 0 generation 2503537317508038655


      2017-06-16 05:21:45,143 INFO [SP 5 Site - 0:5] EXPORT: Setting master for partition: 1 Table EXPORT_COMPANY Replica running false
      2017-06-16 05:21:45,143 INFO [SP 5 Site - 0:5] EXPORT: Accepting mastership for export generation 2503537317508038655 Table EXPORT_COMPANY partition 1
      2017-06-16 05:21:45,143 INFO [SP 5 Site - 0:5] EXPORT: Setting master for partition: 2 Table EXPORT_COMPANY Replica running false
      2017-06-16 05:21:45,143 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 1] EXPORT: Export generation 2503537317508038655 Table EXPORT_COMPANY accepting mastership for partition 1
      2017-06-16 05:21:45,144 INFO [SP 5 Site - 0:5] EXPORT: Accepting mastership for export generation 2503537317508038655 Table EXPORT_COMPANY partition 2
      2017-06-16 05:21:45,144 INFO [SP 5 Site - 0:5] EXPORT: Setting master for partition: 3 Table EXPORT_COMPANY Replica running false
      2017-06-16 05:21:45,144 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 2] EXPORT: Export generation 2503537317508038655 Table EXPORT_COMPANY accepting mastership for partition 2
      2017-06-16 05:21:45,144 INFO [SP 5 Site - 0:5] EXPORT: Accepting mastership for export generation 2503537317508038655 Table EXPORT_COMPANY partition 3
      2017-06-16 05:21:45,145 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 2] EXPORT: Beginning export processing for export source EXPORT_COMPANY partition 2 generation 2503537317508038655
      2017-06-16 05:21:45,145 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 1] EXPORT: Beginning export processing for export source EXPORT_COMPANY partition 1 generation 2503537317508038655
      2017-06-16 05:21:45,145 INFO [SP 5 Site - 0:5] EXPORT: Setting master for partition: 4 Table EXPORT_COMPANY Replica running false
      2017-06-16 05:21:45,145 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 3] EXPORT: Export generation 2503537317508038655 Table EXPORT_COMPANY accepting mastership for partition 3
      2017-06-16 05:21:45,145 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 3] EXPORT: Beginning export processing for export source EXPORT_COMPANY partition 3 generation 2503537317508038655
      2017-06-16 05:21:45,146 INFO [SP 5 Site - 0:5] EXPORT: Accepting mastership for export generation 2503537317508038655 Table EXPORT_COMPANY partition 4
      2017-06-16 05:21:45,149 INFO [SP 5 Site - 0:5] EXPORT: Setting master for partition: 5 Table EXPORT_COMPANY Replica running false
      2017-06-16 05:21:45,149 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 4] EXPORT: Export generation 2503537317508038655 Table EXPORT_COMPANY accepting mastership for partition 4
      2017-06-16 05:21:45,149 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 4] EXPORT: Beginning export processing for export source EXPORT_COMPANY partition 4 generation 2503537317508038655
      2017-06-16 05:21:45,150 INFO [SP 5 Site - 0:5] EXPORT: Accepting mastership for export generation 2503537317508038655 Table EXPORT_COMPANY partition 5
      2017-06-16 05:21:45,150 INFO [SP 5 Site - 0:5] EXPORT: Setting master for partition: 6 Table EXPORT_COMPANY Replica running false
      2017-06-16 05:21:45,151 INFO [SP 5 Site - 0:5] EXPORT: Accepting mastership for export generation 2503537317508038655 Table EXPORT_COMPANY partition 6
      2017-06-16 05:21:45,151 INFO [SP 5 Site - 0:5] EXPORT: Setting master for partition: 7 Table EXPORT_COMPANY Replica running false
      2017-06-16 05:21:45,151 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 5] EXPORT: Export generation 2503537317508038655 Table EXPORT_COMPANY accepting mastership for partition 5
      2017-06-16 05:21:45,151 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 5] EXPORT: Beginning export processing for export source EXPORT_COMPANY partition 5 generation 2503537317508038655
      2017-06-16 05:21:45,151 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 6] EXPORT: Export generation 2503537317508038655 Table EXPORT_COMPANY accepting mastership for partition 6
      2017-06-16 05:21:45,151 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 6] EXPORT: Beginning export processing for export source EXPORT_COMPANY partition 6 generation 2503537317508038655
      2017-06-16 05:21:45,151 INFO [SP 5 Site - 0:5] EXPORT: Accepting mastership for export generation 2503537317508038655 Table EXPORT_COMPANY partition 7
      2017-06-16 05:21:45,151 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 7] EXPORT: Export generation 2503537317508038655 Table EXPORT_COMPANY accepting mastership for partition 7
      2017-06-16 05:21:45,151 INFO [ExportDataSource gen 2503537317508038655 table EXPORT_COMPANY partition 7] EXPORT: Beginning export processing for export source EXPORT_COMPANY partition 7 generation 2503537317508038655
      Last edited by richard; 06-16-2017, 02:00 AM.

      Comment

      Working...
      X