DBI 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 ) = @_;
$opts ||= {};
my $dbh = $class->db_Main;
my $catalog = "";
if ( $class->pg_version >= 7.3 ) {
$catalog = 'pg_catalog.';
}
# find primary key
my $sth = $dbh->prepare(<<"SQL");
SELECT indkey FROM ${catalog}pg_index
WHERE indisprimary=true AND indrelid=(
SELECT oid FROM ${catalog}pg_class
WHERE relname = ?)
SQL
$sth->execute($table);
my %prinum = map { $_ => 1 } split ' ', ($sth->fetchrow_array || '');
$sth->finish;
# find all columns
$sth = $dbh->prepare(<<"SQL");
SELECT a.attname, a.attnum
FROM ${catalog}pg_class c, ${catalog}pg_attribute a
WHERE c.relname = ?
AND a.attnum > 0 AND a.attrelid = 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 = $sth->fetchall_arrayref;
!!!! **************************************************************!!!!
$sth->finish;
# find SERIAL type.
# nextval('"table_id_seq"'::text)
$sth = $dbh->prepare(<<"SQL");
SELECT adsrc FROM ${catalog}pg_attrdef
WHERE
adrelid=(SELECT oid FROM ${catalog}pg_class WHERE relname=?)
SQL
$sth->execute($table);
my ($nextval_str) = $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() >= 8.1) {
# hackish, but oh well...
($sequence) =
$nextval_str =~ m!^nextval\('"?([^"']+)"?'::regclass\)!i ?
$1 :
$nextval_str =~
m!^nextval\(\('"?([^"']+)"?'::text\)?::regclass\)!i ?
$1 :
undef;
} else {
($sequence) = $nextval_str =~
m!^nextval\('"?([^"']+)"?'::text\)!;
}
}
my ( @cols, @primary );
foreach my $col (@$columns) {
# skip dropped column.
next if $col->[0] =~ /^\.+pg\.dropped\.\d+\.+$/;
push @cols, $col->[0];
next unless $prinum{ $col->[1] };
push @primary, $col->[0];
}
@primary = @{ $opts->{Primary} } if $opts->{Primary};
if ([EMAIL PROTECTED]) {
require Carp;
Carp::croak("$table has no primary key");
}
if ($opts->{Primary} && (! $opts->{ColumnGroup} || $opts->{ColumnGroup}
eq 'All')) {
$opts->{ColumnGroup} = 'Essential';
}
$class->table($table);
$class->columns( Primary => @primary );
$class->columns( ($opts->{ColumnGroup} || 'All') => @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
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=10, 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
of the script that is actually causing this error. It's probably some bad
interaction of Class::DBI and DBD::Pg, but hard to say without more info.
You might also try setting DBI_TRACE high (e.g. 10) and see if you can find
any clues in the verbiage.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200707271323
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFGqixOvJuQZxSWSsgRA7FiAKCUhAok6EbnnPZYb63vWetYuEFDmQCg3Ev2
748vmBDQWlITlrhvNW7usqI=
=q/Qp
-----END PGP SIGNATURE-----
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-----Original Message-----
From: Curt Hesher [mailto:[EMAIL PROTECTED]
Sent: Friday, July 27, 2007 12:47 PM
To: '[email protected]'
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 Ive been working to transition a service from an old
server to a new server. However, Ive 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
Im fairly certain the table in question (access) has a primary key, and
to see why, please read the section below titled Appendix A. Finally I've
seen some progress. I've realized that the tables in question have names
that are apparently reserved words in SQL. The table names that I'm having
problems with are named "access", "location", "object", "role", and
"session" (all without the double quotes). When I call up the PERL debugger
and print the array holding the table names I get the following:
.
.
.
73 'public.a_case'
74 'public."access"'
75 'public.additional_allegation'
76 'public.additional_allegation_type'
.
.
.
110 'public.http_user_agent_type'
111 'public."location"'
112 'public.login_attempt'
.
.
.
114 'public.marital_status_of_parents_type'
115 'public."object"'
116 'public.operation'
.
.
.
135 'public.relationship_to_child_type'
136 'public."role"'
137 'public.role_permission'
.
.
.
150 'public.service_area_vicinity_type'
151 'public."session"'
152 '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? 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
[EMAIL PROTECTED]
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. I work with you, at your speed, at your location,
when ever is most convenient for you to help you succeed with modern
technology.
Privileged & Confidential. 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,
Ive recently taken on a project of moving a large Perl based
application from one server to another. In the process of setting things up
Ive come across this error message which I do not know how to handle. Over
the last 48 hours Ive checked the mail list archive at google, checked the
search engines (google, yahoo, live), read some docs (perldoc DBI, perldoc
DBI::Pg), and couldnt get the FAQ to load. If someone could shed some
light on this matter I would greatly appreciate it.
The error message (found in the Apache web server error_log file) is:
index.cgi: "access" has no primary key at
/usr/lib/perl5/site_perl/5.8.8/Class/DBI/Loader/Generic.pm line 166
This seems odd since the table access does have a primary key as evidenced
by the following PostgreSQL psql command:
mydb=> \d access
Table "public.access"
Column | Type |
Modifiers
-------------------------+-----------------------------+----------------
-------------------------+-----------------------------+----
---------------------------------
id | integer | not null default
nextval('access_id_seq'::regclass)
login_attempt_id | integer | not null
access_time | timestamp without time zone |
http_user_agent_type_id | integer | not null
ip | inet |
url | character varying |
Indexes:
"access_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"$1" FOREIGN KEY (login_attempt_id) REFERENCES login_attempt(id)
"$2" FOREIGN KEY (http_user_agent_type_id) REFERENCES
http_user_agent_type(id)
Some details about the system that Im running on are:
# psql --version
psql (PostgreSQL) 8.1.8
contains support for command-line editing
# perldoc perllocal
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"
# perldoc perllocal
Thu Apr 12 12:18:50 2007: "Module" DBD::Pg
· "installed into: /usr/lib/perl5/site_perl/5.8.8"
· "LINKTYPE: dynamic"
· "VERSION: 1.49"
· "EXE_FILES: "
Thank you for your time.
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
Appendix B
# make test
PERL_DL_NONLAZY=1 /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=28, Tests=72, 1 wallclock secs ( 1.22 cusr + 0.28 csys = 1.50 CPU)
# make install
Writing
/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/Class/DBI/.p
acklist
Appending installation info to
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
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: "
----------------------------------------------------------------------------
----------------------------------------------------------------------------
