csql3.3bug

Help
2011-06-09
2013-05-15
  • Dear Sir,

    I tested CSQL 3.3 (Open source version), and found a bug when joining two
    tables. The details are as follows:

    Two tables:T1(A int,B int,C int) T2(C int, D int)
    Five tuples in each table:
              T1:(0,1,2)(1,2,3)(2,3,4)(3,4,5)(4,5,6)
              T2:(0,1)(1,2)(2,3)(3,4)(4,5)
    Three join statements:
            A) select A,D from T1,T2 where T1.A=T2.C
            B) select A,T2.D from T1,T2 where T1.A=T2.C
            C) select T1.A,D from T1,T2 where T1.A=T2.C
    The result:
    A) CSQL>select A,D from T1,T2 where T1.A=T2.C


    A D

    144149184 1
    144149184 2
    144149184 3
    144149184 4
    144149184 5
    B) CSQL>select A,T2.D from T1,T2 where T1.A=T2.C


    A T2.D

    144146936 1
    144146936 2
    144146936 3
    144146936 4
    144146936 5
    C) CSQL>select T1.A,D from T1,T2 where T1.A=T2.C


    T1.A D

    0 1
    1 2
    2 3
    3 4
    4 5

    The system seems to require the first attribute in the select clause
    to be in the form tableName.Fieldname. Is it a bug?

     
  • SABIR P S
    SABIR P S
    2013-03-26

    Hi prabtuty

    i am using csql3.3 and  cached my table from mysql to csql and its working fine, but i need bidirectional cache to work ,i set biderctionalcahce  variable as true in csql.conf and updations in csql table is propagating to target database but  updations in target database is not  reflecting in csql table. please help me in solving this
     

     
  • SABIR P S
    SABIR P S
    2013-03-26

    Hi  Bijay

    i created log table as follows

    CREATE TABLE `csql_log_int` (
      `tablename` char(64) DEFAULT NULL,
      `pkid` int(11) DEFAULT NULL,
      `operation` int(11) DEFAULT NULL,
      `cacheid` int(11) DEFAULT NULL,
      `id` int(11) NOT NULL AUTO_INCREMENT,
      UNIQUE KEY `id` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

    i put the   trigger also ..like this
    use test;
    drop trigger if exists triggerinsertp1;
    drop trigger if exists triggerupdatep1;
    drop trigger if exists triggerdeletep1;

    DELIMITER |
    create trigger triggerinsertp1
    AFTER INSERT on p1
    FOR EACH ROW
    BEGIN
    Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('p1', NEW.f1, 1,1);
    End;

    my csql.table.conf looks like this
    1 p1 NULL NULL NULL myodbc3

    But still iam facing same problem of propagating  target db to csql

    cacheverify -t p1

    Number of Records:
    -------------+-------------+-------------+
      Data             |  In CSQL          |  In TargetDB      |
    -------------+-------------+-------------+
      No. Of Records   |  3                |  4                |
    -------------+-------------+-------------+

    please hep me to solve this

    Thanks
    sabcsql

    create trigger triggerupdatep1
    AFTER UPDATE on p1
    FOR EACH ROW
    BEGIN
    Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('p1', OLD.f1, 2,1);
    Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('p1', NEW.f1, 1,1);
    End;

    create trigger triggerdeletep1
    AFTER DELETE on p1
    FOR EACH ROW
    BEGIN
    Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('p1', OLD.f1, 2,1);
    End;
    |

    i performed one insertion query like insert into p1 values (4,'check');

    after executing this query  ,my log table shows the result like this .
    +-------+----+-------+------+---+
    | tablename | pkid | operation | cacheid | id |
    +-------+----+-------+------+---+
    | p1        |    4 |         1 |       1 |  2 |
    +-------+----+-------+------+---+

     
  • Hi Sabir,

    How did you started csqlserver?

    It should be in -c mode.

    csqlserver -c

    If you started like this. Can you please paste the error log of csqlserver -c here.(if any)

    Regards,
    Bijaya

     
  • SABIR P S
    SABIR P S
    2013-03-26

    Hi Bijaya

    i am starting by running  startup.sh in  csql3.3-cache-linux-x86/scripts
    sh startup.sh
    should i need to start csqlserver -c manually for working bidirectional cache

    Thanks
    sabcsql

     
  • SABIR P S
    SABIR P S
    2013-03-26

    Hi Bijaya

    I started csqlserver by using csqlserver -c ,
    and inserted one record in my target database .and now logs are coming like this
    logtable is like this

    select * from csql_log_int;
    +-------+----+-------+------+---+
    | tablename | pkid | operation | cacheid | id |
    +-------+----+-------+------+---+
    | p1        |    1 |         1 |       1 |  1 |
    +-------+----+-------+------+---+

    Checking for cache updates
    26/03/2013 18:45:33.907255: 3267:3086489296:csqlcacheserver.cxx:485:Log record table:p1 PK:1 RowID:1 not deleted from the target db -14
    Checking for cache updates
    26/03/2013 18:45:43.908308: 3267:3086489296:csqlcacheserver.cxx:485:Log record table:p1 PK:1 RowID:1 not deleted from the target db -14
    26/03/2013 18:45:48.138925: 3268:3086300880:csqlcheckpointserver.cxx:52:Redo log file size 204
    Checking for cache updates
    26/03/2013 18:45:53.908356: 3267:3086489296:csqlcacheserver.cxx:485:Log record table:p1 PK:1 RowID:1 not deleted from the target db -14
    Checking for cache updates
    26/03/2013 18:46:03.908452: 3267:3086489296:csqlcacheserver.cxx:485:Log record table:p1 PK:1 RowID:1 not deleted from the target db -14
    Checking for cache updates
    26/03/2013 18:46:13.909491: 3267:3086489296:csqlcacheserver.cxx:485:Log record table:p1 PK:1 RowID:1 not deleted from the target db -14
    Checking for cache updates
    26/03/2013 18:46:23.910546: 3267:3086489296:csqlcacheserver.cxx:485:Log record table:p1 PK:1 RowID:1 not deleted from the target db -14
    Checking for cache updates
    26/03/2013 18:46:33.911586: 3267:3086489296:csqlcacheserver.cxx:485:Log record table:p1 PK:1 RowID:1 not deleted from the target db -14
    Checking for cache updates
    26/03/2013 18:46:43.912639: 3267:3086489296:csqlcacheserver.cxx:485:Log record table:p1 PK:1 RowID:1 not deleted from the target db -14

    Still csql is not reflected

    cacheverify -t p1

    Number of Records:
    -------------+-------------+-------------+
      Data             |  In CSQL          |  In TargetDB      |
    -------------+-------------+-------------+
      No. Of Records   |  0                |  1                |
    -------------+-------------+-------------+

    how to solve this ?
    thanks
    Sabir

     
  • SABIR P S
    SABIR P S
    2013-03-27

    Hi bijay ,

    please help me to solve this problem

    Checking for cache updates 26/03/2013 18:45:33.907255: 3267:3086489296:csqlcacheserver.cxx:485:Log record table:p1 PK:1 RowID:1 not deleted from the target db -14

    is showing while starting in csqlserver -c mode
    but data is not propagating from target db to mysql

    Thanks

     
  • Hi sabir,

    It looks like, it is not able to delete the old log from target db.

    Can you enable error logging to file in config file and restart server with c option and provide error log file,

    Here is the config parameter

    #Log level 0->no logging 1->Fine 2->Finer 3->Finest
    LOG_LEVEL=3

    #Give full path for the log file where important system actions are stored.
    LOG_FILE=/tmp/csql/log/log.out

    We are re-checking this issue and update soon.

     
  • SABIR P S
    SABIR P S
    2013-03-27

    Hi bijaya

    Thanks for your updation

    I modified csql.conf to enable log level as 3 and i started in csqlserver -c mode

    I inserted one record in my target db mysql table CT and it  looks  like

    select * from CT;
    +---+----+
    | f1 | f2   |
    +---+----+
    |  1 | T001 |
    +---+----+

    and my log table is  now like
    select * from csql_log_int;
    ----+----+-------+------+---+
    | tablename | pkid | operation | cacheid | id |
    +-------+----+-------+------+---+
    | CT        |    1 |         1 |       1 |  1 |
    +-------+----+-------+------+---+

    but its not  updating in CSQL cache table.

    and now the error log is

    27/03/2013 14:11:20.749578:10994:3086104272:csqlcacheserver.cxx:478:INSERT CT 1
    27/03/2013 14:11:20.749724:10994:3086104272:SqlOdbcConnection.cxx:361:Transaction Rollback
    27/03/2013 14:11:20.749747:10994:3086104272:SqlOdbcStatement.cxx:437:CloseCursor
    27/03/2013 14:11:20.749780:10994:3086104272:SqlOdbcStatement.cxx:437:CloseCursor
    27/03/2013 14:11:20.749833:10994:3086104272:SqlOdbcConnection.cxx:350:Transaction Committed
    27/03/2013 14:11:20.749861:10994:3086104272:SqlOdbcStatement.cxx:603:Statement Freed
    27/03/2013 14:11:20.749883:10994:3086104272:SqlOdbcStatement.cxx:603:Statement Freed
    27/03/2013 14:11:30.750411:10994:3086104272:SqlOdbcStatement.cxx:1011:NumParams 0
    27/03/2013 14:11:30.750454:10994:3086104272:SqlOdbcStatement.cxx:1028:Statement prepared SELECT * FROM csql_log_int where cacheid = 1;
    27/03/2013 14:11:30.750493:10994:3086104272:SqlOdbcStatement.cxx:83:NumParams 1
    27/03/2013 14:11:30.750513:10994:3086104272:SqlOdbcStatement.cxx:136:Statement prepared DELETE from csql_log_int where id=?;
    27/03/2013 14:11:30.750540:10994:3086104272:csqlcacheserver.cxx:470:Row value is Table:CT PK:1 OP:1 CID:1

    27/03/2013 14:11:30.750613:10994:3086104272:csqlcacheserver.cxx:478:INSERT CT 1
    27/03/2013 14:11:30.750756:10994:3086104272:SqlOdbcConnection.cxx:361:Transaction Rollback
    27/03/2013 14:11:30.750778:10994:3086104272:SqlOdbcStatement.cxx:437:CloseCursor
    27/03/2013 14:11:30.750810:10994:3086104272:SqlOdbcStatement.cxx:437:CloseCursor
    27/03/2013 14:11:30.750863:10994:3086104272:SqlOdbcConnection.cxx:350:Transaction Committed
    27/03/2013 14:11:30.750891:10994:3086104272:SqlOdbcStatement.cxx:603:Statement Freed
    27/03/2013 14:11:30.750912:10994:3086104272:SqlOdbcStatement.cxx:603:Statement Freed

    i think its not able to delete the  old record from log table using prepared statement

    my linux version is  Red Hat Enterprise Linux Server release 5.4

    and i installed following rpms for mysqlodbcdriver

    mysql-connector-odbc-3.51.12-2.2.i386.rpm
    libltdl3-1.5.26-24.1.src.rpm

    is there any problem with mysqlodbc connectivity driver ,because i am not able to perform db operations using prepared stament in jdbc:gateway mode using my java program ,
    when i am using java.sql.Statment its working fine.

    I hope u can solve this issue by seeing this error log .

    Thanks
    Sabir

     
  • Yes, It looks like there is some issue in mysql odbc or csql odbc code.
    For the parametric statement odbc not able to execute.

    We are planing to replicated the issue in locat lab and update you.
    By the way, which mysql version you are using.

    Regards,
    Bijaya

     
  • SABIR P S
    SABIR P S
    2013-03-27

    ok

    mysql version which i am   using is  5.1.56-ndb-7.1.18-cluster-gpl-log
    hope u will update the solution .

    Thanks
    Sabir

     
  • Hi Sabir,

    I have checked this issue with my local copy. It is working fine. In my system odbc version is 3.51.12.

    I suggest you to create a sample ODBC program with parametric statement and run in your system to check the actual issue.

    If you want I can provide a sample program executable to check why parametric statement are not executing  in your system . if you want the executable then let me know  your email id.

    Regards,
    Bijaya

     
  • SABIR P S
    SABIR P S
    2013-03-28

    Hi Bijaya,

    Thanks for your valuable replies.

    I changed the version of mysql odbc connector driver  and now my version is mysql odbc 5.1
    So prepared statement is working and bidirectional cache also working .
    Thanks Bijaya

    I am stucked in concurrency ,while i am running multiple  threads i am getting the error as

    Unable to get the prepare mutex

    jvm is dumping  the error

    A fatal error has been detected by the Java Runtime Environment:
    #
    #  SIGSEGV (0xb) at pc=0x6eaa7b3c, pid=26682, tid=1859840912
    #
    # JRE version: 6.0_24-b07
    # Java VM: Java HotSpot(TM) Server VM (19.1-b02 mixed mode linux-x86 )
    # Problematic frame:
    # C    _ZN14ProcessManager8addMutexEP5Mutexi+0x3c
    #
    # If you would like to submit a bug report, please visit:
    #   http://java.sun.com/webapps/bugreport/crash.jsp
    # The crash happened outside the Java Virtual Machine in native code.
    # See problematic frame for where to report the bug.

    csql3.3cache is supporting concurrency or not .?
    Is there any other higher version which supports concurrency  ?

    Please guide me to solve this problem because my application  works  using threads.

    Thanks Sabir