WbDataDiff creates empty diff files

[Thomas Keller]

Hi all!

I'm currently trying to integrate liquibase and WbDataDiff (from the workbench build 112) with Ant. The general setup works already, however I have the problem that the WbDataDiff command never finds differences in any table of my two distinct schemas. It is as if it works on the data of just one schema, so I guess I did something wrong.

Here is my call:

$ java -Djava.awt.headless=true -Dworkbench.log.level=DEBUG -Xmx256m -jar lib/sqlworkbench-112.jar -driver=com.mysql.jdbc.Driver -driverjar=lib/mysql-connector-java-5.1.19-bin.jar -url='jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=UTF-8' -username=mytfk -password=*** -script=my.script

and here is `my.script`:

WbDataDiff -referenceSchema="tfk_tfk_temp"

                       -targetSchema="tfk_tfk"

                       -referenceTables="club"

                       -file="output.sql"

                       -includeDelete=true

                       -type=sql

                       -encoding=UTF-8;

output.sql then just contains

-- ***********************************************************************************

-- The following script will migrate the data in: 

-- User=mytfk, URL=jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=UTF-8

--

-- to match the data from: 

-- User=mytfk, URL=jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=UTF-8

--

-- Tables included:

--

-- Generated by SQL Workbench/J at: 2012-04-25 00:41:52.596 MESZ

-- ***********************************************************************************

------------------------

-- UPDATE/INSERT scripts

------------------------

What am I doing wrong?

On a related note, I stumbled upon two things:

1. Due to http://bugs.mysql.com/bug.php?id=14032 MySQL needs a single space after the `--` which marks the start of a comment. Unfortunately these kinds of comments aren't always created apparently, cf. the lines above and below "UPDATE/INSERT scripts".
2. Is there any way to let WbDataDiff generate the complete SQL diff as one monolithic file instead of (up to) three files per table?

Thanks in advance,

Thomas.

[Thomas Kellerer]

Hi,

This line:

> -- Tables included:

indicates that no tables were compared at all. Can you please check the workbench logfile if any errors were reported?

Also, could you do a SHOW CREATE TABLE for both tables and post it here?

> MySQL needs a single space after the `--` which marks the start of a comment.

Thanks, I will change that.

> 2. Is there any way to let WbDataDiff generate the complete SQL diff as one monolithic file instead of (up to) three files per table?
Currently not, but I'll put it on my todo list.

Regards
Thomas

Thomas Keller, 25.04.2012 00:50:

> 1. Due to http://bugs.mysql.com/bug.php?id=14032 MySQL needs a single space after the `--` which marks the start of a comment. Unfortunately these kinds of comments aren't always created apparently, cf. the lines above and below "UPDATE/INSERT scripts".
> 2. Is there any way to let WbDataDiff generate the complete SQL diff as one monolithic file instead of (up to) three files per table?
>
> Thanks in advance,
> Thomas.

[Thomas Keller]

Hi Thomas!

I couldn't get file logging to work, so this is the combined console log output:

$ java -Djava.awt.headless=true -Dworkbench.log.level=DEBUG -Dworkbench.log.console=true -Xmx256m -jar lib/sqlworkbench-112.jar -driver=com.mysql.jdbc.Driver -driverjar=lib/mysql-connector-java-5.1.19-bin.jar -url='jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=UTF-8' -username=mytfk -password=... -script=workbench-script-1693671271

INFO   =================== Log started =================== 

2012-04-25 19:37 INFO  Settings.<init> Using configdir: /Users/tommyd/.sqlworkbench 

2012-04-25 19:37 INFO  WbManager.init() Starting SQL Workbench/J, Build 112 (2012-03-25 18:35) 

2012-04-25 19:37 INFO  WbManager.init() Java version=1.6.0_31, java.home=/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home, vendor=Apple Inc., name=Java HotSpot(TM) 64-Bit Server VM 

2012-04-25 19:37 INFO  WbManager.init() Operating System=Mac OS X, version=10.6.8, platform=x86_64 

2012-04-25 19:37 DEBUG ConnectionMgr.readDrivers() WbDrivers.xml not found. Using defaults. 

