Status of DBD::mysql with MySQL 5
am 25.02.2006 11:25:18 von Giuseppe MaxiaHi, all.
I missed Patrick's request for comments almost one month ago, and I hope
I can still contribute some points.
Migrating Perl applications from 4.1 to 5.0 brings several problems ranging
from broken compatibility to the unavailability of new MySQL 5 features
due to lack of suitable interface.
(1)
The first one is related to the prepared statement protocol, which is
the default behavior in the latest development releases.
This breaks code with statements like "USE db_name", because this command
is not supported by the prepared statement protocol. I reported this
problem (bug #15665) and it was fixed in release 3.0002_5
Unfortunately, I was not thorough enough in my report, since I stopped
at the first problem I had (with USE, that is). But the same problem
arises when using any replication related commands, such as
CHANGE MASTER TO, SLAVE START|STOP, SHOW MASTER/SLAVE STATUS, and
actually most of the SHOW commands.
Either all these commands are filtered (which should slow down query execution
speed), or the default behavior should be brought back to the emulated prepared
statements.
Moreover, I learned recently, during a presentation by a MySQL developer, that
the only benefit of prepared statements in MySQL is to have the query on the
server side, i.e. we only save network bandwidth, but the prepared query is
evaluated through the query optimizer every time. There is no real "prepared"
query mechanism that saves optimizer time. With this fact in mind, I believe
that the balance between this little benefit and the risk of breaking existing code
is in favor of keeping emulated prepared statements as the default.
(2)
The second problem deals with stored procedures that return one or more record sets.
In C, it is enough to use mysql_next_result to get all record sets in order, with their
possibly different metadata correctly read.
With DBD::mysql, only the first record set is recognized and reported.
Example:
delimiter //
drop procedure if exists several//
create procedure several()
begin
select user() as u,now() as t;
select database() as d,now() as t , "hello";
end //
delimiter ;
Using a C routine that reads all recordsets (the same as the regular mysql client does), I get
$ ./multi_client
query> use test
0 rows affected
query> call several()
+================+=====================+
| u | t |
+================+=====================+
| user@localhost | 2006-02-25 11:03:05 |
+================+=====================+
1 rows returned
+======+=====================+=======+
| d | t | hello |
+======+=====================+=======+
| test | 2006-02-25 11:03:05 | hello |
+======+=====================+=======+
1 rows returned
0 rows affected
query> exit
There is no equivalent Perl routine to achieve the same result.
Here is my try:
# --- CUT ----
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Data::Dumper;
my $HOST = '127.0.0.1';
my $dbh = DBI->connect("DBI:mysql:test;host=$HOST"
. ";mysql_read_default_file=$ENV{HOME}/.my.cnf;mysql_multi_res ults=1",
undef, undef, {RaiseError => 1})
or die q{can't connect\n};
print $DBD::mysql::VERSION, $/;
print Dumper $dbh->selectall_arrayref('call several()', {Slice => {}});
# --- CUT ----
From this snippet, I get:
3.0002_5
$VAR1 = [
{
'u' => 'gmax@localhost',
't' => '2006-02-25 11:05:39'
}
];
Perhaps I am missing some obvious thing, but I wasn't able to find any help in the docs so far.
(3)
The third obstacle to have full development with stored procedures is a mechanism
to pass parameters to procedures accepting an OUT or INOUT argument. There is no standard
DBI method for this, so I believe that it is time to make a specific extension to
DBD::mysql.
example:
create procedure xyx (IN param_w, IN param_x, OUT param_y, INOUT param_z ) ....
I could not find any way of passing a variable to param_y and param_z.
All comments on these points will be greatly appreciated.
Ciao
Giuseppe
--
_ _ _ _
(_|| | |(_|>< The Data Charmer
_|
http://datacharmer.blogspot.com/
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org