| Bug #27030 | Binding a GridView to a DataSet containing SHOW PROCESSLIST leaves out columns | ||
|---|---|---|---|
| Submitted: | 11 Mar 2007 12:02 | Modified: | 20 Mar 2007 9:45 |
| Reporter: | Luca Leonardo Scorcia | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.0.5 (server 5.0.22-community-nt-log) | OS: | Windows (Windows XP Pro) |
| Assigned to: | CPU Architecture: | Any | |
[15 Mar 2007 10:35]
Tonci Grgin
Hi Luca and thanks for your report. At the first glance I don't think there's an error in connector but rather VS Grid is unable to process System.Byte[] fields (see Bug#26993): C:\mysql507\bin>mysql -T -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.0.38-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show processlist; Field 1: `Id` Catalog: `def` Database: `` Table: `` Org_table: `` Type: LONGLONG Collation: binary (63) Length: 11 Max_length: 1 Decimals: 0 Flags: NOT_NULL BINARY NUM Field 2: `User` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 16 Max_length: 4 Decimals: 31 Flags: NOT_NULL BINARY Field 3: `Host` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 64 Max_length: 14 Decimals: 31 Flags: NOT_NULL BINARY Field 4: `db` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 64 Max_length: 4 Decimals: 31 Flags: BINARY Field 5: `Command` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 16 Max_length: 5 Decimals: 31 Flags: NOT_NULL BINARY Field 6: `Time` Catalog: `def` Database: `` Table: `` Org_table: `` Type: LONG Collation: binary (63) Length: 7 Max_length: 1 Decimals: 0 Flags: NOT_NULL UNSIGNED BINARY NUM Field 7: `State` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 30 Max_length: 0 Decimals: 31 Flags: BINARY Field 8: `Info` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 100 Max_length: 16 Decimals: 31 Flags: BINARY +----+------+----------------+------+---------+------+-------+------------------ + | Id | User | Host | db | Command | Time | State | Info | +----+------+----------------+------+---------+------+-------+------------------ + | 5 | root | localhost:1555 | test | Query | 0 | NULL | show processlist | +----+------+----------------+------+---------+------+-------+------------------ + 1 row in set (0.00 sec)
[15 Mar 2007 10:50]
Luca Leonardo Scorcia
Hi! I was not aware of the -T option, today I learnt something useful :) I see your point, the connector behavior is perfectly reasonable with these additional info. However this call worked correctly with all the previous versions of the connector - is this behavior the result of some fix in the recent release? I will be investigating for a workaround. In the meanwhile, do you have any reasonable explanation about why those field are returned as binary? :)
[15 Mar 2007 15:31]
Tonci Grgin
Probably caused by Bug#10491.
[15 Mar 2007 19:11]
Luca Leonardo Scorcia
I have been able to create a subclass of GridView that overcomes this problem.
For anybody interested, it's just a few lines of code:
/// <summary>
/// Allows binding of byte arrays in a GridView
/// </summary>
public class CustomGridView: GridView
{
public CustomGridView()
{
this.RowDataBound += grid_OnRowDataBound;
}
public override bool IsBindableType(Type type)
{
return (type == typeof(byte[])) || base.IsBindableType(type);
}
public void grid_OnRowDataBound(object sender, GridViewRowEventArgs e)
{
GridViewRow row = e.Row;
if (row.RowType != DataControlRowType.DataRow) return;
for (int i = 0; i < row.Cells.Count; ++i)
{
DataRowView rowView = (DataRowView)row.DataItem;
if (rowView[i] is byte[])
{
// Special treatment for byte arrays
row.Cells[i].Text = System.Text.Encoding.UTF8.GetString((byte[])rowView[i]);
}
}
}
}
For me it's enough, I consider this bug closed and solved. Thanks for your time, Tonci.
[20 Mar 2007 9:45]
Tonci Grgin
This problem is caused by Bug#10491 and represents grave issue for connectors. They can't work around it, at least not reliably. Ad hoc user queries like this do not allow connector to distinguish between "SHOW CREATE TABLE", where it should treat BIANRY as UTF8, and "SELECT varbinary_col FROM some_table", where it really should be binary... Luca, thanks for your workaround.
Description: When binding a GridView with AutoGenerateColumns="True" to a dataset that has been populated with the command SHOW PROCESSLIST, the gridview displays only the numeric columns (ID and Time). Everything else is not shown. How to repeat: Create a new C# Web Application and add the reference to the MySql.Data assembly. In the default.aspx put this line: <asp:GridView ID="dgTest" runat="server" AutoGenerateColumns="True" /> In the default.aspx.cs put these lines: protected void Page_Load(object sender, EventArgs e) { DataSet ds = new DataSet(); using (MySqlConnection conn = new MySqlConnection("database=yourtestdb;server=yourtestserver;uid=youretestuser;pwd=yourtestpassword;Port=3306;")) { MySqlCommand cmd = new MySqlCommand("SHOW PROCESSLIST", conn); MySqlDataAdapter da = new MySqlDataAdapter(cmd); da.Fill(ds); dgTest.DataSource = ds; dgTest.DataBind(); } } Run the page, you'll only see two columns being generated. Suggested fix: Using the DataSet visualizer in VS.net debugger shows the other fields are in the DataSet, but these show as byte arrays and probably the GridView does not know how to display them. Shouldn't these be strings?