Listing tables not implemented

[bib1963]

Near perfect, but...

Making a query such as "SHOW TABLES" fails, but there is a C API call which is not implemented, mysql_list_tables(). We can seem to list columns just fine.

Perhaps, it can be updated, it's only been 5 years with ne'er a problem until now.

[jrmarino]

okay, surprising. do these alternaives work?

SELECT table_name FROM information_schema.tables;

SELECT table_name FROM information_schema.tables where table_schema='mysql';

see https://www.poftut.com/how-to-list-mysql-tables-with-show-tables-sql-query/

[bib1963]

Yup, completely weird.

When I tried it earlier, I looked at the network dump from both my program & using the mysql client. Both seemed to make the call with exactly the same data. It's just that through Ada, it got rejected from the server.

So, I tried:

      S1                   : constant String := "SELECT table_name FROM information_schema.tables where table_schema='richman';";
      Num_Rows             :          AdaBase.Affected_Rows;
[..]
      Num_Rows := DR_Richman.execute (S1);

And it returned 0, even though there were 2 tables and therefore 2 rows, and it does not matter if I restrict it my schema or not.

When I look at the tcpdump, it actually comes back with an error stating "table 'richman.tables' doesn't exist and is confirmed by Last_Driver_Message call..

And yet, if I plug that request into the mysql client, it does return with a list of rows without error...

MariaDB [richman]> SELECT table_name FROM information_schema.tables where table_schema='richman';
+------------+
| table_name |
+------------+
| settings   |
| settings_1 |
+------------+
2 rows in set (0.000 sec)

If I create a string with "SELECT * FROM settings" within richman, prepare & execute function normally and return the correct number of rows.

If I change that string to "SELECT table_name FROM information_schema.tables where table_schema='richman'", then it fails with: raised ADABASE.CONNECTION.BASE.MYSQL.INITIALIZE_FAIL : Failed to prepare SQL statement 'SELECT table_name FROM information_schema.tables where table_schema='richman''

I'm struggling...

[bib1963]

I think this is a problem with the mysql/maria client library as it also seems to have been seen with the Java connector. Looking at the network trace for the mysql cli client, it does seem to do a number of requests on startup which AdaBase does not, perhaps that has something to do with it. I do think the mysql_list_tables() call should be implemented.

[bib1963]

And some more info...

When the mysql cli client starts, it make the following requests, which AdaBase does not: select * from information_schema.schemata show tables

Then for each table: show fields from

and finally: select @@version_comment limit 1

It seems like AdaBase really does not like selecting anything from the information_schema database, since that firdt select above also fails.

jrmarino commented 3 years ago

so you think selecting from information_schema.tables is broken at the mysql/mariadb client library level? That would make sense as to why it's failing in AdaBase.

bib1963 commented 3 years ago

Yes and no...

If you look at https://stackoverflow.com/questions/1814408/mysql-driver-issues-with-information-schema - it's actually python, not Java.

I think you have to bypass the normal API route for this table, and go about it a bit more raw, if you see what I mean.

Or, as the above link mentioned... client_caps &= ~CAPS.NO_SCHEMA.

bib1963 commented 3 years ago

Hmmmm...

/usr/include/mysql/mariadb_com.h:#define CLIENT_NO_SCHEMA          16   /* Don't allow database.table.column */
/usr/include/mysql/mariadb_com.h:                                 CLIENT_NO_SCHEMA |\

jrmarino commented 3 years ago

so I did a little research and CLIENT_NO_SCHEMA is one of the mysql connection options I believe. https://dev.mysql.com/doc/c-api/8.0/en/mysql-real-connect.html

It could be added to adabase-connection-base-mysql.adb

What I don't understand is that the CLIENT_NO_SCHEMA flag is unset now. description "Do not permit db_name.tbl_name.col_name syntax. This is for ODBC. It causes the parser to generate an error if you use that syntax, which is useful for trapping bugs in some ODBC programs."

