MariaDB Client Library for Java Applications
  1. MariaDB Client Library for Java Applications
  2. CONJ-16

MySQLDatabaseMetaData.getColumns does not respect the catalog

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: jdbc-1.1.1
    • Labels:
      None
    • Global Rank:
      2433

      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.

        Activity

        Hide
        Vladislav Vaintroub added a comment - - edited

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

        Show
        Vladislav Vaintroub added a comment - - edited Could you please tell what are you passing to the function for 'catalog' parameter?
        Hide
        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 El Boustani added a comment - We are passing in the name of the catalog, the database name. So in this case "jira"
        Hide
        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
        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 El Boustani added a comment -

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

        Show
        George El Boustani added a comment - This is the call we are making: columns = metaData.getColumns(null, schemaPattern, null, null);
        Hide
        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
        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
        Vladislav Vaintroub added a comment -

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

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

          People

          • Assignee:
            Vladislav Vaintroub
            Reporter:
            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