TZSQLMetaData mdExportedKeys = access violation?

[aehimself]

Is it just me, or TZSQLMetaData on MySQL, selecting mdExportedKeys throws an AV?

The issue is in ZDbcMySQLMetaData.pas : 2000 (TZMySQLDatabaseMetadata.UncachedGetExportedKeys)

Code: Select all

      ColumnIndexes[1] := FindColumn('Type');

The query what the metadata executes is "SHOW TABLE STATUS FROM tablename" and this query does not return a column named "Type". At least on MySQL 8:

Capture.PNG

Considering that in line 2006 it compares if it's InnoDB, shouldn't that column name be Engine instead? Or it used to be type, now renamed and we should check?

Opinions?

[marsupilami]

I checked with an old MySQL 4.0 manual. In MySQL 4 the second column was named "Type" and is expected to show the engine used. In these manuals they talked about table types rather than engines.

I suggest someting like this:

Code: Select all

      ColumnIndexes[1] := FindColumn('Engine');
      if not assigned(ColumnIndexes[1]) then
        ColumnIndexes[1] := FindColumn('Type');

I don't know when they changed the column name between 4.0 and 5.6. I have no other archived documentation. MariaDB also uses the name "Engine" in their current documentation.

[marsupilami]

I found a copy of the MySQL 5.0 manual. The column is called "Engine" there too: https://docs.oracle.com/cd/E19078-01/my ... ble-status

I assume, they introduced it with version 5.0 then.

[marsupilami]

I think I fixed the problem. Could you please test it? The fix should be available on Github too.

[aehimself]

Well, great. I just realized that the command in the metadata is not correct, and INFORMATION_SCHEMA.KEY_COLUMN_USAGE should be used.
So I changed Imported keys and Exported keys, was about to come to ask if I should check them in, and... :)

Well, I forgot to subscribe to the topic so I wasn't informed about the message...

Edit: I think it won't throw an exception but data won't be returned. If I run the command SHOW TABLE STATUS FROM xxx on MySQL 8, the "Comment" field is always null which would be needed for key extraction.
The query I ended up using was

Code: Select all

SELECT CONSTRAINT_CATALOG, REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Can you please check if this can be executed on earlier versions? If no, we have to make a split according to version number as the old query is not suitable for newer versions for sure.

[aehimself]

I stumbled upon this SO question, where someone, 5 years ago, on MySQL 5.6 was complaining about the speed of a query against INFORMATION_SCHEMA.key_column_usage.
This makes me quite comfortable that this entry was present above V5.
I don't know if IMPORTED KEYS and EXPORTED KEYS metadata ever worked on MySQL but the complex splitting and assembling makes me believe that if it did, it was a really early version.

What would you say, if I'd make 2 internal versions of each of these methods and run my query above V5 and the old version below it?

P.s.: Cross reference also seems to be affected, after getting rid of the AV no data is returned. If someone can tell me what it's supposed to do, I can take a look if I can fix that one aswell.

Edit: This reference shows that in MySQL 5.0 this table already existed.

[aehimself]

I opened a pull request, seems TZSQLMeataData on MySQL is not that commonly used by the community - as even the AV went undetected for who knows how many years.

I opened a pull request on GitHub which fixes the Imported and Exported keys lookup on MySQL 5+.
I also forgot that the MySQL protocol is also the home of MariaDB connections so I included that check in addition to the host version. I do believe that this was unnecessary though as the first MariaDB was forked from MySQL 5.1 and therefore should contain the table I'm running the query against. Anyway I wanted to keep the code as close to the original as possible. If the metadata is not working on any MariaDB installation we'll just have to replace "Self.IsMySQL And" with "Self.IsMariaDB Or' and we are set.

Maybe you can add that as a comment in the source before applying the patch.

Let's see what Jenkins and our users will say.

[marsupilami]

I suggest to use the new functions for MariaDB as well for the reasons you mentioned. Could you maybe modify the pull request accordingly?

