Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-16

MySQLDatabaseMetaData.getColumns does not respect the catalog

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.1.1
    • Component/s: None
    • Labels:
      None

      Description

      MySQLDatabaseMetaData.getColumns is not respecting the catalog. This results in duplicate column entries in the result set when the same table structures exist in multiple catalogs.

        Gliffy Diagrams

          Activity

          Hide
          wlad Vladislav Vaintroub added a comment - - edited

          Could you please tell what are you passing to the function for 'catalog' parameter?

          Show
          wlad Vladislav Vaintroub added a comment - - edited Could you please tell what are you passing to the function for 'catalog' parameter?
          Hide
          george George El Boustani added a comment -

          We are passing in the name of the catalog, the database name. So in this case "jira"

          Show
          george George El Boustani added a comment - We are passing in the name of the catalog, the database name. So in this case "jira"
          Hide
          wlad Vladislav Vaintroub added a comment - - edited

          The full (arguably big and ugly) query that I get for 1.1.0 driver with

          connection.getMetaData().getColumns("jira", null, null, null);

          is given below . WHERE clause has (TABLE_SCHEMA = 'jira') condition, so there is filtering by the database name. The result set, for me, is empty because I do not have database named Jira.

          I have 3 theories on how to explain an error on your side

          1 . You're still using 1.0.0 driver. If you switch to 1.1.0 this will fix the problems
          2. You're not passing "jira" as first parameter, instead you're passing null. Connector/J by default does not handle null in conformance to JDBC specification .according to the docs "null means that the catalog name should not be used to narrow the search". Default handling in ConnectorJ is "null is current database" , and there is a parameter that can tweak JDBC compatibility (nullCatalogMeansCurrent=false would restore compatibility with JDBC spec).
          3. Something else which I do not yet understand

          Could you provide some more info so that I can figure out whether 1.,2.,or 3. is correct? Also, if you need the function to behave in a manner that is not compatible with JDBC spec, but with ConnectorJ instead (i.e if 2. Is true) could you please indicate that too?

          The Query:

          SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, CASE data_type WHEN 'bit' THEN -7 WHEN 'tinyblob' THEN -4 WHEN 'mediumblob' THEN -4 WHEN 'longblob' THEN -4 WHEN 'blob' THEN -4 WHEN 'tinytext' THEN -1 WHEN 'mediumtext' THEN -1 WHEN 'longtext' THEN -1 WHEN 'text' THEN -1 WHEN 'date' THEN 91 WHEN 'datetime' THEN 93 WHEN 'decimal' THEN 3 WHEN 'double' THEN 8 WHEN 'enum' THEN 12 WHEN 'float' THEN 6 WHEN 'int' THEN IF(COLUMN_TYPE LIKE '%unsigned%', -5,4) WHEN 'bigint' THEN -5 WHEN 'mediumint' THEN 4 WHEN 'null' THEN 0 WHEN 'set' THEN 12 WHEN 'smallint' THEN IF(COLUMN_TYPE LIKE '%unsigned%', 4,5) WHEN 'varchar' THEN 12 WHEN 'varbinary' THEN -3 WHEN 'char' THEN 1 WHEN 'binary' THEN -2 WHEN 'time' THEN 92 WHEN 'timestamp' THEN 93 WHEN 'tinyint' THEN -6 WHEN 'year' THEN 91 ELSE 1111 END DATA_TYPE, UCASE(IF(COLUMN_TYPE LIKE '%(%)%', CONCAT (SUBSTRING(COLUMN_TYPE,1, LOCATE('(',COLUMN_TYPE) - 1), SUBSTRING(COLUMN_TYPE,1+ LOCATE(')',COLUMN_TYPE))), COLUMN_TYPE)) TYPE_NAME, CASE COLUMN_TYPE WHEN 'time' THEN 8 WHEN 'date' THEN 10 WHEN 'datetime' THEN 19 WHEN 'timestamp' THEN 19 ELSE IF(NUMERIC_PRECISION IS NULL, LEAST(CHARACTER_MAXIMUM_LENGTH,2147483647), NUMERIC_PRECISION) END COLUMN_SIZE, 65535 BUFFER_LENGTH, NUMERIC_SCALE DECIMAL_DIGITS, 10 NUM_PREC_RADIX, IF(IS_NULLABLE = 'yes',1,0) NULLABLE,COLUMN_COMMENT REMARKS, COLUMN_DEFAULT COLUMN_DEF, 0 SQL_DATA_TYPE, 0 SQL_DATETIME_SUB, LEAST(CHARACTER_OCTET_LENGTH,2147483647) CHAR_OCTET_LENGTH, ORDINAL_POSITION, IS_NULLABLE, NULL SCOPE_CATALOG, NULL SCOPE_SCHEMA, NULL SCOPE_TABLE, NULL SOURCE_DATA_TYPE, IF(EXTRA = 'auto_increment','YES','NO') IS_AUTOINCREMENT
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE (TABLE_SCHEMA = 'jira') AND (1 = 1) AND (1 = 1)
          ORDER BY TABLE_CAT, TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION

          Show
          wlad Vladislav Vaintroub added a comment - - edited The full (arguably big and ugly) query that I get for 1.1.0 driver with connection.getMetaData().getColumns("jira", null, null, null); is given below . WHERE clause has (TABLE_SCHEMA = 'jira') condition, so there is filtering by the database name. The result set, for me, is empty because I do not have database named Jira. I have 3 theories on how to explain an error on your side 1 . You're still using 1.0.0 driver. If you switch to 1.1.0 this will fix the problems 2. You're not passing "jira" as first parameter, instead you're passing null. Connector/J by default does not handle null in conformance to JDBC specification .according to the docs "null means that the catalog name should not be used to narrow the search". Default handling in ConnectorJ is "null is current database" , and there is a parameter that can tweak JDBC compatibility (nullCatalogMeansCurrent=false would restore compatibility with JDBC spec). 3. Something else which I do not yet understand Could you provide some more info so that I can figure out whether 1.,2.,or 3. is correct? Also, if you need the function to behave in a manner that is not compatible with JDBC spec, but with ConnectorJ instead (i.e if 2. Is true) could you please indicate that too? The Query: SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, CASE data_type WHEN 'bit' THEN -7 WHEN 'tinyblob' THEN -4 WHEN 'mediumblob' THEN -4 WHEN 'longblob' THEN -4 WHEN 'blob' THEN -4 WHEN 'tinytext' THEN -1 WHEN 'mediumtext' THEN -1 WHEN 'longtext' THEN -1 WHEN 'text' THEN -1 WHEN 'date' THEN 91 WHEN 'datetime' THEN 93 WHEN 'decimal' THEN 3 WHEN 'double' THEN 8 WHEN 'enum' THEN 12 WHEN 'float' THEN 6 WHEN 'int' THEN IF(COLUMN_TYPE LIKE '%unsigned%', -5,4) WHEN 'bigint' THEN -5 WHEN 'mediumint' THEN 4 WHEN 'null' THEN 0 WHEN 'set' THEN 12 WHEN 'smallint' THEN IF(COLUMN_TYPE LIKE '%unsigned%', 4,5) WHEN 'varchar' THEN 12 WHEN 'varbinary' THEN -3 WHEN 'char' THEN 1 WHEN 'binary' THEN -2 WHEN 'time' THEN 92 WHEN 'timestamp' THEN 93 WHEN 'tinyint' THEN -6 WHEN 'year' THEN 91 ELSE 1111 END DATA_TYPE, UCASE(IF(COLUMN_TYPE LIKE '%(%)%', CONCAT (SUBSTRING(COLUMN_TYPE,1, LOCATE('(',COLUMN_TYPE) - 1), SUBSTRING(COLUMN_TYPE,1+ LOCATE(')',COLUMN_TYPE))), COLUMN_TYPE)) TYPE_NAME, CASE COLUMN_TYPE WHEN 'time' THEN 8 WHEN 'date' THEN 10 WHEN 'datetime' THEN 19 WHEN 'timestamp' THEN 19 ELSE IF(NUMERIC_PRECISION IS NULL, LEAST(CHARACTER_MAXIMUM_LENGTH,2147483647), NUMERIC_PRECISION) END COLUMN_SIZE, 65535 BUFFER_LENGTH, NUMERIC_SCALE DECIMAL_DIGITS, 10 NUM_PREC_RADIX, IF(IS_NULLABLE = 'yes',1,0) NULLABLE,COLUMN_COMMENT REMARKS, COLUMN_DEFAULT COLUMN_DEF, 0 SQL_DATA_TYPE, 0 SQL_DATETIME_SUB, LEAST(CHARACTER_OCTET_LENGTH,2147483647) CHAR_OCTET_LENGTH, ORDINAL_POSITION, IS_NULLABLE, NULL SCOPE_CATALOG, NULL SCOPE_SCHEMA, NULL SCOPE_TABLE, NULL SOURCE_DATA_TYPE, IF(EXTRA = 'auto_increment','YES','NO') IS_AUTOINCREMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_SCHEMA = 'jira') AND (1 = 1) AND (1 = 1) ORDER BY TABLE_CAT, TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION
          Hide
          george George El Boustani added a comment -

          This is the call we are making:
          columns = metaData.getColumns(null, schemaPattern, null, null);

          Show
          george George El Boustani added a comment - This is the call we are making: columns = metaData.getColumns(null, schemaPattern, null, null);
          Hide
          wlad Vladislav Vaintroub added a comment - - edited

          I see. You should be using catalog - the first parameter. In ConnectorJ, as well as (now, consistently) in mariadb-java-client, MySQL database(schema) is a JDBC catalog, whereas JDBC's schema is never used.,

          Show
          wlad Vladislav Vaintroub added a comment - - edited I see. You should be using catalog - the first parameter. In ConnectorJ, as well as (now, consistently) in mariadb-java-client, MySQL database(schema) is a JDBC catalog, whereas JDBC's schema is never used.,
          Hide
          wlad Vladislav Vaintroub added a comment -

          implemented nullCatalogMeansCurrent feature, since you're relying on this.

          Show
          wlad Vladislav Vaintroub added a comment - implemented nullCatalogMeansCurrent feature, since you're relying on this.

            People

            • Assignee:
              wlad Vladislav Vaintroub
              Reporter:
              george George El Boustani
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 3 hours
                3h