Diff on two case insentivie databases is case sensitive

General Discussion
1

Hi,

we want to generate a diff for a H2 database and a MySQL database, both are configured as case insensitive:

  1. Boolean.valueOf(((JdbcConnection) connection).getUnderlyingConnection().getMetaData().supportsMixedCaseIdentifiers()); // returns FALSE for both connections

We use the following schema:
  1. <?xml version="1.0" encoding="UTF-8"?>

    <databaseChangeLog xmlns=“Liquibase | Liquibase XML Schema Definitions” xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”
      xsi:schemaLocation=“Liquibase | Liquibase XML Schema Definitions
             http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd”>

     
       
         
           
         
         
           
         
         
       
     


At first we init the MySQL database from the commandline with our schema. Afterwards we create in a small Java programm the H2 database in memory, migrate the schema and then we run the diff with this result:

INFO 12.11.13 11:52:liquibase: Creating database history table with name: PUBLIC.DATABASECHANGELOG
INFO 12.11.13 11:52:liquibase: Reading from PUBLIC.DATABASECHANGELOG
INFO 12.11.13 11:52:liquibase: Reading from PUBLIC.DATABASECHANGELOG
INFO 12.11.13 11:52:liquibase: db/btl_schema.xml: 1::xrescm: Table performance_logger created
INFO 12.11.13 11:52:liquibase: db/btl_schema.xml: 1::xrescm: ChangeSet db/btl_schema.xml::1::xrescm ran successfully in 0ms

Diff Results:
Reference Database:  @ jdbc:h2:mem:BTL (Default Schema: PUBLIC)
Comparison Database: btl@localhost @ jdbc:mysql://localhost:3306/btl (Default Schema: btl)
Product Name:
     Reference:   ‘H2’
     Target: ‘MySQL’
Product Version:
     Reference:   ‘1.3.170 (2012-11-30)’
     Target: ‘5.6.10-log’
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s):
     BTL
          name changed from ‘BTL’ to ‘btl’
Missing Column(s): NONE
Unexpected Column(s):
     performance_logger.EVENT
     databasechangeloglock.ID
     databasechangeloglock.LOCKED
     databasechangeloglock.LOCKEDBY
     databasechangeloglock.LOCKGRANTED
     performance_logger.LOG_DATE
     performance_logger.active
Changed Column(s):
     DATABASECHANGELOG.DATEEXECUTED
          type changed from ‘TIMESTAMP(23, 10)’ to ‘DATETIME(19)’
     DATABASECHANGELOG.ORDEREXECUTED
          type changed from ‘INTEGER(10)’ to ‘INT(10)’
Missing Foreign Key(s): NONE
Unexpected Foreign Key(s): NONE
Changed Foreign Key(s): NONE
Missing Index(s): NONE
Unexpected Index(s):
     primary on databasechangeloglock(ID)
     primary on performance_logger(LOG_DATE)
Changed Index(s): NONE
Missing Primary Key(s): NONE
Unexpected Primary Key(s):
     PRIMARY on databasechangeloglock(ID)
     PRIMARY on performance_logger(LOG_DATE)
Changed Primary Key(s): NONE
Missing Schema(s): NONE
Unexpected Schema(s): NONE
Changed Schema(s):
     BTL.PUBLIC
          name changed from ‘PUBLIC’ to ‘null’
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Table(s): NONE
Unexpected Table(s):
     databasechangeloglock
     performance_logger
Changed Table(s):
     DATABASECHANGELOG
          schema changed from ‘BTL.PUBLIC’ to ‘btl.null’
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
Missing View(s): NONE
Unexpected View(s): NONE
Changed View(s): NONE

Why are the columns performance_logger.EVENT, performance_logger.LOG_DATE and performance_logger.active marked as unexpected?
Why are there unexpected Index and Primary Keys?
Why are there unexpected tables?
Why are there changed tables?

Thanks an advance for your help!

2

What liquibase version are you running? There have been improvements to case handling from 2.x->3.0 and throughout the 3.x releases.

Nathan