ZDbcMySqlMetadata updates

[MJFShark]

Hi all!

I'm working on some changes to ZDbcMySqlMetadata (related to my ticket #15) and I wanted to check on a few things.

#1. The MySQL databases that I have access to (5.7 and 8.0 all on Ubuntu) don't include the text 'mysql' in the "show variables" result and so IsMySQL is always false and the fork is identified as fUnknown. I'm assuming this is not a "just me" problem and my proposal is to change the defaults to FIsMySQL=true and FMySQLFork = fMySQL (if not detected otherwise.)

#2: Change the get tables call to use INFORMATION_SCHEMA for any versions above 5.0.2. This one is a bit complicated by what the protocol considers a catalog vs a schema and I'm attempting to match the old behavior exactly (I do wonder if that could/should change for the future however as it seems like the MySQL protocol does it differently than the other zeos protocols I've used, I'll create a new topic for that.)

#3: Change the version check for procedures and proccolumns to 5.7 and above. The "proctable" version has problems with parameters that show charset or collation information and this change should solve that issue. It's possible that that version check could be 5.0.2 and above, but I have no way to test this.

I think those three changes are good ones and shouldn't cause any issues, but I'll appreciate any thoughts before I make a pull request on my changes.

Thanks! - Mark

[marsupilami]

Hello Mark,

#1. The MySQL databases that I have access to (5.7 and 8.0 all on Ubuntu) don't include the text 'mysql' in the "show variables" result and so IsMySQL is always false and the fork is identified as fUnknown. I'm assuming this is not a "just me" problem and my proposal is to change the defaults to FIsMySQL=true and FMySQLFork = fMySQL (if not detected otherwise.)

duh - I have to disagree. Why should Zeos prefer one fork over the other? I do understand, why you want to do that. I assume that RequiresInformationSchema doesn't get set correctly and metadata doesn't get read correctly because of this.
But maybe we should find a bettwer way to detect the database in use or find a way to detect if the information schema is available? Simply assuming that we are on MySQL most probably will lead to problems for others...

Best regards,

Jan

[marsupilami]

Note: The following link seems to suggest that the information schema will be listed a a separate schema when using show schemas. Maybe a better way is to check for its availability and use it if it is present?

https://smarttechways.com/2019/06/24/ch ... -in-mysql/

[MJFShark]

Hi Jan,

Agreed on using the existence of information_schema to decide to use it. That seems a much better choice. You should see that the metadata changes I pushed don't do the fork check since checking version > 5.0.2 is enough. Note that IsMySQL is also used for other things, although I'm not sure this particular version check has to be "fork specific".

FSupportsReadOnly := ( IsMariaDB and (GetHostVersion >= EncodeSQLVersioning(10,0,0))) or
( IsMySQL and (GetHostVersion >= EncodeSQLVersioning( 5,6,0)));

Really I was just trying to make a sane default choice to get around my particular issue (the mysql text not being part of the version info.)

-Mark

[MJFShark]

My earlier assumption that INFORMATION_SCHEMA was added in 5.0 appears to be incorrect. I have the MySQL 3.23, 4.0, 4.1 documentation pdf and it seems that it existed at least as far back as 4.0 (which apparently is when the "show databases" command was added?) Now I'm wondering how far back Zeos supports or needs to support. I do occasionally hear from people using Oracle 8, but I'm not sure in the MySQL world how common it is to use really old versions.

-Mark

[marsupilami]

Hello Mark,

EgonHugeist and me seem to remember that there were problems with the INFORMATION_SCHEMA in the 5.x versions of MySQL. Maybe also in MariaDB. I think it is a good idea to keep to the old metadata SQLs for these versions.

I assume the difference really only is important for any version greater than 5.x? I suggest to have a kinda 2-step approach:

1. Check the VERSION and VERSION_COMMENT variables for occurences of the String MySQL and MariaDB. If we do find one of these, we do know which Database we are connected to.
2. If we still are not sure what database we are connected to, we could use differences between MariaDB and MySQL to detect the server type. According to this page there are differences that we can use to check if we are on one of these databases. For checking if we are on MariaDB, we could check for the existance of any of the aria_* variables. I assume that the Aria storage engine is only available on MariaDB. From version 10.4 on it gets used as the default engine for system tables. To check wether we are on MySQL we possibly could check if there is a "new" variable. This variable only seems to exist on MySQL. I didn't check if it exists on version 8, but according to this part of the MySQL 8.0 Reference Manual that variable still exists.

What do you think?

Best regards,

Jan

[MJFShark]

Hi Jan,

If the cutoff for using INFORMATION_SCHEMA is version 8 and above, note that there's an issue with GetProcedureColumnsFromProcTable. It currently won't work for any procedure where the return value includes charset or collation information. The main reason for my changing the cutoff to 5.7 was to get around that issue since the infoschema version works in my 5.7 tests. If INFORMATION_SCHEMA isn't usable for 5.7 then that will have to be looked at.

As far as detecting MySQL or MariaDB goes, is it even needed? I might be missing something but the two places I see those IsMySQL and IsMariaDB variables being used are:

Code: Select all

    FSupportsReadOnly := ( IsMariaDB and (GetHostVersion >= EncodeSQLVersioning(10,0,0))) or
                         ( IsMySQL and (GetHostVersion >= EncodeSQLVersioning( 5,6,0)));

which I think is functionally equivalent to :

Code: Select all

    FSupportsReadonly := (GetHostVersion >= EncodeSQLVersioning( 5,6,0));

(Unless the checks are there because it should be false for other forks?)
and

Code: Select all

    if (sMyOpt = 'utf8') and (IsMariaDB or (GetHostVersion >= EncodeSQLVersioning(4,1,0)))

which I think is functionally equivalent to :

Code: Select all

    if (sMyOpt = 'utf8') and (GetHostVersion >= EncodeSQLVersioning(4,1,0))

-Mark