Bug #65213 Connector/J does not retrieve the table comment in a InnoDB table

• Submitted: 5 May 2012 17:53
• Modified: 28 Jun 2012 11:18
• Reporter: Nicola Mascheroni
• Status: Not a Bug
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: MySQL 5.5.22 - Connector/J 5.1.20
• OS: Windows (Vista SP 2 Home Premium)
• Assigned to: Alexander Soklakov
• CPU Architecture: Any
• Tags: Connector/J, java, Remaks, table

[5 May 2012 17:53] Nicola Mascheroni

Description:
Trying to retrieving the table comment for a InnoDB table with Connector/J you get only a blank value.

How to repeat:
Create an InnoDB table and giv it a comment.

Connect to MySQL via JDBC (Connector/J 5.1.20).

Try to retrieve the table comment with something like this:

String tableComment = new String(yourResultSet.getBytes("REMARKS"), "UTF-8");

[5 May 2012 20:09] Nicola Mascheroni

I think you can try to correct this:

class: com.mysql.jdbc.DatabaseMetaDataUsingInfoSchema

method: getTables (row 1482)

row: 1599

try
  new Field("", "REMARKS", java.sql.Types.VARCHAR, 255) });
instead of
  new Field("", "REMARKS", java.sql.Types.VARCHAR, 0) });

[11 May 2012 17:30] Sveta Smirnova

Thank you for the report.

Verified as described.

Test case for testsuite:

import testsuite.BaseTestCase;
import java.sql.ResultSet;
import java.sql.DatabaseMetaData;

public class bug65213 extends BaseTestCase {
	
	public bug65213(String name) {
		super(name);
	}

	public static void main(String[] args) {
		junit.textui.TestRunner.run(bug65213.class);
	}

	public void testBug65213() throws Exception {
		try {
			this.stmt.execute("DROP  TABLE IF EXISTS table_1");
			this.stmt.execute("CREATE TABLE table_1(f1 int, f2 timestamp, f3 timestamp) comment='foobar'");
			DatabaseMetaData dmd = this.conn.getMetaData();
			ResultSet rs  = dmd.getTables(null, "test", "table_1", null);
			assertTrue("1 column must be found", rs.next());
				assertEquals("Can not retrieve table comment", rs.getString("REMARKS"), "foobar");
		} finally {
//	    	closeMemberJDBCResources();
		}
	}
}

[26 Jun 2012 11:31] Valeriy Kravchuk

Bug #65705 was marked as a duplicate of this one.

[27 Jun 2012 11:26] Alexander Soklakov

Sveta,

default value of "useInformationSchema" connection property is "false". That means you get a DatabaseMetaData object instead of DatabaseMetaDataUsingInfoSchema in test case above and results of SHOW FULL TABLES, which doesn't contains REMARKS.

Nicola,

do you use useInformationSchema=true?

[28 Jun 2012 7:35] Alexander Soklakov

That works fine for me:

package testsuite.simple;

import testsuite.BaseTestCase;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.util.Properties;

import com.mysql.jdbc.DatabaseMetaDataUsingInfoSchema;

public class TestBug65213 extends BaseTestCase {
	
	public TestBug65213(String name) {
		super(name);
	}

	public static void main(String[] args) {
		junit.textui.TestRunner.run(TestBug65213.class);
	}

	public void testBug65213() throws Exception {
	
		this.stmt.execute("DROP  TABLE IF EXISTS testBug65213");
		createTable("testBug65213", "(f1 int, f2 timestamp, f3 timestamp) comment='foobar'", "InnoDB");

		Connection is_conn = null;
	
		try {
			Properties props = new Properties();
			props.setProperty("useInformationSchema", "true");

			is_conn = getConnectionWithProps(props);

			DatabaseMetaData dmd = is_conn.getMetaData();
			assertTrue(dmd instanceof DatabaseMetaDataUsingInfoSchema);
			
			this.rs  = dmd.getTables(null, "test", "testBug65213", null);
			assertTrue("1 column must be found", rs.next());
			assertEquals("Can not retrieve table comment", rs.getString("REMARKS"), "foobar");

		} finally {
			if (is_conn != null) {
				is_conn.close();
			}

		}
	
	}
}

[28 Jun 2012 11:18] Sveta Smirnova

Alexander,

you are correct: this is my mistake and not a bug. User must use useInformationSchema=true

[21 Aug 2012 14:30] Alexander Soklakov

Bug#66442 was marked as a duplicate of this one.