FW: Double Quoting in Table names causing dynamic method access failures
am 31.07.2007 06:00:58 von CurtDBI Users & Greg,
(See Appendix A, and the 2 follow-up messages to the message listed in
Appendix A just above it, for background information)
I agree that Class::DBI may be involved. However, the module =
Class::DBI
specifically is not installed. I have since installed v3.0.16 with no
apparent positive or negative side effects (See Appendix B for the
installation summary). For a listing of all installed DBI related =
modules
see Appendix C. I have narrowed the source of the problem to an empty
$columns array reference called from the file:
/usr/lib/perl5/site_perl/5.8.8/Class/DBI/Pg.pm
In this file, the following method is called:
(In this method the value of $table is '"access"'; and I do mean =
'(double
quote)access(double quote)' (without the single quotes of course). The
double quoting of the table name is where the problem stems from.)
---- Begin Method ----
sub set_up_table {
my ( $class, $table, $opts ) =3D @_;
$opts ||=3D {};
my $dbh =3D $class->db_Main;
my $catalog =3D "";
if ( $class->pg_version >=3D 7.3 ) {
$catalog =3D 'pg_catalog.';
}
# find primary key
my $sth =3D $dbh->prepare(<<"SQL");
SELECT indkey FROM ${catalog}pg_index
WHERE indisprimary=3Dtrue AND indrelid=3D(
SELECT oid FROM ${catalog}pg_class
WHERE relname =3D ?)
SQL
$sth->execute($table);
my %prinum =3D map { $_ =3D> 1 } split ' ', ($sth->fetchrow_array || =
'');
$sth->finish;
# find all columns
$sth =3D $dbh->prepare(<<"SQL");
SELECT a.attname, a.attnum
FROM ${catalog}pg_class c, ${catalog}pg_attribute a
WHERE c.relname =3D ?
AND a.attnum > 0 AND a.attrelid =3D c.oid
ORDER BY a.attnum
SQL
$sth->execute($table);
!!!! ************************************************************ **!!!!
!!!! In the following line, the array ref $columns is empty. This =
should
not be. I think this is happening because my database does not contain =
a
table named '(double quote)access(double quote)', but it does contain a
table named 'access' (without the single quotes of course).
!!!!---->THIS IS EMPTY --> my $columns =3D $sth->fetchall_arrayref;
!!!! ************************************************************ **!!!!
$sth->finish;
# find SERIAL type.
# nextval('"table_id_seq"'::text)
$sth =3D $dbh->prepare(<<"SQL");
SELECT adsrc FROM ${catalog}pg_attrdef
WHERE
adrelid=3D(SELECT oid FROM ${catalog}pg_class WHERE relname=3D?)
SQL
$sth->execute($table);
my ($nextval_str) =3D $sth->fetchrow_array;
$sth->finish;
# the text representation for nextval() changed between 7.x and 8.x
my $sequence;
if ($nextval_str) {
if ($class->pg_version() >=3D 8.1) {
# hackish, but oh well...
($sequence) =3D
$nextval_str =3D~ =
m!^nextval\('"?([^"']+)"?'::regclass\)!i ?
$1 :
$nextval_str =3D~
m!^nextval\(\('"?([^"']+)"?'::text\)?::regclass\)!i ?
$1 :
undef;
} else {
($sequence) =3D $nextval_str =3D~
m!^nextval\('"?([^"']+)"?'::text\)!;
}
}
my ( @cols, @primary );
foreach my $col (@$columns) {
# skip dropped column.
next if $col->[0] =3D~ /^\.+pg\.dropped\.\d+\.+$/;
push @cols, $col->[0];
next unless $prinum{ $col->[1] };
push @primary, $col->[0];
}
@primary =3D @{ $opts->{Primary} } if $opts->{Primary};
if (!@primary) {
require Carp;
Carp::croak("$table has no primary key");
}
if ($opts->{Primary} && (! $opts->{ColumnGroup} || =
$opts->{ColumnGroup}
eq 'All')) {
$opts->{ColumnGroup} =3D 'Essential';
}
$class->table($table);
$class->columns( Primary =3D> @primary );
$class->columns( ($opts->{ColumnGroup} || 'All') =3D> @cols );
$class->sequence($sequence) if $sequence;
}
---- End Method ----
Since the $columns array reference is empty, it is impossible to find a
primary key, and I end up with the error from the PERL debugger:
---- Begin Error Message ----
# ./index.cgi=20
Loading DB routines from perl5db.pl version 1.28
Editor support available.
Enter h or `h h' for help, or `man perldebug' for more help.
main::(./index.cgi:14): if (open(DISABLED,'DISABLED')) {
DB<1> n
main::(./index.cgi:20): SVP::Main::main($svp::cgi_lib_dir);
DB<1> n
[Mon Jul 30 21:45:56 2007] index.cgi: "access" has no primary key at
/usr/lib/perl5/site_perl/5.8.8/Class/DBI/Loader/Generic.pm line 169
[Mon Jul 30 21:45:56 2007] index.cgi: at
/home/kkerce/svp/cgi/production/SVP/Main.pm line 43
at /usr/lib/perl5/5.8.8/CGI/Carp.pm line 314
CGI::Carp::realdie('[Mon Jul 30 21:45:56 2007] index.cgi: =
"access"
has no primary...') called at /usr/lib/perl5/5.8.8/CGI/Carp.pm line 400
CGI::Carp::die('"access" has no primary key at
/usr/lib/perl5/site_perl/5.8.8...') called at
/usr/lib/perl5/5.8.8/CGI/Carp.pm line 409
CGI::Carp::croak('"access" has no primary key at
/usr/lib/perl5/site_perl/5.8.8...') called at
/home/kkerce/svp/cgi/production/SVP/Main.pm line 43
SVP::Main::main('/home/kkerce/svp/cgi/production') called at
../index.cgi line 20
Debugged program terminated. Use q to quit or R to restart,
use o inhibit_exit to avoid stopping after program termination,
h q, h R or h o to get additional info.
---- End Error Message ----
BTW, I did try setting DBI_TRACE=3D10, however, I did not notice any
additional messages on STDOUT/STDERR. Thanks to everyone (and Greg) for
your input.
------------------------------------------------------------ -------------=
---
------------------------------------------------------------ -------------=
---
From:Greg Sabino Mullane
Date:July 27, 2007 10:33
Subject:Re: Double Quoting in Table names causing dynamic method access
failures
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> [Thu Jul 26 16:16:20 2007] index.cgi: "access" has no primary key at
> /usr/lib/perl5/site_perl/5.8.8/Class/DBI/Loader/Generic.pm line 169
Looks to me like Class::DBI is somewhat involved as well. Make sure that =
you hav ethe latest version of it. You haven't told us the command or =
part=20
of the script that is actually causing this error. It's probably some =
bad=20
interaction of Class::DBI and DBD::Pg, but hard to say without more =
info.=20
You might also try setting DBI_TRACE high (e.g. 10) and see if you can =
find=20
any clues in the verbiage.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200707271323
http://biglumber.com/x/web?pk=3D2529DF6AB8F79407E94445B4BC9B 906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFGqixOvJuQZxSWSsgRA7FiAKCUhAok6EbnnPZYb63vWetYuEFDmQCg 3Ev2
748vmBDQWlITlrhvNW7usqI=3D
=3Dq/Qp
-----END PGP SIGNATURE-----
------------------------------------------------------------ -------------=
---
------------------------------------------------------------ -------------=
---
-----Original Message-----
From: Curt Hesher [mailto:Curt@CurtechIT.com]=20
Sent: Friday, July 27, 2007 12:47 PM
To: 'dbi-users@perl.org'
Subject: Double Quoting in Table names causing dynamic method access
failures
DBI users (second send attempt; sorry if this is a repeat),
For some time now I=92ve been working to transition a =
service from an old
server to a new server.=A0 However, I=92ve been plagued by an error:
[Thu Jul 26 16:16:20 2007] index.cgi: "access" has no primary key at
/usr/lib/perl5/site_perl/5.8.8/Class/DBI/Loader/Generic.pm line 169
I=92m fairly certain the table in question (=93access=94) has a primary =
key, and
to see why, please read the section below titled =93Appendix A=94. =
=A0Finally I've
seen some progress. I've realized that the tables in question have names
that are apparently reserved words in SQL.=A0 The table names that I'm =
having
problems with are named "access", "location", "object", "role", and
"session" (all without the double quotes).=A0 When I call up the PERL =
debugger
and print the array holding the table names I get the following:
..
..
..
73=A0 'public.a_case'
74=A0 'public."access"'
75=A0 'public.additional_allegation'
76=A0 'public.additional_allegation_type'
..
..
..
110=A0 'public.http_user_agent_type'
111=A0 'public."location"'
112=A0 'public.login_attempt'
..
..
..
114=A0 'public.marital_status_of_parents_type'
115=A0 'public."object"'
116=A0 'public.operation'
..
..
..
135=A0 'public.relationship_to_child_type'
136=A0 'public."role"'
137=A0 'public.role_permission'
..
..
..
150=A0 'public.service_area_vicinity_type'
151=A0 'public."session"'
152=A0 'public.site_extra'
..
..
..
When I dump the database from the old server using pg_dump, I get dump =
file
with quotes around these 5 table names. I've tried removing the quotes =
in
the ascii dump file before using psql to rebuild the database on the new
server, but the quotes are always there when printing from the PERL
debugger, and only on these 5 table names. Is there a standard way to =
get
around this?=A0 Changing the table names is one of the last things that =
I want
to do at this time since I'm still learning about the massive program =
that
manipulates them.
The version of PostgreSQL on the old server is:
$ rpm -qa | grep postgresql
postgresql-jdbc-7.4.2-1PGDG
postgresql-tcl-7.4.2-1PGDG
postgresql-contrib-7.4.2-1PGDG
postgresql-7.4.2-1PGDG
postgresql-server-7.4.2-1PGDG
postgresql-python-7.4.2-1PGDG
postgresql-odbc-7.2.4-5.73
postgresql-libs-7.4.2-1PGDG
postgresql-test-7.4.2-1PGDG
postgresql-devel-7.4.2-1PGDG
postgresql-docs-7.4.2-1PGDG
postgresql-pl-7.4.2-1PGDG
And on the new server is:
# rpm -qa | grep postgresql
compat-postgresql-libs-4-2PGDG.fc6
postgresql-libs-8.2.4-1PGDG
postgresql-server-8.2.4-1PGDG
postgresql-8.2.4-1PGDG
Thanks to everyone for your time.
Curt Hesher, President
M.S. Computer Science
CURTECH Computer Services, LLC
www.at-home-computer-support.com
850.980.2571
Curt@CURTECHIT.com
=A0
Specializing in the setup, maintenance, and repair of PCs including =
networks
(LAN/WAN), hardware (modems, video cards, memory, sound cards, SCSI =
cards,
mass storage devices, USB and firewire adapters, routers, network =
interface
cards, monitors, speakers), software (MS Office, MS Windows XP, 2000, =
NT,
ME, 98SE, 98, 95, Easy CD Creator, virus scan software, and many =
others),
tutoring, troubleshooting, data backup and recovery, CD/DVD backup, =
virus
removal, and much more.=A0 I work with you, at your speed, at your =
location,
when ever is most convenient for you to help you succeed with modern
technology.
=A0
Privileged & Confidential.=A0 This email is intended for the named
recipient(s) only and may contain information that is proprietary,
privileged, confidential, or otherwise legally exempt from disclosure. =
If
you are not the named addressee, you are not authorized to read, print,
retain, copy, or disseminate this message or any part of it. Nothing in =
this
email is intended to constitute a waiver of any privilege or the
confidentiality of this message. If you have received this email in =
error,
please notify the sender immediately and delete this message. Thank you.
------------------------------------------------------------ -------------=
---
------------------------------------------------------------ -------------=
---
Appendix A
DBI users,
=A0 I=92ve recently taken on a project of =
moving a large Perl based
application from one server to another.=A0 In the process of setting =
things up
I=92ve come across this error message which I do not know how to =
handle.=A0 Over
the last 48 hours I=92ve checked the mail list archive at google, =
checked the
search engines (google, yahoo, live), read some docs (perldoc DBI, =
perldoc
DBI::Pg), and couldn=92t get the FAQ to load.=A0 If someone could shed =
some
light on this matter I would greatly appreciate it.
=A0
The error message (found in the Apache web server error_log file) is:
=A0
index.cgi: "access" has no primary key at
/usr/lib/perl5/site_perl/5.8.8/Class/DBI/Loader/Generic.pm line 166
=A0
This seems odd since the table =93access=94 does have a primary key as =
evidenced
by the following PostgreSQL psql command:
=A0
=A0
=A0
mydb=3D> \d access
=A0=
Table =
"public.access"
Column =A0 =
| =A0 =
Type |
Modifiers =A0 =
-------------------------+-----------------------------+---- ------------
-------------------------+-----------------------------+----
---------------------------------
=A0id = A0 | =
integer | =
not null default
nextval('access_id_seq'::regclass)
=A0login_attempt_id =A0 | =
integer =A0=A 0 | =
not null
=A0access_time | timestamp without =
time zone |=20
=A0http_user_agent_type_id | =
integer =A0=A 0 | =
not null
=A0ip = A0 | =
inet =A 0 =
|=20
=A0url | =
character varying |=20
Indexes:
=A0 "access_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
=A0 "$1" FOREIGN KEY (login_attempt_id) REFERENCES =
login_attempt(id)
"$2" FOREIGN KEY (http_user_agent_type_id) REFERENCES
http_user_agent_type(id)
=A0
=A0
=A0
Some details about the system that I=92m running on are:
=A0
=A0 # psql --version
psql (PostgreSQL) 8.1.8
contains support for command-line editing
=A0
=A0
# perldoc perllocal
Thu Apr 12 09:58:38 2007: "Module" DBI
=A0
· "installed into: =
/usr/lib/perl5/site_perl/5.8.8"
=A0
· "LINKTYPE: dynamic"
=A0
· "VERSION: 1.54"
=A0
· "EXE_FILES: dbiproxy dbiprof"
=A0
=A0
# perldoc perllocal
Thu Apr 12 12:18:50 2007: "Module" DBD::Pg
=A0
· "installed into: =
/usr/lib/perl5/site_perl/5.8.8"
=A0
· "LINKTYPE: dynamic"
=A0
· "VERSION: 1.49"
=A0
· "EXE_FILES: "
=A0
=A0
Thank you for your time.
------------------------------------------------------------ -------------=
---
------------------------------------------------------------ -------------=
---
------------------------------------------------------------ -------------=
---
------------------------------------------------------------ -------------=
---
Appendix B
# make test
PERL_DL_NONLAZY=3D1 /usr/bin/perl "-MExtUtils::Command::MM" "-e"
"test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/01-columns.............ok
t/02-Film................skipped
all skipped: needs DBD::SQLite for testing
t/03-subclassing.........skipped
all skipped: needs DBD::SQLite for testing
t/04-lazy................skipped
all skipped: needs DBD::SQLite for testing
t/05-Query...............skipped
all skipped: needs DBD::SQLite for testing
t/06-hasa................skipped
all skipped: needs DBD::SQLite for testing
t/08-inheritcols.........ok
t/09-has_many............skipped
all skipped: needs DBD::SQLite for testing
t/10-mysql...............skipped
all skipped: Need Date::Simple for this test
t/11-triggers............skipped
all skipped: needs DBD::SQLite for testing
t/12-filter..............skipped
all skipped: needs DBD::SQLite for testing
t/13-constraint..........skipped
all skipped: needs DBD::SQLite for testing
t/14-might_have..........skipped
all skipped: needs DBD::SQLite for testing
t/15-accessor............skipped
all skipped: needs DBD::SQLite for testing
t/16-reserved............skipped
all skipped: needs DBD::SQLite for testing
t/17-data_type...........ok
40/40 skipped: Can't insert new Binary: Cannot bind 1 unknown
sql_type -2 at /home/svpdb/tmp/Class-DBI-v3.0.16/blib/lib/Class/DBI.pm =
line
646.
t/18-has_a...............skipped
all skipped: needs DBD::SQLite for testing
t/19-set_sql.............skipped
all skipped: needs DBD::SQLite for testing
t/21-iterator............skipped
all skipped: needs DBD::SQLite for testing
t/22-deflate_order.......skipped
all skipped: Need Time::Piece::MySQL for this test
t/23-cascade.............skipped
all skipped: needs DBD::SQLite for testing
t/24-meta_info...........ok
t/25-closures_in_meta....skipped
all skipped: Need MySQL for this test
t/26-mutator.............skipped
all skipped: needs DBD::SQLite for testing
t/27-mutator-old.........skipped
all skipped: needs DBD::SQLite for testing
t/97-pod.................skipped
all skipped: Test::Pod 1.00 required for testing POD
t/98-failure.............skipped
all skipped: needs DBD::SQLite for testing
t/99-misc................skipped
all skipped: needs DBD::SQLite for testing
All tests successful, 24 tests and 40 subtests skipped.
Files=3D28, Tests=3D72, 1 wallclock secs ( 1.22 cusr + 0.28 csys =3D =
1.50 CPU)
# make install
Writing
/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/a uto/Class/DBI=
/.p
acklist
Appending installation info to
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.p od
------------------------------------------------------------ -------------=
---
------------------------------------------------------------ -------------=
---
------------------------------------------------------------ -------------=
---
------------------------------------------------------------ -------------=
---
Appendix C
Thu Apr 12 09:58:38 2007: "Module" DBI
· "installed into: /usr/lib/perl5/site_perl/5.8.8"
· "LINKTYPE: dynamic"
· "VERSION: 1.54"
· "EXE_FILES: dbiproxy dbiprof"
Thu Apr 12 10:57:12 2007: "Module" DBIx::ContextualFetch
· "installed into: /usr/lib/perl5/site_perl/5.8.8"
· "LINKTYPE: dynamic"
· "VERSION: 1.03"
· "EXE_FILES: "
Thu Apr 12 11:11:26 2007: "Module" Ima::DBI
· "installed into: /usr/lib/perl5/site_perl/5.8.8"
· "LINKTYPE: dynamic"
· "VERSION: 0.34"
· "EXE_FILES: "
Thu Apr 12 11:26:48 2007: "Module" Class::DBI::Loader
· "installed into: /usr/lib/perl5/site_perl/5.8.8"
· "LINKTYPE: dynamic"
· "VERSION: 0.34"
· "EXE_FILES: "
Thu Apr 12 12:19:44 2007: "Module" Class-DBI-Pg
· "installed into: /usr/lib/perl5/site_perl/5.8.8"
· "LINKTYPE: dynamic"
· "VERSION: 0.09"
· "EXE_FILES: "
Thu Apr 12 22:29:57 2007: "Module" Class::DBI::Plugin::RetrieveAll
· "installed into: /usr/lib/perl5/site_perl/5.8.8"
· "LINKTYPE: dynamic"
· "VERSION: 1.04"
· "EXE_FILES: "
Thu Apr 12 11:15:02 2007: "Module" Class::DBI
· "installed into: /usr/lib/perl5/site_perl/5.8.8"
· "LINKTYPE: dynamic"
· "VERSION: v3.0.16"
· "EXE_FILES: "
------------------------------------------------------------ -------------=
---
------------------------------------------------------------ -------------=
---