Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

"Column has been unexpectedly deleted" error when change structure in different connections [DNET359] #364

Closed
firebird-automations opened this issue Dec 8, 2010 · 7 comments

Comments

@firebird-automations
Copy link

Submitted by: Anton Kononov (stgolem)

Votes: 2

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\(\);
            \}
        \}
    \}
\}

}

@firebird-automations
Copy link
Author

Commented by: Anton Kononov (stgolem)

Recently this is probably in server classic arhitecture bug. I'm not sure, move it if it does.

@firebird-automations
Copy link
Author

Modified by: Anton Kononov (stgolem)

environment: Windows 7, Win2008, Win2008R2, FB 2.5 SuperClassic Instance => Windows 7, Win2008, Win2008R2, FB 2.5 SuperClassic Instance, FB 2.5 and 2.1.3 Classic Instance

@firebird-automations
Copy link
Author

Commented by: Nicolas T (nftimmers)

this problem happens to me too, its very important solve

@firebird-automations
Copy link
Author

Commented by: @cincuranet

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.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Author

Commented by: Anton Kononov (stgolem)

Is there any changes to this in Firebird 3 server?

@firebird-automations
Copy link
Author

Commented by: @cincuranet

This not a correct place to ask. Ask here http://www.firebirdsql.org/en/mailing-lists/ .

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants