<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
Good morning all.<br>
<div class="moz-forward-container">
<p>On a busy server, I found a duplicate user table in iirelation
with duplicated records in iiattributes. The two entries have
different reltids.</p>
<p>There is no physical table or index corruption in the system
catalog tables. There have been no unusual events on the 24x7
server and no downtime. I found this:<br>
</p>
<p><a moz-do-not-send="true"
href="https://communities.actian.com/s/article/Why-Are-Duplicate-Rows-Allowed-in-iirelation-with-Ingres-II-10">https://communities.actian.com/s/article/Why-Are-Duplicate-Rows-Allowed-in-iirelation-with-Ingres-II-10</a></p>
<p>iirelation has a hash index on owner and name but it is not
marked as unique.</p>
<p>How does does Ingres guarantee uniqueness for tables and
columns - clearly it cannot.<br>
</p>
<p>I have a reproducible case lodged with Actian support and will
send an update when there is an outcome. Technical goodies
follow...<br>
</p>
<p><br>
</p>
<p>I captured the event with SET SERVER TRACE. On this server, I
have trace active all the time, with a script to rotate the log
directory daily and keeping a month archived. There is no
detectable impact on operations. I found this feature valuable
to help track down elusive problems after the fact. It provides
a useful daily summary / exceptions report.</p>
<p>The trace provided clear evidence (down to nanosecond timing)
to show there were two sessions connected with the same
credentials running the same OpenROAD application. It creates a
local user temp table for an enquiry function. The code is
faulty with the transaction running READ UNCOMMITTED and DBMS
error checking is missing for some statements. Where an error
is successfully captured, the ROLLBACK/COMMIT sequence is
faulty.<br>
</p>
<p>The first session is running a batch job like this:</p>
<pre>Check for table in iitables returns 1 row
drop table
create table as select...
commit
</pre>
<pre>a few seconds later
Check for table in iitables returns 0 rows
create table as select...
US07DA Duplicate object name '%0c'.
commit
</pre>
<pre>a few minutes later
Check for table in iitables returns 2 rows
drop table
create table as select...
US07DA Duplicate object name '%0c'.
commit
This last sequence repeats until the end of the job. The code can run with the duplicate tables.
</pre>
The second session:<br>
</div>
<div class="moz-forward-container">
<pre><pre>Check for table in iitables returns 1 row
drop table
create table as select...
E_US13EE Duplicate table name
rollback</pre>
</pre>
</div>
<div class="moz-forward-container">Ingres version is:<br>
<p><font size="-1" face="Courier New, Courier, monospace">Version:
II 11.1.0 (a64.lnx/100) + P15546</font></p>
<p><br>
</p>
<p>Duplicate table is here:<br>
</p>
<p><font size="-1" face="Courier New, Courier, monospace">* select
table_owner, varchar( table_name,20) tab, create_date,
table_reltid from iitables where table_owner= 'sbradfor'\g<br>
Executing . . .<br>
<br>
<br>
┌────────────────────────────────┬────────────────────┬─────────────────────────┬─────────────┐<br>
│table_owner │tab
│create_date │table_reltid │<br>
├────────────────────────────────┼────────────────────┼─────────────────────────┼─────────────┤<br>
│sbradfor │tmp_cur_pricelist
│2021_04_20 05:13:31 GMT │ 382408│<br>
│sbradfor │tmp_cur_pricelist
│2021_04_20 05:13:46 GMT │ 382414│<br>
└────────────────────────────────┴────────────────────┴─────────────────────────┴─────────────┘<br>
(2 rows)<br>
</font></p>
<p><font size="-1" face="Courier New, Courier, monospace">* select
table_owner, varchar( table_name,20) tab,
varchar(column_name,20) col from iicolumns where table_owner=
'sbradfor' order by column_name\g<br>
Executing . . .<br>
<br>
<br>
┌────────────────────────────────┬────────────────────┬────────────────────┐<br>
│table_owner │tab
│col │<br>
├────────────────────────────────┼────────────────────┼────────────────────┤<br>
│sbradfor │tmp_cur_pricelist
│catlog_code │<br>
│sbradfor │tmp_cur_pricelist
│catlog_code │<br>
│sbradfor │tmp_cur_pricelist
│cust_code │<br>
│sbradfor │tmp_cur_pricelist
│cust_code │<br>
│sbradfor │tmp_cur_pricelist
│disc_percent │<br>
│sbradfor │tmp_cur_pricelist
│disc_percent │<br>
│sbradfor │tmp_cur_pricelist
│price │<br>
│sbradfor │tmp_cur_pricelist
│price │<br>
└────────────────────────────────┴────────────────────┴────────────────────┘<br>
(8 rows)<br>
continue</font></p>
<br>
<p>I used rollforwarddb on the replica server to track down the
exact timing of the event, and correlate it with the SERVER
TRACE logs</p>
<span style="font-family:"Arial",sans-serif">The SESSION
BEGINS show user sbradfor is in two DB sessions running
simultaneously from different client PCs. At this site, the user
name is hard coded in the vnodes. <br>
</span>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif"> </span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif">Session 1
08:29:30 - 16:15:21</span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif">Session 2
08:25:50 - 15:56:22</span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif"> </span></p>
<p style="margin:0cm;margin-bottom:.0001pt"><span
style="font-family:"Arial",sans-serif"> </span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif">Here is an
example of overlapping events:</span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif"> </span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif">Note that
1618895626 = 15:13:46</span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif"> </span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif">qtrace_ctrust_168856_7f570eae5100.log</span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif">1618895626.089771974
check iitables, one row is returned, </span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif">1618895626.094464779
drop table, </span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif">1618895626.139567455
create table gives E_US13EE Duplicate table name</span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif">1618895626.549601019
rollback</span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif"> </span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif">qtrace_ctrust_168856_7f570f808440.log</span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif">1618895626.125806952
check iitables, zero rows returned</span></p>
<p style="margin:0cm;margin-bottom:.0001pt;box-sizing:
border-box;counter-reset: list-1 0 list-2 0 list-3 0 list-4 0
list-5 0 list-6 0 list-7 0 list-8 0 list-9 0"> <span
style="font-family:"Arial",sans-serif">1618895626.129856430
create table gives E_US07DA Duplicate Object name</span></p>
<span style="font-family:"Arial",sans-serif">1618895626.486972175
commit</span></div>
<div class="moz-forward-container"><span
style="font-family:"Arial",sans-serif"><br>
</span>
<p>Verifydb cannot run on the 24x7 production server because it
needs exclusive access. On the DR server verifydb aborts.</p>
<p>(be sure to upvote enhancement requests here:<a
title="verifydb" moz-do-not-send="true"
href="https://communities.actian.com/s/actian-communities-ideas#0874y000000kCs7AAE">
https://communities.actian.com/s/actian-communities-ideas#0874y000000kCs7AAE</a>
)<br>
</p>
<pre>verifydb -MREPORT -SDBNAME ctrust -ODBMS_CATALOGS</pre>
<pre>S_DU04C0_CKING_CATALOGS VERIFYDB: beginning check of DBMS catalogs for database ctrust
Aborting because of error
E_LQ006A The result of a singleton SELECT query or EXECUTE PROCEDURE with
RESULT ROW clause contains more than one row.
</pre>
<p><br>
</p>
<p>Clearing the problem proved simple:</p>
<pre>* select table_owner, varchar( table_name,20) tab, create_date, table_reltid from iitables where table_owner= 'sbradfor'\g
Executing . . .
┌────────────────────────────────┬────────────────────┬─────────────────────────┬─────────────┐
│table_owner │tab │create_date │table_reltid │
├────────────────────────────────┼────────────────────┼─────────────────────────┼─────────────┤
│sbradfor │tmp_cur_pricelist │2021_04_20 05:13:31 GMT │ 382408│
│sbradfor │tmp_cur_pricelist │2021_04_20 05:13:46 GMT │ 382414│
└────────────────────────────────┴────────────────────┴─────────────────────────┴─────────────┘
(2 rows)
continue
* drop table tmp_cur_pricelist;\g
Executing . . .
continue
* commit\g
Executing . . .
continue
* select table_owner, varchar( table_name,20) tab, create_date, table_reltid from iitables where table_owner= 'sbradfor'\g
Executing . . .
┌────────────────────────────────┬────────────────────┬─────────────────────────┬─────────────┐
│table_owner │tab │create_date │table_reltid │
├────────────────────────────────┼────────────────────┼─────────────────────────┼─────────────┤
│sbradfor │tmp_cur_pricelist │2021_04_20 05:13:46 GMT │ 382414│
└────────────────────────────────┴────────────────────┴─────────────────────────┴─────────────┘
(1 row)
continue
* drop table tmp_cur_pricelist;\g
Executing . . .
continue
* commit\g
Executing . . .
continue
* select table_owner, varchar( table_name,20) tab, create_date, table_reltid from iitables where table_owner= 'sbradfor'\g
Executing . . .
┌────────────────────────────────┬────────────────────┬─────────────────────────┬─────────────┐
│table_owner │tab │create_date │table_reltid │
├────────────────────────────────┼────────────────────┼─────────────────────────┼─────────────┤
└────────────────────────────────┴────────────────────┴─────────────────────────┴─────────────┘
(0 rows)
continue
* \q
Your SQL statement(s) have been committed.
</pre>
</div>
<div class="moz-forward-container">In my test case I can generate
several duplicated instances. I can alter the table structure and
insert data. HELP TABLE returns an error.<br>
</div>
<div class="moz-forward-container"><br>
</div>
<div class="moz-forward-container"><font face="monospace">[ingres@ckrs_linux
20210422]$ sql pw123<br>
INGRES TERMINAL MONITOR Copyright 2019 Actian Corporation<br>
Ingres Linux Version II 11.1.0 (a64.lnx/100) login<br>
Tue Apr 27 11:05:56 2021<br>
Enter \g to execute commands, "help help\g" for general help,<br>
"help tm\g" for terminal monitor help, \q to quit<br>
<br>
continue<br>
* select varchar(table_name,10), varchar(table_owner,10),
count(*)<br>
* from iitables<br>
* group by table_name, table_owner<br>
* having count(*) > 1;\g<br>
Executing . . .<br>
<br>
<br>
┌──────────┬──────────┬─────────────┐<br>
│col1 │col2 │col3 │<br>
├──────────┼──────────┼─────────────┤<br>
│pw1 │user4 │ 2│<br>
│pw1 │user9 │ 2│<br>
└──────────┴──────────┴─────────────┘<br>
(2 rows)<br>
continue<br>
* \q<br>
Your SQL statement(s) have been committed.<br>
<br>
Ingres Version II 11.1.0 (a64.lnx/100) logout<br>
Tue Apr 27 11:06:10 2021</font></div>
<div class="moz-forward-container"><font face="monospace"><br>
</font></div>
<div class="moz-forward-container"><font face="monospace">[ingres@ckrs_linux
20210422]$ sql pw123 -uuser4<br>
INGRES TERMINAL MONITOR Copyright 2019 Actian Corporation<br>
Ingres Linux Version II 11.1.0 (a64.lnx/100) login<br>
Tue Apr 27 11:06:19 2021<br>
Enter \g to execute commands, "help help\g" for general help,<br>
"help tm\g" for terminal monitor help, \q to quit<br>
<br>
continue<br>
* help table pw1\g<br>
Executing . . .<br>
<br>
<br>
Sorry, Error(s) occurred obtaining Help from system catalogs<br>
on object 'pw1'.<br>
You may not have permissions to access this object.<br>
<br>
continue<br>
* alter table pw1 add column fred varchar(10)\g<br>
Executing . . .<br>
<br>
continue<br>
* commit\g<br>
Executing . . .<br>
<br>
continue<br>
* select table_owner, varchar( table_name,20) tab,
varchar(column_name,20) col from iicolumns where table_owner=
'user4'\g<br>
Executing . . .<br>
<br>
<br>
┌────────────────────────────────┬────────────────────┬────────────────────┐<br>
│table_owner │tab
│col │<br>
├────────────────────────────────┼────────────────────┼────────────────────┤<br>
│user4 │pw1
│cust_code │<br>
│user4 │pw1
│catlog_code │<br>
│user4 │pw1
│price │<br>
│user4 │pw1
│disc_percent │<br>
│user4 │pw1
│fred │<br>
│user4 │pw1
│cust_code │<br>
│user4 │pw1
│catlog_code │<br>
│user4 │pw1
│price │<br>
│user4 │pw1
│disc_percent │<br>
└────────────────────────────────┴────────────────────┴────────────────────┘<br>
(9 rows)<br>
continue<br>
</font><font face="monospace">* continue</font><br>
</div>
<div class="moz-forward-container"><font face="monospace">* insert
into pw1 values ('cust','cat',123.45, 67.89)\g<br>
Executing . . .<br>
<br>
E_US0AD7 INSERT: number of target columns must equal the number
of<br>
specified values.<br>
(Tue Apr 27 11:12:23 2021)<br>
<br>
continue<br>
* insert into pw1 values ('cust','cat',123.45, 67.89,'fred')\g<br>
Executing . . .<br>
<br>
(1 row)<br>
continue<br>
* select * from pw1\g<br>
Executing . . .<br>
<br>
<br>
┌─────────┬────────────┬───────────┬────────────┬──────────┐<br>
│cust_code│catlog_code │price │disc_percent│fred │<br>
├─────────┼────────────┼───────────┼────────────┼──────────┤<br>
│SIDWILV0 │OLIVEOILP │ 82.000│ 0.000│ │<br>
│SIDWILV0 │UCO │ 0.000│ 0.000│ │<br>
│SIDWILV0 │XLFRY │ 2.500│ 0.000│ │<br>
│SIDWILV0 │XLFRYBAX15BB│ 37.500│ 0.000│ │<br>
│cust │cat │ 123.450│ 67.890│fred │<br>
└─────────┴────────────┴───────────┴────────────┴──────────┘<br>
(5 rows)<br>
continue<br>
*</font></div>
<div class="moz-forward-container"><font face="monospace">* drop
table pw1\g<br>
Executing . . .<br>
<br>
continue<br>
* select * from pw1\g<br>
Executing . . .<br>
<br>
<br>
┌─────────┬────────────┬───────────┬────────────┐<br>
│cust_code│catlog_code │price │disc_percent│<br>
├─────────┼────────────┼───────────┼────────────┤<br>
│SIDWILV0 │OLIVEOILP │ 82.000│ 0.000│<br>
│SIDWILV0 │UCO │ 0.000│ 0.000│<br>
│SIDWILV0 │XLFRY │ 2.500│ 0.000│<br>
│SIDWILV0 │XLFRYBAX15BB│ 37.500│ 0.000│<br>
└─────────┴────────────┴───────────┴────────────┘<br>
(4 rows)<br>
continue<br>
<br>
</font></div>
<div class="moz-forward-container"><br>
</div>
<div class="moz-forward-container"><br>
</div>
</body>
</html>