So right now, the db_name.tbl_name.col_name syntax is permitted.

I'm not opposed to have a list-tables function but I'd like to understand why the query method is failing and fix that as well.

On Thu, Aug 26, 2021 at 6:19 AM Bob Goddard @.***> wrote:

Hmmmm...

/usr/include/mysql/mariadb_com.h:#define CLIENT_NO_SCHEMA 16 / Don't allow database.table.column / /usr/include/mysql/mariadb_com.h: CLIENT_NO_SCHEMA |\

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/jrmarino/AdaBase/issues/3#issuecomment-906316726, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAISZ5UYA4RLQ3FA7COZU6LT6YPM5ANCNFSM5CYXM4NA . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email .

bib1963 commented 3 years ago

I thought CLIENT_NO_SCHEMA flag is set to true by default, in which case we have to set it to false.

I have coded it up within AdaBase, but I still get a "Failed to prepare SQL statement" error, regardless whether it is true or false. I'm still looking.

jrmarino commented 3 years ago

Ah, re-reading your stackoverflow, I think it's saying the mysql server itself has CLIENT_NO_SCHEMA set. Which means the client's setting is ignored. Maybe you can double check your mysql server settings to confirm that theory.

On Thu, Aug 26, 2021 at 8:35 AM John Marino (Github) @.***> wrote:

so I did a little research and CLIENT_NO_SCHEMA is one of the mysql connection options I believe. https://dev.mysql.com/doc/c-api/8.0/en/mysql-real-connect.html

It could be added to adabase-connection-base-mysql.adb

What I don't understand is that the CLIENT_NO_SCHEMA flag is unset now. description "Do not permit db_name.tbl_name.col_name syntax. This is for ODBC. It causes the parser to generate an error if you use that syntax, which is useful for trapping bugs in some ODBC programs."

So right now, the db_name.tbl_name.col_name syntax is permitted.

I'm not opposed to have a list-tables function but I'd like to understand why the query method is failing and fix that as well.

On Thu, Aug 26, 2021 at 6:19 AM Bob Goddard @.***> wrote:

Hmmmm...

/usr/include/mysql/mariadb_com.h:#define CLIENT_NO_SCHEMA 16 / Don't allow database.table.column / /usr/include/mysql/mariadb_com.h: CLIENT_NO_SCHEMA |\

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/jrmarino/AdaBase/issues/3#issuecomment-906316726, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAISZ5UYA4RLQ3FA7COZU6LT6YPM5ANCNFSM5CYXM4NA . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email .

bib1963 commented 3 years ago

This is truly horrible...

I don't think there is a problem, it's just how the db works...

If I set compression on, I can call SELECT table_name FROM information_schema.tables where table_schema='richman' and it works.

If compression is off, it fails. The setting for CLIENT_NO_SCHEMA makes no difference. Indeed, eventually finding a doc which talks about it, that only applies to ODBC!

Looking at the tcpdump, when the client make the call, no matter what we set it to, it always ends up as true.

So, I think it is a case of my bad, no problem found.

Ah well, if nothing else, at least we know there is still someone behind the code 5 years after the last commit.

jrmarino commented 3 years ago

Interesting. I want to double check constants and make sure we don't have a case of the compression flag doing something other than compression. But I'm glad you can proceed now.

• © Githubissues.
• Githubissues is a development platform for aggregating issues.

[jrmarino]

so you think selecting from information_schema.tables is broken at the mysql/mariadb client library level? That would make sense as to why it's failing in AdaBase.

[bib1963]

Yes and no...

If you look at https://stackoverflow.com/questions/1814408/mysql-driver-issues-with-information-schema - it's actually python, not Java.

I think you have to bypass the normal API route for this table, and go about it a bit more raw, if you see what I mean.

Or, as the above link mentioned... client_caps &= ~CAPS.NO_SCHEMA.

[bib1963]

Hmmmm...