2012-04-25 19:37 INFO  ConnectionMgr.getConnection() Creating new connection for [{Default group}/$Wb$CommandLineProfile] for driver=com.mysql.jdbc.Driver 

2012-04-25 19:37 INFO  DbDriver.loadDriverClass() Adding ClassLoader URL=file:/.../lib/mysql-connector-java-5.1.19-bin.jar 

2012-04-25 19:37 INFO  DbMetadata.<init> Using DBID=mysql 

2012-04-25 19:37 DEBUG DbMetadata.<init> Identifier quote character obtained from driver: ` 

2012-04-25 19:37 DEBUG DbMetadata.<init> Using configured tabletypes: [TABLE] 

2012-04-25 19:37 DEBUG DbMetadata.<init> Using catalog separator: [.] 

2012-04-25 19:37 INFO  ConnectionMgr.getConnection() Connected to: [MySQL], Database version: [5.5.12], Driver version: [mysql-connector-java-5.1.19 ( Revision: tonci...@oracle.com-20111003110438-qfydx066wsbydkbw )], JDBC Version: [4.0], ID: [BatchRunner] 

2012-04-25 19:37 INFO  BatchRunner.connect() Connection to "User=mytfk, URL=jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=UTF-8" successful 

Connection to "User=mytfk, URL=jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=UTF-8" successful

2012-04-25 19:37 INFO  BatchRunner.execute() Processing file: .../workbench-script-1693671271 

2012-04-25 19:37 DEBUG BatchRunner Executing statement: WbDataDiff -referenceSchema="tfk_tfk_temp"

                       -targetSchema="tfk_tfk"

                       -referenceTables="tfk_tfk_temp.club"

                       -file=".../workbench-output-411895675"

                       -includeDelete=true

                       -type=sql

                       -encoding=UTF-8 

2012-04-25 19:37 DEBUG SourceTableArgument.parseArgument() Table tfk_tfk_temp.club not found! 

Sync-Script written to .../workbench-output-411895675

SQL execution time: 0.05s

1 statement(s) executed.

Execution time: 0.07s

2012-04-25 19:37 INFO  ConnectionMgr.disconnect() Disconnecting: [$Wb$CommandLineProfile], ID=BatchRunner 

2012-04-25 19:37 DEBUG WbConnection.close() Connection BatchRunner closed. 

2012-04-25 19:37 DEBUG WbManager.closeAllWindows() Closing all open windows 

2012-04-25 19:37 INFO  WbManager.doShutdown() Stopping SQL Workbench/J, Build 112 

2012-04-25 19:37 INFO   =================== Log stopped =================== 

The create scripts:

mysql> show create table tfk_tfk_temp.club\G

*************************** 1. row ***************************

       Table: club

Create Table: CREATE TABLE `club` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `pid` int(10) unsigned NOT NULL,

  `sorting` int(10) unsigned NOT NULL DEFAULT '0',

  `tstamp` int(10) unsigned NOT NULL DEFAULT '0',

  `club_name` varchar(255) NOT NULL DEFAULT '',

  `club_art` int(10) NOT NULL,

  `club_person_anrede` varchar(255) NOT NULL DEFAULT '',

  `club_person_nachname` varchar(255) NOT NULL DEFAULT '',

  `club_person_vorname` varchar(255) NOT NULL DEFAULT '',

  `club_strasse` varchar(255) NOT NULL DEFAULT '',

  `club_hausnummer` varchar(255) NOT NULL DEFAULT '',

  `club_plz` varchar(255) NOT NULL DEFAULT '',

  `club_ort` varchar(255) NOT NULL DEFAULT '',

  `club_telefon` varchar(255) NOT NULL DEFAULT '',

  `club_email` varchar(255) NOT NULL DEFAULT '',

  `club_steuernummer` varchar(255) NOT NULL DEFAULT '',

  `club_status` int(10) unsigned DEFAULT '0',

  `club_logo_big` varchar(255) NOT NULL DEFAULT '...',

  `club_logo_small` varchar(255) NOT NULL DEFAULT '...',

  `club_payoff` int(10) unsigned DEFAULT NULL,

  `club_oldranking` int(10) unsigned NOT NULL DEFAULT '0',

  `club_fans` int(10) DEFAULT NULL,

  `club_lon` double DEFAULT '0',

  `club_lat` double DEFAULT '0',

  `club_url` varchar(255) NOT NULL DEFAULT '',

  `club_payoff_percent` double DEFAULT NULL,

  `club_login` varchar(255) NOT NULL DEFAULT '',

  `club_pwd` varchar(255) NOT NULL DEFAULT '',

  `member_id` int(10) DEFAULT NULL,

  `admin_info` int(10) DEFAULT NULL,

  `sub_club_art` text,

  `timestamp_club` varchar(10) NOT NULL DEFAULT '',

  `amazon_tracking_id` int(10) NOT NULL DEFAULT '0',

  `amazon_shop_id` int(10) DEFAULT NULL,

  `club_name_long` varchar(255) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `id` (`id`,`club_status`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 row in set (0.01 sec)

and

mysql> show create table tfk_tfk.club\G

*************************** 1. row ***************************

       Table: club

Create Table: CREATE TABLE `club` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `pid` int(10) unsigned NOT NULL,

  `sorting` int(10) unsigned NOT NULL DEFAULT '0',

  `tstamp` int(10) unsigned NOT NULL DEFAULT '0',

  `club_name` varchar(255) NOT NULL DEFAULT '',

  `club_art` int(10) NOT NULL,

  `club_person_anrede` varchar(255) NOT NULL DEFAULT '',

  `club_person_nachname` varchar(255) NOT NULL DEFAULT '',

  `club_person_vorname` varchar(255) NOT NULL DEFAULT '',

  `club_strasse` varchar(255) NOT NULL DEFAULT '',

  `club_hausnummer` varchar(255) NOT NULL DEFAULT '',

  `club_plz` varchar(255) NOT NULL DEFAULT '',

  `club_ort` varchar(255) NOT NULL DEFAULT '',

  `club_telefon` varchar(255) NOT NULL DEFAULT '',

  `club_email` varchar(255) NOT NULL DEFAULT '',

  `club_steuernummer` varchar(255) NOT NULL DEFAULT '',

  `club_status` int(10) unsigned DEFAULT '0',

  `club_logo_big` varchar(255) NOT NULL DEFAULT '...',

  `club_logo_small` varchar(255) NOT NULL DEFAULT '...',

  `club_payoff` int(10) unsigned DEFAULT NULL,

  `club_oldranking` int(10) unsigned NOT NULL DEFAULT '0',

  `club_fans` int(10) DEFAULT NULL,

  `club_lon` double DEFAULT '0',

  `club_lat` double DEFAULT '0',

  `club_url` varchar(255) NOT NULL DEFAULT '',

  `club_payoff_percent` double DEFAULT NULL,

  `club_login` varchar(255) NOT NULL DEFAULT '',

  `club_pwd` varchar(255) NOT NULL DEFAULT '',

  `member_id` int(10) DEFAULT NULL,

  `admin_info` int(10) DEFAULT NULL,

  `sub_club_art` text,

  `timestamp_club` varchar(10) NOT NULL DEFAULT '',

  `amazon_tracking_id` int(10) NOT NULL DEFAULT '0',

  `amazon_shop_id` int(10) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `id` (`id`,`club_status`)

) ENGINE=MyISAM AUTO_INCREMENT=114 DEFAULT CHARSET=utf8

1 row in set (0.04 sec)

>  MySQL needs a single space after the `--` which marks the start of a comment.
Thanks, I will change that.

Ok.

 

>  2. Is there any way to let WbDataDiff generate the complete SQL diff as one monolithic file instead of (up to) three files per table?
Currently not, but I'll put it on my todo list.

Cool, thank you!

Thomas 

[Thomas Kellerer]

Hi Thomas (I nearly feel like I'm speaking to myself ;) ),

> I couldn't get file logging to work

Just specify -logFile=/path/to/some.log on the commandline if you want a different logfile than the default one.
If you do not specify that, it will be created in $HOME/.sqlworkbench/workbench.log
Of course, you don't need the -Dworkbench.log.console=true then.


The reason why the table(s) are not found is because of this:

-referenceSchema=tfk_tfk_temp
-referenceTables="tfk_tfk_temp.club"

MySQL does not support schemas.

A tablename like "tfk_tfk_temp.club" references the _database_ tfk_tfk_temp not a _schema_.
The target/referenceSchema parameters are intended for "real" schema (e.g. for PostgreSQL, Oracle, ...).

Currently there is no parameter to specify the catalog (aka database).

But you can leave the schema parameter out alltogether. The following should work:

wbdatadiff -referenceTables="tfk_tfk_temp.foo"
-targetTables="tfk_tfk.foo"

-file=".../workbench-output-411895675"
-includeDelete=true
-type=sql
-encoding=UTF-8

The only disadvantage is that the header comments in the generated files will list the same database twice. If you don't want that, you can create two different connection profiles, each one connecting to one of the database.

Then you can run the command like this:

WbDataDiff -referenceProfile="tfk_tfk_temp_profile"
-targetProfile="tfk_tfk_profile"
-referenceTables="club"

-file=".../workbench-output-411895675"
-includeDelete=true
-type=sql
-encoding=UTF-8

Where the JDBC URL for "tfk_tfk_temp_profile" would be something like this:

jdbc:mysql://localhost:3306/tfk_tfk_temp?useUnicode=true&characterEncoding=UTF-8

and the one for "tfk_tfk_profile" would be:

jdbc:mysql://localhost:3306/tfk_tfk?useUnicode=true&characterEncoding=UTF-8

Hope this helps.

I will update the documentation to make it clear that the schema parameter is indeed only intended to specify schemas ;)


Kind regards
Thomas

[Thomas Keller]

Am Mittwoch, 25. April 2012 20:09:59 UTC+2 schrieb Thomas Kellerer:

Hi Thomas (I nearly feel like I'm speaking to myself ;) ),

Yeah, erm ;)

 

> I couldn't get file logging to work

Just specify -logFile=/path/to/some.log on the commandline if you want a different logfile than the default one.
If you do not specify that, it will be created in $HOME/.sqlworkbench/workbench.log
Of course, you don't need the -Dworkbench.log.console=true then.

Ok, I tried -Dworkbench.log.file, but this didn't work. I didn't knew of -logFile.

 

The reason why the table(s) are not found is because of this:
     
    -referenceSchema=tfk_tfk_temp
    -referenceTables="tfk_tfk_temp.club"

MySQL does not support schemas.

Ok, I thought you were using this synonymously for those DBMS like MySQL that are missing schema support.

 

A tablename like "tfk_tfk_temp.club" references the _database_ tfk_tfk_temp not a _schema_.
The target/referenceSchema parameters are intended for "real" schema (e.g. for PostgreSQL, Oracle, ...).

Currently there is no parameter to specify the catalog (aka database).

But you can leave the schema parameter out alltogether. The following should work:

wbdatadiff -referenceTables="tfk_tfk_temp.foo"
            -targetTables="tfk_tfk.foo"
            -file=".../workbench-output-411895675"
            -includeDelete=true
            -type=sql
            -encoding=UTF-8

Thats a bit unfortunate, because the table set is fixed for both reference and target set.

 

The only disadvantage is that the header comments in the generated files will list the same database twice. If you don't want that, you can create two different connection profiles, each one connecting to one of the database.

Then you can run the command like this:

WbDataDiff -referenceProfile="tfk_tfk_temp_profile"
            -targetProfile="tfk_tfk_profile"
            -referenceTables="club"
            -file=".../workbench-output-411895675"
            -includeDelete=true
            -type=sql
            -encoding=UTF-8

Where the JDBC URL for "tfk_tfk_temp_profile" would be something like this:

     jdbc:mysql://localhost:3306/tfk_tfk_temp?useUnicode=true&characterEncoding=UTF-8

and the one for "tfk_tfk_profile" would be:

     jdbc:mysql://localhost:3306/tfk_tfk?useUnicode=true&characterEncoding=UTF-8

Hope this helps.

I tried to go down the route without profiles and used the -url parameter, so I guess a

-referenceUrl parameter is not available...? If not, I think using profiles is the only / best

option.

 

I will update the documentation to make it clear that the schema parameter is indeed only intended to specify schemas ;)

Thanks!

(The other) Thomas. 

[Thomas Kellerer]

Hi,

> Ok, I tried -Dworkbench.log.file, but this didn't work. I didn't knew of -logFile.

http://www.sql-workbench.net/manual/using-scripting.html#scripting-logfile

> I tried to go down the route without profiles and used the -url parameter, so I guess a
> -referenceUrl parameter is not available...?

No, for WbDataDiff (and WbSchemaDiff) you always need to use "real" profiles. You cannot specify the full connection with parameters only.
I do have an item on my todo list to make "ad-hoc" connections possible for the two Diff commands though ;)

Regards
Thomas

[Thomas Keller]

Am Mittwoch, 25. April 2012 19:45:11 UTC+2 schrieb Thomas Keller:

Hi Thomas!

>  MySQL needs a single space after the `--` which marks the start of a comment.
Thanks, I will change that.

Ok.

 

>  2. Is there any way to let WbDataDiff generate the complete SQL diff as one monolithic file instead of (up to) three files per table?
Currently not, but I'll put it on my todo list.

Cool, thank you!

Just a quick question - how regularily release new versions and how long is your todo list currently? Because I'd have to prepare ugly 

workarounds for both issues via Ant to make sqlworkbench really usable for my use case; if you say however that these are quickies

on your side and release them in a couple of days, I'd rather wait until then.

Thanks,

Thomas.

[Thomas Kellerer]

Thomas Keller wrote on 25.04.2012 20:57:
>> MySQL needs a single space after the `--` which marks the start of a comment.
>Thanks, I will change that.
>

>> Is there any way to let WbDataDiff generate the complete SQL diff as one monolithic file instead of (up to) three files per table?
> Currently not, but I'll put it on my todo list.
>

> Just a quick question - how regularily release new versions and how long is your todo list currently? Because I'd have to prepare ugly
> workarounds for both issues via Ant to make sqlworkbench really usable for my use case; if you say however that these are quickies
> on your side and release them in a couple of days, I'd rather wait until then.

The first one is already committed to SVN (that was a *real* quickie).

The second one I can probably do until the weekend. Shouldn't be a big thing.

Regards
Thomas

[Thomas Keller]

Am Mittwoch, 25. April 2012 21:39:00 UTC+2 schrieb Thomas Kellerer:

The first one is already committed to SVN (that was a *real* quickie).

The second one I can probably do until the weekend. Shouldn't be a big thing.

Very cool, thank you for this great support!

Thomas.

[Kailash sinduja]

Hi,
Dear Thomas,
I want to join this group to get email alerts on this mail id. Please
guide how to subscribe with one click.

Best Regards,
Kailash Sinduja.

[Thomas Kellerer]

I uploaded a new dev-build yesterday. Let me know if that fixes your problems

Regards
Thomas

[Thomas Keller]

Cool, just seen that today (don't check the list regularily) - I'll give it a try just now.

Many thanks so far for the support!

Thomas.

[Thomas Keller]

Hi Thomas!

I found some time to test the new implementation (112.4) and apparently this still does not work; probably I'm making some kind of stupid mistake.

This is my WbDrivers.xml:

<?xml version="1.0" encoding="UTF-8"?>

<java version="1.6.0" class="java.beans.XMLDecoder"> 

 <object class="java.util.ArrayList"> 

  <void method="add"> 

   <object class="workbench.db.DbDriver"> 

    <void property="driverClass"> 

     <string>com.mysql.jdbc.Driver</string> 

    </void> 

    <void property="library"> 

     <string>/path/to/migration/lib/mysql-connector-java-5.1.19-bin.jar</string> 

    </void> 

    <void property="name"> 

     <string>MySQL</string> 

    </void>

   </object> 

  </void>

 </object>

</java>

and this is my WbProfiles.xml:

<?xml version="1.0" encoding="UTF-8"?>

<java version="1.6.0" class="java.beans.XMLDecoder"> 

 <object class="java.util.ArrayList"> 

  <void method="add"> 

   <object class="workbench.db.ConnectionProfile"> 

    <void property="driverName"> 

     <string>MySQL</string> 

    </void> 

    <void property="driverclass"> 

     <string>com.mysql.jdbc.Driver</string> 

    </void> 

    <void property="name"> 

     <string>tfk_tfk_temp</string> 

    </void> 

    <void property="storeExplorerSchema"> 

     <boolean>true</boolean> 

    </void> 

    <void property="url"> 

     <string>jdbc:mysql://localhost:3306/tfk_tfk_temp?useUnicode=true&amp;characterEncoding=UTF8</string> 

    </void> 

    <void property="username"> 

     <string>mytfk</string> 

    </void>

    <void property="password"> 

       <string>***</string> 

    </void> 

   </object> 

  </void> 

  <void method="add"> 

   <object class="workbench.db.ConnectionProfile"> 

    <void property="driverName"> 

     <string>MySQL</string> 

    </void> 

    <void property="driverclass"> 

     <string>com.mysql.jdbc.Driver</string> 

    </void> 

    <void property="name"> 

     <string>tfk_tfk</string> 

    </void> 

    <void property="storeExplorerSchema"> 

     <boolean>true</boolean> 

    </void> 

    <void property="url"> 

     <string>jdbc:mysql://localhost:3306/tfk_tfk?useUnicode=true&amp;characterEncoding=UTF8</string> 

    </void> 

    <void property="username"> 

     <string>mytfk</string> 

    </void>

    <void property="password"> 

       <string>***</string> 

    </void> 

   </object> 

  </void> 

 </object> 

</java> 

(both files are dynamically created in my Ant script and reside in /path/to/migration/temp)

This is how I call sqlworkbench (from Ant):

java \

  -Djava.awt.headless=true \

  -Dworkbench.log.level=DEBUG \

  -Xmx256m \

  -jar /path/to/migration/lib/sqlworkbench-112.4.jar \

  -configDir=/path/to/migration/temp \

  -script=/path/to/migration/temp/script.sql

These are the contents of script.sql:

WbDataDiff -referenceProfile="tfk_tfk_temp"

   -targetProfile="tfk_tfk"

   -referenceTables="club"

   -ignoreColumns=""

   -file="/path/to/migration/temp/diff.sql"

   -singleFile=true

   -includeDelete=true

   -type=sql

   -encoding=UTF-8

When the script is executed, it still fails with "Reference tables club not found!". Needless to say that both tables (tfk_tfk.club and tfk_tfk_temp.club) exist. This is the workbench log:

2012-05-08 22:23 INFO   =================== Log started =================== 

2012-05-08 22:23 INFO  Settings.<init> Using configdir: /path/to/frontend/migration/temp 

2012-05-08 22:23 INFO  WbManager.init() Starting SQL Workbench/J, Build 112.4 (2012-04-29 20:03) 

2012-05-08 22:23 INFO  WbManager.init() Java version=1.6.0_31, java.home=/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home, vendor=Apple Inc., name=Java HotSpot(TM) 64-Bit Server VM 

2012-05-08 22:23 INFO  WbManager.init() Operating System=Mac OS X, version=10.6.8, platform=x86_64 

2012-05-08 22:23 WARN  BatchRunner.connect() No profile defined, proceeding without a connection. 

2012-05-08 22:23 INFO  BatchRunner.execute() Processing file: /path/to/migration/temp/script.sql 

2012-05-08 22:23 DEBUG BatchRunner Executing statement: WbDataDiff -referenceProfile="tfk_tfk_temp"

   -targetProfile="tfk_tfk"

   -referenceTables="club"

   -ignoreColumns=""

   -file="/path/to/migration/temp/diff.sql"

   -singleFile=true

   -includeDelete=true

   -type=sql

   -encoding=UTF-8

 

2012-05-08 22:23 INFO  ConnectionMgr.getConnection() Creating new connection for [{Default group}/tfk_tfk] for driver=com.mysql.jdbc.Driver 

2012-05-08 22:23 INFO  DbDriver.loadDriverClass() Adding ClassLoader URL=file:/path/to/migration/lib/mysql-connector-java-5.1.19-bin.jar 

2012-05-08 22:23 INFO  DbMetadata.<init> Using DBID=mysql 

2012-05-08 22:23 DEBUG DbMetadata.<init> Identifier quote character obtained from driver: ` 

