Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Re: MySQL row_count and DBI

by graff (Chancellor)
on Apr 13, 2016 at 21:51 UTC ( #1160352=note: print w/replies, xml ) Need Help??


in reply to MySQL row_count and DBI

This seems like the sort of question that could be answered by experimentation. If you have access to both a Mysql and a MariaDB, you could set up the same test tables and data, and run the same operations on both to see if they return the same numbers.

If you only have access to MariaDB, I guess it's a matter of setting up test tables, test data, and test operations in such a way that you can confirm what the return values actually correspond to (in some set of scenarios relevant to your application).

If it really matters to you how this compares to a Mysql server, but you don't have access to try that out, perhaps you could post your experimental tables, data and operations here, and someone who does have Mysql could try it out and tell you what they get.

Replies are listed 'Best First'.
Re^2: MySQL row_count and DBI
by msh210 (Monk) on Apr 14, 2016 at 14:50 UTC

    Okay, good idea, graff. Here's what I did:

    use strict; use warnings; use DBI; use Try::Tiny; $|=1; my $redo; my $dbh; print 'mysql username: '; chomp(my $username=<STDIN>); print 'mysql password: '; system('stty','-echo');chomp(my $pw=<STDIN>);system('stty','echo'); print $/; sub reconn { $dbh = DBI->connect('dbi:mysql:scratch;mysql_enable_utf8=1',$usernam +e,$pw,{mysql_auto_reconnect => 1}) or die $DBI::errstr; } reconn; my $sth; # MariaDB [scratch]> create table foo(bar int,baz int,primary key(bar) +); # Query OK, 0 rows affected (0.02 sec) # # MariaDB [scratch]> insert foo(bar)value(1); # Query OK, 1 row affected (0.01 sec) # # MariaDB [scratch]> desc foo; # +-------+---------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+---------+------+-----+---------+-------+ # | bar | int(11) | NO | PRI | 0 | | # | baz | int(11) | YES | | NULL | | # +-------+---------+------+-----+---------+-------+ # 2 rows in set (0.00 sec) # # # MariaDB [scratch]> select * from foo; # +-----+------+ # | bar | baz | # +-----+------+ # | 1 | NULL | # +-----+------+ # 1 row in set (0.00 sec) $\=$/; my $rows; $dbh->ping or reconn; $sth = $dbh->prepare("insert foo(bar)value(2);"); $redo = 1; while ($redo) {$redo=0; try {$rows=$sth->execute();} catch +{$redo = /deadlock found/i;}} print "new: $rows"; undef $rows; $dbh->ping or reconn; $sth = $dbh->prepare("insert ignore foo(bar)value(1);"); $redo = 1; while ($redo) {$redo=0; try {$rows=$sth->execute();} catch +{$redo = /deadlock found/i;}} print "old ignore: $rows"; undef $rows; $dbh->ping or reconn; $sth = $dbh->prepare("insert foo(bar)value(1) on duplicate key update +baz=1;"); $redo = 1; while ($redo) {$redo=0; try {$rows=$sth->execute();} catch +{$redo = /deadlock found/i;}} print "old on duplicate key update: $rows";

    And the results were:

    new: 1 old ignore: 0E0 old on duplicate key update: 2

    And row_count gives the same numbers (in these cases):

    # MariaDB [scratch]> select * from foo; # +-----+------+ # | bar | baz | # +-----+------+ # | 1 | NULL | # +-----+------+ # 1 row in set (0.00 sec) # # MariaDB [scratch]> insert foo(bar)value(2); # Query OK, 1 row affected (0.00 sec) # # MariaDB [scratch]> select row_count(); # +-------------+ # | row_count() | # +-------------+ # | 1 | # +-------------+ # 1 row in set (0.00 sec) # # MariaDB [scratch]> insert ignore foo(bar)value(1); # Query OK, 0 rows affected, 1 warning (0.00 sec) # # MariaDB [scratch]> select row_count(); # +-------------+ # | row_count() | # +-------------+ # | 0 | # +-------------+ # 1 row in set (0.01 sec) # # MariaDB [scratch]> insert foo(bar)value(1) on duplicate key update b +az=1; # Query OK, 2 rows affected (0.00 sec) # # MariaDB [scratch]> select row_count(); # +-------------+ # | row_count() | # +-------------+ # | 2 | # +-------------+ # 1 row in set (0.00 sec)
    $_="msh210";$"=$\;@_=@{[split//,uc]}[2,0];$_="@_$\1";$\=$/;++$_[0]for$...1;print lc substr crypt($_,"@_"),1,6

      Ah, but now I've tried an on duplicate key update where the update didn't actually change anything (the value to update to was there already), and select row_count() gave 0, but execute returned 1. So they're not always the same.

      (The query was insert foo(bar)value(1)on duplicate key update baz=1; and the table already had 1 in each column.)

      $_="msh210";$"=$\;@_=@{[split//,uc]}[2,0];$_="@_$\1";$\=$/;++$_[0]for$...1;print lc substr crypt($_,"@_"),1,6

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1160352]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (3)
As of 2021-02-25 00:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?