Re: Problems with DBI

Mon, 16 Aug 2004 09:18:11 -0700

I have a test.testac table created by a test script from a previous install. It is of type MyISAM, which does not support transactions. Did you alter or create your testac table to be of type InnoDB, which does support transactions? You can check with

  SHOW CREATE TABLE testac;

or

  SHOW TABLE STATUS LIKE 'testac';

I tried your code on my machine (as dbhtest.pl). First, I ran it with testac still of type MyISAM, with the following results:

DBD::mysql::db do failed: You have an error in your SQL syntax.  Check the manual that 
corresponds to your MySQL server version for the right syntax to use near 
'string',22)' at line 2 at /Users/stassenm/bin/dbhtest.pl line 18.
One of the SQL statements failed
DBD::mysql::db rollback failed: Warning:  Some non-transactional changed tables 
couldn't be rolled back at /Users/stassenm/bin/dbhtest.pl line 26.
DBD::mysql::db rollback failed: Warning:  Some non-transactional changed tables 
couldn't be rolled back at /Users/stassenm/bin/dbhtest.pl line 26.


Next, I converted testac to InnoDB, then ran it again, with these results:

DBD::mysql::db do failed: You have an error in your SQL syntax.  Check the manual that 
corresponds to your MySQL server version for the right syntax to use near 
'string',22)' at line 2 at /Users/stassenm/bin/dbhtest.pl line 18.
One of the SQL statements failed

In the latter case, the rollback was successful. In neither case did my output match yours.

For reference, I'm running Mac OS X (10.3.5), MySQL 4.0.20, DBI 1.42 (current is 1.43), and DBD-mysql 2.9003 (current is 2.9004). I note that your DBI version, 1.32, is significantly older than mine/current. You didn't say what version of DBD-mysql you have. So, I would guess that:

* testac is type MyISAM, so transactions aren't supported. Possibly your DBI-DBD-mysql is fine, other than that the error message wasn't very helpful.

or

* testac is type InnoDB, but there is a problem with rollback in your DBI-DBD-mysql which has since been fixed.

Michael

michael watson (IAH-C) wrote:

Hi

Not sure if I should be posting to the DBI list really, but I will try
this one too.

I'm running suse linux enterprise server 8.2, MySQL 4.0.15-standard-log,
DBI 1.32

I get the following error message:

DBD::mysql::db rollback failed: ROLLBACK failed at dbirollbacktest.cgi
line 49
DBD::mysql::db rollback failed: ROLLBACK failed at dbirollbacktest.cgi
line 49

My script looks like this:

my $dbh = DBI->connect("DBI:mysql:test:localhost", ... );
$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;

eval {
        # the first SQL statement should be OK
        my $sql = "insert into testac(object_id, object_title) values
(22,'michael')";
        $dbh->do($sql);

        # the second SQL statement should fail
        my $sql = "insert into testac(object_id, object_title) values
('\'string',22)";
        $dbh->do($sql);

        # if get to here, commit
        $dbh->commit();
};
if ($@) {
        print "One of the SQL statements failed\n";
        $dbh->rollback();
}

$dbh->disconnect;

Does anyone know why my rollback won't work?

Thanks
Mick



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to