MySQL Bugs: #14868: Show Tables command returns half of the tables

Bug #14868	Show Tables command returns half of the tables
Submitted:	11 Nov 2005 16:32	Modified:	18 Nov 2005 18:50
Reporter:	Nathan Huebner	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	Latest	OS:	Windows (Windows Server 2003)
Assigned to:		CPU Architecture:	Any

Description:
show tables in server: Using the MyODBC Connector for Windows, using the "show tables" statement, returns half of the tables through the odbc connection. It appears the connection is dropped for some reason, and half of the tables are given. 

however, using the command tool for MySQL, show tables statement works fine, as well as PHP. The only workaround is to not use ODBC when using certain statements like this, as they will not return all of the needed information.

I consider this a bug, because .... well, it works through other methods except ODBC, and if it works through ODBC it will only return up to 4 tables, out of 7 tables, or 6 tables out of 11. See my point?

I would like to see this fixed in a future release if possible :)

How to repeat:
Create a database.
Create 11 tables, or more.
Install MyODBC

Using visual basic, I called on the MyODBC to return the following:

show tables in server;

server being the database i have.

it only returns the following:

books
dvd
music
finder
verify

It should have returned:

books
dvd
music
finder
verify
sales
orders
toys
sports
other
bags

?

:(

Using the ODBC tool odbcte32:

	Full Connect(Default)

	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

	Successfully connected to DSN 'mydb5'.
SQLExecDirect:
				In:				hstmt = 0x003B1B48, szSqlStr = "show tables", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"Tables_in_db5"
"tb01"
"tb02"
"tb03"
"tb04"
"tb05"
"tb06"
"tb07"
"tb08"
"tb09"
"tb10"
"tb11"
"tb12"
12 rows fetched from 1 column.

The same as client:

c:\mysql\bin>mysql -uroot db5
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.16-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables;
+---------------+
| Tables_in_db5 |
+---------------+
| tb01          |
| tb02          |
| tb03          |
| tb04          |
| tb05          |
| tb06          |
| tb07          |
| tb08          |
| tb09          |
| tb10          |
| tb11          |
| tb12          |
+---------------+
12 rows in set (0.00 sec)

Miguel,

Thank you for checking into this. I am unsure why you are unable to repeat it, however I can repeat it on any machine i go to. I know it isn't your job to fix my coding, however if this is a bug im sure you want to know about it.

You must use Visual Basic 6 for the MySQL ODBC Connection (MyODBC). I'm not sure why but Visual Basic seems to have a problem with ODBC and show tables, or in general it has trouble returning all of the data. Maybe it's my problem? I don't know.

a VB 6 project has been posted for you to test:

A repeatable example can be found here: (Visual Basic 6 coded)

http://www.sellchain.com/vb6test.zip

Sorry for the trouble, who knows what you will find.

Miguel,

please let me know what you find. as i said, I can repeat this on any computer, a million computers if i had the resources, with Visual Basic.

Thx.

Sorry I will take a look on your sample code when I will able for to
install Access on my computer unlucky my Office package was 
damaged and I will need to re-install all my Windows machine.

Thank you for the feedback.

Show tables on Access

Attachment: bug14868.PNG (image/x-png, text), 43.94 KiB.

Please see picture I attached to this bug report. With the below VB code
on Access 2003 I was unable to repeat:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
Dim MySQL_User As String
Dim MySQL_Password As String
Dim MySQL_Server As String
Dim MySQL_Database As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mydataset As String
MyCrLF = Chr(13) + Chr(10)

MySQL_User = "root"
MySQL_Password = ""
MySQL_Server = "localhost"
MySQL_Database = "vb"

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.CursorLocation = adUseClient
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=" & MySQL_Server & ";" _
& "DATABASE=" & MySQL_Database & ";" _
& "UID=" & MySQL_User & ";" _
& "PWD=" & MySQL_Password & ";" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

conn.Open
rs.Open "SHOW TABLES", conn
rs.MoveFirst
Do Until rs.EOF
  For Each fld In rs.Fields
    mydataset = mydataset & fld.Value & MyCrLF
  Next
    rs.MoveNext
Loop
rs.Close

Text3 = mydataset

Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
    
End Sub

So looks to me there is something wrong on your application or
the VB environment on your side.

Try this code. For me it works. It return 72 table names in ComboBox.
--------------------------------------
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub Form_Load()
Set con = New ADODB.Connection
con.ConnectionString = "Provider=MSDASQL;Driver=MySQL ODBC 3.51 Driver;Database=jtest;Server=localhost;UID=root;PWD=;"
con.Open
Set rs = New ADODB.Recordset
sq = "show tables"
With rs
.ActiveConnection = con
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.Open sq
End With
rs.MoveFirst
While Not rs.EOF
Combo1.AddItem rs(0)
rs.MoveNext
Wend
End Sub