MySQL Bugs: #13128: show variables not returning a number

Bug #13128	show variables not returning a number
Submitted:	13 Sep 2005 3:32	Modified:	20 Sep 2005 9:14
Reporter:	Ricardo Guevara	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	4.1.14	OS:	Windows (windows xp)
Assigned to:		CPU Architecture:	Any

Description:
In Visual Basic 6, I queried the database's variables, and the return value is not a number. This behavior is only present in version 4.1.14, I tried it in 4.1.13 and 4.1.12 and it returned the value in a long variable.

How to repeat:
Using this code and mysql 4.1.14.

Private Sub Command1_Click()
    Dim cnx As ADODB.Connection
    Dim rst As ADODB.Recordset
        
    Set cnx = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    cnx.CursorLocation = adUseClient
    cnx.Open "DRIVER={MySQL ODBC 3.51 Driver};" _
        & "SERVER=localhost;" _
        & "DATABASE=test;" _
        & "UID=root;" _
        & "PWD=password;" _
        & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
    
    rst.Open "show variables like 'max_allowed_packet'", cnx
    MsgBox rst.Fields("Value")
    '1048576 returned in version 4.1.12 and 4.1.13
    '???? returned in version 4.1.14
End Sub

the variable that i queried was max_allowed_packet

Changed category because everything works fine for me on XP in mysql client:

mysql> show variables like 'max%';
+----------------------------+------------+
| Variable_name              | Value      |
+----------------------------+------------+
| max_allowed_packet         | 1048576    |
| max_binlog_cache_size      | 4294967295 |
| max_binlog_size            | 1073741824 |
| max_connect_errors         | 10         |
| max_connections            | 100        |
| max_delayed_threads        | 20         |
| max_error_count            | 64         |
| max_heap_table_size        | 16777216   |
| max_insert_delayed_threads | 20         |
| max_join_size              | 4294967295 |
| max_length_for_sort_data   | 1024       |
| max_relay_log_size         | 0          |
| max_seeks_for_key          | 4294967295 |
| max_sort_length            | 1024       |
| max_tmp_tables             | 32         |
| max_user_connections       | 0          |
| max_write_lock_count       | 4294967295 |
+----------------------------+------------+
17 rows in set (0.16 sec)

mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.18 sec)

I have the same problem with other variables (basedir, datadir): the returned value IS NOT a string (not with the correct encoding).

The following ASP code reproduces the bug (MyODBC 3.52.10 plus MySQL 4.1.14):

<%@ language="javascript" %>
<%
  var strConnectionString = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=mydb;Option=2;User ID=mylogin;Password=mypassword;";
  var objConnection = Server.CreateObject("ADODB.Connection");
  objConnection.Open(strConnectionString);
  var objRecordset = objConnection.Execute("SHOW VARIABLES LIKE 'datadir';");
%>
objRecordset("Variable_name").Value = "<%=Server.HTMLEncode(objRecordset("Variable_name").Value)%>"<br/>
objRecordset("Value").Value = "<%=Server.HTMLEncode(objRecordset("Value").Value)%>"<br/>

This page displays invalid characters (in fact - looks like chinese chars!)... 
Note that this bug is visible only with MySQL 4.1.14 as the back-end (it works fine on 4.1.13 with same MyODBC driver)...

Thanks for the bug report. max_allowed_packet is returned like array of bytes. 
I attached my test case.

Test case

Attachment: 13128.zip (application/x-zip-compressed, text), 5.98 KiB.

Right now I'm using this work around:
Each byte of the array of bytes that it returns has to be converted using the chr function and must be concat into a string, the i convert the string to a Long.

Public Function ByteArrayToString(bytArray() As Byte) As String
    Dim sAns As String
    Dim iPos As String
    
    sAns = StrConv(bytArray, vbUnicode)
    iPos = InStr(sAns, Chr(0))
    If iPos > 0 Then sAns = Left(sAns, iPos - 1)
    
    ByteArrayToString = sAns
 
 End Function

Dim b() As Byte
Dim str As String
        
b = rst.Fields("Value")
MsgBox ByteArrayToString(b)

Wax, please re-test w/ MySQL-4.1.15, as I've noticed this same issue in Connector/J, but only present in 4.1.14, as it appears to be a regression to fix the length reported for results from other "SHOW" commands (show columns, to be exact) that caused a regression, which was then fixed for 4.1.15.

Sorry...I was not able to reproduce the bug on 4.1.15. The bug was fixed. Thanks for bug report.