/usr/include/mysql/mariadb_com.h:#define CLIENT_NO_SCHEMA          16   /* Don't allow database.table.column */
/usr/include/mysql/mariadb_com.h:                                 CLIENT_NO_SCHEMA |\

[jrmarino]

so I did a little research and CLIENT_NO_SCHEMA is one of the mysql connection options I believe. https://dev.mysql.com/doc/c-api/8.0/en/mysql-real-connect.html

It could be added to adabase-connection-base-mysql.adb

What I don't understand is that the CLIENT_NO_SCHEMA flag is unset now. description "Do not permit db_name.tbl_name.col_name syntax. This is for ODBC. It causes the parser to generate an error if you use that syntax, which is useful for trapping bugs in some ODBC programs."

So right now, the db_name.tbl_name.col_name syntax is permitted.

I'm not opposed to have a list-tables function but I'd like to understand why the query method is failing and fix that as well.

On Thu, Aug 26, 2021 at 6:19 AM Bob Goddard @.***> wrote:

Hmmmm...

/usr/include/mysql/mariadb_com.h:#define CLIENT_NO_SCHEMA 16 / Don't allow database.table.column / /usr/include/mysql/mariadb_com.h: CLIENT_NO_SCHEMA |\

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/jrmarino/AdaBase/issues/3#issuecomment-906316726, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAISZ5UYA4RLQ3FA7COZU6LT6YPM5ANCNFSM5CYXM4NA . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email .

[bib1963]

I thought CLIENT_NO_SCHEMA flag is set to true by default, in which case we have to set it to false.

I have coded it up within AdaBase, but I still get a "Failed to prepare SQL statement" error, regardless whether it is true or false. I'm still looking.

[jrmarino]

Ah, re-reading your stackoverflow, I think it's saying the mysql server itself has CLIENT_NO_SCHEMA set. Which means the client's setting is ignored. Maybe you can double check your mysql server settings to confirm that theory.

On Thu, Aug 26, 2021 at 8:35 AM John Marino (Github) @.***> wrote:

so I did a little research and CLIENT_NO_SCHEMA is one of the mysql connection options I believe. https://dev.mysql.com/doc/c-api/8.0/en/mysql-real-connect.html

It could be added to adabase-connection-base-mysql.adb

What I don't understand is that the CLIENT_NO_SCHEMA flag is unset now. description "Do not permit db_name.tbl_name.col_name syntax. This is for ODBC. It causes the parser to generate an error if you use that syntax, which is useful for trapping bugs in some ODBC programs."

So right now, the db_name.tbl_name.col_name syntax is permitted.

I'm not opposed to have a list-tables function but I'd like to understand why the query method is failing and fix that as well.

On Thu, Aug 26, 2021 at 6:19 AM Bob Goddard @.***> wrote:

Hmmmm...

/usr/include/mysql/mariadb_com.h:#define CLIENT_NO_SCHEMA 16 / Don't allow database.table.column / /usr/include/mysql/mariadb_com.h: CLIENT_NO_SCHEMA |\

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/jrmarino/AdaBase/issues/3#issuecomment-906316726, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAISZ5UYA4RLQ3FA7COZU6LT6YPM5ANCNFSM5CYXM4NA . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email .

[bib1963]

This is truly horrible...

I don't think there is a problem, it's just how the db works...

If I set compression on, I can call SELECT table_name FROM information_schema.tables where table_schema='richman' and it works.

If compression is off, it fails. The setting for CLIENT_NO_SCHEMA makes no difference. Indeed, eventually finding a doc which talks about it, that only applies to ODBC!

Looking at the tcpdump, when the client make the call, no matter what we set it to, it always ends up as true.

So, I think it is a case of my bad, no problem found.

Ah well, if nothing else, at least we know there is still someone behind the code 5 years after the last commit.

[jrmarino]

Interesting. I want to double check constants and make sure we don't have a case of the compression flag doing something other than compression. But I'm glad you can proceed now.