[Info-ingres] The column with no datatype

[Paul White]

This is a bit like Colonel Mustard in the Drawing room with the ice pick


Need some clues Marty.

help table?   create time?  what is in errlog.log, iidbms...log around 
the time?

Is the database and table journaled?  Maybe run auditdb 
-aborted_transactions near the create time.

What is the state of the the extended table?

select r1.relid as base_table, c.attname as column_name, r2.relid as 
extend_table
from iirelation r1,iirelation r2,iiattribute c,iiextended_relation e
where r1.reltid=e.etab_base and r2.reltid=e.etab_extension
   and r1.reltid=c.attrelid
   and c.attid=e.etab_attid
   and r1.reltidx=0
   and c.attrelidx=0
where column_name = 'responses'
order by base_table,column_name

help table (r2.relid from above)

select * from iirelation where table_name = (r2.relid from above)

Paul


On 18/06/2021 9:18 pm, Martin Bowes wrote:

> Hi All,
>
> Knowing the perverted nature of the user in question I wondered if he 
> had created the table from a distributed database via a statement like:
>
> direct execute immediate ‘create table ztrans_symdig as select * from 
> symdig’
>
> with node = ‘…’, database =’…’
>
> So I set up a test case and … nope, worked perfectly.
>
> The user’s log file had better be phenomenally good.
>
> Marty
>
> *From:*Martin Bowes <martin.bowes at ndph.ox.ac.uk>
> *Sent:* 18 June 2021 11:25
> *To:* info-ingres at lists.planetingres.org
> *Subject:* [Info-ingres] The column with no datatype
>
> Hi All,
>
> So one day I did…
>
> copydb -c -uoqs oqs_result_live ztrans_symdig
>
> INGRES COPYDB Copyright 2016 Actian Corporation
>
> Unload directory is '/user/ingres'.
>
> Reload directory is '/user/ingres'.
>
> There are 0 sequences owned by user 'oqs'.
>
> There is one table owned by user 'oqs'.
>
> E_AD2003 ADF routine was passed an unknown datatype name.
>
> ‘WTF!’ I thought to myself and after some investigation….
>
> select column_sequence, column_datatype
>
> from iicolumns
>
> where table_name='ztrans_symdig' and column_name = 'responses';
>
> ┌───────────────┬────────────────────────────────┐
>
> │column_sequence│column_datatype │
>
> ├───────────────┼────────────────────────────────┤
>
> │ 18│                                │
>
> └───────────────┴────────────────────────────────┘
>
> (1 row)
>
> How is that possible?
>
> Now iicolumns is a view and so I went back to the iirelation and 
> iiattribute tables:
>
> select varchar(r.relid, 13), attid, varchar(attname, 13),
>
> a.attfrmt, a.attfrml,
>
> varchar(uppercase(iitypename(ii_ext_type(a.attfrmt, a.attfrml))), 12) 
> as datatype
>
> from iiattribute a
>
>     join iirelation r on a.attrelid = r.reltid and a.attrelidx = r.reltidx
>
> where a.attname = 'responses'
>
> ┌─────────────┬──────┬─────────────┬───────┬─────────────┬────────────┐
>
> │col1         │attid │col3 │attfrmt│attfrml      │datatype    │
>
> ├─────────────┼──────┼─────────────┼───────┼─────────────┼────────────┤
>
> │symdig       │    18│responses │     22│           32│LONG VARCHAR│
>
> │ztrans_symdig│    18│responses │     36│           16│            │
>
> └─────────────┴──────┴─────────────┴───────┴─────────────┴────────────┘
>
> (2 rows)
>
> I’m trying to track down how the user has created this table and if 
> they have a log of the activity.
>
> I think the user is doing a create table ztrans_symdig as select * 
> from symdig where 1 = 0;
>
> However,  when I try this the responses column correctly becomes a 
> long varchar without any problem.
>
> Any suggestions?
>
> Marty
>
>
> _______________________________________________
> Info-ingres mailing list
> Info-ingres at lists.planetingres.org
> https://lists.planetingres.org/mailman/listinfo/info-ingres
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20210618/7fbc378f/attachment.html>