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
>
