Issue Details (XML | Word | Printable)

Key: DNET-359
Type: Bug Bug
Status: Resolved Resolved
Resolution: Won't Fix
Priority: Critical Critical
Assignee: Jiri Cincura
Reporter: Anton Kononov
Votes: 2
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
.NET Data provider

"Column has been unexpectedly deleted" error when change structure in different connections

Created: 08/Dec/10 06:01 AM   Updated: 24/Apr/13 11:54 AM
Component/s: ADO.NET Provider
Affects Version/s: 2.5.2, 2.6
Fix Version/s: None

Time Tracking:
Not Specified

Environment: Windows 7, Win2008, Win2008R2, FB 2.5 SuperClassic Instance, FB 2.5 and 2.1.3 Classic Instance


 Description  « Hide
1. We have some table TABLE1 contains one column ID (int)
2. Open 2 connections in different threads
3. Connection 1 - select some data from TABLE1, then close
4. Connection 2 - changes structure of TABLE1 adding column COL_1 (any type)
5. Connection 1 (open new)
6. Connection 2 - close
7. Connection 1 - insert data in TABLE1, gets error

Here is sample Code for console application:


using System;
using System.Collections.Generic;
using System.Text;
using FirebirdSql.Data.FirebirdClient;
using System.Threading;

namespace ConsoleApplication1
{
    class Program
    {
        static FbConnectionStringBuilder fbcs;
        static FbConnection cn1;
        static FbConnection cn2;
        static ManualResetEvent e1 = new ManualResetEvent(false);
        static ManualResetEvent e2 = new ManualResetEvent(false);
        static ManualResetEvent e3 = new ManualResetEvent(false);

        static void Main(string[] args)
        {
            fbcs = new FbConnectionStringBuilder();
            fbcs.ServerType = FbServerType.Default;
            fbcs.Database = @"C:\Temp\base.fdb"; //Path to sample DB
            fbcs.DataSource = "localhost";
            fbcs.Dialect = 3;
            fbcs.UserID = "SYSDBA";
            fbcs.Password = "masterkey";

            ThreadPool.QueueUserWorkItem(new WaitCallback(StartConnectionOne));
            ThreadPool.QueueUserWorkItem(new WaitCallback(DoInThread));

            Thread.Sleep(1000);

            e1.Set();

            Console.Read();
        }

        static void StartConnectionOne(object state)
        {
            cn1 = new FbConnection(fbcs.ConnectionString);
            cn1.Open();

            e2.WaitOne();

            using (var cmd = cn1.CreateCommand())
            {
                cmd.Transaction = cn1.BeginTransaction();

                try
                {
                    Console.WriteLine("Alter");
                    cmd.CommandText = "alter table TABLE1 add COL_1 varchar(255)";

                    cmd.ExecuteNonQuery();

                    cmd.Transaction.Commit();
                    Console.WriteLine("Alter success");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Alter error: " + ex.Message);
                    cmd.Transaction.Rollback();
                }
            }

            e3.Set();

        }

        static void DoInThread(object state)
        {
            cn2 = new FbConnection(fbcs.ConnectionString);
            cn2.Open();

            e1.WaitOne();

            using (var cmd = cn2.CreateCommand())
            {
                try
                {
                    Console.WriteLine("Select 1");
                    cmd.CommandText = "select * from TABLE1 order by ID";

                    using (var r = cmd.ExecuteReader())
                    {
                        while (r.Read())
                        {
                            Console.WriteLine(r.ToString());
                        }
                    }
                    Console.WriteLine("Select 1 success");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Select 1 error: " + ex.Message);
                }
            }

            cn2.Close();
            cn2.Dispose();

            e2.Set();

            e3.WaitOne();

            cn2 = new FbConnection(fbcs.ConnectionString);
            cn2.Open();

            cn1.Close();
            cn1.Dispose();

            using (var cmd = cn2.CreateCommand())
            {
                try
                {
                    Console.WriteLine("Select 2");
                    cmd.CommandText = "select * from TABLE1 order by COL_1";

                    using (var r = cmd.ExecuteReader())
                    {
                        while (r.Read())
                        {
                            Console.WriteLine(r.ToString());
                        }
                    }
                    Console.WriteLine("Select 2 success");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Select 2 error: " + ex.Message);
                }
            }

            using (var cmd = cn2.CreateCommand())
            {
                cmd.Transaction = cn2.BeginTransaction();

                try
                {
                    Console.WriteLine("Insert");
                    cmd.CommandText = "insert into TABLE1 (ID, COL_1) Values (1, '1234')";
                    
                    cmd.ExecuteNonQuery();

                    cmd.Transaction.Commit();
                    Console.WriteLine("Insert success");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Insert error: " + ex.Message);
                    cmd.Transaction.Rollback();
                }
            }
        }
    }
}


 All   Comments   Work Log   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Anton Kononov added a comment - 08/Dec/10 08:10 AM
Recently this is probably in server classic arhitecture bug. I'm not sure, move it if it does.

Nicolas T added a comment - 02/Sep/11 02:59 PM
this problem happens to me too, its very important solve

Jiri Cincura added a comment - 24/Apr/13 11:54 AM
This is expected behavior of SuperClassic/Classic architecture. Your code has connection pooling turned on, so the step "5. Connection 1 (open new)" will actually give you the same connection as used in "3. Connection 1" and this one was active before the alter happened. The metadata is in these architectures cached for connection and hence it fails.

After DDL changes you should close and open all other connections (or simply restart Firebird server) so the metadata are fresh.