Biml Studio 2024处理MySQL 8.0 longtext类型时报错求助

报错原因 #

Biml Studio 2024内置的MySQL元数据解析组件存在兼容性bug：当读取包含longtext类型字段的MySQL表时，解析逻辑会异常中断，导致GetDatabaseSchema方法返回的TableNodes集合为空。代码中直接调用ElementAt(0)访问空集合的第一个元素，触发索引越界错误。

此前BimlExpress 2019的元数据解析逻辑可正确处理MySQL的longtext类型，新版本因驱动更新、类型映射规则调整引入了该问题。

临时解决办法 #

• 脚本增加空值校验
  在访问TableNodes前先判断集合是否为空，避免直接索引访问：

  <#
      string target_schema = "source_schema";
      var includedSchemas = new List<string>{target_schema};
      
      string table_name = "sales_order_item_test_longtext";
      var includedTables = new List<string>{table_name};
          
      var sourceMetaConnection = RootNode.DbConnections["Source"];
      var sourceMetadata = sourceMetaConnection.GetDatabaseSchema(includedSchemas, includedTables,
          ImportOptions.None);        
      var sourceTable = sourceMetadata.TableNodes.Count > 0 ? sourceMetadata.TableNodes.ElementAt(0) : null;
  #>
  
  <#= "<!-- sourceMetadata.SchemaNodes count: " + sourceMetadata.SchemaNodes.ToArray().Count().ToString() + " -->" #>
  <#= "<!-- sourceMetadata.TableNodes count: " + sourceMetadata.TableNodes.ToArray().Count().ToString() + " -->" #>
  
  <Biml xmlns="http://schemas.varigence.com/biml.xsd"></Biml>
  

• 临时修改测试表结构
  在测试环境中将longtext字段临时改为text类型，待元数据读取完成后再改回：

  ALTER TABLE `sales_order_item_test_longtext` MODIFY COLUMN `product_options` text COMMENT 'Product Options';
  

• 手动定义表元数据
  绕过自动读取逻辑，直接在Biml中手动定义表结构：

  <Biml xmlns="http://schemas.varigence.com/biml.xsd">
      <Connections>
          <Connection Name="Source" ConnectionString="your_mysql_connection_string" Provider="MySql"/>
      </Connections>
      <Databases>
          <Database Name="SourceDb" ConnectionName="Source" SchemaName="source_schema">
              <Tables>
                  <Table Name="sales_order_item_test_longtext">
                      <Columns>
                          <Column Name="item_id" DataType="Int32" IsNullable="False" IsIdentity="True"/>
                          <Column Name="product_options" DataType="String" Length="-1" IsNullable="True"/>
                      </Columns>
                      <Keys>
                          <PrimaryKey Name="PK_sales_order_item_test_longtext">
                              <Columns>
                                  <Column ColumnName="item_id"/>
                              </Columns>
                          </PrimaryKey>
                      </Keys>
                  </Table>
              </Tables>
          </Database>
      </Databases>
  </Biml>
  

兼容旧版本行为的方案 #

• 自定义元数据读取逻辑
  绕过Biml内置的GetDatabaseSchema方法，直接通过ADO.NET查询MySQL的information_schema获取表结构，自行构建Biml表节点：

  <#@ import namespace="System.Data" #>
  <#@ import namespace="MySql.Data.MySqlClient" #>
  <#
      string connString = RootNode.DbConnections["Source"].ConnectionString;
      string tableName = "sales_order_item_test_longtext";
      string schemaName = "source_schema";
      
      List<ColumnNode> columns = new List<ColumnNode>();
      
      using (MySqlConnection conn = new MySqlConnection(connString))
      {
          conn.Open();
          string sql = @"
              SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, EXTRA 
              FROM INFORMATION_SCHEMA.COLUMNS 
              WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName
              ORDER BY ORDINAL_POSITION;
          ";
          using (MySqlCommand cmd = new MySqlCommand(sql, conn))
          {
              cmd.Parameters.AddWithValue("@SchemaName", schemaName);
              cmd.Parameters.AddWithValue("@TableName", tableName);
              using (MySqlDataReader reader = cmd.ExecuteReader())
              {
                  while (reader.Read())
                  {
                      string colName = reader["COLUMN_NAME"].ToString();
                      string dataType = reader["DATA_TYPE"].ToString();
                      bool isNullable = reader["IS_NULLABLE"].ToString() == "YES";
                      string extra = reader["EXTRA"].ToString();
                      
                      ColumnNode col = new ColumnNode();
                      col.Name = colName;
                      col.IsNullable = isNullable;
                      
                      // 映射MySQL类型到Biml数据类型
                      switch(dataType)
                      {
                          case "int":
                              col.DataType = DataType.Int32;
                              col.IsIdentity = extra.Contains("auto_increment");
                              break;
                          case "longtext":
                              col.DataType = DataType.String;
                              col.Length = -1;
                              break;
                          // 可按需添加其他类型映射
                      }
                      
                      columns.Add(col);
                  }
              }
          }
      }
      
      TableNode customTable = new TableNode();
      customTable.Name = tableName;
      customTable.Columns.AddRange(columns);
  #>
  

• 回退到BimlExpress 2019开发
  若项目允许，暂时切换回BimlExpress 2019进行开发，等待Varigence修复Biml Studio 2024的该兼容性bug。

内容的提问来源于stack exchange，提问作者Mike