Code levels: Redhat EL3, perl-DBI-1.602-1.el3.rf, perl-DBD-mysql-4.006-1.el3.rf, perl-PlRPC-0.2020-1.el3.rf, perl-5.8.0-97.EL3, MySQL-devel-community-5.1.23-0.rhel3, MySQL-shared-compat-5.1.23-0.rhel3, MySQL-client-community-5.1.23-0.rhel3 and MySQL-server-community-5.1.23-0.rhel3.
I have defined a table to use the InnoDB engine so that I can use the FOREIGN KEY feature. If I manually execute the SQL "INSERT" command line from the MySQL command line client, the INSERT is successful and a SELECT * FROM [table_name]; returns the row data ok.
If I DROP and CREATE the table (to clear it out) and execute the same INSERT command from a perl script, $DBI::errstr is not set (no error) but SELECT * from [table_name]; returns 0 rows. Re-running the script should produce a 'duplicate key' error, but it doesn't. If I execute two identical INSERT commands in the same script one after the other, then I get a 'duplicate key' error on the second INSERT executed but 'SELECT * FROM [table_name];' still returns 0 rows. Re-running the double INSERT script still only produces the one 'duplicate' error on the second INSERT, not the first INSERT. As this works ok from the MySQL-client-community interface, I believe it's either a perl DBD or DBI problem. I have updated both perl DBD/DBI & MySQL to the latest versions I can find and the problem is still occuring. If I redefine the TABLE to use the MyISAM engine, the same INSERT script works ok (SELECT * from .... returns rows of data ok) but this engine doesn't support referential integrity via foreign keys. Any suggestions please? Test code bits and pieces:
CREATE TABLE network_names (network_name_key INT UNSIGNED NOT NULL AUTO_INCREMENT, networkName VARC
+HAR(20) not null, PRIMARY KEY (network_name_key)) ENGINE = InnoDB;
INSERT INTO network_names (networkName) values('ME');
INSERT INTO network_names (networkName) values('YOU');
INSERT INTO network_names (networkName) values('PRIVATE');
CREATE TABLE router_namesx (network_name_key INT UNSIGNED NOT NULL, routerName VARCHAR(30), vlanNum
+ber INT(6) UNSIGNED NOT NULL, networkSubnet INT UNSIGNED NOT NULL, index (network_name_key), PRIMA
+RY KEY (routerName,vlanNumber), FOREIGN KEY (network_name_key) REFERENCES network_names (network_n
+ame_key)) ENGINE = InnoDB;
#! /usr/bin/perl -w
# -*- mode: Perl -*-
require 5.005;
use strict;
use warnings;
use IO::Socket;
use Net::hostent;
use DBI;
use Net::SNMP;
my $db="testDB";
my $passwd="mypassword";
my $userid = "myuserid";
my $host = "localhost";
my $driver = "mysql";
my $connectionInfo="dbi:$driver:$db;$host";
my $result = read_vlanTable();
exit;
sub read_vlanTable {
# my $networkName = &trim(shift(@argList));
# my $host = &trim(shift(@argList));
# my $version = &trim(shift(@argList));
# my $community = &trim(shift(@argList));
# my $oid = ".1.3.6.1.4.1.9.5.1.9.2.1.1"; # VLAN table from CISCO-STACK-MIB
my $networkSubnet = "0";
my $networkNameKey;
my $vlanNumber;
my $dbh;
my $sth;
my $sql;
if ($dbh = DBI->connect($connectionInfo,$userid,$passwd,{RaiseError => 0,AutoCommit => 0})) {
$sth = $dbh->prepare("INSERT INTO router_namesx (network_name_key,routerName,vlanNumber,network
+Subnet) VALUES (?,?,?,?)");
$sth->execute("1", "EastCoast15", "10");
print $client "\r\nsql error: $DBI::errstr";
$sth->finish(); $dbh->disconnect;
}
return;
}
This works from the "mysql" command line prompt with an engine type of InnoDB on the table:
INSERT INTO router_namesx (network_name_key, routerName, vlanNumber) values('1','EastCoast15','10')
+;
Thanks in advance,
Neil M