2012-05-08 22:23 DEBUG DbMetadata.<init> Using configured tabletypes: [TABLE] 

2012-05-08 22:23 DEBUG DbMetadata.<init> Using catalog separator: [.] 

2012-05-08 22:23 INFO  ConnectionMgr.getConnection() Connected to: [MySQL], Database version: [5.5.12], Driver version: [mysql-connector-java-5.1.19 ( Revision: tonci...@oracle.com-20111003110438-qfydx066wsbydkbw )], JDBC Version: [4.0], ID: [Wb-Diff-Target] 

2012-05-08 22:23 INFO  ConnectionMgr.getConnection() Creating new connection for [{Default group}/tfk_tfk_temp] for driver=com.mysql.jdbc.Driver 

2012-05-08 22:23 INFO  DbMetadata.<init> Using DBID=mysql 

2012-05-08 22:23 DEBUG DbMetadata.<init> Identifier quote character obtained from driver: ` 

2012-05-08 22:23 DEBUG DbMetadata.<init> Using configured tabletypes: [TABLE] 

2012-05-08 22:23 DEBUG DbMetadata.<init> Using catalog separator: [.] 

2012-05-08 22:23 INFO  ConnectionMgr.getConnection() Connected to: [MySQL], Database version: [5.5.12], Driver version: [mysql-connector-java-5.1.19 ( Revision: tonci...@oracle.com-20111003110438-qfydx066wsbydkbw )], JDBC Version: [4.0], ID: [Wb-Diff-Source] 

2012-05-08 22:23 ERROR WbDataDiff.execute() Error during diff Reference table club not found!

java.sql.SQLException: Reference table club not found!

at workbench.db.compare.TableDataDiff.setTableName(TableDataDiff.java:273)

at workbench.sql.wbcommands.WbDataDiff.execute(WbDataDiff.java:336)

at workbench.sql.StatementRunner.runStatement(StatementRunner.java:410)

at workbench.sql.BatchRunner.executeScript(BatchRunner.java:649)

at workbench.sql.BatchRunner.executeScript(BatchRunner.java:587)

at workbench.sql.BatchRunner.runFiles(BatchRunner.java:459)

at workbench.sql.BatchRunner.execute(BatchRunner.java:415)

at workbench.WbManager.runBatch(WbManager.java:931)

at workbench.WbManager.startApplication(WbManager.java:816)

at workbench.WbManager.main(WbManager.java:1030)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at workbench.WbStarter.main(WbStarter.java:131)

2012-05-08 22:23 ERROR BatchRunner.execute() Reference table club not found! 

2012-05-08 22:23 INFO  ConnectionMgr.disconnect() Disconnecting: [tfk_tfk], ID=Wb-Diff-Target 

2012-05-08 22:23 DEBUG WbConnection.close() Connection Wb-Diff-Target closed. 

2012-05-08 22:23 INFO  ConnectionMgr.disconnect() Disconnecting: [tfk_tfk_temp], ID=Wb-Diff-Source 

2012-05-08 22:23 DEBUG WbConnection.close() Connection Wb-Diff-Source closed. 

2012-05-08 22:23 DEBUG WbManager.closeAllWindows() Closing all open windows 

2012-05-08 22:23 INFO  WbManager.doShutdown() Stopping SQL Workbench/J, Build 112.4 

2012-05-08 22:23 INFO   =================== Log stopped =================== 

I also saw that it created two empty files, club_$insert.sql and club_$delete.sql, in the temp dir, though I thought it would save everything in one monolithic file...?

Thanks for your ongoing support!

Thomas.

[Thomas Kellerer]

Hi,

> When the script is executed, it still fails with "Reference tables club not found!".
> Needless to say that both tables (tfk_tfk.club and tfk_tfk_temp.club) exist. This is the workbench log:

This is caused by an incompability of the MySQL JDBC driver. Luckily I have just released a fix for the same problem with the Microsoft drivers.

Please use 112.6 and add the line:

workbench.db.mysql.metadata.retrieval.wildcards=false

to workbench.settings.

> I also saw that it created two empty files, club_$insert.sql and
> club_$delete.sql, in the temp dir, though I thought it would save
> everything in one monolithic file...?

Yes that's expected.

During comparison the individual steps still write one file each.
Once the diff finishes without an error, they will be merged into the script specified.

In your case the "missing" table caused the diff to not reach the "merging" phase.


Regards
Thomas

[Thomas Keller]

Hah, that worked great. Finally! Many, many thanks!

Thomas.