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

          Attachments

            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