MySQL Bugs: #81056: Connector/J with Fabric always returns lower_case_table

Bug #81056	Connector/J with Fabric always returns lower_case_table_names false
Submitted:	12 Apr 2016 21:05	Modified:	14 Sep 2016 14:02
Reporter:	Robert Sandiford (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.38	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any
Tags:	Connector/J, fabric, lower_case_table_names

Description:
Running a MySQL Fabric fronted cluster with one primary and 2 secondary servers.  All three of the servers in the cluster were created with lower_case_table_names = 1.

When we connect through Fabric with the Connector/J, the call to connection.getMetaData().supportsMixedCaseIdentifiers() returnd true.

When we direct connect to the server with Connector/J (i.e. not with Fabric), the call to connection.getMetaData().supportsMixedCaseIdentifiers() returned false. 

The base problem is the lowerCaseTableNames() function in FabricMySQLConnectionProxy is hardcoded to return false.  

So, for example, any (third party) code that relies on that value being accurate fails.

How to repeat:
Here's a quick program.  Connects to the same server - first is through Fabric, the second is direct.  (arch-3 is the primary)

public class FabricConnect {

    
    public static void main(String[] args) {
        try {
            Connection conn = DriverManager.getConnection(
                    "jdbc:mysql:fabric://arch-4.bcc.sirsi.pvt:32274/cure?fabricUsername=admin&fabricPassword=MyNewPass4!&fabricServerGroup=my_group",
                    "root",
                    "MyNewPass4!");

            DatabaseMetaData metadata = conn.getMetaData();
            
            boolean caseInsensitive = metadata.supportsMixedCaseIdentifiers();
            
            System.out.println("supports mixed case = " + caseInsensitive);
            
            Connection conn2 = DriverManager.getConnection(
                    "jdbc:mysql://arch-3.bcc.sirsi.pvt:3306/cure",
                    "root",
                    "MyNewPass4!"
            );
            
            DatabaseMetaData metadata2 = conn2.getMetaData();
            
            boolean caseInsensitive2 = metadata2.supportsMixedCaseIdentifiers();
            
            System.out.println("supports mixed case = " + caseInsensitive2);
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Note: Code in the 6.0 branch still has this hardcoded to return false.

Hello Robert Sandiford,

Thank you for the bug report.
Verified based on internal discussion with dev's.

Thanks,
Chiranjeevi.

Strongly related to bug #91072

Observation: These two bugs (and others we've found and not reported) are symptoms of a larger issue - specifically that the FabricMySQLConnectionProxy class has many, many methods that are just stubbed - returning false or null and having no actual action.

Suggested resolution: 
Rather than fixing just these two specific bugs, please make a pass through the class and implement all the methods.  We did a few temporary local fixes in FabricMySQLConnectionProxy based on code in the (non-Fabric) class ConnectionImpl - i.e. the jdbc implementation for direct connections.

We understand that there's an 'interesting' conundrum with the Fabric class - specifically, since Fabric fronts multiple servers (primary and secondaries), and can fail over or change the topology, then WHICH of the servers should govern things like properties?  As a couple of suggestions:
1) Make the assumption (and document!) that all servers need to have the same setup parameters (such as the lower_case_table_names value).  That way, getting the properties from ANY server would work no matter what connection is actually active.
2) Get the properties and cache them by server.  Then, when a request for a property value is made, get the current server, get the properties from the cache for that server (or initialize if not already present), and return the appropriate value for that server.

Thanks!

Bob.

p.s. the third problem we ran into was that getProperties() returns null.  At that point, we gave up trying to make things work, recognizing the significant number of unimplemented methods in FabricMySQLConnectionProxy.

FabricMySQLConnectionProxy.java - with our patches - in case you're interested. Based on 5.1.38 tag.

Attachment: FabricMySQLConnectionProxy.java (application/octet-stream, text), 104.94 KiB.

Note: Re the attached FabricMySQLConnectionProxy - to make it compile, you also have to change EscapeProcessor and EscapeProcessorResult to make them explicitly 'public' classes, as well as changing the instance variable 'escapedSql' in EscapeProcessorResult to be 'public'.

Posted by developer:
 
Added the following entry to the Connector/J 5.1.40 changelog:

"In a Fabric setup, getMetaData().supportsMixedCaseIdentifiers() always returned “true,” even if lower_case_table_names was set to “1” on the servers. This was because FabricMySQLConnectionProxy.lowerCaseTableNames() was not implemented properly. With this fix, the function now returns the proper value for the current active connection, so that getMetaData().supportsMixedCaseIdentifiers() also returns the correct value.

Users are reminded that in a Fabric setup, lower_case_table_names, as well as all other settings, should be configured the same way for all servers in the Fabric server group."