Rusanov wrote :
>Hello, I'm use sapdb Version: 'Kernel 7.4.3 Build 027-123-048-452'
>I have problem, my database crash
>I sent you the source DB procedure LAB.add_usergroup, executing
>which the crash is happeneing. Having tested the procedure, I've found the
>reason of crash. If I delete call LAB.add_event, the crash is not
>happening.
>type: Error
>source: SAPDB:Lab
>category: Fast
>code: 18245
>date: 03.10.2003
>time: 18:34:00
>user: NT AUTHORITY\SYSTEM
>computer: EMELIAN
>Desc:
>SAPDB - Lab :
> - ERROR TID: 0x244 PID: 0x3DC MsgID: 18245
> Kernel aborts,
> (view diagnostic file: 'knldiag')
>type: error
>source: SAPDB:Lab
>category: Fast
>code: 18006
>date: 03.10.2003
>time: 18:34:00
>user: NT AUTHORITY\SYSTEM
>computer: EMELIAN
>Desc:
>SAPDB - Lab :
> - ERROR TID: 0x244 PID: 0x3DC MsgID: 18006
> EXCEPTION:0xc0000005 Addr:0x6a732d ( 0x1:0x658:0:0 )
>I create db proc
>CREATE DBPROC LAB.add_usergroup
> ( IN role VARCHAR(50)
> , IN group_name VARCHAR(50)
> , IN parent INT
> , OUT group_id INT
> )
>AS
>VAR parent_role VARCHAR(50); sql_cmd VARCHAR(255); log_msg VARCHAR (255);
>log_type VARCHAR (255);
>SET log_type = 'info';
>SET log_msg = '';
>TRY
> TRY
> select user_login from LAB.lab_users
> where user_id = :parent;
> FETCH INTO :parent_role;
> CATCH
> IF $rc = 100 THEN STOP(5001, 'invalid argument parent ''' || parent || '''
>not found')
> ELSE STOP($rc, $errmsg);
> TRY
> select * from LAB.lab_users
> where user_name = :group_name;
> IF $rc = 0 THEN STOP(5002, 'invalid argument: group name ''' || group_name
>|| ''' already exists in system');
> CATCH
> IF $rc <> 100 THEN STOP($rc, $errmsg);
> TRY
> select * from DOMAIN.ROLES
> where role = UPPER(:role);
> IF $rc = 0 THEN STOP(5003, 'invalid argument: role name ''' || role || '''
>already exists in database');
> CATCH
> IF $rc <> 100 THEN STOP($rc, $errmsg);
> TRY
> SET sql_cmd = 'create role ' || role;
> execute sql_cmd;
> CATCH
> STOP($rc, $errmsg);
> TRY
> SET sql_cmd = 'grant ' || parent_role || ' to ' || role;
> execute sql_cmd;
> CATCH
> STOP($rc, $errmsg);
> TRY
> SET sql_cmd = 'insert into LAB.lab_users (parent, group_flag, user_name,
>user_login) values (' || parent || ' , 1 , ''' || group_name || ''' , ''' ||
>role || ''')';
> execute sql_cmd;
> SET group_id = LAB.lab_users.CURRVAL;
> SET log_type = 'info';
> SET log_msg = 'The user group ''' || group_name || ''' || role || ''' ||
>role || ''' parent ''' || parent || ''' with group_id ''' || group_id || '''
>has been added successfully.';
> call LAB.add_event(:log_type, 'add_usergroup', 'LAB.add_usergroup',
>:log_msg);
> CATCH
> STOP($rc, $errmsg);
>CATCH
> BEGIN
> SET log_type = 'error';
> SET log_msg = 'The user group ''' || group_name || ''' || role || ''' ||
>role || ''' parent ''' || parent || ''' has not been added. Error ''' || $rc
>|| ''', errmsg ''' || $errmsg || '''';
> call LAB.add_event(:log_type, 'add_usergroup', 'LAB.add_usergroup',
>:log_msg);
> STOP($rc, $errmsg);
> END;
>CREATE DBPROC LAB.add_event
> ( IN type VARCHAR(10)
> , IN event VARCHAR(25)
> , IN source VARCHAR(50)
> , IN comment VARCHAR(2000)
> )
>AS
>VAR node VARCHAR(50);
>SET node = 'N/A';
>TRY
> TRY
> SELECT APPLNODEID
> FROM SYSDBA.TRANSACTIONS T, DOMAIN.CONNECTPARAMETERS C
> WHERE T.SESSION = C.SESSION;
> FETCH INTO :node;
> CATCH
> IF $rc <> 100 THEN STOP($rc, $errmsg);
> INSERT LAB.lab_eventlog(source, type, event, event_user, computer,
>event_time, comment)
> values(:source, :type, :event, USER, :node, timestamp, :comment);
>CATCH
> STOP($rc);
>CREATE TABLE LAB.lab_eventlog
>(
>id INT NOT NULL DEFAULT SERIAL(1) ,
> record_time timestamp NOT NULL DEFAULT TIMESTAMP ,
> source VARCHAR(50) UNICODE NOT NULL ,
> type VARCHAR(10) UNICODE NOT NULL ,
> event VARCHAR(25) UNICODE NOT NULL ,
> event_user VARCHAR(50) UNICODE NOT NULL DEFAULT 'N/A' ,
> computer VARCHAR(50) UNICODE NOT NULL DEFAULT 'N/A' ,
> event_time timestamp NULL ,
> comment VARCHAR(2000) UNICODE NULL ,
> raw_data LONG UNICODE NULL ,
> PRIMARY KEY(id),
>CONSTRAINT type IN ('info','warning','error') ,
> FOREIGN KEY ref2event (event)
> REFERENCES LAB.lab_events (event)
>)
>///////////////////////////////////////////////////
>// TABLE LAB.lab_events
>CREATE TABLE LAB.lab_events
>(
> event VARCHAR(25) UNICODE NOT NULL ,
> event_name VARCHAR(100) UNICODE NOT NULL ,
> event_desc VARCHAR(255) UNICODE NULL ,
> audit INT NOT NULL DEFAULT 1 ,
> PRIMARY KEY(event) ,
> CONSTRAINT unique_event_name
> UNIQUE (event_name)
>)
>///////////////////////////////////////////////////
>// events
>insert lab_events (event, event_name, event_desc)
> values ('add_usergroup' , 'add user group in system', 'add user group in
>system')
>insert lab_events (event, event_name, event_desc)
> values ('drop_usergroup' , 'remove user group from system', 'remove user
>group from system')
>insert lab_events (event, event_name, event_desc)
> values ('add_user' , 'add user in system', 'add user in system')
>insert lab_events (event, event_name, event_desc)
> values ('drop_user' , 'remove user from system', 'remove user from system')
>CREATE TABLE LAB.lab_users
>(
> user_id INT NOT NULL DEFAULT SERIAL(1),
> parent INT NOT NULL ,
> group_flag INT NOT NULL ,
> user_name VARCHAR(50) UNICODE NOT NULL,
> user_login VARCHAR(50) UNICODE NOT NULL,
> PRIMARY KEY(user_id),
> CONSTRAINT group_flag IN (0, 1),
> CONSTRAINT unique_user_name
> UNIQUE (user_name),
> CONSTRAINT unique_user_login
> UNIQUE (user_login)
>)
>insert LAB.lab_users (parent, group_flag, user_name, user_login)
> values (0, 1, 'Users of laboratory', 'role_users_of_laboratory')
>create role role_users_of_laboratory
>create_lab_script
>@echo off
>rem #################################################################
>rem #
>rem #create example database TST with:
>rem # - 20 MB data volume and 8 MB log volume
>rem # - demo database user test (with password test)
>rem ##################################################################
>rem # IND -> independent program path
>rem # DEP -> dependent path
>rem # values will be set by installer, if it isn't set correctly - doit
>yourself right (replace with "$")
>set PATH=c:\program files\sapdb\indep_prog\bin;c:\program
>files\sapdb\indep_prog\pgm;%PATH%
>set INSTPATH="c:\program files\sapdb\depend"
>rem name of the database
>set SID=LAB
>rem directory root for volumes (data,log)
>set DATA=%SystemDrive%\data
>rem start remote communication server
>x_server stop
>x_server start
>rem stop and drop probably existing demo database
>dbmcli -d %SID% -u dbm,dbm db_offline > NUL
>dbmcli -d %SID% -u dbm,dbm db_drop > NUL
>rem create new demo database
>dbmcli -R %INSTPATH% db_create %SID% dbm,dbm
>rem create directory where to put the database files
>md %DATA% > NUL
>md %DATA%\%SID% > NUL
>rem setup database parameters
>echo param_rmfile > param.tmp
>echo param_startsession >> param.tmp
>echo param_init OLTP >> param.tmp
>echo param_put CAT_CACHE_SUPPLY 300 >> param.tmp
>echo param_put CACHE_SIZE 3000 >> param.tmp
>echo param_put MAXDATADEVSPACES 5 >> param.tmp
>echo param_put RESTART_SHUTDOWN AUTO >> param.tmp
>echo param_checkall >> param.tmp
>echo param_commitsession >> param.tmp
>echo param_addvolume 1 DATA %DATA%\%SID%\DISKD0001 F 2560 >> param.tmp
>echo param_addvolume 1 LOG %DATA%\%SID%\DISKL001 F 1024 >> param.tmp
>type param.tmp | dbmcli -d %SID% -u dbm,dbm
>rem startup database
>dbmcli -d %SID% -u dbm,dbm db_start
>rem initialize database files
>echo util_connect dbm,dbm > param.tmp
>echo util_execute init config >> param.tmp
>echo util_activate dba,dba >> param.tmp
>type param.tmp | dbmcli -d %SID% -u dbm,dbm
>rem load database system tables
>dbmcli -d %SID% -u dbm,dbm load_systab -u dba,dba -ud domain
>rem create database demo user
>echo sql_connect dba,dba > param.tmp
>echo sql_execute CREATE USER lab PASSWORD lab DBA NOT EXCLUSIVE >> param.tmp
>type param.tmp | dbmcli -d %SID% -u dbm,dbm
>echo medium_put data %DATA%\%SID%\datasave FILE DATA 0 8 YES > param.tmp
>echo medium_put auto %DATA%\%SID%\autosave FILE AUTO >> param.tmp
>echo util_connect dbm,dbm >> param.tmp
>echo backup_save data >> param.tmp
>echo autosave_on >> param.tmp
>type param.tmp | dbmcli -d %SID% -u dbm,dbm
>del param.tmp
>perl 5.8
>script
>#####################################################################
>#
>use English;
>use strict;
>use subs;
>use DBI qw(:sql_types);
>my $dbi = DBI->connect("DBI:ODBC:LAB", "lab", "lab") or die "connecting:
>$DBI::errstr";
>my $sql = "call LAB.add_usergroup('role_biochemistry', 'biochemistry', 1,
>?)";
>my $sth = $dbi->prepare($sql)
> or die "preparing: $DBI::errstr";
>my $group = undef;
>$sth->bind_param_inout(1, \$group, 4);
>$sth->execute
> or die "executing: err = $DBI::err, errstr = $DBI::errstr";
>print "group = ", $group, "\n";
>$sth->finish;
>$dbi->disconnect;
>exit;
The crash has been caused by 2 problems :
1. there's a bug in the db-procedure compiler, which produces wrong code for the
exception
handling of dynamic sql statements.
2. due to a memory management problem memory has been released too early.
We will fix these problems with one of the next versions.
Thanks for reporting the bug,
Thomas
--
Thomas Anhaus
SAP DB, SAP Labs Berlin
[EMAIL PROTECTED]
http://www.sapdb.org/
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