Let's see what Jenkins and our users will say.

Jenkins doesn't do tests currently. I planned to do some changes that will help me in saving energy. One of them is to not have the Jenkins servers run 24/7 while they are simple idle. This is one of the reasons why I moved the Jenkins instance to a raspberry PI (so it continues serving pages). The idea was to start up the Jenkins slaves only once per day and to only keep them running while tests are running.

[aehimself]

Added a second commit to the same PR, now MariaDB should use the new queries too.
Any idea what Cross reference should show? That one seems to be broken on MySQL as well. If I know what should be there, I could try to fix that one aswell.

[marsupilami]

Added a second commit to the same PR, now MariaDB should use the new queries too.

Thank you

Any idea what Cross reference should show? That one seems to be broken on MySQL as well. If I know what should be there, I could try to fix that one aswell.

The only clue I have is that we derive from JDBC. I assume that the following documentation is the best one will find:
Android documentation on DatabaseMetaData.getCrossReference

Another option might be to check the output of any of these drivers: dblib (uses MS stored procedure to generate the output), ODBC for any driver you trust to implement it correctly (generates the output based on the SQLForeignKeysW function call). OLEDB and ADO are also candidates - depending on how much you trust that code

So basically MS SQL Server or is the only reliable source I know of. Maybe it makes sense to take a look at the MariaDB JDBC driver source - depending on their license?

[aehimself]

"Retrieves a description of the foreign key columns in the given foreign key table that reference the primary key or the columns representing a unique constraint of the parent table (could be the same or a different table)."

This is also the ONLY method using the Foreign* properties of the metadata.
To me it sounds like that cross reference needs 2 tables and displays ALL direct connections between them.

Did I parse this correctly?

[marsupilami]

Yes and no. I think this is a list of all foreign keys in a database. GetExportedKeys and GetImported Keys in ODBC and dblib simply call GetCrossReference with some parameters being empty.
So I assume, that GetCrossReference without any filtering will list all foreign keys and that giving filter expressions could allow to list only the foreign keys between two tables or two schemas or ...

The MariaDB Connector/J implementatation can be seen here:
https://github.com/mariadb-corporation/ ... aData.java

I assume it is ok to copy the SQL statement from them. They use the LGPL and we use an LGPL with an linking exception.

[aehimself]

Well, I just tried cross reference with DBLib and it returned the same as exported keys, only results were filtered on the foreign table as well.
Cross reference (based on pure user expectation) would list ALL connections of ALL tables but no protocol I am using (Oracle, MSSQL, MySQL) does that currently.
This is why I currently need two metadata objects (Imported and Exported keys) to collect incoming and outgoing relations for a table in my project.

In the mean time I corrected some minor issues in MySQLMetadata's Imported and Exported keys and also made Cross reference to show the same data as MSSQL dblib. Pull request is available, as usual.

[marsupilami]

Well, I just tried cross reference with DBLib and it returned the same as exported keys, only results were filtered on the foreign table as well.
Cross reference (based on pure user expectation) would list ALL connections of ALL tables but no protocol I am using (Oracle, MSSQL, MySQL) does that currently.

Yes - basically that is what it does. It is like GetExportedKexys - only you can filter on the foreign table as well.

This is why I currently need two metadata objects (Imported and Exported keys) to collect incoming and outgoing relations for a table in my project.

Unfortunately it seems there is no metadata call to get imported and exported keys of one table at the same time.

In the mean time I corrected some minor issues in MySQLMetadata's Imported and Exported keys and also made Cross reference to show the same data as MSSQL dblib. Pull request is available, as usual.

I applied the patch. It should be available on github too.

[aehimself]

I don't know if it actually worth it, but PK name can also be determined with a single join in imported, exported keys and cross reference. I included them just for the heck of it, but they always return 'PRIMARY' so idk :)

If you wish to include it, it's in a new pull request.

And now I stop with the metadata thing :)